MySQL 索引详解

目录

什么是索引

索引的使用

实现索引的数据结构

B树

B+树

总结

---

什么是索引

索引(index)相当于论文的目录,通过索引 我们可以大大加快查找速度(省略了遍历的过程)

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。

使用场景:
要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

使用索引会提高空间的开销, 构造索引需要额外的硬盘空间来保存; 索引在提高找效率的同时也加剧了增删改的开销, 此时的增删改, 需要调整已经创建好的索引目录.

索引的使用

创建索引使用的关键字  index   

查看索引

show index from 表名;

创建索引

create index 索引名 on 表名(列名);

如果表里的数据很大,那么此时建立索引的开销也会很大

好的做法是,在创建表之初 就把索引设定好

删除索引

drop index 索引名 on 表名;

此时就删除完成了

实现索引的数据结构

MySQL数据库采用的是B+树的数据结构,在了解B+树前先了解一下B树(B-树)

B树

B树又被称为n叉搜索树(相比于二叉搜索树),因为其每个节点上都有多个key,而N个key又能延伸出N+1个分叉出来,因此,N个key就能划分出N+1个区间来.         B树按照区间来进行搜索

B树查询元素流程:

1.拿着要查询的元素从根节点出发,判断是否在根节点上存在

2.如果不存在则看看是在哪个区间,然后沿着区间往下一个节点找,如果找到叶子节点还没找到,就说明不存在

B树的特点:

每个节点上都可以存储多个key值,因此B树的高度远远小于二叉搜索树,查询速度更快

B树的优势:

对于数据库来说,每个节点的数据都是存在于硬盘上的,每次读取时,不管节点上 的key值是一个还是多个,其硬盘IO的开销都是一样的

而B树每个节点上可以存在多个key值,因此当总的元素个数一定时,B树的节点数会远远小于二叉搜索树,因此高度也会大大降低,所以进行查询的时候,硬盘IO的次数也大大减少了

B+树

用于数据库索引的数据结构是B+树,在B树的基础上改进而来 

B+树的特点:

•   B+树也是一个N叉搜索树, 每个节点上可能包含N个key, N个key划分出N个区间; 最后一个      key就相当于最大值了.
•   父元素的key会在子元素中重复出现, 这样的重复出现会让叶子节点包含了所有数据的全集,    非叶子节点的所有值都会在叶子节点中体现出来.
•   会把叶子节点, 用类似于链表的方式首尾巴相连.
•    由于叶子节点是完整的数据集合,因此在叶子节点存储数据表的每一行数据,而非叶子节点 ,     只   存key值即可,不存数据

B+树的优势:

• 一个节点保存了更多的key,因此最终树的高度相对更矮,查询的时候减少了硬盘IO访问次数(与B树相同)
• 所有的查询最终都会落到叶子节点上,因为非叶子节点不存储数据,因此查询任何一个数据,经过的硬盘IO访问次数都是一样的  (查询效率很稳定)
• B+树的所有叶子节点,会构成一个链表,此时就可以很方便的进行范围查询
• B+树在非叶子节点上只存储key,数据都在叶子节点上. 因此非叶子节点所占用的空间是非常小的  因此这些非叶子节点就可能在内存中缓存 (或缓存一部分) 此时查询时就可以直接在内存中比较,又进一步减少了硬盘IO次数

总结

使用索引提高查询速度, 本质上是在减少硬盘IO的次数

MySQL中对于带有主键的表, 就是按照主键索引的B+树来组织的.

如果表中不止以有主键索引, 还有别的非主键列, 也有索引; 对于非主键列会构造另一个B+树, 树中非叶子节点存储的都是这一列里面的key(比如一堆学生的姓名), 到了叶子节点这一层, 存储的不是完整的数据行, 存的只是id(主键列);

所以, 当使用非主键列的索引进行查询时, 需要先查一遍索引列的B+树, 找到对应的主键列, 再查一遍主键列的B+树(回表), 查询过到对应的记录.