mysql创建表分区

怼烎@ 2021-11-02 17:31 488阅读 0赞

### 文章目录 ###

*  创建表分区
 *  创建批量查询测试数据的存储过程

# 创建表分区 #

注意：创建分区个数最好是质数

DROP TABLE IF EXISTS `mg_zz_feature`;
    CREATE TABLE  mg_zz_feature  (
    feature VARCHAR(50),
    fileCode VARCHAR(32),
    flag enum('0','1'),
    readFlag enum('0','1')
    )  ENGINE=InnoDB DEFAULT CHARSET=utf8
    PARTITION BY KEY(feature) PARTITIONS 5;
    --  A PRIMARY KEY must include all columns in the table's partitioning function
    -- HASH(feature): Field 'feature' is of a not allowed type for this type of partitioning  feature 必须是int类型
    call batch_insert_test(5000);
    
    -- 分区情况
    select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.`PARTITIONS` where TABLE_NAME='mg_zz_feature';
    select sum(TABLE_ROWS) from information_schema.`PARTITIONS` where TABLE_NAME='mg_zz_feature';
    
    select VERSION();
    
    -- p0
    EXPLAIN PARTITIONS select * from mg_zz_feature where feature='3749041';
    
    -- 删除所有分区
    -- alter table mg_zz_feature REMOVE PARTITIONING;
    -- 增加3个分区
    alter table mg_zz_feature ADD PARTITION PARTITIONS 5;
    -- 删除3个分区
    alter table mg_zz_feature COALESCE PARTITION 2;
    select * from mg_zz_feature  where feature='3749041' ;
    select count(1) from mg_zz_feature;
    update mg_zz_feature set readFlag='1' where fileCode='37459';
    
    show PROCESSLIST;

# 创建批量查询测试数据的存储过程 #

drop PROCEDURE if exists batch_insert_test;
    
    delimiter $$
    create PROCEDURE batch_insert_test(records integer)
    BEGIN
    DECLARE i int;
    set i =0;
    while i<records DO
    			insert into mg_zz_feature values(FLOOR(RAND()*10000000),FLOOR(RAND()*100000),'1','0');
    set i = i+1;
    end while;
    END
    $$
    
    call batch_insert_test(5000);