由一个面试题引发出的众多面试题之数据库性能优化

提问：假设现在你所在公司有个数据库-单表1千万数据，未来1年还会增长多500万，性能比较慢，说下你的优化思路或者说你会怎么办呢？

俗话说的好，解铃还需系铃人，我们要通过现象看本质，为什么数据多了性能就会慢呢？为什么为什么为什么呢？ 那我们来慢慢分析，我能想到以下内容，欢迎大家补充，我们一起共同进步：

1. 索引失效或缺失 无效索引扫描：查询未命中合适索引，导致全表扫描（1000万行扫描效率极低）。 索引碎片化：频繁增删导致索引物理存储不连续，降低检索效率。
2. I/O瓶颈 磁盘读写压力：全表扫描或大范围索引扫描需从磁盘加载大量数据（如SELECT *查询）。 缓冲池不足：内存缓冲区（如InnoDB Buffer Pool）过小，频繁磁盘交换。
3. 执行计划劣化 统计信息过时：数据库误判数据分布（例如认为某状态值只有10行，实际有500万行），生成低效计划。 复杂查询优化失败：多表JOIN、子查询或聚合函数（如GROUP BY）未优化到位。
4. 锁与并发冲突 锁竞争：高频写入导致行锁/表锁阻塞查询（尤其事务未及时提交）。 MVCC开销：长事务导致版本链过长，查询需遍历多个版本。

我们在已知问题本质原因条件下，是不是再去想解决方案就会清晰很多了呢？因此我们来将他们逐个击破即可。

索引失效解决方案

那这个时候我们就可以将慢查询语句保存到某个log中，然后通过EXPLAIN分析，光说不练假把式，为了方便我们创建一个名为CBook的表

CREATE TABLE `cbook` (
  `m_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `m_isbn` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `m_pressInfo` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `m_price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `m_page` int(11) DEFAULT NULL COMMENT '页数',
  `m_author` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `m_num` int(11) DEFAULT NULL COMMENT '数量',
  PRIMARY KEY (`m_isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='图书表';

同时向表中插入一些数据：

INSERT INTO `cbook` (`m_name`, `m_isbn`, `m_pressInfo`, `m_price`, `m_page`, `m_author`, `m_num`)
VALUES
('数据结构与算法分析：C语言描述', '9787111571768', '机械工业出版社', 59.00, 454, 'Mark Allen Weiss', 10),
('计算机网络（第 7 版）', '9787115493033', '人民邮电出版社', 69.00, 508, 'Adrian·Tanenbaum', 15),
('Java 程序设计：从入门到精通', '9787302613470', '清华大学出版社', 88.00, 436, 'John Smith', 8);

再补充几条数据：

INSERT INTO `cbook` VALUES 
('平凡的世界', '9787020190512', '人民文学出版社', 39.00, 240, '路遥', 8),
('Python 程序设计', '9787111649068', '机械工业出版社', 119.00, 688, 'Eric Matthes', 12),
('计算机网络架构与原理', '9787302444863', '清华大学出版社', 89.00, 576, '吴功宜', 15),
('数据科学与应用', '9787539982536', '江苏凤凰文艺出版社', 45.00, 320, '张三', 6),
('深度学习基础', '9787544262335', '南海出版公司', 28.00, 192, '李四', 8);

在EXPLAIN 输出的 12 个字段中，有六个最关键的字段是需要我们关注的，见下表。

现在就让我们来使用EXPLAIN来更加具体的认识以上内容。 执行sql语句：

EXPLAIN SELECT * FROM cbook WHERE m_isbn = '9787020190512';

结果如下：

在这里插入图片描述

从表中看到，我们的Extra字段是为NULL的，而为NULL的原因是： SELECT * 需要所有列数据，主键索引 PRIMARY(m_isbn) 仅包含 ISBN 列因此必须回表查询完整行数据 → 无法使用 Using index，若我们将sql改写为

-- 只查询索引列（Extra 显示 Using index）
EXPLAIN SELECT m_isbn FROM cbook WHERE m_isbn = '9787020190512';

执行结果如下：

在这里插入图片描述

可以看到，Extra列内容变为了using index,这是因为改写后的sql满足以下 全部条件 时 Extra 才会显示 Using index：

1. 查询列 完全包含 在索引中
2. 使用 覆盖索引（covering index）
3. 不需要 回表查询

由于我们查询列只需要m_isbn，且m_isbn为主键列，所以满足全部条件。 全字段解析（sql未改写前）

同时需要大家注意以下内容：

1. const类型下Extra: NULL为何是最优？ const访问类型的本质：当查询通过主键或唯一索引直接定位到唯一一行时（如WHERE id=1），MySQL 会将其标记为const类型，这是仅次于system的最高效访问类型。此时： 数据库无需执行额外操作（如文件排序、临时表生成），因此Extra字段显示为NULL。 Using index（覆盖索引）的场景：当查询所需字段全部包含在索引中，无需回表查询数据时，会显示Using index。但const类型下，即使需要回表（如查询非索引字段），由于主键定位速度极快，回表成本几乎可以忽略，因此整体效率依然最优。 结论：const类型本身已代表 “通过唯一索引精准定位”，是比Using index更底层的高效判定，两者不冲突（例如：const + Using index组合则是 “双重优化”）。
2. varchar(20) utf8mb4的key_len计算逻辑 字段定义：varchar(20) CHARACTER SET utf8mb4 计算步骤： 字符编码字节数： utf8mb4编码每个字符最多占用 4 字节（可存储 Emoji、生僻字等），因此 20 个字符的最大存储空间为： 20字符 × 4字节/字符 = 80字节。 长度标识字节数： varchar类型需要额外字节存储字符串实际长度： 当字段长度≤255 时，使用 1 字节标识长度； 当字段长度 > 255 时，使用 2 字节标识长度（本例中varchar(20)未超过 255，但 MySQL 对utf8mb4编码的varchar统一使用 2 字节长度标识）。 因此长度标识占用：2字节。 最终key_len： 字符编码字节数 + 长度标识字节数 = 80字节 + 2字节 = 82字节。

我们再来执行下列sql：

EXPLAIN SELECT * FROM cbook WHERE m_page > 300;

执行结果如下：

在这里插入图片描述

接下来我试试使用复合索引，我们先执行以下sql创建复合索引：

ALTER TABLE cbook 
  ADD INDEX idx_price (m_price),
  ADD INDEX idx_author_press (m_author, m_pressInfo);

然后执行下列sql语句：

EXPLAIN SELECT m_author, m_pressInfo 
FROM cbook 
WHERE m_author = 'Eric Matthes' 
  AND m_pressInfo LIKE '%机械%';

执行结果如下：

在这里插入图片描述

这里需要注意，由于左模糊匹配会导致联合索引中m_pressInfo失效，但m_author仍然生效，我们可以执行以下sql测试:

-- 删除原索引
DROP INDEX idx_author_press ON cbook;

-- 创建反转顺序索引
CREATE INDEX idx_press_author ON cbook(m_pressInfo, m_author);

-- 重写查询（需改变条件顺序）
EXPLAIN SELECT m_author, m_pressInfo 
FROM cbook 
WHERE m_pressInfo LIKE '机械%'  -- 改为右通配
  AND m_author = 'Eric Matthes';

在这里插入图片描述

可以看到执行结果中，key_len变成了806，因此验证了左模糊匹配会导致联合索引中m_pressInfo失效的问题.

我们再来看看索引失效场景

在这里插入图片描述

可以看到，由于我们第一次price字段参与计算导致无法走索引,因此在索引列上进行计算操作，将导致索引失效。

经过以上EXPLAIN使用，相信大家已经学的差不多了，前辈们呢已经总结好了许多的常见的索引失效场景，因此，大家在考虑索引失效的时候，可以想想是不是有使用导致了索引失效，以下是常见的索引失效场景:

• 使用 SELECT * 进行查询; SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
• 创建了组合索引，但查询条件未遵守最左匹配原则;
• 在索引列上进行计算、函数、类型转换等操作;
• 以 % 开头(左匹配)的 LIKE 查询比如 like '%abc';
• 查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
• 发生隐式转换;
• 使用了 is not null 、 is null ，索引不生效（is null并不会影响索引使用，mysql认为扫描全表都比使用索引快的时候，那么索引就不会被使用！

违法最左匹配原则：

在这里插入图片描述

索引缺失解决方案：重建索引

一、核心原理

本质：通过删除旧索引并创建全新索引，消除物理存储碎片化，重构B+树结构。 关键过程：

表重建机制：

MySQL创建临时空表 → 按主键顺序读取旧表数据 → 按顺序插入新表 
→ 重建全量索引 → 原子性重命名表

碎片化消除逻辑：

• 旧索引因频繁增删导致物理存储不连续，新索引按插入顺序排列，提升磁盘I/O效率。
• 聚集索引（主键）重建后，二级索引的指针引用同步优化。

二、MySQL实施语法（InnoDB引擎）

三、优缺点对比

四、执行流程详解（以OPTIMIZE TABLE为例）

1. 准备阶段：
   • 创建临时表结构（与原表完全一致）。
2. 数据迁移：

在这里插入图片描述

1. 原子切换：
   • 旧表重命名为表名_old，新表重命名为原表名。
   • 删除表名_old相关文件。

总结

重建索引是解决索引碎片化的终极手段，但需在"性能需求"与"业务影响"间平衡。建议建立分级策略：

• 轻度碎片（<20%）：仅更新统计信息（ANALYZE TABLE）
• 中度碎片（20-30%）：使用OPTIMIZE TABLE在线重建
• 重度碎片（>30%）：分库分表+全量重建结合

查询语句：

SELECT 
    t.TABLE_NAME,
    i.INDEX_NAME,
    t.DATA_LENGTH,
    t.INDEX_LENGTH,
    t.DATA_FREE,
    ROUND(t.DATA_FREE / t.INDEX_LENGTH * 100, 2) AS fragmentation_percentage
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.STATISTICS i 
    ON t.TABLE_SCHEMA = i.TABLE_SCHEMA 
    AND t.TABLE_NAME = i.TABLE_NAME
WHERE 
    t.TABLE_SCHEMA = 'test' --替换成自己的数据库名称
    AND t.TABLE_NAME = 'cbook'--替换成自己表名称
GROUP BY 
    t.TABLE_NAME, i.INDEX_NAME
ORDER BY 
    fragmentation_percentage DESC;

在这里插入图片描述

聚集索引与填充因子优化策略

一、聚集索引键的选择原则

核心原则：聚集索引决定表的物理存储顺序，选择不当将导致严重碎片化

❌ 避免选择的三类字段

1. 频繁更改的列
   • 问题：值变更触发行物理位置移动，导致页分裂（Page Split）
   • 案例：订单状态字段（频繁从"待支付"→"已完成"）
2. 宽列（大长度字段）
   • 影响：单页容纳行数减少，页分裂阈值降低
   • 数据对比： 字段类型单页容量（16KB）页分裂频率VARCHAR(100)约100行中VARCHAR(1000)约10行高
3. 随机值/GUID
   • 碎片机制：新行无法填充已有页空白，只能追加到表尾或随机位置
   • 优化方案：
     • SQL Server：使用NEWSEQUENTIALID()生成有序GUID
     • MySQL：使用UUID_TO_BIN(UUID(), 1)生成有序UUID

同时，当存在以下三种情况时，不推荐使用索引：

1. 数据量较小 当数据量较小时，使用索引并不能带来明显的性能提升，反而可能会降低查询速度。这是因为索引本身也需要占用一定的空间，如果数据量太小，使用索引就会浪费空间，增加系统负担。
2. 数据更新频繁 如果表中的数据更新频繁，那么使用索引就会带来性能问题。这是因为每次更新都需要更新索引，这会增加系统负担。如果数据更新的频率非常高，那么使用索引就会成为瓶颈。
3. 列的基数非常低：基数是指列中唯一值的数量。如果某个列的基数非常低，即该列中的大部分值都是重复的，那么使用索引可能不会带来很大的性能提升。因为索引的目的是减少数据的扫描范围，但当基数低时，扫描整个表可能比使用索引更高效。

✅ 优先选择的三类字段

1. 自增ID（AUTO_INCREMENT）
   • 优势：新行始终追加到表尾，物理存储天然连续
   • 适用场景：日志表、订单表等追加型业务
2. 时间戳（TIMESTAMP）
   • 特性：按时间顺序插入，碎片增长缓慢
   • 组合优化：CREATE TABLE logs (ts TIMESTAMP PRIMARY KEY, ...)
3. 稳定窄列（如INT类型）
   • 空间效率：4字节INT比16字节UUID节省75%空间
   • 性能对比：INT主键查询速度比UUID快30%+

二、填充因子（FILLFACTOR）深度解析

核心作用：通过预留页空间减少页分裂，控制碎片生成速度

🔍 MySQL InnoDB的实现机制

📊 填充因子设置策略

I/O瓶颈：数据库参数调优相关内容

一、innodb_buffer_pool_size：缓冲池大小的黄金法则

核心作用：InnoDB 将表数据和索引缓存至内存的区域，所有数据读写均经过缓冲池。

▶ 调优逻辑与设置原则

⚠ 避免误区：

• 并非越大越好：过大可能引发操作系统 SWAP，导致性能骤降。
• 初始化耗时：超大规模缓冲池（如 100GB+）重启初始化可能耗时数分钟。

▶ 精准调优：核心监控指标

-- 计算缓冲池命中率（理想值 > 99%）
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

在这里插入图片描述

例如在我的数据库中，执行此命令，可以看到Innodb_buffer_pool_read_requests值为2604，而Innodb_buffer_pool_reads的值为367，因此命中率为1-367/2604=0.86

警报阈值：

• 命中率 < 99%：需关注热点数据缓存情况。
• 命中率 < 95%：必须增大缓冲池，存在严重磁盘 I/O 瓶颈。

二、innodb_buffer_pool_instances：缓冲池实例数的并发优化

核心作用：将单一缓冲池拆分为多个独立实例，分散锁竞争（如 buffer_pool_mutex）。

▶ 调优逻辑与设置原则

三、缓冲池预热：innodb_buffer_pool_dump* 与 load*

核心作用：解决 MySQL 重启后缓冲池冷启动导致的性能骤降，通过转储和加载热页加速缓存恢复。

▶ 核心参数与逻辑

▶ 状态监控与手动干预

-- 查看转储/加载状态
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- 手动转储/加载（无需重启）
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

3. 缓冲池健康检查清单

1. 缓冲池大小是否 ≥ 核心表数据+索引大小的 1.5 倍？
2. 实例数是否满足 缓冲池大小 / 实例数 ≥ 1GB？
3. 预热功能是否已启用，ib_buffer_pool 文件是否存在？
4. 最近 7 天缓冲池命中率是否稳定在 99% 以上？

当然我们除了对数据库参数进行调优，也可以通过提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘等

关于三执行计划劣化解决方案与一完全类似，所以我们直接开始解决最后一个问题锁竞争和MVCC开销：架构设计与优化

这里博主只简单介绍一些可选方案，具体内容后续可能会更新，或再出一篇博客专门讲解

关于这个问题，我们更多能做的或许就是对架构与设计优化

方案对比与选择矩阵

同时，实在不行，我们只有祭出我们的终极大招：

分库分表：高并发场景的终极架构方案

一、分库分表决策流程

核心原则：无通用策略，需结合业务增长预期与技术瓶颈动态调整。

▶ 决策流程图

#mermaid-svg-9VmGCj7Rwdeoggeq {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq .error-icon{fill:#552222;}#mermaid-svg-9VmGCj7Rwdeoggeq .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-9VmGCj7Rwdeoggeq .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-9VmGCj7Rwdeoggeq .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-9VmGCj7Rwdeoggeq .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-9VmGCj7Rwdeoggeq .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-9VmGCj7Rwdeoggeq .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-9VmGCj7Rwdeoggeq .marker{fill:#333333;stroke:#333333;}#mermaid-svg-9VmGCj7Rwdeoggeq .marker.cross{stroke:#333333;}#mermaid-svg-9VmGCj7Rwdeoggeq svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-9VmGCj7Rwdeoggeq .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq .cluster-label text{fill:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq .cluster-label span{color:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq .label text,#mermaid-svg-9VmGCj7Rwdeoggeq span{fill:#333;color:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq .node rect,#mermaid-svg-9VmGCj7Rwdeoggeq .node circle,#mermaid-svg-9VmGCj7Rwdeoggeq .node ellipse,#mermaid-svg-9VmGCj7Rwdeoggeq .node polygon,#mermaid-svg-9VmGCj7Rwdeoggeq .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-9VmGCj7Rwdeoggeq .node .label{text-align:center;}#mermaid-svg-9VmGCj7Rwdeoggeq .node.clickable{cursor:pointer;}#mermaid-svg-9VmGCj7Rwdeoggeq .arrowheadPath{fill:#333333;}#mermaid-svg-9VmGCj7Rwdeoggeq .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-9VmGCj7Rwdeoggeq .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-9VmGCj7Rwdeoggeq .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-9VmGCj7Rwdeoggeq .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-9VmGCj7Rwdeoggeq .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-9VmGCj7Rwdeoggeq .cluster text{fill:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq .cluster span{color:#333;}#mermaid-svg-9VmGCj7Rwdeoggeq div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-9VmGCj7Rwdeoggeq :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}

是

是

否

否

单表数据量/并发是否瓶颈?

分表能否解决?

仅分表

分库+分表

持续监控

▶ 关键判断指标

二、分表策略：解决单表查询效率问题

核心价值：拆分单表数据量，提升查询效率，但无法突破单库I/O瓶颈。

▶ 分表实现方式

垂直分表（字段拆分）

将热数据字段与冷数据字段分离：

-- 原表
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  phone VARCHAR(20),
  address TEXT,
  create_time DATETIME,
  profile TEXT  -- 冷数据字段
);

-- 拆分为热表与冷表
CREATE TABLE users_hot (id, name, phone, create_time);
CREATE TABLE users_cold (id, address, profile);

水平分表（行拆分）

按时间分表（如每月一个表）：

CREATE TABLE orders_202506 (like orders);
CREATE TABLE orders_202507 (like orders);

按ID取模分表：

-- 10张表
CREATE TABLE orders_0 (like orders);
CREATE TABLE orders_1 (like orders);
-- ... 直到orders_9

▶ 分表局限性

• 单库I/O瓶颈：所有分表仍在同一数据库实例，受限于磁盘读写速度。
• 事务一致性：跨表事务需手动处理（如分布式事务）。
• 复杂查询：多表JOIN需应用层处理，或引入中间件（如MyCat）。

三、分库分表：突破单库并发限制

核心价值：将数据分散到多个数据库实例，提升整体并发能力与资源利用率。

▶ 分库分表策略对比

▶ 水平分库分表示例（ID取模）

分库规则：

def get_db_id(user_id):
    return user_id % 4  # 4个数据库实例

分表规则：

def get_table_id(user_id):
    return user_id % 10  # 每个库10张表

表结构：

-- 库1中的表
CREATE TABLE users_0 (id, name, ...);
CREATE TABLE users_1 (id, name, ...);
-- 库2中的表同理

四、分库分表 vs 其他方案对比

这篇博客就到此结束，我们下次再见！！！！