新手必看：MySQL优化性能优化实战 | 11分钟学会

在MySQL性能优化中，我见过太多人搞砸，但真正能拿结果说话的方案不多。别以为调个参数、加个索引就能搞定，你得知道哪些配置是真有用，哪些是摆设。直接上干货：在8核16G服务器上，MySQL的innodb_buffer_pool_size设置到总内存的70%左右是最稳的，别贪心，别偷懒。 用EXPLAIN查慢查询的时候，别只看type字段，要看rows和extra字段，尤其extra里出现Using temporary或Using filesort，说明你得重新设计表结构或索引。还有那个skip-name-resolve配置，别以为是鸡肋，其实是提升连接效率的神技。这些细节，我踩过坑，也验证过，现在直接给你说。

在实际操作中，MySQL的查询缓存是鸡肋，很多人开启后性能反而更差。一次我用show engine innodb status看慢查询日志，发现一个诡异的现象：同一个查询在不同时间点执行时间差距巨大。后来发现是表锁的问题，直接修改了innodb_flush_log_at_trx_commit参数，从2改成1，性能提升明显。另外，别随便用ALTER TABLE，它会锁表，影响线上业务。要是必须改，选在低峰期，用在线DDL工具，比如pt-online-schema-change，绝对不能硬刚。

还有个误区，很多人以为索引越多越好，其实不是。一个表有十几个索引，反而让写入变慢。我之前处理过一个订单系统，订单表加了12个索引，结果写入性能下降30%。后来把不常用的索引删掉，性能反而好了。索引要用对，比如在where条件中出现的字段，或者join的关联字段，尽可能用覆盖索引，让查询直接从索引拿数据。索引的顺序也重要，左前到右后，尽量让选择性高的字段在前。

MySQL的慢查询日志配置，不能随便写个阈值。我见过有人设置slow_query_log_threshold=1000，结果日志里全是几毫秒的查询，根本看不出问题。得根据实际业务来定，比如电商系统，订单查询可能有几百ms的延迟，所以日志阈值设为2000。另外，慢查询日志要开log_queries_not_using_indexes，这样能发现那些没用索引的查询。还有，你得用slow query log分析工具，比如pt-query-digest，它能帮你统计慢查询的分布情况，找出最大的性能瓶颈。

查询语句优化时，别忽略subquery的使用。我之前处理过一个统计报表查询，里面有三层subquery，执行时间长达十几秒。后来把它改成join，结果直接从3秒缩短到0.5秒。使用临时表也会带来性能问题，尤其是频繁创建和删除的临时表。如果有多个复杂子查询，可以考虑物化视图或者缓存结果。还有，避免在where条件中使用函数，比如用YEAR(date)来过滤数据，这会让索引失效。写查询时，要让数据库能直接用索引，而不是你做处理。

连接池配置是很多人忽略的点。我之前用的是mysql-connector-java，但连接池参数没调好，导致频繁创建连接，CPU飙升。后来改用HikariCP，设置maximumPoolSize=100，keepalive时间30秒，结果连接数下降了40%，响应时间也降下来。另外，连接池的空闲连接回收策略也很重要，不能让连接长时间不活动，否则会浪费资源。还要注意jvm的内存分配，别让MySQL连接池和jvm的堆内存冲突，这会引发OOM。

在索引优化中，索引合并是常见的坑。我遇到过一个查询，把两个索引合并后，反而用了临时表，导致性能变差。索引合并的条件是两个索引字段的类型和数据分布要匹配，否则会适得其反。还有，别迷信联合索引的最左前缀原则，实际中，比如你有(a, b)索引，查询条件是(b=1 and a=2)，这时候索引不一定有用。得看数据分布和查询频率，有时候单独索引反而更高效。联合索引的字段顺序也得合理，比如where条件中出现概率高的字段放前面。

表结构设计是优化的起点。我之前设计表的时候，把所有字段都加了索引，结果查询反而更慢。后来发现，很多字段根本不用查询，索引反而增加了写入开销。要根据业务场景，只在必要字段加索引。比如，订单表的status字段加了索引，但是实际查询中很少用，后来去掉后，写入性能提升了15%。还有，别用太长的字段类型，比如varchar(255)浪费存储和索引空间，改成char(255)更高效。表的主键也得选对，一般选自增id，别用uuid，这样索引更紧凑。

缓存策略是优化不可忽视的环节。我见过太多人没配置query cache，结果每次查询都要走磁盘。开启query cache后，命中率能提升到80%以上，但别盲目开，要根据查询频率判断。比如，读多写少的系统，query cache才有用。如果查询经常变化，反而会增加锁表开销。除了query cache，还有innodb_buffer_pool，这个配置直接影响性能。设置为70%左右的物理内存，能保证大部分数据在内存中，减少磁盘IO。还有，别忽略innodb_io_capacity，这个参数能控制写入的并发能力，设置成10000以上可以提升写性能。

另外，别把所有数据都堆在一个表里。分表分库是很多人被迫采用的方案。我之前处理过一个百万级的用户表，每次查询都要全表扫描，后来拆成按年分表，查询速度提升明显。但分表也有问题，比如分页查询会变得麻烦，得改用延迟加载或者单独维护汇总表。还有，别用全表扫描做统计，尽量用索引扫描或者直接查count。如果表数据量太大，考虑建立汇总表来存储统计结果，这样查询时不用每次都计算。

内存和CPU的配置也会影响MySQL性能。我在一次高并发测试中，发现CPU使用率接近100%，这时候得检查是不是有大量排序操作。innodb_log_file_size设置过小会导致频繁刷盘，增大到2G以上会减少刷盘频率。还有，别让MySQL和应用服务器共享内存，这样容易造成内存争抢。建议单独分配一台服务器，只装MySQL，这样能避免其他服务占用内存。此外，配置文件中的max_connections不能随意调高，否则会占满系统资源，引发连接池饥饿。

对于写入性能，别用默认的innodb_flush_log_at_trx_commit=1，这样会带来延迟。如果业务对数据一致性要求不高，可以改成2，让事务提交时异步刷盘。但这种设置有风险，容易丢数据。如果一定要用2，得确保有可靠的备份机制。另外，innodb_log_buffer_size设置为16M到32M之间，太大反而会增加磁盘写入压力。还有，别把innodb_additional_mem_pool_size设成默认值，一般设置成100M左右就够了。

查询优化中，别用select ，只选需要的字段。有一次我优化一个报表查询，把select 改成只查几个关键字段，执行时间从5秒降到0.8秒。这不只是减少数据传输，更是减少MySQL的处理负担。还有，别用OR连接条件，尽可能用IN或者多个AND条件。OR的查询优化比较麻烦，容易导致索引失效。如果必须用OR，可以考虑使用union或者临时表来替代。索引失效其实很常见，比如where条件中用了函数、like开头模糊查询、或者类型不一致，这时候索引就完全没用。

连接池的超时时间也要合理。我之前设置连接池最多等5秒才能获取连接，结果在高峰期出现大量等待，CPU飙升。后来改成3秒，问题缓解。还有，别让连接池一直保持满，要定期关闭闲置连接，这样能降低内存占用。另外，连接池的最小空闲连接数也要根据业务调整，如果系统有波动，可以适当调大，避免频繁创建连接。

MySQL的版本也会影响性能。比如，5.7和8.0在索引优化、查询缓存、锁机制等方面有明显差异。我在升级到8.0后，发现很多旧版本的查询可以在新版本中自动优化。别死守老版本，能升级就升级，尤其是8.0的CTE（公共表表达式）和窗口函数，对复杂查询很有帮助。但升级前得做充分测试，避免兼容性问题。

在实际生产中，我见过很多人没有日志分析的习惯。直接使用MySQL的日志文件，比如slow query log，能发现很多隐藏的性能问题。用pt-query-digest分析日志，能找出最耗资源的查询。还有，别忽略binlog的配置，如果写入性能差，可以调整binlog_format为ROW，减少日志解析的负担。总之，别光看表面，要深入日志和配置，才能找到真正的性能瓶颈。