MYSQL分区功能，该功能只在5.1中增加，所有要从以前的5.0升级至5.1.
刚开始编译，
./configure --prefix=/opt/mysql --localstatedir=/mysql/data
结果安装完以后登入MSYQL显示不支持分区
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | NO |
+——————-+——-+
后来再仔细看了下帮助，发现MYSQL 5.1.23默认好像不安装分区，这个功能只是当作一个插件
再重新编译
./configure --prefix=/opt/mysql --localstatedir=/mysql/data --with-plugins=partition
make && make install
再查看发现以支持分区功能
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
刚开始编译，
./configure --prefix=/opt/mysql --localstatedir=/mysql/data
结果安装完以后登入MSYQL显示不支持分区
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | NO |
+——————-+——-+
后来再仔细看了下帮助，发现MYSQL 5.1.23默认好像不安装分区，这个功能只是当作一个插件
再重新编译
./configure --prefix=/opt/mysql --localstatedir=/mysql/data --with-plugins=partition
make && make install
再查看发现以支持分区功能
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
0
对一个大表分区的sql执行语句： //50万条记录为一个分区，针对pid进行分区。
ALTER TABLE cdb_posts partition by range (pid) (
PARTITION p0 VALUES LESS THAN (500000),
PARTITION p1 VALUES LESS THAN (1000000),
PARTITION p2 VALUES LESS THAN (1500000),
PARTITION p3 VALUES LESS THAN (2000000),
PARTITION p4 VALUES LESS THAN (2500000),
PARTITION p5 VALUES LESS THAN (3000000),
PARTITION p6 VALUES LESS THAN (3500000),
PARTITION p7 VALUES LESS THAN (4000000),
PARTITION p8 VALUES LESS THAN (4500000),
PARTITION p9 VALUES LESS THAN (5000000),
PARTITION p10 VALUES LESS THAN (5500000),
PARTITION p11 VALUES LESS THAN (6000000),
PARTITION p12 VALUES LESS THAN (6500000),
PARTITION p13 VALUES LESS THAN (7000000),
PARTITION p14 VALUES LESS THAN (7500000),
PARTITION p15 VALUES LESS THAN (8000000),
PARTITION p16 VALUES LESS THAN (8500000),
PARTITION p17 VALUES LESS THAN (9000000),
PARTITION p18 VALUES LESS THAN (9500000),
PARTITION p19 VALUES LESS THAN (10000000),
PARTITION p20 VALUES LESS THAN (10500000),
PARTITION p21 VALUES LESS THAN (11000000),
PARTITION p22 VALUES LESS THAN (11500000),
PARTITION p23 VALUES LESS THAN (12000000),
PARTITION p24 VALUES LESS THAN (12500000),
PARTITION p25 VALUES LESS THAN (13000000),
PARTITION p26 VALUES LESS THAN (13500000),
PARTITION p27 VALUES LESS THAN (14000000),
PARTITION p28 VALUES LESS THAN (14500000),
PARTITION p29 VALUES LESS THAN (15000000),
PARTITION p30 VALUES LESS THAN (15500000),
PARTITION p31 VALUES LESS THAN (16000000),
PARTITION p32 VALUES LESS THAN (16500000),
PARTITION p33 VALUES LESS THAN (17000000),
PARTITION p34 VALUES LESS THAN (17500000),
PARTITION p35 VALUES LESS THAN (18000000),
PARTITION p36 VALUES LESS THAN (18500000),
PARTITION p37 VALUES LESS THAN (19000000),
PARTITION p38 VALUES LESS THAN (19500000),
PARTITION p39 VALUES LESS THAN (20000000),
PARTITION p40 VALUES LESS THAN (20500000),
PARTITION p41 VALUES LESS THAN (21000000),
PARTITION p42 VALUES LESS THAN (21500000),
PARTITION p43 VALUES LESS THAN (22000000),
PARTITION p44 VALUES LESS THAN (22500000),
PARTITION p45 VALUES LESS THAN (23000000),
PARTITION p46 VALUES LESS THAN (23500000),
PARTITION p47 VALUES LESS THAN (24000000),
PARTITION p48 VALUES LESS THAN (24500000),
PARTITION p49 VALUES LESS THAN (25000000),
PARTITION p50 VALUES LESS THAN (25500000),
PARTITION p51 VALUES LESS THAN (26000000),
PARTITION p52 VALUES LESS THAN (26500000),
PARTITION p53 VALUES LESS THAN (27000000),
PARTITION p54 VALUES LESS THAN (27500000),
PARTITION p55 VALUES LESS THAN (28000000),
PARTITION p56 VALUES LESS THAN (28500000),
PARTITION p57 VALUES LESS THAN (29000000),
PARTITION p58 VALUES LESS THAN (29500000),
PARTITION p59 VALUES LESS THAN (30000000),
PARTITION p60 VALUES LESS THAN MAXVALUE
)；
ALTER TABLE cdb_posts partition by range (pid) (
PARTITION p0 VALUES LESS THAN (500000),
PARTITION p1 VALUES LESS THAN (1000000),
PARTITION p2 VALUES LESS THAN (1500000),
PARTITION p3 VALUES LESS THAN (2000000),
PARTITION p4 VALUES LESS THAN (2500000),
PARTITION p5 VALUES LESS THAN (3000000),
PARTITION p6 VALUES LESS THAN (3500000),
PARTITION p7 VALUES LESS THAN (4000000),
PARTITION p8 VALUES LESS THAN (4500000),
PARTITION p9 VALUES LESS THAN (5000000),
PARTITION p10 VALUES LESS THAN (5500000),
PARTITION p11 VALUES LESS THAN (6000000),
PARTITION p12 VALUES LESS THAN (6500000),
PARTITION p13 VALUES LESS THAN (7000000),
PARTITION p14 VALUES LESS THAN (7500000),
PARTITION p15 VALUES LESS THAN (8000000),
PARTITION p16 VALUES LESS THAN (8500000),
PARTITION p17 VALUES LESS THAN (9000000),
PARTITION p18 VALUES LESS THAN (9500000),
PARTITION p19 VALUES LESS THAN (10000000),
PARTITION p20 VALUES LESS THAN (10500000),
PARTITION p21 VALUES LESS THAN (11000000),
PARTITION p22 VALUES LESS THAN (11500000),
PARTITION p23 VALUES LESS THAN (12000000),
PARTITION p24 VALUES LESS THAN (12500000),
PARTITION p25 VALUES LESS THAN (13000000),
PARTITION p26 VALUES LESS THAN (13500000),
PARTITION p27 VALUES LESS THAN (14000000),
PARTITION p28 VALUES LESS THAN (14500000),
PARTITION p29 VALUES LESS THAN (15000000),
PARTITION p30 VALUES LESS THAN (15500000),
PARTITION p31 VALUES LESS THAN (16000000),
PARTITION p32 VALUES LESS THAN (16500000),
PARTITION p33 VALUES LESS THAN (17000000),
PARTITION p34 VALUES LESS THAN (17500000),
PARTITION p35 VALUES LESS THAN (18000000),
PARTITION p36 VALUES LESS THAN (18500000),
PARTITION p37 VALUES LESS THAN (19000000),
PARTITION p38 VALUES LESS THAN (19500000),
PARTITION p39 VALUES LESS THAN (20000000),
PARTITION p40 VALUES LESS THAN (20500000),
PARTITION p41 VALUES LESS THAN (21000000),
PARTITION p42 VALUES LESS THAN (21500000),
PARTITION p43 VALUES LESS THAN (22000000),
PARTITION p44 VALUES LESS THAN (22500000),
PARTITION p45 VALUES LESS THAN (23000000),
PARTITION p46 VALUES LESS THAN (23500000),
PARTITION p47 VALUES LESS THAN (24000000),
PARTITION p48 VALUES LESS THAN (24500000),
PARTITION p49 VALUES LESS THAN (25000000),
PARTITION p50 VALUES LESS THAN (25500000),
PARTITION p51 VALUES LESS THAN (26000000),
PARTITION p52 VALUES LESS THAN (26500000),
PARTITION p53 VALUES LESS THAN (27000000),
PARTITION p54 VALUES LESS THAN (27500000),
PARTITION p55 VALUES LESS THAN (28000000),
PARTITION p56 VALUES LESS THAN (28500000),
PARTITION p57 VALUES LESS THAN (29000000),
PARTITION p58 VALUES LESS THAN (29500000),
PARTITION p59 VALUES LESS THAN (30000000),
PARTITION p60 VALUES LESS THAN MAXVALUE
)；
0
alter table cdb_posts1 partition by range (id) (
PARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN (4000000),
PARTITION p4 VALUES LESS THAN (5000000),
PARTITION p5 VALUES LESS THAN (6000000),
PARTITION p6 VALUES LESS THAN (7000000),
PARTITION p7 VALUES LESS THAN (8000000),
PARTITION p8 VALUES LESS THAN (9000000),
PARTITION p9 VALUES LESS THAN (10000000),
PARTITION p10 VALUES LESS THAN (11000000),
PARTITION p11 VALUES LESS THAN (12000000),
PARTITION p12 VALUES LESS THAN (13000000),
PARTITION p13 VALUES LESS THAN (14000000),
PARTITION p14 VALUES LESS THAN (15000000),
PARTITION p15 VALUES LESS THAN (16000000),
PARTITION p16 VALUES LESS THAN (17000000),
PARTITION p17 VALUES LESS THAN (18000000),
PARTITION p18 VALUES LESS THAN (19000000),
PARTITION p19 VALUES LESS THAN (20000000),
PARTITION p20 VALUES LESS THAN (21000000),
PARTITION p21 VALUES LESS THAN (22000000),
PARTITION p22 VALUES LESS THAN (23000000),
PARTITION p23 VALUES LESS THAN (24000000),
PARTITION p24 VALUES LESS THAN (25000000),
PARTITION p25 VALUES LESS THAN (26000000),
PARTITION p26 VALUES LESS THAN (27000000),
PARTITION p27 VALUES LESS THAN (28000000),
PARTITION p28 VALUES LESS THAN MAXVALUE );
PARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN (4000000),
PARTITION p4 VALUES LESS THAN (5000000),
PARTITION p5 VALUES LESS THAN (6000000),
PARTITION p6 VALUES LESS THAN (7000000),
PARTITION p7 VALUES LESS THAN (8000000),
PARTITION p8 VALUES LESS THAN (9000000),
PARTITION p9 VALUES LESS THAN (10000000),
PARTITION p10 VALUES LESS THAN (11000000),
PARTITION p11 VALUES LESS THAN (12000000),
PARTITION p12 VALUES LESS THAN (13000000),
PARTITION p13 VALUES LESS THAN (14000000),
PARTITION p14 VALUES LESS THAN (15000000),
PARTITION p15 VALUES LESS THAN (16000000),
PARTITION p16 VALUES LESS THAN (17000000),
PARTITION p17 VALUES LESS THAN (18000000),
PARTITION p18 VALUES LESS THAN (19000000),
PARTITION p19 VALUES LESS THAN (20000000),
PARTITION p20 VALUES LESS THAN (21000000),
PARTITION p21 VALUES LESS THAN (22000000),
PARTITION p22 VALUES LESS THAN (23000000),
PARTITION p23 VALUES LESS THAN (24000000),
PARTITION p24 VALUES LESS THAN (25000000),
PARTITION p25 VALUES LESS THAN (26000000),
PARTITION p26 VALUES LESS THAN (27000000),
PARTITION p27 VALUES LESS THAN (28000000),
PARTITION p28 VALUES LESS THAN MAXVALUE );
0
alter table cdb_posts1 partition by range (id) ( PARTITION p0 VALUES LESS THAN (1000000), PARTITION p1 VALUES LESS THAN (2000000), PARTITION p2 VALUES LESS THAN (3000000), PARTITION p3 VALUES LESS THAN (4000000), PARTITION p4 VALUES LESS THAN (5000000), PARTITION p5 VALUES LESS THAN (6000000), PARTITION p6 VALUES LESS THAN (7000000), PARTITION p7 VALUES LESS THAN (8000000), PARTITION p8 VALUES LESS THAN (9000000), PARTITION p9 VALUES LESS THAN (10000000), PARTITION p10 VALUES LESS THAN (11000000), PARTITION p11 VALUES LESS THAN (12000000), PARTITION p12 VALUES LESS THAN (13000000), PARTITION p13 VALUES LESS THAN (14000000), PARTITION p14 VALUES LESS THAN (15000000), PARTITION p15 VALUES LESS THAN (16000000), PARTITION p16 VALUES LESS THAN (17000000), PARTITION p17 VALUES LESS THAN (18000000), PARTITION p18 VALUES LESS THAN (19000000), PARTITION p19 VALUES LESS THAN (20000000), PARTITION p20 VALUES LESS THAN (21000000), PARTITION p21 VALUES LESS THAN (22000000), PARTITION p22 VALUES LESS THAN (23000000), PARTITION p23 VALUES LESS THAN (24000000), PARTITION p24 VALUES LESS THAN (25000000), PARTITION p25 VALUES LESS THAN (26000000), PARTITION p26 VALUES LESS THAN (27000000), PARTITION p27 VALUES LESS THAN (28000000), PARTITION p28 VALUES LESS THAN MAXVALUE );
0
alter table cdb_posts1 partition by range (id) ( PARTITION p0 VALUES LESS THAN (500000), PARTITION p1 VALUES LESS THAN (1000000), PARTITION p2 VALUES LESS THAN (1500000), PARTITION p3 VALUES LESS THAN (2000000), PARTITION p4 VALUES LESS THAN (2500000), PARTITION p5 VALUES LESS THAN (3000000), PARTITION p6 VALUES LESS THAN (3500000), PARTITION p7 VALUES LESS THAN (4000000), PARTITION p8 VALUES LESS THAN (4500000), PARTITION p9 VALUES LESS THAN (5000000), PARTITION p10 VALUES LESS THAN (5500000), PARTITION p11 VALUES LESS THAN (6000000), PARTITION p12 VALUES LESS THAN (6500000), PARTITION p13 VALUES LESS THAN (7000000), PARTITION p14 VALUES LESS THAN (7500000), PARTITION p15 VALUES LESS THAN (8000000), PARTITION p16 VALUES LESS THAN (8500000), PARTITION p17 VALUES LESS THAN (9000000), PARTITION p18 VALUES LESS THAN (9500000), PARTITION p19 VALUES LESS THAN (10000000), PARTITION p20 VALUES LESS THAN (10500000), PARTITION p21 VALUES LESS THAN (11000000), PARTITION p22 VALUES LESS THAN (11500000), PARTITION p23 VALUES LESS THAN (12000000), PARTITION p24 VALUES LESS THAN (12500000), PARTITION p25 VALUES LESS THAN (13000000), PARTITION p26 VALUES LESS THAN (13500000), PARTITION p27 VALUES LESS THAN (14000000), PARTITION p28 VALUES LESS THAN (14500000), PARTITION p29 VALUES LESS THAN (15000000), PARTITION p30 VALUES LESS THAN (15500000), PARTITION p31 VALUES LESS THAN (16000000), PARTITION p32 VALUES LESS THAN (16500000), PARTITION p33 VALUES LESS THAN (17000000), PARTITION p34 VALUES LESS THAN (17500000), PARTITION p35 VALUES LESS THAN (18000000), PARTITION p36 VALUES LESS THAN (18500000), PARTITION p37 VALUES LESS THAN (19000000), PARTITION p38 VALUES LESS THAN (19500000), PARTITION p39 VALUES LESS THAN (20000000), PARTITION p40 VALUES LESS THAN (20500000), PARTITION p41 VALUES LESS THAN (21000000), PARTITION p42 VALUES LESS THAN (21500000), PARTITION p43 VALUES LESS THAN (22000000), PARTITION p44 VALUES LESS THAN (22500000), PARTITION p45 VALUES LESS THAN (23000000), PARTITION p46 VALUES LESS THAN (23500000), PARTITION p47 VALUES LESS THAN (24000000), PARTITION p48 VALUES LESS THAN (24500000), PARTITION p49 VALUES LESS THAN (25000000), PARTITION p50 VALUES LESS THAN (25500000), PARTITION p51 VALUES LESS THAN (26000000), PARTITION p52 VALUES LESS THAN (26500000), PARTITION p53 VALUES LESS THAN (27000000), PARTITION p54 VALUES LESS THAN MAXVALUE );
编辑回复