8.2 order by文件排序效率为什么较低

order by用到文件排序时，为什么查询效率会相对低呢？

MySQL语句 desc Mysql语句双字段排序很慢_MySQL语句 desc

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。

rowid排序

rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个SQL，使用rowid排序，执行过程是这样：

select name,age,city from staff where city = '深圳' order by age limit 10;
  1. MySQL 为对应的线程初始化sort_buffer，放入需要排序的age字段，以及主键id；
  2. 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键id，也就是图中的id=9；
  3. 到主键id索引树拿到id=9的这一行数据， 取age和主键id的值，存到sort_buffer；
  4. 从索引树idx_city拿到下一个记录的主键id，即图中的id=13；
  5. 重复步骤 3、4 直到city的值不等于深圳为止；
  6. 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
  7. 遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name 和 age三个字段返回给客户端。

MySQL语句 desc Mysql语句双字段排序很慢_sql_02

全字段排序

同样的SQL，如果是走全字段排序是这样的：

select name,age,city from staff where city = '深圳' order by age limit 10;
  1. MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
  2. 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键 id，也就是图中的id=9；
  3. 到主键id索引树拿到id=9的这一行数据， 取name、age、city三个字段的值，存到sort_buffer；
  4. 从索引树idx_city 拿到下一个记录的主键id，即图中的id=13；
  5. 重复步骤 3、4 直到city的值不等于深圳为止；
  6. 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
  7. 按照排序结果取前10行返回给客户端。

MySQL语句 desc Mysql语句双字段排序很慢_sql_03

sort_buffer的大小是由一个参数控制的：sort_buffer_size。

  • 如果要排序的数据小于sort_buffer_size，排序在sort_buffer内存中完成
  • 如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序。

借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。

8.3 如何优化order by的文件排序

order by使用文件排序，效率会低一点。我们怎么优化呢？

  • 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化order by语句。
  • 我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化；

9. 拿不到锁

有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。

举一个生活的例子哈，你和别人合租了一间房子，这个房子只有一个卫生间的话。假设某一时刻，你们都想去卫生间，但是对方比你早了一点点。那么此时你只能等对方出来后才能进去。

这时候，我们可以用show processlist命令，

看看当前语句处于什么状态哈。

10. delete + in子查询不走索引！

之前见到过一个生产慢SQL问题，当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。

MySQL版本是5.7，假设当前有两张表account和old_account,表结构如下：

CREATE TABLE `old_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

执行的SQL如下：

delete from account where name in (select name from old_account);

查看执行计划，发现不走索引：

MySQL语句 desc Mysql语句双字段排序很慢_mysql_04

但是如果把delete换成select，就会走索引。如下：

MySQL语句 desc Mysql语句双字段排序很慢_数据库_05

为什么select + in子查询会走索引，delete + in子查询却不会走索引呢？

我们执行以下SQL看看：

explain select * from account where name in (select name from old_account);
show WARNINGS; //可以查看优化后,最终执行的sql

结果如下：

select `test2`.`account`.`id` AS `id`,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account` 
semi join (`test2`.`old_account`)
where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)

可以发现，实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化。

11、group by使用临时表

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。

11.1 group by的执行流程

假设有表结构：

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

我们查看一下这个SQL的执行计划：

explain select city ,count(*) as num from staff group by city;

MySQL语句 desc Mysql语句双字段排序很慢_数据库_06

  • Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表
  • Extra 这个字段的Using filesort表示使用了文件排序

group by是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程

select city ,count(*) as num from staff group by city;
  1. 创建内存临时表，表里有两个字段city和num；
  2. 全表扫描staff的记录，依次取出city = 'X'的记录。
  • 判断临时表中是否有为 city='X'的行，没有就插入一个记录 (X,1);
  • 如果临时表中有city='X'的行，就将X这一行的num值加 1；
  1. 遍历完成后，再根据字段city做排序，得到结果集返回给客户端。这个流程的执行图如下：

MySQL语句 desc Mysql语句双字段排序很慢_数据库_07

临时表的排序是怎样的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和rowid排序

  • 如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回
  • 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

11.2  group by可能会慢在哪里？

group by使用不当，很容易就会产生慢SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

  • 如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
  • 如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

11.3 如何优化group by呢？

从哪些方向去优化呢？

  • 方向1：既然它默认会排序，我们不给它排是不是就行啦。
  • 方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？

我们一起来想下，执行group by语句为什么需要临时表呢？group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?

可以有这些优化方案：

  • group by 后面的字段加索引
  • order by null 不用排序
  • 尽量只使用内存临时表
  • 使用SQL_BIG_RESULT

12. 系统硬件或网络资源

  • 如果数据库服务器内存、硬件资源，或者网络资源配置不是很好，就会慢一些哈。这时候可以升级配置。这就好比你的计算机有时候很卡，你可以加个内存条什么的一个道理。
  • 如果数据库压力本身很大，比如高并发场景下，大量请求到数据库来，数据库服务器CPU占用很高或者IO利用率很高，这种情况下所有语句的执行都有可能变慢的哈。

最后

如果测试环境数据库的一些参数配置，和生产环境参数配置不一致的话，也容易产生慢SQL哈。之前见过一个慢SQL的生产案例，就是测试环境用了index merge，所以查看explain执行计划时，是可以走索引的，但是到了生产，却全表扫描，最后排查发现是生产环境配置把index merge关闭了。大家是否还遇到其他场景的慢SQL呢？如果有的话，欢迎评论区留言交流哈