MySQL中的索引及索引的优化

一、简介

1、索引的作用

  索引是指帮助 MySQL 高效获取数据的数据结构。可以说索引是排好序的快速查找的数据结构。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  我们平常所说的索引，如果没有特别指明，都是指 B+ 树（多路搜索树，并不一定是二叉的）结构的索引。其中聚集索引、次要索引、覆盖索引、复合索引，前缀索引、唯一索引默认都是使用 B+ 树索引，统称为索引。当然除了 B+ 树这种类型的索引之外，还有哈希索引。

2、索引和数据表之间的关系

  为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运行二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。

二、索引的优劣

1、优势

  ①索引类似于大学图书馆建书目索引。提高数据检索的效率，降低数据的 IO 成本。
  ②通过索引列队数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。
  ③创建索引可以提升 where 和 order by 的效率。

2、劣处

  ①实际上索引的创建索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
  ②虽然索引大大提高了查询的速度，同时却会降低更新表的速度，如果对表进行 insert，update、delete，因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
  ③索引只是提高效率的一个因数，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引或优化查询

三、索引的分类

1、按字段分：

  ① 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  ② 复合索引：即一个索引包含多个列。
  ③ 唯一索引：索引列的值必须唯一，但允许有空值。

2、按结构分：

  ① BTree 索引
  ② Hash 索引
  ③ full-text 全文索引
  ④ R-Tree 索引

四、BTree 索引的结构

  一颗 B+ 树，浅蓝色的块我们称之为一个磁块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）如磁盘块 1 包含数据项 17 和 35，包含指针 p1，p2，p3。p1 表示小于 17 的磁盘块，p2 表示在 17 和 35 之间的磁盘块，p3 表示大于 35 的磁盘。真实的数据存在叶子的节点即 3、5、9、10 等等。非叶子节点只不存储真实的数据，只存储指引搜索方式的数据项，如 17、35 并不真实存在于数据表中。

五、索引的创建

1、创建索引

  方法一：

create [unique] index 索引名 on 表名(字段列表);  

  方法二：

alter 表名 add [unique] index [索引名] on (字段列表);  

2、查看索引

show index from 表名;

3、删除索引

drop index 索引名 on 表名;

六、创建索引的注意事项

1、什么时候需要创建索引

  ① 主键自动建立唯一索引。
  ② 频繁作为查询条件的字段应该创建索引。
  ③ 查询中与其他表关联的字段，外键关系建立索引。
  ⑥ 单键/组合索引的选择问题（在高并发下倾向创建组合索引）。
  ⑦ 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  ⑧ 查询中统计或分组字段建立索引。

2、什么时候不需要创建索引

  ① 表的记录太少。
  ② 经常增删改的字段。
  ③ 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引。
  ④ 数据重复且分布平均的表字段不建立索引，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  
  如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  假如一个表有 10w 行记录，有一个字段 A 只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表 A 字段建立索引一般不会提高数据库的查询速度。
  索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有 2000 条记录，表索引列有 1980 个不同的值，那个这个索引的选择性是 1980/2000=0.99。一个索引的选择性越接近于 1，这个索引的效率就越高。

七、性能分析

1、MySQL Query Optimizer

  1）MySQL 中有专门负责优化 select 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划。
  2）当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 select 并转发给 MySQL Query Optimizer，MySQL Query Optimizer 首先对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成变量值，并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后在得出最后的执行计划。

2、MySQL 常见瓶颈

  1）CPU：CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。
  2）IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候。
  3）服务器硬件的性能瓶颈：top，free，iostat 和 vmstat 来查看系统的性能状态。

3、Explain