Mysql索引优化

单表优化案例

多表优化案例

三表连接也是两个表两个表地连接。与双表差不多。以左连接为例，最左边的表一定都有的。主要需要确定右边表两表的搜索行，所以可以将索引建立在靠右的两个表中。

一、遵循最左前缀匹配原则

上述索引索引失效的原因：复合索引是将各个索引字段做字符串连接后作为key，使用时将整体做前缀匹配。就是最左前缀匹配（最左前缀法则）。如果索引了多列，要遵守最左前缀法则。比如(a,b,c)的时候，B+树是按照从左到右的顺序来建立搜索树的，比如(where a=? and b=? and c=?)当有数据来检索时会先比较a列来确定下一步的搜索方向，如果a列相同再一次比较b列和c列。最后得到检索的数据。上面（where b=? ）和（where c=?）和（where b=? and c=？）这三个例子均没有a列数据，B+树连第一步都无法比较,无法确定，致使索引全部失效。当我们为其加上a列数据的时候，如下：

部分使用索引的情况：

 

 当表中字段全部在索引列中时，即使不遵守最左匹配原则，仍然会使用索引。具体如下（删除字段d）：

上面id是天生的主键索引，(a,b,c)为复合索引。也就是说表中的全部字段都充当索引。

二、不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换）

还是先排除表中字段全在索引列上的情况，这种情况下还是会用到索引的。如下：

排除上面情况验证：

特别的：一定要严格按照索引的类型查询，否则可能导致索引失效。比如字符串类型不加单引号，Mysql内部会做自动类型转换

三、存储引擎不能使用索引中范围条件右边的列（范围以后全失效） 

针对上述例子因为B+树结构，先比较出a，根据a比对完进行下一步b的比较，b现在是一个范围值，可以用到b再检索到所有符合b这个范围中的数据，再检索到c就无法用到索引了，因为b本身就没有确定，那么B+树再跟据c确定节点位置的时候，它不知道往那个方向走。所以只能用到a，b

四、like可能导致索引失效

其实就是看like后面是不是以常量开头，如果以常量开头，就可以走索引，反之则不行：

is null和is not null导致索引失效

上面介绍了一些查询时候的索优化，下面主要针对排序

排序优化

首先如果order by 字段不是索引字段。那肯定不会走索引排序。如下：

如果不在索引列上，就会产生filesort，其有两种算法：

1. 双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出从磁盘取排序字段，sort_buffer进行排序，再从磁盘取其他字段。（其中sort_buffer是Mysql为排序开辟的内存空间。这块内存的大小在另外一个参数中体现：sort_buffer_size）

   

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

以上面的表为例：

双路排序过程:

1. 从索引 class_id找到第一个满足 class_id = 1 的主键id
2. 根据主键 id 取出整行，取出字段 student_name 和主键 id的值，将这两个字段放到 sort buffer(排序缓存) 中
3. 从索引 class_id 取下一个满足 class_id= 1 记录的主键 id
4. 重复 2、3 直到不满足 class_id = 1
5. 对 sort_buffer 中的字段 student_name 和主键 id 按照字段 student_name 进行排序
6. 遍历排序好的 id 和字段 student_name ，按照 id 的值回到原表中取出所有字段的值返回给客户端

单路排序过程：

1. 从索引class_id找到第一个满足 class_id =1 条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，并存入 sort_buffer(排序缓存)中
3. 从索引class_id找到下一个满足 class_id=1条件的主键 id，直到不满足class_id = 1
4. 对 sort_buffer 中的数据按照字段 student_name 排好序，并返回结果给客户端

这种单路排序算法有一个问题，就是比较占用sort_buffer的空间，因为单路是把所有字段都取出放进sort_buffer中,如果单行长度过大且需要返回的记录数很多，就容易造成数据的总大小超出sort_buffer的容量，这样的话就需要借助外存。每次只能取sort_buffer容量大小的数据，进行排序〈创建临时文件文件，多路合并），排完再去取sort_buffer容量大小，再排……从而多次I/O。本来想省一次IO操作，反而导致了大量的I/O操作，反而得不偿失。

这种情况下：

1. 可以增大sort_buffer_size参数的设置，扩大sort_buffer的空间。这个sort_buffer_size是connection级别的参数，在每个connection第一次使用sort_buffer的时候，以该值为基准一次性分配空间。这个参数也并非越大越好，由于是connection级别的，所以过大的设置+高并发可能会耗尽系统内存资源。
2. 增大max_length_for_sort_data参数的设置。这是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

所以我们应该尽量使用索引进行排序，避免使用filesort方式。但是如果是带排序列是索引字段仍需满足两种情况，才会走索引排序：

1. order by 使用索引最左前缀匹配原则
2. 使用Where子句与order by子句条件列组合满足索引最左前缀匹配原则

看视频时候的截图：

group by：

1. group by实质是先排序后进行分组，遵照索引建的最佳左前缀匹配
2. 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
3. where高于having，能写在where限定的条件就不要去having限定。

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL，这些SQL会被记录到慢查询日志中。默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置参数开启。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。下面时查看和开启慢查询日志的方式：

利用日志可以提取出慢sql，可以再结合explain来分析慢的原因，进行相应的调节。