- 论坛徽章:
- 0
|
mysql 5.1.31版本 分区问题
有分区但仍是全表扫描的问题，谢谢帮忙!问题如下：
本人根据需求，一个月大概是3，4百万的数据。按天分表，分了31个区（定义了day_flag tinyint)，采用的是range(dat_flag)方式。建表一路ok后。
第一次查询,
按分区查找正常。如下：
mysql> explain partitions select * from frontr where day_flag<14\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: frontr
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1708593
Extra: Using where
接着在查，出现故障：
explain partitions select * from frontr where day_flag<4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: frontr
partitions: p1,p2,p3,p4
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1708593
Extra: Using where
1 row in set (0.00 sec)
explain partitions select * from frontr where day_flag>4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: frontr
partitions: 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,p31,p32
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3435283
Extra: Using where
分区如下：
PARTITION BY range (`day_flag`)
(
PARTITION p1 values less than (1),
PARTITION p2 values less than (2),
PARTITION p3 values less than (3),
PARTITION p4 values less than (4),
PARTITION p5 values less than (5),
PARTITION p6 values less than (6),
PARTITION p7 values less than (7),
PARTITION p8 values less than (,
PARTITION p9 values less than (9),
PARTITION p10 values less than (10),
PARTITION p11 values less than (11),
PARTITION p12 values less than (12),
PARTITION p13 values less than (13),
PARTITION p14 values less than (14),
PARTITION p15 values less than (15),
PARTITION p16 values less than (16),
PARTITION p17 values less than (17),
PARTITION p18 values less than (1,
PARTITION p19 values less than (19),
PARTITION p20 values less than (20),
PARTITION p21 values less than (21),
PARTITION p22 values less than (22),
PARTITION p23 values less than (23),
PARTITION p24 values less than (24),
PARTITION p25 values less than (25),
PARTITION p26 values less than (26),
PARTITION p27 values less than (27),
PARTITION p28 values less than (2,
PARTITION p29 values less than (29),
PARTITION p30 values less than (30),
PARTITION p31 values less than (31),
PARTITION p32 values less than (32)
);
请问是怎么个情况。有分区但扫描的是全表？多谢！ |
|
免费注册
发表于 2009-02-27 16:19
