如何优化Digikam中分配检测人物时的MariaDB慢查询？

问题背景 #

在Digikam中为检测到的人物分配标签时，一条内置查询频繁运行且耗时超1秒，CPU占用率高，查询语句如下：

SELECT 
tagid, COUNT(*) 
FROM ImageTagProperties 
LEFT JOIN Images ON Images.id=ImageTagProperties.imageid WHERE ImageTagProperties.property='autodetectedFace' AND Images.status=1   
GROUP BY tagid

当前已使用部分索引，但性能仍不理想，索引统计信息：

SELECT * FROM information_schema.INDEX_STATISTICS WHERE TABLE_SCHEMA = 'digikam';
+--------------+--------------------+----------------------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME         | INDEX_NAME                       | ROWS_READ |
+--------------+--------------------+----------------------------------+-----------+
| digikam      | ImageTagProperties | imagetagproperties_imageid_index |    160043 |
| digikam      | Images             | PRIMARY                          |     72919 |
+--------------+--------------------+----------------------------------+-----------+
2 rows in set (0,001 sec)

由于无法修改Digikam内置查询，需通过创建索引或MariaDB调优解决问题。

解决方案 #

1. 创建针对性复合索引 #

现有索引无法覆盖查询的过滤、连接和分组需求，建议创建以下两个复合索引：

• ImageTagProperties表：创建包含property、imageid、tagid的复合索引

  CREATE INDEX idx_itp_property_imageid_tagid ON ImageTagProperties (property, imageid, tagid);
  

  该索引可快速筛选出property='autodetectedFace'的记录，同时直接获取连接和分组所需的imageid和tagid，避免回表查询。
• Images表：创建包含id和status的复合索引

  CREATE INDEX idx_images_id_status ON Images (id, status);
  

  虽然id是主键，但复合索引可直接在索引层面完成status=1的过滤，无需访问主表数据。

2. MariaDB配置调优 #

• 调整缓冲区大小：根据服务器内存情况，适当增大innodb_buffer_pool_size（建议设置为物理内存的50%-70%，专用数据库服务器可进一步提高），让更多数据和索引缓存到内存，减少磁盘IO。
• 优化排序分组参数：增大sort_buffer_size，提升分组统计的效率，避免因内存不足使用磁盘临时表。
• 更新索引统计信息：执行ANALYZE TABLE ImageTagProperties, Images;，让优化器获取最新的索引和表统计数据，生成更优的执行计划。

3. 验证效果 #

创建索引和调整配置后，通过EXPLAIN查看查询执行计划，确认新索引是否被使用，同时监控查询耗时和CPU占用率的变化。

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