mysql order by排序性能优化

问题描述

今天查询数据涉及到排序，但是排序后性能有较大幅度下降。

表结构

查询语句

SELECT data_time,stock_code,`close` FROM `stock_data` WHERE stock_code = '002125' ORDER BY data_time DESC LIMIT 5

使用 explain 命令来看看这个语句的执行情况

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

性能优化

这里我们需要对查询字段添加组合索引，单个索引是没有效果的。

ALTER TABLE stock_data ADD INDEX `idx_code_time` (stock_code,`data_time`)

添加好索引之后，我们可以再使用 explain 查看语句执行情况

这个时候就会发现 Extra 字段中没有Using filesort了，也就是不需要排序了。性能有了大幅提升。

还可以进一步优化，把 close 字段加入索引中

ALTER TABLE stock_data ADD INDEX `idx_code_time_close` (stock_code,`data_time`,`close`)

再执行 sql 发现时间反而变长了，使用 explain 查看语句执行情况，发现可能没有使用刚刚建立的【idx_code_time_close】索引。

这个时候需要强制使用索引来解决

SELECT data_time,stock_code,`close` FROM `stock_data` FORCE INDEX (`idx_code_time_close`) WHERE stock_code = '002125' ORDER BY data_time DESC LIMIT 5