MySQL性能优化 #

MySQL性能优化
├── 查询优化
│   ├── SQL语句优化
│   ├── 索引优化
│   └── 执行计划分析
├── 结构优化
│   ├── 表结构设计
│   ├── 数据类型选择
│   └── 范式与反范式
├── 配置优化
│   ├── 内存配置
│   ├── 缓冲区配置
│   └── 连接配置
└── 架构优化
    ├── 读写分离
    ├── 分库分表
    └── 缓存策略

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 查看完整执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

-- MySQL 8.0+ 分析语句
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

EXPLAIN SELECT * FROM users WHERE name = 'John';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

-- 从好到差：
-- system > const > eq_ref > ref > range > index > ALL

-- system：单行表
EXPLAIN SELECT * FROM (SELECT 1) t;

-- const：主键或唯一索引
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref：JOIN时使用主键或唯一索引
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- ref：非唯一索引
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- range：范围查询
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- index：索引扫描
EXPLAIN SELECT id FROM users;

-- ALL：全表扫描（最差）
EXPLAIN SELECT * FROM users WHERE age + 1 = 25;

-- Using index：覆盖索引
EXPLAIN SELECT id FROM users WHERE id = 1;

-- Using where：WHERE过滤
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- Using temporary：使用临时表
EXPLAIN SELECT DISTINCT name FROM users;

-- Using filesort：文件排序
EXPLAIN SELECT * FROM users ORDER BY name;

-- Using index condition：索引条件下推
EXPLAIN SELECT * FROM users WHERE name LIKE 'J%' AND age = 25;

-- 不推荐
SELECT * FROM users;

-- 推荐：只查询需要的列
SELECT id, name, email FROM users;

-- 确保WHERE条件使用索引
-- 创建索引
CREATE INDEX idx_name ON users(name);

-- 使用索引
SELECT * FROM users WHERE name = 'John';

-- 避免索引失效
-- 错误：使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 正确
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 错误：字符串与数字比较
SELECT * FROM users WHERE phone = 13800138000;

-- 正确
SELECT * FROM users WHERE phone = '13800138000';

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

-- 正确：不以%开头
SELECT * FROM users WHERE name LIKE 'John%';

-- 必须使用%开头时，考虑全文索引
CREATE FULLTEXT INDEX ft_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('John');

-- 错误：OR可能导致索引失效
SELECT * FROM users WHERE name = 'John' OR age = 25;

-- 正确：使用UNION
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE age = 25;

-- 传统分页（大数据量时慢）
SELECT * FROM users ORDER BY id LIMIT 10000, 10;

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

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

-- COUNT(*) 和 COUNT(1) 性能相同
SELECT COUNT(*) FROM users;

-- COUNT(列) 不统计NULL
SELECT COUNT(email) FROM users;

-- 大表计数优化：使用缓存或统计表
SELECT COUNT(*) FROM user_stats WHERE date = CURDATE();

-- 确保JOIN列有索引
CREATE INDEX idx_user_id ON orders(user_id);

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

-- 避免JOIN过多表（建议不超过5个）

-- 1. 为WHERE条件列创建索引
CREATE INDEX idx_status ON users(status);

-- 2. 为JOIN列创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 3. 为ORDER BY列创建索引
CREATE INDEX idx_created ON users(created_at);

-- 4. 组合索引遵循最左前缀
CREATE INDEX idx_status_age ON users(status, age);

-- 有效
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE status = 1 AND age = 25;

-- 无效
SELECT * FROM users WHERE age = 25;

-- 覆盖索引：查询的列都在索引中
CREATE INDEX idx_name_email ON users(name, email);

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

-- Extra显示Using index

-- 计算索引选择性
SELECT 
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM users;

-- 选择性越高，索引效果越好
-- 选择性接近1的列适合创建索引

-- 查看冗余索引
SELECT 
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = 'mydb'
GROUP BY table_name, index_name;

-- 冗余索引示例
-- INDEX idx_a (a)
-- INDEX idx_ab (a, b)
-- idx_a是冗余的，可以删除

[mysqld]
# InnoDB缓冲池（建议设为物理内存的70-80%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数
innodb_buffer_pool_instances = 4

# 日志缓冲区
innodb_log_buffer_size = 16M

# 排序缓冲区
sort_buffer_size = 2M

# 连接缓冲区
join_buffer_size = 2M

# 读缓冲区
read_buffer_size = 1M

# 随机读缓冲区
read_rnd_buffer_size = 1M

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

# 最大错误连接数
max_connect_errors = 100

# 连接超时
connect_timeout = 10

# 等待超时
wait_timeout = 28800

# 交互超时
interactive_timeout = 28800

[mysqld]
# MySQL 5.7及以下版本
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

[mysqld]
# 开启慢查询日志
slow_query_log = 1

# 慢查询日志文件
slow_query_log_file = /var/log/mysql/slow.log

# 慢查询阈值（秒）
long_query_time = 2

# 记录没有使用索引的查询
log_queries_not_using_indexes = 1

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

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

-- 使用pt-query-digest分析
-- pt-query-digest /var/log/mysql/slow.log

-- 查看状态变量
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Connections';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看缓冲池状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

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

-- 查看性能模式
USE performance_schema;

-- 查看等待事件
SELECT * FROM events_waits_summary_by_instance ORDER BY sum_timer_wait DESC LIMIT 10;

-- 查看语句统计
SELECT * FROM events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;

-- 查看索引使用情况
SELECT * FROM table_io_waits_summary_by_index_usage WHERE object_schema = 'mydb';