|
|
|
|
移动端

最快速的办法解决MySQL数据量增大之后翻页慢问题

MySQL单表数据量超过30万后,使用Limit翻页查询时 越往后翻页性能越慢。

作者:Oo若离oO来源:Oo若离oO的博客|2018-04-02 15:37

【新品产上线啦】51CTO播客,随时随地,碎片化学习

最快速的办法解决MySQL数据量增大之后翻页慢问题

MySQL最易碰到的性能问题就是数据量逐步增大之后的翻页速度变慢的额问题,而且越往后翻页速度越慢,如果用最快速的办法解决,以下就是解决办法,简单方便。

1、问题现状

现有MySQL数据表 event_data ,数据量 36.7万,如下:

  1. mysql> select count(1) from event_data; 

  2. +----------+ 

  3. count(1) | 

  4. +----------+ 

  5. |   367961 | 

  6. +----------+ 

  7. 1 row in set (0.05 sec) 

使用SQL-Limit分页查询,需要花费时间382秒,如下:

  1.   SELECT a.* 

  2.     FROM event_data a 

  3.    WHERE a.receive_time >= '2018-03-28 00:00:00' 

  4.      AND a.receive_time <= '2018-03-28 23:59:59' 

  5. ORDER BY a.receive_time DESC 

  6.    LIMIT 56280,15; 

其中 receive_time  字段已建立索引,event_data 表的主键字段为 pk_id。

2、问题分析

但是如果把上面的SQL稍微变动一下,就会发现查询速度有质的飞跃,如下:

  1. mysql>    SELECT a.pk_id 

  2.     ->      FROM event_data a 

  3.     ->     WHERE a.receive_time >= '2018-03-28 00:00:00' 

  4.     ->       AND a.receive_time <= '2018-03-28 23:59:59' 

  5.     ->  ORDER BY a.receive_time DESC 

  6.     ->     LIMIT 56280,15; 

  7. +----------------------------------+ 

  8. | pk_id                            | 

  9. +----------------------------------+ 

  10. | ce7d990f39a4411c88ebb3240497e6f3 | 

  11. | bd47b1c380c946c39b3ec172a262823f | 

  12. | c1820a5633714a5e9a0b0c2abb092579 | 

  13. | d58456cbe16d4cc4a20645c1126fb9b7 | 

  14. | a1d786640ea048a7bd10bd9f12868d6d | 

  15. | f590aa4f26034dd9af2f4ba0d4f7430c | 

  16. | af3e81eb43b84467bd3e2c7ad31d6aff | 

  17. | 3ee331dce2064a788515986a0a97ac2b | 

  18. | 46f462dc4209499f90dad2dc4076e4ca | 

  19. | 8ebb7ff55bc443e4854e583f6dc37ff7 | 

  20. | 883fd68d93dc49eab5b35ebf9ab8f8ee | 

  21. | 932c264d6dd140f3ac4f07f3410ce147 | 

  22. | 19b6426cdd664fe2983166e0cca93c53 | 

  23. | 3a0bfa4e000e4b69bba1a6bed6545973 | 

  24. | 5640a162380346f19d57a61c1dc0dd42 | 

  25. +----------------------------------+ 

  26. 15 rows in set (0.03 sec) 

查询结果返回只需要30毫秒,虽然SQL变动很小,只是将查询结果 由 a.* 改为了 a.pk_id,但是速度的提升是巨大的。

原因分析:两条SQL where 条件语句都是使用了索引的,这里没有问题,但是查询结果不同,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快,这里就是这种情况。

利用覆盖索引,将查询语句需要扫描表的行数降低到最少,即可降低查询时间。

3、问题解决

按照上面的分析,我们使用表连接的情况实现SQL优化,如下:

  1. select a.* FROM (  
  2.             SELECT pk_id   
  3.               FROM event_data c   
  4.              WHERE c.receive_time >= '2018-03-28 00:00:00'   
  5.                AND c.receive_time <= '2018-03-28 23:59:59'   
  6.                     ORDER BY c.receive_time DESC   
  7.              LIMIT 56280,15  
  8.                 ) b   
  9.       left join event_data a   
  10.              on a.pk_id=b.pk_id 

执行时间为0.048秒,达到优化目标。

【编辑推荐】

  1. 关于MySQL线程池,这也许是目前最全面的实用帖!
  2. MySQL常用30种SQL查询语句优化方法
  3. 数据库性能优化之SQL语句优化(上)
  4. 数据库性能优化之SQL语句优化(下)
  5. MySQL高性能优化实战全解!
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网络技术应试辅导(三级)

本书根据教育部考试中心2004年最新发布的《全国计算机等级考试大纲》编写,针对计算机等级考试三级网络技术各方面的考点进行讲解和训练。本...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊