PHP项目如何优化SQL查询速度？

本文目录导读：

1. 核心：分析并优化慢查询（最重要的第一步）
2. 索引优化（见效最快）
3. SQL语句本身优化（减少数据量）
4. PHP代码与数据库交互优化
5. 架构和缓存层（更上层的优化）
6. 终极手段：分库分表与读写分离
7. 一个优化流程案例

在PHP项目中优化SQL查询速度,通常需要从SQL语句本身、数据库结构、PHP代码与数据库交互方式以及服务器/缓存四个层面入手。

以下是针对PHP项目的系统性优化方案,按优先级排列：

核心：分析并优化慢查询（最重要的第一步）

不要凭感觉优化,要先找到真正慢的SQL。

1. 开启慢查询日志：

   • 在MySQL配置文件中设置：

     slow_query_log = 1
     slow_query_log_file = /var/log/mysql/mysql-slow.log
     long_query_time = 2  # 超过2秒的查询

   • 或使用即时命令（需重启后失效）：SET GLOBAL slow_query_log = 'ON';

2. 使用EXPLAIN分析SQL：

   • 在SQL前加上EXPLAIN，查看执行计划。
   • 重点关注： type（性能从好到差：const > eq_ref > ref > range > index > ALL，ALL是全表扫描，必须避免）、rows（扫描的行数）、Extra（出现Using filesort或Using temporary说明需要优化）。
   • 例子： EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';

索引优化（见效最快）

大多数慢查询的根源是缺少合适的索引或索引未被使用。

1. 为高频查询字段加索引：

   • WHERE、JOIN、ORDER BY、GROUP BY中出现的字段，优先考虑建立索引。
   • 联合索引（复合索引）： 按查询条件顺序建立，遵循最左前缀原则，经常 WHERE status = 1 AND created_at > '2023-01-01'，可以建立 INDEX(status, created_at)。
   • 覆盖索引： 让索引包含查询所需的所有列（SELECT 中的列都在索引里），避免“回表”查询磁盘数据，这是极致的性能提升。

2. 避免让索引失效的典型写法：

   • 不要在索引列上使用函数： WHERE DATE(created_at) = '2023-01-01' → 改为 WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'。
   • 隐式类型转换： WHERE user_id = '123'（如果user_id是整型）→ 改为 WHERE user_id = 123。
   • LIKE 以通配符开头： LIKE '%keyword' 无法使用索引，考虑使用全文索引（FULLTEXT）或搜索引擎（Elasticsearch）。
   • OR 连接条件： OR 两边的字段没有分别建索引，会导致全表扫描，可改为 UNION ALL。

SQL语句本身优化（减少数据量）

1. 只查询需要的字段：

   • *不要写 `SELECT **，只取SELECT id, name, email` 这样需要的列，减少IO和内存占用。

2. 限制结果集：

   • 分页查询必须带 LIMIT，并且对于大偏移量（LIMIT 100000, 20），要优化为游标分页或子查询延迟关联。

     -- 低效
     SELECT * FROM articles ORDER BY id LIMIT 100000, 20;
     -- 高效（利用覆盖索引）
     SELECT * FROM articles WHERE id > 100000 LIMIT 20;

3. 优化JOIN和子查询：

   • 小表驱动大表：JOIN 时，让数据量小的表作为驱动表（MySQL优化器通常会自动选择，但可以强制指定 STRAIGHT_JOIN）。
   • 尽量使用JOIN代替子查询（MySQL 8.0之前），现代MySQL已经优化了，但JOIN通常仍更可控。
   • GROUP BY + ORDER BY：确保使用了索引，避免Using filesort和Using temporary。

PHP代码与数据库交互优化

PHP本身不是瓶颈,但代码写法会影响数据库连接和查询次数。

1. 使用数据库连接池（长连接）：

   • 避免每次请求都创建/销毁连接，使用 mysqli 的 p:host 持久连接，或使用 Swoole/Workerman 的连接池（生产环境强烈建议）。

2. 减少查询次数（N+1问题）：

   • 在循环中逐条查询（N+1）是性能杀手。
   • 错误例子： foreach ($users as $user) { $posts = $db->query("SELECT * FROM posts WHERE user_id = {$user['id']}"); }
   • 正确做法： 先用 WHERE user_id IN (...) 批量查询，再在PHP中组装数据。

3. 批量操作：

   • 插入或更新多条数据时,使用一条SQL语句包含多条记录，而不是循环执行。

     -- 改为一条语句
     INSERT INTO table (col1, col2) VALUES (1, 'a'), (2, 'b'), (3, 'c');

4. 使用预处理语句（Prepared Statements）：

   不仅是防SQL注入,还能让MySQL解析一次SQL，多次执行，使用PDO或mysqli的prepare/execute。

5. 优化ORM：

   • 如果使用Laravel Eloquent、ThinkPHP等ORM，务必开启懒加载并在需要时使用预加载（Eager Loading），避免N+1。
     • Laravel例子：User::with('posts')->get() 会只发2条SQL，而 User::all() 再循环 $user->posts 会发N+1条。

架构和缓存层（更上层的优化）

如果SQL已经优化到极致,考虑用缓存扛住高并发。

1. 查询结果缓存：

   • Redis/Memcached：将高频查询、耗时查询的结果存入缓存（如热门文章列表、用户信息），PHP读取缓存比查询MySQL快几个数量级。
   • MySQL Query Cache（MySQL 8.0已废弃）：不推荐，容易失效。

2. MySQL内部配置调优：

   • innodb_buffer_pool_size：设置为服务器物理内存的70%-80%，这是InnoDB最重要的配置，决定了数据和索引能在内存中缓存多少。
   • query_cache_size：8.0之前可以适当开启，但建议为0（让操作系统缓存文件）。
   • tmp_table_size / max_heap_table_size：增大内存临时表大小，避免排序/分组时使用磁盘临时表。

终极手段：分库分表与读写分离

当单表数据量达到千万/亿级别，上述方法失效。

1. 读写分离： 主库写，从库读（PHP连接多个数据库实例）。
2. 分表： 垂直分表（将大字段分离出去）、水平分表（按ID取模分表）。
3. 分库： 按业务（用户、订单）分独立数据库。

一个优化流程案例

假设你的PHP项目中有一个文章列表页很慢。

1. 发现问题： 打开慢查询日志，找到 SELECT * FROM articles ORDER BY created_at DESC LIMIT 100000, 20;
2. 分析： 使用 EXPLAIN，发现 type: ALL（全表扫描），rows: 500000。
3. 优化SQL： 改为 SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 20;（假设ID连续）或使用子查询延迟关联。
4. 加索引： 给 created_at 和 id 加索引（如果上面改了，用覆盖索引）。
5. PHP代码： 检查是否在循环中查附件表，改为IN查询+预加载。
6. 加缓存： 列表页结果用Redis缓存1分钟，减轻数据库压力。
7. 结果： 加载时间从5秒降到20毫秒。

记住一条铁律： 先定位慢查询，针对性加索引，最后才考虑改代码或加缓存。不要盲目优化。