MySQL中的常见索引规则

灰太狼 2022-10-29 06:29 9阅读 0赞

在使用索引规则的时候，会有一个前置条件，就是索引的类型一般是联合索引，至于为什么要使用联合索引，是因为数据的增删改都会维护索引，所以过多的索引在方便我们快速查询的时候，同时还会带来一部分的性能问题。这时就需要使用到联合索引了，联合索引既能减少索引的数量，又能满足我们查询的需求

1.  **全值匹配规则**

这个规则的使用需要我们在where条件的判断中，根据联合索引的顺序来查询，同时条件必须是等于

举个例子，假如我们有一张表，主键就是id，还有name，age，email这些字段，假如我们建立了一个name和age的联合索引，那么在查询的时候，使用select \* from table where name = ‘张三’ and age = 20; 这样的查询语句就能使用到全值匹配规则

1.  **最左侧列匹配规则**

还是以上面的表为例，假设我们按照name，age，email的顺序的顺序建立了一个联合索引，要满足最左侧列匹配规则的话，我们在进行查询的时候，就必须以name作为where条件的开头，下面举几个例子

select \* from table where name = ‘张三’ and age = 20; 这种语句就能使用到我们的规则，因为是以最左侧列作为开始查询条件

select \* from table where age = 20 and email = ‘XXX@163.com’; 这种的语句就无法使用最左侧列匹配规则，因为必须要以name作为查询的初始条件

select \* from table where name = ‘张三’ and email = ‘XXX@163.com’; 这条语句只有name能在索引中使用，而email无法使用到索引

这里解释一下原因，这也是联合索引的查询机制，如下图所示  
![联合索引][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xPX1lVTg_size_16_color_FFFFFF_t_70_pic_center]  
我们的联合索引页中包含了联合索引中的字段和表的主键，同时索引页向下指向我们的数据页，数据页存储的就是具体的数据，索引页中只是多个数据页的最小主键值的记录，数据页和数据页之间组成双向链表，里面的数据之间组成单向链表

数据页中的数据都是有顺序的，排序方式会按照联合索引中的字段来排序，如图所示，张三那条数据就排在李四那条数据之前，排过序的数据在最后的查询过程中就可以基于二分查找快速定位到具体的位置，那么如果我们查询的条件不是按照联合索引的顺序，或者是中间查询字段出现了断层，就会破坏索引本身维护的结构，导致无法使用索引或者部分使用到索引

1.  **最左前缀匹配原则**

这个规则一般使用在模糊搜索的场景中，假设我们的查询语句是 select \* from table where name like ‘%三’; 此时我们是没法使用到索引的，而如果我们的SQL是 select \* from table where name like ‘张%’; 这样就可以使用到索引，我们需要保证在模糊查询中最左侧不使用%

1.  **范围查找规则**

还是直接举例子，select \* from table where name > ‘张三’ and name < ‘王五’; 这条语句是可以使用到我们的联合索引的，在数据页中，直接找到name等于张三和王五的数据页，然后直接取范围内的数据即可，而如果SQL是 select \* from table where name > ‘张三’ and age < 32; 这样的话，前面的name范围查询可以使用到索引，后面的age就没法使用到索引了

如果where条件中有范围查询的话，只有最左侧列才能使用到索引

1.  **等值匹配 + 范围匹配规则**

这条规则其实是对上面一个规则的补全，如果非要根据非最左侧列进行范围查询，并且想要使用索引，还有一个办法，就是保证使用范围查询的列之前的字段都是等值匹配的，比如 select \* from table where name = ‘张三’ and age > 18; 这么做的话数据页中会先根据name精确定位到一些数据，再对这些数据进行范围查询，这样就能使用到我们的索引了

除了上面常用的索引使用规则，还有一些特定场景下的索引使用技巧

比如说常见的order by排序，如果是随便按照字段的顺序排序的话，可能就会导致数据进入到内存中，基于磁盘文件来排序，恰巧此时数据又非常大的话，那么速度肯定就很慢了

如果要使用到索引的话，可以按照我们之前说的联合索引的顺序来进行排序，因为联合索引在数据页中就根据数据的顺序进行了排序，这样能大大加快我们的查询速度，同时在使用order by的时候，不要使用复杂的函数，也不要升序降序一起使用

group by语句和order by语句一样，也要注意到这些点才能顺利使用到我们的联合索引

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xPX1lVTg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221024/9c11f1aea0504f90bea87f804ff2e467.png