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 | | |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------