MySQL学习Day23——索引优化与查询优化

SQL查询优化的技术有很多，大方向可分为物理查询优化和逻辑查询优化

物理查询优化:通过索引和表连接方式等技术来进行优化;

逻辑查询优化:通过SQL等价变换提升查询效率

一、索引失效案例:

是否使用索引是由优化器决定，优化器基于cost开销而不是规则和语义。SQL语句是否使用索引跟数据库版本、数据量、数据选择度都有关系。

1.全值匹配我最爱

2.最左前缀法则:

在MySQL建立联合索引时会遵循最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。MySQL可为多个字段创建索引，一个索引可以包含16个字段，对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中的第一个字段时，多列(或联合)索引不会被使用。

3.主键插入顺序:

向表中插入数据时最好保证插入记录的主键值是依次递增的，这样就不会导致性能的损耗。为了避免性能损耗，最好让插入的记录的主键值依次递增。最好让主键具有AUTO_INCREMENT,让存储引擎自己为表生成主键，而不是手动插入。

4.计算、函数、类型转换(自动或手动)导致索引失效

对索引进行表达式计算、调用函数会导致索引失效，这是因为需要把索引的全部字段都取出来，让然后依次进行表达式或函数的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢得多。同时，索引字段如果在过滤条件中发生类型转换也会导致索引的失效。

5.范围条件右边的索引失效:创建联合索引时位于范围查找字段右边的字段上面的索引无法被使用，因此创建联合索引中务必把涉及到的范围字段写到最后。

6.不等于索引失效

7.IS NULL可以使用索引，IS NOT NULL无法使用索引:最好在设计数据表就应该将字段设置为NOT NULL约束，同理在查询中使用NOT LIKE也无法使用索引。

8.LIKE以通配符%开头索引失效:在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为%，索引就不会起作用，只有%不在第一个位置索引才会起到作用。

9.OR前后存在非索引的列，索引失效:在WHERE子句中如果在OR前的条件列进行了索引而在OR后面的条件列没有进行索引，那么索引就会失效。OR前后的两个条件的列都是索引时查询中才能使用索引。

10.数据库和表的字符集统一使用UTF8MB4:统一使用utf8mb4兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要转换导致索引失效。

一般性建议:

1.对于单列索引，尽量选择针对当前query过滤性更好的索引 ；

2.在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中位置越靠前越好；

3.在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引；

4.在选择组合索引时，如果某个字段可能出现范围查询时，尽量把这个字段写在索引次序的最后面；

二、关联查询的优化

1.左外连接:在连接条件上带有索引会提高连接的效率

2.内连接:对于内连接来说，查询优化器可以决定驱动表和被驱动表。被驱动表上的连接字段带有索引可以使得连接效率更高。如果表的连接条件上只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表。如果两个表的连接条件都存在索引，那么会选择数据量小的表作为驱动表。

3.JOIN语句原理:

JOIN方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5之前只支持一种表间关联方式，就是嵌套循环。如果关联表的数据量很大，则JOIN关联的执行时间会非常长。在5.5之后MySQL通过引入BNLJ算法来优化嵌套执行。

(1)简单嵌套循环连接(Simple Nested-Loop Join)

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

(2)Index Nested-Loop Join(索引嵌套循环连接)

主要思路是为了减少内层表数据的匹配次数，要求被驱动的表上必须有索引才可以。通过外层表匹配条件直接与内存表索引进行匹配，避免和内层表的每条记录去进行比较，可以极大地减少对内层表的匹配次数。驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是固定的，因此都倾向于使用记录数少的表作为驱动表。如果被驱动表加索引，效率是很高的，但如果索引不是主键索引，还得进行一次回表操作。

(3)Block Nested-Loop Join(块嵌套循环连接) 

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer级冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和joinbufer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循中的多次比较合并成一次，降低了被驱动表的访问频率。

(4)整体效率比较:INLJ>BNLJ>SNLJ

(5)永远使用小结果集驱动大结果集，大小的度量 = 表行数 * 每行的大小

(6)为被驱动表匹配的条件增加索引(减少内存表的循环匹配次数)

(7)增大join buffer的大小(一次缓存的数据越多，那么内层表的扫描次数越少)

(8)减少驱动表不必要的查询字段(字段越少，join buffer所缓存的数据越多)

三、子查询优化与排序优化

1.子查询执行效率不高的原因:

(1)执行子査询时MySQL需要为内层查询语句的査询结果建立一个临时表，然后外层査询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

(2)子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

(3)对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中可以用JOIN查询代替子查询，连接查询不需要建立临时表，其速度比子查询要快。

四、排序优化:

在MySQL中，支持两种排序方式分别是FileSort和Index排序。Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。FileSort 排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

1.优化建议:

(1).SQL中可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。

(2).尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。

(3).无法使用Index时，需要对FileSort方式进行调优。

2.FileSort算法:双路排序和单路排序

(1)双路排序(慢):MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对它们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

(2)单路排序(快):从磁盘读取查询需要的所有列，并且按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中。

对单路排序的优化策略:

(1)尝试提高sort_buffer_size:不管用哪种算法提高这个参数都会提高效率。在提高这个参数时应该注意要根据系统的能力去提高，因为这个参数是针对每个进程(connection)的1M-8M之间进行调整的。在MySQL5.7中InnoDB中sort_buffer_size默认值是1048576字节，即1MB。

(2)尝试提高max_length_for_sort_data:提高max_length_for_sort_data会增加用改进算法的概率,但是如果max_length_for_sort_data太高，数据总容量超出sort_buffer_size的概率就增大，会明显导致较高的磁盘IO使用情况和较低的处理器使用率。因此，如果需要返回的列的总长度大于max_length_for_sort_data，就应该使用双路算法，否则使用单路算法。

(3)Order by时最好只查询Query需要的字段，避免select *