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，及时做排查优化。

Linux公社的RSS地址 ： https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址： https://www.linuxidc.com/Linux/2018-10/155053.htm