当前位置:首页 > 编程教程 > mysql技术文章 > MySQL单表百万数据记录分页性能优化技巧

MySQL单表分页性能优化技巧

  • 发布时间:
  • 作者:码农之家
  • 点击:57

这篇文章主要知识点是关于MySQL、分页性能优化、的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

深入理解MariaDB与MySQL
  • 类型:MariaDB大小:75.3 MB格式:PDF作者:李成旭
立即下载

MySQL单表百万数据记录分页性能优化技巧

测试环境:

先让我们熟悉下基本的sql语句,来查看下我们将要测试表的基本信息

use infomation_schema
SELECT * FROM TABLES WHERE TABLE_SCHEMA = ‘dbname' AND TABLE_NAME = ‘product'

查询结果:

MySQL单表百万数据记录分页性能优化技巧

从上图中我们可以看到表的基本信息:

表行数:866633
平均每行的数据长度:5133字节
单表大小:4448700632字节

关于行和表大小的单位都是字节,我们经过计算可以知道
平均行长度:大约5k
单表总大小:4.1g
表中字段各种类型都有varchar、datetime、text等,id字段为主键

测试实验

1. 直接用limit start, count分页语句, 也是我程序中用的方法:

select * from product limit start, count
当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

select * from product limit 10, 20  0.016秒
select * from product limit 100, 20  0.016秒
select * from product limit 1000, 20  0.047秒
select * from product limit 10000, 20  0.094秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下(也就是记录的一般左右) select * from product limit 400000, 20 3.229秒

再看我们取最后一页记录的时间
select * from product limit 866613, 20 37.44秒

难怪搜索引擎抓取我们页面的时候经常会报超时,像这种分页最大的页码页显然这种时
间是无法忍受的。

从中我们也能总结出两件事情:
1)limit语句的查询时间与起始记录的位置成正比
2)mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

2. 对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询
我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。

因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列),如下:
select id from product limit 866613, 20 0.2秒
相对于查询了所有列的37.44秒,提升了大概100多倍的速度

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒,简直是一个质的飞跃啊,哈哈

另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
查询时间也很短,赞!

其实两者用的都是一个原理嘛,所以效果也差不多

以上就是本次给大家分享的关于java的全部知识点内容总结,大家还可以在下方相关文章里找到相关文章进一步学习,感谢大家的阅读和支持。

您可能感兴趣的文章:

  • 如何根据status信息优化MySQL服务器
  • MySQL limit查询语句的优化技巧
  • MySQL 5.7增强版Semisync Replication性能优化知识点总结
  • MySQL性能优化总结
  • MySQL性能优化技巧
  • MySQL通过修改配置文件my.cnf优化性能
  • mysql分页优化案例详解
  • MySQL优化知识点总结
  • 相关电子书
    学习笔记

    Copyright 2018-2020 www.xz577.com 码农之家

    投诉 / 推广 / 赞助:QQ:520161757