Mysql基于成本选择索引

本篇文章介绍mysql基于成本选择索引的行为，解释为什么有时候明明可以走索引，但mysql却没有走索引的原因

mysql索引失效的场景大致有几种

1. 不符合最左前缀原则
2. 在索引列上使用函数或隐式类型转换
3. 使用like查询，如 %xxx
4. 回表代价太大
5. 索引列区分度过低
6. 数据量少，没有走索引的必要
7. in中的条件过多

其中前三种失效场景，是因为无法利用索引的有序性。而后面几种场景，则是Mysql从成本上考虑，认为走索引的代价比不走索引的代价高，因此Mysql没有走索引。

同样的，如果我们一个查询，可以利用多个索引，那么mysql最终会走哪个索引呢？这也是基于成本考虑的，哪个索引的成本更低，就使用哪个索引。

我们可以来做个实验。创建一个person表，该表有一个主键索引，一个联合索引，以及一个create_time索引。

CREATE TABLE `person`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `score` int NOT NULL,
  `create_time` timestamp NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name_score`(`name`, `score`) USING BTREE,
  INDEX `create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100000 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6
7
8
9

通过下面的存储过程循环创建 10 万条测试数据。

CREATE DEFINER=`root`@`%` PROCEDURE `insert_person`()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    set c_id=c_id+1;
    end while;
end
1
2
3
4
5
6
7
8

接下来，查看下面语句的执行计划。从执行计划中可以看出该sql可能走name_score和create_time俩个索引。但最终mysql选择的确实全表扫描

EXPLAIN SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2023-09-05 05:00:00'
1

我们查询条件的时间从5点修改成22点，在查看执行计划，发现此时走了create_time索引。

EXPLAIN SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2023-09-05 22:00:00'
1

同一条sql，不同的查询条件，mysql会根据计算的成本选择走或不走索引。这里的成本主要包括IO成本和CPU成本：

1. IO 成本，是从磁盘把数据加载到内存的成本。
2. CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本。

我们仔细看上面俩个执行计划的rows列，可以很明显的发现第二个执行计划的rows小得多，也就是说要扫描的行更小，CPU的成本也就会更小，所以mysql选择了走索引。

在Mysql5.6及之后的版本中，我们还可以使用optimizer trace功能查看每个索引、全表扫描具体的成本是多少，从而知道mysql为什么选这个索引，或为什么走全表扫描。

如下代码所示，打开 optimizer_trace 后，再执行 SQL 就可以查询 information_schema.OPTIMIZER_TRACE 表查看执行计划了，最后可以关闭 optimizer_trace 功能：

SET optimizer_trace="enabled=on";
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2023-09-05 05:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
1
2
3
4

OPTIMIZER_TRACE部分片段如下：

"analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "name_score",
                        "ranges": [
                          "name84059 < name"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 25362,
                        "cost": 27618.4,     #走name_score索引需要花费的成本
                        "chosen": false,	 #没有选择name_score索引
                        "cause": "cost"
                      },
                      {
                        "index": "create_time",
                        "ranges": [
                          "0x64f64550 < create_time"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 46320,   	#走create_time索引需要花费的成本
                        "cost": 50440.2,	#没有选择create_time索引
                        "chosen": false,
                        "cause": "cost"
                      }
                    ]
                  }
{
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`person`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 92641,
                      "access_type": "scan",		#走全表花费的成本
                      "resulting_rows": 92641,
                      "cost": 9549.9,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 92641,
                "cost_for_plan": 9549.9,
                "chosen": true
              }
            ]
          }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

从optimizer_trace中我们可以看出，name_score索引、create_time索引、全表扫描的成本分别是27618.4、50440.2、9549.9所以mysql最终选择了全表扫描。

有时候mysql也可能会选错索引，此时我们可以通过FORCE INDEX强制mysql走索引，如：

SELECT * FROM person FORCE INDEX(create_time) WHERE NAME >'name84059' AND create_time>'2023-09-05 05:00:00'
1

当然，实际上并不建议使用FORCE INDEX，因为mysql的选择往往会更正确