(1)在这个区间范围内的数据一共才180万条,表全部的数据(表现在是710万行的数据)
mysql> select count(*) from errand_log_dt where update_time <= '20120729235959';
+----------+
| count(*) |
+----------+
| 1812836 |
+----------+(2)但是explain的时候却会扫描表全部的数据
mysql> explain PARTITIONS select count(*) from errand_log_dt where update_time <= '20120729235959';
+----+-------------+---------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | errand_log_dt | __0730 | index | time | time | 8 | NULL | 7169119 | Using where; Using index |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+---------+--------------------------+另:我用的是range方式的分区
partition by range (to_days(update_time))
(
PARTITION __0728 VALUES LESS THAN (to_days('2012-07-28')),
PARTITION __0729 VALUES LESS THAN (to_days('2012-07-29')),~~~~~~求各位大侠帮忙看看,解释下,这样的情况是不是不正常
mysql> select count(*) from errand_log_dt where update_time <= '20120729235959';
+----------+
| count(*) |
+----------+
| 1812836 |
+----------+(2)但是explain的时候却会扫描表全部的数据
mysql> explain PARTITIONS select count(*) from errand_log_dt where update_time <= '20120729235959';
+----+-------------+---------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | errand_log_dt | __0730 | index | time | time | 8 | NULL | 7169119 | Using where; Using index |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+---------+--------------------------+另:我用的是range方式的分区
partition by range (to_days(update_time))
(
PARTITION __0728 VALUES LESS THAN (to_days('2012-07-28')),
PARTITION __0729 VALUES LESS THAN (to_days('2012-07-29')),~~~~~~求各位大侠帮忙看看,解释下,这样的情况是不是不正常
'截至2012-08-09 18:47:09 用户结帖率0.00% 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖
索引就一个update_time上的
另外贴出 你的 show create table , show index from 以供分析。
否则别人只能猜。
`id` int(11) NOT NULL DEFAULT '0',
`update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`action` varchar(32) NOT NULL DEFAULT '0',
`account` varchar(32) NOT NULL DEFAULT '',
`gid` varchar(32) NOT NULL DEFAULT '',
KEY `time` (`update_time`),
KEY `gid` (`gid`)
) ENGINE=innodb
/*!50100 PARTITION BY RANGE (to_days(update_time))
(PARTITION __0728 VALUES LESS THAN (735077),
PARTITION __0729 VALUES LESS THAN (735078),
PARTITION __0730 VALUES LESS THAN (735079),
PARTITION __0731 VALUES LESS THAN (735080),
PARTITION __0801 VALUES LESS THAN (735081),
PARTITION __0802 VALUES LESS THAN (735082),
PARTITION __0803 VALUES LESS THAN (735083),
PARTITION __max VALUES LESS THAN MAXVALUE) */
+----+-------------+---------------+----------------------+-------+---------------+------+---------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+----------------------+-------+---------------+------+---------+------+----------+--------------------------+
| 1 | SIMPLE | errand_log_dt | __0728,__0729,__0730 | index | time | time | 8 | NULL | 7188872| Using where; Using index |
+----+-------------+---------------+----------------------+-------+---------------+------+---------+------+----------+--------------------------+mysql> explain PARTITIONS select count(*) from errand_log_dt where to_days(update_time) <= to_days('2012-07-29 23:59:59');
+----+-------------+---------------+--------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
| 1 | SIMPLE | errand_log_dt | __0728,__0729,__0730,__0731,__0801,__0802,__0803,__max | index | NULL | time | 8 | NULL | 7188872 | Using where; Using index |
+----+-------------+---------------+--------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
+----+-------------+---------------+----------------------+-------+---------------+------+---------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+----------------------+-------+---------------+------+---------+------+----------+--------------------------+
| 1 | SIMPLE | errand_log_dt | __0728,__0729,__0730 | index | time | time | 8 | NULL | 7188872| Using where; Using index |
+----+-------------+---------------+----------------------+-------+---------------+------+---------+------+----------+--------------------------+这个不是已经使用分区了吗?
partitions= __0730 说明只查询了这个分区。
partitions= __0730 才1812836万的数据,但是为什么在扫描时会搜索表中全部700万行的数据呢