background and phenomena
- the amount of data in the report_product_sales_ data table is 28 million;
- it has been tested that when the, order by primary key id,limit reaches 49 under the current data volume, the PRIMARY primary key index can be used when the index report_product_sales_data_hq_code_orgz_id_index, is greater than 49.
Table structure
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 command to view the execution plan
-- trace
{
"steps": [
{
"join_preparation": {
"select-sharp": 1,
"steps": [
{
"expanded_query": "/* select-sharp1 */ 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-sharp": 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_indexcost
"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 idMySQLPRIMARY
"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-sharp": 1,
"steps": [
]
}
}
]
}question
through the phenomenon, we can see that when MySQL order by primary key id, the threshold value reaches a certain critical value (in the data environment I tested, limit > = 50 will take the primary key index), the execution plan is changed and the primary key index is selected, but I don"t know what the specific rules are. With the guidance of the Great God, why does this change of implementation plan occur?