工作中同事问的一个问题，查了很久没有明白，望大神指点

有一个表 TableA，数据量很大，约 4000w 条。
使用 where 条件，where TableA.columnA='value'后，可以查出约 70w 条数据。
现在有个业务逻辑: 想在 TableA 中使用 where 条件 where TableA.columnA='value'查询数据，并且将数据按照时间戳排序，所以最后的 sql 是:
select * from TableA where TableA.columnA='value' order by timestamp;
通过 explain 发现，此次执行是一个 simple 查询，扫描了全表 4000w 数据(columnA 上没有建索引，所以是正常的)，耗时 13s 。
但是奇怪的是，如果不用 order by，直接 select * from TableA where TableA.columnA='value'; 花了 1s 不到的时间，因此，可以认为时间都花在了 order by 排序上。
于是他用 select * from TableA order by timestamp; 发现也需要 17s 左右的时间，验证了时间都花在了 order by 上。

现在我的同事灵机一动，想到可以把数据用子查询先查出，再做排序，按道理因为子查询查出的数据量只有 70w，已经降了两个量级了，这个时候再做排序应该要轻松一些。于是: select * from (select * from TableA where Table.A.columnA='value') as B order by B.timestamp; 但是用 explain 一看:

'1','SIMPLE','ulog_data_attitude',NULL,'ALL',NULL,NULL,NULL,NULL, 'xxx','xxx','Using where; Using filesort'

发现用没用子查询执行计划都一样。

现在的问题是:
1. 为什么「先用子查询查出数据，再用 order by 排序子查询的数据」的方法行不通?
2. 这个查询除了对 columnA 建索引外，有没有其他更好的优化方法?

望大神们指点~

order

Where

tablea

null

14 条回复 • 2020-06-03 10:38:20 +08:00

vindac

2020-06-02 18:38:06 +08:00 via Android

先查 id,再查全部？

pushback

2020-06-02 18:43:12 +08:00

order by 不是也吃索引吗

776491381

2020-06-02 18:43:33 +08:00 via iPhone

索引不只是要对 columnA 建立，如果要建，要把 timestamp 也建进去，否则优化的只是 1s

rogwan

2020-06-02 18:44:51 +08:00 via iPhone

sql 执行不是想当然的先后顺序，order by 需要建个索引是常规操作。

allen9527

2020-06-02 18:44:57 +08:00

为什么不想加索引?看执行计划，基本在扫描然后又内存排序。子查询应该是一样的啊。。。
emm 。。。。要不改改 sort_buffer_size 之类的参数大小

yjxjn

2020-06-02 18:45:42 +08:00

慎用子查询，join 不行吗？再就是 order by 不建立索引肯定速度慢呀。。

eke

2020-06-02 18:47:12 +08:00

1. db 已经帮你做了 query optimization 吧？

nnd

2020-06-02 19:02:14 +08:00

1. 为什么「先用子查询查出数据，再用 order by 排序子查询的数据」的方法行不通?
2. 这个查询除了对 columnA 建索引外，有没有其他更好的优化方法?

答：
1. 数据库做了逻辑优化，所以查询路径是一致的，当然你可以修改数据库统计信息，来欺骗数据库。如果数据库统计信息没有错误的话，数据库给你的执行计划就是相对最佳的执行计划。

2.
A. 如果建索引要建的是 columnA 和 timestamp 的联合索引，而不是单列索引；
B.更好的优化方法：
1) 改业务，不需要排序；
2 ）换存储（ RAID 、SSD 、傲腾等），增大内存；
3 ）架构层面增加缓存系统，REDIS 、memcache 等；
4 ）数据库缓存结果，或者使用物化视图；
5 ）优化 SQL，增加联合索引；

供参考😀

zivyou

2020-06-02 19:18:22 +08:00

@nnd 感谢详细的解答

多谢各位老哥~

snoy

2020-06-02 20:37:30 +08:00

用 select * from TableA force index(columnA) where TableA.columnA='value' order by timestamp;试试？

jay0726

2020-06-02 23:14:05 +08:00

搜索关键字全字段排序和 rowid 排序了解一下就知道了，可以建立 columnA 和 timestamp 的联合索引，进一步优化还能建立覆盖索引减少回表

xyjincan

2020-06-02 23:15:34 +08:00

查询取出 70w 数据好多啊，你看看分页，取前面的，跟取最后面的速度也是不一样的。把结果查询保存到内存的临时表

egfegdfr

2020-06-03 09:53:14 +08:00

1. 为什么「先用子查询查出数据，再用 order by 排序子查询的数据」的方法行不通?
2. 这个查询除了对 columnA 建索引外，有没有其他更好的优化方法?
答
1. ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
排序的对象是 where 后的数据，所以你用不用子查询，需要排序的数据都是 70w 。所以在这里，这个方法是行不通的。

2. 不知道数据插入的时候有主键是不是按时间戳顺序生成的。如果是那就好办了，直接 order by 主键就行。如果不是，好像除了改业务，或者是加硬件也就是建立 timestamp 的索引这个方法比较实际了。

ljzxloaf

2020-06-03 10:38:20 +08:00

1. mysql 本来就是这么干的
2. 适当调大 sort-buffer，使每次排序的数据量大一些，减少 merge 排序的次数，从而减少 io ；不过还是建议加个联合索引（ columnA，timestamp ），这样就不用每次排序了