Mysql索引

索引的分类

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
  简单说就是索引结构上有数据。
• 非聚簇索引：不是聚簇索引，就是非聚簇索引

为什么要使用索引

(1) 索引能极大的减少存储引擎需要扫描的数据量
(2) 索引可以把随机IO变成顺序IO
(3) 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表

什么是B+tree?（Binary Search Tree）

二叉查找树

平衡二叉查找树（Balanced binary search tree）

• 会在数据的插入或删除的时候，通过一个平衡因子（旋转树的方式）去保证整个树的相对平衡
  (1) 相对平衡二叉树：某个节点的子节点的高度差不会超过1(不会出现一条腿特别长的情况)
  (2) 完全平衡二叉树：AVL树，整棵树的高度差都不会超过1(所有叶子节点高度差不超过1)

• 检索过程：一个节点就认为是在一个磁盘块的位置，一个节点包含了关键字（比如id），数据区（真正存储磁盘位置或内存地址的地方，通过这个地址的映射真正去表中加载数据的区域），子节点引用（有P1，P2两个指针地址），要加载的关键字和根节点的关键字进行比对，通过子节点引用快速进行顺序IO，指向左或右子节点，继续进行关键字的比对，直到命中关键字，然后加载数据区的数据。

• 平衡二叉查找树的缺点：
  (1) 它太深了：数据处的（高）深度决定着他的IO操作次数，IO操作耗时大
  (2) 它太小了：每一个磁盘块（节点/页）保存的数据量太小了没有很好的利用操作磁盘IO的数据交换特性（操作系统去硬盘中读取一次，交换的数据是4kb） 也没有利用好磁盘IO的预读能力（空间局部性原理：操作系统在读取文件头的4k以后，它会认为接下来的8k,16k或更多的数据也需要读取），从而带来频繁的IO操作
  注意：MySQL一次性加载16k，相当于一次性读操作系统的4页。

多路平衡查找树（B-树）

特点：

(1) 所有子节点全部在同一层，新增一条数据时，节点的关键字个数超过路数时，由下向上分裂增加高度。

(2) 每个节点的关键字的个数最多有（m路-1）个，MySQL路数=16k * 1024/索引字段的数据类型的字节长度。(问题：考虑数据的情况怎么算？)

(3) 定义数据类型的长度时，能短则短，否则冗余的字节数会拖垮整个路数，使得IO开销增大，性能下降。

很多关系型数据库选择B树这种数据结构作为索引机制，因为他很好的利用了磁盘的交互特性，特别是硬盘级的索引，要么是B树，要么是B树的变种，比如MySQL用的B+树，Oracle用的B树。

加强版多路平衡查找树（B+树）

• 是以左闭合区间的方式实现，推崇的是以数字作为索引，基本是自增，往右边插入数据。

• 数据全保存在叶子节点，子节点只保存关键字和引用。

• B+tree与B-tree 的区别：
  (1) B+tree节点关键字搜索采用左闭合区间，就算根节点命中了，最终也会找到叶子节点。
  (2) B+tree非叶子节点不保存数据相关信息，只保存关键字和子节点的引用
  (3) B+tree关键字对应的数据保存在叶子节点中
  (4) B+tree叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

• MySQL索引选择B+tree的原因是：
  (1) B+树是B-树的变种（PLUS版）多路绝对平衡查找树，他拥有B-树的优势
  (2) B+树扫库、表能力更强
  (3) B+树的磁盘读写能力更强
  (4) B+树的排序能力更强
  (5) B+树的查询效率更加稳定

mysql B+Tree 索引体现形式

mysql数据目录截屏

• myisam
  
  

• Innodb
  

• Innodb与myisam的比较
  
  (1) 对于复杂查询，Myisam的索引结构在查询效率上更优于Innodb，路径更短
  (2) 对于行级锁的使用上，Myisam的索引结构简直无用武之地，而Innodb通过主键索引的必走路径堵住了所有非聚集索引的入口。

索引知识的补充

• 列的离散性
  

  越大离散型越好 结论： 离散性越高，选择性就越好

• 最左匹配原则
  对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过

• 联合索引
  (1) 单列索引 节点中关键字[name]
  (2) 联合索引 节点中关键字[name,phoneNum]
  (3) 单列索引是特殊的联合索引
  (4) 联合索引列选择原则

  • 经常用的列优先 【最左匹配原则】
  • 选择性（离散度）高的列优先【离散度高原则】
  • 宽度小的列优先【最少空间原则】
  

• 覆盖索引
  (1) 如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引。
  (2) 覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能

• 几条理解
  （1）索引列的数据长度能少则少。
  （2）索引一定不是越多越好，越全越好，一定是建合适的。
  （3）匹配列前缀可用到索引 like 9999%，而like %9999%、like %9999用不到索引；
  （4）Where 条件中 not in 和 <>操作无法使用索引；
  （5）匹配范围值，order by 也可用到索引；
  （6）多用指定列查询，只返回自己想到的数据列，少用select *；
  （7）联合索引中如果不是按照索引最左列开始查找，无法使用索引；
  （8）联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
  （9）联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；
  （10）用EXISTS替代IN、用NOT EXISTS替代NOT IN。
  （11）避免在索引列上使用IS NULL和IS NOT NULL
  （12）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描