轉自知乎問題:mysql索引最左比對原則的了解?
具體問題描述如下:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_cid_INX` (`name`,`cid`),
KEY `name_INX` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
随便建了一個student表做測試。
create INDEX name_cid_INX ON student(name,cid)
;
create INDEX name_INX ON student(name);
建了兩個索引,故意這樣建的。
執行1:
依據mysql索引最左比對原則,兩個索引都比對上了,這個沒有問題。。
執行2:
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小紅';
為什麼還能比對索引。
**歸納如下:**
(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看看執行計劃。