MySQL 5.6,5.7的优化器对于count(*)的处理方式

   最近看了很多阿里同学的MySQL文章,阿里内核同学的文章一言不合就上代码,不光让我们看到了结果,还能有代码可读,如果碰到了类似的问题,这样的解读确实是很难得的。

   今天做了一个小的测试,发现MySQL 5.7中对于count(*)的处理好像有点霸道,没想象中那么好。

   为了对比,我找了一套5.6的环境。

总体而言5.6的环境中对于count(*)的处理可塑性很强,很随和,你让我怎么查我就怎么查。初始数据为100万。

+———-+
| count(*) |
+———-+
|  1000000 |
+———-+

建表的语句如下:

>show create table test/G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mrrx` (`a`,`b`),
  KEY `xx` (`c`)

) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)一直以来MySQL中count(*)的用法都是不被提倡,或者说是恶名远扬,这一点让很多学习Oracle的同学很不理解,其实他们是身在福中不知福。

这样的一个count(*)的查询,在5.6中的效果是这样的,估算的时候默认是走了索引xx

>explain select count(*) from test/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: xx
      key_len: 5
          ref: NULL
         rows: 998396
        Extra: Using index
1 row in set (0.01 sec)   
如果我们强制走mrrx索引,优化器说也行,于是就走了mrrx的索引,估算的数据情况和上面有一些小的差别。
>explain select count(*) from test force index(mrrx)/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: mrrx
      key_len: 10
          ref: NULL
         rows: 947698
        Extra: Using index
1 row in set (0.00 sec)或者我们显式指定就要xx索引了,优化器说好,然后估算得到的行数和第一个差别很小。
>explain select count(*) from test force index(xx)/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: xx
      key_len: 5
          ref: NULL
         rows: 947698
        Extra: Using index
1 row in set (0.00 sec)如果换一种姿势,如果指定索引列c,指定一个条件,再来看看,就会看到前后的结果差别就很大了。
>explain select count(*) from test where c > 0/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: xx
          key: xx
      key_len: 5
          ref: NULL
         rows: 473849
        Extra: Using where; Using index
1 row in set (0.00 sec)这么看来,5.6里面的一个硬伤还是对于统计信息这块的评估差别较大,没有了统计信息还是有很大的局限性,不过优化器还是很随和的。

我们看看5.7的表现

同样的语句和数据量,在5.7中明显做了过滤处理,

> explain select count(*) from test/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.02 sec)

这表示在优化器阶段已经被优化了。

而接下来同样的语句也都是同样的处理方式。

> explain select count(*) from test force index(mrrx)/G
> explain select count(*) from test force index(xx)/G
        Extra: Select tables optimized away

而如果我们还是像之前一样给定索引列c一个过滤条件,优化器就一下子变得温和起来。很明显这个执行的效果要好很多。

> explain select count(*) from test where c > 0/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: range
possible_keys: xx
          key: xx
      key_len: 5
          ref: NULL
         rows: 498949
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.02 sec)

从某种程度来说,5.7这样的处理也算是一种变相的退步啦。

这一点,阿里的同学已经开了case.

https://bugs.mysql.com/bug.php?id=81854

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » MySQL 5.6,5.7的优化器对于count(*)的处理方式

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址