mysql千万级数据分页查询优化方案

2020-11-02

字数统计: 2.1k字 | 阅读时长≈ 7分

前言

使用mysql作为数据分页查询的时候我们一般使用limit，这样做在数据量不大的情况下是完全没问题的，当数据量达到几百万或者几千万的时候，使用limit速度将会非常的慢，比如在一张600万数据量的表中使用以下sql分页查询

SELECT * FROM TABLE WHERE IS_DELETE=0 LIMIT 5000000,20

也许你查前几页的时候是很快的，比如limit 0,20，但是当分到第几百万行的时候，越往后速度越慢，这是由于mysql的innoDB引擎使用的是B+Tree数据存储结构。

关于优化

有人可能说数据量上千万了为何不直接分表分库，而我个人觉得分表分库会增加查询的复杂性，能通过优化索引解决的问题还没有必要分表，除非数据量大到了索引无法解决问题的地步.

另外一个，一般很少有业务需求是直接把一张大数据量表的数据分页查询的，一般都是要带点条件，比如订单表那肯定是根据用户ID和删除状态去查，这种情况只需要建好索引即可。

开始优化

先来看看未优化前的SQL

select * from TEST_ORDER WHERE limit 10000000,10000010

从第一千万条开始，查10条数据，结果是执行时间12秒。

有人提供思路，通过ID分页查询，于是SQL变成了如下：
select * from TEST_ORDER where id>10000000 limit 10
由于ID自带主键索引，所以查询非常快，从之前的12s瞬间变为0.02s

那么问题来了，如果我的ID不是连续的，中间删除了几条怎么办？如果不做处理，这样分页的结果是，上一页和下一页会查出重复的数据。
于是改进版变成了每次请求下一页之前带上上一页的最大值ID（lastId）,且添加删除标记，不再物理删除
select * from TEST_ORDER where id>#{lastId} AND DELETED=0 limit 10

然而还是有问题，谁会一页一页的去点？如果不是一页一页的去点，而是跳页点，那么就拿不到lastId。

普通分页查询优化版

为了解决这个问题，我想了一个(tou)骚(ji)操(qu)作(qiao)的办法，首先必须给删除标记字段添加索引（DELETED），查询时通过ID - (被删除数据的条数) = 实际分页起始ID ，因此代码如下：
SELECT ID,PRODUCT_NAME,ORDER_NO,PAY_TIME,CREATE_TIME FROM TEST_ORDER WHERE ID>#{offset} - (SELECT COUNT(*) FROM TEST_ORDER WHERE DELETED=1 AND ID<#{offset}) AND DELETED=0 LIMIT #{pageSize} )

比如要查询第10000页，每页20条，那么offset参数= 10000*20 = 200000,pageSize=20,如此分页就不会查出重复数据了。 注意：如果是第一页offset参数应该要=0

以上通过ID走主键索引，查询时间基本在0.2s左右。

查询总条数不需要减删除条数：
SELECT COUNT(*) FROM TEST_ORDER WHERE ID>#{offset} AND DELETED= 0

然而你以为就这样结束了吗？此时站在你后面的产品经理说了：小X，你这样不行啊！这个必须要按时间排序啊，客户需要优先看到最新的数据！

通过ID分页实际上已经是使用了ORDER BY ID ASC，时间与ID排序只能二选一？

按时间分页查询优化版

所幸我还是有手段解决这个问题的。

ID是自增的，那么最新创建的数据必然是ID值最大的，根据这个规则我们可以倒序通过ID分页

1
2
3
4
5
select ID,PRODUCT_NAME,ORDER_NO,PAY_TIME,CREATE_TIME from TEST_ORDER  WHERE 

ID <(select max(id) from TEST_ORDER ) - (select count(id) from TEST_ORDER WHERE ID>((select max(id) from TEST_ORDER)-#{offset}) AND DELETED=1)

-#{offset} AND DELETED=0 ORDER BY ID DESC limit #{pageSize}

直接从最大的ID开始查起，然后减去要分页的条数,由于是倒过来了所以要用最大值ID减去要查的行数再得出被删除的条数，比如要查询第10000页，

每页20条，那么offset参数= 10000*20=200000，分页计算规则和上一版一样！ 注意：如果是第一页offset参数应该要=0

到此使用以上方法按时间排序查询，第一次可能在0.2-0.5S内，后面基本在0.02S左右

关于更新数据

更新数据时，where条件一定要带上索引字段，否则走全表扫描更新时间必定>10s，最好根据ID更新。

分析SQL是否有走索引

可通过expain关键字分析：
expain SELECT * FROM TEST_ORDER WHERE ORDER_NO = 100010022012

出现结果：

SELECT_TYPE	TABLE	TYPE	POSSIBLE_KEYS	KEYS	ROWS	FILTERED	EXTRA
SIMPLE	TEST_ORDER	ref	ORDER_NO_INDEX	ORDER_NO_INDEX	20	10	Using where

以下是expain字段说明，资料来源于互联网

type(重要)

表示MySQL在表中找到所需行的方式，又称“访问类型”。是分析”查数据过程”的重要依据 

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好） 

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 ,逐行做全表扫描.,运气不好扫描到最后一行. (说明语句写的很失败) 

index: Full Index Scan，index与ALL区别为index类型只遍历索引树,相当于data_all index 扫描所有的索引节点,相当于index_all 

range:只检索给定范围的行，使用一个索引来选择行,能根据索引做范围的扫描 

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值,通过索引列,可以直接引用到某些数据行 

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system 

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

　　性能从好到坏:useing index>usinh where > using temporary | using filesort

该列包含MySQL解决查询的详细信息,有以下几种情况： 

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询 

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序” 

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤 

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 

Impossible where：这个值强调了where语句会导致没有符合条件的行。 

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行 

useing index代表索引覆盖，就是查询的列正好在索引中，不用回物理行查询数据

filtered 列

5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。(个人理解是它指返回结果的行占需要读到的行(rows列的值)的百分比,也就是比例越小越好)

本文作者： reiner
本文链接： https://reiner.host/posts/38aafa6a.html
版权声明： 转载请注明出处，并附上原文链接