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": [
        ]
      }
    }
  ]
}

现象及修改方案

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/c19ae44b40e84e08d3f9df7d4f28ded9.html