mysql server是5
问题很简单,语句如下SELECT count(*) as count FROM `data_changed` WHERE `datetime`>='2012-02-25 21:34:02' AND `datetime`<='2012-02-25 23:36:02';
mysql console下运行得到5.4W条数据,耗时0.25秒假如我把后边的一个时间改成'2012-03-25 23:36:02 (扩大了搜索范围)
mysql console下运行得到799W条数据,耗时22.67秒其中datetime字段已经添加了索引。
但为什么查询一个月的数据那么慢呢?当然我肯定字段datetime的索引效果的,毕竟这个表的文件大小和表索引的文件分别为3.3G和1.9G了,没索引肯定第一条语句也非常慢才对。在实际应用的查询中,还有其他子查询条件(也索引过)。加上其他条件实在忍受不了啦。
这个问题通过google好几天了,还是找不到结果,网上多数说是没建索引之类的问题而已,这些信息对于我没有帮助。
所以我看会不会是mysql的极限了?要真是极限能推荐一下使用什么数据库吗?
问题很简单,语句如下SELECT count(*) as count FROM `data_changed` WHERE `datetime`>='2012-02-25 21:34:02' AND `datetime`<='2012-02-25 23:36:02';
mysql console下运行得到5.4W条数据,耗时0.25秒假如我把后边的一个时间改成'2012-03-25 23:36:02 (扩大了搜索范围)
mysql console下运行得到799W条数据,耗时22.67秒其中datetime字段已经添加了索引。
但为什么查询一个月的数据那么慢呢?当然我肯定字段datetime的索引效果的,毕竟这个表的文件大小和表索引的文件分别为3.3G和1.9G了,没索引肯定第一条语句也非常慢才对。在实际应用的查询中,还有其他子查询条件(也索引过)。加上其他条件实在忍受不了啦。
这个问题通过google好几天了,还是找不到结果,网上多数说是没建索引之类的问题而已,这些信息对于我没有帮助。
所以我看会不会是mysql的极限了?要真是极限能推荐一下使用什么数据库吗?
explain SELECT count(*) as count FROM `data_changed` WHERE `datetime`>='2012-02-25 21:34:02' AND `datetime`<='2012-02-25 23:36:02';
show index from `data_changed`以供分析。
这个表当前的数据量大概为2.5千万条,799W大概是1/3的数据量吧;
mysql> select count(*) as count from `data_changed` where 1;
+----------+
| count |
+----------+
| 24955210 |
+----------+
1 row in set (0.00 sec)mysql> explain SELECT count(*) as count FROM `data_changed` WHERE `datetime`>='2012-02-25 21:34:02' AND `datetime`<='2012-02-25 23:36:02';
+----+-------------+---------------+-------+---------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra |
+----+-------------+---------------+-------+---------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | data_changed | range | datetime | datetime | 8| NULL | 53893 | Using where; Using index |
+----+-------------+---------------+-------+---------------+----------+---------+------+-------+--------------------------+
1 row in set (0.13 sec)mysql> show index from `data_changed`;
+---------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| data_changed | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | |
| data_changed | 1 | redirector | 1 | redirector | A | NULL | NULL | NULL | | BTREE | |
| data_changed | 1 | type | 1 | type | A | NULL | NULL | NULL | | BTREE | |
| data_changed | 1 | sub_type | 1 | sub_type | A | NULL | NULL | NULL | | BTREE | |
| data_changed | 1 | datetime | 1 | datetime | A | NULL | NULL | NULL | | BTREE | |
+---------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.08 sec)
不过2楼的意思是不是我这样的查询失去意义了?应该找估计更少数据的字段作为索引并作为最先查找的条件呢?
不过我还是想先让它“减肥”一下。
1.要先把server升级为5.1。
2.分区工作可能要花大概20个小时左右。
3.我再另外再从新考虑我的实际查询需求来优化了——其实就是把预期返回最小量的字段进行查询对吧?(当然前提要建立索引)。
晚些报告情况。
1.现在已经符合我的预期了,虽然还是要等几十秒的查询,其他插入操作也得到几十倍的速度提升(目前足够也不打算去优化了)。
2.总结是因为自己对数据库的索引不够了解,我以为并且建单键索引后引擎会联合查询,但事实不是,唯有根据查询语句建立联合索引才是我想要的。
3.最后自己的经验是根据实际查询需要,尽量将可能得到最少返回行数的查询键放到左边并建立索引。