MySQL 索引以及索引优化 - 笔记

chan 作者
阅读 889 喜欢 0

存储引擎

InnoDB(B+树):聚簇索引 -> 表数据与索引存放在同一个文件下。
MyISAM(B+树):非聚簇索引 -> 表数据与索引分开存储。
Memory(HASH):底层是 HASHTABLE

什么是索引?

帮助 MySQL 高效率获取数据
索引存储在文件系统中,存储形式与存储引擎有关。
索引结构:B树、B+树、HASH、二叉树、红黑树。

为什么 MySQL 选择使用 B+树作为默认索引结构?

HASH

  1. 索引只包含指针和 HASH 值,不存储字段值。
  2. 无序,因此无法进行排序。
  3. 需要等值匹配,不支持部分内容计算 HASH 值。
  4. HASH 冲突,即使加入扰动函数,让高位参与运算,尽可能减少冲突,但也难免会冲突。
  5. 一旦使用 HASH 存储,意味着当读取数据的时候,要将所有索引数据加载到内存中,占用空间大。

二叉树 / 红黑树

每一个节点只会存储一个数据,当查找数据时,会将整个节点加载进内存,随着树的深度加深,会造成 IO 次数过多,影响数据读取效率

B树

  1. Balance-tree(平衡多路查找树)
  2. 相对二叉树而言,B树的父节点包含多个子节点,这个父节点称为 Degree(度),Degree 存储的数据量越多,子节点越多,树的高度也低,查询的效率更高。MySQL 中默认每个节点 16k 大小。

B+树

  1. B+树是 B树的升级版,每个节点不存储数据,只存储索引键值以及指针,这样使每个节点能存储更多的索引键值,层数更少,效率更高。
  2. 数据有序聚集的存储在叶子节点中,形成链表,遍历更快,有利于全表扫描。

常见名词

回表

当一张表中存在多个索引,叶子节点记录的不是行数据而是主键 ID,当用户查询姓名 chan 时,结果为 chan 的 ID,然后再通过 ID 查找主键索引树,查出对应的行数据。这种情况称为回表。

最左匹配

组合索引中,MySQL 遵循最左匹配的原则进行查询,在检索数据时从组合索引左边开始匹配,相当于创建了 (key1)(key1、key2)(key1、key2、key3:顺序可以打乱,因为 MySQL 有优化器)。

索引下推

MySQL 5.6 版本后的一个优化,一般来说我们查询两个字段 name 、 age。在 5.6 之前 MySQL 会先根据 name 字段,拉取匹配的数据到 MySQL 服务器中,再根据 age 字段查筛选出数据。在 5.6 之后有个索引下推的过程,MySQL 先会查询与 name 匹配的数据,再对 age 进行匹配,然后直接把结果返回。

索引覆盖

需要查询的数据就是索引的本身。
比如说当前有索引 (name、age),当我们根据 age 查询 name 的时候,就使用了索引覆盖,直接返回了索引本身,避免了回表的操作。

MRR mult_range read

如图,假设查询姓名,查询出来的主键 ID 可能是无序的,那么我们可以选择开启 MRR,在触发回表前,先对查询出来的主键 ID 进行一个排序,当主键 ID 是有序的时候,就避免了回表后再一个一个在主键索引树进行比对,可以直接从叶子节点中获取到有序的结果,提高回表查询效率。

索引没有命中的原因

  1. 使用 '%xxx%' 模糊查询会导致索引失效,'xxx%'不会。
  2. 组合索引查询,条件语句中出现范围查询,会导致后面的索引失效。
  3. 隐式类型转换,字符型转换数值型会执行类型转换函数导致索引失效:phone = ‘123’ -> phone = 123
  4. or:组合索引不等于搜索字段时会导致索引失效。

索引优化

  1. 索引查询避免函数计算、表达式的使用。
  2. 尽量使用主键查询,避免回表查询。
  3. 控制索引的长度,使用前缀作为索引。
  4. 使用索引扫描进行排序,如果不走索引,会生成临时表进行排序。
  5. 合理创建索引,不适宜过多。
  6. 组合索引不允许超过5个。
  7. 不适宜过早优化数据,视情况优化。

全部评论0