MySql性能优化---分区

分区介绍

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象碧昂城一些小对象。

MySQL分区即可以对数据进行分区也可以对索引进行分区。

分区类型

• range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
• list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区
• hash分区：基于给定的分区个数，把数据分配到不同的分区
• key分区：类似于hash分区

注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。

实战

CREATE TABLE `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20) DEFAULT NULL,
   `sex` tinyint(1) DEFAULT NULL,
   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

通过show variables like ‘%datadir%’;命令查看mysql的data存放目录，可以查看是否分区成功。切换到目录中可以看到分区后的表如下

.....
user#p#p0.ibd
user#p#p1.ibd
user.frm
....

在创建分区的时候经常会遇到这个错误：A PRIMARY KEY must include all columns in the table’s partitioning function。意思是说分区的字段必须是要包含在主键当中。解决方法是先把之前的主键删除，再把原来的主键和需要分区的字段都设置为主键。

ALTER TABLE user DROP PRIMARY KEY, ADD PRIMARY KEY(id, sex);

range分区

若你是对一个连续区间的范围值进行分区的字段，这个字段满足特定值就分配到该区间，以这样的出发点分区那就用range分区吧。

在创建表时进行分区：

-- 语法
create table <table> (
	// 字段
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
  partition <分区名称> values less than (Value),
  partition <分区名称> values less than (Value),
  ...
  partition <分区名称> values less than maxvalue
);

注意：range对应的分区建值必须时数值。

• range：代表分区方式
• less than：表示小于
• Value：表示小于某个具体值，比如 less than (10)，那么分区字段的值小于10的分在该分区。

比如user表中的create_time就是这样的字段。

create table `user` (
	// 和上面相同
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
  partition p0 values less than (1000000),
  partition p1 values less than (2000000),
  partition p2 values less than (3000000),
  partition p3 values less than (4000000),
  partition p5 values less than maxvalue
);

也可以在创建表之后进行过分区：

alter table <table> partition by RANGE(id) (
	PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN MAXVALUE 
);

list分区

若要分区的字段是一个有限值，是固定的，比如枚举类型的。就适合适用list分区。user表中的sex（性别）适合适用list分区

-- 语法
create table <table> (
	// 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
  partition <分区名称> values IN (Value1，Value2, Value3),
  ...
  partition <分区名称> values IN (Value4, Value5),
);

如何查看一张表的分区的情况

Mysql中的SQLyog工具有sql ddl信息，点击查看会出现如下信息：

/*DDL 信息*/------------

CREATE TABLE `st_address_customer` (
 ...
) ENGINE=InnoDB AUTO_INCREMENT=171233 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='审补表'
/*!50100 PARTITION BY LIST (TASK_TYPE)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3,4,5,6,7,8,9) ENGINE = InnoDB) */

知道了表的创建语句，表分区的情况不就一目了然了嘛。

如果想知道表分区存储的数据数目的情况，可以使用下面的语句：

select partition_name part,partition_expression expr,partition_description descr,table_rows  from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='mx_domain'

还有更多的分区形式大家可以看这篇博客，我这篇博客也基本是缩减，照猫画虎。强力推荐一波：

MySQL性能优化(六)：分区