MariaDB 优化技巧

FreeGuideOnline 最新 2026-07-14

cnf [mysqld] innodb_buffer_pool_size = 8G # 8 GB 内存服务器示例 innodb_buffer_pool_instances = 4 # 大于 1GB 时，建议分多个实例减少锁争用


**如何查看命中率**：

```sql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests：缓冲池读请求次数
Innodb_buffer_pool_reads：从磁盘读取的次数

命中率 = (1 - reads / read_requests) * 100%，理想值应 > 99%。

2.2 日志文件大小与刷新

innodb_log_file_size 影响写入性能和崩溃恢复时间。一般设置为 Buffer Pool 大小的 25%～50%，但不超过 4GB（总大小）。更大的日志文件减少 checkpoint 刷新次数，提升写入性能。

innodb_log_file_size = 1G
innodb_log_files_in_group = 2         # 两个日志文件
innodb_flush_log_at_trx_commit = 2    # 平衡持久性和性能（允许每秒一次刷新）

=1：每次提交都刷盘，最安全，但最慢。
=2：每秒刷盘，宕机可能丢失最近 1 秒的事务，适合对性能要求高的场景。
=0：每秒刷盘，但不保证，性能最好，但不安全。

2.3 表定义缓存与打开文件限制

如果数据库有大量表，需要增大这些值，避免频繁打开和关闭表文件。

table_open_cache = 4000
table_definition_cache = 2000
open_files_limit = 10000

可以通过 SHOW GLOBAL STATUS LIKE 'Opened_tables'; 观察：如果 Opened_tables 快速增长，说明缓存不够。

2.4 连接与线程管理

每有一个客户端连接，MariaDB 就会创建一个线程。如果连接数过多，内存开销会激增。

max_connections = 200
thread_cache_size = 100               # 缓存线程以供复用

观察 SHOW STATUS LIKE 'Threads_created'; — 如果该值持续增加，可适当调大 thread_cache_size。

2.5 查询缓存（谨慎使用）

查询缓存将 SELECT 结果完整缓存，但受表写操作影响，容易导致锁竞争。现代高并发系统建议禁用：

query_cache_type = 0
query_cache_size = 0

如果读远多于写，可适度启用，但需要监控锁等待情况。

三、索引优化：让查询快起来

3.1 基本原则

为 WHERE、JOIN、ORDER BY、GROUP BY 列创建索引。
选择性高的列优先：选择性 = 不重复的值 / 总行数，越接近 1 越好。
最左前缀：复合索引 (A, B, C) 可以加速 A=?、A=? AND B=?、A=? AND B=? AND C=?，但不能单独使用 B 条件。
避免过犹不及：过多索引会拖慢 INSERT/UPDATE/DELETE，并占用额外空间。

3.2 使用 EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01';

关注以下字段：

type：连接类型，从好到差依次为 const、eq_ref、ref、range、index、ALL。ALL 表示全表扫描，必须优化。
key：实际使用的索引。
rows：估计扫描的行数，越小越好。
Extra：Using index 表示覆盖索引（无需回表），性能最好；Using filesort 表示额外排序，Using temporary 表示使用临时表，需警惕。

3.3 覆盖索引

查询列全部包含在索引中时，MariaDB 只需扫描索引，无需回表读取数据行。

CREATE INDEX idx_customer_order ON orders (customer_id, order_date, total);
SELECT customer_id, order_date, total FROM orders WHERE customer_id = 100;

此时 Extra 显示 Using index。

3.4 避免索引失效的常见写法

在索引列上使用函数或运算：WHERE YEAR(order_date) = 2023 → 应改为 WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'。
使用 OR 但部分条件没有索引：可能导致全表扫描，考虑使用 UNION 改写。
隐式类型转换：列是字符串，却传入数字，索引可能失效。
使用 LIKE '%abc'（前置通配符）索引失效，LIKE 'abc%' 可以使用索引。

四、查询 SQL 写法优化

4.1 尽量精确 SELECT，避免 SELECT *

只读取需要的列，减少数据传输和内存占用，更容易利用覆盖索引。

4.2 限制结果集大小

分页查询务必使用 LIMIT offset, count，但大偏移量会效率低下。优化方案：

-- 慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 快：基于上一次获取的最后一个 id
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

4.3 JOIN 优化

小表驱动大表：在多表连接时，优化器通常选择较小的结果集作为驱动表。
确保 ON 连接条件字段有索引。
避免在 JOIN 条件中使用函数。
使用 INNER JOIN 替代逗号连接，意图更清晰且一般不影响性能。

4.4 子查询 vs. JOIN

在许多情况下，连接（JOIN）比子查询更高效，特别是非相关的子查询可以被重写为 JOIN。例如：

-- 子查询
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);
-- 改写为 JOIN
SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.active = 1;

对于 MySQL/MariaDB，应测试实际执行计划，因为优化器有时会自动转换。

4.5 合理使用 INSERT、UPDATE 批处理

INSERT INTO ... VALUES (...), (...), (...) 批量插入，减少事务开销。
对于大批量更新，可分批提交，避免长事务锁表和 undo 堆积。

五、表结构与存储引擎优化

5.1 选择合适的数据类型

使用最小满足需求的列类型：TINYINT 代替 INT，日期用 DATE 而非 VARCHAR。
字符串长度适当：固定长度用 CHAR，可变长度用 VARCHAR。
避免使用 TEXT/BLOB 当做主键或排序列。
IP 地址可用 INT UNSIGNED + INET_ATON()/INET_NTOA() 代替 VARCHAR(15)。

5.2 表的碎片整理

频繁更新/删除 InnoDB 表可能导致碎片化，定期执行：

ALTER TABLE tablename ENGINE=InnoDB;

这将重建表，回收空间并重排数据，提升顺序扫描性能。

5.3 分区表

对于超大型表，可考虑水平分区（按范围、列表、哈希）。例如按年月进行 RANGE 分区，能够快速清除旧数据和提升特定范围查询性能。

CREATE TABLE logs (
  id INT, message TEXT, log_date DATE
) PARTITION BY RANGE (YEAR(log_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

六、服务器性能监控与诊断

6.1 常用状态变量

SHOW GLOBAL STATUS LIKE '%created_tmp%';   -- 临时表统计
SHOW GLOBAL STATUS LIKE '%select_scan%';  -- 全表扫描次数
SHOW GLOBAL STATUS LIKE '%sort%';         -- 排序统计
SHOW GLOBAL STATUS LIKE '%innodb_%';       -- InnoDB 特定指标

重点关注：

Created_tmp_disk_tables：越少越好，磁盘临时表慢。
Select_scan：做好索引后期望不大增。
Innodb_row_lock_waits / Innodb_row_lock_time：锁等待，高则存在锁竞争。

6.2 慢查询日志

开启慢查询日志，设置最小执行时间，找出性能瓶颈。

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2         # 记录超过 2 秒的查询
log_queries_not_using_indexes = 1   # 也记录未使用索引的查询

然后使用 mysqldumpslow 或 pt-query-digest 工具分析。

6.3 PROCESSLIST 与 SHOW ENGINE INNODB STATUS

SHOW FULL PROCESSLIST;       -- 查看当前运行的查询，发现长时间未结束的线程
SHOW ENGINE INNODB STATUS\G  -- InnoDB 引擎详细状态，包含死锁、锁等待、I/O、缓冲池信息

6.4 使用 Performance Schema 和 sys 库

MariaDB 10.5+ 默认启用 Performance Schema，可借助 sys 库（兼容 MySQL 5.7+ 视图）方便监控：

SELECT * FROM sys.schema_unused_indexes;       -- 未使用的索引
SELECT * FROM sys.statements_with_full_table_scans; -- 全表扫描语句
SELECT * FROM sys.io_global_by_wait_by_latency;    -- I/O 延迟