脚本exlpain结果与optimizer_trace结果不一致
先说结论：
表数据量太少，使用索引的效率不如全表扫描。
表信息：
CREATE TABLE `w_map_cell` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `shelf_id` bigint(11) DEFAULT NULL COMMENT '货架id', `cell_no` varchar(50) DEFAULT NULL COMMENT '储位编号', `cell_name` varchar(50) DEFAULT NULL COMMENT '储位名称', `rend_x` double DEFAULT NULL COMMENT '货架坐标x', `rend_y` double DEFAULT NULL COMMENT '货架坐标y', `floor_in` int(11) DEFAULT NULL COMMENT '第几层', `span_code` varchar(50) DEFAULT NULL COMMENT '储位所在跨度', `distribution_name` varchar(32) DEFAULT NULL COMMENT '配送中心名称名称', `distribution_no` bigint(32) DEFAULT NULL COMMENT '大区编号', `ware_name` varchar(32) DEFAULT NULL COMMENT '仓库名称', `ware_no` bigint(32) NOT NULL COMMENT '仓库编号', `create_user` varchar(45) DEFAULT NULL COMMENT '创建人', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_user` varchar(45) DEFAULT NULL COMMENT '更新人', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `yn` tinyint(2) DEFAULT '0' COMMENT '删除标识', `ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳', `cell_length` double DEFAULT '0' COMMENT '储位长度', `cell_width` double DEFAULT '0' COMMENT '储位宽度', PRIMARY KEY (`id`,`ware_no`), KEY `idx_cellNo` (`cell_no`), KEY `uniq_shelfId_distributionNo_wareNo` (`shelf_id`,`distribution_no`,`ware_no`) ) ENGINE=InnoDB AUTO_INCREMENT=9652906 DEFAULT CHARSET=utf8 COMMENT='储位表' /*!50100 PARTITION BY HASH (ware_no) PARTITIONS 64 */
exlpain结果（全表扫描）：
|
explain select id, shelf_id, cell_no, cell_name, cell_width, cell_length, rend_x, rend_y, floor_in, span_code from w_map_cell where yn = 0 and shelf_id in ( 40001 , 40002 , 40003 , 40004 , 40005 , 40006 , 40007 , 40008 , 40009 , 40010 , 40011, 40012 , 40013, 40014 , 40015, 40016 , 40017 , 40018 , 40019 , 40020 , 40021 , 40022, 40023 , 40024 , 40025 , 40026 , 40027 , 40028 , 40029 , 40030, 40031 , 40032 , 40033 , 40034 , 40035, 40036, 40037 , 40038 , 40039, 40040) and distribution_no = 696 and ware_no = 52 |
|||||||||
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
|
1 |
SIMPLE |
w_map_cell |
ALL |
uniq_shelfId_distributionNo_wareNo |
|
|
|
3,295 |
Using where |
optimizer_trace结果（走索引）：
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `w_map_cell`.`id` AS `id`,`w_map_cell`.`shelf_id` AS `shelf_id`,`w_map_cell`.`cell_no` AS `cell_no`,`w_map_cell`.`cell_name` AS `cell_name`,`w_map_cell`.`cell_width` AS `cell_width`,`w_map_cell`.`cell_length` AS `cell_length`,`w_map_cell`.`rend_x` AS `rend_x`,`w_map_cell`.`rend_y` AS `rend_y`,`w_map_cell`.`floor_in` AS `floor_in`,`w_map_cell`.`span_code` AS `span_code` from `w_map_cell` where ((`w_map_cell`.`yn` = 0) and (`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)) and (`w_map_cell`.`distribution_no` = 696) and (`w_map_cell`.`ware_no` = 52)) limit 0,200"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`w_map_cell`.`yn` = 0) and (`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)) and (`w_map_cell`.`distribution_no` = 696) and (`w_map_cell`.`ware_no` = 52))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)) and multiple equal(0, `w_map_cell`.`yn`) and multiple equal(696, `w_map_cell`.`distribution_no`) and multiple equal(52, `w_map_cell`.`ware_no`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)) and multiple equal(0, `w_map_cell`.`yn`) and multiple equal(696, `w_map_cell`.`distribution_no`) and multiple equal(52, `w_map_cell`.`ware_no`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)) and multiple equal(0, `w_map_cell`.`yn`) and multiple equal(696, `w_map_cell`.`distribution_no`) and multiple equal(52, `w_map_cell`.`ware_no`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`w_map_cell`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`w_map_cell`",
"range_analysis": {
"table_scan": {
"rows": 3324,
"cost": 3990.8
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_cellNo",
"usable": false,
"cause": "not_applicable"
},
{
"index": "uniq_shelfId_distributionNo_wareNo",
"usable": true,
"key_parts": [
"shelf_id",
"distribution_no",
"ware_no"
] /* key_parts */
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "uniq_shelfId_distributionNo_wareNo",
"ranges": [
"40001 <= shelf_id <= 40001 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40002 <= shelf_id <= 40002 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40003 <= shelf_id <= 40003 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40004 <= shelf_id <= 40004 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40005 <= shelf_id <= 40005 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40006 <= shelf_id <= 40006 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40007 <= shelf_id <= 40007 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40008 <= shelf_id <= 40008 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40009 <= shelf_id <= 40009 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40010 <= shelf_id <= 40010 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40011 <= shelf_id <= 40011 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40012 <= shelf_id <= 40012 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40013 <= shelf_id <= 40013 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40014 <= shelf_id <= 40014 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40015 <= shelf_id <= 40015 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40016 <= shelf_id <= 40016 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40017 <= shelf_id <= 40017 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40018 <= shelf_id <= 40018 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40019 <= shelf_id <= 40019 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40020 <= shelf_id <= 40020 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40021 <= shelf_id <= 40021 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40022 <= shelf_id <= 40022 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40023 <= shelf_id <= 40023 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40024 <= shelf_id <= 40024 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40025 <= shelf_id <= 40025 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40026 <= shelf_id <= 40026 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40027 <= shelf_id <= 40027 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40028 <= shelf_id <= 40028 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40029 <= shelf_id <= 40029 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40030 <= shelf_id <= 40030 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40031 <= shelf_id <= 40031 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40032 <= shelf_id <= 40032 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40033 <= shelf_id <= 40033 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40034 <= shelf_id <= 40034 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40035 <= shelf_id <= 40035 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40036 <= shelf_id <= 40036 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40037 <= shelf_id <= 40037 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40038 <= shelf_id <= 40038 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40039 <= shelf_id <= 40039 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40040 <= shelf_id <= 40040 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52"
] /* ranges */,
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1680,
"cost": 2056,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "uniq_shelfId_distributionNo_wareNo",
"rows": 1680,
"ranges": [
"40001 <= shelf_id <= 40001 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40002 <= shelf_id <= 40002 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40003 <= shelf_id <= 40003 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40004 <= shelf_id <= 40004 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40005 <= shelf_id <= 40005 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40006 <= shelf_id <= 40006 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40007 <= shelf_id <= 40007 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40008 <= shelf_id <= 40008 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40009 <= shelf_id <= 40009 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40010 <= shelf_id <= 40010 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40011 <= shelf_id <= 40011 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40012 <= shelf_id <= 40012 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40013 <= shelf_id <= 40013 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40014 <= shelf_id <= 40014 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40015 <= shelf_id <= 40015 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40016 <= shelf_id <= 40016 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40017 <= shelf_id <= 40017 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40018 <= shelf_id <= 40018 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40019 <= shelf_id <= 40019 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40020 <= shelf_id <= 40020 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40021 <= shelf_id <= 40021 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40022 <= shelf_id <= 40022 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40023 <= shelf_id <= 40023 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40024 <= shelf_id <= 40024 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40025 <= shelf_id <= 40025 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40026 <= shelf_id <= 40026 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40027 <= shelf_id <= 40027 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40028 <= shelf_id <= 40028 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40029 <= shelf_id <= 40029 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40030 <= shelf_id <= 40030 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40031 <= shelf_id <= 40031 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40032 <= shelf_id <= 40032 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40033 <= shelf_id <= 40033 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40034 <= shelf_id <= 40034 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40035 <= shelf_id <= 40035 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40036 <= shelf_id <= 40036 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40037 <= shelf_id <= 40037 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40038 <= shelf_id <= 40038 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40039 <= shelf_id <= 40039 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52",
"40040 <= shelf_id <= 40040 AND 696 <= distribution_no <= 696 AND 52 <= ware_no <= 52"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1680,
"cost_for_plan": 2056,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`w_map_cell`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 1680,
"cost": 2392,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2392,
"rows_for_plan": 1680,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`w_map_cell`.`ware_no` = 52) and (`w_map_cell`.`distribution_no` = 696) and (`w_map_cell`.`yn` = 0) and (`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)))",
"attached_conditions_computation": [
{
"table": "`w_map_cell`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 200,
"row_estimate": 1680
} /* rechecking_index_usage */
}
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`w_map_cell`",
"attached": "((`w_map_cell`.`ware_no` = 52) and (`w_map_cell`.`distribution_no` = 696) and (`w_map_cell`.`yn` = 0) and (`w_map_cell`.`shelf_id` in (40001,40002,40003,40004,40005,40006,40007,40008,40009,40010,40011,40012,40013,40014,40015,40016,40017,40018,40019,40020,40021,40022,40023,40024,40025,40026,40027,40028,40029,40030,40031,40032,40033,40034,40035,40036,40037,40038,40039,40040)))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`w_map_cell`",
"access_type": "range"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
浙公网安备 33010602011771号