mysql分区

参考链接：
https://www.jianshu.com/p/1cdd3e3c5b3c
http://mysql.taobao.org/monthly/2017/11/09/
https://www.cnblogs.com/duanxz/p/3825155.html
https://www.cnblogs.com/huchong/p/10231719.html
https://blog.csdn.net/orangleliu/article/details/57088338

---

表分区实践

1、建立表分区（根据 字符串 分区）

CREATE TABLE employees3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
) PARTITION BY LIST COLUMNS(fname)(
    PARTITION pNorth VALUES IN ('1'),
    PARTITION pEast VALUES IN ('2')
)

2、 添加表分区

alter table employees3 add partition (partition pWest values in ('3'))

# ALTER TABLE sale_data ADD PARTITION (PARTITION s20100402 VALUES LESS THAN (20100403));

3、删除表分区

alter table employees3 drop partition pWest;

# ALTER TABLE sale_data DROP PARTITION s20100406 ;

4、正常使用

insert into employees3 values(1,'2','xxx','1995-01-01','1995-01-01',1,1)

5、查看分区

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sale_data';

6、将已有表改为分区表

ALTER TABLE employees3 PARTITION BY LIST COLUMNS(fname)(
PARTITION pNorth VALUES IN ('1'),
PARTITION pEast VALUES IN ('2')
)

// 方式二
1、先创建分区表
2、导出原表数据
（mysqldump -u dbname -p --no-create-info dbname apdailysts  > apdailysts.sql）
3、新表名改为原表名
4、导入数据，建立普通索引

插入表数据

存储过程 example （用于插入表数据）

1、创建存储过程

DELIMITER &&
CREATE PROCEDURE load_part_tab()
  begin
  declare v int default 0;
  while v < 800
  do
  insert into part_tab
  values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
  set v = v + 1;
  end while;
end &&
DELIMITER ;

2、调用存储过程

call load_part_tab();

3、复制表数据

insert into no_part_tab select * from part_tab;

查看表分区信息

• 1 查看创建分区表语句

show create table `part_tab`;

• 2 查看表是不是分区表

show table status like '%part_tab%';

// Ceate_option = partitioned  表示是分区表

• 3 表分区详细信息

查看information_schema.partitions表。

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'part_tab'

查看关键字段

SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'employees2'

• 4 表分区使用信息

explain partitions select * from `part_tab`;

性能对比

1 耗时对比（查询即可）

select * from `part_tab`;
select * from `no_part_tab`;

2 扫描次数对比

explain PARTITIONS select * from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

explain PARTITIONS select * from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

// rows 字段具有对比意义

补充

1、合并分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));
// 将分区a,b合并为分区m
// 注意：同RANGE分区一样，只能合并相邻的几个分区，不能跨分区合并。例如不能合并a,c两个分区，只能通过合并a,b,c

2、 拆分分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b,c INTO 
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

ALTER TABLE tblist REORGANIZE PARTITION  n INTO 
    ( PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (3,9,10));

3、删除分区

ALTER TABLE tblist DROP PARTITION e;
// 注意：删除分区同时会将分区中的数据删除，同时枚举的list值也被删除，后面无法往表中插入该值的数据。

4、移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

// 注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除。