Mysql索引最左匹配原则
前提知识:使用explain确定一条查询计划
示例表SQL:
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`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
SQL1:
EXPLAIN SELECT * FROM student WHERE cid=1;--type:index
SQL2:
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';--type:ref
解释一下index和ref的区别:
* index:mysql会对整个该索引进行扫描,效率低,会遍历索引中数据知道符合为止,触发:只要是索引,或者是某个复合索引的一部分
* ref:mysql内部使用特定算法优化过的,触发:索引字段数据必须有序。
复合索引:以该表的(name,cid)复合索引为例,内部简单来说就是这样排列的:
name cid
a——-6
c——-4
c——-5
h——-1
z——-9
* mysql创建复合索引的规则是首先对复合索引最左边的(这里是name)字段的数据进行排序,在此基础上,再对后面的字段(这里是cid)进行排序,类似于order by name cid这样
* 第一个字段(name)是绝对有序的,后面的字段就是无序的了,一般情况下第二个cid字段进行条件判断是用不到索引的,可能出现type是index类型的,这就是mysql最左前缀的原因。
使用到的场景:
* cid字段的索引数据也是有序的时候,便可以使用,当name字段是等值匹配的情况下,cid是有序的,当name都是c的时候,cid的排序是4、5。
* mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引。
对于
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';
复合索引是(name, cid),这时mysql查询优化器会纠正这条sql语句该以哪种顺序执行效率最高,最后生成真正的执行计划(QEP),尽量利用到索引时查询顺序效率最高。所以mysql查询优化器最终会以这种顺序查询。