CRM自动生成的SELECT COUNT查询性能优化咨询
2026-6-14
CRM自动生成慢查询的性能优化方案
原始慢查询SQL
SELECT COUNT(DISTINCT vtiger_crmentity.crmid) AS count FROM vtiger_production INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_production.productionid INNER JOIN vtiger_crmentityrel ON (vtiger_crmentityrel.relcrmid = vtiger_crmentity.crmid OR vtiger_crmentityrel.crmid = vtiger_crmentity.crmid) LEFT JOIN vtiger_productioncf ON vtiger_productioncf.productionid = vtiger_production.productionid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted = 0 AND (vtiger_crmentityrel.crmid = 1913774 OR vtiger_crmentityrel.relcrmid = 1913774)
查询执行计划
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | vtiger_crmentityrel | index_merge | crmid_idx, relcrmid_idx, vtiger_crmentityrel_crmid_IDX | crmid_idx, relcrmid_idx | 4,4 | 29323 | Using union(crmid_idx,relcrmid_idx); Using where | |
| 1 | SIMPLE | vtiger_production | index | PRIMARY | vtiger_production_UN_PN | 4 | 79859 | Using where; Using index | |
| 1 | SIMPLE | vtiger_crmentity | eq_ref | PRIMARY, crmentity_deleted_idx, vtiger_crmentity_deleted_IDX | PRIMARY | 4 | spvtwp.vtiger_production.productionid | 1 | Using where |
性能分析
| Stage | Time (seconds) |
|---|---|
| executing | 0.000078 |
| Sending data | 520.865258 |
| end | 0.000060 |
涉及表信息
| Database | Table | Table Type | Engine | Version | Row Format | Rows | Avg Row Length | Data Length | Max Data Length | Index Length | Data Free | Auto Increment | Create Time | Update Time | Check Time | Collation | Checksum | Create Options | Comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| def | vtiger_crmentity | BASE TABLE | InnoDB | 10 | Compressed | 3523335 | 48 | 170352640 | 244023296 | 5242880 | 2025-02-07 18:39:00.000 | 2025-02-07 18:39:00.000 | utf8_general_ci | row_format=COMPRESSED | |||||
| def | vtiger_crmentityrel | BASE TABLE | InnoDB | 10 | Compressed | 1117999 | 29 | 33349632 | 48119808 | 4194304 | 2025-02-12 19:38:43.000 | 2025-02-12 19:38:43.000 | utf8_general_ci | row_format=COMPRESSED | |||||
| def | vtiger_groups | BASE TABLE | InnoDB | 10 | Compressed | 0 | 0 | 8192 | 8192 | 0 | 2025-01-30 22:34:50.000 | 2025-01-30 22:34:50.000 | utf8_general_ci | row_format=COMPRESSED | |||||
| def | vtiger_production | BASE TABLE | InnoDB | 10 | Compressed | 79859 | 29 | 2367488 | 2383872 | 2621440 | 2025-01-30 22:51:16.000 | 2025-01-30 22:51:16.000 | utf8_general_ci | row_format=COMPRESSED | |||||
| def | vtiger_productioncf | BASE TABLE | InnoDB | 10 | Compressed | 88526 | 20 | 1843200 | 0 | 2621440 | 2025-01-30 22:51:18.000 | 2025-01-30 22:51:18.000 | utf8_general_ci | row_format=COMPRESSED | |||||
| def | vtiger_users | BASE TABLE | InnoDB | 10 | Compressed | 41 | 599 | 24576 | 16384 | 0 | 53 | 2025-01-30 22:51:46.000 | 2025-01-30 22:51:46.000 | utf8_general_ci | row_format=COMPRESSED |
服务器配置与核心参数
- 服务器RAM：32G
- InnoDB总数据量：7G（共1335张表）
- 关键配置参数：
| variable | value |
|---|---|
| innodb_buffer_pool_size | 17179869184 |
| tmp_table_size | 1073741824 |
| max_heap_table_size | 1073741824 |
性能优化方案（无法修改查询语句）
1. 索引结构优化
- 给
vtiger_crmentityrel创建联合索引(crmid, relcrmid)，替换当前的单字段索引，避免index_merge带来的性能损耗。MySQL的index_merge在处理OR条件时效率远低于联合索引，联合索引可直接定位符合crmid=1913774 OR relcrmid=1913774的所有行。 - 给
vtiger_crmentity添加联合索引(crmid, deleted)，当前查询依赖PRIMARY索引后再过滤deleted=0，联合索引可直接覆盖过滤条件，减少回表开销。 - 检查
vtiger_production的vtiger_production_UN_PN索引是否以productionid为前缀，若不是则调整为以productionid开头的索引，或直接使用PRIMARY索引（如果productionid是主键），当前非主键索引扫描会额外消耗资源。
2. MySQL配置参数调整
- 提升
innodb_buffer_pool_size：当前设置为16G，服务器有32G内存，建议调整为22G-24G（innodb_buffer_pool_size=24159191040），让更多表数据和索引缓存到内存，减少磁盘IO，直接降低"Sending data"阶段耗时。 - 临时关闭
index_merge优化：若创建联合索引后MySQL仍选择index_merge，可设置optimizer_switch='index_merge=off'（全局或会话级），强制使用联合索引，需先测试验证效果。 - 调整事务日志刷写策略：若业务允许，设置
innodb_flush_log_at_trx_commit=2，减少磁盘刷写频率提升IO性能，仅在服务器断电时存在少量数据丢失风险。
3. 表数据与结构优化
- 清理
vtiger_crmentityrel无效数据：该表有110万+行，清理冗余关联记录、已删除实体的关联行，减少索引扫描行数。 - 重建大表索引：对
vtiger_crmentity、vtiger_crmentityrel执行ALTER TABLE ... FORCE（InnoDB），重建索引并优化表空间，消除碎片提升扫描效率。 - 反馈CRM厂商优化查询逻辑：
vtiger_productioncf、vtiger_users、vtiger_groups为LEFT JOIN但未在查询中使用，后续可建议厂商移除不必要的关联，当前只能通过缓存或索引降低影响。
4. 缓存与间接查询优化
- 启用查询缓存（MySQL 5.7及以下版本）：设置
query_cache_type=1和合适的query_cache_size，缓存查询结果，避免重复执行，不适用于数据频繁更新的场景。 - 预计算结果存储：创建定时任务，用存储过程预先计算该查询结果并存储到临时表，让CRM查询临时表，需确保临时表数据与原表同步，适合低更新频率业务。
内容的提问来源于stack exchange，提问作者Юрий Глущенко
相关产品推荐
相关产品推荐
