MySQL / 系统课时

控制冗余索引与写入成本

控制冗余索引与写入成本

索引不是越多越安全。每新增一个二级索引,读路径可能多一个选择,但插入、删除和部分更新也多一份维护工作。本课用一组可验证的索引说明什么叫重复或被包含,并给出“先不可见、再删除”的低风险清理方法。

学习目标

  • 区分完全重复、左前缀包含和仅列集合相同但顺序不同的索引
  • 识别索引对空间、写放大和统计维护的额外成本
  • 使用不可见索引验证访问路径依赖,再决定是否真正删除

先区分重复、包含和互补

判断冗余不能只看“列名差不多”。两个索引列顺序完全一致,通常是重复索引;(customer_id) 被 (customer_id, status) 左前缀覆盖时,前者常常是可疑冗余;但 (status, customer_id) 与 (customer_id, status) 的首列不同,支持的访问路径也不同,不能仅因为列集合相同就删除。

还有两类必须单独检查:UNIQUE 索引承担约束语义,不能由普通非唯一索引等价替代;某些索引还可能被外键、关键覆盖查询或极低频但高价值的故障查询依赖,因此“看起来没用”不等于“可以删”。

用系统表看清现有索引

下面建立三个二级索引,其中 idx_customer 是 idx_customer_status 的左前缀候选冗余,而 idx_status_customer 因列顺序相反仍是独立访问路径。

sql
CREATE DATABASE IF NOT EXISTS index_cost_lab;
USE index_cost_lab;

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(16) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_customer (customer_id),
    KEY idx_customer_status (customer_id, status),
    KEY idx_status_customer (status, customer_id)
);

SELECT
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    NON_UNIQUE,
    IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'index_cost_lab'
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

预期结果:

text
INDEX_NAME           SEQ_IN_INDEX  COLUMN_NAME   NON_UNIQUE  IS_VISIBLE
PRIMARY              1             id            0           YES
idx_customer         1             customer_id   1           YES
idx_customer_status  1             customer_id   1           YES
idx_customer_status  2             status        1           YES
idx_status_customer  1             status        1           YES
idx_status_customer  2             customer_id   1           YES

从结构上看,idx_customer 的键列顺序完全包含在 idx_customer_status 的左前缀中,所以它值得进入清理候选;idx_status_customer 不能因为包含相同两列就一起删除。

先设为不可见,再验证真实依赖

MySQL 8.0 的不可见索引不会参与常规优化器选路,但索引本身仍会继续维护。因此它适合验证“查询是否还依赖这个访问路径”,却不会立刻带来写入成本下降。

sql
ALTER TABLE orders
    ALTER INDEX idx_customer INVISIBLE;

SELECT
    INDEX_NAME,
    IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'index_cost_lab'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_customer';

预期结果:

text
INDEX_NAME    IS_VISIBLE
idx_customer  NO

把候选索引设为不可见后,应覆盖一个完整业务周期运行代表性查询,比较慢查询数量、关键 SQL 的 EXPLAIN、延迟分位数和数据库负载。如果某条查询从 idx_customer 自然切换到 idx_customer_status 的左前缀且扫描量没有恶化,说明删除风险较低。

把写入成本纳入索引价值判断

二级索引的成本主要来自三处。插入和删除需要维护每个二级索引;更新某个被索引列时,要同步修改相关索引项;索引页本身还会消耗磁盘、缓冲池和统计信息维护资源。主键发生变化时成本更高,因为 InnoDB 二级索引记录中保存主键值。

因此,清理索引时不要只问“有没有查询用它”,还要问“它承担的读收益是否值得持续写成本”。高写入表、批量导入表和热点更新表对冗余索引尤其敏感;相反,一个低写高读表可能愿意保留更多有明确查询收益的索引。

确认后删除,并重新验证

经过不可见阶段确认没有关键回退,再执行真正删除。删除后才会停止该索引的维护成本。

sql
ALTER TABLE orders
    DROP INDEX idx_customer;

SELECT INDEX_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'index_cost_lab'
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

预期结果:

text
INDEX_NAME
PRIMARY
idx_customer_status
idx_customer_status
idx_status_customer
idx_status_customer

生产清理建议按固定顺序执行:先确认唯一约束、外键和关键查询依赖,再把候选索引设为不可见,观察完整业务周期,最后删除并继续监控。对于 idx_customer_status 这类真正承担稳定访问路径的索引,即使它比单列索引更宽,也不应为了追求“索引更少”而误删。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

控制冗余索引与写入成本

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通