InnoDB 索引

InnoDB 索引 #

表数据结构 #

索引组织表 #

  1. 每张表必然有主键
  2. 没有显示建立主键，使用第一个非空唯一索引作为主键
  3. 没有非空唯一索引，存储引擎自动创建一个6字节大小的指针row_id作为主键

逻辑存储架构 #

  1. 所有数据存放在表空间中，表空间由段、区、页组成
  2. 段有数据段、索引段、回滚段等。概念上数据就是索引，数据段就是B+树的叶子节点，索引段是B+树的非叶子节点
  3. 区是连续页组成的空间，每个区大小固定1MB。页大小16KB，每区固定64个连续页
  4. 页是存储引擎数据管理最小单位，每次读写磁盘最小一页，即16KB
  5. 行：Innodb是面向列的存储引擎，数据按行存放，每页最多存放7992行记录

B+树 #

  1. B+树由二叉查找树和二叉平衡树发展而来，但B+树是多叉树，减小树高度
  2. B+树的记录数据都在叶子节点上，非叶子节点存放的数据是索引值
  3. 叶子节点之间以指针双向连接，形成链表，方便范围查找
  4. B+树索引分成聚簇索引和二级索引，区别在于叶子节点是否存放所有行信息
  5. B+树是平衡树，所有叶子节点在同一层，查询稳定性高

优势 #

  1. 树高度低，减少磁盘IO
    1. 每个非叶子节点存放多个范围的下一级节点的指针，而不只是普通二叉查找树的两个，靠这一点减少树高度
    2. 在索引树上查找时，先从磁盘读取根节点，每找到下一级索引就要从磁盘读取索引页得到数据继续判断，索引树高度低，使得读取一次磁盘筛选数据效率高，大幅减少磁盘IO次数
  2. 叶子链表范围查询高效 叶子节点形成索引值的顺序链表，支持顺序遍历，便于范围查询
  3. 查询稳定性
    1. B+树使用分裂+合并技术保持结构稳定性。因为插入数据导致一个节点达到容量上限，就会分裂成两个节点；因为删除数据导致两个节点太小，空间利用率低，就会合并成一个节点。这也是它的自平衡策略的一部分，同层横向扩展
    2. B+树高度变化少，一般最高四层就能存储千万以上数据
    3. 只有叶子节点才有具体数据，插入和删除对树结构影响小

对比B树 #

B树非叶子节点存放数据，使得一次读取磁盘筛选数据效率低，且树高度变高，磁盘IO负担大

聚簇索引/聚集索引/主键索引 #

  1. 按主键构造B+树
  2. 叶子节点存放的行记录信息包含所有列
  3. 通过聚集索引查找数据，可以直接在叶子节点得到全部数据

二级索引 #

  1. 根据索引列值构造B+树
  2. 叶子节点有索引值、该索引值对应的主键
  3. 通过二级索引查找数据时，会根据索引找到对应的叶子节点，再根据叶子节点上的主键值，回表到主键索引获取更多列数据

索引失效情况枚举 #

隐式类型转换导致索引失效 #

原因：查询条件和主键类型不一致，转换失败时无法使用索引，导致全表扫描

-- 索引`id`为INT类型
SELECT * FROM t WHERE id = '100'; -- 字符串转INT，索引有效（依赖优化器）
SELECT * FROM t WHERE id = '100ABC'; -- 转换失败，全表扫描

使用函数或计算索引列 #

原因：索引树结构逻辑为大小排列，函数计算后无法找到对应的节点

# 取绝对值
SELECT * FROM t WHERE ABS(id) = 100; -- 失效

NOT IN 或 NOT EXISTS #

原因：索引树结构逻辑为大小排列，not in 查询将遍历所有数据来判断是否not，无法通过比较确定位置。否定查询导致全表扫描

SELECT * FROM t WHERE id NOT IN (1,2,3); -- 失效（全表扫描）

不等于操作符 !=, <> #

原因：同上

SELECT * FROM t WHERE a != 5; -- 全表扫描
SELECT * FROM t WHERE a <> 5; -- 同上

查询条件包含IS NULL/IS NOT NULL(可能失效) #

原因：查询成本导致索引失效，如果IS NULL/IS NOT NULL过滤得到大量的数据，将会导致大量的查询和结果。失效原因并不是Null，因为innodb规定null值是最小的，数据节点会在B+树的最左边

SELECT * FROM t WHERE id NOT IN (1, 2, 3); -- 可能失效
SELECT * FROM t WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t.id = t2.id); -- 同上

OR查询索引失效 #

原因：OR 条件对单一索引表示可取可不取，含义模糊导致索引失效，除非优化器选择Index merge
应该考虑使用union all 替换 or 条件

-- 索引列`a`和`b`各自有独立索引
SELECT * FROM t WHERE a = 1 OR b = 2; -- 可能触发全表扫描（除非优化器选择Index Merge）

前导模糊查询 Like ‘%xxx’ #

原因：非末尾的模糊通配，都无法在索引树上判断接下来应该走左子叶还是右子叶

SELECT * FROM t WHERE name LIKE '%John'; -- 失效
SELECT * FROM t WHERE name LIKE 'John%'; -- 有效

索引选择性过低 #

原因：索引条件筛选数据能力低下，同样会遍历到很多数据，优化器可能会放弃索引

-- 索引列`gender`只有'M'/'F'两种值
SELECT * FROM t WHERE gender = 'M'; -- 可能全表扫描

联合索引未遵循特性导致失效 #

原因：联合索引树先按左边列值进行排列，左列值相同再按右列值排列，以此类推。如果不遵循最左前缀，即无法确定左边列的定值，就会遍历左边列值，找到每一个左边定值再找右边列值，导致右边索引列加速作用实质上无效

-- 组合索引`(a, b, c)`
# 没有遵循最左前缀匹配原则
SELECT * FROM t WHERE b = 2 AND c = 3; -- 失效（缺少最左列`a`）

# 左列范围查询导致右列索引失效
# 联合索引要全部生效，范围查询就要放最右边
SELECT * FROM t WHERE a > 1 AND b = 2; -- 仅`a`使用索引，`b`失效
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- `a`和`b`有效，

# 跳过中间列，同样是不遵循最左前缀，也相当于中间列全表范围查询
SELECT * FROM t WHERE a = 1 AND c = 3; -- 仅`a`有效，`c`失效

# 排序字段和索引顺序不一致
-- 组合索引`(a, b)`
SELECT * FROM t WHERE a = 1 ORDER BY b DESC; -- 有效（利用索引排序）
SELECT * FROM t WHERE a = 1 ORDER BY b, c; -- 失效（`c`不在索引中）

-- 组合索引`(a, b)`
SELECT * FROM t ORDER BY b, a; -- 无法利用索引排序

字符集不同，联表Join时索引失效 #

原因：字符集不同无法在索引树上匹配

-- 表A的`code`字段为utf8，表B的`code`为utf8mb4
SELECT * FROM A JOIN B ON A.code = B.code; -- 索引失效

索引设计原则 #

查多写少列建立索引 #

经常作为查询条件、联表字段(也是一种查)、排序字段和分组字段的条件，需要建立索引以提升这些查询的性能
数据量大的索引字段，增删改的性能确实会低一些，但是如果大批量的增删改不会经常发生，倒也不太有所谓

索引列应具备高选择性 #

如果索引列基数，也就是索引列值的枚举少，那么使用该索引进行筛选时，筛选数据的效率低，甚至可能导致全表扫描

组合索引优化 #

  1. 经常组合筛选和查询的列，可以考虑建立组合索引，以利用覆盖索引
  2. 索引列顺序应该根据查询的方式设计，避免索引失效。
  • 经常等值查询的放在前面
  • 经常模糊、范围、不确定查询的放在后面，留待索引下推处理

查询索引优化方法 #

首先尽量使用索引，以及避免索引失效的操作
其次还需要一些索引使用的技巧

利用覆盖索引 #

原理：直接从索引树取得查询所需数据，避免回表操作 主要用于联合索引

-- 索引 (a, b)
SELECT a, b FROM table WHERE a = 1;   -- 有效（覆盖索引）
SELECT a, b, c FROM table WHERE a = 1; -- 失效（需回表查询c）

开启和倾向索引下推 #

索引下推 （index condition pushdown ）简称ICP 原理：MySQL服务层判断where条件可以交给存储引擎层筛选，于是下推交给存储引擎层筛选，筛选结果变少，于是减少回表次数
只用于联合索引，作用是优化一部分联合索引查询的性能

# 如 联合索引 (a, b)
select * from table where a > 10 and b = 20

explain 显示 using index condition 表示使用了索引下推
  1. 联合索引左侧范围查询后，右侧索引效果应该失效
  2. 未开启索引下推时，确实如此。。存储引擎层只根据a > 10筛选索引树，回表出大量数据交给Server层，server层再筛选b = 20，显然回表次数很多
  3. 开启索引下推后，b = 20会被判断可以交给存储引擎层筛选，于是存储引擎层根据a > 10筛选，满足的进一步根据b = 20筛选，筛选效率高，回表次数减少，返回给Server层的数据也更准确

开启方法：

  1. MySQL5.6版本引入，且默认开启
  2. 命令开启：set optimizer_switch=‘index_condition_pushdown=on’;

注意点：

  1. 仅用于二级索引，主要是联合索引

查询优化方法 #

综合前面的知识，可以得出以下查询优化的注意点
这些注意点不是必须遵守的，需要根据业务需求、数据量、数据分布来分析查询成本，综合系统的可接受度来选择

查询具体列，而不是 * #

查询时 select 列表仅包含所需数据，不需要的数据不要查，不要使用 * 查询
好处

  1. 减少查询结果数据量，传输快
  2. 无效数据查过来也是无效占用内存，增大垃圾回收负担
  3. 更有可能触发覆盖索引

利用索引，避免失效 #

筛选、排序、分组、联表等操作使用索引字段

  • 注意不要对索引字段进行函数计算
  • 注意联表字段使用相同字符集
  • 注意不要对索引使用隐式数据转换，传入数据应该是对应类型

注意操作符 #

  • 对于需要 OR 条件的场景，使用Union查询替代
  • 不要在索引上使用不等于!= <>查询，考虑在业务上避免
  • Like 模糊查询匹配应该放最后

关注查询成本、筛选效率 #

  • 索引数据量太少，选择性低，每次查询筛选剩余行数太多，效率很低
  • IS NULL / IS NOT NULL，也有可能筛选效率低，最好同时匹配其他筛选条件

对于联合索引，利用其特性 #

  • 索引左列范围、模糊、不指定条件等不精确查询，都可能导致右列索引能力失效
    即需要左侧指定，右侧才有效。（涵盖了所谓的最左前缀匹配原则）
  • 排序字段顺序遵循索引顺序，否则无法利用索引排序

深分页优化 #

分页性能瓶颈 #

MySQL分页采用Limit和Offset进行分页查询，Limit限制本次返回记录数，Offset表示需要跳过的记录数
当Offset很大，即深分页时，需要跳过大量的记录找到取数据的起始点，会导致性能瓶颈

  1. 大量数据行扫描，时间成本高、磁盘IO压力大
  2. 扫描过的数据多，影响数据多，也会被很多数据的状态影响，更可能导致锁竞争问题

优化方法 #

深分页性能瓶颈根因是Offset扫描数据量大，优化思路也围绕Offset，要么不使用Offset，要么Offset扫描数据不能多

  1. 业务上避免深分页
    不用深分页了，普通深度的分页Offset不大，扫描数据的性能可以接受，自然不会有问题。页面或者应用层限制分页数量最大值即可
  2. 游标分页
    游标是上一次分页的终点，代表了本次查询的数据起点，每次查询记录在客户端，翻页时自然不用扫描数据
# 传统分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 1000;
# 游标分页
SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 10;

缺陷

  • 无法跳页，但往往业务上就是需要跳页
  • 实现复杂，客户端存储游标倒是小事。主要是多列排序时的分页游标怎么确定比较困难，业务往往需要支持多字段的正逆序组合方式

  1. 索引优化
    主要是对排序列使用索引，减少排序所需的时间

  2. 使用分区表
    分区表存放在不同的物理存储区域，数据扫描时仅扫描相关的物理区域，天然筛掉一部分数据，减少扫描数据量

  • 但分区的设计是固定的，无法同时支持多种排序分页条件的性能提升
  1. 预分页与缓存
    采用缓存存储分页查询的结果，减少数据库负担，对于短时间频繁的反复分页查询有很好的提速效果

避免大表联小表和无意义联表 #

以 from 驱动表 join 被驱动表 on condition where xxx 为例
联表过程是：
驱动表逐行根据condition查询被驱动表，将对应数据行互相连接形成中间表
where对中间表进行筛选
所以如果使用大表联小表，联表过程就可能产生大量的查询过程，反之则不然

另外在代码层如果存在联表查询多表字段的SQL，但每次一些字段不需要查出的，也没必要联相关表，减少联表消耗

业务代码层优化 #

  1. 避免循环中查询数据库
    反复的创建数据库链接和会话也是不小的开销，如果非必要，就不要这样做。否则会导致整体查询时间的延长，以及数据库负担的不必要提高
  • 对于数据量不大的查询，最好一次性查出相关列，而不是在循环中等值查询
  • 对于每次查询都是相同条件的，更不要在循环中执行，循环开始前查出，在内存中备用即可

  1. 避免一次查询大量结果
    大量结果的返回会造成较大的网络负担，应该进行分页，或者条件分组的查询

  2. 缓存查询结果 使用缓存，减少数据库压力

优化的认知 #

  • 从前面的一些方法可以看到，优化的操作往往不能达到尽善尽美，总是具有各种限制，来自业务场景，或者方法本身效果没那么好
  • 可以看出，优化这件事，本质是综合考虑了业务需求、数据量、数据分布，继而分析查询成本，考量系统的可接受度的基础上进行优化的，而且往往不能优化到理论上尽善尽美，而是在实际场景上，能好一些是一些
  • 特别是考量整体的优化幅度，对于权重更高的场景优化更多，对整个系统的性能提升，远远大于对有限低发生量的场景的优化
  • 更不应该因为技术情节，优化低权重场景，降低高权重场景性能，本末倒置
2025-05-03