性能优化｜索引优化最佳实战(二)

2022-05-05 07:33:23

表初始化sql见 性能优化｜索引优化最佳实(一)

https://my-wechat.mdnice.com/fullstack-1.png); margin-bottom: -22px;">深入索引优化


https://my-wechat.mdnice.com/fullstack-2.png); background-position: center; background-size: 30px; margin: auto; opacity: 1; background-repeat: no-repeat; margin-bottom: -8px;">我们很好奇，mysql是如何选择合适的索引的？


其实在mysql底层，使用了一个trace的工具，帮助你分析每条SQL语句，它会按照索引去查询语句，预估消耗的时间，然后再按照全表扫描，预估消耗的时间，最后比较两者消耗时间，选择是否走索引还是全表扫描,执行如下sql,查看结果2，分析trace字段的结果：


https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">
SET SESSION optimizer_trace = "enabled=on",
end_markers_in_json = ON;

SELECT
* 
FROM
 student 
WHERE
 stu_age > 10 
ORDER BY
 stu_age;
SELECT
* 
FROM
 information_schema.OPTIMIZER_TRACE;

预估表的访问成本
性能优化｜索引优化最佳实战(二)


使用索引预估的访问成本


性能优化｜索引优化最佳实战(二)https://img.alicdn.com/tfs/TB1Yycwyrj1gK0jSZFuXXcrHpXa-32-32.png); display: inline-block; width: 18px; height: 18px; background-size: 18px; background-repeat: no-repeat; background-position: center; margin-right: 5px; margin-bottom: -5px;">在这里插入图片描述

比较成本大小，则决定使用索引查询。


https://my-wechat.mdnice.com/fullstack-2.png); background-position: center; background-size: 30px; margin: auto; opacity: 1; background-repeat: no-repeat; margin-bottom: -8px;">order by优化



  • SQL1

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '刘备' 
ORDER BY
 stu_addr;

性能优化｜索引优化最佳实战(二)https://img.alicdn.com/tfs/TB1Yycwyrj1gK0jSZFuXXcrHpXa-32-32.png); display: inline-block; width: 18px; height: 18px; background-size: 18px; background-repeat: no-repeat; background-position: center; margin-right: 5px; margin-bottom: -5px;">在这里插入图片描述

执行计划发现使用了文件排序 using filesort，原因是由于where 后面的语句过滤字段和排序字段中间跳过了age字段，索引排序是在文件中排序。



  • SQL2

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '刘备' and stu_age =  16
ORDER BY
 stu_addr;

性能优化｜索引优化最佳实战(二)https://img.alicdn.com/tfs/TB1Yycwyrj1gK0jSZFuXXcrHpXa-32-32.png); display: inline-block; width: 18px; height: 18px; background-size: 18px; background-repeat: no-repeat; background-position: center; margin-right: 5px; margin-bottom: -5px;">在这里插入图片描述

分析执行计划，发现using filesort没有了，因为满足了最左前缀原则了。加上了stu_age字段，直接可以在索引中完成排序。



  • SQL3

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '刘备'
ORDER BY
 stu_addr,stu_age

性能优化｜索引优化最佳实战(二)https://img.alicdn.com/tfs/TB1Yycwyrj1gK0jSZFuXXcrHpXa-32-32.png); display: inline-block; width: 18px; height: 18px; background-size: 18px; background-repeat: no-repeat; background-position: center; margin-right: 5px; margin-bottom: -5px;">在这里插入图片描述

执行计划中出现了filesort,原因是排序字段与索引建立的顺序不一致，不满足最左前缀法则，mysql没有使用索引进行排序。



  • SQL4

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">explain 
SELECT
*
FROM
 student 
WHERE
 stu_name = '刘备'
ORDER BY
 stu_age, stu_addr desc

分析执行计划，发现又出现了using filesort，使用了文件排序，按照排序确实按照最左前缀了啊，不应该啊。仔细观察发现。两个字段的排序方向不一致，这会导致索引无法进行排序，在索引树中：只能使用排序方向一致的索引，否则就需要使用文件排序了。



  • SQL5

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">explain 
SELECT
*
FROM
 student 
WHERE
 stu_name in('刘备','张飞')
ORDER BY
 stu_age

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P8CAbS5j-1603062000913)(media/16030236994724/16030326775297.jpg)]
执行计划发现明明按照最左前缀的顺序查询和排序的，为什么又出现了文件排序呢？
因为in查询属于范围查询，会导致右侧所有索引失效。



  • order by 总结：

    • 排序字段尽量是索引字段
    • 尽量使用索引覆盖
    • where字段和排序字段遵循最左前缀
    • 出现filesort ，尽量优化成 using index，在索引中排序肯定比使用文件排序要快得多。
    • group by 和order by 优化点是一样的，默认会按照字段排序的，如果不需要排序 则可以在group by 之后加上order by null，省去排序的消耗。


https://my-wechat.mdnice.com/fullstack-2.png); background-position: center; background-size: 30px; margin: auto; opacity: 1; background-repeat: no-repeat; margin-bottom: -8px;">文件排序 using filesort


在上面优化中，发现在mysql中排序分为文件排序和索引排序，在无法使用索引排序的情况下，我们就得考虑如何优化文件排序了。
文件排序分为两种：



  • 单路排序
    单路排序是一次性取出所有满足条件的数据的所有字段，保存在sort buffer中，然后在sort buffer排序完成之后，直接返回结果。
  • 双路排序
    双路排序是取出排序的字段和能够定位数据的行标识，保存在sort_buffer中，完成排序后，再按照行标识按顺序从文件中取出需要查询的字段，这个比单路排序多了一步操作。

https://my-wechat.mdnice.com/fullstack-3.png); display: inline-block; width: 16px; height: 16px; background-size: 100%; background-position: left bottom; background-repeat: no-repeat; width: 16px; height: 15px; line-height: 15px; margin-right: 6px; margin-bottom: -2px;">单路排序和双路排序分别在什么情况下使用呢？



  • 如果查询的字段比sort buffer 空间要大(大小由系统变量max_length_for_sort_data来决定的，默认为1024字节)，则使用双路排序，因为一次性无法从文件中取出所有字段放在sort buffer中。
  • 相反，如果查询的字段总长度比sort buffer 空间小，则使用单路排序。

https://my-wechat.mdnice.com/fullstack-3.png); display: inline-block; width: 16px; height: 16px; background-size: 100%; background-position: left bottom; background-repeat: no-repeat; width: 16px; height: 15px; line-height: 15px; margin-right: 6px; margin-bottom: -2px;">如何区分执行器使用的是单路排序还是双路排序呢？


我们可以使用上面提到的trace工具来分析：


https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">
SET SESSION optimizer_trace = "enabled=on",
end_markers_in_json = ON;

SELECT
*
FROM
 student 
WHERE
 stu_name ='a'
ORDER BY
 stu_addr,stu_age;
SELECT
* 
FROM
 information_schema.OPTIMIZER_TRACE;

https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">   {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "student",
                "field": "stu_addr"
              },
              {
                "direction": "asc",
                "table": "student",
                "field": "stu_age"
              }
            ] / filesort_information /,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } / filesort_priority_queue_optimization /,
            "filesort_execution": [
            ] / filesort_execution /,
            "filesort_summary": {
              "rows": 0,
              "examined_rows": 0,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 204800 // sort buffer大小
              "sort_mode": "<sort_key, packed_additional_fields>" // 单路排序
            } / filesort_summary /
          }
        ] / steps /
      } / join_execution /
    }

分析trace结果中主要的字段：
sort_buffer_size：204800 mysql设置的默认值为1m
"sort_mode": "<sort_key, packed_additional_fields>" 单路排序
因为我们查询的字段长度肯定是小于1m的，我们现在设置下sort_buffer_size大小，然后再trace下结果：


https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">// 修改默认值，记得测试完之后修改回默认值（1m）
 set max_length_for_sort_data = 10;

再执行上面的语句，查看结果为：


https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;">       "filesort_summary": {
              "rows": 0,
              "examined_rows": 0,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 57344,
              "sort_mode": "<sort_key, rowid>" // 双路排序 因为包含了rowid,需要根据rowid再去提取查询的字段。
            } / filesort_summary /

sort_mode 字段中包含了rowid，所以排序模式已经更改为双路排序了。
// 设置回原来的值
set max_length_for_sort_data = 1024;


❝

微信搜一搜【乐哉开讲】关注帅气的我，回复【干货领取】，将会有大量面试资料和架构师必看书籍等你挑选，包括java基础、java并发、微服务、中间件等更多资料等你来取哦。


❞

本文使用 mdnice 排版

上一篇：SQL Serever学习12——数据库的备份和还原


下一篇：源码解析｜第一次有人把HashMap说的这么清楚~