首页  思维笔记  详情

5-04 MySQL索引优化实战一

2022-12-13 00:28:02   0  举报





仅支持查看

04 Mysql索引优化实战一

MySQL

作者其他创作

大纲/内容

实战应用示例表（以下所有示例使用此表）

1. 创建示例表

2. 手动插入一些数据

3. 通过存储过程插入海量数据

难以理解的综合示例

1、联合索引第一个字段用范围不会走索引

如下所示：

结论：联合索引第一个字段就用范围查找不会走索引，MySQL内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

2、强制走索引

如下所示：

与默认的联合索引第一个字段用范围不会走索引对比：

如下所示：

由上可看出，强制走索引后预估的扫描行数相对未走索引的少了点儿

但根据实际的执行时间比较，强制走索引查询耗时比较多，因为走索引需要一个回表操作，回表效率不高，影响了整体查询效率

3、覆盖索引优化

如下所示：

结论：针对以上范围查询不走索引情况，可以采用覆盖索引进行优化

4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

如下所示1：（数据量比较大时，in查询走索引）

如下所示2：（数据量比较小时，in查询全表扫描）

如下所示3：（数据量比较大时，or查询走索引）

如下所示4：（数据量比较小时，or查询全表扫描）

结论：在数据量比较大时，in或or查询，全表扫描元素量较大，效率不如走索引；反之，数据量小时，全表扫描少了一步回表操作，相对比走索引效率高

5、like KK% 一般情况都会走索引

如下所示1：（大数据量时，走索引）

如下所示2：（小数据量时，走索引）

这里需要补充一个概念，索引下推（Index Condition Pushdown，ICP）, like KK%其实就是用到了索引下推优化

索引下推（Index Condition Pushdown，ICP）

什么是索引下推了？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则

如上述 like KK% 语句为例，这种情况只会走name字段索引

因为根据name字段过滤完，得到的索引行里的 age 和 position 是无序的，无法很好的利用索引

在MySQL5.6之前的版本，这个查询：

1）只能在联合索引里匹配到名字是 'LiLei' 开头的索引

2）然后拿这些索引对应的主键逐个回表

3）到主键索引上找出相应的记录

4）再比对age和position这两个字段的值是否符合

MySQL 5.6引入了索引下推优化

索引下推：可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

使用了索引下推优化后，上面那个查询：

1）在联合索引里匹配到名字是 'LiLei' 开头的索引之后

2）同时还会在索引里过滤age和position这两个字段

3）拿着过滤完剩下的索引对应的主键id再回表查整行数据

索引下推会减少回表次数

对于innodb引擎的表索引下推只能用于二级索引

innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果

为什么范围查找MySQL没有用索引下推优化？

估计应该是MySQL认为范围查找过滤的结果集过大

like KK% 在绝大多数情况来看，过滤后的结果集比较小（当然这也不是绝对的，有时like KK% 也不一定就会走索引下推）

MySQL如何选择合适的索引

如下所示1：（范围查询，未走索引）

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高

可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果

如下所示2：（范围查询，使用覆盖索引优化）

如下所示3：（范围查询，修改查询条件，走了索引）

对于上面这两种 name>'a' 和 name>'zzz' 的执行结果：

MySQL最终是否选择走索引或者一张表涉及多个索引，MySQL最终如何选择索引？

可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

trace工具用法

1. 开启trace：

如下所示：(此工具会影响mysql性能，用完记得关哦)

2-1. 执行查询语句并查看trace信息1：

如下所示：(查询name > 'a'，注意查看trace需要与查询语句一起执行才会生成trace内容)

trace信息分析：(使用Json工具查看)

结论：全表扫描的成本低于索引扫描，所以MySQL最终选择“全表扫描”

2-2.  执行查询语句并查看trace信息2：

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

如下所示：(查询name > 'zzz'，注意查看trace需要与查询语句一起执行才会生成trace内容)

trace信息分析：(同上2-1内容结构，此处略。。)

结论：可知索引扫描的成本低于全表扫描，所以mysql最终选择“索引扫描”

3. 关闭trace：

如下所示：

常见SQL深入优化

Order by 与 Group by优化

示例1：

如下所示：（查找用到了name索引和position索引，order by 排序使用了age索引）

分析：

利用最左前缀法则：中间字段不能断，因此查询用到了name索引

从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

示例2：

如下所示：（查找使用了name索引，order by 排序未使用索引，因跳过了联合索引的age索引字段）

分析：

从 key_len=74 能看出，查询使用了name索引

由于用了position进行排序，跳过了age，出现了 Using filesort

示例3：

如下所示：（查找使用了name索引，order by 排序使用了 age 索引和 position 索引）

分析：

查找只用到索引name，age和position用于排序，无Using filesort

示例4：

如下所示：（查找使用了name索引，order by 排序未使用索引，因排序字段顺序与索引字段顺序不一致）

分析：

和Case 3中explain的执行结果一样，但是出现了Using filesort

因为索引的创建顺序为name、age、position，但是排序的age和position顺序颠倒了

示例5：

如下所示：（查询使用了name索引和age索引，在排序中被优化，排序使用了索引）

分析：

与Case 4对比，在Extra中并未出现 Using filesort

因为age为常量，在排序中被优化，所以索引未颠倒，不会出现 Using filesort

示例6：

如下所示：（查找使用了name索引，排序中position使用降序，导致与索引方式不一致，未使用到索引）

分析：

虽然排序的字段列与索引顺序一样，且 order by 默认升序

这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort

MySQL 8.0 以上版本有降序索引可以支持该种查询方式

示例7：

如下所示：（查找使用了name索引，但排序未使用索引）

分析：

对于排序来说，多个相等条件也是范围查询

示例8：

如下所示1：（使用范围查询，查找和排序都未使用索引）

如下所示2：（使用覆盖索引优化后，查找和排序都使用了索引）

分析：

可以用覆盖索引优化

优化总结：

1、MySQL支持两种方式

Using filesort排序：

1）先将要排序的数据分割，分割成每块数据都可以放到 sort_buffer 中

2）对每块数据在 sort_buffer 中进行排序，排序好后，写入某个临时文件中

3）当所有的数据都写入临时文件后，这时对于每个临时文件而言，内部都是有序的，但是它们并不是一个整体，整体还不是有序的，所以接下来就得合并数据

4）假设现在存在 tmpX 和 tmpY 两个临时文件，这时会从 tmpX 读取一部分数据进入内存，然后从 tmpY 中读取一部分数据进入内存。为什么是一部分而不是整个或者单个？

因为首先磁盘是缓慢的，所以尽量每次多读点数据进入内存，但是不能读太多，因为还有 buffer 空间的限制

Using index排序

是指MySQL扫描索引本身完成排序

Using index效率高，Using filesort 效率低

2、order by满足两种情况会使用Using index

1) order by语句使用索引最左前列

2) 使用where子句与order by子句条件列组合满足索引最左前列

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则

4、如果order by的条件不在索引列上，就会产生Using filesort

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似

其实质是先排序后分组，遵照索引创建顺序的最左前缀法则

对于groupby的优化如果不需要排序的可以加上order by null禁止排序

注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

filesort文件排序方式

单路排序：

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

双路排序（又叫回表排序模式）：

首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID

然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

filesort如何判断使用哪种排序模式？

MySQL 通过比较系统变量 max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小

如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序 模式

如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序 模式

“单路排序”与“双路排序”示例：

1）查询排序语句：（排序未使用索引，使用了filesort）

2）开启trace工具

如下所示：(用完记得及时关哦)

3-1）执行查询语句与查询trace信息1（使用单路排序 模式）

如下所示：（默认max_length_for_sort_data=1024）

查看trace内容：（只展示排序部分，trace信息使用Json工具查看）

结论：字段的总长度小于max_length_for_sort_data，使用 单路排序 模式

3-2）执行查询语句与查询trace信息2（使用双路排序模式）

如下所示1：（设置set max_length_for_sort_data = 10，employees表所有字段长度总和肯定大于10字节）

如下所示2：（查询trace结果）

查看trace内容：（只展示排序部分，trace信息使用Json工具查看）

结论：字段的总长度大于max_length_for_sort_data，使用 双路排序 模式

4）关闭trace工具

如下所示：

单路排序的详细过程：

1）从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id

2）根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中

3）从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id

4）重复步骤 2、3 直到 name != ‘zhuge’

5）对 sort_buffer 中的数据按照字段 position 进行排序

6）返回结果给客户端

双路排序的详细过程：

1）从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id

2）根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中

3）从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id

4）重复 3、4 直到不满足 name = ‘zhuge’

5）对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序

6）遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

单路排序与双路排序对比：

单路排序：会把所有需要查询的字段都放到 sort buffer 中进行排序

双路排序：只会把主键和需要排序的字段放到 sort buffer 中进行排序，再通过主键回到原表查询需要的字段

如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了

可以适当把max_length_for_sort_data 配置小点

让优化器选择使用双路排序算法

可以在sort_buffer 中一次排序更多的行

只是需要再根据主键回到原表取数据

如果 MySQL 排序内存有条件可以配置比较大

可以适当增大 max_length_for_sort_data 的值

让优化器优先选择全字段排序（单路排序）

把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果

所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率

注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整

索引设计原则

1. 代码先行，索引后上

一般应该等到主体业务功能开发完毕，把涉及到的相关SQL都拿出来分析之后再建立索引

2. 联合索引尽量覆盖条件

尽量少建单值索引，可以设计两三个联合索引

每个联合索引都尽量去包含SQL语句里的 where、order by、group by 的字段

同时，还要确保这些联合索引的字段顺序尽量满足SQL查询的最左前缀原则

3. 不要在小基数字段上建立索引

索引基数：是指这个字段在表里总共有多少个不同的值

如果对小基数字段建立索引的话，查找效率还不如全表扫描高。因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了

建议尽量使用那些基数比较大的字段（就是值比较多的字段）建立索引，这样才能发挥出B+树快速二分查找的优势

4. 长字符串我们可以采用前缀索引

1）尽量对字段类型较小的列设计索引，比如说什么tinyint之类的。因为字段类型较小的话，占用磁盘空间比较小，查找性能也会比较好一点

2）针对字段类型较大的列建立索引，比如说varchar(255)之类的。因为字段类型较大的话，可能会比较占用磁盘空间，查找性能也会比较差一点

可以稍微优化下，对这个字段的前20个字符建立索引（就是说，将这个字段里每个值的前20个字符放在索引树里），类似于 KEY index(name(20), age, position)

但是，如果应用于order by name，因索引树仅包含前20个字符，所以排序无法使用索引，group by 同理

5. where与order by冲突时，优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？

一般建议，让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序

因为大多数情况，基于索引进行where筛选往往可以最快速度筛选出你要的数据且数据量不对太大，然后做排序的成本可能会小很多

6. 基于慢sql查询做优化

可以根据监控后台的一些慢SQL，针对这些慢sql查询做特定的索引优化

关于慢SQL查询可以参考这篇文章：<a class="link" target="_bank" href="https://blog.csdn.net/qq_40884473/article/details/89455740">https://blog.csdn.net/qq_40884473/article/details/89455740</a>

索引设计实战

以社交场景APP来举例：

1. 一般搜索一些好友，用户数据量大，就需要对用户信息进行筛选及排序：（先不考虑分库分表情况）

地区(省市)

性别

年龄

身高

爱好

评分

...

2. 对于后台程序，除了过滤以上条件，还可能需要分页处理，如：

select xx from user where xx=xx and xx=xx order by xx limit xx,xx

3. 这种情况如何合理设计索引呢？是否应该设计一个联合索引呢？

从筛选条件可看出，这些字段基数都不大

但应该给这些字段设计联合索引(province,city,sex)，因为这些信息字段查询太频繁了

4. 假如，新增需求1：用户需要进行年龄范围的筛选：

根据综上所学，这种范围查找的条件一般都要放在最后 (province,city,sex,age)，因为联合索引范围之后条件是不能用索引的

但是当前我用不到sex性别筛选，会影响后边范围索引的使用，该怎么优化呢？

可以这么来优化，因为sex的字段基数比较小，我们在查询时包含它所有的值，如下写法：

where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx

对于爱好之类的字段也可以类似于sex字段的处理(province,city,sex,hobby,age)

4. 假如，新增需求2：用户需要筛选最近一周登录过的用户，希望能与活跃用户交友：

后台SQL可能这样写：where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx and latest_login_time>= xx

但显然，latest_login_time 它不能使用索引，其实这里可以通过新增一个最近7天登录的标识字段

如果一周内登录，值就为1，否则为0

于是我们就可以把索引设计成(province,city,sex,hobby,is_login_in_latest_7_days,age)，解决以上不能使用索引问题

5. 实际应用场景中：

1）一般情况下，通过这么多字段的索引过滤后的数据量不会太多，接下来再进行order by语句排序、limit 进行分页，性能还是比较高的

2）特殊情况下，用户只筛选sex，如：where sex = 'female' orderby score limit xx,xx，那么上边的索引就用不上了，也不能把所有字段类似上班sex的方式用in语句拼接到SQL里。该怎么办呢？

其实我们可以再设计一个辅助的联合索引，如： (sex,score)，这样就能满足要求了

综上所述，核心思想就是：

1. 尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询

2. 然后用一两个辅助索引尽量抗下剩余的一些非典型查询

保证这种大数据量表的查询，尽可能多的都能充分利用索引，这样就能保证查询速度和性能了

 收藏

立即使用

5-06 深入理解MySQL事务隔离级别与锁机制

 收藏

立即使用

5-07 深入理解MVCC与BufferPool缓存机制【undo日志版本链&BufferPool缓存机制】

 收藏

立即使用

5-07 深入理解MVCC与BufferPool缓存机制

 收藏

立即使用

01 多线程【线程生命周期】

转角遇见

职业：暂无

去主页





0 条评论

下一页

为你推荐

查看更多

