脚本exlpain结果与optimizer_trace结果不一致

先说结论：

表数据量太少，使用索引的效率不如全表扫描。

表信息：

?

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

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结果（走索引）：

?

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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266

{   "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 */ }