深入理解 MySQL —— MySQL 的索引

1. 索引是什么？

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

2. 为什么要用索引？

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

3. 为什么使用 B+Tree？

3.1 二叉树分析

我们分析二叉树和平衡二叉树，下图所示二叉树和平衡二叉树结构：

1. 二叉查找树
   

   图 二叉树

2. 平衡二叉查找树

   

   图 平衡二叉树

由上图可知，二叉树和平衡二叉树的缺点：

1. 二叉树的高度太高了，树的高度决定了磁盘的 IO 操作次数，IO 操作耗时大。
2. 平衡树太小了，每一个磁盘块（节点/页）保存的数据量太小了。没有很好的利用操作磁盘 IO 的数据交换特性；也没有利用好磁盘 IO 的预读能力，从而带来频繁的 IO 操作。

3.2 多路平衡树分析

然后我们分析多路平衡树，我们先看 B-Tree，如下图所示：

图 B-Tree 然后，我们看下 B+Tree，如下图所示：

图 B+Tree ### 3.3 B+Tree 和 B-Tree 的区别

1. B+ 节点关键字搜索采用闭合区间。
2. B+ 非叶子节点不保存数据，只保存关键字和子节点的引用。
3. B+ 关键字对应的数据保存在叶子节点。
4. B+ 叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。

3.4 为什么选用 B+Tree ？

1. B+Tree 是 B-Tree 升级版。
2. B+Tree 扫库、扫表能力更强。（叶子节点相邻节点有顺序引用的关联）
3. B+Tree 的磁盘读写能力更强。
4. B+Tree 的排序能力更强。（叶子节点有序）
5. B+Tree 的查询效率更稳定。

4. B+Tree 在 MySQL 索引中的体现

1. Myisam 存储引擎中的索引结构
   

   图 索引存储格式

   

   图 ID索引和 name列索引

2. Innodb 存储引擎中索引的结构

   聚集索引
   数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同

   

   图 主键索引

   辅助索引

   

   图 辅助索引

3. Innodb VS Myisam

   

   图 Innodb VS Myisam
   1. Innodb 存储引擎中数据存储在主键索引的叶子节点，Myisam 的主键索引和辅助索引的叶子节点都存储数据。
   2. Innodb 的辅助索引存储主键索引ID，查询数据的时候通过辅助索引查询到主键ID，然后查询主键索引得到数据。Myisam 查询辅助索引直接获取数据。

5. 选择怎样的列创建索引

5.1 选择离散性高的列

离散性越高，选择性就越好

离散性：

count(distinct col) / count(col)

5.2 最左匹配原则

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

5.3 联合索引选择原则

1. 经常查询的列优先【最左匹配原则】。
2. 选择性（离散度）高的列优先【离散度高原则】。
3. 宽度小的列优先【最小空间原则】。

6. 覆盖索引

如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引。

覆盖索引可减少数据库 IO，将随机 IO 变为顺序 IO，可提高查询性能。

7. 现在，你能都理解了么？

1. 索引列的数据长度能少则少。

   减少索引存储空间

2. 索引一定不是越多越好，越全越好，一定是建合适的。

   减少索引的数量可以减少磁盘存储量，提高写入速度，使用联合索引加快检索速度。

3. like 9999%可以使用到索引

满足最左匹配原则

4. like %9999、Where 条件中 notin和 <>操作无法使用索引。

   无法满足最左匹配原则

5. 匹配范围值，order by也可用到索引；

   使用到覆盖索引

6. 多用指定列查询，只返回自己想到的数据列，少用select*；

   减少传输的数据，有可能使用到覆盖索引

7. 联合索引中如果不是按照索引最左列开始查找，无法使用索引；

8. 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；

9. 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；