# InnoDB 缓冲池大小（专用服务器建议设置物理内存的 70%~80%）
innodb_buffer_pool_size = 4G

# 连接数
max_connections = 500
max_user_connections = 50

# 日志刷盘策略（非金融级可设为 2 提升写入性能）
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1

# 独立表空间
innodb_file_per_table = 1

# 慢查询日志
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /var/lib/mysql/mysql-slow.log

# 默认存储引擎
default-storage-engine = InnoDB

-- 主库安装
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 从库安装
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

gtid_mode = ON
enforce_gtid_consistency = ON

 垂直分库

 垂直分表

 逆规范化：适当增加冗余字段，减少 JOIN 查询，提高读性能。适合读多写少的场景。

四、SQL 优化实战

4.1 打开慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- 0.5 秒

使用 mysqldumpslow 分析：

mysqldumpslow -s t -t 20 /var/lib/mysql/mysql-slow.log

4.2 使用 profiling 查询单条 SQL 耗时

SET profiling = 1;
SELECT * FROM user WHERE id = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

4.3 EXPLAIN 执行计划分析

EXPLAIN 是 SQL 优化的核心工具。示例：

EXPLAIN SELECT t.tname, c.cname, tc.phone
FROM teacher t, course c, teacher_contact tc
WHERE t.tid = c.tid AND t.tcid = tc.tcid AND (c.cid = 2 OR tc.tcid = 3);

关键列解读：

type 优化目标：至少达到 range，争取 ref 或 const。

小技巧：key_len 计算示例（utf8mb4 每个字符 4 字节）
若 phone varchar(11) 且 utf8mb4，索引长度 ≈ 11×4 + 2 = 46 字节。

五、索引与表结构优化

5.1 索引使用原则

• 在 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列上建立索引

• 区分度高的列优先

• 联合索引遵循最左前缀原则

• 避免索引失效：LIKE '%xxx'、函数操作、隐式类型转换、OR 条件等

5.2 表结构优化实例

垂直拆分（适用于表过宽或含大字段）：

- 原表
CREATE TABLE article (id INT, title VARCHAR(200), content TEXT, ...);
-- 拆分后
CREATE article_base (id INT, title VARCHAR(200));
CREATE article_content (id INT, content TEXT);

　水平拆分（按时间或 ID 范围）：

-- 按年份分区，或使用应用层路由到 order_2023、order_2024 等

六、锁机制与死锁分析

6.1 InnoDB 锁类型

• 行锁：锁定单行记录（Record Lock）

• 间隙锁：锁定范围（Gap Lock），防止幻读

• Next-Key Lock = 行锁 + 间隙锁，RR 隔离级别下默认使用

6.2 死锁的四个必要条件

1. 两个或以上事务

2. 锁资源不兼容（排他锁互斥）

3. 每个事务持有锁并申请新锁

4. 循环等待

示例死锁演示：

6.3 查看与处理死锁

-- 查看当前锁等待的信息
SHOW STATUS LIKE 'innodb_row_lock%';

-- 查看正在运行的事务
SELECT * FROM information_schema.innodb_trx;

-- 查看锁详细信息（MySQL 8.0）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

发现阻塞线程后，可使用 KILL <thread_id> 终止。

6.4 避免死锁的建议

• 多表操作按固定顺序访问

• 将大事务拆分为多个小事务

• 降低隔离级别（如 RR → RC，可减少间隙锁）

• 为表添加合理索引，避免表锁升级

七、常用监控命令速查

-- 查看当前连接
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;

-- 查看各 SQL 执行次数
SHOW GLOBAL STATUS LIKE 'Com_%';

-- 查看服务端变量
SHOW VARIABLES LIKE '%connect%';
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看表锁情况
SHOW GLOBAL STATUS LIKE 'table_locks%';

-- 查看打开文件数
SHOW GLOBAL STATUS LIKE 'open_files';

八、总结：调优路线图

最后，记住一条真理：没有任何一次调优是“无脑加配置”能解决的，必须结合业务特点与监控数据，循序渐进。