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上查询出整行的数据,这个过程也称为回表查询。(查询出来的主键一样要从根节点上开始索引,以下方便画图)