MySQL是如何计算查询成本的

Question

我有这个查询：

explain format=json
select count(*)
from info2 -- force index(idx_02)
where order_code = '1027' and issue = '20220720'
and state = 0 ;

实际上选择idx_02比idx_01好，但是MySQL选择id_01。

看看这两个指标的成本，idx_01(129979.27(比idx_02(287195.76(少，但我不明白MySQL是如何计算idx_01成本的？

MySQL如何计算read_cost和eval_cost成本？

附表架构和解释结果。

表模式：

CREATE TABLE `info2` 
(
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`shop_code` varchar(20) NOT NULL DEFAULT '',
`user_id` int unsigned NOT NULL DEFAULT '0' ,
`user_name` varchar(30) NOT NULL DEFAULT '' ,
`record_code` char(20) NOT NULL DEFAULT '' ,
`order_code` varchar(10) NOT NULL DEFAULT '',
`issue` char(12) NOT NULL DEFAULT '' ,
`order_number` varchar(700) NOT NULL DEFAULT '',
`order_amount` decimal(18,4) NOT NULL ,
`award` varchar(20) NOT NULL DEFAULT '' ,
`order_count` int unsigned NOT NULL ,
`state` tinyint unsigned NOT NULL DEFAULT '0',
`order_unit` decimal(3,2) NOT NULL ,
`order_id` bigint unsigned NOT NULL ,
`source_id` tinyint unsigned NOT NULL ,
`is_test` bit(1) NOT NULL DEFAULT b'0' ,
`created_date` date NOT NULL DEFAULT '1970-01-01' ,
`ip` varchar(45) NOT NULL DEFAULT '' ,
`gmt_created` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`gmt_modified` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
KEY `idx_01` (`issue`),
KEY `idx_02` (`order_code`,`issue`,`state`,`order_number`),
KEY `idx_03` (`user_id`) ,
KEY `idx_04` (`order_id`) ,
KEY `idx_05` (`created_date`,`is_test`,`order_code`,`state`) 
) ENGINE=InnoDB AUTO_INCREMENT=982131317 DEFAULT CHARSET=utf8 ;

查询解释不强制索引

{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "129979.27"
},
"table": {
"table_name": "info2",
"access_type": "ref",
"possible_keys": [
"idx_01",
"idx_02"
],
"key": "idx_01",
"used_key_parts": [
"issue"
],
"key_length": "36",
"ref": [
"const"
],
"rows_examined_per_scan": 1028525,
"rows_produced_per_join": 10285,
"filtered": "1.00",
"cost_info": {
"read_cost": "27126.77",
"eval_cost": "1028.53",
"prefix_cost": "129979.27",
"data_read_per_join": "32M"
},
"used_columns": [
"order_code",
"issue",
"state"
],
"attached_condition": "((`jerry`.`info2`.`state` = 0) and (`jerry`.`info2`.`order_code` = '1027'))"
}
}
}

使用力指数进行查询解释

{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "287195.76"
},
"table": {
"table_name": "info2",
"access_type": "ref",
"possible_keys": [
"idx_02"
],
"key": "idx_02",
"used_key_parts": [
"order_code",
"issue",
"state"
],
"key_length": "69",
"ref": [
"const",
"const",
"const"
],
"rows_examined_per_scan": 820642,
"rows_produced_per_join": 820642,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "205131.56",
"eval_cost": "82064.20",
"prefix_cost": "287195.76",
"data_read_per_join": "2G"
},
"used_columns": [
"order_code",
"issue",
"state"
]
}
}
}

Answer 1

请在bugs.mysql.com上提交一个错误。idx_02是一个"覆盖"；索引，并且应该优先于idx_01。

解决方法是替换

INDEX(issue)

当您只需要issue:时，可以继续使用覆盖索引

INDEX(issue, order_code, state)

请注意，我省略了庞大的第4纵队。