MySQL索引

1.什么是MySQL索引

索引index在 c / java 中也常用于下标的表示,这里谈到的索引更类似于目录

在数据库中索引的作用就是为了加快查询速度

1.1索引的原理

MySQL索引的原理可以概括为以空间换时间的策略,索引本身也是一种数据结构,它的主要目的是提高查询效率.在没有索引的情况下,数据库的查询需要全文搜索,即逐行扫描便利数据,来找到匹配项,当有了索引之后,数据库可以利用索引的数据结构快速定位到所需的数据行,从而大大减少查询时间.但是值得注意的是,对某表的列添加索引后如果没有按照索引列进行查询,查询速度也不会变快.

1.2 索引的特点

• 需要消耗额外的空间(不是主要矛盾)
• 可以影响数据库"增删改"的速度
• 大大提高查询数据的效率
• 减少硬盘IO次数

1.3索引的使用

1)查看该表已有的索引

show index from 表名

•  值得注意的是unique 和 foreign key也会自带索引,是自动生成的

 2)对某表的某列创建索引

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

• 如果表的数据非常多,直接创建索引会引起大规模硬盘IO操作导致数据集被卡死,所以设计表的时候应该事先规划好

3)删除索引

drop index 索引名 on 表名 

•  删除索引操作只能删除手动创建的,而unique之类的自动生成的索引删除不了,且删除操作也十分危险,可能引起大规模硬盘IO操作导致数据库挂了

2. MySQL索引底层的数据结构

聊起查询相关的数据结构,肯定会想到红黑树和hash,但是hash只能进行精准查询,不能进行模糊匹配,红黑树虽然可以进行范围查询和模糊匹配,但是会引起较多的硬盘IO,所以这里就引入了MySQL索引底层,其中主要采用的是B+树数据结构。B+树是一种平衡的多路查找树，它的特点是能够保持数据稳定有序，并且插入与修改拥有较稳定的对数时间复杂度。

简单了解一下B树

1. 多路搜索：B树的每个节点可以拥有多个子节点，这有助于减少树的高度，进而减少查找数据所需的磁盘I/O操作次数。

2. 有序性：节点内的关键字从左到右递增排列，这使得搜索过程更加高效。

3. 自平衡性：在插入或删除节点时，B树会自动调整以保持平衡，这确保了搜索操作的效率不会因为树的失衡而降低。

4. 节点大小适中：B树的节点大小适中，既包含关键字信息也包含指向子节点的指针，适合磁盘存储。

B+树在MySQL索引中的应用

 

1. 结构特点：B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上子节点的节点。内部节点保存关键字用作索引，但不保存数据；所有数据都保存在叶子节点中。叶子节点通过指针链相连，且叶子节点本身按关键字的大小从小到大顺序排列。非叶子节点包含关键字和指向其子节点的指针，这些关键字用于指导搜索过程。
2. 查询效率：B+树的平衡性和有序性使得查询、插入和删除操作都能保持相对稳定的性能。由于叶子节点相连且有序，范围查询变得非常高效。通过B+树的索引结构，数据库系统可以快速定位到所需的数据行，避免了全表扫描的昂贵操作。
3. 应用与优化：在数据库系统中，B+树被广泛应用于构建索引，以加快数据的检索速度。MySQL的InnoDB存储引擎就采用了B+树作为其索引结构。优化器可以根据查询条件选择合适的索引，从而进一步提高查询性能。

MySQL选择B+树作为其索引底层数据结构

1. 提升范围查询效率：B+树的叶子节点形成一个链表，范围查询时只需从根节点开始，通过索引查找到叶子节点，然后顺着链表向后扫描即可。而B树的范围查询需要对每个节点进行完整的搜索，效率较低。

2. 更适合磁盘存储：B+树的叶子节点存放了所有的数据，非叶子节点只存放索引信息，使得B+树更适合磁盘存储。相对于B树，B+树有着更高的节点填充率，可以减少磁盘的I/O次数。

3. 更好的顺序访问性能：B+树的叶子节点构成了一个有序链表，这使得顺序遍历和范围查询更加方便。

4. 查询效率更加稳定：由于B+树的数据都存储在叶子节点中，分支节点均为索引，所以任何关键字的查找路径长度相同，导致每一个数据的查询效率相当。