循环中MySQLi查询过慢：PHP5.2秒级执行PHP7耗时超500秒求助

嘿，我之前也碰到过类似的跨PHP版本数据库性能跳水问题，结合你的代码片段，咱们一步步拆解问题和解决方案：

首先，定位核心性能瓶颈 #

你的代码在循环内执行复杂嵌套查询，这在数据量稍大时就是性能灾难——PHP5.2时代的旧驱动可能有一些隐性的缓存或者优化，但PHP7的MySQLi驱动对查询的执行更严格，多次重复查询的开销会被放大。另外，嵌套子查询本身也可能触发全表扫描，尤其是当表没有合适索引的时候。

具体优化步骤 #

1. 先查执行计划，找到慢查询根源 #

先把你的查询替换成实际参数，在MySQL里执行EXPLAIN，看看哪一步拖慢了速度：

EXPLAIN
SELECT count(*) AS count FROM (
    SELECT COUNT(DISTINCT TASKS.task_ID) AS count 
    FROM TASKS 
    JOIN TRANSLATOR_LANGUAGES 
    JOIN PROJECT_USERS 
    JOIN TRANSLATED_TASKS 
    JOIN PROJECTS 
    ON (
        TASKS.task_ID = TRANSLATED_TASKS.task_ID 
        AND TASKS.project_ID = PROJECT_USERS.project_ID 
        AND PROJECTS.project_ID = TASKS.project_ID 
        AND TRANSLATED_TASKS.language_code = TRANSLATOR_LANGUAGES.language_code 
        AND PROJECT_USERS.translator_ID = TRANSLATOR_LANGUAGES.translator_ID 
        AND PROJECT_USERS.language_code = TRANSLATOR_LANGUAGES.language_code
    ) 
    WHERE 'your_lang_code' = TRANSLATOR_LANGUAGES.language_code 
      AND 'your_translator_id' = TRANSLATOR_LANGUAGES.translator_ID 
      AND 'your_project_id' = PROJECT_USERS.project_ID 
      AND TASKS.archived < 'your_archived_value' 
    GROUP BY TASKS.task_ID, TRANSLATOR_LANGUAGES.language_code, PROJECTS.vote_threshold 
    HAVING your_having_clause_here
) STATS_PHP;

重点看：

• type列：如果出现ALL说明是全表扫描，需要加索引
• key列：如果是NULL说明没用到索引
• rows列：数值越大说明扫描的数据越多

2. 给关联表加针对性联合索引 #

根据你的查询条件，这些联合索引能大幅提升JOIN和WHERE的效率：

• TASKS：(project_ID, archived, task_ID) —— 覆盖WHERE的archived和JOIN的project_ID、task_ID
• TRANSLATED_TASKS：(task_ID, language_code) —— 匹配JOIN的两个字段
• TRANSLATOR_LANGUAGES：(translator_ID, language_code) —— 直接命中WHERE的两个条件
• PROJECT_USERS：(project_ID, translator_ID, language_code) —— 覆盖JOIN和WHERE的字段
• PROJECTS：(project_ID, vote_threshold) —— 匹配GROUP BY的字段

3. 彻底改掉循环内查询的坏习惯 #

这是最关键的优化！把循环里的N次查询改成1次批量查询，性能会有数量级的提升：

// 第一步：收集所有需要查询的language_code
$languageCodes = [];
foreach ($res->result() as $row) {
    $languageCodes[] = $row->language_code;
}
$languageCodes = array_unique($languageCodes); // 去重减少查询范围

// 第二步：改写查询，用IN条件一次性查所有language_code的结果
$placeholders = implode(',', array_fill(0, count($languageCodes), '?'));
$query = "SELECT tl.language_code, COUNT(*) AS total_count FROM (
    SELECT COUNT(DISTINCT TASKS.task_ID) AS count 
    FROM TASKS 
    JOIN TRANSLATOR_LANGUAGES tl
    JOIN PROJECT_USERS pu
    JOIN TRANSLATED_TASKS tt
    JOIN PROJECTS p
    ON (
        TASKS.task_ID = tt.task_ID 
        AND TASKS.project_ID = pu.project_ID 
        AND p.project_ID = TASKS.project_ID 
        AND tt.language_code = tl.language_code 
        AND pu.translator_ID = tl.translator_ID 
        AND pu.language_code = tl.language_code
    ) 
    WHERE tl.language_code IN ({$placeholders})
      AND tl.translator_ID = ? 
      AND pu.project_ID = ? 
      AND TASKS.archived < ? 
    GROUP BY TASKS.task_ID, tl.language_code, p.vote_threshold 
    HAVING " . $this->getHavingClause($filter) . ") STATS_PHP
GROUP BY tl.language_code";

// 第三步：绑定所有参数，执行一次查询
$params = array_merge($languageCodes, [$translator_ID, $project_ID, $archived]);
$countResults = $this->db->query($query, $params)->result_array();

// 第四步：把结果转成关联数组，方便后续循环取值
$countMap = [];
foreach ($countResults as $item) {
    $countMap[$item['language_code']] = $item['total_count'];
}

// 原来的循环里直接从countMap取数，不用再查数据库
foreach ($res->result() as $row) {
    $currentCount = isset($countMap[$row->language_code]) ? $countMap[$row->language_code] : 0;
    // 你的后续业务逻辑
}

4. 检查MySQL配置适配PHP7 #

PHP7的MySQLi驱动和旧的mysql扩展在连接和缓存上有差异：

• 如果用的是MySQL 5.7，确保innodb_buffer_pool_size设置足够（建议设为服务器内存的50%-70%），让数据库能缓存更多数据
• 若MySQL版本低于8.0，可以检查query_cache_type和query_cache_size是否开启，不过注意query_cache在高并发下可能有反效果，需根据实际情况调整

最后总结 #

优先解决循环内多次查询的问题，这是导致耗时从几秒变500秒的核心原因；再通过EXPLAIN和索引优化查询本身的执行效率，这样应该就能把性能拉回正常水平。

内容的提问来源于stack exchange，提问作者snehm