记一次大数据量查询优化

由于之前接收了公司题库模块，在编写demo时，给到的数据只有1500+道数据。因而在SQL上没有做太多的优化。直接使用最基本的SQL语句去做处理，当正式题库进入时，题目数量是在200w以上。原先的逻辑已不能使用于当前数据上。因而对程序进行优化。

1、逻辑本身

测试demo原先是直接从数据库从抽取所有的题目来处理。百万级数据显然无法适用，因而采用分页的形式，循环读取，批量处理。

2、SQL

使用分页查询时，之前是使用最基本的limit实现；当起始页达到十来万的时候，会开始变得非常的卡顿。无法适用.

原先使用SQL：

SELECT * FROM table limit n,m

需要对此处进行优化修改.

思路：利用主键索引

方法：使用主键或唯一索引，利用索引（假设每页10条）

select * from table where id > (n*10) limit m

利用索引扫描,速度会很快，但是由于此处使用id做判断，而题库内id为递增但非等差的形式，因而会丢失数据，此处不可取

方法订正二：利用主键为递增且非等差的形式，可以将原有SQL逻辑修改,并基于索引再排序

select * from questions where id > 上次查询的最后一道题的id order by id asc limit m;

成功快速分页查询。

 

ps: 其他方法转载

方法三：基于索引再排序

语句样式，MySQL中可用如下方法：

select * from table_name where id_pk > (pageNum * 10) order by id_pk asc limit m;

 

适应场景: 适用于数据量多的情况(元组数上万). 最好 order by 后的列对象是主键或唯一所以,使得 order by 操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)

原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有 asc 没有 desc ( desc 是假的,未来会做真正的 desc ,期待…).

方法四：基于索引使用prepare

第一个问号表示pageNum，第二个问号表示每页元组数

语句样式，MySQL中可用如下方法：

prepare stmt_name from select * from table_name where id_pk > (? * ?) order by id_pk asc limit m;

 

适应场景: 大数据量

原因: 索引扫描,速度会很快. prepare语句又比一般的查询语句快一点。

方法五：利用MySQL支持order操作可以利用索引快速定位部分元祖，避免全表扫描

比如：读第1000到1019行元组(pk是主键/唯一键).

select * from your_table where pa >= 1000 order by pk asc limit 0,20;

 

方法六：利用"子查询/连接+索引"快速定位元祖的位置，然后再读取元祖。

比如(id是主键/唯一键,蓝色字体时变量)

利用子查询示例:

select * from your_table where id <= 
(select id from your_table order by id desc limit ($page - 1) * $pagesize order by id desc limit $pagesize)

 

利用连接示例:

select * from your_table as t1
join (select id from your_table order by id desc limit ($page - 1) * $pagesize as t2 where t1.id <= t2.id order by t1.id desc limit $pagesize);

 

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。

 

参考博客:

https://cloud.tencent.com/developer/article/1849944