转自知乎问题:mysql索引最左匹配原则的理解?
具体问题描述如下:
**归纳如下:**
(1)沈杰的回复很清楚,第一个查询
select * from student where name = 'xx' and cid = xx;
显然这个查询使用了索引覆盖,因为表存在索引:
KEY `name_cid_INX` (`name`,`cid`)
(2)第二个查询,explain也显示使用了覆盖索引
select * from student where cid = xxx;
因为id是主键,是聚集索引,name,cid是联合索引,那么联合索引里也包含了id,即表中所以的列,不用再回表查询,索引是using index;
(3)根据(2)进行测试,给student新增一列sex,如下
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | MUL | NULL | |
| cid | int(11) | YES | | NULL | |
| sex | varchar(8) | YES | | | |
+-------+--------------+------+-----+---------+----------------+
再次执行(2),得到的explain信息如下:
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
证明(2)是正确的,这里如果有兴趣可以使用optimizer_trace看看执行计划。