MySQL索引优化与查询优化（二）

智慧包
111 阅读2分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路

本文参考自宋红康老师的课程

案例：假设index(a,b,c)

where语句索引是否被使用
where a = 3使用到a
where a = 3 and b = 5使用到a，b
where a = 3 and b = 5 and c = 4使用到a，b，c
where b = 3、where b = 3 and c = 4、where c = 4——
where a = 3 and c = 5使用到a；b中断，c无法使用
where a = 3 and b > 4 and c = 5使用到a，b；c在范围后，无法使用
where a is null and b is not null使用到a
where a <> 3——
where abs(a) = 3——
where a = 3 and b like 'kk%' and c = 4使用到a，b，c
where a = 3 and b like '%kk' and c = 4使用到a
where a = 3 and b like 'k%kk%' and c = 4使用到a，b，c

建议：

  • 对于单列索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引时，当前query中过滤性最好的字段在索引字段顺序中越靠前越好
  • 在选择组合索引时，尽量选择包含当前query的where子句中更多字段的索引
  • 在选择组合索引时，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后

3.关联查询优化

3.1 左(右)外连接

被驱动表条件索引可以避免全表扫描。

3.2 内连接

inner join时MySQL自动选择驱动表。如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会作为被驱动表，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。小表驱动大表。

小表：

两个表按照各自的条件过滤，过滤完成后，计算参与join的各个字段的总数据量，数据量小的表就是小表，应该作为驱动表。

3.3 小结

  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN的字段，数据类型保持绝对一致
  • LEFT JOIN时，选择小表作为驱动表，大表作为被驱动表，减少外层循环的次数
  • INNER JOIN时，MySQL会自动将小结果集的表选为驱动表
  • 能够直接多表关联的尽量直接关联，不用子查询，减少查询的趟数
  • 衍生表建不了索引

4.子查询优化

子查询执行效率不高的原因：

  • 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量慢查询。
  • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，查询性能会受到一定的影响。
  • 对于返回结果集比较大的子查询，查询性能的影响也就越大。

**可以使用连接查询（JOIN）替代子查询。**尽量不要使用NOT IN或NOT EXISTS，用LEFT JOIN XXX ON XX WHERE XX IS NULL替代。

5.排序优化

在MySQL中，支持两种排序方式，分别是FileSort和Index。

  • Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
  • FileSort排序中，一般在内存中进行排序，占用CPU较多，如果待排结果较大，会产生临时文件IO到磁盘进行排序的情况，效率极低

在where和order by子句中使用索引，目的是在where子句中避免全表扫描，在order by子句中避免使用FileSort排序。但是某些情况下全表扫描或FileSort排序不一定比索引慢。总的来说，还是要避免。

order by中索引失效的情况：

INDEX a_b_c(a,b,c)

  • 排序规则不一致

    ORDER BY a ASC,b DESC,c DESC

  • 索引丢失

    WHERE g = const ORDER BY b,c

  • 存在非索引部分

    WHERE a = const ORDER BY a,d

5.1filesort算法

双路排序（慢）

MySQL4.1前使用双路排序，两次扫描磁盘。读取行指针和order by列，进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序（快）

从磁盘中读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，避免了第二次读取数据，把随机IO变成了顺序IO。

存在的问题：

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的大小超出了sort_buffer容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp文件，多路合并），排完再取，再排……

优化策略：

  1. 尝试提高sort_buffer_size

    根据系统的能力去提高，这个参数是针对每个进程的1M-8M之间的调整。MySQL5.7，InnoDB存储引擎默认1MB。

  2. 尝试提高max_length_for_sort_data

    提高这个参数会增加用改进算法的概率，默认1024字节。但是如果设置的太高，数据总容量超出sort_buffer_size的概率就增大。如果需要返回的列的总长度大于max_length_for_sort_data，就使用双路算法，否则使用单路算法。（1024-8192字节之间调整）

  3. order by时，select*是大忌，最好只query需要的字段

avatar
文章
阅读
粉丝