MySQL

MySQL索引

1. 索引概述

MySQL中的索引可以帮助数据库更加快速的查询数据的一种数据结构，平时大多的查询都只是全表扫描查询，每次查询都会去扫描整一个表，数据少还好，一旦数据达到十几万级别，就会很耗时间。


模拟500万条数据中进行普通查询和索引查询的时间差，图一是未建立索引时，查询用时：7.07秒，图二是建立索引查询，用时：0.01秒，时间差距就很大了。那么索引的好处有：提高数据检索的效率，降低数据库的IO成本、也通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。当然，也是有缺点的，虽然减少了查询时间，但也降低更新表的速度，每一次的update/insert/delete时，数据库不仅更新数据，还要更新索引文件。

2.索引的分类

MySQL中的索引实在是很多，这里就只从数据结构和物理存储的两个角度介绍：

• 数据结构角度：

  B+Tree索引：常见的索引类型，B+Tree就是在B-Tree数据结构上做的优化

  R-Tree索引（空间索引）：R-Tree是B-Tree在高维空间的扩展，也叫空间索引，主要用于地理空间数据类型，存储高维数据的平衡树。

  Hash索引：基于哈希表实现，只有精确匹配索引所有列的查询才会生效。对于每一行数据，存储引擎都会对所有的索引列计算一个hash code,并将有的hash code存储在索引中，同时在哈希表中保存指向每个数据行的指针。

  Full-text索引（全文索引）：Full-text索引就是我们常说的全文索引，它的存储结构也是B-Tree。主要是为了解决当须要用like查询时的低效问题。(ES代替)

• 物理存储角度：

  聚集索引（一级索引）:也叫聚簇索引，以主键建立的索引就叫聚集索引，而且只有InnoDB能够建立聚集索引。

  非聚集索引（二级索引）:也叫普通索引，辅助索引，除去聚集索引就是非聚集索引。

B+Tree的介绍：在实际应用中，使用较多数据结构的索引主要是B+Tree，是B-Tree的优化，同样是基于二叉树的基础上。https://www.cs.usfca.edu/~galles/visualization/Algorithms.html (数据结构可视化网站)。

二叉树结构的特点就是查询快，但是当数据量变的很大，二叉树的深度也随着增加，这样平均查找的次数也会随之增加，这时就出现B-Tree，也叫多路平衡搜索树，B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支。图一是B-Tree存储的树深度，图二是二叉树存储的树深度，一对比查询的速度就很明显了。


每个节点都会占用一个磁盘块的磁盘空间，当在数据检索时，会将此磁盘块加载到内存中，每个非根节点上都有两个升序排序的键值和三个指向子树的指针，该指针存储的是子节点所在磁盘块的内存地址。比如磁盘块1，P1指针：数据范围小于26，P2指针：大于26小于37，P3指针：大于37。data代表着一行数据。

而B+Tree是在B-Tree的基础上进行了优化，就是将所有的数据结构的数据统一放在叶子节点上，这样可以让节点上可以容纳更多的指针，提高区间访问的性能。

聚集索引和非聚集索引：聚集索引的所有数据存储在B+Tree上的叶节点上，非聚集索引的B+Tree只会存储当前索引的索引列和主键索引列的数据，如果需要通过非聚集索引查询一行数据，先是非聚集索引查出主键，然后通过主键去主键建立的B+Tree上查询出整行的数据，这个过程也称为回表查询。(查询出来的主键一样要从根节点上开始索引，以下方便画图)