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;

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

CREATE DEFINER=`root`@`%` PROCEDURE `insert_person`()
begindeclare c_id integer default 1;while c_id<=100000 doinsert 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

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

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

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

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

同一条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";

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}]}

从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'

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