延迟关联优化SQL一例

昨日下午和今日凌晨出现较多告警

经排查发现时又下面SQL导致

SELECT * FROM pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000

Limit愈来愈大,导致走全表扫描加文件排序

root@localhost 10:23:  [forum_longtugamev2_db]> explain SELECT * FROM pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000;
+----+-------------+----------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+----------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | pre_forum_post | ALL  | NULL          | NULL | NULL    | NULL | 9554664 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.00 sec)

改写SQL,使用延迟关联

root@localhost 10:23:  [forum_longtugamev2_db]> explain select a.* from pre_forum_post a ,(select tid,position from pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000) b where a.tid=b.tid and a.position=b.position;
+----+-------------+----------------+--------+-----------------------------------------+---------+---------+------------------+---------+-------------+
| id | select_type | table          | type   | possible_keys                           | key     | key_len | ref              | rows    | Extra       |
+----+-------------+----------------+--------+-----------------------------------------+---------+---------+------------------+---------+-------------+
|  1 | PRIMARY     | <derived2>     | ALL    | NULL                                    | NULL    | NULL    | NULL             |    1000 |             |
|  1 | PRIMARY     | a              | eq_ref | PRIMARY,displayorder,first,idx_position | PRIMARY | 7       | b.tid,b.position |       1 |             |
|  2 | DERIVED     | pre_forum_post | index  | NULL                                    | pid     | 9       | NULL             | 7333000 | Using index |
+----+-------------+----------------+--------+-----------------------------------------+---------+---------+------------------+---------+-------------+
3 rows in set (1.32 sec)

可以看到b表为派生表,走了二级索引pid,由于二级索引包含主键值,所以直接走这个索引就能拿到主键
然后在将主键值传给主表a, 那么a表又走主键,

优化前31秒,优化后1秒

最后

以上就是秀丽跳跳糖最近收集整理的关于延迟关联优化SQL一例的全部内容，更多相关延迟关联优化SQL一例内容请搜索靠谱客的其他文章。