网站开发者的常用工具,在哪查网站备案,h5个网站的区别,网站制作推广SSL本篇文章介绍mysql基于成本选择索引的行为#xff0c;解释为什么有时候明明可以走索引#xff0c;但mysql却没有走索引的原因 mysql索引失效的场景大致有几种
不符合最左前缀原则在索引列上使用函数或隐式类型转换使用like查询#xff0c;如 %xxx回表代价太大索引列区分度过… 本篇文章介绍mysql基于成本选择索引的行为解释为什么有时候明明可以走索引但mysql却没有走索引的原因 mysql索引失效的场景大致有几种
不符合最左前缀原则在索引列上使用函数或隐式类型转换使用like查询如 %xxx回表代价太大索引列区分度过低数据量少没有走索引的必要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 DEFINERroot% PROCEDURE insert_person()
begindeclare c_id integer default 1;while c_id100000 doinsert into person values(c_id, concat(name,c_id), c_id100, date_sub(NOW(), interval c_id second));set c_idc_id1;end while;
end接下来查看下面语句的执行计划。从执行计划中可以看出该sql可能走name_score和create_time俩个索引。但最终mysql选择的确实全表扫描
EXPLAIN SELECT * FROM person WHERE NAME name84059 AND create_time2023-09-05 05:00:00我们查询条件的时间从5点修改成22点在查看执行计划发现此时走了create_time索引。
EXPLAIN SELECT * FROM person WHERE NAME name84059 AND create_time2023-09-05 22:00:00同一条sql不同的查询条件mysql会根据计算的成本选择走或不走索引。这里的成本主要包括IO成本和CPU成本
IO 成本是从磁盘把数据加载到内存的成本。CPU 成本是检测数据是否满足条件和排序等 CPU 操作的成本。
我们仔细看上面俩个执行计划的rows列可以很明显的发现第二个执行计划的rows小得多也就是说要扫描的行更小CPU的成本也就会更小所以mysql选择了走索引。
在Mysql5.6及之后的版本中我们还可以使用optimizer trace功能查看每个索引、全表扫描具体的成本是多少从而知道mysql为什么选这个索引或为什么走全表扫描。
如下代码所示打开 optimizer_trace 后再执行 SQL 就可以查询 information_schema.OPTIMIZER_TRACE 表查看执行计划了最后可以关闭 optimizer_trace 功能
SET optimizer_traceenabledon;
SELECT * FROM person WHERE NAME name84059 AND create_time2023-09-05 05:00:00;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_traceenabledoff;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_time2023-09-05 05:00:00当然实际上并不建议使用FORCE INDEX因为mysql的选择往往会更正确
