Mysql索引优化学习笔记

1. 什么是mysql索引：

​ 索引是帮助MySQL高效获取数据的数据结构

2.索引的数据结构：

​ MySQL中常用的的索引数据结构有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存储引擎)等等。

3.索引分类

• 主键索引
• 唯一索引
• 单值索引
• 复合索引

4.explain关键字

​ 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。可以用来分析查询语句或是表的结构的性能瓶颈。其作用:

1）表的读取顺序

2）哪些索引可以使用

3）数据读取操作的操作类型

4）那些索引被实际使用

5）表之间的引用

6）每张表有多少行被优化器查询

explain + SQL语句查询出来的结构如下：

5.MySQL常见瓶颈

​ SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈。实例内存满足不了缓存数据或排序等需要，导致产生大量的物理IO。查询数据时扫描过多数据行，导致查询效率低。

6.查询优化

6.1 索引失效

1）最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

2）不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。

3）存储引擎不能使用索引中范围条件右边的列。

4）Mysql在使用不等于时无法使用索引会导致全表扫描。

5）is null可以使用索引，但是is not null无法使用索引。

6）like以通配符开头会使索引失效导致全表扫描。

7）字符串不加单引号索引会失效。

8）使用or连接时索引失效。

​ 建议：1.对于单值索引，尽量选择针对当前查询字段过滤性更好的索引。2.对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。3.对于组合索引，尽量选择能够包含在当前查询中where子句中更多字段的索引。4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

6.2 单表查询优化

​ 可以通过explain分析出添加索引的方式，老师举例为：把原来的3个字段的联合索引，减少为2个字段。因为在当前查询语句中另一个字段使用了范围条件，会导致最后一个索引失效。

6.3 关联查询优化

​ 要点：内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。

上面的理解：因为外连接查询的话，肯定有一个表是要全表查询的，这个不可避免，在这个表添加索引意义不大，所以往往在另一个表添加索引。

6.4 排序优化、分组优化

1. 尽量避免使用Using FileSort方式排序。（这个体现在查询结果的Extra下）

2. order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。

3. where子句中如果出现索引范围查询会导致order by索引失效。

7. 慢查询日志

1. 简介

​ MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

​ 查看超时的sql记录日志：Mysql的数据文件夹下 —slow.log

2. 日志的使用

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数。

查看是否开启：show variables like ‘%slow_query_log%’;

开启日志：set global slow_query_log = 1;

设置时间: set global long_query_time = 1;

查看时间: SHOW VARIABLES LIKE ‘long_query_time%’;

查看超时的sql记录日志：Mysql的数据文件夹下

注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。