分页优化
由于limit
的查询方式,因此使用limit
进行分页效率并不高
我们举个🌰:limit 1000,10
实际执行的时候,会从头开始查询到1010条数据,然后再舍弃掉前1000条。。。没错就手笔是这么阔(˶‾᷄ ⁻̫ ‾᷅˵)
既然limit
效率不高,那么如何进行优化呢?
不同场景的优化方式
自增且连续的主键(要求数据中间无缺失)
WHERE id LIMIT 1000,10
可以优化为WHERE id>1000 LIMIT 10
举个🌰
优化前
select * from app_user ORDER BY name limit 100000,5;
优化后
select * from app_user a inner join (select id from app_user order by name limit 100000,5) b on a.id = b.id;
分别使用Explain分析下
优化前,也用到了索引,但是是使用了range进行范围扫描
优化后, 有三行数据,第一行看着虽然走的是全局扫描,但是他是由第三行派生出的,即只有一“页”(本案例中只有5行数据),第二行可以看出使用的是主键进行关联索引,type
是eq_ref
比ref
的速度还快,第三行虽然是遍历索引,但是没有进行回表,而且只查询五个值即可,因而优化后速度快很多
JOIN关联优化
先介绍下驱动表和被驱动表的概念( ̄∇ ̄)/
简单理解先执行的就是驱动表,不同的join类型MySQL选择的驱动表不同
inner join
不确定,MySQL会自行判断(一般选数据量小的表做驱动表)left join
左边👈的是驱动表right join
右边👉的是驱动表
算法介绍
使用索引字段进行关联的关联查询一般会使用NLJ
简单理解就是拿一张表(驱动表)中的所有数据,一次一次的去另一张表(被驱动表)中查找对应行,最后取出两张表的结果合集
EXPLAIN select * from app_user a inner join app_user_copy1 b on a.id = b.id;
上面SQL执行的大致流程如下:
使用非索引字段进行关联的关联查询一般会使用BNL
EXPLAIN select * from app_user a inner join app_user_copy1 b on a.age = b.age;
Extra中Using join buffer (hash join)
表示使用BNL,可以看到两张表进行的都是全表扫描
上面SQL执行的大致流程如下:
为什么MySQL会根据关联字段是否有索引而使用不同的算法
首先,我们大致量化下两个算法总消耗
NLJ总消耗(有索引)
- 磁盘扫描:a表行数 * 2(会先扫描索引之后直接扫描符合条件的数据行)
- 内存判断:a表行数 * b索引
BNL总消耗(无索引)
- 磁盘扫描:a表行数 + b表行数
- 内存判断:a表行数 * b表行数(由于join_buffer中的数据是无序的,因此判断次数=a表行数 * b表行数)
如果在没索引的情况下使用NLJ,会导致磁盘扫描=a表行数 * b表行数,由于内存判断要比磁盘扫描快的多,因此在没索引的情况下,MySQL一般会选择BNL,而有索引则选择NLJ
优化方式
关联字段加索引
减少不必要的字段查询
加大join_buffer_size的大小(一次缓存的越多,内层扫描的次数就越少)
小表驱动大表(Explain结果集中小表在上)
可使用straight join
设置左表驱动右表,不过只适用于inner join
尽量让MySQL的优化器自行判断(MySQL的优化器还是很稳的)
关于in
和exsits
:
当后半部分筛选出的结果集小于前面半部分,一般用in
in
可以理解为以后面部分的结果集的大小作为外层循环的遍历次数,做个简单的代换就是a IN b
就相当于for(b.size){a}
,因此in
会先执行b
部分,b
部分越小,也就相当于for循环次数越少当后半部分筛选出的结果集大于前面半部分,一般用exsits
exsits
会先执行exsits
前面的部分,做个简单的代换就是a EXSITS b
就相当于for(a.size){b}
,即a
部分越小,for循环次数越少值得注意的是,这里的小表的“小”是指关联的表们分别按照各自的过滤条件进行过滤后,参与join的数据量,而非原始数据量
说白了就是先执行的那部分所得到的结果集越小,执行效率越高
COUNT优化
关于count(*)
、count(1)
、count(id)
、count(字段)
哪种效率最高?
自问自答:
-
字段有索引
count(*)
约等于count(1)
>count(字段)
>count(id)
- 二级索引存储的数据比主键索引小
count(1)
无需取字段,count(字段)
需要取字段,理论上来说count(1)
会比count(字段)
快一点count(*)
被特别优化了下,按行累加,效率很高
-
字段无索引
count(*)
约等于count(1)
>count(id)
>count(字段)
但其实MySQL优化到现在的版本,这四个的执行效率基本差不多,explain
下
可以看到四条执行结果完全一致,SO你懂的(o^^o)/🎉
不过count(字段)
跟其他3个有个很大的区别,当字段中有NULL
时(如下图id
为5
的数据行name
的值为NULL
)
我们分别执行下count(*)、count(1)、count(id)、count(name)
可以看到,当count
某个字段时,如果该字段为NULL
,则不会被统计到(其他三个都会统计到)
优化方式
查询MySQL自己维护的总行数
WHERE
的)会被myisam存储到磁盘上,查询无需计算,超级快模糊获取
show table status like '表名'
获取近似值,不准确,但很快并且无需额外操作在Redis中维护总行数
在数据库增加计数表