MySQL优化器中一个Count和覆盖索引的问题

    前天在微薄上发了个优化器的问题，从评论来看，还是需要简单说明一下。

 

 

现象说明

       其实这里主要要说明的是一个优化器还需要改进的地方。

 

优化器会根据where条件和select_list里面的字段决定在使用一个索引(sta)后，是否需要回表—回到聚集索引取数据。

 

基本的做法是：在确定了一个索引后，将select_list和where中出现的所有字段都拿来判断一下，如果字段都存在于sta索引中，则可以使用覆盖索引。

 

第一个explan可以用上覆盖索引(Using Index), 是因为select_list里面只有count(*)，而count(*)在语法解析阶段就被特殊处理，不作为特殊字段。

 

第二个字段在现在的实现中，因为gmt不是sta索引的一部分（sta索引定义上只有sta一个字段，算上聚集索引结构，就是sta,auci）。所以最后判定为不能使用覆盖索引。

 

性能差别

         表中放入500w数据以后，这两个查询的时间相差25倍(0.19s vs  5s)。

 

存在改进

         其实我说这个“不科学”，是因为第二个语句居然不能使用覆盖索引。按照count(gmt)的语义，是计算gmt不为NULL的所有行的数目。但是表定义中，分分明已经说明了gmt为not null。那么就可以转成count(*)了！

     目前因为count、sum、count(distinct)这些操作的处理方法被揉在一起，因此没有特别分开。

 

应用警惕

         好在按照正常的习惯，需要计算总数时，最多是count(auci)或者count(*)， 不会故意去count一个别的字段。当时若使用的是MyISAM，就需要小心了。

 

         从上面的分析可以推测得到，如果这是个MyISAM表，还是一样的where条件，count(*)是可以使用覆盖索引的，但是count(auci)就不行了。

评论

1 楼 zhoujy 2013-05-20  

root@192.168.200.202 : test 11:44:37>show create table my_data\G;
*************************** 1. row ***************************
       Table: my_data
Create Table: CREATE TABLE `my_data` (
  `auci` int(11) NOT NULL,
  `tit` varchar(256) DEFAULT NULL,
  `gmt` datetime DEFAULT NULL,
  `sta` datetime DEFAULT NULL,
  `pic` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`auci`),
  KEY `sta` (`sta`),
  KEY `idx_gmt` (`gmt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

root@192.168.200.202 : test 11:47:26>explain select count(*) from my_data where sta>='2011-05-20';
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | my_data | index | sta           | sta  | 6       | NULL |    2 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

root@192.168.200.202 : test 11:47:43>explain select count(gmt) from my_data where sta>='2011-05-20';
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | my_data | range | sta           | sta  | 6       | NULL |    2 | Using index condition |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

root@192.168.200.202 : test 11:47:52>insert into my_data values(3,'asdasdasda',NULL,now(),'SDFSDF');
Query OK, 1 row affected (0.00 sec)

root@192.168.200.202 : test 11:48:02>explain select count(gmt) from my_data where sta>='2011-05-20';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | my_data | ALL  | sta           | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)