最近在做Mysql分区测试的时候发现一个问题,希望能够得到大家的解答,在此希望高手帮忙。谢谢首先建立Mysql分区表:
CREATE TABLE `businesslog` (
`UserId` VARCHAR(300) COLLATE utf8_bin NOT NULL,
`ServiceId` VARCHAR(300) COLLATE utf8_bin NOT NULL,
`CreatedDateTime` DATETIME NOT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY RANGE (YEAR(`CreatedDateTime`))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MYISAM,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = MYISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MYISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MYISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MYISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MYISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MYISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MYISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MYISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MYISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MYISAM,
PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = MYISAM)然后向表中插入1000多万行记录
数据分布如下:
SELECT YEAR(CreatedDateTime),COUNT(1) FROM businesslog
GROUP BY YEAR(CreatedDateTime)
--------------------------------
YEAR(CreatedDateTime) count(1)
1999 867940
2000 867940
2001 867940
2002 867940
2003 867940
2004 867940
2005 867940
2006 867940
2007 867940
2008 867940
2009 867940
2010 867940
2011 867940
现在执行执行计划:
EXPLAIN PARTITIONS
SELECT COUNT(1) FROM businesslog
WHERE CreatedDateTime>=DATE'2001-01-01' AND CreatedDateTime<=DATE'2001-12-31'查看结果
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: businesslog
partitions: p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11283220
Extra: Using where
1 row in set (0.03 sec)可以看到查询对分区p2进行了扫描,和想象的结果一样,可是发现rows: 11283220显示的是表所有的行数,想象中的应该是
867940啊,怎么会出现这样的问题?希望高手解答。谢谢!
CREATE TABLE `businesslog` (
`UserId` VARCHAR(300) COLLATE utf8_bin NOT NULL,
`ServiceId` VARCHAR(300) COLLATE utf8_bin NOT NULL,
`CreatedDateTime` DATETIME NOT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY RANGE (YEAR(`CreatedDateTime`))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MYISAM,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = MYISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MYISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MYISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MYISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MYISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MYISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MYISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MYISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MYISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MYISAM,
PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = MYISAM)然后向表中插入1000多万行记录
数据分布如下:
SELECT YEAR(CreatedDateTime),COUNT(1) FROM businesslog
GROUP BY YEAR(CreatedDateTime)
--------------------------------
YEAR(CreatedDateTime) count(1)
1999 867940
2000 867940
2001 867940
2002 867940
2003 867940
2004 867940
2005 867940
2006 867940
2007 867940
2008 867940
2009 867940
2010 867940
2011 867940
现在执行执行计划:
EXPLAIN PARTITIONS
SELECT COUNT(1) FROM businesslog
WHERE CreatedDateTime>=DATE'2001-01-01' AND CreatedDateTime<=DATE'2001-12-31'查看结果
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: businesslog
partitions: p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11283220
Extra: Using where
1 row in set (0.03 sec)可以看到查询对分区p2进行了扫描,和想象的结果一样,可是发现rows: 11283220显示的是表所有的行数,想象中的应该是
867940啊,怎么会出现这样的问题?希望高手解答。谢谢!
解决方案 »
- 服务器死机怎么办
- MySQL的my.ini中的log会记录哪些内容?
- 求com.mysql.jdbc.MysqlDataTruncation: Data truncation: 错误的解决方案
- mysql 自定义函数中 select 语句赋值
- 如何重新规划分区?
- 怎样修复mysql表---Incorrect information in file: '.\mydata\a.frm'
- 高手帮忙:mysql ERROR 1130
- 高分求助:还是mysql乱码,头疼,各位大哥帮小弟瞧瞧吧~
- mysql如何添加某个用户,并且设置其只能访问某一个特定数据库
- sql问题,行转列
- 数据库设计是时候要不要指定字段长度
- 问一个MYSQL计划任务的怪问题
但是你后面的那个查询分析就不正常了。