MariaDB性能优化 #

性能优化方向
├── 查询优化
│   ├── SQL语句优化
│   ├── 索引使用
│   └── 执行计划分析
├── 结构优化
│   ├── 表设计
│   ├── 索引设计
│   └── 分区设计
├── 配置优化
│   ├── 内存配置
│   ├── 缓存配置
│   └── 连接配置
└── 硬件优化
    ├── CPU
    ├── 内存
    └── 存储

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 查看完整执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John';

-- 关键字段说明
-- id: 查询标识符
-- select_type: 查询类型
-- table: 表名
-- type: 访问类型
--   system > const > eq_ref > ref > range > index > ALL
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 索引长度
-- rows: 预估扫描行数
-- Extra: 额外信息

-- 避免：无索引条件
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 优化：使用范围条件
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 避免：LIKE以%开头
SELECT * FROM users WHERE name LIKE '%John%';

-- 优化：使用前缀匹配
SELECT * FROM users WHERE name LIKE 'John%';

-- 避免：OR条件
SELECT * FROM users WHERE name = 'John' OR email = 'john@example.com';

-- 优化：使用UNION
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

-- 使用覆盖索引
-- 创建索引
CREATE INDEX idx_user_status_name ON users(status, name);

-- 使用覆盖索引（不需要回表）
SELECT name FROM users WHERE status = 1;

-- 使用索引排序
SELECT * FROM users WHERE status = 1 ORDER BY name;

-- 避免索引失效
-- 错误：使用函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- 正确：不使用函数
SELECT * FROM users WHERE name = 'John';

-- 传统分页（大偏移量性能差）
SELECT * FROM users ORDER BY id LIMIT 10000, 10;

-- 优化：使用WHERE条件
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- 优化：使用JOIN
SELECT u.* FROM users u
JOIN (SELECT id FROM users ORDER BY id LIMIT 10000, 10) t
ON u.id = t.id;

-- 优化：使用覆盖索引
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 10000, 10) t
ON u.id = t.id;

-- 避免：相关子查询
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 0;

-- 优化：使用JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 避免：IN子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 优化：使用EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

索引设计原则
├── 选择性高的列
│   └── 基数/总行数 接近1
├── WHERE条件列
│   └── 经常用于过滤
├── JOIN连接列
│   └── 外键列
├── ORDER BY列
│   └── 排序列
├── 复合索引顺序
│   └── 高选择性在前
└── 避免过度索引
    └── 索引有成本

-- 查看索引使用情况
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
ORDER BY count_read DESC;

-- 查找未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema = 'mydb';

-- 删除未使用的索引
ALTER TABLE users DROP INDEX idx_unused;

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;

-- 更新统计信息
ANALYZE TABLE users;

-- 整数类型
-- 根据范围选择最小类型
TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT

-- 字符串类型
-- 定长用CHAR，变长用VARCHAR
-- 长文本用TEXT

-- 日期时间
-- DATE: 只存日期
-- DATETIME: 日期时间
-- TIMESTAMP: 时间戳

-- 避免过度设计
-- 错误：VARCHAR(1000)存储10字符
-- 正确：VARCHAR(50)

-- 范式设计（减少冗余）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 反范式设计（提高查询性能）
CREATE TABLE users_denormalized (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    department_name VARCHAR(50)  -- 冗余字段
);

-- 根据场景选择
-- OLTP：偏向范式
-- OLAP：偏向反范式

-- 按日期分区
CREATE TABLE logs (
    id INT,
    log_date DATE,
    message TEXT
) PARTITION BY RANGE (TO_DAYS(log_date)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询特定分区
SELECT * FROM logs PARTITION(p202401);

[mysqld]
# InnoDB缓冲池（物理内存的50-70%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数
innodb_buffer_pool_instances = 4

# 日志缓冲区
innodb_log_buffer_size = 16M

# 排序缓冲区
sort_buffer_size = 4M

# 连接缓冲区
join_buffer_size = 4M

# 读缓冲区
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# 表缓存
table_open_cache = 4000
table_definition_cache = 2000

# 线程缓存
thread_cache_size = 64

[mysqld]
# 日志文件大小
innodb_log_file_size = 512M

# 日志刷新策略
# 0: 每秒刷新
# 1: 每次提交刷新（默认，最安全）
# 2: 每次提交写入，每秒刷新
innodb_flush_log_at_trx_commit = 1

# 刷新方法
innodb_flush_method = O_DIRECT

# IO线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 锁等待超时
innodb_lock_wait_timeout = 50

# 死锁检测
innodb_deadlock_detect = ON

[mysqld]
# 最大连接数
max_connections = 500

# 最大错误连接数
max_connect_errors = 100

# 连接超时
connect_timeout = 10

# 等待超时
wait_timeout = 28800
interactive_timeout = 28800

[mysqld]
# 查询缓存（注意：10.5+已移除）
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置阈值（秒）
SET GLOBAL long_query_time = 2;

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 选项说明
# -s: 排序方式
#   t: 按查询时间
#   c: 按查询次数
#   l: 按锁定时间
#   r: 按返回记录数
# -t: 显示前N条

-- 查看慢查询统计
SELECT 
    digest_text,
    count_star,
    avg_timer_wait/1000000000 as avg_ms,
    sum_timer_wait/1000000000 as total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 查看全局状态
SHOW GLOBAL STATUS;

-- 查看关键指标
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Innodb%';
SHOW STATUS LIKE 'Qcache%';

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

-- 连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查询统计
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Queries';
SHOW STATUS LIKE 'Slow_queries';

-- InnoDB统计
SHOW STATUS LIKE 'Innodb_row_lock%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 计算命中率
SELECT 
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
    AS buffer_pool_hit_rate
FROM (
    SELECT variable_value AS Innodb_buffer_pool_reads 
    FROM information_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_reads'
) r,
(
    SELECT variable_value AS Innodb_buffer_pool_read_requests 
    FROM information_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_read_requests'
) rr;

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看锁信息
SELECT * FROM information_schema.innodb_locks;

-- 查看事务
SELECT * FROM information_schema.innodb_trx;

优化检查清单
├── 查询优化
│   ├── 使用EXPLAIN分析
│   ├── 避免全表扫描
│   └── 合理使用索引
├── 索引优化
│   ├── 选择性高的列
│   ├── 复合索引顺序
│   └── 定期维护
├── 结构优化
│   ├── 合适的数据类型
│   ├── 适当的范式
│   └── 分区设计
├── 配置优化
│   ├── 内存配置
│   ├── 连接配置
│   └── InnoDB配置
└── 监控告警
    ├── 慢查询监控
    ├── 连接数监控
    └── 资源使用监控