ThankNeko's Blog
Hoshinozora
2023-03-19
目录

MySQL索引及优化方式

# 索引介绍

# 索引简介

索引类似于一本书中的目录，正如通过目录可以找到实际文章，在数据库中，通过索引也可以找到实际数据。正确的创建合适的索引可以提高查询的效率和速度，是提升数据库查询性能的基础。

# 索引工作机制

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构，索引中存放的内容由不同的索引结构决定，但一般都会包含关键字。例如下图：

img

  • 如果没有索引，我们就只能通过全表扫描来找到指定数据。

  • 如果有索引，我能就能通过各类索引结构来提高数据的查找效率，最终快速的找到对应数据。

  • 索引保存数据的方式一般有两种：一是存放关键字对应的实际数据内容，二是存放关键字对应数据在磁盘上的地址。

# B树索引结构的演变

  • 二叉树 -> 平衡二叉树 -> B树 -> B+树(InnoDB) -> B*树
  • MySQL的InnoDB引擎就是用的B+Tree的索引结构。

# 二叉树

# 介绍

二叉树把第一个插入的节点做为根节点，后面插入的数据从根节点开始比较，小于比较节点的值都走左边，大于等于比较节点的值都走右边。基于二叉查找树的这种特点，在查找某个节点的时候，可以采取类似于二分查找的思想，快速找到某个节点。image-20230318164416684

# 缺点

二叉树的缺点是树的高度不稳定。如果插入的数据都是有序的，那么二叉树的节点也就不会分叉，也就是说树的节点会变成线性结构，这种情况虽也满足二叉树的条件，但已经近似退化为一条链表，将会极大的降低我们的查询效率。比如需要查找一个数，而这个数正好在树的最深处，那么就需要比较所有的节点值。

image.png

# 平衡二叉树

# 介绍

当二叉树节点分布不均匀时，会极大影响数据查询的性能，所以为了保证数据的均衡性，就有了平衡二叉树的结构。

平衡二叉树采用平衡算法可以让数据均匀的分布到树里的各个节点，避免树的高度相差太多，从而解决二叉树的层级不稳定问题。

# 数据查找过程

img

  • 比如现在要查找id=8的数据，则查询过程如下：
    1. 把根节点加载进内存，用8和10进行比较，发现8比10小，继续加载10的左子树P1。
    2. 把5加载进内存，用8和5比较，同理，加载5节点的右子树P2。
    3. 此时发现命中，则读取id为8的索引对应的数据。

# 二分法

平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，二分法是我们常用的一种查找算法，可以有效的提升数据找找的效率，其实现思路是：

  1. 首先对数据集进行排序。
  2. 找到数据集中间位置的节点。
  3. 用查找值和中间节点值进行比较，如果等于则表示找到了查找值则返回。如果查找值小于中间节点的值则说明查找值在中间节点的左边，反之大于则说明查找值在中间节点的右边。
  4. 重复2、3步直到找到目标值后返回。
  • 比如从[9,2,6,5,7,8,4,3,1]中快速查找到7。

image-20230318161150884

  • 将上面的[9,2,6,5,7,8,4,3,1]构建成平衡二叉树，其结构就是如下

image-20230318170416656

# 特点

  1. 非叶子节点最多只能拥有两个子节点。
  2. 每一个节点的左边子节点的值小于当前节点，右边子节点的值大于当前节点。
  3. 树左右两边节点的层级相差不会大于1。

# 缺点

  1. 存储的数据内容太少，在数据量大时，树的高度会非常高，这种情况下如果查询的数据在非常高的深度中时，查询效率会很低。
  2. 查询不稳定，如果查询的数据落在根节点，只需要一次IO，但如果查询的数据落在叶子节点或者是支节点，则需要多次IO才可以找到。

# B树

# 介绍

B树(Balance Tree) 又名B-树、或者多路平衡查找树，多路也就是多叉的意思。

B树能够很好的利用操作系统和磁盘的交互特性，MySQL为了更好的利用磁盘的预读能力，将页大小设置为16Kb，即将一个节点(磁盘块)的大小设置为16Kb，一次IO将一个节点(16Kb)内容加载进内存。

假设关键字类型为int，即4字节，若每个关键字对应的数据区也为4字节，不考虑子节点引用的情况下，则上图中的每个节点大约能够存储(16kb)/(4byte*2)=2000个关键字，共2001个路数。

对于二叉树来说，三层高度最多只能保存7个关键字。而对于这种有多路的B树，三层高度能够保存的关键字个数要远远的大于二叉树。也就是说树的高度降低了，数据的检索效率比起二叉树大幅提高了。

# 数据查找过程

img

  • 假设要从上图中查找id=X的数据，B-TREE检索过程如下：
    1. 取出根节点(磁盘块)，加载40和60两个关键字。
    2. 如果X等于40，则命中，直接从该节点上取出对应数据。如果X小于40则走P1。如果40<X<60则走P2，如果X=60，则命中，直接从该节点上取出对应数据。如果X>60走P3。
    3. 反复以上过程最终找到目标数据。

# 特点

  1. 所有节点关键字是按递增次序排列，并遵循左小右大原则。
  2. 每个节点关键字个数和路数关系为：关键字个数=路数–1。
  3. 所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字对应数据的指针外，也有指向其子节点的指针只不过其指针地址都为null对应上图最后一层节点的空格子。

# 节点宽度

每个根节点是16KB，其中存储的3个值，对应的分别是三个枝节点的最小值，而P1、P2、P3则是三个枝节点的指针。

每个枝节点也是16KB，其中存储的3个值，对应的分别是三个叶子节点的最小值，而P1、P2、P3则是三个叶子节点的指针。

每个叶子节点也是16KB，用于存储3个值(数据)。

但实际上16KB可以存储16KB大小的字符串，并非只能存储3个值。

# B+树

# 介绍

B+Tree是B-Tree的一个升级，也是InnoDB引擎使用的索引结构，它在B-树的基础上降低树的高度，增大节点存储数据量，B-树能解决的问题，B+树也能够解决。

另外在B+树中，B树的路数和关键字个数的关系不再成立了，数据检索规则采用的是左闭合区间，路数和关键个数关系为1比1。

# 数据查找过程

image-20230318191504211

  • 例子一，比如要在上图中查找值33，则检索过程如下：
    1. B树会先判断根节点的的P1/P2/P3，看看33属于哪一个枝节点，P2是28 <= P2 < 65符合条件，所以走P2。
    2. 然后到枝节点，再判断枝结点的P1/P2/P3，看看33属于哪个叶子节点，P1是28 <= P1 < 35符合条件，所以走P1。
    3. 然后到了P1的叶子结点后，都遍历一遍就能找到对应索引值了，然后读取对应内容即可。

image-20230318192034595

  • 例子二，比如要在上图中查找值X=1，则检索过程如下：
    • 取出根磁盘块，加载1、28、66三个关键字。
    • X<=1走P1，取出磁盘块，加载1、10、20三个关键字。
    • X<=1走P1，取出磁盘块，加载1、8、9三个关键字。
    • 到达叶子节点后，遍历叶子节点命中1，然后加载对应的数据，图中数据区中存储的是具体的数据。

# B-Tree与B+Tree的区别

  1. B+树扫库和扫表能力更强，如果我们要根据索引去进行数据表的扫描，对B-TREE进行扫描，需要把整棵树遍历一遍，而B+TREE只需要遍历他的所有叶子节点即可（叶子节点之间有引用）。
  2. B+树磁盘读写能力更强，他的根节点和支节点不保存数据区，所以根节点和支节点同样大小的情况下，保存的关键字要比B-TREE要多。而叶子节点不保存子节点引用，能用于保存更多的关键字和数据。所以，B+TREE读写一次磁盘加载的关键字比B-TREE更多。
  3. B+树排序能力更强，B+Tree天然具有排序功能。
  4. B+树查询性能稳定，B+Tree数据只保存在叶子节点，每次查询数据，查询IO次数一定是稳定的。当然这个每个人的理解都不同，因为在B-TREE如果根节点命中直接返回，确实效率更高。

# 特点

  1. B+Tree关键字的搜索采用左闭合区间，即：如果根节点和枝节点id=1命中，会继续往下查找，直到找到叶子节点中的1。

  2. B+Tree的根节点和支节点没有数据区，关键字对应的数据只保存在叶子节点中，也就是只有叶子节点中的关键字数据区才会保存真正的数据内容或者是内容的地址，而在B-Tree中，如果节点命中，则会直接返回数据。

  3. 在B+Tree中，叶子节点不会去保存子节点的引用。

  4. B+Tree叶子节点是顺序排列的，并且相邻的节点具有顺序引用的关系，如上图中叶子节点之间有指针相连接。

  5. B+Tree的基础上，在叶子节点之间，针对范围查找添加了双向指针，有了IO方面的优化，而B-Tree树的叶子结点没有Q指针，所以在查询范围数时，会不断地遍历三层结点，遍历的越多，对IO的压力越大。

# B*树

在B+树的基础上，在枝节点之间也添加了双向指针，对范围查找进一步的优化，加速了范围查找的效率。

image-20230318230835473

# B树索引中的分类

# 聚集索引

聚集索引就是对应实际记录行数据的索引，在一张表中聚集索引只能有一个，一般是主键列。聚集索引是MySQL自动生成维护的，只需要建表时指定主键即可。MySQL会自动选择主键列作为聚集索引列，如果表没有主键，会自动生成隐藏的。

设置主键后，用户在添加数据时，数据会按照主键列基于数字或字母进行排序，有序的存储数据行到表和磁盘中。然后聚集索引会直接将原表数据页作为叶子节点，然后提取聚集索引向上生成B+树结构的枝和根，因为在存储到表时已经经过排序了，所以就不需要再生成叶子节点了。

当通过主键查询数据时，可以直接查询到整行数据行。但是如果不使用主键列查询，还想使用索引功能，就需要用到辅助索引。

# 辅助索引

管理员选择一个列创建辅助索引后，MySQL会自动将此列的值提取，然后基于数字或字母进行排序，并将排序好的数据，均匀的存储到索引的叶子结点，然后再生成枝节点和根节点。

辅助索引中的数据区存储的不是实际的记录行数据，而是记录行对应的聚集索引关键字。也就是说，通过辅助索引查询数据时，需要进行两次索引查询，一次查询辅助索引关键字对应的聚集索引关键字，一次是聚集索引关键字对应的记录行数据。

辅助索引一般还分为单列辅助索引和联合索引(覆盖索引)和唯一索引。

# 索引树高度影响因素

# 数据行数量

MySQL中索引的每个节点最大能存储的数据时有限的，因此在数据行较多时，索引树的高度也会相应的变高。

解决方法是分表，将大表分成一个个小表，以减少单张表的索引树高度。

# 关键字值长度

关键字值的长度越大，每个节点能存储的关键字就越少，也就会造成节点数增多，最终导致索引树变高。

反之关键字占用的空间越小，每个节点保存的关键字个数就越多，从而达到降低索引树高度的目的。且关键字占用越小，每次加载进内存的关键字个数就越多，检索效率就越高。

# 数据类型

数据类型所需空间的长度越大，每个节点能存储的关键字就越少，也就会造成节点数增多，最终导致索引树变高。

所以定长选char，变长选varchar，能用enum时就用enum。

# 联合索引 (覆盖索引)

# 介绍

联合索引是由多个字段组成的索引。若一条常用的SQL，需要多次用到多个条件，我们就可以将其设置为联合索引以提高查询效率。

例如：WHERE username='xx',password='xxxx';，我们就可以将username、password设置为联合索引。当索引在检索password字段的时候，由于检索username时已经缩小了范围，所以password需要检索的数据量大大缩小，提高了索引的效率。

# 最左匹配原则

在对于索引中的关键字进行对比时，一定是从左往右开始对比，且不可跳过，只有左边的索引用到了，右边的索引才会生效。

所以我们在创建联合索引时，一定要经常用到的字段放在前面，不经常用到的放到后面。对于只匹配了左边部分的联合索引，我们可以多创建一个索引。

image-20230319100842181

# 等值联合索引查询

=或IN的字段只要索引正确应用上了，查询条件的顺序无所谓，MySQL的查询优化器会自动优化成索引识别的形式。

# 只命中 col1
WHERE col1='xxx';

# 命中col1，col2，顺序可以颠倒
WHERE clo2='xxx', `clo1`='xxx';  

# 命中col1,col2,col3，同理，三个列的顺可以颠倒
WHERE col2='xxx',col3='xxx',col1='xxx';
1
2
3
4
5
6
7
8

# 不等值联合索引查询

当出现>、<、>=、<=、like时，联合索引只会走到出现不等值查询的列。

所以在使用联合索引时，我们应当将不等值判断的列放到最后面，然后重建一个对应顺序的联合索引。

  • 例如: WHERE k2='2' k1>1;，然后创建联合索引ind_k(k2,k1)。

# 多子句应用联合索引

在多子句需要使用联合索引时，我们需要按照子句的执行顺序去建立联合索引，例如：WHERE k1=1 ORDER BY k2;，按顺序建就是ind_k(k1,k2)。

# 联合索引优化

  • # 联合索引列的选择原则
    • # 经常用的列优先 (最左匹配原则)
      • 对于索引中的关键字进行对比的时候，一定是从左往右以此对比，且不可跳过，如果说最左边的列没有用到，则不会走该索引。
    • # 离散度高的列优先 (离散度高原则)
      • 离散度越高，通过索引最终确定的范围越小，最终扫面的行数也就越少。也就是唯一值多的列放在最左侧，能过滤掉更大部分的数据。
    • # 宽度小的列优先 (最少空间原则)
      • 关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。
  • # 联合索引设置的索引，查询时应用的越全效果越好。
    • 例如：ind_k(k1,k2,k3);，查询时都用上WHERE k1='123' AND k2='321' AND k3='123';
  • # 优化using filesort排序问题
    • 执行计划的extra中出现using filesort则表示排序相关的子句没有创建索引，建立索引即可。
    • ORDER BY、GROUP BY、DISTINCT、UNION都是排序有关的子句。

# MySQL索引相关命令

# 查询索引

  • desc [表名]; - 主要看Key字段。

    • PRI - 聚集索引(主键)
    • MUL - 辅助索引
    • UNI - 唯一索引

  • show index from [表名]; - 获得表的索引详细信息，主要看Column_name字段。

# 创建索引

  • 注意：因为是DDL操作所以会锁表。

  • # 聚集索引
    • 聚集索引需要在建表时就指定PRIMARY KEY。
  • # 单列的辅助索引
    • alter table [表名] add index 索引名(列名);
  • # 多列的联合索引
    • alter table [表名] add index 索引名(列名1,列名2...);
  • # 唯一索引
    • alter table [表名] add unique index 索引名(列名1);
  • # 前缀索引
    • alter table [表名] add index 索引名(列名(前n位));
    • 只有字符串类可以做前缀索引。
  • # 索引名规范
    • 唯一索引以uni_开头；一般索引以ind_开头。

# 删除索引

  • 先查询有哪些索引，然后删掉，同样会锁表。
  • alter table [表名] drop index [索引名];

# 执行计划分析

# 介绍

执行计划是在语句进入SQL层后，其中预处理的执行计划分析，我们可以将优化器选择后的执行计划，截取出来，便于管理员判断语句的执行效率以进行优化。

# 获取执行计划

  • desc [SQL语句]; 或 explain [SQL语句];
  • 以上两种作用相同。

# 分析执行计划

# table - 表名

  • 可以在多表查询时，分析哪个表性能低。

# type - 查询的类型

  • # all - 全表扫描
    • 不走索引，而是遍历所有叶子节点。
    • 在不指定过滤条件或者条件没有使用索引时，会使用该类型。另外 != 或like前有%时也不会走索引。
    • 例如：select * from table;
  • # 索引扫描
    • 索引扫描类型：index, range, ref, eq_ref, const(system), null
    • 越靠右边的，性能越好，至少要达到range及以上索引才算是有效果。
  • # index - 全索引扫描
    • 要查询的数据结果需要遍历整个索引树时会使用该类型。
    • 例如：select id from table;
  • # range - 索引范围扫描
    • 使用范围查询时会使用该类型。
    • 范围查询表达式有>、<、>=、<=、between、and、or、in、like，同级中因为查询连续的值更符合B+树，所以>、<、>=、<=、like会比between、and、or、in性能更好。
    • 另外 != 或like前有%时不会走索引，但如果是对于主键索引则会走range索引，例如：where id != 10;。
  • # ref - 辅助索引等值查询
    • 等值查询和union联合查询时，会使用该类型。
  • # eq_ref - 多表连接查询
    • 多表连接时，子表使用主键列或唯一列作为连接条件时，子表会使用该类型。
    • 如果索引不符合预期，可以试着将表的位置调换一下，看看哪种情况性能更高。
  • # const/system - 主键或唯一键等值查询
    • 等值查询主键或唯一键时，会使用该类型。
    • 例如：where id=1;
  • # null
    • 查询的数据不存在时会出现。

# possible_keys - 可能的索引

  • 查询中可能会使用的索引，可能的会有多个。

# key - 实际的索引

  • 真正选择走的索引，我们需要关注有没有走我们想要走的索引。

# key_len - 索引覆盖长度

  • 在联合索引当中，key_len的值越大越好，越大也就表示应用的索引越多。
  • 它是按照最大预留长度来计算的，而非实际占用长度来计算。

# extra - 额外

  • using filesort - 如果出现，则表示在查询中有关排序的条件列没有应用索引，ORDER BY、GROUP BY、DISTINCT、UNION都是排序有关的子句。

# 索引应用规范

# 建立索引的原则

  • 建表必须要有主键(聚集索引)，一般是无关列，自增长。
  • 经常作为WHERE或其他有关排序的语句的条件列，要做好索引。
  • 最好使用唯一值多的列，作为联合索引查询的前导列，其他的按照联合索引的优化细节来做。
  • 列值长度较长的索引列，建议使用前缀索引，降低索引树的高度。
  • 降低索引条目，不要创建没有用的索引，不常使用的索引要进行清理。表中的索引并不是越多越好，冗余或者无用索引会占用磁盘空间并且会影响增删改的效率。
    • pt-duplicate-key-checker工具可检查索引应用的频率。
  • 小表不要建立索引，几百、几千行的小表。
  • 索引的维护要避开业务繁忙期。

# 建了索引也不走索引的情况

  • 没有查询条件，或者查询的条件列没有建索引。
  • 查询结果集是原表中的大部分数据时不走索引，大概25%以上。
  • 索引本身失效，统计数据不真实，大量的索引列值更新，索引更新不及时，导致索引失效。
  • 查询条件使用函数在索引列上，或者对索引列进行运算，会导致索引失效，例如：如avg() + - * / !等。
  • 隐式转换导致索引失效。mysql会对其中的字符串类型的数值，通过内置的函数将其转换成数值，然后再去匹配，由于做了函数操作，因而导致索引失效。
    • 比如对于数值类型，使用引号将其作为字符串查询时，会导致索引失效。
    • 或者对于字符串类型，不加引号进行查询，即便其中的值是数值，也会导致索引失效。
  • 辅助索引中>、<、not、in不走索引。
  • like '%xxx'百分号在最前面时不走索引。
  • datetime不能使用like，like是针对字符串的会有隐式转换，范围查询只能用 >、<、BETWEEN才走索引。
#数据库#MySQL#BTREE
MySQL元数据与视图
MySQL存储引擎与事务

← MySQL存储引擎与事务→

最近更新
01
二〇二五年四月十七日随笔
04-17
02
二〇二五年四月十六日随笔
04-16
03
二〇二五年四月九日随笔
04-09
更多文章>
Theme by Vdoing | Copyright © 2022-2025 Hoshinozora | MIT License
湘ICP备2022022820号-1