order by + limit 改写优化
3,824 total views, 3 views today
order by +limit 常用于分页功能。本案例中，用ID列进行排序，除了查询ID列外，还查询了value和stuffing列。这种情况容易出现性能问题或者不是性能最优的SQL写法。
|
1 2 3 |
SELECT id, value, LENGTH(stuffing) AS len FROM t_limit ORDER BY id LIMIT 150000, 10; |
测试中发现，如果t_limit为MySIAM。很容易出现性能问题，执行计划走全表扫描不走索引。
现在几乎不用MySIAM，所以本文测试结果基于InnoDB。
在InnoDB引擎前提下，执行上面的SQL，速度也不慢，0.04秒完成。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
+--------+--------------+-----+ | id | value | len | +--------+--------------+-----+ | 150001 | Value 150001 | 200 | | 150002 | Value 150002 | 200 | | 150003 | Value 150003 | 200 | | 150004 | Value 150004 | 200 | | 150005 | Value 150005 | 200 | | 150006 | Value 150006 | 200 | | 150007 | Value 150007 | 200 | | 150008 | Value 150008 | 200 | | 150009 | Value 150009 | 200 | | 150010 | Value 150010 | 200 | +--------+--------------+-----+ 10 rows in set (0.04 sec) |
information_schema.INNODB_BUFFER_PAGE 记录了innodb buffer pool中的每个page的信息。
查看此次查询用到的主键page个数，共12827个pages。（重启MySQL，去除干扰）。
|
1 2 3 4 5 6 7 8 |
select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where TABLE_NAME like '%t_limit%' group by index_name; +------------+----------+ | index_name | count(*) | +------------+----------+ | PRIMARY | 12827 | +------------+----------+ 1 row in set (7.62 sec) |
下面，进行SQL改写优化。子查询获取到需要的ID号，然后拿着ID号再和原有表t_limit通过ID列关联，最终得出结果。
|
1 2 3 4 5 |
SELECT l.id, value, LENGTH(stuffing) AS len FROM (SELECT id FROM t_limit ORDER BY id LIMIT 150000, 10) o JOIN t_limit l ON l.id = o.id ORDER BY l.id |
执行速度较第一种SQL写法快了0.01秒。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
+--------+--------------+-----+ | id | value | len | +--------+--------------+-----+ | 150001 | Value 150001 | 200 | | 150002 | Value 150002 | 200 | | 150003 | Value 150003 | 200 | | 150004 | Value 150004 | 200 | | 150005 | Value 150005 | 200 | | 150006 | Value 150006 | 200 | | 150007 | Value 150007 | 200 | | 150008 | Value 150008 | 200 | | 150009 | Value 150009 | 200 | | 150010 | Value 150010 | 200 | +--------+--------------+-----+ 10 rows in set (0.03 sec) |
查看执行计划
|
1 2 3 4 5 6 7 8 |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+---------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 150010 | 100.00 | Using temporary; Using filesort | | 1 | PRIMARY | l | NULL | eq_ref | PRIMARY | PRIMARY | 4 | o.id | 1 | 100.00 | NULL | | 2 | DERIVED | t_limit | NULL | index | NULL | PRIMARY | 4 | NULL | 150010 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+---------------------------------+ 3 rows in set, 1 warning (0.00 sec) |
查看此次查询用到的page个数。
为了防止之前的操作干扰，需重启MySQL，并确保InnoDB buffer pool中没有t_limit表的缓存page。
|
1 2 3 4 5 |
mysqladmin -uroot -p shutdown mysqld_safe --defaults-file=/etc/my.cnf mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where TABLE_NAME like '%t_limit%' group by index_name; Empty set (8.98 sec) |
执行改写后的SQL
|
1 2 3 4 5 |
SELECT l.id, value, LENGTH(stuffing) AS len FROM (SELECT id FROM t_limit ORDER BY id LIMIT 150000, 10) o JOIN t_limit l ON l.id = o.id ORDER BY l.id |
查看此次查询用到主键的page总数为4889个，比SQL改写前减少了7938个！
|
1 2 3 4 5 6 7 |
mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where TABLE_NAME like '%t_limit%' group by index_name; +------------+----------+ | index_name | count(*) | +------------+----------+ | PRIMARY | 4889 | +------------+----------+ 1 row in set (7.62 sec) |
t_limit的主键创建在id列上，那么它会占多少InnoDB buffer pool呢？
我分别用这两种方式，将整个主键缓存到InnoDB buffer pool中，最终占用的page个数相同，都是13546。
|
1 2 |
mysql> select id from t_limit; mysql> select sum(id) from t_limit; |
|
1 2 3 4 5 6 7 |
mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where TABLE_NAME like '%t_limit%' group by index_name; +------------+----------+ | index_name | count(*) | +------------+----------+ | PRIMARY | 13546 | +------------+----------+ 1 row in set (8.04 sec) |
t_limit主键为13546个pages，SQL在改写前共需要缓存12827个pages，改写减少到4889个pages。
这说明，SQL改写后，需要扫描的索引pages数量减少，提高了SQL执行效率。所以，再次遇到order by + limit 分页SQL时，请按照上面的改写方法书写，能提升不少性能。
参考：
https://explainextended.com/2011/02/11/late-row-lookups-innodb/
https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
https://www.cnblogs.com/zhangyachen/p/8030252.html