MySQL性能优化是开发者和运维人员都需要掌握的核心技能，合理的优化能够大幅提升数据库的响应速度，降低服务器资源消耗，保障业务系统的稳定运行。优化工作可以从多个层面展开，每个层面都有对应的可操作要点。
一、索引优化相关要点
1. 合理创建索引
索引是提升查询性能的核心手段，但是不能盲目创建。优先为经常出现在WHERE子句、JOIN连接条件、ORDER BY和GROUP BY子句中的字段创建索引。同时要避免在区分度低的字段上创建索引，比如性别字段只有两个值，创建索引的收益极低。
2. 避免索引失效
很多写法会导致索引无法生效，比如对索引字段使用函数、进行运算、使用LIKE以通配符开头、类型隐式转换等。以下是不合理的查询示例和对应的优化写法：
-- 错误写法：对索引字段使用函数，索引失效 SELECT * FROM user WHERE YEAR(create_time) = 2023; -- 优化写法：范围查询替代函数，索引生效 SELECT * FROM user WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
3. 控制索引数量
单表索引数量建议不超过5个，过多的索引会占用额外的存储空间，同时会降低插入、更新、删除操作的性能，因为每次数据变更都需要同步更新所有相关索引。
二、SQL语句优化要点
1. 避免SELECT * 查询
只查询需要的字段，减少数据传输量和数据库解析负担。如果只需要用户ID和姓名，就不要查询所有字段：
-- 不推荐 SELECT * FROM user WHERE id = 1; -- 推荐 SELECT id, name FROM user WHERE id = 1;
2. 优化JOIN查询
尽量使用小表驱动大表，减少循环次数。同时保证JOIN的关联字段有索引，避免笛卡尔积的产生。如果JOIN的表过多，建议拆分查询或者考虑冗余字段减少关联。
3. 处理慢查询
开启慢查询日志，定期分析慢查询语句，针对性优化。可以通过EXPLAIN命令查看SQL的执行计划，分析是否使用了索引、扫描行数等信息：
-- 查看SQL执行计划 EXPLAIN SELECT id, name FROM user WHERE age > 18;
三、表结构优化要点
1. 选择合适的数据类型
优先使用更小的数据类型，比如能用TINYINT就不用INT，能用VARCHAR就不用TEXT。字段长度根据实际需求设置，不要过度预留长度。以下是常用类型的选择建议：
| 场景 | 推荐类型 | 说明 |
|---|---|---|
| 用户状态（0/1/2） | TINYINT | 占用1字节，足够存储小范围整数 |
| 用户昵称（长度不固定） | VARCHAR(50) | 按需设置长度，避免过长 |
| 文章正文 | TEXT | 存储长文本，避免用VARCHAR存超长内容 |
2. 适当进行分表
当单表数据量超过千万级时，查询性能会明显下降，可以考虑水平分表或者垂直分表。水平分表按照时间、用户ID哈希等维度拆分数据，垂直分表把不常用的字段拆分到扩展表，减少单表字段数量。
3. 避免使用外键
外键会影响写入性能，同时增加表之间的耦合度，建议在应用层实现外键的逻辑约束，而不是依赖数据库的外键功能。
四、配置与运维优化要点
1. 调整InnoDB缓冲池大小
InnoDB缓冲池用于缓存表数据和索引，建议设置为服务器可用内存的60%-80%，减少磁盘IO次数。可以在MySQL配置文件my.cnf中调整：
[mysqld] # 设置缓冲池大小为4G，根据实际内存调整 innodb_buffer_pool_size = 4G
2. 优化连接配置
合理设置最大连接数，避免连接数过多导致数据库崩溃。同时设置连接超时时间，释放长时间空闲的连接：
[mysqld] # 最大连接数设置为500 max_connections = 500 # 非交互连接超时时间300秒 wait_timeout = 300
3. 定期维护表
对于频繁更新的表，定期执行OPTIMIZE TABLE命令整理碎片，回收闲置的存储空间，提升查询性能。但是该操作会锁表，建议在业务低峰期执行。
4. 读写分离与分库
当单库性能达到瓶颈时，可以搭建主从复制架构实现读写分离，写操作走主库，读操作走从库，分摊数据库压力。如果业务规模更大，可以考虑分库，按照业务模块拆分不同的数据库实例。
五、其他实用要点
- 批量操作代替单条操作，比如批量插入数据，减少和数据库的交互次数
- 避免在循环中执行SQL语句，尽量把操作合并到一次数据库请求中
- 合理使用查询缓存，但是注意查询缓存会在表数据变更时失效，频繁更新的表不建议开启
- 监控数据库的性能指标，比如QPS、TPS、连接数、慢查询数量等，及时发现性能问题