乐鱼app：MySQL表中具有相同字段的两个索引

例如爱游戏app下载亚博app安全有保障 ,我们有表：

CREATE TABLE `my_tbl` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_type` int(11) NOT NULL,
 `date` date NOT NULL,
 `other_fields` varchar(200) CHARACTER SET latin1 NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id_type` (`id_type`),
 KEY `type_date` (`id_type`,`date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Ther是两个索引：id_type和id_type澳洲幸运10 ,date.

据我所知索引把两个表有相同字段的,如果我们有两个字段的索引索引把两个表有相同字段的爱游戏官网app华体会真人 ,我们可以将它用作第一个字段的单个索引.

我可以删除索引id_type而不会丢失性能吗？

更新：问这个问题引起注意,有时不同索引中的同一个字段具有不同的基数.

MySQL 5.7.9 – 删除id_type索引并没有什么区别.多列索引(type_date)适用于两个查询.

解释查询输出：

mysql> explain SELECT id_type,date FROM my_tbl WHERE id_type='some';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_tbl | NULL       | ref  | type_date     | type_date | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+-----
mysql> explain  SELECT id_type FROM my_tbl WHERE id_type='some';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
        +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_tbl | NULL       | ref  | type_date     | type_date | 4       | const |    1 |   100.00 | Using index |
        +----+-------------+--------+------------+------+---------------+---------
mysql> show indexes from my_tbl;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| my_tbl |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| my_tbl |          1 | type_date |            1 | id_type     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| my_tbl |          1 | type_date |            2 | date        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------