Pimcore无法查看文件夹下全部对象，通信失败问题求助

问题现象 #

点击文件夹以网格视图展示时触发通信失败错误：

Timestamp: Fri Nov 08 2024 10:02:01 GMT+0530 (India Standard Time)
Status: 0 | communication failure
URL: /admin/object/grid-proxy?classId=1&folderId=41419&xaction=read&_dc=1731040291645
Method: POST
Message:
undefined

已执行的优化操作 #

• 将Innodb缓冲池大小调整至46G
• 增大全局等待超时、读取超时等参数
• 启用慢查询日志，捕获到以下慢查询：

Time: 2024-11-08T04:46:48.085146Z
User@Host: hostname Id: 162
Query_time: 42.447281 Lock_time: 0.000001 Rows_sent: 25 Rows_examined: 35485195
SET timestamp=1731041165;
SELECT object_2.o_id as o_id, object_2.o_type as o_type FROM object_2 WHERE (o_path = '/Assets/folder' OR o_path LIKE '/Assets/folder/%') AND object_2.o_type IN ('object','folder') ORDER BY o_id ASC LIMIT 25;

Time: 2024-11-08T04:47:34.063308Z
User@Host: hostname Id: 162
Query_time: 45.821325 Lock_time: 0.000002 Rows_sent: 1 Rows_examined: 35489690
SET timestamp=1731041208;
SELECT COUNT(*) FROM object_2 WHERE (o_path = '/Assets/folder' OR o_path LIKE '/Assets/folder/%') AND object_2.o_type IN ('object','folder');

• 为对象表的o_type、o_path、o_classId字段创建单独索引

当前MySQL配置（my.cnf） #

[mysqld]
bind-address = 0.0.0.0
log-bin=mysql-bin
server-id=1
max_execution_time = 99999999
innodb_buffer_pool_size = 46G
innodb_log_file_size = 1G
innodb_file_per_table = 1
key_buffer_size = 2G
sort_buffer_size = 64M
sort_buffer_size = 64M
tmp_table_size = 64M # Set to 64 MB
max_heap_table_size = 64M # Set to 64 MB
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
innodb_flush_method = O_DSYNC
innodb_log_buffer_size = 512M

执行计划与表结构 #

• 执行的EXPLAIN语句：

EXPLAIN SELECT COUNT(*) FROM object_2 WHERE (o_path = '/Assets/folder' OR o_path LIKE '/Assets/folder/%') AND object_2.o_type IN ('object','folder');

• 执行计划截图：
• 表结构查询语句：

mysql> SHOW CREATE TABLE objects;

• 表结构截图：

技术解决方案 #

1. 核心优化：创建复合索引 #

当前单独索引无法覆盖查询的复合过滤条件，导致全表扫描（扫描行数超3500万）。创建以下复合索引：

CREATE INDEX idx_o_type_o_path ON object_2(o_type, o_path);

该索引可同时匹配o_type过滤和o_path的前缀匹配查询，直接定位目标数据，大幅降低查询耗时。

2. 简化查询逻辑（可选） #

将o_path = '/Assets/folder' OR o_path LIKE '/Assets/folder/%'替换为等价的o_path LIKE '/Assets/folder%'，简化条件的同时不影响结果，让索引匹配更高效：

-- 优化后的列表查询
SELECT object_2.o_id as o_id, object_2.o_type as o_type 
FROM object_2 
WHERE o_type IN ('object','folder') 
  AND o_path LIKE '/Assets/folder%' 
ORDER BY o_id ASC LIMIT 25;

-- 优化后的计数查询
SELECT COUNT(*) 
FROM object_2 
WHERE o_type IN ('object','folder') 
  AND o_path LIKE '/Assets/folder%';

3. MySQL配置微调 #

• 删除重复的sort_buffer_size = 64M配置，消除冗余
• 若使用SSD存储，将innodb_flush_method改为O_DIRECT，减少磁盘IO开销：

innodb_flush_method = O_DIRECT

• 增加join_buffer_size（应对潜在隐式关联）：

join_buffer_size = 16M

4. Pimcore系统层面优化 #

• 在config/config.yml中开启对象网格视图缓存，减少重复查询请求
• 调整网格默认分页数量（比如从25条改为10条），降低单查询数据量
• 检查Pimcore后台接口超时设置，确保超时阈值大于MySQL查询的最大执行时间

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