MySQL limit查询 百万级数据分页查询优化的两种方式

注 : 如解释错误的地方,请指出 会及时修改,谢谢 java学习笔记 思维导图版 码云仓库地址 https://gitee.com/vx202158/vx202158.git

一 生成测试数据

参考 文章 MySQL快速生成大量测试数据1000万

二 测试

1.创建索引

    建表时已经设置了 id 是自增主键 在innoDB存储引擎中 自增主键默认作为聚蔟索引

2.分页查询

2.1 通常方式

#测试 LIMIT  测试
select * from t limit 1900000,10

可以看到执行时间为0.36秒

对执行计划进行分析

可以看到 type 是 ALL 也就是最慢的全表扫描,操作了二百一十万行记录

2.2 优化方式一

思路

因为有主键索引 我们可以利用索引覆盖 首先查询 主键id 再利用 eq_ref

2.2.1 第一步 查询 id

因为有主键索引 我们可以利用索引覆盖 首先查询 主键id 再利用 eq_ref

select id from t order by id limit 1900000,10

分析SQL执行计划

SQL执行计划分析

可以看到 type 为 index 使用了主键索引 并且覆盖索引

2.2.1 第二步 eq_ref 关联子查询

    select t.* from t , ( select id from t order by id limit 1900000,10 ) t1 where t.id = t1.id

可以看到耗时为0.245s 优化了将近0.12s 数据量越大 优化时间越明细

分析SQL执行计划

接着分析SQL执行计划

可以看到 查询了两趟 (子查询导致) 第一趟查询了t表, 全索引扫描,使用了主键,以及覆盖索引
接着 对<derived2> 派生表 进行 全表扫描查询了10行记录 ,然后 对 关联表 t 进行 等值引用查询 </derived2>

2.2 优化方式二

思路

首先查询 我们进行分页的起始 主键id,然后用where 条件 range 查询 最后取 我们需要获取的记录数

2.2.1 第一步 查询 主键id

select id from t limit 1900000,1

分析SQL执行计划

SQL执行分析

操作t 表进行了 全索引扫描,使用了 主键索引,使用了覆盖索引

2.2.1 第二步 关联范围子查询

select * from t,(select id from t limit 1900000,1) t1 where t.id > t1.id LIMIT 0,10

可以看到耗时 仅需 0.19s 优化了0.17s

分析SQL执行计划

接着我们分析SQL执行计划

首先对 t 表进行了 全索引扫描操作,使用了 主键索引 ,并且覆盖索引
然后 对 派生表 <derived2> 进行了 system 操作(最快的系统属性操作)
在然后 对 t表进行范围查询,用到了主键索引 以及使用了where 条件</derived2>

总结

SQL操作速度快慢

system -> const  -> eq_ref -> ref -> range -> index -> all

优化limit分页查询方式列出了两种
第一种 先利用主键索引 查询 范围查询的 范围 主键id 然后进行关联子查询
查询类型为
eq_ref
index
all

第二种 先查出复合范围查询条件的id 的最小值 然后进行关联子查询 在分页
查询类型为
system
range
index