背景及现象
- report_product_sales_data表数据量2800万；
- 经测试，在当前数据量情况下，order by主键id，limit最大到49的时候可以用到索引report_product_sales_data_hq_code_orgz_id_index，大于49时就走PRIMARY主键索引。
表结构
1CREATE TABLE `report_product_sales_data` ( 2 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', 3 `hq_code` char(16) COLLATE utf8_unicode_ci NOT NULL COMMENT '公司编码', 4 `product_id` int(10) unsigned NOT NULL COMMENT '商品ID', 5 `orgz_id` int(10) unsigned NOT NULL COMMENT '组织ID', 6 `sales_num` double(16,3) NOT NULL COMMENT '销售数量', 7 `report_date` date NOT NULL COMMENT '报表日期', 8 `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态: 0.未日结，1.已日结', 9 `created_at` timestamp NULL DEFAULT NULL, 10 `updated_at` timestamp NULL DEFAULT NULL, 11 PRIMARY KEY (`id`), 12 UNIQUE KEY `report_product_sales_data_unique` (`hq_code`,`report_date`,`orgz_id`,`product_id`), 13 KEY `report_product_sales_data_hq_code_orgz_id_index` (`hq_code`,`orgz_id`,`report_date`) 14) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='商品日营业数据表';
Explain命令查看执行计划
1-- 批量查询耗时154ms 2select product_id, sales_num, report_date from `report_product_sales_data` 3where `hq_code` = '000030' 4and `orgz_id` = 229 5and `product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938) 6and `report_date` > '2018-05-11' order by id desc 7limit 320; 8-- explain结果如下 9id select_type table type possible_keys key key_len ref rows Extra 101 SIMPLE report_product_sales_data range report_product_sales_data_unique,report_product_sales_data_hq_code_orgz_id_index report_product_sales_data_hq_code_orgz_id_index 55 NULL 37088 Using index condition; Using where; Using filesort 11 12 13-- 批量查询耗时397ms 14select product_id, sales_num, report_date from `report_product_sales_data` 15where `hq_code` = '000030' 16and `orgz_id` = 229 17and `product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938) 18and `report_date` > '2018-05-11' 19order by `id` desc limit 10; 20-- explain结果如下 21id select_type table type possible_keys key key_len ref rows Extra 221 SIMPLE report_product_sales_data index report_product_sales_data_unique,report_product_sales_data_hq_code_orgz_id_index PRIMARY 4 NULL 7624 Using where
开启优化器跟踪查看MySQL优化过程
1-- 开启优化器跟踪 2set session optimizer_trace='enabled=on'; 3-- 在执行完查询语句后，在执行以下的select语句可以查看具体的优化器执行过程 4select * from information_schema.optimizer_trace; 5 6 7-- 对于这条走了预期report_product_sales_data_hq_code_orgz_id_index索引的查询，我们看下优化器的执行过程 8select product_id, sales_num, report_date from `report_product_sales_data` 9where `hq_code` = '000030' 10and `orgz_id` = 229 11and `product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938) 12and `report_date` > '2018-05-11' order by id desc 13limit 320; 14 15 16-- 看下trace部分 17{ 18 "steps": [ 19 { 20 "join_preparation": { 21 "select#": 1, 22 "steps": [ 23 { 24 "expanded_query": "/* select#1 */ select `report_product_sales_data`.`product_id` AS `product_id`,`report_product_sales_data`.`sales_num` AS `sales_num`,`report_product_sales_data`.`report_date` AS `report_date` from `report_product_sales_data` where ((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11')) order by `report_product_sales_data`.`id` desc limit 320" 25 } 26 ] 27 } 28 }, 29 { 30 "join_optimization": { 31 "select#": 1, 32 "steps": [ 33 { 34 "condition_processing": { 35 "condition": "WHERE", 36 "original_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11'))", 37 "steps": [ 38 { 39 "transformation": "equality_propagation", 40 "resulting_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11') and multiple equal(229, `report_product_sales_data`.`orgz_id`))" 41 }, 42 { 43 "transformation": "constant_propagation", 44 "resulting_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11') and multiple equal(229, `report_product_sales_data`.`orgz_id`))" 45 }, 46 { 47 "transformation": "trivial_condition_removal", 48 "resulting_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11') and multiple equal(229, `report_product_sales_data`.`orgz_id`))" 49 } 50 ] 51 } 52 }, 53 { 54 "table_dependencies": [ 55 { 56 "table": "`report_product_sales_data`", 57 "row_may_be_null": false, 58 "map_bit": 0, 59 "depends_on_map_bits": [ 60 ] 61 } 62 ] 63 }, 64 { 65 "ref_optimizer_key_uses": [ 66 { 67 "table": "`report_product_sales_data`", 68 "field": "hq_code", 69 "equals": "'000030'", 70 "null_rejecting": false 71 }, 72 { 73 "table": "`report_product_sales_data`", 74 "field": "hq_code", 75 "equals": "'000030'", 76 "null_rejecting": false 77 }, 78 { 79 "table": "`report_product_sales_data`", 80 "field": "orgz_id", 81 "equals": "229", 82 "null_rejecting": false 83 } 84 ] 85 }, 86 { 87 "rows_estimation": [ 88 { 89 "table": "`report_product_sales_data`", 90 "range_analysis": { 91 "table_scan": { 92 "rows": 28276082, 93 "cost": 6.14e6 94 }, 95 "potential_range_indices": [ 96 { 97 "index": "PRIMARY", 98 "usable": false, 99 "cause": "not_applicable" 100 }, 101 { 102 "index": "report_product_sales_data_unique", 103 "usable": true, 104 "key_parts": [ 105 "hq_code", 106 "report_date", 107 "orgz_id", 108 "product_id" 109 ] 110 }, 111 { 112 "index": "report_product_sales_data_hq_code_orgz_id_index", 113 "usable": true, 114 "key_parts": [ 115 "hq_code", 116 "orgz_id", 117 "report_date", 118 "id" 119 ] 120 } 121 ], 122 "setup_range_conditions": [ 123 ], 124 "group_index_range": { 125 "chosen": false, 126 "cause": "not_group_by_or_distinct" 127 }, 128 "analyzing_range_alternatives": { 129 "range_scan_alternatives": [ 130 { 131 "index": "report_product_sales_data_unique", 132 "ranges": [ 133 "000030 <= hq_code <= 000030 AND 2018-05-11 < report_date" 134 ], 135 "index_dives_for_eq_ranges": true, 136 "rowid_ordered": false, 137 "using_mrr": false, 138 "index_only": false, 139 "rows": 1848962, 140 "cost": 2.22e6, 141 "chosen": true 142 }, 143 { 144 "index": "report_product_sales_data_hq_code_orgz_id_index", 145 "ranges": [ 146 "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date" 147 ], 148 "index_dives_for_eq_ranges": true, 149 "rowid_ordered": false, 150 "using_mrr": false, 151 "index_only": false, 152 "rows": 37088, 153 "cost": 44507, 154 "chosen": true 155 } 156 ], 157 "analyzing_roworder_intersect": { 158 "usable": false, 159 "cause": "too_few_roworder_scans" 160 } 161 }, 162 "chosen_range_access_summary": { 163 "range_access_plan": { 164 "type": "range_scan", 165 "index": "report_product_sales_data_hq_code_orgz_id_index", 166 "rows": 37088, 167 "ranges": [ 168 "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date" 169 ] 170 }, 171 "rows_for_plan": 37088, 172 "cost_for_plan": 44507, 173 "chosen": true 174 } 175 } 176 } 177 ] 178 }, 179 { 180 "considered_execution_plans": [ 181 { 182 "plan_prefix": [ 183 ], 184 "table": "`report_product_sales_data`", 185 "best_access_path": { 186 "considered_access_paths": [ 187 { 188 "access_type": "ref", 189 "index": "report_product_sales_data_unique", 190 "rows": 1.85e6, 191 "cost": 1.82e6, 192 "chosen": true 193 }, 194 { 195 //可以看到选择report_product_sales_data_hq_code_orgz_id_index这个索引时cost最小 196 "access_type": "ref", 197 "index": "report_product_sales_data_hq_code_orgz_id_index", 198 "rows": 37088, 199 "cost": 44506, 200 "chosen": true 201 }, 202 { 203 "access_type": "range", 204 "rows": 27816, 205 "cost": 51924, 206 "chosen": false 207 } 208 ] 209 }, 210 "cost_for_plan": 44506, 211 "rows_for_plan": 37088, 212 "chosen": true 213 } 214 ] 215 }, 216 { 217 "attaching_conditions_to_tables": { 218 "original_condition": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11'))", 219 "attached_conditions_computation": [ 220 { 221 "access_type_changed": { 222 "table": "`report_product_sales_data`", 223 "index": "report_product_sales_data_hq_code_orgz_id_index", 224 "old_type": "ref", 225 "new_type": "range", 226 "cause": "uses_more_keyparts" 227 } 228 } 229 ], 230 "attached_conditions_summary": [ 231 { 232 "table": "`report_product_sales_data`", 233 "attached": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11'))" 234 } 235 ] 236 } 237 }, 238 { 239 "clause_processing": { 240 "clause": "ORDER BY", 241 "original_clause": "`report_product_sales_data`.`id` desc", 242 "items": [ 243 { 244 "item": "`report_product_sales_data`.`id`" 245 } 246 ], 247 "resulting_clause_is_simple": true, 248 "resulting_clause": "`report_product_sales_data`.`id` desc" 249 } 250 }, 251 { 252 "refine_plan": [ 253 { 254 "table": "`report_product_sales_data`", 255 "pushed_index_condition": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`report_date` > '2018-05-11'))", 256 "table_condition_attached": "(`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938))", 257 "access_type": "range" 258 } 259 ] 260 }, 261 { 262 "reconsidering_access_paths_for_index_ordering": { 263 //到了order by id这边时，MySQL也没有改变执行计划，还是选择了report_product_sales_data_hq_code_orgz_id_index索引 264 "clause": "ORDER BY", 265 "index_order_summary": { 266 "table": "`report_product_sales_data`", 267 "index_provides_order": false, 268 "order_direction": "undefined", 269 "index": "report_product_sales_data_hq_code_orgz_id_index", 270 "plan_changed": false 271 } 272 } 273 } 274 ] 275 } 276 }, 277 { 278 "join_execution": { 279 "select#": 1, 280 "steps": [ 281 { 282 "filesort_information": [ 283 { 284 "direction": "desc", 285 "table": "`report_product_sales_data`", 286 "field": "id" 287 } 288 ], 289 "filesort_priority_queue_optimization": { 290 "limit": 320, 291 "rows_estimate": 61044633, 292 "row_size": 76, 293 "memory_available": 262144, 294 "chosen": true 295 }, 296 "filesort_execution": [ 297 ], 298 "filesort_summary": { 299 "rows": 321, 300 "examined_rows": 15768, 301 "number_of_tmp_files": 0, 302 "sort_buffer_size": 26964, 303 "sort_mode": "<sort_key, additional_fields>" 304 } 305 } 306 ] 307 } 308 } 309 ] 310} 311 312 313-- 对于这条走了非预期PRIMARY主键索引的查询，我们看下优化器的执行过程 314select product_id, sales_num, report_date from `report_product_sales_data` 315where `hq_code` = '000030' 316and `orgz_id` = 229 317and `product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938) 318and `report_date` > '2018-05-11' order by id desc 319limit 10; 320 321 322-- 看下trace部分 323{ 324 "steps": [ 325 { 326 "join_preparation": { 327 "select#": 1, 328 "steps": [ 329 { 330 "expanded_query": "/* select#1 */ select `report_product_sales_data`.`product_id` AS `product_id`,`report_product_sales_data`.`sales_num` AS `sales_num`,`report_product_sales_data`.`report_date` AS `report_date` from `report_product_sales_data` where ((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11')) order by `report_product_sales_data`.`id` desc limit 10" 331 } 332 ] 333 } 334 }, 335 { 336 "join_optimization": { 337 "select#": 1, 338 "steps": [ 339 { 340 "condition_processing": { 341 "condition": "WHERE", 342 "original_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11'))", 343 "steps": [ 344 { 345 "transformation": "equality_propagation", 346 "resulting_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11') and multiple equal(229, `report_product_sales_data`.`orgz_id`))" 347 }, 348 { 349 "transformation": "constant_propagation", 350 "resulting_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11') and multiple equal(229, `report_product_sales_data`.`orgz_id`))" 351 }, 352 { 353 "transformation": "trivial_condition_removal", 354 "resulting_condition": "((`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11') and multiple equal(229, `report_product_sales_data`.`orgz_id`))" 355 } 356 ] 357 } 358 }, 359 { 360 "table_dependencies": [ 361 { 362 "table": "`report_product_sales_data`", 363 "row_may_be_null": false, 364 "map_bit": 0, 365 "depends_on_map_bits": [ 366 ] 367 } 368 ] 369 }, 370 { 371 "ref_optimizer_key_uses": [ 372 { 373 "table": "`report_product_sales_data`", 374 "field": "hq_code", 375 "equals": "'000030'", 376 "null_rejecting": false 377 }, 378 { 379 "table": "`report_product_sales_data`", 380 "field": "hq_code", 381 "equals": "'000030'", 382 "null_rejecting": false 383 }, 384 { 385 "table": "`report_product_sales_data`", 386 "field": "orgz_id", 387 "equals": "229", 388 "null_rejecting": false 389 } 390 ] 391 }, 392 { 393 "rows_estimation": [ 394 { 395 "table": "`report_product_sales_data`", 396 "range_analysis": { 397 "table_scan": { 398 "rows": 28276082, 399 "cost": 6.14e6 400 }, 401 "potential_range_indices": [ 402 { 403 "index": "PRIMARY", 404 "usable": false, 405 "cause": "not_applicable" 406 }, 407 { 408 "index": "report_product_sales_data_unique", 409 "usable": true, 410 "key_parts": [ 411 "hq_code", 412 "report_date", 413 "orgz_id", 414 "product_id" 415 ] 416 }, 417 { 418 "index": "report_product_sales_data_hq_code_orgz_id_index", 419 "usable": true, 420 "key_parts": [ 421 "hq_code", 422 "orgz_id", 423 "report_date", 424 "id" 425 ] 426 } 427 ], 428 "setup_range_conditions": [ 429 ], 430 "group_index_range": { 431 "chosen": false, 432 "cause": "not_group_by_or_distinct" 433 }, 434 "analyzing_range_alternatives": { 435 "range_scan_alternatives": [ 436 { 437 "index": "report_product_sales_data_unique", 438 "ranges": [ 439 "000030 <= hq_code <= 000030 AND 2018-05-11 < report_date" 440 ], 441 "index_dives_for_eq_ranges": true, 442 "rowid_ordered": false, 443 "using_mrr": false, 444 "index_only": false, 445 "rows": 1848962, 446 "cost": 2.22e6, 447 "chosen": true 448 }, 449 { 450 "index": "report_product_sales_data_hq_code_orgz_id_index", 451 "ranges": [ 452 "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date" 453 ], 454 "index_dives_for_eq_ranges": true, 455 "rowid_ordered": false, 456 "using_mrr": false, 457 "index_only": false, 458 "rows": 37088, 459 "cost": 44507, 460 "chosen": true 461 } 462 ], 463 "analyzing_roworder_intersect": { 464 "usable": false, 465 "cause": "too_few_roworder_scans" 466 } 467 }, 468 "chosen_range_access_summary": { 469 "range_access_plan": { 470 "type": "range_scan", 471 "index": "report_product_sales_data_hq_code_orgz_id_index", 472 "rows": 37088, 473 "ranges": [ 474 "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date" 475 ] 476 }, 477 "rows_for_plan": 37088, 478 "cost_for_plan": 44507, 479 "chosen": true 480 } 481 } 482 } 483 ] 484 }, 485 { 486 "considered_execution_plans": [ 487 { 488 "plan_prefix": [ 489 ], 490 "table": "`report_product_sales_data`", 491 "best_access_path": { 492 "considered_access_paths": [ 493 { 494 "access_type": "ref", 495 "index": "report_product_sales_data_unique", 496 "rows": 1.85e6, 497 "cost": 1.82e6, 498 "chosen": true 499 }, 500 { 501 //可以看到选择report_product_sales_data_hq_code_orgz_id_index这个索引时cost最小 502 "access_type": "ref", 503 "index": "report_product_sales_data_hq_code_orgz_id_index", 504 "rows": 37088, 505 "cost": 44506, 506 "chosen": true 507 }, 508 { 509 "access_type": "range", 510 "rows": 27816, 511 "cost": 51924, 512 "chosen": false 513 } 514 ] 515 }, 516 "cost_for_plan": 44506, 517 "rows_for_plan": 37088, 518 "chosen": true 519 } 520 ] 521 }, 522 { 523 "attaching_conditions_to_tables": { 524 "original_condition": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11'))", 525 "attached_conditions_computation": [ 526 { 527 "access_type_changed": { 528 "table": "`report_product_sales_data`", 529 "index": "report_product_sales_data_hq_code_orgz_id_index", 530 "old_type": "ref", 531 "new_type": "range", 532 "cause": "uses_more_keyparts" 533 } 534 } 535 ], 536 "attached_conditions_summary": [ 537 { 538 "table": "`report_product_sales_data`", 539 "attached": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > '2018-05-11'))" 540 } 541 ] 542 } 543 }, 544 { 545 "clause_processing": { 546 "clause": "ORDER BY", 547 "original_clause": "`report_product_sales_data`.`id` desc", 548 "items": [ 549 { 550 "item": "`report_product_sales_data`.`id`" 551 } 552 ], 553 "resulting_clause_is_simple": true, 554 "resulting_clause": "`report_product_sales_data`.`id` desc" 555 } 556 }, 557 { 558 "refine_plan": [ 559 { 560 "table": "`report_product_sales_data`", 561 "pushed_index_condition": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = '000030') and (`report_product_sales_data`.`report_date` > '2018-05-11'))", 562 "table_condition_attached": "(`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938))", 563 "access_type": "range" 564 } 565 ] 566 }, 567 { 568 "reconsidering_access_paths_for_index_ordering": { 569 //到了order by id这边时，MySQL改变了执行计划，选择了PRIMARY主键索引 570 "clause": "ORDER BY", 571 "index_order_summary": { 572 "table": "`report_product_sales_data`", 573 "index_provides_order": true, 574 "order_direction": "desc", 575 "disabled_pushed_condition_on_old_index": true, 576 "index": "PRIMARY", 577 "plan_changed": true, 578 "access_type": "index_scan" 579 } 580 } 581 } 582 ] 583 } 584 }, 585 { 586 "join_execution": { 587 "select#": 1, 588 "steps": [ 589 ] 590 } 591 } 592 ] 593}
现象及修改方案
-
通过现象可以看到MySQL在order by 主键id时，limit值的大小达到了某个临界值后，改变了执行计划，选择了主键索引，但不知道具体的规则究竟是怎样。
-
既然如此，就不用order by id这个clause，改为order by report_date，因为id和report_date的大小是正相关的，而且可以走到report_product_sales_data_hq_code_orgz_id_index索引，换了个法子解决了当前这个问题。
explain select product_id, sales_num, report_date from
report_product_sales_datawherehq_code= '000030' andorgz_id= 229 andproduct_idin (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938) andreport_date> '2018-05-11' order byreport_datedesc limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE report_product_sales_data range report_product_sales_data_unique,report_product_sales_data_hq_code_orgz_id_index report_product_sales_data_hq_code_orgz_id_index 55 NULL 37088 Using index condition; Using where
总结
- 在order by id的情况下，MySQL由于自身的优化器选择，为了避免某些排序的消耗，可能会走非预期的PRIMARY主键索引；
- order by 和 limit 结合使用，如果where 字段，order by字段都是索引，那么有limit索引会使用order by字段所在的索引，没有limit会使用where 条件的索引；
- 对于数据量比较大，而且执行量很高的分页sql，尽可能将所有的查询字段包括在索引中，同时使用索引来消除排序；
- 多用explain查看是否使用到了最优索引；
- 利用optimizer trace查看优化器执行过程；
- 观察mysql的slow_query_log，及时做排查优化。