V2EX = way to explore V2EX 是一个关于分享和探索的地方
Sign Up Now For Existing Member Sign In
MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目
MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html MySQL 相关项目 
This topic created in 2157 days ago, the information mentioned may be changed or developed.
有一个表 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 建索引外，有没有其他更好的优化方法?
望大神们指点~
使用 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 建索引外，有没有其他更好的优化方法?
望大神们指点~
 | 1 vindac Jun 2, 2020 via Android 先查 id,再查全部？ |
 | 2 pushback Jun 2, 2020 order by 不是也吃索引吗 |
 | 3 776491381 Jun 2, 2020 via iPhone 索引不只是要对 columnA 建立，如果要建，要把 timestamp 也建进去，否则优化的只是 1s |
 | 4 rogwan Jun 2, 2020 via iPhone sql 执行不是想当然的先后顺序，order by 需要建个索引是常规操作。 |
 | 5 allen9527 Jun 2, 2020 为什么不想加索引?看执行计划，基本在扫描然后又内存排序。 子查询应该是一样的啊。。。 emm 。。。。 要不改改 sort_buffer_size 之类的参数大小 |
 | 6 yjxjn Jun 2, 2020 慎用子查询，join 不行吗？再就是 order by 不建立索引肯定速度慢呀。。 |
 | 7 eke Jun 2, 2020 1. db 已经帮你做了 query optimization 吧？ |
 | 8 M7w2kh5a58AhKlcT Jun 2, 2020  2 1. 为什么「先用子查询查出数据，再用 order by 排序子查询的数据」的方法行不通? 2. 这个查询除了对 columnA 建索引外，有没有其他更好的优化方法? 答： 1. 数据库做了逻辑优化，所以查询路径是一致的，当然你可以修改数据库统计信息，来欺骗数据库。如果数据库统计信息没有错误的话，数据库给你的执行计划就是相对最佳的执行计划。 2. A. 如果建索引要建的是 columnA 和 timestamp 的联合索引，而不是单列索引； B.更好的优化方法： 1) 改业务，不需要排序； 2 ）换存储（ RAID 、SSD 、傲腾等），增大内存； 3 ）架构层面增加缓存系统，REDIS 、memcache 等； 4 ）数据库缓存结果，或者使用物化视图； 5 ）优化 SQL，增加联合索引； 供参考 |
 | 10 snoy Jun 2, 2020 用 select * from TableA force index(columnA) where TableA.columnA='value' order by timestamp;试试？ |
 | 11 jay0726 Jun 2, 2020 搜索关键字全字段排序和 rowid 排序了解一下就知道了，可以建立 columnA 和 timestamp 的联合索引，进一步优化还能建立覆盖索引减少回表 |
 | 12 xyjincan Jun 2, 2020 查询取出 70w 数据好多啊，你看看分页，取前面的，跟取最后面的速度也是不一样的。 把结果查询保存到内存的临时表 |
 | 13 egfegdfr Jun 3, 2020 2. 这个查询除了对 columnA 建索引外，有没有其他更好的优化方法?答 1. ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。 排序的对象是 where 后的数据，所以你用不用子查询，需要排序的数据都是 70w 。 所以在这里，这个方法是行不通的。 2. 不知道数据插入的时候有 主键是不是按时间戳顺序生成的。如果是那就好办了，直接 order by 主键就行。如果不是，好像除了改业务，或者是加硬件 也就是建立 timestamp 的索引这个方法比较实际了。 |
 | 14 ljzxloaf Jun 3, 2020 1. mysql 本来就是这么干的 2. 适当调大 sort-buffer，使每次排序的数据量大一些，减少 merge 排序的次数，从而减少 io ； 不过还是建议加个联合索引（ columnA，timestamp ），这样就不用每次排序了 |
Select Language