(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')),~~~~~~求各位大侠帮忙看看,解释下,这样的情况是不是不正常
解决方案 »
- 求一个sql语句
- 美国vps服务器 windows2003 中,在cms(MYSQL数据库)后台批量操作文章的时候报错'max_allowed_packet'
- 求sql
- 关于where条件顺序
- postgreSQL 转 mysql
- mysql 几个问题
- 请问为什么我查询这个执行为空呢?
- asp网页中的sql语句中的字段数据类型的转换是用哪个函数?
- 把mysql的数据导入ACCESS中,如何将datetime型分解成date和time,分别存入ACCESS的日期时间字段?
- 我有100万条企业名称,如果用 like %企业名称% 来查询不走索引特别慢,集群的效果也不明显,大佬们有什么好办法吗?
- 锁表操作
- 如何实现返回数据表的观测总数
'截至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万行的数据呢