无法调整表结构时MySQL多动态FIND_IN_SET批量统计查询方案

针对无法调整表结构的场景，最核心的优化是把原本N次查询（每个动物单独查1次）、或者查回所有匹配行到PHP二次计算的流程，压缩为1次数据库全表扫描直接出统计结果，大幅降低网络传输和重复扫描的开销。

核心实现逻辑 #

利用FIND_IN_SET匹配返回值的特性（匹配成功返回非0的位置值，不匹配返回0），直接在SQL层做条件聚合，一次查询完成所有待搜索动物的出现次数统计。
针对示例中的搜索数组，SQL写法如下：

SELECT
  SUM(FIND_IN_SET('fox', `value`) > 0) AS fox_count,
  SUM(FIND_IN_SET('cat', `value`) > 0) AS cat_count,
  SUM(FIND_IN_SET('dog', `value`) > 0) AS dog_count
FROM `animal`;

对示例表的查询结果为：

相比你之前考虑的OR关联多个FIND_IN_SET的方案（需要把所有匹配的value行回传到PHP端做拆分统计），单次条件聚合的方案只需要返回1行统计结果，没有二次计算和大流量传输的开销。

适配数十个搜索值的动态生成方式 #

待搜索动物数量较多时，无需手动写每个统计项，可以在PHP端动态拼接SQL，搭配预处理语句避免SQL注入风险，示例代码：

<?php
$animalsToSearch = ['fox', 'cat', 'dog'];
$selectParts = [];
$bindParams = [];
foreach ($animalsToSearch as $animal) {
    // 每个搜索值用占位符代替，防止SQL注入
    $selectParts[] = "SUM(FIND_IN_SET(?, `value`) > 0) AS `{$animal}_count`";
    $bindParams[] = $animal;
}
$sql = "SELECT " . implode(', ', $selectParts) . " FROM `animal`";
// 执行预处理查询
$stmt = $pdo->prepare($sql);
$stmt->execute($bindParams);
$rawResult = $stmt->fetch(PDO::FETCH_ASSOC);
// 整理为最终统计格式
$count = [];
foreach ($animalsToSearch as $animal) {
    $count[$animal] = $rawResult["{$animal}_count"];
}

关于派生表连接方案的性能说明 #

你附的EXPLAIN执行计划对应的就是将搜索值生成派生表后和animal表关联的方案，该方案存在明显的性能劣势：

• 对animal表执行全表扫描（对应rows值近190万行）
• 使用块嵌套循环做连接，额外引入连接开销
• 触发临时表和文件排序，进一步增加查询耗时
  相比之下，直接做条件聚合的方案没有额外的连接和排序开销，全表扫描一次即可完成所有计算，资源占用更低。

注意：由于FIND_IN_SET无法利用普通B树索引，所有针对逗号分隔列的匹配查询基本都需要全表扫描，将多次扫描合并为单次扫描就是这类场景下的最优优化方向。

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