mysql 优化笔记

老咸鱼也要加饭

于 2023-01-25 11:43:34 发布

阅读量103

点赞数

分类专栏： java 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44960500/article/details/128759889

版权

java 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一、分页查询优化
先看一条平常用到的分页sql

select * from sys_view limit 50000,10

分页查询时，mysql会把前面50000条数据行也一同查出来，然后再跳过查询结果中不需要50000条数据，再返回后面10条。
执行计划
在这里插入图片描述
由于如果没添加order by排序规则，mysql默认是按主键排序，因此可以改造为如下，查看执行计划，发现这里能使用到逐渐索引。但这里也存在一个问题，如果id不是连续自增的，这里查询结果就会有问题

select * from sys_view where id > 50000 limit 10

执行计划
在这里插入图片描述
终极方案

select * FROM atd_user_view t1 join (select id from atd_user_view ORDER BY id limit 50000,10 )
t2 on t1.id = t2.id

执行计划
在这里插入图片描述

二、join连接查询优化
如下sql

select * from sys_view t1  join  sys_user  t2 on t1.user_no = t2.user_no

执行计划
根据上面的执行计划，可以看到inner join 时，mysql会选择数据量更少的表t2先执行并查询所有的数据，再更据t2的结果查询再t1的数据。这里t2被称作驱动表，t1被称作被驱动表。

mysql执行连接查询，通常又两种查询算法
1.嵌套循环连接Nested-Loop Join(NLJ)算法
循环遍历驱动表，用关联的字段在另一张关联表中再次循环遍历查出满足条件的行,结构类似如下
for(){
for(){}
}
以上面的sql为例，大致sql执行流程

从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

可见sql执行过程中IO次数为t2数据行数+t2的数据行数。

2.基于块的嵌套循环连接Block Nested-Loop Join (BNL)
把驱动表中的数据都读入到join_buffer中，然后再扫描另一张关联表中，把每一行取出来跟join_buffer中的数据对比
sql执行过程中IO次数为t1数据行数+t2的数据行数。

结论: 对于关联字段没有索引，一般会用BNL算法，有索引的情况下NLJ算法比BNL算法更高效

为什么要小表驱动大表
由于mysql是基于嵌套循环算法去遍历数据的，假设小表t1有100条，大表t2有10000条数据，mysql去先把小表t1的100条加载到join_buffer，循环遍历这100条数据，再从t2匹配连接条件，可见IO次数只有1+100次。相反先加载大表t2的数据，再遍历t2，从t1中匹配连接条件，就会放生1+10000次，大大增加了IO次数。

关联sql优化
1.关联字段添加索引
2.小表驱动大表
3.阿里巴巴手册建议关联表不超过3张