前言一提到MySQL 优化很多人第一反应是加索引或者改 my.cnf 参数。这两个都重要但它们排在工作流的后面。真正有效的顺序是先用执行计划和慢查询日志定位是哪条查询慢、慢在哪里再针对性地改 SQL、加索引、调结构最后才轮到服务器参数与架构层面。跳过定位直接动手结果通常是把索引加了一堆、写入反而变慢瓶颈却还在原地。本文从 PHP 开发者的视角梳理优化策略覆盖四个层次执行计划的读法、索引的设计与失效场景、查询模式的写法、以及 PHP 侧配合的方式。所有结论都讲机制不给提升百分之多少这类没有来源的数字要比较性能正确的做法是自己写测量代码跑一遍。另外要提醒一句本文示例统一使用PDO因为mysql_*系列函数在PHP 7.0 已被移除不是废弃老项目里的mysql_query拼字符串写法在今天的 PHP 上根本跑不起来必须换成mysqli或PDO的预处理语句。一、先定位读得懂执行计划优化的第一步不是改代码是知道哪条 SQL 慢。两个工具慢查询日志服务器端开关属于 MySQL 配置而非 PHP-- 打开慢查询日志阈值设为 1 秒SET GLOBAL slow_query_log ON;SET GLOBAL long_query_time 1;SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;-- 顺带记录没有走索引的查询日志会明显变大按需开启SET GLOBAL log_queries_not_using_indexes ON;-- 查看当前设置SHOW VARIABLES LIKE slow_query%;SHOW VARIABLES LIKE long_query_time;EXPLAIN。把可疑 SQL 前面加一个EXPLAINMySQL 不执行它只返回执行计划。关注四列列关注点type访问类型。从好到差大致是const、eq_ref、ref、range、index、ALL。出现ALL表示全表扫描key实际用到的索引。为NULL说明没用索引rows预估要检查的行数。这个数很大就意味着代价高Extra附加信息。Using filesort、Using temporary值得警惕Using index是好消息Extra里几个值的含义Using index查询需要的列全在索引里不需要回表覆盖索引。Using where拿到行之后还要再过滤。本身不是问题但配合大rows就说明过滤太晚。Using filesort需要额外排序。注意它不一定真的写文件但确实意味着不能直接利用索引顺序。Using temporary用了临时表常见于GROUP BY、DISTINCT与ORDER BY作用在不同列上。Using index condition启用了索引条件下推ICP把部分过滤下推到存储引擎层。MySQL 8.0.18 及以上还提供EXPLAIN ANALYZE它会真的执行语句并给出各步骤的实际耗时与行数用来验证预估是否准确。注意它会执行语句写操作不要拿它做实验。二、索引设计原则与失效场景最左前缀是复合索引的核心规则。给(a, b, c)建一个复合索引等价于同时拥有a、(a, b)、(a, b, c)三种前缀能力查询条件能否用上(a, b, c)索引WHERE a 1能用到a这一列WHERE a 1 AND b 2能用到a, bWHERE b 2不能缺少最左列WHERE a 1 AND c 3只能用ac因为跳过了b用不上WHERE a 1 ORDER BY b能借索引顺序免去排序WHERE a 1 ORDER BY b排序用不上索引因为a是范围条件覆盖索引指的是查询用到的列全部包含在索引中。比如(author_id, created_at)上有索引那么下面这条就只需要读索引SELECT author_id, created_at FROM articles WHERE author_id 10;代价是索引本身要占空间、写操作要维护它所以列不是越多越好。索引失效的常见情形记住原理索引是按列值有序排列的 B 树一旦对列做了运算就没法再用有序去定位在列上套函数WHERE DATE(created_at) 2025-01-01。改成范围条件WHERE created_at 2025-01-01 AND created_at 2025-01-02就能用上索引。以通配符开头的模糊匹配LIKE %关键词无法定位起点。LIKE 关键词%可以。隐式类型转换字符串列拿数字去比比如phone是VARCHAR写WHERE phone 13800000000MySQL 会把列转成数字来比较索引用不上。要么给值加引号要么让列类型与参数类型一致。对索引列做运算WHERE id 1 100。OR连接不同列、NOT IN、!这类否定条件通常难以有效利用索引。索引列的选择还有一个容易被忽视的点区分度低的列单独建索引意义不大比如只有两个取值的状态列因为扫描索引和扫描表差别不大。真正有价值的是过滤后能剩下很少行的列。主键的选择也属于索引设计。InnoDB 的表是按主键组织的聚簇索引二级索引里存的是主键值。用自增整数做主键时插入是顺序追加的用随机值例如 UUID 字符串做主键则会导致插入位置随机跳转、页分裂增多。如果业务必须用 UUID可以考虑用一个独立的自增列当主键。长字符串列建索引要注意长度限制InnoDB 在 DYNAMIC 行格式下单列索引键前缀上限是 3072 字节用 utf8mb4每字符最多 4 字节换算大约 768 个字符。所以给很长的VARCHAR建索引时要么限制列长度要么建前缀索引INDEX idx (col(20))。三、查询模式写法层面的优化不要无条件SELECT *。它有两个坏处一是传输不需要的列二是让覆盖索引失效。按需列出字段。深分页是最典型的看起来没问题其实很慢的写法。LIMIT 20 OFFSET 1000000需要先扫描并丢弃前一百万行OFFSET越大越慢。改用记住上一页最后一条的定位值的方式常称为 keyset 分页-- 慢OFFSET 越大要跳过并丢弃的行越多SELECT id, title FROM articles ORDER BY id DESC LIMIT 20 OFFSET 1000000;-- 快直接从索引定位不需要丢弃前置行SELECT id, title FROM articles WHERE id 1234567 ORDER BY id DESC LIMIT 20;代价是不能随意跳到第 N 页只适合下一页/加载更多这类场景。避免 N1 查询。在循环里对每个元素单独发一条查询是 PHP 里最常见的性能杀手?php// 适用于 PHP 7.0// ❌ 错误示范循环里逐条查询N1// foreach ($authorIds as $id) {// $stmt $pdo-prepare(SELECT COUNT(*) FROM articles WHERE author_id ?);// $stmt-execute([$id]);// $counts[$id] (int) $stmt-fetchColumn();// }// ✅ 正确一次 IN 查询拿回全部结果if ($authorIds ! []) {$placeholders implode(,, array_fill(0, count($authorIds), ?));$stmt $pdo-prepare(SELECT author_id, COUNT(*) AS cnt FROM articlesWHERE author_id IN ($placeholders) GROUP BY author_id);$stmt-execute(array_values($authorIds));$counts $stmt-fetchAll(PDO::FETCH_KEY_PAIR); // author_id 作为键}array_fill(0, 个数, ?)生成占位符串再把参数数组交给execute()仍然是参数化查询不会引入注入风险。要注意IN列表太长时几千个解析和优化的开销会上升此时应改为分批查询或走临时表。批量写入放进事务。每条语句都自动提交autocommit意味着每次都要刷一次日志到磁盘把它们包在一个事务里提交能显著减少这类等待?php// 适用于 PHP 7.0$pdo-beginTransaction();try {$stmt $pdo-prepare(INSERT INTO logs (user_id, action, created_at) VALUES (?, ?, ?));foreach ($rows as $row) {$stmt-execute([$row[user_id], $row[action], $row[created_at]]);}$pdo-commit();} catch (Throwable $e) {$pdo-rollBack();throw $e;}注意Throwable需要 PHP 7.0 及以上它同时覆盖Exception与Error比只捕获PDOException更稳。JOIN 时留意被驱动表。多表关联时被驱动内层表的关联列上应当有索引否则每扫一行外表就要全表扫一次内表。四、PHP 侧怎么配合只在需要时取数据。用LIMIT限制结果集用fetch()逐行处理大结果集避免fetchAll()把几十万行一次性读进内存那会直接把memory_limit撑爆。?php// 适用于 PHP 7.0$stmt $pdo-query(SELECT id, title FROM articles ORDER BY id LIMIT 10000);while (($row $stmt-fetch(PDO::FETCH_ASSOC)) ! false) {process($row); // 逐行处理内存占用平稳}连接管理。PHP-FPM 下每个 worker 进程在处理请求时建立连接请求结束就释放。这带来两个务实结论一是不要把连接当成跨请求的缓存二是要警惕持久连接。PDO 可以用PDO::ATTR_PERSISTENT true开启持久连接它会让 worker 进程复用连接、省下握手开销但代价是连接数会按worker 数而不是并发请求数增长容易撞上 MySQL 的max_connections而且上一个请求遗留的事务状态可能被下一个请求继承。没有明确测量需求时默认的非持久连接更安全。预处理语句可以复用。同一条 SQL 执行多次时只prepare一次、循环里多次execute比每次重新准备要省事得多——这同时也是防 SQL 注入的正确姿势?php// 适用于 PHP 7.0$stmt $pdo-prepare(SELECT id, title FROM articles WHERE author_id ? AND status ?);foreach ($pairs as $pair) {$stmt-execute([$pair[author_id], $pair[status]]);$rows $stmt-fetchAll(PDO::FETCH_ASSOC);// ... 处理 $rows}别在结果集没读完时就发新查询。同一个连接上还有未读取完的结果集时执行新语句会报 Commands out of sync。用 PDO 时用$stmt-closeCursor()显式结束用 mysqli 时要注意store_result()把结果全部取回客户端之后可以继续发查询与use_result()逐行取回期间不能发新查询的区别。用 EXPLAIN 自己检查。下面这段把计划打印出来比凭感觉改 SQL 靠谱?php// 适用于 PHP 7.0$pdo new PDO(mysql:host127.0.0.1;dbnameapp;charsetutf8mb4, $user, $pass, [PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC,PDO::ATTR_EMULATE_PREPARES false, // 使用服务端真预处理]);$authorId (int) $authorId; // 强制转成整数后再拼进诊断语句杜绝注入$sql EXPLAIN SELECT id, title FROM articlesWHERE author_id {$authorId} AND status 1ORDER BY created_at DESC LIMIT 20;foreach ($pdo-query($sql) as $row) {printf(type%s key%s rows%s extra%s\n,$row[type],$row[key] ?? (NULL),$row[rows],$row[Extra]);}如果type是ALL、key是(NULL)、rows很大说明这条查询大概率需要补索引如果Extra里出现Using filesort而排序又很频繁可以考虑把排序列纳入复合索引。常见坑点❌ 看到慢就加索引一条 SQL 配一个单列索引加到最后写入越来越慢✅ 索引要按实际查询模式设计优先考虑能同时服务多个查询的复合索引索引越多写入维护成本越高。先看 EXPLAIN 再决定加什么。❌ 在索引列上套函数WHERE DATE(created_at) 2025-01-01✅ 改成对范围友好的写法WHERE created_at 2025-01-01 AND created_at 2025-01-02让 B 树的有序性能被利用。❌ 用LIKE %关键词%做搜索然后抱怨加索引没用✅ 以通配符开头的匹配无法利用 B 树定位。要么改成前缀匹配LIKE 关键词%要么改用专门的全文索引方案。❌ 字符串列用数字比较WHERE phone 13800000000✅ 会发生隐式类型转换导致索引失效。写成WHERE phone 13800000000或确保参数类型与列类型一致。❌ 用LIMIT 20 OFFSET 1000000做深分页✅ 大偏移量要先扫描并丢弃前置行代价随偏移量增长。改用基于定位值的 keyset 分页WHERE id 上一页末值。❌ 用 SQL 字符串拼接把用户输入直接塞进查询比如把搜索词拼进WHERE✅ 任何时候都用预处理语句加占位符。mysql_*系列在 PHP 7.0 已被移除mysqli与PDO都支持预处理这是防注入的基本功。❌ 用fetchAll()处理几十万行的报表查询直接把memory_limit撑爆✅ 用fetch()逐行处理或用LIMIT分批取。内存占用平稳比一次性读回更快更安全。❌ 只盯着my.cnf参数调优SQL 里的 N1 和全表扫描一个没改✅ 服务器参数解决的是资源分配问题改不了单条查询的复杂度。先改 SQL 与索引参数调优放在最后。总结层次主要手段判断依据定位慢查询日志、EXPLAIN、EXPLAIN ANALYZEtype、key、rows、Extra索引复合索引最左前缀、覆盖索引、避免列上运算Extra里有没有Using index查询模式按需取列、keyset 分页、消除 N1、事务批量写单次请求发出的 SQL 条数与总行数表结构自增主键、合理列长度、注意索引键长度上限写入模式与索引前缀长度限制PHP 侧预处理复用、逐行取结果、控制连接数内存占用与连接池水位服务器慢日志阈值、缓冲池等参数在 SQL 层优化到位之后优化最省力的路径永远是先测量、再动手。用慢查询日志找出最耗时的那几条用EXPLAIN确认它到底扫了多少行、有没有用上索引改完之后再量一次——这个循环做上三五轮收益通常远大于凭直觉加索引。而无论怎么优化参数化查询都是不能让步的底线它既是防 SQL 注入的根本手段也不影响优化空间。
