乐闻世界logo
5月27日 17:38

如何优化MariaDB查询性能？

查询性能是数据库系统的生命线。一条低效的SQL可能拖垮整个应用，而一次精准的优化能让响应时间从秒级降到毫秒级。这篇文章从诊断、索引、写法、配置四个层面，给出经过生产验证的优化方法。

用 EXPLAIN 定位性能瓶颈

优化之前，先要找到问题。EXPLAIN 是最直接的诊断工具：

sql
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

输出中有四个字段值得重点关注：

  • type — 访问类型，从差到优依次为 ALL → index → range → ref → eq_ref → const。出现 ALL 意味着全表扫描，必须优化
  • key — 实际使用的索引。如果为 NULL，说明索引未被命中
  • rows — 预估扫描行数。数字越大，查询越慢
  • Extra — Using filesort 表示额外排序，Using temporary 表示使用了临时表，两者都应尽量避免

一个简单的判断标准：type 不是 ALL 且 Extra 没有 Using filesort/Using temporary，查询基本合格。

索引：最有效的加速手段

建立合适的复合索引

单列索引在多条件查询时往往不够用。复合索引遵循最左前缀原则，把区分度高的列放前面：

sql
-- 假设查询条件为 WHERE user_id = ? AND status = ?
-- user_id 区分度远高于 status，放前面
CREATE INDEX idx_user_status ON orders(user_id, status);

用覆盖索引避免回表

当查询的列全部包含在索引中时，引擎无需回表读取数据行，性能提升显著：

sql
-- 索引 idx_user_status(user_id, status) 无法覆盖此查询（需要 amount 列）
SELECT user_id, status, amount FROM orders WHERE user_id = 100;

-- 建立覆盖索引后，直接从索引读取所有数据
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

避免索引失效的常见写法

以下写法会导致索引无法命中：

  • 对索引列使用函数：WHERE YEAR(created_at) = 2025 改为 WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
  • 隐式类型转换：WHERE varchar_col = 123 改为 WHERE varchar_col = '123'
  • 前缀模糊查询：WHERE name LIKE '%John' 改为 WHERE name LIKE 'John%'
  • 使用 OR 连接不同索引列：改用 UNION ALL 拆分

查询写法的优化技巧

只查需要的列

SELECT * 是性能杀手。它强制读取所有列的数据，增加 I/O 和内存开销，还可能破坏覆盖索引：

sql
-- 不推荐
SELECT * FROM users WHERE id = 1;

-- 推荐：只查业务需要的列
SELECT id, name, email FROM users WHERE id = 1;

用 JOIN 替代子查询

MariaDB 优化器对子查询的处理不如 JOIN 高效，特别是 IN 子查询：

sql
-- 不推荐
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 推荐
SELECT u.id, u.name, u.email FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

UNION ALL 替代 UNION

UNION 会对结果去重，需要额外的排序操作。如果确定结果集无重复，用 UNION ALL 省掉去重开销：

sql
-- 不需要去重时
SELECT name FROM customers WHERE region = 'east'
UNION ALL
SELECT name FROM suppliers WHERE region = 'east';

深分页的两种优化方案

OFFSET 值很大时，数据库需要扫描并跳过前面的所有行：

sql
-- 传统写法：跳过 10 万行，极其缓慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 方案一：游标分页（要求排序字段连续且有索引）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 方案二：延迟关联（先查主键再回表，减少扫描列数）
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;

JOIN 优化

  • 被驱动表的连接列必须有索引
  • 小结果集驱动大表，减少循环次数
  • 当优化器选错连接顺序时，用 STRAIGHT_JOIN 强制指定：
sql
SELECT * FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.small_id;

配置层面的调优

InnoDB 缓冲池

这是影响 InnoDB 性能最重要的参数，建议设为物理内存的 50%-70%：

ini
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

排序和连接缓冲

ini
sort_buffer_size = 4M        -- 每个连接的排序缓冲
join_buffer_size = 4M        -- 每个无索引连接的缓冲
read_rnd_buffer_size = 4M    -- MRR 读取缓冲

临时表大小

ini
tmp_table_size = 256M
max_heap_table_size = 256M

超过此大小的临时表会写到磁盘，导致性能骤降。

关于查询缓存

注意：MariaDB 10.6 起默认禁用查询缓存，后续版本已移除该功能。如果使用 10.6+，不要配置 query_cache_size，而是关注应用层缓存（如 Redis）。

监控慢查询

开启慢查询日志，定期分析并优化：

sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

结合 pt-query-digest 工具分析慢查询日志，找出最需要优化的 SQL：

bash
pt-query-digest /var/lib/mysql/slow.log

优化决策路径

面对一个慢查询，按以下顺序排查：

  1. 先用 EXPLAIN 查看执行计划，确认是否走了索引
  2. 如果走了索引仍然慢，考虑建立覆盖索引或调整索引列顺序
  3. 如果索引没有问题，检查查询写法是否有优化空间（避免 SELECT *、子查询改 JOIN、深分页优化）
  4. 如果单条 SQL 已最优，考虑配置调优（缓冲池、排序缓冲、临时表大小）
  5. 配置也调不动了，考虑架构层面优化（读写分离、分库分表、引入缓存）

每个阶段都有明确的检查点和动作，避免盲目调参。

标签：MariaDB