解决中mysql数据库中深度分页问题

柔情只为你懂 2023-06-23 02:49 43阅读 0赞

面试题：MySQL数据库中解决深度分页。 表格tb\_test，**主键id是唯一有索引的字段**。表格有数据1000万，分页查询数据，每页显示10条，查询第1万页，如何编写SQL效率最高。

### deep paging --索引、SQL优化 ###

**MySQL执行SQL语法的顺序：**  
select column\_name 3 筛选需要的字段  
from table\_name 1 定位表格，解析表格特性（索引）  
where bool expression 2 在索引或全表扫描的前提下，过滤数据  
group by column\_name 4 分组  
having bool expression 5 分组后的条件  
order by column\_name 6 基于结果排序

**索引特性**：在MySQL中索引的底层实现是B\[+\] Tree。Balance Tree是平衡树。

**什么时候使用索引？**  
1.查询条件where子句中的字段有索引的时候使用（必须第一个条件就利用了有索引的字段才会使用索引）；  
2.select子句中的所有查询字段都是索引的时候，一定使用索引（索引覆盖查询）。*索引是在内存里的。*

**索引限制**：DBMS对索引的数量有限制。每个表格创建的索引数量有限制。每个索引可涵盖的字段数量有限制。

\*\*索引代价：\*\*在写数据的时候，需要维护索引。写操作越频繁，维护索引的代价越高昂。索引字段一定创建在不频繁变更的字段和表上。如果必须创建索引，且字段数据或表数据频繁变更，需要定时重建索引。

1.普通的分页 all–全盘扫描  
select id,c1,c2,c3,c4 from table limit 100000,10

2.索引查询–range范围查询  
select id,c1,c2,c3,c4 from table where id in  
(select id from table order by id asc limit 100000,10)

查询效率：const>ref>range>index>all

const 常量，索引覆盖查询  
ref 引用查询，在索引中可以精确定位数据在硬盘的位置。 主键等值查询  
range 范围查询，在索引中可以定位数据在硬盘的位置范围。主键in查询  
idnex 索引查询 ，只要使用了索引，就是索引查询。多条件查询，第一个查询条件有索引，  
all 全表扫描，不使用索引。

商业开发中，要求所有的业务查询，必须是index效率以上的，建议range效率以上。

explain select …  
EXPLAIN select \* from tb\_item where id=536563  
![在这里插入图片描述][20191217093336912.png]  
EXPLAIN select id from tb\_item  
![在这里插入图片描述][2019121709373976.png]  
EXPLAIN select \* from tb\_item where id<536563  
![在这里插入图片描述][20191217093908871.png]  
EXPLAIN select \* from tb\_item  
![在这里插入图片描述][2019121709395842.png]  
create index tb\_item\_price on tb\_item(price);  
EXPLAIN select id from tb\_item where price=29900000  
![在这里插入图片描述][20191217100141661.png]

## 1.直接使用数据库提供的SQL语句 ##

select * from tb_item limit 0,10

利用覆盖索引，只包含主键列

SELECT id FROM tb_item LIMIT 3000, 10

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

SELECT * FROM tb_item WHERE id >= (SELECT id FROM tb_item LIMIT 3000, 1) LIMIT 10;
    
    SELECT * FROM tb_item a JOIN (select id from tb_item limit 10, 10) b ON a.id = b.id

## 2.建立主键或唯一索引，利用索引 ##

SELECT * from tb_item where id>100 limit 10

## 3.基于索引在排序 ##

SELECT * from tb_item where id>100 ORDER BY id asc LIMIT 10

## 4.基于索引使用prepare ##

PREPARE stmt_name FROM 'SELECT * FROM tb_item WHERE id > (10*10) ORDER BY id ASC LIMIT 10'

## 5.利用mysql支持order操作可以利用索引快速定位部分元组，避免全表扫描 ##

SELECT * from tb_item where id >=100 ORDER BY id ASC LIMIT 10,10

## 6.利用子查询/连接+索引 快速定位元组的位置，然后在读取元组 ##

-- （1）利用子查询
     select * from tb_item where id <=(select id FROM tb_item ORDER BY id DESC limit 1) ORDER BY id DESC LIMIT 10
    -- （2）利用连接join
    
     SELECT * FROM tb_item AS t1 JOIN (SELECT id FROM tb_item ORDER BY id desc LIMIT 10) AS t2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 10;

## 7.使用mysql的found\_rows()函数 ##

select SQL_CALC_FOUND_ROWS * FROM tb_item WHERE id > 100 LIMIT 10;
     SELECT FOUND_ROWS();

其他：

select * from tb_item where id BETWEEN 300000 and 800000

[20191217093336912.png]: https://img-blog.csdnimg.cn/20191217093336912.png
[2019121709373976.png]: https://img-blog.csdnimg.cn/2019121709373976.png
[20191217093908871.png]: https://img-blog.csdnimg.cn/20191217093908871.png
[2019121709395842.png]: https://img-blog.csdnimg.cn/2019121709395842.png
[20191217100141661.png]: https://img-blog.csdnimg.cn/20191217100141661.png