MySQL性能优化分区之实战( 二 ) _MySQL

四：range分区MySQL有五种分区类型 range、list、hash、key、子分区，其中最常用的是range和list分区-- 查看mysql版本select version();-- 查看分区插件是否激活 partition activeshow plugins;对于低版本的MySQL，如果InnoDB引擎要想分区成功，需要在my.conf中设置innodb_file_per_table=1 设置成独立表空间独立表空间：每张表都有对应的.ibd文件innodb_file_per_table=1ange分区：给定一个连续区间的范围值进行分区，某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段，如日期范围, 连续的数字
-- 语法create table <table> ( // 字段) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1partition by range (分区字段) ( partition <分区名称> values less than (Value), partition <分区名称> values less than (Value), ... partition <分区名称> values less than maxvalue);range：表示按范围分区
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行表达式运算如year(create_time)，使用range最终的值必须是数字
【MySQL性能优化分区之实战】分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称，
less than : 表示小于
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的都会被分到这个分区
maxvalue: 表示一个最大的值
注意：range 对应的分区键值必须是数字值，可以使用range columns(分区字段) 对非int型做分区，如字符串，对于日期类型的可以使用year()、to_days()、to_seconds()等函数
create table emp_date( id int not null, separated date not null default '9999-12-31')partition by range columns(separated) ( partiontion p0 values less than ('1990-01-01'), partiontion p0 values less than ('2001-01-01'), partiontion p0 values less than ('2018-01-01'));分区可以在创建表的时候进行分区，也可以在创建表之后进行分区
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 );-- 创建分区表CREATE TABLE `tbl_user_part` ( `id` int(11) NOT NULL , `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP -- PRIMARY KEY (`id`,`age`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8PARTITION BY RANGE (age) ( PARTITION p0 VALUES LESS THAN (20), PARTITION p1 VALUES LESS THAN (40), PARTITION p2 VALUES LESS THAN (60), PARTITION p3 VALUES LESS THAN (80), PARTITION p4 VALUES LESS THAN MAXVALUE);

文章插图

在创建分区的时候经常会遇到这个错误：A PRIMARY KEY must include all columns in the table’s partitioning function 。意思是说分区的字段必须是要包含在主键当中。可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。在做分区表时，选择分区的依据字段时要谨慎，需要仔细斟酌这个字段拿来做为分区依据是否合适，这个字段加入到主键中做为复合主键是否适合。
使用range分区时表结构要么没有主键，要么分区字段必须是主键。
-- 将tbl_user_no_part表中的数据复制到tbl_user_part表中(数据量比较多，可能要等几分钟)INSERT INTO tbl_user_part SELECT * FROM tbl_user_no_part;SELECT count(*) FROM tbl_user_no_part WHERE age > 25 AND age < 30;SELECT count(*) FROM tbl_user_part WHERE age > 25 AND age < 30;

文章插图

五：list 分区设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列。list分区使用in表示一些固定的值的列表
-- 语法create table <table> ( // 字段) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) ( partition <分区名称> values IN (Value1，Value2, Value3), ... partition <分区名称> values IN (Value4, Value5),);columns分区在mysql5.5之前range分区和list分区只支持整数分区，可以通过额外的函数运算或者额外的转换从而得到一个整数。columns分区分为 range columns 和 list columns 两种，支持整数(tinyint到bigint，不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大数据类型。