MySQL索引

我爱学算法

于 2023-09-14 21:36:07 发布

阅读量260

点赞数

文章标签：分类 sql mysql

本文链接：https://blog.csdn.net/m0_54138535/article/details/132891139

版权

四、索引

索引概述、索引结构、索引分类、索引语法、SQL性能分析、索引使用、索引设计原则

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。
优势：提高数据检索的效率，降低数据库IO成本，通过索引排序，降低数据的排序成本，降低cpu的消耗

缺点：索引也是占数据空间的，索引大大提高了查询效率但同时降低了更新表的速度，insert,update,delete

索引是在存储引擎层实现的，不同的存储引擎有不同的结构

基本所有的存储引擎都支持b+树

B-Tree(多路平衡查找树)，每个节点可以有多个子节点，指针个数比节点个数多1.

达到节点个数中间节点会向上分裂，（5阶每个节点最多存储4个key）
B+Tree，每个节点都在叶子节点中，叶子节点之间形成一个单向链表，上面非叶子节点只是起到索引作用

在MySQL中索引结构对B+Tree进行了优化，增加一个指向相邻叶子节点的链表指针，提高区间访问性能

键值，数据，指针，页/块（16k）
Hash索引结构和HashMap底层原理一样，只能用于查询，一次检索就可以了，效率高于B+Tree，支持Hash索引的是Memory，而InnoDB具有自适应哈市功能。
为什么InnoDB存储引擎选择使用B+Tree索引结构？
1. 二叉树顺序插入会形成链表结构，红黑树虽然有所优化，但本质还是二叉树，
2. 相对于二叉树，在一定数据的情况下，B+Tree层级更少，搜索效率高，
3. 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，同样保存大量的数据，只能增加树的高度，导致性能降低，
4. 相对Hash索引，B+Tree支持范围匹配及排序操作，hash只能用于单次查询。

主键索引：针对于表中主键创建的索引，默认自动创建，只能有一个，primary

唯一索引：避免同一列中某个数据列中的重复值，可以有多个，unique

常规索引：快速定位特定数据，可以有多个

全文索引：全文索引是查找文本中的关键字，而不是索引中的值，可以有多个，fulltext

聚集索引：将数据与索引放到了一块，索引结构的叶子节点保存了行数据，（比如id对应的行数据都存在在聚集索引叶子节点上，可以根据id查到一行的数据）必须有，而且只有一个
二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，（name字段作为二级索引，其叶子节点存储是每个字段对应的id，然后再根据id查询）可以存在多个，

聚集索引选用规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一索引（unique）作为聚集索引

如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

InnoDB主键索引的B+Tree高度为多高呢？

假设：一行数据的大小是1k，一页中可以存16行这样的数据。InnoDB的指针占用6个字节空间，主键即使为bigint，占用字节数为8。

高度为2：根节点，n * 8 + ( n + 1) * 6 = 16 * 1024 ,n约等1170，数据个数：1171 * 16 = 18736

高度为3：1171 * 1171 * 16 = 21939856

关注