企业官网建设流程全解析

前言

一提到"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 前面加一个EXPLAIN，MySQL 不执行它，只返回执行计划。关注四列：

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)三种前缀能力：

覆盖索引指的是查询用到的列全部包含在索引中。比如(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 = 13800000000，MySQL 会把列转成数字来比较，索引用不上。要么给值加引号，要么让列类型与参数类型一致。
• 
  
• 对索引列做运算：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 页，只适合"下一页/加载更多"这类场景。

避免 N+1 查询。在循环里对每个元素单独发一条查询是 PHP 里最常见的性能杀手：

<?php
// 适用于 PHP 7.0+
// ❌ 错误示范：循环里逐条查询（N+1）
// 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 articles
WHERE 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:host=127.0.0.1;dbname=app;charset=utf8mb4', $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 articles
WHERE author_id = {$authorId} AND status = 1
ORDER 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而排序又很频繁，可以考虑把排序列纳入复合索引。

常见坑点

1. 
   
2. ❌ 看到慢就加索引，一条 SQL 配一个单列索引，加到最后写入越来越慢
3. 
   

✅ 索引要按实际查询模式设计，优先考虑能同时服务多个查询的复合索引；索引越多，写入维护成本越高。先看 EXPLAIN 再决定加什么。

1. 
   
2. ❌ 在索引列上套函数：WHERE DATE(created_at) = '2025-01-01'
3. 
   

✅ 改成对范围友好的写法：WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'，让 B+ 树的有序性能被利用。

1. 
   
2. ❌ 用LIKE '%关键词%'做搜索，然后抱怨加索引没用
3. 
   

✅ 以通配符开头的匹配无法利用 B+ 树定位。要么改成前缀匹配LIKE '关键词%'，要么改用专门的全文索引方案。

1. 
   
2. ❌ 字符串列用数字比较：WHERE phone = 13800000000
3. 
   

✅ 会发生隐式类型转换导致索引失效。写成WHERE phone = '13800000000'，或确保参数类型与列类型一致。

1. 
   
2. ❌ 用LIMIT 20 OFFSET 1000000做深分页
3. 
   

✅ 大偏移量要先扫描并丢弃前置行，代价随偏移量增长。改用基于定位值的 keyset 分页（WHERE id < 上一页末值）。

1. 
   
2. ❌ 用 SQL 字符串拼接把用户输入直接塞进查询，比如把搜索词拼进WHERE
3. 
   

✅ 任何时候都用预处理语句加占位符。mysql_*系列在 PHP 7.0 已被移除，mysqli与PDO都支持预处理，这是防注入的基本功。

1. 
   
2. ❌ 用fetchAll()处理几十万行的报表查询，直接把memory_limit撑爆
3. 
   

✅ 用fetch()逐行处理，或用LIMIT分批取。内存占用平稳比一次性读回更快更安全。

1. 
   
2. ❌ 只盯着my.cnf参数调优，SQL 里的 N+1 和全表扫描一个没改
3. 
   

✅ 服务器参数解决的是资源分配问题，改不了单条查询的复杂度。先改 SQL 与索引，参数调优放在最后。

总结

优化最省力的路径永远是"先测量、再动手"。用慢查询日志找出最耗时的那几条，用EXPLAIN确认它到底扫了多少行、有没有用上索引，改完之后再量一次——这个循环做上三五轮，收益通常远大于凭直觉加索引。而无论怎么优化，参数化查询都是不能让步的底线：它既是防 SQL 注入的根本手段，也不影响优化空间。