面试题: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查询