MySQL查询优化指南（LNMP环境）

基础优化策略

1. 索引优化

   • 为WHERE、JOIN、ORDER BY和GROUP BY子句中的列创建适当索引
   • 使用复合索引时遵循最左前缀原则
   • 避免过度索引，定期分析索引使用情况
   • 使用EXPLAIN分析查询执行计划

2. 查询语句优化

   • 避免使用SELECT *，只查询需要的列
   • 合理使用JOIN，避免笛卡尔积
   • 使用LIMIT分页，大数据量时考虑游标分页
   • 避免在WHERE子句中对字段进行函数操作

3. 表结构优化

   • 选择合适的数据类型（如使用INT而非VARCHAR存储数字）
   • 规范化与反规范化平衡考虑
   • 对大表考虑分区或分表

LNMP环境特定优化

1. MySQL配置优化

   # my.cnf 关键配置
   [mysqld]
   innodb_buffer_pool_size = 系统内存的50-70%
   innodb_log_file_size = 256M
   innodb_flush_log_at_trx_commit = 1 (数据安全) 或 2 (性能优先)
   innodb_flush_method = O_DIRECT
   query_cache_type = 0 (MySQL 8.0已移除查询缓存)
   max_connections = 根据应用需求设置
   

2. PHP连接优化

   • 使用持久连接（mysqlnd驱动）
   • 实现连接池（如Swoole）
   • 及时关闭数据库连接

3. Nginx缓存层

   • 对静态化内容启用Nginx缓存
   • 使用FastCGI缓存动态内容

高级优化技术

1. 查询缓存替代方案

   • 使用Redis/Memcached缓存查询结果
   • 实现应用层缓存

2. 读写分离

   • 主库写，从库读
   • 使用ProxySQL或MySQL Router实现负载均衡

3. 监控与分析工具

   • 使用Percona PMM或MySQL Enterprise Monitor
   • 慢查询日志分析

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
   

4. 定期维护

   ANALYZE TABLE table_name;
   OPTIMIZE TABLE table_name;
   

常见问题解决方案

1. 慢查询处理流程

   • 通过慢查询日志定位问题SQL
   • 使用EXPLAIN分析执行计划
   • 添加适当索引或重写查询
   • 验证优化效果

2. 连接数过多

   • 检查应用是否及时关闭连接
   • 调整wait_timeout和interactive_timeout
   • 增加连接池大小

3. 锁竞争严重

   • 使用InnoDB而非MyISAM
   • 优化事务大小和持续时间
   • 考虑使用乐观锁替代悲观锁

通过以上优化措施，可以显著提升LNMP环境中MySQL的查询性能。建议根据实际业务场景和负载情况逐步调整优化参数。