mysql最左前缀匹配举例子_mysql知识点总结1 —— mysql联合索引最左前缀匹配

Mysql联合索引的最左前缀匹配原则是面试中常问的知识点，之前也在网上看到过很多的文章，但是感觉都不够全面，所以这里就自己总结一下。

2. 概念

2.1 索引原理

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。

2.1.1 构建索引目的

构建索引在于提高查询效率，索引类似书的目录，需要几页纸来存放目录，但通过目录可快速定位到章、节部分。同理，如果没有索引的话就需要在无序的表中遍历表直到找到指定的数据。

2.1.2 索引定义

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

2.1.3 索引优缺点：优点大大加快数据的检索速度

创建唯一性索引，保证数据库表中每一行数据的唯一性

加速表与表之间的连接

在使用分组和排序子句进行数据检索时，可以显著的减少查询中分组和排序的时间

缺点索引索要占用额外的物理空间

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

2.2 索引建立规则

用于索引最好的备选数据列是那些出现在where子句、join子句、order by或group by子句中的列。仅仅出现在select关键字后面的输出数据列列表中的数据列并不是很好的备选列。

1

2

3

4

5

6

7SELECT

name // 不是很好的备选列

FROM

student LEFT JOIN score

ON student.name = score.studentName //好的备选列

WHERE

point = 90; //好的备选列

2.3 最左前缀匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

示例：

1

2

3

4

5

6

7

8

9CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` VARCHAR(5) DEFAULT 'male',

`addr` VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `name_age_sex`(`name`,`age`,`sex`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

上面代码对列name、列age和列sex建了一个联合索引name_age_sex，索引中的数据行按照name/age/sex的次序排列，这意味着，即使你在查询中只指定了name值，或者指定name和age值，MySQL也可以使用这个索引(最左匹配)。因此，这个索引可以被用于搜索如下所示的数据列组合：

name, age, sex

name, age

name

注意：查询语句中的字段顺序可以不完全按照索引的顺序。1

2SELECT * FROM student WHERE name="li" AND age=16

SELECT * FROM student WHERE age=16 AND name="li"

上面两个查询语句的结果是完全一样的，只不过是字段顺序不一致，但都会用到联合索引name_age_sex，因为mysql的查询优化器会帮你优化成索引可以识别的形式。最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

经过实际验证(mysql版本5.7.11)1

2

3SELECT * FROM student WHERE name="li" AND sex='male'; //可以使用联合索引name_age_sex

SELECT * FROM student WHERE age=16 AND sex="male"; //不可以使用联合索引name_age_sex

SELECT * FROM student WHERE age=16; //不可以使用联合索引name_age_sex

但网上有好多人说第二个和第三个语句可以使用索引类型为index的索引，可能版本问题，有待进一步验证。

附

1.帮助理解联合索引的一个小例子：

假设数据 表T (a,b,c) rowid 为物理位置

rowid a b c

(1) 1 1 1

(2) 2 1 13

(3) 2 2 14

(4) 1 3 3

(5) 2 3 12

(6) 1 2 5

(7) 2 3 9

(8) 1 2 2

(9) 1 3 6

(10) 2 2 11

(11) 2 2 8

(12) 1 1 7

(13) 2 3 15

(14) 1 1 4

(15) 2 1 10

当你创建一个索引 create index index_a_b_c on t(a,b,c), 则索引文档逻辑上等同于如下

a b rowid

1 1 1

1 1 12

1 1 14

1 2 6

1 2 8

1 3 4

1 3 9

2 1 2

2 1 15

2 2 3

2 2 10

2 2 11

2 3 5

2 3 7

2 3 13

当select * from T where a=1 and b=3 的时候， 数据库系统可以直接从索引文档中直接二分法(mysql索引是基于B+树的)找到A=1的记录，然后再B=3的记录。

但如果你 where b=3 则需要遍历这个索引表的全部！

3.索引为什么基于B+树而不是hash索引

(1)由于它们两个的数据结构，hash索引只适用于等值查询(等值查询时hash索引优于B+)，(相当)不适用于范围查询(key相邻的数据的位置不一定相邻)，而B+树在存储数据的叶子节点增加了顺序访问指针，提高了范围查找的效率。

(2)同理，hash索引也不能利用索引完成排序(key相邻的数据的位置不一定相邻)，以及模糊查询。

(3)hash索引不支持联合索引的最左匹配规则。

参考