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

-- 临时启用
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 配置文件永久启用
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

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

# 参数说明：
# -s: 排序方式 (c=次数, t=时间, l=锁定时间)
# -t: 显示前N条
# -g: 匹配模式

# 示例
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 安装 Percona Toolkit
yum install percona-toolkit

# 分析慢查询
pt-query-digest /var/log/mysql/slow.log

# 分析特定时间段的查询
pt-query-digest --since='2024-01-01' --until='2024-01-31' slow.log

EXPLAIN SELECT * FROM users WHERE id = 1;
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' ORDER BY created_at;

system > const > eq_ref > ref > range > index > ALL

-- 示例分析
EXPLAIN SELECT * FROM users WHERE name = '张三' ORDER BY created_at;

-- 输出：
-- type: ref (使用索引)
-- key: idx_name (使用 name 索引)
-- Extra: Using index condition; Using where; Using filesort
-- (虽然使用索引，但 ORDER BY 需要额外排序)

-- 查看执行计划和实际耗时
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@example.com';

-- 1. 避免全表扫描
-- 坏示例
SELECT * FROM users WHERE age = 25;

-- 好示例（添加索引）
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE age = 25;

-- 2. 最左前缀原则
-- 组合索引 (name, age, status)
-- ✅ SELECT * FROM users WHERE name = '张三';
-- ✅ SELECT * FROM users WHERE name = '张三' AND age = 25;
-- ❌ SELECT * FROM users WHERE age = 25;

-- 3. 覆盖索引
-- ✅ 不需要回表
SELECT name, age FROM users WHERE name = '张三';

-- ❌ 需要回表
SELECT * FROM users WHERE name = '张三';

-- 4. 前缀索引（长字符串）
ALTER TABLE articles ADD INDEX idx_title (title(50));

-- 1. 函数/运算
❌ SELECT * FROM users WHERE YEAR(created_at) = 2024;
✅ SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 2. 类型转换
❌ SELECT * FROM users WHERE id = '1';
✅ SELECT * FROM users WHERE id = 1;

-- 3. LIKE 前缀通配符
❌ SELECT * FROM users WHERE name LIKE '%三%';
✅ SELECT * FROM users WHERE name LIKE '三%';

-- 4. OR 条件
❌ SELECT * FROM users WHERE name = '张三' OR age = 25;
✅ SELECT * FROM users WHERE name = '张三' UNION SELECT * FROM users WHERE age = 25;

-- ✅ 批量插入（推荐）
INSERT INTO users (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com');

-- ❌ 逐条插入
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO users (name, email) VALUES ('李四', 'lisi@example.com');

-- 禁用自动提交（大批量时）
SET autocommit = 0;
INSERT INTO users (name, email) VALUES ...; -- 10000条
COMMIT;
SET autocommit = 1;

-- ❌ 深度分页（性能差）
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- ✅ 优化1：使用主键
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

-- ✅ 优化2：记录上次位置
SELECT * FROM users WHERE id > :last_id ORDER BY id LIMIT 10;

-- ✅ 优化3：获取ID再关联
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 1000000, 10) t
ON u.id = t.id;

-- 1. 小表驱动大表
-- ✅ SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;
-- (t1 数据量小，放左边)

-- 2. 添加索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 3. 避免 SELECT *
SELECT o.id, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id;

-- 4. 避免子查询（在 MySQL 5.7 之前）
-- ❌ SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- ✅ SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- ❌ 全表 COUNT
SELECT COUNT(*) FROM users;  -- 慢

-- ✅ 使用主键 COUNT
SELECT COUNT(id) FROM users;

-- ✅ 统计缓存表
CREATE TABLE stats_users AS SELECT COUNT(*) AS cnt FROM users;

-- ✅ 多条件 COUNT
SELECT 
    SUM(status = 1) AS active_count,
    SUM(status = 0) AS inactive_count
FROM users;

[mysqld]
# 连接数
max_connections = 500
max_connect_errors = 1000

# 缓存大小（根据服务器内存调整）
innodb_buffer_pool_size = 4G  # 建议为系统内存的60-80%
key_buffer_size = 256M

# 查询缓存（MySQL 8.0 已移除）
# query_cache_size = 0

# 临时表和排序缓存
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# 日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# InnoDB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

# 使用 mysqltuner 分析配置
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --password yourpassword

✅ 启用并分析慢查询日志
✅ 使用 EXPLAIN 分析查询执行计划
✅ 遵循索引设计原则，避免索引失效
✅ 优化批量插入、深度分页、JOIN 操作
✅ 合理配置数据库参数