深入理解mysql索引底层结构与算法
2026-01-06 10:11:40 0 举报
AI智能生成
深入理解mysql索引底层结构与算法
作者其他创作
大纲/内容
索引
什么是索引?<br>
帮助mysql高效查找数据的排好序的数据结构
数据结构
二叉树
平衡二叉树
红黑树
B-树
1.叶节点具有相同深度,叶节点指针为空<br>2.所有索引元素不重复。<br>3.节点中的数据索引从左到右递增。
B+树<br>
1.非叶子节点不存储数据,只存储冗余索引字段,这样每页可以存储更多索引字段<br>2.叶子节点包含所有索引字段与数据。如果是聚集索引,则包含整行数据,如果是非聚集索引(普通索引),则包含的是索引字段与主键值。<br>3.叶子节点用指针链接,提高区间访问性能。<br>
Hash
1.对索引的key做hash计算就可以定位到数据位置,但是如果存在hash冲突,还要遍历链表。<br>2.只能支持=,in的情况,不支持范围查找,但比B+树查询更高效。<br>
innodb索引实现(聚集)<br>
1.表数据文件本身就是按照B+tree结构组织的一个索引文件结构。<br>2.聚集索引:叶子节点包含了完成的数据记录。<br><br>
问题1:为什么建议innodb表必须建主键,并且推荐使用整形的自增主键?<br>
答:innodb引擎建表时如果不指定主键,则会自己筛选第一个不包含null的唯一索引作为主键,如果没有,则生成一个6字节的rowid作为主键,自己指定了主键,则减少前面两步开销,至于整形自增,则是为了快速比对与防止索引结构的页分裂与平衡旋转带来的开销。<br>
问题2:为什么非主键索引结构叶子节点存储的值是主键值?<br>
答:为了数据一致性与节省存储空间<br>
非聚集索引(普通索引)<br>
叶子节点不包含所有行的数据记录,只是会在叶子节点存有自己本身的键值和主键的值<br>
索引类型
按照数据结构分
B+树索引<br>
Hash索引
全文索引(FULLTEXT)<br>
空间索引
R-Tree 数据结构
按照功能性区分
主键索引
值唯一且不为null
唯一索引
确保索引值得唯一性,允许null值存在,除非显示指定not null <br>
普通索引
最基本的索引类型,没有唯一性约束
全文索引<br>
专门用于文本内容的搜索,支持自然语言搜索和布尔搜索
按照列数区分
单列索引
只包含一个列的索引
CREATE INDEX idx_email ON users (email);
多列索引(联合索引)<br>
1.遵循最左前缀原则,先按照第一个字段排序,如果第一个字段一样,在按照第二个字段排序,如果都一样依先后递增排序。<br>2.mysql8.0以后引入了索引跳跃扫描:即使没有最左条件,mysql会自动添加最左列的值进行查找数据。
CREATE INDEX idx_name ON users (last_name, first_name);
特殊索引类型(索引优化的一种方式)<br>
覆盖索引
只是一种索引使用的一种方式:只需要通过搜索索引就可以得到要查询的值,而不用回表查询数据,提高查询性能,减少io开销<br>
前缀索引
只对列的前一部分字段创建索引,优点:索引空间使用更少,查询快。缺点:不能在orderby或者groupby中使用前缀索引,也不用作覆盖索引<br>
函数索引(mysql8.0+)<br>
基于表达式或函数结果的索引
mysql索引优化
问:一条sql语句如何优化?<br>
1.先看表的数据类型设计是否合理,有没有选择数据类型越简单越小原则。<br>2.表中的碎片是否整理。<br>3.表的统计信息是否收集,只有统计信息准确,执行计划才可以帮助我们优化。<br>4.通过explian关键字查看执行计划,检查索引使用情况,没有索引,创建索引。<br>5.创建索引前,先看准备作为索引的字段区分度高不高,区分度越高越适合作为索引。<br>6.通过explian再次查看执行计划,对比两次结果,看看效率是否提高。<br>
问:什么样的字段适合作为索引字段?<br>
1.经常被查询的列。<br>2.经常被用于表链接的列。<br>3.经常排序分组的列。<br>4.离散度高的列。
explain关键字
模拟优化器执行sql语句,分析你的查询语句或是结构的性能瓶颈。只是返回执行计划信息,不会执行sql,除非from 后跟着子查询。会执行子查询,并将子查询结果放在临时表中。<br>
explain两个变种<br>
explain extended:会在explain的基础上额外提供一些查询优化信息。<font color="#e74f4c">mysql 8.0已经删除</font><br>
explain partitions:如果查询是基于分区表的话,会显示查询将访问的分区<br>
explain中的列<br>
id列
id列的编号是select的序列号,有几个select就有几个id,并且id的顺序是按select出现的顺序增长。<br><font color="#e74f4c">id列的值越大执行优先级越高,值一样,从上往下执行,为null的最后执行</font><br>
select_type列<br>
表示对应的行是简单还是复杂的查询<br>
simple<br>
简单查询,查询不包含子查询和union<br>
primary<br>
复杂查询中最外层的select<br>
subquery
包含在select中的子查询,不包含from子查询<br>
derived<br>
包含在from子句中的子查询,mysql会将结果放在一个临时表中,也称为派生表<br>
union
在union中第二个和随后的select
table列
表示explain的一行正在访问哪个表,如果是derived n表示依赖select值为n的子查询的临时表<br>
type列
这一列表示关联类型或访问类型,从优到差为:<br>system>const>eq_ref>ref>range>index>ALL<br>
possible_key列<br>
表示可能用哪些索引来查找<br>
key列
mysql实际用的哪个索引来查找数据<br>
extra列
using index:使用覆盖索引<br>
use where:使用where语句来处理结果并且查询的列未被索引覆盖。<br>
use index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围。<br>
use tempoary:需要建立一个临时表来处理查询<br>
use filesort:将用外部排序而不是内部排序。<br>
查询优化技术
ICP(索引条件下推)<br>
1.将本来要在server层过滤的条件,下推到存储引擎层进行提前过滤,优势:大幅减少了存储引擎层访问基表的次数与server层访问存储引擎层的次数。<br>
MRR(多范围读取)<br>
1.核心思想:将大量的随机磁盘I/O转换为更高效的顺序磁盘I/O<br>原理:将普通索引的主键放到read_rnd_buffer中进行排序,在用排序号的主键集合回表读取数据,这样就将随机io变为了顺序io,大量降低了io查询开销。<br>
BKA(批量键访问)<br>
1.BKA依赖于MRR,对多表连接(Join)查询进行优化,批量地将驱动表的连接键发送给被驱动表的MRR接口。<br>作用:极大地减少了对被驱动表的访问次数,结合MRR的顺序I/O优势,显著提升了Join操作的性能<br>
收藏
收藏
0 条评论
下一页