MySQL千万级数据表性能优化与高效行计数方案咨询

核心问题分析 #

原代码存在两个关键性能隐患：

• 重复执行同条件查询，完全冗余；
• 通过SELECT id后取num_rows计数，需要数据库返回所有匹配的id记录，1000万级数据下会产生极大的IO开销和内存占用。

具体优化步骤 #

1. 给过滤字段添加索引 #

针对RegAddress_County字段创建普通索引，这是大表查询性能提升的核心，能避免全表扫描：

CREATE INDEX idx_regaddress_county ON company_data(RegAddress_County);

2. 使用COUNT(*)直接统计 #

MySQL对COUNT(*)有专门优化，尤其是在有索引的情况下，无需返回实际数据就能快速得到计数结果，效率远高于取num_rows。

3. 修复SQL注入风险 #

原代码直接拼接变量到SQL语句中，存在严重安全漏洞，必须使用预处理语句。

改写后的代码 #

// 执行高效的COUNT(*)统计，同时避免SQL注入
$queryCount = "SELECT COUNT(*) AS counthowmanycompany FROM company_data WHERE RegAddress_County = ?";
$stmt = $conn->prepare($queryCount);
$stmt->bind_param("s", $county); // "s"表示字符串类型，可根据$county实际类型调整
$stmt->execute();
$stmt->bind_result($countHowManyCompany);
$stmt->fetch();
$stmt->close();

// 若后续确实需要获取id列表，再单独执行查询（不需要可删除）
// $query = "SELECT id FROM company_data WHERE RegAddress_County = ?";
// $stmt = $conn->prepare($query);
// $stmt->bind_param("s", $county);
// $stmt->execute();
// $result = $stmt->get_result();
// // 后续处理结果集逻辑...

额外优化建议 #

• 如果RegAddress_County字段重复率极高（比如仅几十个不同值），可将统计结果缓存到Redis等缓存系统，定期更新，进一步降低数据库压力；
• 高峰时段执行统计时，可添加LOW_PRIORITY关键字，让查询在数据库空闲时执行：

  SELECT COUNT(*) AS counthowmanycompany FROM company_data WHERE RegAddress_County = ? LOW_PRIORITY;
  

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