索引优化学习笔记

listenFeng.

已于 2024-02-26 16:29:23 修改

阅读量521

点赞数 22

分类专栏： mysql 文章标签：学习笔记 mysql

于 2024-02-18 15:57:47 首次发布

本文链接：https://blog.csdn.net/listenFen/article/details/135975117

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文详细讨论了SQL查询优化的物理和逻辑层面，涉及索引策略、范围查询、JOIN操作的优化方法，如InnoDB和MyISAM表的ICP使用条件，以及如何通过覆盖索引和索引下推减少I/O操作，提升数据库查询效率。

摘要由CSDN通过智能技术生成

查询sql的优化有两个方面一个是物理查询优化，一个是逻辑查询优化
物理查询优化：是通过索引和表连接方式等技术来进行优化
逻辑查询优化：是通过 sql等价变换来提升查询效率，即换一种sql写法（列如将子查询转换为表连接的方式）

where条件优化(避免索引失效导致全表扫描)

对查询的字段进行计算、或者函数调用或者进行类型转换（包括比较的值和字段不是同一类型这样会进行隐式转换）这些需要先全表扫描对所有行进行处理后再比较，导致索引失效
虽然创建有联合索引但是查询字段不符合最左前缀原则则联合索引失效（特殊情况就是查询条件不符合最左前缀原则，但是select的字段正好只包含联合索引剩下的字段，那么优化器也会使用索引，这种情况叫索引覆盖）
创建有联合索引idx_a_b_c,查询时abc三个字段条件都有，但是b字段为范围查询，那么c则不会走索引需要全表扫描（MySQL的B-Tree索引是按照索引列的顺序存储的。当你进行范围查询时，MySQL无法确定范围查询之后的列值的范围，因此无法继续使用索引，只能进行全表扫描）。
对字段进行不等于、NOT IN、NOT EXISTS或者is not null 的查询均会导致索引失效，因为索引是辅助查找确定的值，查询不等于的情况则需要全表扫描判断每行对应字段的值是否确实不相等（因此在创建字段时，条件允许的情况下尽量避免null值的出现，可以用空字符串或者0代替null值）
对字符串进行左模糊匹配，给字符串创建索引后如果是右模糊匹配则可以先依靠索引定位到符合要求的前缀字段，但是左模糊则需要全表扫描一个一个的匹配
当or的字段中有一个没有索引，则这个没有索引的字段就需要进行全表扫描，如果此时另一个字段有索引的话，也不会走索引查询，因为那样就是索引查一遍再全表查一遍，还不如直接全表扫一遍每次对两个字段进行比较来的简单，只有两个字段都有索引时才会走索引
多表联合查询时，驱动表需要全表扫描，被驱动表有索引则会走索引查询，而且在内连接查询时，如果两张表只有一个表中有索引，那么优化器会自动选择有索引的表作为被驱动表

join优化

尽量使用小结果集作为驱动表
被驱动表关联字段最好可以创建索引
对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
尽量不要使用is not null 或者 not in 的where条件，可以替换为left join xxxx on xx where xx is null的语句
增大join_buffer_size大小
减少不必要字段的查询

join的底层原理

join方式连接多个表，本质上就是各个表之间数据的循环匹配，在MySQL5.5之前只有一种表关联方式就是嵌套循环（Nested Loop Join）。这种方式在数据量很大的情况下会非常耗费时间。在MySQL5.5之后引入了BNLJ算法来优化嵌套执行。

SNLJ(Simple Nested-Loop-Join)

就是从表A中取出一条数据1，再遍历表B，将匹配到的数据放到result. 以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断。

INLJ(Index Nested-Loop-Join)

索引嵌套循环连接SNLJ的索引优化版，主要就是为了减少内表数据的匹配次数，使用条件是被驱动表的连接字段需要有索引，而且两个表的关联字段需要为同一类型，匹配时是拿着A表的数据先去和B表的索引去匹配，找到数据后在进行回表（非主键索引）,相比SNLJ就不需要A表的每条数据都要遍历一遍B表来匹配了。

BNLJ(Block Nested-Loop-Join)

块嵌套循环连接，INLJ无法使用的情况下使用的算法，SNLJ中每次匹配都需要一条一条的取出表中的数据到内存中再进行匹配，循环多次IO，为了减少被驱动表的IO次数，于是就出现了Block Nested-Loop-Join的连接方式。
先将驱动表的数据取出一部分放到内存中的join buffer缓冲区中，然后再对被驱动表进行一次全表扫描，将被驱动表数据放入内存中，和join buffer中的数据一次性完成所有数据的匹配，由于join buffer缓冲区有大小限制，受到驱动表查询和关联的字段数量和字段大小的限制（所以查询时尽量避免使用select *），所以可能join buffer缓冲区不能一次放下驱动表中的所有数据，则需要重复几次上述操作。相当于将SNLJ中的被驱动表的多次匹配合并成了一次，大大减少了IO次数，提高了连接效率
注：参数blook_netsted_loop默认为on,join_buffer_size默认为256k

统计

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数	1	1	1
内表扫描次数	A	0	A查询和关联的字段总大小/join_buffer_size+1
读取记录树	A+B*A	A+B表匹配数	AB(A查询和关联的字段总大小/join_buffer_size)
join比较次数	B*A	A*B表索引的层数	B*A
回表读取记录树	0	BB表匹配数（需要回表时）	0

Hash join

BNLJ在MySQL8.0.20后废弃，在MySQL8.0.18加入了Hash join 之后，此后都默认使用Hash join。Hash join相比BNLJ更加适用于大数据集的等值连接。
工作原理：优化器将两张表中数据量较小的表放入内存中作为散列表，sql语句中匹配使用的join key 作为散列表的key，再扫描另外一张表并探测散列表，找到匹配的数据。
相比之下nested loop适用于任何匹配条件，在使用索引时效率比hash join 要高，其他情况下数据越多hash join 的效率就越高，而且hash join 创建散列表需要消耗大量内存，但是减少了IO次数。

order排序优化（避免filesort）

在mysql中支持两种排序方式,分别是filesort和index两种排序方式

index排序中，索引可以保证数据的有序性，无需再次排序，效率比较高
filesort排序则是在内存中排序，需要占用cpu,如果数据量比较大还需要使用I/O来创建临时文件来排序，效率比较低

避免filesort

where条件中没有使用到索引，order by依然可以使用索引
当表中创建有联合索引idx_a_b_c，排序时需要按照最左前缀原则进行排序(如order by a,order by a,b,order by a,b,c)，才能使用索引,且排序字段必须同向不能一个升序另一个降序；
当sql即使使用索引也需要回表的情况下，如果where条件过滤后的数据量依然很大，那么优化器考虑到大数据量回表的开销则不会使用索引来排序，但是如果使用limit限制查询较少的条数，优化器预估使用索引即使回表依然比较快的情况下则选择使用索引排序（如果where条件过滤后的数据量比较小，优化器判断使用索引排序优化的效果也有限时，那么可能就直接filesort了）
当where条件和order by字段的索引需要二选一时，优先观察条件字段的过滤数量，如果过滤的数量足够多，而需要排序的数据并不多时，优先把索引放在条件字段上，反之，亦然。

filesort排序算法

filesort排序算法有两种双路排序和单路排序

双路排序

在MySQL4.1之前使用的都是双路排序，先从磁盘取出排序字段到buffer中进行排序，再从磁盘获取其他需要查询的字段，一次排序需要进行两次IO，所以说效率偏低

单路排序

针对双路排序进行了优化，直接一次取出所有需要查询的列到buffer中再进行排序，避免了第二次读取数据，并且把随机IO变成了顺序IO（在一个区内的数据页大概率是可以顺序读取的），提高了效率，但是这样就加大了内存的使用（当查询数据量过大时可能超过sort_buffer的大小则需要多次进行读取排序的过程），所以查询时尽量不去使用select *,如果无法使用索引排序时，也可以通过加大max_length_for_sort_data和sort_buffer_size和sort_buffer_size的大小来提高排序效率

group by优化

where条件中没有使用到索引，group by依然可以使用索引
group by先排序再分组，遵循索引的最左前缀原则
当无法使用索引列时，可以增大max_length_for_sort_data和sort_buffer_size的参数设置
where的效率高于having，能写在where里的就不要写在having里
group by前最好是先用where过滤掉一部分数据，保证数据在1000行以内，否则就会很慢

覆盖索引

一个索引满足查询结果的数据就叫做覆盖索引，即非聚簇联合索引中包含一个查询里select、join以及where的所有列就叫覆盖索引（覆盖索引时，之前列举的一些索引失效的情况，可能优化器判断也可以使用索引了，列如 idx_a_b_c,select * from t where a <> 100 这时是索引失效的，但是select a,b,c,id from t where a <> 100，索引是生效的，优化器判断所有的数据都在一个索引里不需要回表则可以使用索引来提高查询效率）

好处

避免了InnoDB的二次查询，一个索引包含了所有需要的信息就不需要再进行回表了
避免了随机IO，提高了查询效率。同一个索引内的数据大部分是连续存储的，大概率是顺序IO，而回表时数据位置不确定就是随机IO

索引下推（ICP）

索引条件推送（Index Condition Pushdown，简称ICP）是MySQL在5.6版本引入的一种查询优化策略。在没有ICP的情况下，MySQL在使用二级索引进行查询时，会先通过索引找到满足部分WHERE条件的记录，然后再回表，获取整行数据，最后再对整行数据进行剩余的WHERE条件判断。而在开启了ICP的情况下，MySQL会尽可能地将WHERE条件中可以通过索引判断的部分推送（下推）到存储引擎层，让存储引擎在读取索引的时候就进行条件判断，只有满足条件的记录才会回表，获取整行数据。列如：idx_a_b_c,select * from where a = 1 and b like ‘%XX’ 这个查询如果不使用ICP那么就是先使用索引查找a=1的数据然后回表再查找b b like ‘%XX’ 的数据，而使用ICP的话就是存储引擎会把 a = 1 和 b like ‘%XX’ 两个条件都放到索引中进行查找（先使用索引去查找a=1的数据再在索引中过滤b like '%XX’的数据），然后再进行回表，这样就减少了回表次数，减少了IO，提高了查询效率。