分页查询sql说明
SELECT * from tableA WHERE col1 ='xxx' LIMIT m,n;
这条sql会先读取m+n条数据,然后丢弃前面m条数据,只取后面n条数据;所以分页查询时,页码越大,速度越慢。
常见分页场景及分页技巧
1、根据主键自增且连续的主键排序分页查询
优化后:
能够按上面sql优化的前提条件:
1)主键递增且连续
2)按主键排序
2、非主键字段排序分页查询
根据字段name排序,取9001-9005
-- 常规写法
SELECT * from employees order by name LIMIT 9000,5;
-- 优化后
SELECT * from employees m INNER JOIN (SELECT id from employees ORDER BY name LIMIT 9000,5) n on m.id=n.id;
常规分页查询写法:扫描二级索引拿到主键id,再回表聚簇索引成本比全表扫描还高,故没走索引
优化后:
分析:可以通过走二级索引,取到符合条件的记录id,作为衍生表(结果集就5条记录),这时再去关联这个子查询结果,效率就很快
对于关联sql的优化
left join:左边是驱动表,右边是被驱动表
right join:右边是驱动表,左边是被驱动表
join:MySQL会自动选择数据量小作为驱动表,大表作为被驱动表
1、关联sql优化一般遵循下面两点
1)关联字段加索引:大表关联的字段一定要加索引,小表字段无所谓(驱动表会全表扫描),让关联sql做join时,尽量选择NLJ算法
2)小表驱动大表:也就是执行计划里面,先执行的一定要是小表(因为驱动表需要把数据扫描到join_buffer,如果是大表,就需要分多次扫描到join_buffer);如果多表关联时,明确知道哪张是小表,可以用straight_join写法固定连接驱动方式,省去MySQL优化器自己判断的时间
2、MySQL的表关联常见的两种算法
示例准备说明:T1、T2表结构一样,T1表10000数据,T2表100数据,其中a字段建立了索引,b字段没有建立索引;
2.1、嵌套循环连接NLJ算法:
-- 关联字段是索引:a字段建了索引
EXPLAIN SELECT * from T1 INNER JOIN T2 on T1.a = T2.a;
执行流程:驱动表T2表100条记录全表扫描(也就是磁盘扫描)到内存里,再逐行取T2.a去T1表走二级索引拿到主键id,回表获取数据,也就是T2每一行数据的a只需通过一次索引定位就可以取到数据,换句话说T1也要扫描100次磁盘,就可拿到关联查询的结果;
2.2、基于块的嵌套循环连接BNL算法
-- 关联字段非索引
EXPLAIN SELECT * from T1 INNER JOIN T2 on T1.b = T2.b;
执行流程:驱动表T2表100条记录全表扫描加到内存join_buffer中,因为b字段不是索引,故内存中的T2表的b字段是无序的;再从T1表里逐行取出来去内存中join_buffer比对(每次取出一行就表示一次磁盘扫描),也就是T1的每一行都要比对100次,共需要在内存中比对10000x100 = 100W次。
3、问题:大表得关联字段没有建立索引,为什么选用BNL算法?(大表关联字段建索引,就直接用NLJ算法)
若大表关联字段没有索引,
由BNL算法示例可知:磁盘扫描T2表=100次,扫描T1表=10000次,总共扫描磁盘 = 100+10000 = 10100次,内存比对10000x100 = 100W次;
如果是NLJ算法:磁盘扫描T2表=100次,因为没有b字段不是索引,故需要遍历T1表聚集索引的叶子节点(是索引时,可以直接定位,只需要磁盘扫描一次),极端情况下,需要扫描10000次磁盘,故极端情况总共扫描磁盘 = 100x10000 = 100w次;
磁盘扫描效率远远低于内存扫描,故选择的是BNL算法
in和exists优化
-- in查询:小表应该放在in后面
EXPLAIN SELECT * from A where id in (SELECT id from B);
#等价于
for(SELECT id from B){
SELECT * from A where A.id = B.id
}
-- exists查询:exists后面放大表
-- EXISTS (subquery)返回的是true或false
EXPLAIN SELECT * from A where EXISTS (SELECT 1 from B where A.id=B.id);
#等价于
for(SELECT * from A ){
SELECT * from B where A.id=B.id
}
in操作能避免则避免,若实在避免不了,需要仔细评估in后面的集合不要超过1000个。
上一篇中可知,in查询在数据量大的情况,可能走索引,当然数据量小的时候有可能走全表扫描;也就是跟表数据量有关
count(*)查询优化
1、sql写法
-- count()查询优化
EXPLAIN SELECT count(1) from employees;
EXPLAIN SELECT count(*) from employees;
EXPLAIN SELECT count(name) from employees;
EXPLAIN SELECT count(id) from employees;
name字段有索引前提下,4个sql的执行计划一样,说明效率差不多
需要注意的是:count(字段)不会统计null值情况,count(*)会统计null值情况,也就是尽量使用count(*)
2、sql性能分析
1、字段有索引:count(*)≈count(1) > count(字段) > count(id)
count(字段)统计走二级索引,二级索引存储数据比较主键索引少,故count(字段) > count(主键id)
2、字段无索引:count(*)≈count(1) > count(id) > count(字段)
count(字段)走不了二级索引,count(id)还可以走主键索引,故count(字段) > count(主键id)
3、count(1) > count(字段)
count(1) 不需要取出字段统计,直接用常量1统计,count(字段)需要取出字段name,所以理论上count(1) > count(字段)
4、count(*)是例外,MySQL不会把全部字段取出来,而是专门做了优化,不取值,按行累加,效率高;所以不需要用count(字段)或conut(常量)来代替count(*)
3、问:字段有索引情况,count(id)为什么最终选择的是二级索引而不是主键索引?
因为二级索引相对主键索引数据更小,检索性能更高,MySQL内部做了优化(应该是5.7版本才优化)