MariaDB 10.3升级至10.9后索引未被使用的技术问询

数据老陈

2026-7-6

升级MariaDB 10.3到10.9后索引使用的困惑与抉择

我正将MariaDB服务器从10.3版本（10.3.38-MariaDB-0ubuntu0.20.04.1）升级至10.9版本（10.9.3-MariaDB-1:10.9.3+maria~ubu2004-log）。预部署测试时发现部分场景下索引未被使用，但生产环境的10.3版本会使用索引。

已知这是版本间优化器成本判断逻辑变化导致，尝试调整过eq_range_index_dive_limit、use_stat_tables、sort_buffer_size等配置项，也执行过各类ANALYZE TABLE操作，但对目标SELECT语句的索引使用情况无任何改变。

我现在面临以下抉择：

信任版本变更，寄希望于不使用索引的性能更优；
对所有查询执行EXPLAIN并强制使用索引（之前已这么做过）；
无法判断让优化器决定不使用索引，还是强制索引哪种更好。

另外，我发现某些场景下不使用索引时查询返回速度更快，这是否支持选择第一个方案？希望得到经验反馈或调优建议，比如如何提升索引使用率，以及如果使用索引不如全表扫描，是否真会导致性能下降？

相关上下文SQL输出

未强制索引的EXPLAIN结果

MariaDB [pweb]&gt; EXPLAIN extended select * from `accounting_transactions` where `accounting_transactions`.`cr_account` = 'f7d78ef5-ca59-44d1-9d67-70a83960f473' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: accounting_transactions
         type: ALL
possible_keys: accounting_transactions_cr_account_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 532030
     filtered: 35.63
        Extra: Using where
1 row in set, 1 warning (0.002 sec)

强制索引的EXPLAIN结果

MariaDB [pweb]&gt; EXPLAIN extended select * from `accounting_transactions` force index (accounting_transactions_cr_account_index ) where `accounting_transactions`.`cr_account` = 'f7d78ef5-ca59-44d1-9d67-70a83960f473'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: accounting_transactions
         type: ref
possible_keys: accounting_transactions_cr_account_index
          key: accounting_transactions_cr_account_index
      key_len: 144
          ref: const
         rows: 189558
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.001 sec)

当前MariaDB版本

MariaDB [pweb]&gt; SELECT VERSION();
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 10.9.3-MariaDB-1:10.9.3+maria~ubu2004-log |
+-------------------------------------------+
1 row in set (0.001 sec)

表结构

MariaDB [pweb]&gt; show create table accounting_transactions \G
*************************** 1. row ***************************
       Table: accounting_transactions
Create Table: CREATE TABLE `accounting_transactions` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `event_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `dr_account` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cr_account` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accounting_transactions_event_id_index` (`event_id`),
  KEY `accounting_transactions_dr_account_index` (`dr_account`),
  KEY `accounting_transactions_cr_account_index` (`cr_account`),
  CONSTRAINT `accounting_transactions_cr_account_foreign` FOREIGN KEY (`cr_account`) REFERENCES `accounting_accounts` (`id`),
  CONSTRAINT `accounting_transactions_dr_account_foreign` FOREIGN KEY (`dr_account`) REFERENCES `accounting_accounts` (`id`),
  CONSTRAINT `accounting_transactions_event_id_foreign` FOREIGN KEY (`event_id`) REFERENCES `accounting_events` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.002 sec)

查询缓存配置

MariaDB [pweb]&gt; SHOW variables LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
7 rows in set (0.002 sec)

分析与建议

核心逻辑解读

从EXPLAIN结果看，该查询匹配的行数占表总数据的35%左右，对于InnoDB来说，当查询返回数据量超过表的20%-30%时，优化器会判定全表扫描成本更低——因为索引需要先定位数据再回表读取整行，这个过程的随机IO开销可能远高于全表扫描的顺序IO。10.9版本的优化器成本模型比10.3更精准，所以做出了不同选择，而你实际测试到全表扫描更快，也验证了这个判断的合理性。

具体方案

优先信任优化器选择：既然实际测试中全表扫描性能更优，说明优化器的成本计算符合当前场景。新版本优化器在成本模型上做了大量改进，大部分情况下无需强行干预。
避免盲目强制索引：强制索引会剥夺优化器的动态调整能力，未来数据分布变化后，强制索引可能反而成为性能瓶颈。
修正统计信息配置：你当前设置use_stat_tables=NEVER，导致优化器无法使用最新的表统计数据，建议改为use_stat_tables=PREFERABLY，再执行ANALYZE TABLE accounting_transactions;，让优化器基于更准确的统计信息做判断。
尝试覆盖索引：如果该查询频率较高，可以创建包含所需字段的覆盖索引，比如：
```
CREATE INDEX idx_cr_account_cover ON accounting_transactions (cr_account, id, event_id, amount, created_at);
```
覆盖索引可以让查询直接从索引获取数据，无需回表，可能会让优化器重新选择索引，同时提升整体查询性能。
验证生产负载：若担心生产环境性能，可以用sysbench或自定义脚本模拟高并发场景，对比两种查询方式的执行时间、CPU和IO占用，确保选择的方案符合真实负载需求。

关于性能下降的担忧

如果优化器基于准确的统计数据和成本计算选择全表扫描，不会导致性能下降。反而强制索引可能在数据量变化后引发性能问题。只有当优化器因统计信息不准确做出错误判断时，才需要手动干预。

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