MySQL ORDER BY主键id加LIMIT限制走错索引

背景及现象

• report_product_sales_data表数据量2800万；
• 经测试，在当前数据量情况下，order by主键id，limit最大到49的时候可以用到索引report_product_sales_data_hq_code_orgz_id_index，大于49时就走PRIMARY主键索引。

表结构

CREATE TABLE `report_product_sales_data` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',   `hq_code` char(16) COLLATE utf8_unicode_ci NOT NULL COMMENT '公司编码',   `product_id` int(10) unsigned NOT NULL COMMENT '商品ID',   `orgz_id` int(10) unsigned NOT NULL COMMENT '组织ID',   `sales_num` double(16,3) NOT NULL COMMENT '销售数量',   `report_date` date NOT NULL COMMENT '报表日期',   `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态: 0.未日结，1.已日结',   `created_at` timestamp NULL DEFAULT NULL,   `updated_at` timestamp NULL DEFAULT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `report_product_sales_data_unique` (`hq_code`,`report_date`,`orgz_id`,`product_id`),   KEY `report_product_sales_data_hq_code_orgz_id_index` (`hq_code`,`orgz_id`,`report_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='商品日营业数据表';

Explain命令查看执行计划

-- 批量查询耗时154ms select product_id, sales_num, report_date from `report_product_sales_data` where `hq_code` = '000030' and `orgz_id` = 229 and `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_date` > '2018-05-11' order by id desc limit 320; -- explain结果如下 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; Using filesort-- 批量查询耗时397ms select product_id, sales_num, report_date from `report_product_sales_data` where `hq_code` = '000030' and `orgz_id` = 229 and `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_date` > '2018-05-11' order by `id` desc limit 10; -- explain结果如下 id  select_type table  type    possible_keys  key key_len ref rows    Extra 1  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优化过程

-- 开启优化器跟踪 set session optimizer_trace='enabled=on'; -- 在执行完查询语句后，在执行以下的select语句可以查看具体的优化器执行过程 select * from information_schema.optimizer_trace;-- 对于这条走了预期report_product_sales_data_hq_code_orgz_id_index索引的查询，我们看下优化器的执行过程 select product_id, sales_num, report_date from `report_product_sales_data` where `hq_code` = '000030' and `orgz_id` = 229 and `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_date` > '2018-05-11' order by id desc limit 320;-- 看下trace部分 {   "steps": [     {       "join_preparation": {         "select#": 1,         "steps": [           {             "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"           }         ]       }     },     {       "join_optimization": {         "select#": 1,         "steps": [           {             "condition_processing": {               "condition": "WHERE",               "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'))",               "steps": [                 {                   "transformation": "equality_propagation",                   "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`))"                 },                 {                   "transformation": "constant_propagation",                   "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`))"                 },                 {                   "transformation": "trivial_condition_removal",                   "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`))"                 }               ]             }           },           {             "table_dependencies": [               {                 "table": "`report_product_sales_data`",                 "row_may_be_null": false,                 "map_bit": 0,                 "depends_on_map_bits": [                 ]               }             ]           },           {             "ref_optimizer_key_uses": [               {                 "table": "`report_product_sales_data`",                 "field": "hq_code",                 "equals": "'000030'",                 "null_rejecting": false               },               {                 "table": "`report_product_sales_data`",                 "field": "hq_code",                 "equals": "'000030'",                 "null_rejecting": false               },               {                 "table": "`report_product_sales_data`",                 "field": "orgz_id",                 "equals": "229",                 "null_rejecting": false               }             ]           },           {             "rows_estimation": [               {                 "table": "`report_product_sales_data`",                 "range_analysis": {                   "table_scan": {                     "rows": 28276082,                     "cost": 6.14e6                   },                   "potential_range_indices": [                     {                       "index": "PRIMARY",                       "usable": false,                       "cause": "not_applicable"                     },                     {                       "index": "report_product_sales_data_unique",                       "usable": true,                       "key_parts": [                         "hq_code",                         "report_date",                         "orgz_id",                         "product_id"                       ]                     },                     {                       "index": "report_product_sales_data_hq_code_orgz_id_index",                       "usable": true,                       "key_parts": [                         "hq_code",                         "orgz_id",                         "report_date",                         "id"                       ]                     }                   ],                   "setup_range_conditions": [                   ],                   "group_index_range": {                     "chosen": false,                     "cause": "not_group_by_or_distinct"                   },                   "analyzing_range_alternatives": {                     "range_scan_alternatives": [                       {                         "index": "report_product_sales_data_unique",                         "ranges": [                           "000030 <= hq_code <= 000030 AND 2018-05-11 < report_date"                         ],                         "index_dives_for_eq_ranges": true,                         "rowid_ordered": false,                         "using_mrr": false,                         "index_only": false,                         "rows": 1848962,                         "cost": 2.22e6,                         "chosen": true                       },                       {                         "index": "report_product_sales_data_hq_code_orgz_id_index",                         "ranges": [                           "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date"                         ],                         "index_dives_for_eq_ranges": true,                         "rowid_ordered": false,                         "using_mrr": false,                         "index_only": false,                         "rows": 37088,                         "cost": 44507,                         "chosen": true                       }                     ],                     "analyzing_roworder_intersect": {                       "usable": false,                       "cause": "too_few_roworder_scans"                     }                   },                   "chosen_range_access_summary": {                     "range_access_plan": {                       "type": "range_scan",                       "index": "report_product_sales_data_hq_code_orgz_id_index",                       "rows": 37088,                       "ranges": [                         "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date"                       ]                     },                     "rows_for_plan": 37088,                     "cost_for_plan": 44507,                     "chosen": true                   }                 }               }             ]           },           {             "considered_execution_plans": [               {                 "plan_prefix": [                 ],                 "table": "`report_product_sales_data`",                 "best_access_path": {                   "considered_access_paths": [                     {                       "access_type": "ref",                       "index": "report_product_sales_data_unique",                       "rows": 1.85e6,                       "cost": 1.82e6,                       "chosen": true                     },                     {                     //可以看到选择report_product_sales_data_hq_code_orgz_id_index这个索引时cost最小                       "access_type": "ref",                       "index": "report_product_sales_data_hq_code_orgz_id_index",                       "rows": 37088,                       "cost": 44506,                       "chosen": true                     },                     {                       "access_type": "range",                       "rows": 27816,                       "cost": 51924,                       "chosen": false                     }                   ]                 },                 "cost_for_plan": 44506,                 "rows_for_plan": 37088,                 "chosen": true               }             ]           },           {             "attaching_conditions_to_tables": {               "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'))",               "attached_conditions_computation": [                 {                   "access_type_changed": {                     "table": "`report_product_sales_data`",                     "index": "report_product_sales_data_hq_code_orgz_id_index",                     "old_type": "ref",                     "new_type": "range",                     "cause": "uses_more_keyparts"                   }                 }               ],               "attached_conditions_summary": [                 {                   "table": "`report_product_sales_data`",                   "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'))"                 }               ]             }           },           {             "clause_processing": {               "clause": "ORDER BY",               "original_clause": "`report_product_sales_data`.`id` desc",               "items": [                 {                   "item": "`report_product_sales_data`.`id`"                 }               ],               "resulting_clause_is_simple": true,               "resulting_clause": "`report_product_sales_data`.`id` desc"             }           },           {             "refine_plan": [               {                 "table": "`report_product_sales_data`",                 "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'))",                 "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))",                 "access_type": "range"               }             ]           },           {             "reconsidering_access_paths_for_index_ordering": {             //到了order by id这边时，MySQL也没有改变执行计划，还是选择了report_product_sales_data_hq_code_orgz_id_index索引               "clause": "ORDER BY",               "index_order_summary": {                 "table": "`report_product_sales_data`",                 "index_provides_order": false,                 "order_direction": "undefined",                 "index": "report_product_sales_data_hq_code_orgz_id_index",                 "plan_changed": false               }             }           }         ]       }     },     {       "join_execution": {         "select#": 1,         "steps": [           {             "filesort_information": [               {                 "direction": "desc",                 "table": "`report_product_sales_data`",                 "field": "id"               }             ],             "filesort_priority_queue_optimization": {               "limit": 320,               "rows_estimate": 61044633,               "row_size": 76,               "memory_available": 262144,               "chosen": true             },             "filesort_execution": [             ],             "filesort_summary": {               "rows": 321,               "examined_rows": 15768,               "number_of_tmp_files": 0,               "sort_buffer_size": 26964,               "sort_mode": "<sort_key, additional_fields>"             }           }         ]       }     }   ] }-- 对于这条走了非预期PRIMARY主键索引的查询，我们看下优化器的执行过程 select product_id, sales_num, report_date from `report_product_sales_data` where `hq_code` = '000030' and `orgz_id` = 229 and `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_date` > '2018-05-11' order by id desc limit 10;-- 看下trace部分 {   "steps": [     {       "join_preparation": {         "select#": 1,         "steps": [           {             "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"           }         ]       }     },     {       "join_optimization": {         "select#": 1,         "steps": [           {             "condition_processing": {               "condition": "WHERE",               "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'))",               "steps": [                 {                   "transformation": "equality_propagation",                   "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`))"                 },                 {                   "transformation": "constant_propagation",                   "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`))"                 },                 {                   "transformation": "trivial_condition_removal",                   "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`))"                 }               ]             }           },           {             "table_dependencies": [               {                 "table": "`report_product_sales_data`",                 "row_may_be_null": false,                 "map_bit": 0,                 "depends_on_map_bits": [                 ]               }             ]           },           {             "ref_optimizer_key_uses": [               {                 "table": "`report_product_sales_data`",                 "field": "hq_code",                 "equals": "'000030'",                 "null_rejecting": false               },               {                 "table": "`report_product_sales_data`",                 "field": "hq_code",                 "equals": "'000030'",                 "null_rejecting": false               },               {                 "table": "`report_product_sales_data`",                 "field": "orgz_id",                 "equals": "229",                 "null_rejecting": false               }             ]           },           {             "rows_estimation": [               {                 "table": "`report_product_sales_data`",                 "range_analysis": {                   "table_scan": {                     "rows": 28276082,                     "cost": 6.14e6                   },                   "potential_range_indices": [                     {                       "index": "PRIMARY",                       "usable": false,                       "cause": "not_applicable"                     },                     {                       "index": "report_product_sales_data_unique",                       "usable": true,                       "key_parts": [                         "hq_code",                         "report_date",                         "orgz_id",                         "product_id"                       ]                     },                     {                       "index": "report_product_sales_data_hq_code_orgz_id_index",                       "usable": true,                       "key_parts": [                         "hq_code",                         "orgz_id",                         "report_date",                         "id"                       ]                     }                   ],                   "setup_range_conditions": [                   ],                   "group_index_range": {                     "chosen": false,                     "cause": "not_group_by_or_distinct"                   },                   "analyzing_range_alternatives": {                     "range_scan_alternatives": [                       {                         "index": "report_product_sales_data_unique",                         "ranges": [                           "000030 <= hq_code <= 000030 AND 2018-05-11 < report_date"                         ],                         "index_dives_for_eq_ranges": true,                         "rowid_ordered": false,                         "using_mrr": false,                         "index_only": false,                         "rows": 1848962,                         "cost": 2.22e6,                         "chosen": true                       },                       {                         "index": "report_product_sales_data_hq_code_orgz_id_index",                         "ranges": [                           "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date"                         ],                         "index_dives_for_eq_ranges": true,                         "rowid_ordered": false,                         "using_mrr": false,                         "index_only": false,                         "rows": 37088,                         "cost": 44507,                         "chosen": true                       }                     ],                     "analyzing_roworder_intersect": {                       "usable": false,                       "cause": "too_few_roworder_scans"                     }                   },                   "chosen_range_access_summary": {                     "range_access_plan": {                       "type": "range_scan",                       "index": "report_product_sales_data_hq_code_orgz_id_index",                       "rows": 37088,                       "ranges": [                         "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date"                       ]                     },                     "rows_for_plan": 37088,                     "cost_for_plan": 44507,                     "chosen": true                   }                 }               }             ]           },           {             "considered_execution_plans": [               {                 "plan_prefix": [                 ],                 "table": "`report_product_sales_data`",                 "best_access_path": {                   "considered_access_paths": [                     {                       "access_type": "ref",                       "index": "report_product_sales_data_unique",                       "rows": 1.85e6,                       "cost": 1.82e6,                       "chosen": true                     },                     {                     //可以看到选择report_product_sales_data_hq_code_orgz_id_index这个索引时cost最小                       "access_type": "ref",                       "index": "report_product_sales_data_hq_code_orgz_id_index",                       "rows": 37088,                       "cost": 44506,                       "chosen": true                     },                     {                       "access_type": "range",                       "rows": 27816,                       "cost": 51924,                       "chosen": false                     }                   ]                 },                 "cost_for_plan": 44506,                 "rows_for_plan": 37088,                 "chosen": true               }             ]           },           {             "attaching_conditions_to_tables": {               "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'))",               "attached_conditions_computation": [                 {                   "access_type_changed": {                     "table": "`report_product_sales_data`",                     "index": "report_product_sales_data_hq_code_orgz_id_index",                     "old_type": "ref",                     "new_type": "range",                     "cause": "uses_more_keyparts"                   }                 }               ],               "attached_conditions_summary": [                 {                   "table": "`report_product_sales_data`",                   "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'))"                 }               ]             }           },           {             "clause_processing": {               "clause": "ORDER BY",               "original_clause": "`report_product_sales_data`.`id` desc",               "items": [                 {                   "item": "`report_product_sales_data`.`id`"                 }               ],               "resulting_clause_is_simple": true,               "resulting_clause": "`report_product_sales_data`.`id` desc"             }           },           {             "refine_plan": [               {                 "table": "`report_product_sales_data`",                 "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'))",                 "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))",                 "access_type": "range"               }             ]           },           {             "reconsidering_access_paths_for_index_ordering": {             //到了order by id这边时，MySQL改变了执行计划，选择了PRIMARY主键索引               "clause": "ORDER BY",               "index_order_summary": {                 "table": "`report_product_sales_data`",                 "index_provides_order": true,                 "order_direction": "desc",                 "disabled_pushed_condition_on_old_index": true,                 "index": "PRIMARY",                 "plan_changed": true,                 "access_type": "index_scan"               }             }           }         ]       }     },     {       "join_execution": {         "select#": 1,         "steps": [         ]       }     }   ] }

现象及修改方案

1. 通过现象可以看到MySQL在order by 主键id时，limit值的大小达到了某个临界值后，改变了执行计划，选择了主键索引，但不知道具体的规则究竟是怎样。
2. 既然如此，就不用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_data` where `hq_code` = '000030' and `orgz_id` = 229 and `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_date` > '2018-05-11' order by `report_date` desc 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，及时做排查优化。