MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(四)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(四)

⑤. filesort文件排序方式(了解)


①. 单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >


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


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


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


重复步骤 2、3 直到不满足name =‘zhuge’


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


返回结果给客户端


②. 双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID,然后在 sort buffer 中进行排序,排序完后需要再次取回其它需要的字段;用trace工具可以看到sort_mode信息里显示< sort_key, rowid >


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


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

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


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


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


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


③. MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。


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


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


④. 示例验证下各种排序方式:


微信图片_20220109130349.png


mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启trace
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果:
"join_execution": {    --Sql执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {                      --文件排序信息
              "rows": 10000,                           --预计扫描行数
              "examined_rows": 10000,                  --参与排序的行
              "number_of_tmp_files": 3,                --使用临时文件的个数,这个值如果为0代表全部使用的sort_buffer内存排序,否则使用的磁盘文件排序
              "sort_buffer_size": 262056,              --排序缓存的大小,单位Byte
              "sort_mode": "<sort_key, packed_additional_fields>"       --排序方式,这里用的单路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
mysql> set max_length_for_sort_data = 10;    --employees表所有字段长度总和肯定大于10字节
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果:
"join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 10000,
              "examined_rows": 10000,
              "number_of_tmp_files": 2,
              "sort_buffer_size": 262136,   
              "sort_mode": "<sort_key, rowid>"         --排序方式,这里用的双路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
mysql> set session optimizer_trace="enabled=off";    --关闭trace


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
19小时前
|
算法 关系型数据库 MySQL
MySQL (索引 & 事务)
MySQL (索引 & 事务)
9 3
|
1天前
|
存储 关系型数据库 MySQL
MySQL索引
MySQL索引
7 0
|
2天前
|
SQL 算法 关系型数据库
【MySQL】索引介绍、索引的数据结构
【MySQL】索引介绍、索引的数据结构
14 0
|
3天前
|
SQL 缓存 关系型数据库
下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
【5月更文挑战第20天】下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
24 3
|
5天前
|
存储 数据采集 关系型数据库
✅MySQL是如何保证唯一性索引的唯一性的?
MySQL使用B树实现唯一性索引,确保高效检索和插入。事务机制和锁定协议维护InnoDB存储引擎的唯一性。唯一索引可允许NULL值,且InnoDB允许多个NULL。唯一索引查询速度快,能提升数据质量,但插入和更新时需检查唯一性,可能影响性能。
|
4天前
|
关系型数据库 分布式数据库 数据库
【阿里云云原生专栏】云原生时代的数据库选型:阿里云RDS与PolarDB对比分析
【5月更文挑战第24天】阿里云提供RDS和PolarDB两种数据库服务。RDS是高性能的在线关系型数据库,支持MySQL等引擎,适合中小规模需求;而PolarDB是分布式数据库,具备高扩展性和性能,适用于大规模数据和高并发场景。RDS与PolarDB在架构、性能、弹性伸缩、成本等方面存在差异,开发者应根据具体需求选择。示例代码展示了如何通过CLI创建RDS和PolarDB实例。
299 0
|
6天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
36 2
|
6天前
|
存储 数据可视化 关系型数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
|
6天前
|
存储 SQL 关系型数据库
【MySQL】数据库基础 -- 详解
【MySQL】数据库基础 -- 详解
|
7天前
|
关系型数据库 MySQL 数据库
如何在MySQL中查看已创建的数据库列表?
【5月更文挑战第22天】如何在MySQL中查看已创建的数据库列表?
17 1