order by原理

mysql排序主要是先把数据筛选出来，再放到内存/临时文件中进行排序。

示例table

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

全字段排序

查询sql 如下

1	select city,name,age from t where city='杭州' order by name limit 1000 ;

在 city 字段上创建索引之后，我们用 explain 命令来看看这个语句的执行情况。

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

通常情况下，这个语句执行流程如下所示：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

**sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。**如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

**number_of_tmp_files 表示的是，排序过程中使用的临时文件数。**内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

rowid排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。

它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

city、name、age 这三个字段的定义总长度是 36，我把 max_length_for_sort_data 设置为 16，就会使用rowid排序，流程如图。多了一个回表的操作

利用索引优化排序

引入 (city,name) 联合索引后，查询语句的执行计划

用explain，可以发现已经没有用 file sort了。

利用覆盖索引优化排序

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

1	alter table t add index city_user_age(city, name, age);

Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。

使用索引优化排序的时候，一定要注意查询条件，和排序的字段，要满足最左匹配原则。

索引失效排查

背景

最近项目在做性能测试，返现一个慢sql的问题。

性能测试反馈如下sql存在性能问题

主要为sql数据查询耗时，
SQL1：select * from wecall_task_call where account_key = "xingnengyace1" and task_id = 2041 and call_status = 1 order by call_time DESC limit 30; 耗时8ms
SQL2：select * from wecall_task_call where account_key = "xingnengyace1" and task_id = 2041 and call_status = 0 order by call_time DESC limit 30; 耗时18288ms

SQL1、SQL2走索引KEY `IDX_CALL` (`account_key`,`call_time`)
SQL1查询条件call_status = 1命中120000个数据，取30条，很快
SQL2无命中数据，查询account_key = "xingnengyace1"下所有数据（等于全表查询）很慢

业务表简化之后结构如下图

CREATE TABLE `wecall_task_call_v1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `account_key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'account_key',
  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '任务id',
  `cid` bigint(20) NOT NULL DEFAULT '0' COMMENT '客户id',
  `call_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '唯一标识用户通话id',
  `call_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '呼叫状态 0待呼叫 1已呼叫',
  `import_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '导入时间',
  `call_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '通话时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `IDX_TASK_CALLID` (`account_key`,`call_id`) USING BTREE,
  KEY `IDX_TASK_CID` (`account_key`,`task_id`,`cid`) USING BTREE,
  KEY `IDX_CALL` (`account_key`,`call_time`) USING BTREE,
  KEY `IDX_TASK_STATUS_TIME` (`account_key`,`task_id`,`call_status`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=142607 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通话详情表'

第一次处理（修改索引）

原表中是存在IDX_TASK_STATUS_TIME索引，但是索引的最后一个字段不是 call_time。

于是将IDX_TASK_STATUS_TIME进行修改，sql语句如下

1 2	ALTER TABLE wecall_task_call ADD INDEX `IDX_AK_TASKID_CALLSTATUS_CALLTIME`(`account_key`,`task_id`,`call_status`,`call_time`); DROP INDEX `IDX_TASK_STATUS` ON wecall_task_call;

修改后的表结构sql如下

CREATE TABLE `wecall_task_call_v1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `account_key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'account_key',
  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '任务id',
  `cid` bigint(20) NOT NULL DEFAULT '0' COMMENT '客户id',
  `call_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '唯一标识用户通话id',
  `call_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '呼叫状态 0待呼叫 1已呼叫',
  `import_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '导入时间',
  `call_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '通话时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `IDX_TASK_CALLID` (`account_key`,`call_id`) USING BTREE,
  KEY `IDX_TASK_CID` (`account_key`,`task_id`,`cid`) USING BTREE,
  KEY `IDX_CALL` (`account_key`,`call_time`) USING BTREE,
  KEY `IDX_AK_TASKID_CALLSTATUS_CALLTIME`(`account_key`,`task_id`,`call_status`,`call_time`)
) ENGINE=InnoDB AUTO_INCREMENT=142607 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通话详情表'

然后这样，引起慢查询的sql应该不会再有问题了，因为查询条件也是满足索引的最左原则的，理论上还能用到索引排序优化性能。
结果通过explain出来结果，还是没有做到预期的IDX_AK_TASKID_CALLSTATUS_CALLTIME索引。

通过sql进行查询，任然还是很慢。

第二次处理(强制走索引)

通过上面的现象，我们发现mysql进行优化的时候，并没有走到我们期望的索引。还是走原来的IDX_CALL索引，导致扫全表。本质原因还是mysql 的优化器没有按照我们的预期进行处理。于是想到了添加强制走索引的语句，对这个sql语句进行优化。

优化后sql为

1	select * from wecall_task_call force index(IDX_AK_TASKID_CALLSTATUS_CALLTIME) where account_key = "xingnengyace1" and task_id = 2041 and call_status = 0 order by call_time DESC limit 30

再次运行sql，问题得到解决。
但是引入了force index，后期维护的时候需要小心，对用到的索引进行变更的时候，mysql会报错。

总结

添加索引的时候需要全面评审

原本业务上索引都是正常的，不存在慢sql的情况。但是后续某个场景下，同事A添加了索引IDX_CALL，导致原来的sql执行的时候选择索引异常，最终才导致了这个慢sql。

一般sql写到代码里之前，都会explain来看一下是否会出现慢查询的情况。但是新加索引的时候，一般确实不太会想得到对之前sql的影响，除了更加谨慎的评审之外，也没有特别好的处理方式。

强制走索引的坑

强制索引会增加一定的维护成本。同时要特别注意不要修改用到的索引，否则就会引起sql报错，导致系统异常。

olap业务最好独立出去

同事A加IDX_CALL索引的原因是满足一些定制的报表查询需求。这类需求最好还是用专业的olap数据库来处理，比如CH。后续这些需求迁移到olap数据库之后，索引IDX_CALL可以删除。

Chenaa's Notes

mysql11_orderBy原理&记一次索引失效case