mysql 索引 字段重复_MySQL表中具有相同字段的两个索引

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

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------