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

Wesley13
• 阅读 523

背景及现象

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

参考链接

mysql执行计划mysql索引mysql运维开发运维开发
点赞
收藏
评论区
推荐文章
blmius blmius
•
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
3502
0
0
Jacquelyn38 Jacquelyn38
•
2年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
1986
0
0
helloworld_34035044 helloworld_34035044
•
1年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
780
0
0
Easter79 Easter79
•
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中，有一些需要使用全局唯一ID的场景，这种时候为了防止ID冲突可以使用36位的UUID，但是UUID有一些缺点，首先他相对比较长，另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID，并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求，最初Twitter把存储系统从MySQL迁移
870
0
0
Wesley13 Wesley13
•
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
921
0
0
Wesley13 Wesley13
•
2年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景：表结构：主键（非自增）contentCode（varchar），过滤条件列为updateTime（timeStamp），已经为timestamp建立索引。搜索sql为：SELECTFROMmy_hello_tableWHEREupdat
639
0
0
Stella981 Stella981
•
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段，id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
852
0
0
Wesley13 Wesley13
•
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1115
0
0
京东云开发者 京东云开发者
•
1年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
714
0
0
京东云开发者 京东云开发者
•
6个月前
Vitess全局唯一ID生成的实现方案 | 京东云技术团队
为了标识一段数据，通常我们会为其指定一个唯一id，比如利用MySQL数据库中的自增主键。但是当数据量非常大时，仅靠数据库的自增主键是远远不够的，并且对于分布式数据库只依赖MySQL的自增id无法满足全局唯一的需求。因此，产生了多种解决方案，如UUID，Sn
303
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
19
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？