mysql分区实例（MyISAM引擎有效）

淩亂°似流年 2022-03-25 11:16 176阅读 0赞

1、RANGE分区

a.创建表

DROP TABLE IF EXISTS \`admin\_log\`;  
CREATE TABLE \`admin\_log\` (  
  \`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,  
  \`name\` CHAR(20) NOT NULL DEFAULT '' COMMENT '名字',  
  PRIMARY KEY (\`id\`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PARTITION BY RANGE (id) (  
PARTITION p0 VALUES LESS THAN (5),  
PARTITION p1 VALUES LESS THAN (10),  
PARTITION p2 VALUES LESS THAN (15),  
PARTITION p3 VALUES LESS THAN (20),  
PARTITION p4 VALUES LESS THAN (25),  
PARTITION p5 VALUES LESS THAN MAXVALUE  
);

\--注释：admin\_log按id分区，p0区存放数据id<5，p1是5≤id<10，依次类推，id≥25的全部存放在p5中

b.新增数据同常表一致

INSERT INTO admin\_log  (name) VALUES ('test1'),('test2'),('test3'),('test4'),('test5'),('test6'),('test7'),('test8'),('test9'),('test10'),('test11'),('test12'),('test13'),('test14'),('test15');

c.验证新增的数据是否对应存入分区

SELECT partition\_name,partition\_expression,partition\_description,table\_rows  FROM information\_schema.PARTITIONS  WHERE table\_schema = SCHEMA() AND table\_name='admin\_log';

![20190120170438331.png][]

d.删除分区

ALTER TABLE admin\_log DROP PARTITION p0;

删除成功后，实例中id<5的数据都将被删除

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70][]

2、HASH分区

a.创建表

DROP TABLE IF EXISTS \`admin\_log1\`;  
CREATE TABLE \`admin\_log1\` (  
  \`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,  
  \`name\` CHAR(20) NOT NULL DEFAULT '' COMMENT '名字',  
  PRIMARY KEY (\`id\`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PARTITION BY HASH (id) PARTITIONS 20 ;

\--注释：id通过hash计算，分成20个区

b.新增数据同常表一致

INSERT INTO admin\_log1  (name) VALUES ('test1'),('test2'),('test3'),('test4'),('test5'),('test6'),('test7'),('test8'),('test9'),('test10'),('test11'),('test12'),('test13'),('test14'),('test15');

c.验证新增的数据是否对应存入分区

SELECT partition\_name,partition\_expression,partition\_description,table\_rows  FROM information\_schema.PARTITIONS  WHERE table\_schema = SCHEMA() AND table\_name='admin\_log1';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70 1][]

d.删除分区（减去3个分区）

ALTER TABLE admin\_log1 COALESCE PARTITION 3;  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70 2][]

e.新增分区（增加4个分区）

ALTER TABLE admin\_log1 add PARTITION partitions 4;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70 3][]

注：hash不能像range那样单个分区删除 只能使用减去或新增合并分区，在新增或减去时，数据会重新计算进行分区

[20190120170438331.png]: /images/20220325/9cff86c986374ad2a9e5d7d328a72138.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70]: /images/20220325/33d19816f57e44118f30b30b7ce34b36.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70 1]: /images/20220325/11ea0e9e49ce49038d8c5971f69430c1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70 2]: /images/20220325/ea14993a137847aea6e3e95de42c5690.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjdG1laQ_size_16_color_FFFFFF_t_70 3]: /images/20220325/cefc6b4627e04a92b9bc2906292ce0b9.png