迁移至Percona Server 5.7后查询未用client表索引致性能下降求助

遇到这种优化器选择偏差的问题确实棘手，尤其是在不能修改查询语句的限制下。结合你描述的场景（从MariaDB 10.2迁移到Percona Server 5.7后查询性能骤降，核心是client表索引未被使用，ORDER BY导致临时表开销过大），我整理了几个针对性的解决方向：

1. 创建精准的覆盖索引（优先级最高） #

你的查询核心痛点是ORDER BY client.id DESC LIMIT 10 OFFSET 10触发了临时表排序，且优化器未利用client表的索引。最有效的方案是创建同时满足过滤、排序、覆盖查询所需字段的复合索引：

CREATE INDEX idx_client_covering ON client 
(status, id DESC) 
INCLUDE (name1, name2, user_id, rate_id, provider_id, main_address_id, main_person_id, main_organization_id, id_organization, shipping_address_id, shipping_person_id, correspondense_address_id, correspondense_person_id);

• 索引前缀(status, id DESC)：先过滤status !=5的记录，再直接按id倒序排序，完美匹配WHERE和ORDER BY条件，避免临时表生成。
• INCLUDE子句：包含查询中需要从client表获取的所有字段（关联外键、返回字段），实现索引覆盖，无需回表查询原数据，进一步提升效率。

2. 校准优化器统计信息 #

Percona Server与MariaDB的统计信息收集逻辑存在差异，如果client表的统计信息过时或不准确，优化器会误判索引成本。可以执行以下操作：

• 手动更新表统计信息：

  ANALYZE TABLE client;
  

• 调整InnoDB统计样本量（针对大数据量表）：

  SET GLOBAL innodb_stats_sample_pages = 100; -- 默认是8，增大样本量提升统计准确性
  ANALYZE TABLE client;
  

3. 调整优化器成本模型（适配旧版行为） #

Percona 5.7的优化器成本计算逻辑比MariaDB 10.2更激进，你可以尝试切换到旧版成本模型，让优化器更倾向于选择索引：

• 会话级测试（不影响全局）：

  SET SESSION optimizer_cost_model = 'legacy';
  

  执行查询验证性能，如果有效，可以考虑将该参数配置到my.cnf中全局生效，或者通过应用连接池设置会话参数。

4. 排查关联表的辅助索引 #

虽然核心问题在client表，但关联表的索引缺失也可能间接影响优化器的选择：

• 确保所有关联表的关联字段（如users.id、privateData.id、tariff.id等）都有主键或唯一索引（通常主键默认存在，但需确认）。
• 对于users.status !=5、organizations.status !=5这类过滤条件，可以给users和organizations表添加(id, status)的复合索引，减少关联时的过滤开销。

5. 验证临时表相关参数 #

如果临时表排序是主要开销，可以适当调整以下参数（需根据服务器内存配置合理设置）：

sort_buffer_size = 2M -- 增大排序缓冲区，减少磁盘临时表使用
tmp_table_size = 64M
max_heap_table_size = 64M -- 确保内存临时表足够大，避免写入磁盘

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