mysql深度分页游标 mysql的深度分页

转载

数据大侠客 2024-02-19 13:49:22

文章标签 mysql深度分页游标 MySQL 分页字段 文章分类 MySQL 数据库

深度分页

深度分页是指在大数据量下，查询大页码的分页方式

MySQL基本分页方式

select * from tb order by id desc limit 0, 10
耗时0.003s
-----------------------------------------------
select * from tb order by id desc limit 10000, 10
耗时0.058s
-----------------------------------------------
select * from tb order by id desc limit 100000, 10
耗时0.575s
-----------------------------------------------
select * from tb order by id desc limit 1000000, 10
耗时7.28s

limit n,m的工作原理是先读取前面n条记录，然后抛弃前n条，返回后面m条数据，所以随着n的增加，limit语句的偏移量越来越大，速度会越来越慢

优化：延迟关联+范围扫描

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得所需要的数据
通过使用条件对id进行筛选，在子查询中只查询id这个字段比查询所有字段要节省大量的查询开销

子查询

select * from tb where id > (select id from tb order by id desc limit 1000000,1) order by id desc limit 0,10
耗时0.365s

适合id连续、不带条件的查询情况

select * from tb where id between 1000000 and 1000010 order by id desc

关联查询

select * from tb inner join (select id from tb order by id desc limit 1000000,10) using id

优化：使用MySQL的FOUND_ROWS()函数

参考

MySQL高效分页解决方案集百万级数据下的mysql深度解析MySQL分页查询的性能优化MySQL 分页优化中的 “ INNER JOIN方式优化分页算法 ” 到底在什么情况下会生效？

deep paging

索引、SQL优化

MySQL执行SQL语法的顺序：

3.select id - 筛选所需字段
1.from tb - 定位表格，解析特性（有没有索引）
2.where bool expression - 在索引或全表的扫描的前提下，过滤数据
4.group by name - 分组
5.having bool expression - 分组后的条件
6.order by age - 基于结果排序

索引特性：在MySQL中索引的底层实现是B[+]Tree（balance tree）

什么时候用索引：
1.查询条件where子句中的第一个条件字段有索引（如果第一个字段没有索引，未必会使用索引）
2.select子句中所有查询字段都是索引（索引覆盖查询）

索引限制：DBMS中，每个表格创建的索引数量有限制、每个索引可涵盖的字段数量有限制、内存加载也有限制

索引代价：写数据需要维护索引，写操作越频繁，代价越高（如果写操作频繁、又必须建立索引，则需要定时重建索引）

// 全盘查询-all
select id, name, age from tb limit 9990,10

// 范围查询-range
select id, name, age from tb where id in
(select id from tb order by id asc limit 9990,10)

查询效率：const > ref > range > index > all
const - 常量，如：索引覆盖查询（全在内存中进行查询）
ref - 引用，在索引中可以精确定位数据在硬盘的位置，如：主键等值查询（where pk = xx）
range - 范围，在索引中可以定位数据在硬盘的位置范围，如：主键in查询
index - 索引，只要使用了索引，如多条件查询中，第一个查询条件又索引
all - 全表，不使用索引

商业开发中，要求所有的业务查询，必须是index效率以上（建议range效率以上）
测试查询效率：explain + sql查询语句（查看type类型）