mysql RANGE 大数据分区实例

目录

创建分区

ALTER TABLE lrn_performance PARTITION BY RANGE (user_id)
(   
PARTITION lrn_performance_pmin VALUES LESS THAN (-6000000000),
PARTITION lrn_performance_p0  VALUES LESS THAN (0), 
PARTITION lrn_performance_p10 VALUES LESS THAN (10000000),
PARTITION lrn_performance_p11 VALUES LESS THAN (11000000),
PARTITION lrn_performance_p111 VALUES LESS THAN (11100000),
PARTITION lrn_performance_p112 VALUES LESS THAN (11200000),
PARTITION lrn_performance_p113 VALUES LESS THAN (11300000),
PARTITION lrn_performance_p114 VALUES LESS THAN (11400000),
PARTITION lrn_performance_p115 VALUES LESS THAN (11500000),
PARTITION lrn_performance_p116 VALUES LESS THAN (11600000),
PARTITION lrn_performance_p117 VALUES LESS THAN (11700000),
PARTITION lrn_performance_p118 VALUES LESS THAN (11800000),
PARTITION lrn_performance_p119 VALUES LESS THAN (11900000),
PARTITION lrn_performance_p12 VALUES LESS THAN (12000000),
PARTITION lrn_performance_p13 VALUES LESS THAN (13000000),
PARTITION lrn_performance_p14 VALUES LESS THAN (14000000),
PARTITION lrn_performance_p15 VALUES LESS THAN (15000000),
PARTITION lrn_performance_p16 VALUES LESS THAN (16000000),
PARTITION lrn_performance_p17 VALUES LESS THAN (17000000),
PARTITION lrn_performance_p18 VALUES LESS THAN (18000000),
PARTITION lrn_performance_p19 VALUES LESS THAN (19000000),
PARTITION lrn_performance_p20 VALUES LESS THAN (20000000),
PARTITION lrn_performance_pmax VALUES LESS THAN maxvalue
);

删除分区

 alter table lrn_performance drop partition lrn_performance_pmin;
 alter table lrn_performance drop partition lrn_performance_p0;
 alter table lrn_performance drop partition lrn_performance_p10;
 alter table lrn_performance drop partition lrn_performance_p11;
 alter table lrn_performance drop partition lrn_performance_p111;
 alter table lrn_performance drop partition lrn_performance_p112;
 alter table lrn_performance drop partition lrn_performance_p113;
 alter table lrn_performance drop partition lrn_performance_p114;
 alter table lrn_performance drop partition lrn_performance_p115;
 alter table lrn_performance drop partition lrn_performance_p116;
 alter table lrn_performance drop partition lrn_performance_p117;
 alter table lrn_performance drop partition lrn_performance_p118;
 alter table lrn_performance drop partition lrn_performance_p119;
 alter table lrn_performance drop partition lrn_performance_p12;
 alter table lrn_performance drop partition lrn_performance_p13;
 alter table lrn_performance drop partition lrn_performance_p14;
 alter table lrn_performance drop partition lrn_performance_p15;
 alter table lrn_performance drop partition lrn_performance_p16;
 alter table lrn_performance drop partition lrn_performance_p17;
 alter table lrn_performance drop partition lrn_performance_p18;
 alter table lrn_performance drop partition lrn_performance_p19;
 alter table lrn_performance drop partition lrn_performance_p20;
 alter table lrn_performance drop partition lrn_performance_pmax;

查看分区

SELECT
      partition_name part, 
      partition_expression expr, 
      partition_description descr, 
      table_rows 
FROM
      INFORMATION_SCHEMA.partitions 
WHERE
      TABLE_SCHEMA = SCHEMA() 
AND TABLE_NAME='lrn_performance' ;

查询阻塞的sql

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
SELECT * FROM information_schema.INNODB_TRX