Server version: 5.7.18 MySQL Community Server (GPL)CREATE TABLE `p0_05` (
`aid` bigint(20) NOT NULL AUTO_INCREMENT,
`atime` datetime NOT NULL,
`num` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`aid`,`atime`),
KEY `atime` (`atime`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (dayofmonth(atime))
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM) */;insert into `p0_05` (`atime`,`num`) values('2017-05-01 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-02 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-03 10:00:00',1);
mysql> explain partitions select *
-> from `p0_05`
-> where atime BETWEEN '2017-05-01' and
-> '2017-05-02';
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | p0_05 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30 | range | atime | atime | 5 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)为什么 他查询了全部分区
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30
`aid` bigint(20) NOT NULL AUTO_INCREMENT,
`atime` datetime NOT NULL,
`num` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`aid`,`atime`),
KEY `atime` (`atime`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (dayofmonth(atime))
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM) */;insert into `p0_05` (`atime`,`num`) values('2017-05-01 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-02 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-03 10:00:00',1);
mysql> explain partitions select *
-> from `p0_05`
-> where atime BETWEEN '2017-05-01' and
-> '2017-05-02';
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | p0_05 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30 | range | atime | atime | 5 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)为什么 他查询了全部分区
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30
解决方案 »
- postgresql建表的时候需要给主键列加唯一键和唯一索引吗?
- mysql 5.0.21-log 如何选择存储引擎?
- 什么mysql客户端工具可以显示某个操作对应的sql语句呢
- mysql的关联查询问题,
- sqlserver的datediff(day,startdate,enddate)+1函数如何在mysql 找到对应
- 大侠!帮我看看--急!
- 熟悉VBB数据库的过来拿分!
- 请问在mysql中怎么建立自动增加的字段呢 , 好想在mysql中没有那么强大的工具。
- 求助 这个查询应该怎么写呢
- 更新mysql数据库爆重复数据问题,但是我根本没有这个重复问题,请高人看看
- MySQL 中 ON DUPLICATE KEY 方法问题 (求助贴)
- Mysql 标记每个客户的第几单交易
如果你用 =, 会发现没有问题,只扫描确定的分区
用 between, >,< 之类的范围比较,则会扫描所有分区
另外,就算用分区表达式做条件,也会扫描所有分区(也就是这个 dayofmonth(atime) =1 也扫描所有分区),这点似乎应该改进一下