设为首页 加入收藏

TOP

MySQL ORDER BY主键id加LIMIT限制走错索引(一)
2018-10-28 16:08:21 】 浏览:98
Tags:MySQL ORDER 主键 LIMIT 限制 索引

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='商品日营业数据表';


-- 批量查询耗时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


-- 开启优化器跟踪
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`.`prod
编程开发网

首页 上一页 1 2 3 4 5 6 7 下一页 尾页 1/17/17
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇表数据量影响MySQL索引选择 下一篇MySQL锁原理浅谈

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

最新文章

热门文章

C 语言

C++基础

windows编程基础

linux编程基础

C/C++面试题目