如何提升该SQL统计查询语句的执行效率？

我有一张结构如下的数据表：

我的需求是统计满足以下条件的行数：

• name属于指定名称列表
• task属于指定任务列表
• startDate处于指定日期范围内

我用PHP开发，当前代码和SQL查询语句如下：

$taskList = 需查询的所有任务列表;
$nameList = 需查询的所有名称列表;

SELECT COUNT(ID) FROM table WHERE
`task` IN ('".implode("', '", $taskList)."')
AND `name` IN ('".implode("', '", $nameList)."')
AND (startDate >= '".$startDate."')
AND (startDate <= '".$endDate."')

单次查询耗时约40毫秒，但我需要多次执行这个查询，有没有办法缩短执行时间？

1. 添加复合索引 #

针对查询的过滤条件，创建复合索引是最直接的优化手段。推荐创建包含name、task、startDate的联合索引：

CREATE INDEX idx_name_task_startdate ON your_table_name (name, task, startDate);

索引顺序建议把过滤基数高的字段放在前面（比如name的不同值更多就放最前），能更快缩小数据筛选范围。

2. 改用参数化预处理查询 #

当前直接拼接SQL的写法不仅有注入风险，多次执行时数据库无法复用查询计划。用PHP的PDO或mysqli预处理语句，数据库会缓存执行计划，重复执行时直接复用，减少SQL解析开销：

// PDO示例
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'pass');
$taskPlaceholders = implode(',', array_fill(0, count($taskList), '?'));
$namePlaceholders = implode(',', array_fill(0, count($nameList), '?'));
$sql = "SELECT COUNT(ID) FROM your_table_name WHERE task IN ($taskPlaceholders) AND name IN ($namePlaceholders) AND startDate >= ? AND startDate <= ?";

$stmt = $pdo->prepare($sql);
$params = array_merge($taskList, $nameList, [$startDate, $endDate]);
$stmt->execute($params);
$count = $stmt->fetchColumn();

3. 合并批量查询 #

如果多次查询是不同的条件组合（不同的name列表、task列表或日期范围），可以把多个查询合并成一次，用分组统计一次性获取所有结果，避免多次数据库连接和查询的开销：

SELECT 
    CONCAT_WS('|', GROUP_CONCAT(DISTINCT name), GROUP_CONCAT(DISTINCT task), startDate_range) AS group_key,
    COUNT(ID) AS total
FROM (
    SELECT 
        ID, name, task,
        CASE 
            WHEN startDate BETWEEN '2022-09-20' AND '2022-09-21' THEN 'range1'
            WHEN startDate BETWEEN '2022-09-22' AND '2022-09-23' THEN 'range2'
            -- 按需添加更多日期范围
        END AS startDate_range
    FROM your_table_name
    WHERE (name IN ('John','Joe') AND task IN ('Day','Midday')) 
       OR (name IN ('Sara') AND task IN ('Night'))
       -- 按需添加更多条件组
) AS temp
WHERE startDate_range IS NOT NULL
GROUP BY group_key;

之后在PHP中解析结果对应到各个查询需求即可。

4. 缓存查询结果 #

如果查询条件和数据表不会频繁更新，可以把结果缓存到Redis、Memcached或PHP文件缓存中，重复查询时直接读缓存，跳过数据库访问：

$redis = new Redis();
$redis->connect('localhost', 6379);
// 用条件拼接唯一缓存键
$cacheKey = 'count_' . md5(implode(',', $nameList) . '_' . implode(',', $taskList) . '_' . $startDate . '_' . $endDate);

if ($redis->exists($cacheKey)) {
    $count = $redis->get($cacheKey);
} else {
    // 执行数据库查询获取$count
    $redis->setex($cacheKey, 3600, $count); // 缓存1小时，按需调整过期时间
}

5. 优化COUNT语句 #

如果ID是主键，COUNT(ID)没问题；换成COUNT(*)效率会略高，因为数据库无需检查ID是否为NULL：

SELECT COUNT(*) FROM your_table_name WHERE ...

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