MariaDB 性能调优有哪些关键参数和优化策略？ - 面试题

MariaDB 的性能调优需要从多个维度进行优化，以下是主要的调优策略：

1. 配置参数优化

ini
# my.cnf 配置文件

# 连接配置
max_connections = 500
max_connect_errors = 100000
wait_timeout = 28800
interactive_timeout = 28800

# InnoDB 配置
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# MyISAM 配置
key_buffer_size = 256M
myisam_sort_buffer_size = 64M

# 查询缓存（MariaDB 10.3+ 已移除）
# query_cache_size = 64M
# query_cache_type = 1

# 临时表配置
tmp_table_size = 256M
max_heap_table_size = 256M

# 排序和连接配置
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

# 线程配置
thread_cache_size = 16
thread_stack = 256K

# 日志配置
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1

2. 内存优化

sql
-- 查看 InnoDB 缓冲池使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- 查看内存使用
SHOW STATUS LIKE 'Memory%';

3. 硬件优化

CPU：多核处理器，建议 8 核以上
内存：建议 16GB 以上，InnoDB 缓冲池占用 70-80%
磁盘：使用 SSD，配置 RAID 10
网络：千兆以上网络带宽

4. 表结构优化

sql
-- 使用合适的数据类型
-- 不推荐：VARCHAR(255) 用于状态字段
-- 推荐：TINYINT 或 ENUM

-- 规范化与反规范化权衡
-- 读取频繁：适当反规范化
-- 写入频繁：保持规范化

-- 分区表
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

5. 查询优化

sql
-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 创建合适的索引
CREATE INDEX idx_user_id_created ON orders(user_id, created_at);

-- 避免全表扫描
SELECT * FROM large_table WHERE indexed_column = 'value';

-- 使用批量操作
INSERT INTO users (name, email) VALUES
    ('John', 'john@example.com'),
    ('Jane', 'jane@example.com');

6. 监控和诊断

sql
-- 查看慢查询
SELECT * FROM mysql.slow_log 
ORDER BY query_time DESC LIMIT 10;

-- 查看表状态
SHOW TABLE STATUS FROM database_name;

-- 查看索引使用情况
SELECT * FROM information_schema.statistics 
WHERE table_schema = 'database_name';

-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

7. 性能测试工具

bash
# 使用 sysbench 进行性能测试
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=300 \
  --report-interval=10 \
  run

8. 定期维护

sql
-- 分析表
ANALYZE TABLE table_name;

-- 优化表
OPTIMIZE TABLE table_name;

-- 检查表
CHECK TABLE table_name;

-- 修复表
REPAIR TABLE table_name;

通过系统性的性能调优，可以显著提升 MariaDB 的整体性能和稳定性。