mysql数据到千万级别分区分表

原创

后端从入门到精通 2022-11-01 18:53:17 博主文章分类：后端 ©著作权

©著作权归作者所有：来自51CTO博客作者后端从入门到精通的原创作品，请联系作者获取转载授权，否则将追究法律责任

当数据量太大时，提高查询效率，因为只用扫描指定分区，删除分区效率高。

查询的时候加上select * from user partition(区间名);

有range分区：用partition按自己分配的区间分区。

list分区：用partition按list集合分区。

CREATE TABLE provinces (
       id INT,
       province_name VARCHAR(20),
       province_id INT,
       num_people INT
     )DEFAULT CHARSET = utf8       
     PARTITION BY LIST(province_id) (
      PARTITION p0 VALUES IN (0),
      PARTITION p1 VALUES IN (1)
     );

hash分区：

CREATE TABLE tbhash (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4
;

ALTER TABLE tbhash ADD INDEX ix_store_id(store_id);

INSERT INTO tbhash() VALUES(1,100),(1,101),(2,102),(3,103),(4,104);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tbhash';

hash分区只对整数进行hash，非整形的需要函数或者表达式来计算，所以不推荐太过复杂的函数来计算，会降低效率，给定分区，然后平均分给每个分区。

线性HASH(LINEAR HASH)：与常规hash分区不同的是，partition by linear hash(store_id) partitions 4;

优点是增加，修改，合并拆分效率更高，但常规hash的数据分布更均匀。

hash分区和range分区不同的是:range分区能直接用drop删除某一个分区，hash只能合并分区，

alert table ... coalesce partition num 此处num代表合并的分区数量。

alert table ... add partition partitions num 此处num代表新增的分区。

移除分区用:alert table ... remove partitions。与drop不同，drop会连数据一起删除，remove仅仅是移除分区。

key分区：partition by linear key () partitions 4;

子分区：假如一家员工的数据存储到数据库，按员工日期年份分区之后又再次按天分区，就可以用到子分区。