SQL优化

[zhangzhenting]

/** 创建表结构 *********************************************************/
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a1` varchar(20) DEFAULT NULL,
  `a2` varchar(20) DEFAULT NULL,
  `a3` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `aaa` (`a1`,`a2`,`a3`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

/** 插入数据，可能要等一段时间 ****************************************/
BEGIN
   declare i int ;
   set i = 1;
   while ( i <= 500000 ) do
      insert into a (a1, a2,a3) values ('aaa',concat('test',i), adddate('2010-01-01',(rand(i)*36520) mod 3652));
      set i = i+1;
   end while;
   while ( i <= 1000000 ) do
      insert into a (a1, a2,a3) values ('bbb',concat('test',i-500000), adddate('2010-01-01',(rand(i)*36520) mod 3652));
      set i = i+1;
   end while;
END

看下面两条SQL:
1: select count(1) from (select 1 from a where a3 between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'  group by a1,a2) tmp;
2: select count(distinct a1,a2) from a where a3 between '2010-01-01 00:00:00' and '2010-12-31 23:59:59';

SQL1执行时间：2.313s
SQL2执行时间：3.062s

请问：
这两条SQL都用到了索引 aaa，怎么后面的比前面的SQL更耗费时间呢？
有没有其他方法更高效的获取按a1,a2分组之后的个数？

[ACMAIN_CHM]

创建一个a3的索引。

[wwwwb]

有没有其他方法更高效的获取按a1,a2分组之后的个数？
没有
在上个帖子就说过，GROUP BY是最快的

[ACMAIN_CHM]

和你的索引有关，创建 create index bbb on a(a3); 后你可以看到相反的情况。 如果需要可以进一步创建 (a3,a1,a2) 的索引。

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from (select 1 from a where a3 between '2010-01-01 00:00:
00' and '2010-12-31 23:59:59'  group by a1,a2) tmp;
+----------+
| count(1) |
+----------+
|    99813 |
+----------+
1 row in set (2.08 sec)

mysql> select count(distinct a1,a2) from a where a3 between '2010-01-01 00:00:00
' and '2010-12-31 23:59:59';
+-----------------------+
| count(distinct a1,a2) |
+-----------------------+
|                 99813 |
+-----------------------+
1 row in set (2.55 sec)

mysql>
mysql> create index bbb on a(a3);
Query OK, 1000000 rows affected (23.03 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> select count(1) from (select 1 from a where a3 between '2010-01-01 00:00:
00' and '2010-12-31 23:59:59'  group by a1,a2) tmp;
+----------+
| count(1) |
+----------+
|    99813 |
+----------+
1 row in set (1.50 sec)

mysql> select count(distinct a1,a2) from a where a3 between '2010-01-01 00:00:00
' and '2010-12-31 23:59:59';
+-----------------------+
| count(distinct a1,a2) |
+-----------------------+
|                 99813 |
+-----------------------+
1 row in set (1.17 sec)

mysql>

[zhangzhenting]

嗯， 加了a3索引， 确实看到效果了。两条SQL都使用 a3索引

SQL1执行时间：1.656s
SQL2执行时间：1.496s

只是第一条SQL出现了 Using where; Using temporary; Using filesort。

wwwwb，如果使用group by，可以对SQL1进行优化吗？

[ACMAIN_CHM]

为什么一定要GROUP BY呢？ GROUP BY 并不快啊。

[zhangzhenting]

ACMAIN_CHM
看你的执行结果， 电脑挺牛的啊。我还是双核电脑，2G内存， 加个索引就卡死了。
既然group by 不快， 就不用了

[ACMAIN_CHM]

我的T60 , CPU T7200 @ 2.00GHz 2GB memory

[zhangzhenting]

结贴了 谢谢

[zhangzhenting]

使用 （a3,a1,a2）索引
sql 1: 0.875s Using where; Using index; Using temporary; Using filesort
sql 2: 0.735s Using where; Using index