logoZeroDot618

Mysql 索引优化解决方案

1. 索引介绍

1.1 什么是 MySQL 的索引

  • 索引是帮助 MySQL 高效获取数据的数据结构。
  • 索引是 MySQL 中满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现高级查找算法来快速查找数据。
  • 简单理解：排好序的可以快速查找数据的数据结构。

1.2 索引的数据结构

下图是一种可能的二叉树的索引方式

二叉树

二叉树数据结构的弊端：当极端情况下，数据递增时，会一直向右插入，形成链表，查询效率会降低。

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

1.3 索引优势

  • 提高数据检索的效率，降低数据库的 IO 成本
  • 通过索引对数据进行排序，降低数据排序的成本，降低 CPU 的消耗

1.4 索引劣势

  • 索引实际也是一张表，保存主键和索引的字段，并且指向实体表的记录，所以索引也需占用空间。
  • 索引在大大提高查询速度的同时，却会降低表的更新速度，在对表进行数据增删改的同时，MySQL不仅需要更新数据，还更新索引文件。
  • 每次更新添加了的索引列的字段，都会去调整因为更新带来的减值变化后的索引的信息。

1.5 索引使用场景

哪些情况需要创建索引：

  1. 主键自动奖励唯一索引
  2. 频繁作为查询条件的字段应该创建索引(where 后面的语句)
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 多字段查询下倾向创建组合索引
  5. 查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度
  6. 查询中统计或者分组字段

哪里情况不推荐建立索引：

  1. 表记录太少
  2. 经常增删改的表
  3. Where 条件里用不到的字段不建立索引

1.6 索引分类

1.6.1 主键索引

  1. 表中的列设定为主键后，数据库会自动建立主键索引

  2. 单独创建和删除主键索引语法

    • 创建主键索引语法： alter table 表名 add primary key (字段);
    • 删除主键索引语法： alter table 表名 drop primary key;

1.6.2 唯一索引

  1. 表中的列创建了唯一约束时，数据库会自动建立唯一索引。
  2. 单独创建和删除唯一索引语法：
    • 创建唯一索引语法：alter table 表名 add unique 索引名(字段); 或 create unique index 索引名 on 表名(字段);
    • 删除唯一索引语法：drop index 索引名 on 表名;

1.6.3 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引。

  1. 建表时可随表一起建立单值索引
  2. 单独创建和删除单值索引：
    • 创建单值索引：alter table 表名 add index 索引名(字段); 或 create index 索引名 on 表名(字段);
    • 删除单值索引： drop index 索引名 on 表名;

1.6.4 复合索引

即一个索引包含多个列：

  1. 建表时可随表一起建立复合索引
  2. 单独创建和删除复合索引：
    • 创建复合索引：create index 索引名 on 表名(字段1，字段2); 或 alter table 表名 add index 索引名(字段1，字段2);
    • 删除复合索引：drop index 索引名 on 表名;

2. 性能分析

2.1 MySQL 常见瓶颈

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

2.2 Explain

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

  1. 表的读取顺序
  2. 哪些索引可以使用
  3. 数据读取操作的操作类型
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

EXPLAIN 关键字使用起来比较简单： explain + SQL 语句。

3. 查询优化

3.1 索引失效

  1. 最佳左前缀法则：如果索引了多列，要遵循最做前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
  2. 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
  3. 存储引擎不能使用索引中范围条件右边的列。
  4. MySQL 在使用不等于时无法使用索引会导致全部扫描。
  5. is null 可以使用索引，但是 is not null 无法使用索引。
  6. like 以通配符开头会使索引失效导致全表扫描。
  7. 字符串不加单引号索引会失效。
  8. 使用 or 连接时索引失效。

建议：

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

3.2 排序优化（分组优化与排序优化类似）

  1. 尽量避免使用 Using Filesort 方式排序。
  2. order by 语句使用索引最左前列或使用 where 子句与 order by 子句条件组合满足索引最左前列。
  3. where 子句中如果出现索引范围查询会导致 order by 索引失效。

3.3 关联查询优化

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

4 慢查询日志

4.1 慢查询日志简介

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

4.2 慢查询日志使用

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

查看是否开启：show variables like '%show_query_log%';

开启日志：set global show_query_log = 1;

设置时间：set global long_query_time = 1;

查询时间：show variables like 'long_query_time%';

查看超时的 sql 记录日志： MySQL 的数据文件夹下，例：Mysql5.5\Data\设备名称-slow.log

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