MySQL查询优化求助：PHP应用中高资源占用查询的优化方案
2026-8-15
MySQL查询优化请求
我有一段PHP应用中的MySQL查询语句，根据Cpanel资源使用监控数据，该查询占用了大量系统资源，恳请提供可行的优化方案。以下是查询语句及相关数据表结构：
$q = "SELECT businesses.id FROM businesses JOIN businesses_business_types ON businesses_business_types.business_id = businesses.id JOIN business_counties ON business_counties.business_id = businesses.id JOIN business_details ON business_details.business_id = businesses.id WHERE ( businesses_business_types.business_type_id = $cat OR businesses.primary_business_type_id = $cat ) AND ( business_counties.county_id = $county OR businesses.primary_city = $county ) AND business_details.status = 2 AND businesses.status = 13 LIMIT 1";
数据表结构
business_counties表
| 字段名 | 类型 | 是否允许为空 | 默认值 | 主键 |
|---|---|---|---|---|
| id | int(11) | 否 | - | 是 |
| business_id | int(11) | 否 | - | 否 |
| county_id | int(11) | 否 | - | 否 |
business_details表
| 字段名 | 类型 | 是否允许为空 | 默认值 | 主键 |
|---|---|---|---|---|
| id | int(11) | 否 | - | 是 |
| business_id | int(11) | 否 | - | 否 |
| image | varchar(255) | 是 | NULL | 否 |
| logo | varchar(255) | 是 | NULL | 否 |
| image_draft | varchar(255) | 是 | NULL | 否 |
| logo_draft | varchar(255) | 是 | NULL | 否 |
| description | text | 是 | NULL | 否 |
| description_draft | text | 否 | - | 否 |
| status | int(11) | 否 | 0 | 否 |
| modified | datetime | 否 | - | 否 |
| created | datetime | 否 | - | 否 |
| uk_description | text | 是 | NULL | 否 |
| uk_description_draft | text | 是 | NULL | 否 |
business_types表
| 字段名 | 类型 | 是否允许为空 | 默认值 | 主键 |
|---|---|---|---|---|
| id | int(11) | 否 | - | 是 |
| name | varchar(255) | 否 | - | 否 |
| parent_id | int(11) | 否 | 0 | 否 |
| description | text | 是 | NULL | 否 |
| display_order | int(11) | 是 | 0 | 否 |
| level | int(2) | 否 | 1 | 否 |
| display_in_nav | int(2) | 是 | 0 | 否 |
businesses表
| 字段名 | 类型 | 是否允许为空 | 默认值 | 主键 |
|---|---|---|---|---|
| id | int(11) | 否 | - | 是 |
| name | varchar(255) | 否 | - | 否 |
| postcode | varchar(50) | 否 | - | 否 |
| primary_city | int(11) | 否 | - | 否 |
| actual_location | int(11) | 是 | NULL | 否 |
| primary_business_type_id | int(11) | 是 | NULL | 否 |
| notes | text | 是 | NULL | 否 |
| status | int(2) | 是 | 0 | 否 |
| created | datetime | 是 | NULL | 否 |
| updated | datetime | 是 | NULL | 否 |
| business_address_1 | varchar(255) | 是 | NULL | 否 |
| business_address_2 | varchar(255) | 是 | NULL | 否 |
| business_address_3 | varchar(255) | 是 | NULL | 否 |
| business_city | varchar(255) | 是 | NULL | 否 |
| business_county | varchar(255) | 是 | NULL | 否 |
| business_postcode | varchar(50) | 是 | NULL | 否 |
| billing_address_1 | varchar(255) | 是 | NULL | 否 |
| billing_address_2 | varchar(255) | 是 | NULL | 否 |
| billing_address_3 | varchar(255) | 是 | NULL | 否 |
| billing_city | varchar(255) | 是 | NULL | 否 |
| billing_county | varchar(255) | 是 | NULL | 否 |
| billing_postcode | varchar(50) | 是 | NULL | 否 |
| salesman_id | int(11) | 否 | 0 | 否 |
| next_callback | datetime | 是 | NULL | 否 |
| business_function | varchar(255) | 是 | NULL | 否 |
| bunsiness_type | varchar(255) | 是 | NULL | 否 |
| rating | int(11) | 是 | 0 | 否 |
| business_type_tags | text | 是 | NULL | 否 |
| show_on | date | 是 | NULL | 否 |
| trial_start | date | 是 | NULL | 否 |
| trial_end | date | 是 | NULL | 否 |
| added_by | int(11) | 是 | 0 | 否 |
| latitude | double | 是 | NULL | 否 |
| longitude | double | 是 | NULL | 否 |
| multiple_email | tinyint(1) | 否 | 0 | 否 |
| website | varchar(255) | 是 | NULL | 否 |
businesses_business_types表
| 字段名 | 类型 | 是否允许为空 | 默认值 | 主键 |
|---|---|---|---|---|
| id | int(11) | 否 | - | 是 |
| business_id | int(11) | 否 | - | 否 |
| business_type_id | int(11) | 否 | - | 否 |
| level | int(2) | 否 | 2 | 否 |
优化方案
1. 添加针对性索引
businesses表创建组合索引：(status, primary_business_type_id, primary_city, id)，覆盖WHERE过滤条件和返回字段，避免回表查询。businesses_business_types表创建组合索引：(business_type_id, business_id)，快速过滤类型匹配的记录并关联商家表。business_counties表创建组合索引：(county_id, business_id)，高效定位区县匹配的商家记录。business_details表创建组合索引：(business_id, status)，快速验证商家状态是否符合要求。
2. 重构查询逻辑，规避OR的性能损耗
OR条件会导致MySQL无法有效利用索引，可将原查询拆分为多个子查询用UNION ALL合并（外层加LIMIT 1，找到第一条符合条件的记录就停止）：
SELECT businesses.id FROM businesses JOIN business_details ON business_details.business_id = businesses.id WHERE businesses.primary_business_type_id = $cat AND businesses.primary_city = $county AND business_details.status = 2 AND businesses.status = 13 LIMIT 1 UNION ALL SELECT businesses.id FROM businesses JOIN businesses_business_types ON businesses_business_types.business_id = businesses.id JOIN business_details ON business_details.business_id = businesses.id WHERE businesses_business_types.business_type_id = $cat AND businesses.primary_city = $county AND business_details.status = 2 AND businesses.status = 13 LIMIT 1 UNION ALL SELECT businesses.id FROM businesses JOIN business_counties ON business_counties.business_id = businesses.id JOIN business_details ON business_details.business_id = businesses.id WHERE businesses.primary_business_type_id = $cat AND business_counties.county_id = $county AND business_details.status = 2 AND businesses.status = 13 LIMIT 1 UNION ALL SELECT businesses.id FROM businesses JOIN businesses_business_types ON businesses_business_types.business_id = businesses.id JOIN business_counties ON business_counties.business_id = businesses.id JOIN business_details ON business_details.business_id = businesses.id WHERE businesses_business_types.business_type_id = $cat AND business_counties.county_id = $county AND business_details.status = 2 AND businesses.status = 13 LIMIT 1 LIMIT 1;
每个子查询都能利用对应索引快速定位数据，大幅提升查询效率。
3. 前置过滤条件减少关联数据量
将过滤条件直接写在JOIN语句中，在关联阶段就过滤掉不符合的记录：
比如JOIN businesses_business_types ON businesses_business_types.business_id = businesses.id AND businesses_business_types.business_type_id = $cat，避免后续处理无效数据。
4. 校验JOIN的必要性
原查询用JOIN business_counties会过滤掉没有关联区县记录的商家，若业务允许商家仅通过primary_city=$county匹配，需将JOIN改为LEFT JOIN，并调整WHERE条件为(business_counties.county_id = $county OR businesses.primary_city = $county OR business_counties.id IS NULL)，避免误过滤有效数据的同时优化性能。
5. 修复SQL注入风险
原查询直接拼接变量到SQL中存在严重安全隐患，改用PHP预处理语句：
$stmt = $pdo->prepare("SELECT businesses.id FROM businesses JOIN businesses_business_types ON businesses_business_types.business_id = businesses.id JOIN business_counties ON business_counties.business_id = businesses.id JOIN business_details ON business_details.business_id = businesses.id WHERE (businesses_business_types.business_type_id = ? OR businesses.primary_business_type_id = ?) AND (business_counties.county_id = ? OR businesses.primary_city = ?) AND business_details.status = 2 AND businesses.status = 13 LIMIT 1"); $stmt->execute([$cat, $cat, $county, $county]);
预处理不仅提升安全性，还能让MySQL缓存查询计划，重复执行时性能更优。
内容的提问来源于stack exchange，提问作者jdk
相关产品推荐
相关产品推荐
