索引优化与查询优化
文章目录
1. 概述
虽然 SQL 查询优化的技术有很多,但是大方向上完全可以分成 物理查询优化
和 逻辑查询优化
两大块
- 物理查询优化是通过
索引
和表连接方式
等技术来进行优化,这里重点需要掌握索引的使用 - 逻辑查询优化就是通过
SQL 等价变换
提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高
2. 索引失效案例
MySQL 提升性能
最有效的方式就是 设计合理的索引
,但是否用索引都由优化器决定。
优化器是基于 开销(CostBaseOptimizer)
,它不是基于 规则(Rule-BasedOptimizer)
,也不是基于 语义
。怎么开销小就怎么来。另外,SQL语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。
索引失效情况:
- 最佳左前缀法则:对于MySQL多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
- 计算、函数、类型转换(自动或手动)都会导致索引失效
- 范围条件右边的列索引失效,多列索引中,过滤条件中索引左边有范围查询的话,则对应列右边的列索引将失效
- 应用开发中,金额和日期查询往往是范围查询,建立联合索引时务必把涉及范围查询的字段放在索引后面
- 不等于(!= 或者 <>)索引失效
- is null可以使用索引,is not null无法使用索引
- 最好在设计表的时候就将字段设置为not null约束,并给定默认值,int为0,字符串为’’
- like 以通配符 %开头将无法使用索引
- or 前后存在非索引的列,索引失效
- 数据库和表的字符集统一,不同字符集进行比较前需要进行
转换
会造成索引失效
一般性建议:
- 对于单列索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
- 在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
3. 关联查询优化
3.1 Join语句原理
驱动表就是主表,被驱动表就是从表、非驱动表。Join连接时,无索引时,需遍历主表,逐个与被驱动表比对,嵌套循环遍历被驱动表。MySQL5.5后的版本引入了BNLJ算法来优化嵌套执行。MySQL8.0.18版本前,在被驱动表有索引的情况下运用了INLJ算法,无索引时采用BNLJ算法。从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join,默认都会使用hash join
3.2 Simple Nested-Loop Join(简单嵌套循环连接)
算法:从驱动表A中取出一条数据1,遍历被驱动表B,将匹配到的数据放入result…以此类推,驱动表A中的每一条记录与驱动表B的记录进行判断,算法复杂度为O(A * B),效率很低。MySQL当然不会这么粗暴的进行表的连接。
3.3 Index Nested-Loop Join(索引嵌套循环连接)
Index Nested-Loop Join优化的主要思路是 减少被驱动表数据的匹配次数,所以要求被驱动表上必须 有索引
才行。通过驱动表匹配条件直接与被驱动表索引进行匹配,避免和被驱动表的每条记录比较,使得算法复杂度为O(A * LogB),其中因为索引的结构是B+树,LogB的大小一般不会超过4。如果在B上的索引不是主键,还需要进行回表操作,所以如果被驱动表的索引是主键索引的话效率会更高
3.4 Block Nested-Loop Join(块嵌套循环连接)
该算法不是按SNLJ算法一样逐条获取驱动表的数据,而是一块一块的获取,引入了 join buffer缓冲区
,将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer缓冲区中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并为一次,降低了被驱动表的访问频率。
注意:这里缓存的不只是关联表的列,select 后面的列也会缓存起来。
在一个有N个join关联的sql中会分配N-1个join buffer,所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列
参数设置:
- block_nested_loop
- 通过
show variables like '%optimizer_switch%'
查看block_nested_loop状态。默认是开启的
- 通过
- join_buffer_size:默认值:256k
3.5 Hash Join
- Nested Loop:对于被连接的数据子集较小的情况,嵌套循环还是个较好的选择
- Hash Join是做
大数据集连接
时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key
在内存中建立 散列表,然后扫描较大的表并探测散列表,找出与Hash表匹配的行,内存设置还是join_buffer_size- 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和
- 在表很大的情况下并不能完全放入内存,这时优化器会将它分割成
若干不同的分区
,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高l/O 的性能 - 它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1= B.COL2),这是由Hash的特点决定的
3.6 小结
-
整体效率:INLJ > BNLJ > SNLJ
-
永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是 表行数 * 每行大小,而不是单纯指行数)
-- STRAIGHT_JOIN就是在内连接中使用,而强制使用左表来当驱动表,所以这个特性可以用于一些调优,强制改变mysql的优化器选择的执行计划 select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100; #推荐,t2取了所有字段,相对来说join buffer里能存的数据更多 select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100; #不推荐
-
为被驱动表匹配的条件增加索引(减少被驱动表的循环匹配次数)
-
增大join_buffer_size的大小 (一次缓存的数据越多,那么被驱动表的扫表次数就越少)
-
减少驱动表不必要的字段查询 (字段越少,join buffer 所缓存的数据行数就越多)
4. 子查询优化
执行子查询时需要建立撤销临时表,且临时表还是磁盘都不会有索引,在MySQL中建议使用Join查询来替代子查询,尽量不用not in 或in 函数
select a.* from
(
select a.* ,b.field
from tabname as a left outer join tabname as b on a.field =b.field
) x where field is null
5. 排序优化
在MySQL 中,支持两种排序方式,分别是 FileSort
和 Index
排序。
- Index 排序中,索引可以保证数据的有序性,不需要再进行排序,
效率更高
- FileSort 排序则一般在
内存中
进行排序,占用 CPU 较多。如果待排结果较大,会产生临时文件 I/O 到磁盘进行排序的情况,效率较低
优化建议:
- FileSort 排序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率
- 尽量使用Index 完成ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引
- 如果靠过滤条件能过滤掉大部分数据,则使用 FileSort也能接受,order by 在数据过滤后剩余数据量还比较大的时候才会考虑使用索引,否则会优先使用成本低的FileSort
- 无法使用Index 时考虑对 FileSort 方式进行调优
filesort的两种排序方式:
- 双路排序:从磁盘取排序列,排序完再从磁盘取出对应数据输出
- 单路排序:从磁盘读取查询需要的所有列,排序后输出;需要更大内存空间,如果待排数据量很大,每次只能取sort_buffer的容量,进行排序再合并,排完再取会导致大量的I/O操作,反而得不偿失
FileSort 优化策略
- 提高
sort_buffer_size
,提升排序时可用的内存容量大小,innodb存储引擎默认值是1MB - 尝试提高
max_length_for_sort_data
,如果需要返回的列的总长度大于max_length_for_sort_data,使用双路算法,否则使用单路算法,1024~8192字节之间调整。默认1024字节;即提高后会增加使用单路算法的阈值
注意:
order by 时 select * 是大忌,最好只取需要的字段,原因:
- 当查询的字段大小总和小于
max_length_for_sort_data
时,而且排序字段不是TEXT|BLOB类型时,会用改进后的排序——单路排序,否则使用多路排序 - 两种算法的数据都有可能超过
sort_buffer_size
的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size
6. GROUP BY 优化
- where效率高于having,能写在where限定的条件就不要写在having中
- 减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。Order by、 group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的
- 包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢
7. 分页查询优化
优化思路一:在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容
SELECT * FROM student t,(SELECT id FRON student ORDER BY id LIMIT 2000000,10) aWHERE t.id = a.id;
优化思路二:该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询
SELECT * FROM student WHERE d > 2000000 LIMIT 10:
优化思路三:游标方式,适合单调递增的数据,每次查询时传入上次查询的末尾的值,需与前端一起配合,和方案二类似,但无法跳页查询
8. 字符串的前缀索引
如果不指定索引长度,则索引会包含整个字符串 index1,指定长度则为前缀索引 index2
mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6));
如果使用的是index1(即email整个字符串的索引结构),执行顺序是这样的:
- 从index1索引树找到满足索引值是’ zhangssxyz@xxx.com ’的这条记录,取得ID2的值;
- 到主键上查到主键值是ID2的行,判断email的值是正确的,将这行记录加入结果集;
- 取index1索引树上刚刚查到的位置的下一条记录,发现已经不满足email=’ zhangssxyz@xxx.com ’的
条件了,循环结束。
这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。
如果使用的是index2(即email(6)索引结构),执行顺序是这样的:
- 从index2索引树找到满足索引值是’zhangs’的记录,找到的第一个是ID1;
- 到主键上查到主键值是ID1的行,判断出email的值不是’ zhangssxyz@xxx.com ’,这行记录丢弃;
- 取index2上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出ID2,再到ID索引上取整行然
后判断,这次值对了,将这行记录加入结果集; - 重复上一步,直到在idxe2上取到的值不是’zhangs’时,循环结束。
也就是说使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。前面已经讲过区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少。
注意:使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是在选择是否使用前缀索引时需要考虑的一个因素
9. 索引下推
在二级索引或联合索引中,判断条件中如果还有对应索引中字段,则先判断再回表
select * from student where name > 'a' and sno like '%20'; -- index(name, sno)
上述查询会通过联合索引找到 name > 'a’的数据的同时判断 sno like ‘%20’,之后再进行回表;如无icp(索引下推),则找到 name > 'a’的数据就会回表,查出数据后再判断sno like ‘%20’
10. 普通索引和唯一索引
普通索引和唯一索引应该怎么选择?其实,这两类索引在查询能力上是没差别的,主要考虑的是对 更新性能
的影响。所以,建议 尽量选择普通索引
,但要考虑业务正确性优先
10.1 更新过程
为了说明普通索引和唯一索引对更新语句性能的影响这个问题,介绍一下change buffer。
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer中
,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将change buffer中的操作应用到原数据页,得到最新结果的过程称为 merge
。除了 访问这个数据页
会触发merge外,系统有 后台线程会定期 merge
。在 数据库正常关闭(shutdown) 的过程中,也会执行merge操作。
如果能够将更新操作先记录在change buffer,减少读磁盘
,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够 避免占用内存
,提高内存利用率。唯一索引的更新就不能使用change buffer ,实际上也只有普通索引可以使用
10.2 使用场景
首先, 业务正确性优先
。我们的前提是“业务代码已经保证不会写入重复数据”的情况下,讨论性能问题。如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。
然后,在一些“ 归档库
”的场景。比如,线上数据只需要保留半年,然后历史数据保存在归档库。这时候,归档数据已经是确保没有唯一键冲突了。要提高归档效率,可以考虑把表里面的唯一索引改成普通索引
innodb_change_buffer_max_size
:Change Buffer的最大大小,默认为25%的缓冲池大小;innodb_change_buffering
:Change Buffer的开启状态,默认为all,表示所有插入、更新和删除操作都会使用Change Buffer;
11. 其它查询优化策略
11.1 EXISTS 和 IN 的区分
遵守小表驱动大表
select * from a where cc in (select cc from b) -- a表比b表大
select * from a where exist(select cc from b where b.cc = a.cc) -- a表比b表小
11.2 count(具体字段)的使用
使用innodb存储引擎的情况下,如果使用count(具体字段)要尽量选择二级索引,因为主键是聚簇索引,需要把所有数据加载到内存中。
11.3 关于select *
- MySQL在解析的过程中,会通过
查询数据字典
将*
按序转换为列名 - 无法使用
覆盖索引
11.4 多使用commit
commit所释放的资源:
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer 中的空间
- 管理上述3种资源中的内部花费
12. 推荐的主键策略
非核心业务
:对应表的主键自增ID,如警告、日志、监控等信息
核心业务
:主键设计至少应该是全局唯一且是单调递增的
最简单的主键设计:有序的UUID,将UUID时间高位与低位交换,且去除无意义的“-”字符串
UUID = 时间 + UUID版本(16字节) - 时钟序列(4字节) - MAC地址(12字节)
MySQL8.0提供的uuid_to_bin函数实现上述功能
select uuid_to_bin(uuid(), true);
在当今的互联网环境中,非常不推荐自增ID作为主键的数据库设计。更推荐类似有序UUID的全局唯一的实现。
另外在真实的业务系统中,主键还可以加入业务和系统属性,如用户的尾号,机房的信息等。这样的主键设计就更为考验架构师的水平了。