MySql-高级(索引优化分析) 学习笔记

尚硅谷-MySql-高级思维导图：思维导图（mmap+HTML格式）

1. 前言

• 为什么系统性能下降（ sql 执行慢、执行时间长、等待时间长）
  1. 数据过多 —— 当单表数据量快要达到 500 万条瓶颈时，分库分表
  2. 关联的表太多（使用太多 join） —— SQL 优化
  3. 没有充分利用索引 —— 优化索引建立
  4. 服务器调优及呵呵参数设置 —— 调整 my.cnf
• 优化索引是效果较好的，最重要的。

2.简单回顾多表查询

3.索引介绍

3.1 概念

索引是帮助 MySQL高效获取数据的结构，可以理解为，排好序的快速查找数据结构

3.2 优势

1. 查询速度快
2. 排序速度很快

3.3 劣势

1. 降低更新表的速度
2. 要保存索引结构，所以所需空间更大（索引存在硬盘中）

4.索引结构

4.1 B Tree索引

• 每个节点要存放：
  1. 数据
  2. 指向下一节点的指针
  3. 指向数据的指针

4.2 B+Tree索引

• 非叶子节点要存放：
  1. 下一节点的最小值
  2. 指向下一节点的指针
• 叶子节点要存放：
  1. 具体的数据

4.3 MySQL选择的索引结构

• B+Tree
• 原因：B+Tree 较 B Tree 而言结构所需空间小。索引加载 IO 的次数可以相对少一些，从而效率高一些。

4.4 索引的分类

1. 单值索引

   • 一个索引只包含单个列，一个表可以有多个单列索引

2. 唯一索引

   • 索引列的值必须唯一，可以为空（值为空也只能出现一次）

3. 主键索引

   • 列设置为主键后会自动创建

4. 复合索引

   • 针对多个字段建立索引

4.5 基本语法

1. 创建

   create 【unique】 index 【索引名】 on 表名(列名, 【列表……】);
   

   或者

   alter 表名 add 【unique】 index 【索引名称】 on (列名, 【列表……】);
   

2. 查看

   show index from 表名 【\G】;
   

3. 删除

   drop index 【索引名称】 on 表名;
   

4.6 建立索引的情况

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表的关联的字段，如：外键关系建立索引
4. 组合索引性价比更好
5. 查询中，排序的字段
6. 查询中，统计或分组的字段（分组比排序更伤性能：因为执行分组时会，自动先排序在分组）

4.7 不需要建立索引的情况

1. 表中的记录条数很少（小于几百条）
2. 经常增、删、改 的表或字段
3. where 条件里用不到的字段
4. 过滤性不好的不适合建索引（例如，字段中只有几种取值）

5 性能分析

5.1 MySQL Query Optimizer 优化器

1. 通过计算分析系统中的统计信息，提供最优的执行计划
2. 当客户端向 MySql 请求一条 Query 时，命令解析器模块会区分出 Select 并转发给优化器，优化器会对整条 Query 进行优化，将一些常量表达式的预算直接替换成常量值；并对查询条件进行简化和转换；再分析 Query 中的 Hint 信息，得出最后的执行计划

5.2 MySql 常见瓶颈

1. CUP：一般发生在将数据存入内存或从磁盘读取数据时
2. IO：一般发生在装入数据远大于内存容量时
3. 服务器硬件的性能瓶颈

5.3 Explain

1. Explain 是什么

   • 查看执行计划
   • 使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 时如何处理 SQL 语句的。进而可以分析 该语句或是表结构的性能

2. 作用

   • 表的读取顺序
   • 那些索引可以使用
   • 数据读取操作的操作类型
   • 那些索引被实际使用
   • 表之间的引用
   • 每张表有多少行被物理查询

3. 使用

   • Explain + SQL 语句
     例子：
     

   • 以上各个字段解释（ ★ 表示重要）

1. ★ id ★

表示 相关表的读取顺序，有三种情况：

• 若id相同，则读取顺序就是从上至下
• 若id不同，则读取顺序就是从大到小
• 若id既有不同又有相同的，则读取顺序就是先由大到小，一样的再从上到下
• 注意：每个 id 表示一趟独立的查询（id相同表示一趟），一个 SQL 语句 趟数越少越好

2. select_type

查询类型

• SIMPLE ： 简单的SELECT语句（不包括UNION操作或子查询操作）
• PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY）
• SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）
• DERIVED：在 From 列表中包含子查询，MySql 会递归这些子查询，并把结果放在临时表里
• UNION：UNION 操作中，第二个出现的 Select，若 Union 包含在 From 子查询中，则外层 Select 会被标记为 DERIVED
• UNIOIN RESULT： 从 UNIOIN 表中获取结果的 SELECT
• 百度的更详细的解读：mysql 查询优化 ~explain解读之select_type的解读

3. table

显示这一行的字段是关于那张表的

4. partitions

代表分区表中的命中情况，非分区表值为 null

5. ★ type ★

显示查询了何种类型；

最差 到 最好 依次为（至少要达到 range 级别）：
ALL< index < range < ref < eq_ref < const < system

• ALL: 扫描全表
• index: 使用了索引但是没有通过索引过滤，即只在 select 关键字后使用，并没有再 where 、order by 等筛选关键字后使用
• range: 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
• ref: 使用非唯一索引或非唯一索引前缀进行的查找。返回匹配某个单独值的所有行，它可能会找到多个符合条件的行。
• eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
• const： 只通过一次搜索就查到了
• system：表里只有一行记录

---

• index_merge： 在多个索引组合使用时，出现在有 or 的SQL中
• ref_or_null：在单个索引时，出现有 or 的SQL中
• index_subquery：利用索引来关联子查询，不在全表扫描
• unique_subquery：唯一子查询索引

6. possible_keys

可能会用的索引，但不一定是真正使用到的

7. key

实际使用的索引，若查询中使用到的覆盖索引，则该索引仅出现在 Key 列表中

8. ★ key_len ★

命中的索引的字节长度（越大越好），通常用于判断是否复合索引被完全使用。如果索引字段内容可以为空，mysql会用一个字节标识，即长度会+1；用两个字节标识varchar（可变长度），即长度会再+2。

9. ref

显示索引的哪一列被使用了。哪些列或者常量被用于查找索引列上的值

10. ★ rows ★

整个 SQL 物理扫描的行数（越少越好 ）

11. filtered

12. ★ Extra ★

额外信息
-----------坏的-----------

• Using filesort ：MySql 会使用一个外部的索引排序，而不是按照索引的顺序进行读取。MySql 无法利用索引完成的排序操作称为“文件排序”
• Using temporary ：使用临时表保存中间结果，MySql 在对查询结果排序时用到了临时表。常见与 Order By 与 Group By。（需要按照 Group By 后的字段顺序、个数建索引）
• Using join buffer：多表关联，使用了连接缓存
• impossible where：where子句永远为false，不能用来查找元素，可以理解为where写错了
  -----------好的-----------
• Using where ：使用了 Where 进行过滤
• Using index ： 使用了覆盖索引，此次查询不读取原表，直接从索引中获取，也即不需要回表查询。
• select tables optimized away：使用优化器

6 索引使用情况分析

1. 单表索引的失效的情况

1. where、order by、group by 中字段使用顺序与索引顺序不一致

• where 后的多个条件建立索引后，不论怎样改变顺序，都会用上索引，因为 MySQL 优化器会改变执行顺序。
• 跳过索引中间的字段，索引失效
• where 、order by、group by 后使用的字段建立组合索引，如果使用索引顺序与索引顺序不一致，则会索引失效
• 若有 age 的单独索引 又有 age 、name、dept的组合索引，在查询时只有 age、name字段时，只会命中 age的单独索引。

总结上述情况：最佳左前缀法（指的是查询从索引的最左列开始，并且不跳过索引中的列），因为 组合索引的结构是：按照建立索引的多个字段（A，B，。。。）的顺序，先建立 第A字段的 索引树，A字段的树中的每一个节点各自对应一个B字段的索引树，以此类推。所以当 SQL 中缺少组合索引之一 就不能够完成索引，也就不会执行该组合索引。

2. 索引字段使用 ‘函数、运算、类型转换’ 会使索引失效

• 在索引字段使用 函数、运算、类型转换 会使索引失效，索引筛选筛选条件尽量少用
• 补充上一点，如果 在匹配时 出现 等号后的值的类型与字段类型不一致，会自动进行类型转换，也会索引失效
• 使用 like 模糊匹配时，如果开头有 % ，例：like “%abc%”，也会使索引失效，因为索引树建立是按照首字母，若用 % 匹配前面的字符，则会失效

3. 进行范围查询字段右边的字段索引失效

• 虽然 三个筛选条件建立了组合索引，但是只用到了两个
• 在 范围查询的右边（按照建立索引时的顺序来说） 的所有索引失效
• 所以在建立索引时 范围查询的字段要放最后

4. 字段 使用 不等于 ，索引失效

1. 筛选条件有 不等于

• 没建立索引时，未使用索引
  
  
• 建立索引，依旧没有使用索引
  
  

5. 字段使用 is null / is not null

6. 总结

• 以下建立索引的顺序 为：（a，b，c）
  

2. 多表关联索引分析

1. Left join 驱动表索引失效

• 前面的表（class）是驱动表，后面的表（book）是被驱动表
• 给驱动表建立索引同样会全表扫描，优化效果有限
• 给被驱动表建索引才能大幅优化
• 小表尽量做驱动表，大表尽量做被驱动表

2. Inner Join MySql 会优化两表顺序

1. 两表内连接查询

• 没有建索引
  
  

• 建立索引
  
  

2. 总结

• 使用内连接时，mysql 自己选择哪一个表是被驱动表
• 优先选择有索引的表为被驱动表

3. 总结

1. 保证被驱动表的join字段已经被索引
2. left join 时一定是左边是驱动表，右边是被驱动表，所以选择小的表放在驱动表位置，大的放在被驱动表位置（左连接建右表、右连接建左边），永远用小表驱动大表
3. inner join 时，MySQL会自己把小结果集（即添加索引的表）的表选为被驱动表
4. 子查询尽量不要放在被驱动表位置，可能导致用不上索引
5. 优先优化内层查询
6. 能够直接多表关联，尽量不使用子查询