您现在的位置是：网站首页> 编程资料编程资料

MySQL使用Partition功能实现水平分区的策略_Mysql_

2023-05-27 384人已围观

简介 MySQL使用Partition功能实现水平分区的策略_Mysql_

1 回顾

上一节我们详细讲解了如何对数据库进行分区操作，包括了垂直拆分（Scale Up 纵向扩展）和水平拆分（Scale Out 横向扩展），同时简要整理了水平分区的几种策略，现在来回顾一下。

2 水平分区的5种策略

2.1 、Hash（哈希）

这种策略是通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如我们可以建立一个对表的日期的年份进行分区的策略，这样每个年份都会被聚集在一个区间。

 PARTITION BY HASH(YEAR(createtime)) PARTITIONS 10

2.2、 Range（范围）

这种策略是将数据划分不同范围。例如我们可以将一个千万级别的表通过id划分成4个分区，每个分区大约500W的数据，超过750W后的数据统一放在第4个分区。

 PARTITION BY RANGE(id) ( PARTITIONP0 VALUES LESS THAN(2500001), PARTITIONP1 VALUES LESS THAN(5000001), PARTITIONp2 VALUES LESS THAN(7500001), PARTITIONp3 VALUES LESS THAN MAXVALUE )

2.3、Key（键值）

Hash策略的一种延伸，这里的Hash Key是MySQL系统产生的。

2.4、List（预定义列表）

这种策略允许系统通过定义列表的值所对应的行数据进行分割。例如，我们根据岗位编码进行分区，不同岗位类型的编码对应到不同的分区去，达到分治的目的。

 PARTITION BY LIST(gwcode) ( PARTITIONP0 VALUES IN (46,77,89), PARTITIONP1 VALUES IN (106,125,177), PARTITIONP2 VALUES IN (205,219,289), PARTITIONP3 VALUES IN (302,317,458,509,610) )

上述的SQL脚本，使用了列表匹配LIST函数对员工岗位编号进行分区，共分为4个分区，行政岗位编号为46,77,89的对应在分区P0中，技术岗位 106,125,177类别在分区P1中，依次类推即可。

2.5、Composite（复合模式）

复合模式其实就是对上面几种模式的组合使用，比如你在Range的基础上，再进行Hash 哈希分区。

3 测试Range策略

3.1 建立总表与分表

我们建立一个普通的用户表 users，再建立一个分区表users_part，将80年代出生的用户按照年份进行了分区，如下：

3.1.1 总表语句

 mysql> CREATE TABLE users ( "id" int(10) unsigned NOT NULL, "name" varchar(100) DEFAULT NULL, "birth" datetime ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected

3.1.2 分表语句

最后一行注意一下，是将89年之后出生的都归属到第10个分区上，我们这边模拟的都是80年代出生的用户，实际业务中跟据具体情况进行拆分。

 mysql> create table users_part ( "id" int(10) unsigned NOT NULL, "name" varchar(100) DEFAULT NULL, "birth" datetime ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (year(birth)) ( PARTITION p0 VALUES LESS THAN (1981), PARTITION p1 VALUES LESS THAN (1982), PARTITION p2 VALUES LESS THAN (1983), PARTITION p3 VALUES LESS THAN (1984), PARTITION p4 VALUES LESS THAN (1985), PARTITION p5 VALUES LESS THAN (1986), PARTITION p6 VALUES LESS THAN (1987), PARTITION p7 VALUES LESS THAN (1988), PARTITION p8 VALUES LESS THAN (1989),17 PARTITION p9 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected

3.2 初始化表数据

我们可以使用函数或者存储过程批量进行数据初始化,这边插入1000W条数据。

 DROP PROCEDURE IF EXISTS init_users_part; delimiter $ /* 设定语句终结符为 $*/ CREATE PROCEDURE init_users_part()   begin    DECLARE srt int default 0;    while srt < 10000000 /* 设定写入1000W的数据 */ do     insert into `users_part` values (srt, concat('username_',idx1),adddate('1980-01-01',rand() * 3650)); /*在10年的时间内随机取值*/   set srt = srt + 1;   end while;   end $ delimiter ; call init_users_part();

3.3 同步数据至完整表中

 mysql> insert into users select * from users_part; //将1000w数据复制到未分区的完整表users 中 Query OK, 10000000 rows affected (51.59 sec) Records: 10000000 Duplicates: 0 Warnings: 0

3.4 测试执行SQL的效率

 mysql> select count(*) from users_part where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (0.335 sec) mysql> select count(*) from users where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (5.187 sec)

结果比较清晰，分区表的执行效率确实比较高，执行时间是未分区表 1/10 都不到。

3.5 使用Explain执行计划分析

 mysql> explain select count(*) from users_part where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | users_part | p7 | ALL | NULL | NULL | NULL | NULL | 987769| 100.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(*) from users where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL |10000000 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

这边关注两个关键参数：一个是partitions，users_part中是p7，说明数据检索在第七分区中，users表是null的，说明是全区域扫描，无分区。

另外一个参数是rows，是预测扫描的行数，users表明显是全表扫描。

3.6 建索引提效

因为我们使用birth字段进行分区和条件查询，所以这边尝试在birth字段上简历索引进行效率优化。

 mysql> create index idx_user on users(birth); Query OK, 0 rows affected (1 min 7.04 sec) Records: 10000000 Duplicates: 0 Warnings: 0 mysql> create index idx_user_part on users_part(birth); Query OK, 0 rows affected (1 min 1.05 sec) Records: 10000000 Duplicates: 0 Warnings: 0

创建索引后的数据库文件大小列表：

2008-05-24 09:23 8,608 no_part_tab.frm 2008-05-24 09:24 255,999,996 no_part_tab.MYD 2008-05-24 09:24 81,611,776 no_part_tab.MYI 2008-05-24 09:25 0 part_tab#P#p0.MYD 2008-05-24 09:26 1,024 part_tab#P#p0.MYI 2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD 2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI 2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD 2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI 2008-05-24 09:25 0 part_tab#P#p11.MYD 2008-05-24 09:26 1,024 part_tab#P#p11.MYI 2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD 2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI 2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD 2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI 2008-05-24 09:26

上一篇：MySQL 数据库索引和事务_Mysql_

下一篇：mysql事务详细介绍_Mysql_