如何优化 MariaDB 的查询性能？有哪些常用的优化技巧？ - 面试题

MariaDB 的查询优化是提升数据库性能的核心，以下是主要的优化策略：

1. 使用 EXPLAIN 分析查询

sql
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;

关键指标：

type：访问类型（ALL < index < range < ref < eq_ref < const < system）
key：使用的索引
rows：预估扫描行数
Extra：额外信息（Using filesort, Using temporary 需要优化）

2. 索引优化

sql
-- 创建合适的索引
CREATE INDEX idx_name_age ON users(name, age);

-- 使用覆盖索引
SELECT id, name, age FROM users WHERE name = 'John';

-- 避免索引失效
-- 不推荐：WHERE name LIKE '%John%'
-- 推荐：WHERE name LIKE 'John%'

3. 查询重写

sql
-- 避免 SELECT *
-- 不推荐：SELECT * FROM users WHERE id = 1;
-- 推荐：SELECT id, name, email FROM users WHERE id = 1;

-- 使用 LIMIT 限制结果
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- 避免子查询，使用 JOIN
-- 不推荐：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 推荐：SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;

-- 使用 UNION ALL 替代 UNION（如果不需要去重）
-- 不推荐：SELECT name FROM users UNION SELECT name FROM admins;
-- 推荐：SELECT name FROM users UNION ALL SELECT name FROM admins;

4. 分页优化

sql
-- 传统分页（深分页性能差）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 优化方案1：使用游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 优化方案2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;

5. JOIN 优化

sql
-- 确保被驱动表有索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 小表驱动大表
SELECT * FROM small_table s JOIN large_table l ON s.id = l.small_id;

-- 使用 STRAIGHT_JOIN 强制连接顺序
SELECT * FROM large_table l STRAIGHT_JOIN small_table s ON s.id = l.small_id;

6. 配置优化

ini
# my.cnf 配置
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

7. 监控慢查询

sql
-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

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

通过以上优化策略，可以显著提升 MariaDB 的查询性能。