本网站(662p.com)打包出售,且带程序代码数据,662p.com域名,程序内核采用TP框架开发,需要联系扣扣:2360248666 /wx:lianweikj
精品域名一口价出售:1y1m.com(350元) ,6b7b.com(400元) , 5k5j.com(380元) , yayj.com(1800元), jiongzhun.com(1000元) , niuzen.com(2800元) , zennei.com(5000元)
需要联系扣扣:2360248666 /wx:lianweikj
MySQL Order BY 排序过程
yuanzun168 · 447浏览 · 发布于2021-03-26 +关注

MySQL 在进行 Order By 操作排序时,通常有两种排序方式:

  • 全字段排序

  • Row_id 排序

MySQL 中每个线程在执行排序时,都会被分配一块区域 - sort buffer,它的大小通过 sort_buffer_size 控制。

全字段排序指的是,将要查询的字段,全都存入 sort buffer 中,然后对 sort buffer 进行排序,然后将结果返回给客户端。

Row_id 排序:将被排序的字段和对应主键索引的 ID 放入,sort buffer 中,然后对 sort buffer 进行排序,最后额外进行一次回表操作查找额外的信息,然后将结果返回给客户端。

全字段排序和 Row_id 排序的主要区别在:

  1. sort buffer 存入的内容不同

  2. 回表查询的次数不一致。

对于 InnoDB 表来说,在内存足够的情况下,会优先选择全字段排序的方式。在内存不足的情况下,可能会借用外部文件进行排序。

但如果单行内容较大时,会导致拆分的外部文件过多,进行归并排序时,效率变低。此时会采用 Row_id 的排序方式。

对于 Memory 表来说,会优先选择 Row_id 的排序方式。

接下来会对全字段排序和 Row_id 排序进行验证,最后并给出一些调优的技巧。

环境准备#

假设存在如下表结构,表里有 5万的数据行, 其中 type 为二级索引。

 

# MySQL5.7.28, RR
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `type` varchar(16) NOT NULL,
  `phone` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB;

向表中插入数据:

import random
import MySQLdb

def prepare_data():
    result = []
    type = ["a", "b", "c", "d", "e"]

    for i in range(50000):
        index = random.randint(0, 4)
        result.append((str(i), str(type[index]), str(i + 10), str(i)))
    return result

def insert_data():
    db = MySQLdb.connect(host='10.124.207.xxx',
                         user='xxxx',
                         passwd='xxxxx',
                         db='usecase',
                         charset='utf8')
    sql = 'INSERT INTO test_table ( name, type, phone, addr) VALUES ( %s, %s, %s, %s);'
    cur = db.cursor()
    cur.executemany(sql, prepare_data())
    db.commit()
    db.close()

if __name__ == '__main__':
    try:
        insert_data()
    except Exception as e:

下面会进行查询操作:

``select name,type,phone from test_table where type='a' order by name limit 1000 ;`

并对排序的过程进行分析。

全字段排序#

这里对该查询语句进行了 EXPLAIN 操作,可以看到在 Extra 列 :

  • 用到了索引

  • 进行了排序操作 - filesort (无法利用索引默认有序的情况)

针对本次 SELECT 来说,经历如下的过程:

  1. 使用 type = a 的二级索引,找到满足的第一个值。

  2. 根据该值,找到主键 ID。回表去找 name 和 phone 的值。

  3. 然后将 type,name, phone 存入 sort_buffer。

  4. 然后重复 1 - 4 过程,查询所有 type =a 的内容,然后将 name, phone, type 存入 sort_buffer。

  5. 然后对分配内存里的信息进行排序。

  6. 然后选择前 1000 条,返回给客户端。

打开 optimizer_trace ,查看执行的流程:

可以看到,对应 "sort_mode": 中的内容为 sort_key 和其他字段,这就表示用的是全字段排序。

chosen: true 表示使用的是优先队列的排序算法。

但在实际情况下,往往会出现待排序的内容大于分配用于排序的空间,此时就会用到外部的文件排序,而这种外部排序一般都使用归并排序。

将 sort_buffer 调小,重新执行:

将默认大小临时改小:

# 将 size 调小, 并重新登录
set sort_buffer_size= 24 * 1024;

/* 打开optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select type,name,phone from test_table where type='a' order by name limit 1000 ;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

在 OPTIMIZER_TRACE 中查看:

number_of_tmp_files 表示使用的临时文件数为 20,上面的 chosen 表示由于空间不足,无法使用堆排序。

可以发现:

  • 当 sort_buffer_size 大于要待排序的内容,则使用内存排序。

  • 如果小于,则使用外部临时文件辅助排序。

但还有一种情况,就是待排序的内容数据量太大或者单行查询的字段太多(如 SELECT *)这种情况,会导致生成的临时文件数量太多,效率不高。所以就出现了另一种 Row_id 的排序方式。

Row_id 排序#

在单行查询字段很多时,在 sort_buffer 中仅仅保存必要的字段,最后额外再进行一次统一回表的操作,查询必要的信息。

这里可以将 SET max_length_for_sort_data = 16; 改小,模拟这种情况。

这里 sort_mode: sort_key, rowid 指的就是用的 Row_id 这种处理方式。

对应经历的过程就会变成:

  1. 使用 type = a 的二级索引,找到满足的第一个值。

  2. 根据该值,找到主键 ID。回表去找 row_id 的值。(发生变化)

  3. 然后将 type, row_id 存入 sort_buffer。(发生变化)

  4. 然后重复 1 - 4 过程,查询所有 type = a 的内容,然后将 type, row_id 存入 sort_buffer。(发生变化)

  5. 然后对分配内存里的信息进行排序。

  6. 然后选择前 1000 条,并取到需要的 row_id 集合。

  7. 根据 row_id,回表查询所需要的信息,然后返回给客户端。(发生变化)

这里 2, 3, 4, 7 和之前全字段排序相比,发生了变化。

但需要注意的是,在内存足够的情况下,InooDB 会优先选择全排序的方式。但对于 Memory 方式的表结构,则会有不同的选择。

内存临时表的排序选择#

在如使用 Union 或者 Group By 等查询的情况下,会创建临时表,采用 Memory 作为存储的引擎。

而对于内存临时表,会优先采用 row_id 排序。

因为内存临时表,本身会在原表基础上,新建一张临时表保存需要的信息。因为临时表本身就在内存中,所以最后一次回表的操作,不会进行额外的磁盘 IO。所以 MySQL 会优先选择 row_id 的排序方式。

优化方法#

场景1:利用索引有序,让 Order by 不排序#

之前仅仅有 type 类型的索引,可以将其改成 type, name 的联合索引:

alter table test_table add index type_name(type, name);

在创建联合索引时,因为本身有序。在查询 typem,name,phone 时,会将 sort buffer 中的排序过程省略,也就是全排序过程中的第五步。

可以看到,在 Extra 中 filesort 的过程,已经被省略了。

场景2:利用覆盖索引,简化回表流程#

alter table test_table add index type_name(type, name, phone);

这里由于想要查询的字段,都已经在二级索引上了。所以不需要进行回表,而且本身也是有序的。

并且可以发现,Extra 变成了 Using index. 表示直接使用了索引。

场景3:IN 导致 Order By 需要排序#

假设存在 type_name(type, name); 联合索引。

将之前的 type = 换成了 IN,由于是对 a 和 b 两个类型同时排序,所以就需要 filesort 操作。

如果不想让 MySQL 进行排序的操作,可以将 IN 拆分成多个 = 执行,然后在调用端,自己进行合并。

总结#

在 MySQL 中,使用 Order BY 时,通常有全字段排序和 Row_id 两种排序方式。

对于 InooDB 来说,在内存足够的情况下,会优先选择全字段排序。在内存不足,并所需排序内容不多时,会采用外部归并排序的方式。

但如果所在单行内容太大,导致拆分文件过多的情况下,会选择 Row_id 的排序方式。

对于 Memory 表来说,由于本身就在内存中,所以会优先选择 Row_id 的排序方式。

使用 Order By 操作时,不一定真的意味着真的去做排序,可以利用索引本身有序,或者覆盖索引,拆分 SQL 的方式,减少 MySQL 的排序过程。


相关推荐

PHP实现部分字符隐藏

沙雕mars · 1312浏览 · 2019-04-28 09:47:56
Java中ArrayList和LinkedList区别

kenrry1992 · 896浏览 · 2019-05-08 21:14:54
Tomcat 下载及安装配置

manongba · 957浏览 · 2019-05-13 21:03:56
JAVA变量介绍

manongba · 953浏览 · 2019-05-13 21:05:52
什么是SpringBoot

iamitnan · 1076浏览 · 2019-05-14 22:20:36
加载中

0评论

评论
欢迎大家关注我的个人主页!!!
分类专栏
小鸟云服务器
扫码进入手机网页