[MySQL 优化] --order by 原理

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

一、简介:

大部分查询中都要使用到order by。那么处理排序的方法有两种:一种是使用索引,另外一种则是查询时候使用Filesort处理。

1. 利用索引进行排:

利用有序索引进行排序,当 Query ORDER BY 条件和 Query 的执行计划中所利用的 Index 的索引键完全一致,且索引访问方式为 rang、 ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。因为 MySQL 不需要进行实际的排序操作

2. 利用内存或者磁盘排序算法:

1. single pass
1. two pass  

优缺点对比

优点 缺点
使用索引 执行insert,update,delete查询时,索引已经排序好,只需要依次读取即可,处理速度快 1.处理insert,update,delete时需要额外添加索引,所以很慢 2.索引需要更多的磁盘空间需要合理的规划好 3.索引个数越多,越需要更多innodb缓冲池
使用Filesort 1.因不必创建索引,故无使用索引时那么多的缺点 2.若要排序的记录不多,在内存中进行Filesort处理,速度也非常快 1.因为要在执行查询时进行排序处理,目标的数据越多,则响应的速度会越慢

不能使用索引进行排序的场景:

  1. 排序的基准太多,无法依据某个基准创建索引
  2. 要对group by 的结果或者DISTINCT的结果进行排序时
  3. 对临时表的结果(union union all[5.7进行优化没有临时表的出现])重新排序时

二、 举例:

表结构:

5.7@3306>[employees]>show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.使用索引进行排序:

5.7@3306>[employees]>desc select emp_no,first_name, last_name from employees order by emp_no desc limit 0,10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------+

2.使用single pass:

5.7@3306>[employees]>show status like '%sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

5.7@3306>[employees]>select emp_no,first_name, last_name from employees order by first_name limit 100;
分析:将select的列,order 列放入到排序缓冲,进行排序处理,排序完成后,直接将排序缓冲中的内容返回。

5.7@3306>[employees]>show status like '%sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 100   |
| Sort_scan         | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

5.7@3306>[employees]>desc  select emp_no,first_name, last_name from employees order by first_name limit 100;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299423 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

那么这样会造成什么问题:IO,网络等

3.Two pass

5.7@3306>[employees]>show variables like "%max_length%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+
1 row in set (0.00 sec)
排序的值需要大于上面的值

5.7@3306>[employees]>show status like '%sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec) 

5.7@3306>[employees]> select count(1) from ( select * from employees order by first_name desc limit 100000000 ) a;  
+----------+
| count(1) |
+----------+
|   300024 |
+----------+
1 row in set (0.87 sec)

5.7@3306>[employees]>show status like '%sort%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Sort_merge_passes | 25     |
| Sort_range        | 0      |
| Sort_rows         | 300024 |
| Sort_scan         | 1      |
+-------------------+--------+
4 rows in set (0.00 sec)
解释:Sort_merge_passes  超出sort buffer的值,将数据写入到了tmp file中

三、 优化filesort:

优先选择第一种using index 的排序方式,在第一种方式无法满足的情况下,尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序工作的效率。
1、去掉不必要的字段
2、加大max_length_for_sort_data 参数的设置
3、增大sort buffer的设置

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
8天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
2天前
|
SQL 关系型数据库 MySQL
MySQL Online DDL原理解读
MySQL Online DDL原理解读
15 3
|
4天前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
7天前
|
缓存 关系型数据库 MySQL
如何优化MySQL 8.0的性能?
【6月更文挑战第14天】如何优化MySQL 8.0的性能?
29 5
|
5天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
9 2
|
9天前
|
缓存 监控 关系型数据库
mysql优化
【6月更文挑战第12天】mysql优化
18 3
|
8天前
|
存储 算法 关系型数据库
*MYSQL--索引--内部原理
*MYSQL--索引--内部原理
|
14天前
|
SQL 关系型数据库 MySQL
MySQL大数据量分页查询方法及其优化
MySQL大数据量分页查询方法及其优化
|
15天前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
226 1