首页  思维导图  详情

深入理解mysql索引底层结构与算法

2026-01-06 10:11:40   0  举报





AI智能生成

深入理解mysql索引底层结构与算法

读书笔记

运维

作者其他创作

大纲/内容

索引

什么是索引？

帮助mysql高效查找数据的排好序的数据结构

数据结构

二叉树

平衡二叉树

红黑树

B-树

1.叶节点具有相同深度，叶节点指针为空 2.所有索引元素不重复。 3.节点中的数据索引从左到右递增。

B+树

1.非叶子节点不存储数据，只存储冗余索引字段，这样每页可以存储更多索引字段 2.叶子节点包含所有索引字段与数据。如果是聚集索引，则包含整行数据，如果是非聚集索引（普通索引），则包含的是索引字段与主键值。 3.叶子节点用指针链接，提高区间访问性能。

Hash

1.对索引的key做hash计算就可以定位到数据位置，但是如果存在hash冲突，还要遍历链表。 2.只能支持=，in的情况，不支持范围查找，但比B+树查询更高效。

innodb索引实现（聚集）

1.表数据文件本身就是按照B+tree结构组织的一个索引文件结构。 2.聚集索引：叶子节点包含了完成的数据记录。

问题1：为什么建议innodb表必须建主键，并且推荐使用整形的自增主键？

答：innodb引擎建表时如果不指定主键，则会自己筛选第一个不包含null的唯一索引作为主键，如果没有，则生成一个6字节的rowid作为主键，自己指定了主键，则减少前面两步开销，至于整形自增，则是为了快速比对与防止索引结构的页分裂与平衡旋转带来的开销。

问题2：为什么非主键索引结构叶子节点存储的值是主键值？

答：为了数据一致性与节省存储空间

非聚集索引（普通索引）

叶子节点不包含所有行的数据记录，只是会在叶子节点存有自己本身的键值和主键的值

索引类型

按照数据结构分

B+树索引

Hash索引

全文索引(FULLTEXT）

空间索引

R-Tree 数据结构

按照功能性区分

主键索引

值唯一且不为null

唯一索引

确保索引值得唯一性，允许null值存在，除非显示指定not null

普通索引

最基本的索引类型，没有唯一性约束

全文索引

专门用于文本内容的搜索，支持自然语言搜索和布尔搜索

按照列数区分

单列索引

只包含一个列的索引

CREATE INDEX idx_email ON users (email);

多列索引（联合索引）

1.遵循最左前缀原则，先按照第一个字段排序，如果第一个字段一样，在按照第二个字段排序，如果都一样依先后递增排序。 2.mysql8.0以后引入了索引跳跃扫描：即使没有最左条件，mysql会自动添加最左列的值进行查找数据。

CREATE INDEX idx_name ON users (last_name, first_name);

特殊索引类型（索引优化的一种方式）

覆盖索引

只是一种索引使用的一种方式：只需要通过搜索索引就可以得到要查询的值，而不用回表查询数据，提高查询性能，减少io开销

前缀索引

只对列的前一部分字段创建索引，优点：索引空间使用更少，查询快。缺点：不能在orderby或者groupby中使用前缀索引，也不用作覆盖索引

函数索引（mysql8.0+）

基于表达式或函数结果的索引

 mysql索引优化

问：一条sql语句如何优化？

1.先看表的数据类型设计是否合理，有没有选择数据类型越简单越小原则。 2.表中的碎片是否整理。 3.表的统计信息是否收集，只有统计信息准确，执行计划才可以帮助我们优化。 4.通过explian关键字查看执行计划，检查索引使用情况，没有索引，创建索引。 5.创建索引前，先看准备作为索引的字段区分度高不高，区分度越高越适合作为索引。 6.通过explian再次查看执行计划，对比两次结果，看看效率是否提高。

问：什么样的字段适合作为索引字段？

1.经常被查询的列。 2.经常被用于表链接的列。 3.经常排序分组的列。 4.离散度高的列。

explain关键字

模拟优化器执行sql语句，分析你的查询语句或是结构的性能瓶颈。只是返回执行计划信息，不会执行sql，除非from 后跟着子查询。会执行子查询，并将子查询结果放在临时表中。

explain两个变种

explain extended：会在explain的基础上额外提供一些查询优化信息。mysql 8.0已经删除

explain partitions:如果查询是基于分区表的话，会显示查询将访问的分区

explain中的列

id列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长。 id列的值越大执行优先级越高，值一样，从上往下执行，为null的最后执行

select_type列

表示对应的行是简单还是复杂的查询