例如,我们有表：
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,date.
据我所知,如果我们有两个字段的索引,我们可以将它用作第一个字段的单个索引.
更新：问这个问题引起注意,有时不同索引中的同一个字段具有不同的基数.
最佳答案
MysqL 5.7.9 – 删除id_type索引并没有什么区别.多列索引(type_date)适用于两个查询.
原文链接：https://www.f2er.com/mysql/432852.htmlMysqL> 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 | | |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------