417_MySQL性能调优¶

-- ========== MySQL 8.0 性能调优实战 ==========

-- ========== 1. 开启和分析慢查询日志 ==========
-- 开启慢查询日志（运行时设置，重启失效；永久配置在my.cnf）
SET GLOBAL slow_query_log = 'ON';          -- 开启慢查询记录
SET GLOBAL long_query_time = 1;            -- 超过1秒的查询记录为慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  -- 日志路径

-- 查询当前慢查询设置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 使用 mysqldumpslow 命令行工具分析慢查询日志（终端中运行，非SQL）
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t: 按执行时间排序
-- -t 10: 只显示前10个

-- ========== 2. EXPLAIN分析执行计划 ==========
-- 关键列说明：
-- type: ALL(全表)< index(全索引)< range(范围)< ref(非唯一索引)< eq_ref < const(最优)
-- Extra: Using filesort（需要排序操作，耗性能）/ Using index（覆盖索引，最好）

EXPLAIN SELECT gene_id, expression
FROM gene_expression
WHERE condition = 'treatment'    -- 有索引则type=ref，否则ALL
  AND expression > 100
ORDER BY expression DESC;        -- 如果走了filesort，考虑加ORDER BY列的索引

-- EXPLAIN FORMAT=JSON 可以看到更详细的信息（包括cost估算）
EXPLAIN FORMAT=JSON
SELECT * FROM gene_expression WHERE sample_id = 'S001';

-- ========== 3. 索引策略 ==========
-- 创建复合索引（注意顺序！等值查询列放前面，范围查询列放后面）
CREATE INDEX idx_condition_expr ON gene_expression (condition, expression);
-- 适用于: WHERE condition='x' AND expression>y  ← 正确
-- 不适用: WHERE expression>y                    ← 无法用此索引（最左前缀原则）

-- 查看已有索引
SHOW INDEX FROM gene_expression;

-- 查找重复/冗余索引（MySQL 8.0+）
SELECT * FROM sys.schema_redundant_indexes;  -- 需要sys schema

-- 查找未使用的索引（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;

-- 删除不需要的索引
DROP INDEX idx_old_unused ON gene_expression;

-- ========== 4. 查询优化技巧 ==========
-- ❌ 避免：在WHERE中对列使用函数
SELECT * FROM gene_expression WHERE YEAR(created_at) = 2024;
-- ✅ 改为范围查询（可以走索引）
SELECT * FROM gene_expression 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ 避免：SELECT *（传输不必要的列）
SELECT * FROM samples WHERE patient_id = 'P001';
-- ✅ 只选需要的列
SELECT sample_id, tissue_type, collection_date FROM samples WHERE patient_id = 'P001';

-- ❌ 避免：大offset分页（需要跳过大量行）
SELECT * FROM gene_expression ORDER BY gene_id LIMIT 100 OFFSET 100000;
-- ✅ 改用游标分页（基于上一页最后一个ID）
SELECT * FROM gene_expression 
WHERE gene_id > 'LAST_GENE_ID_FROM_PREV_PAGE'  -- 上一页最后的gene_id
ORDER BY gene_id 
LIMIT 100;

-- ========== 5. InnoDB缓冲池调优 ==========
-- 查看缓冲池命中率（越高越好，应该>99%）
SELECT 
    (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100 
    AS buffer_pool_hit_rate_pct
FROM (
    SELECT 
        variable_value AS innodb_buffer_pool_reads
    FROM performance_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_reads'
) reads_table,
(
    SELECT 
        variable_value AS innodb_buffer_pool_read_requests
    FROM performance_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_read_requests'
) requests_table;
-- 如果命中率 < 99%，需要增大 innodb_buffer_pool_size

-- ========== 6. 性能schema查询热点 ==========
-- 找占用IO最多的表（需要开启performance_schema）
SELECT 
    object_schema,
    object_name,
    count_read,
    count_write,
    SUM_TIMER_READ / 1000000000 AS read_time_sec  -- 皮秒转秒
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_READ DESC
LIMIT 10;

-- 找最常执行的慢查询（MySQL 8.0+）
SELECT 
    LEFT(query, 100) AS query_sample,   -- 只显示前100个字符
    exec_count,
    avg_latency,
    rows_examined_avg,
    full_scan                            -- 是否全表扫描（YES要警惕！）
FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;

-- ========== 7. my.cnf关键配置（/etc/mysql/my.cnf） ==========
config_example = """
[mysqld]
# 缓冲池大小（服务器内存的70-80%）
innodb_buffer_pool_size = 8G         # 假设服务器有16G内存

# 写入性能（SSD硬盘可以用O_DIRECT）
innodb_flush_log_at_trx_commit = 2   # 1=最安全，2=性能好（1秒内最多丢1秒数据）
innodb_io_capacity = 2000            # SSD设置2000，普通HDD设置200

# 慢查询
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                  # 1秒以上记录

# 连接
max_connections = 500                # 根据业务调整
wait_timeout = 600                   # 10分钟空闲连接断开
"""

-- 常用诊断命令
SHOW PROCESSLIST;                    -- 当前连接和执行的SQL
SHOW STATUS LIKE 'Innodb_buffer%';  -- 缓冲池状态
SHOW VARIABLES LIKE 'max_connections';  -- 配置参数
EXPLAIN SELECT ...;                  -- 执行计划

-- 索引操作
SHOW INDEX FROM table_name;
CREATE INDEX idx ON tbl (col1, col2);
DROP INDEX idx ON tbl;
ALTER TABLE tbl ADD INDEX idx (col);

-- 锁监控（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks;     -- 当前锁
SELECT * FROM performance_schema.data_lock_waits; -- 锁等待