mysql分区认知

MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：

 

Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

 

Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

 

Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

 

List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。

 

Composite（复合模式） –以上模式的组合使用　

 

 

二，分区能做什么

 

   逻辑数据分割

   提高单一的写和读应用速度

   提高分区范围读查询的速度

   分割数据能够有多个不同的物理文件路径

   高效的保存历史数据

   一个表上的约束检查

   不同的主从服务器分区策略，例如master按Hash分区，slave按range分区

 

 

三，分区的限制

 

   只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列，例如create_time字段是datetime类型，可通过to_days等函数将其字段值转换成数字类型

 

   最大分区数目不能超过1024

 

   如果含有唯一索引或者主键，则分区列必须包含在“所有的唯一索引或者主键”之内

 

   不支持外键

 

   不支持全文索引（fulltext）

 

   按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多

 

四，什么时候使用分区

 

   海量数据表

 

   历史表快速的查询，可以采用ARCHIVE+PARTITION的方式。

 

   数据表索引大于服务器有效内存

 

   对于大表，特别是索引远远大于服务器有效内存时，可以不用索引，此时分区效率会更有效

 

 

五，分区具体操作：

 

情况一：

创建good_2表，并且按照the_date字段的日期按年份来分区,由于the_date字段是整形类型，所以没法使用时间函数year来做分区，但可以通过div除法运算得出年份

CREATE TABLE `good_2` (

  `id` int(11) NOT NULL DEFAULT '0',

  `name` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `the_date` int(8) DEFAULT '0'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by range ( the_date div 100 )(

partition g2_2013 less than (2014),

partition g2_2014 less than (2015),

partition g2_2015 less than (2016)

);

 

 

情况二：

创建good_2表，并且按照create_time字段按年份来分区，timestamp类型的字段无法进行分区，所以得用datetime类型, to_days函数将日期折算成天数

CREATE TABLE `good_2` (

  `id` int(11) NOT NULL DEFAULT '0',

  `name` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `create_time` datetime,

  `the_date` int(8) DEFAULT '0'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by range ( to_days(create_time) )(

partition g2_2013 values less than (to_days('2014-01-01')),

partition g2_2014 values less than (to_days('2015-01-01')),

partition g2_2015 values less than (to_days('2016-01-01'))

);

 

 

情况三：

对已有的表进行分区，使用alter table的语句，如下：

//时间字段为日期格式，例如datetime格式，分区需要用to_days

alter table good3 partition by range( to_days(create_time) )(

partition g2_2013 values less than (to_days('2014-01-01')),

partition g2_2014 values less than (to_days('2015-01-01')),

partition g2_2015 values less than (to_days('2016-01-01')),

partition g21_3 values less than MAXVALUE

);

 

//时间字段为时间戳，例如timestamp格式，需要使用UNIX_TIMESTAMP方法，而不是to_days
alter table `good_20180902_1` partition by range(UNIX_TIMESTAMP(`create_time`))(
partition g21_1 values less than (UNIX_TIMESTAMP('2018-07-01 00:00:00')),
partition g21_2 values less than (UNIX_TIMESTAMP('2018-08-01 00:00:00')),

partition g21_3 values less than MAXVALUE
);

 

 

情况四：

删除表中的指定分区，如下：

ALTER TABLE 表名 DROP PARTITION 分区名;  例如：alter table good_2 drop partition g2_2015;

 

 

情况五：

追加分区，如下：

alter table good_2 add partition (   

partition g2_2015 values less than (to_days('2016-01-01'))

partition g2_2016 values less than (to_days('2017-01-01'))

);  

 

 

情况六：

如果表中存在主键字段(primary key),需要把创建分区的字段包含在主键字段中，也就是说需要将分区字段和主键字段作为复合主键

例如存在表me：

CREATE TABLE `me` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` char(20) DEFAULT NULL,

  the_date int(8) not null default 0

  PRIMARY KEY (`id`)

);

如果需要对the_date字段做分区，需要先将它与主键id做复合主键，操作步骤如下：

先删除自增：alter table me modify id int(11) not null;  

删除旧主键：alter table me drop primary key;

创建复合主键：alter table me add primary key(id, the_date);

最后再创建分区

 

 

查看表分区的情况（主要是记录数）：

SELECT    

        partition_name part,     

        partition_expression expr,     

        partition_description descr,     

        table_rows     

FROM    

        INFORMATION_SCHEMA.partitions     

WHERE    

        TABLE_SCHEMA = schema()    

        AND TABLE_NAME='good_2'; 

 

 

查看查询语句使用分区的情况：

explain partitions select * from good_2 where the_date between 20140102 and 20140103;

 

相关的分区测试结论：

分区和未分区占用文件空间大致相同 （数据和索引文件）

如果查询语句中有未建立索引字段，分区时间远远优于未分区时间

如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。

对于大数据量，建议使用分区功能。

去除不必要的字段

 

根据手册，增加myisam_max_sort_file_size 会增加分区性能