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

目录
  • 1 回顾
  • 2 水平分区的5种策略
    • 2.1 、Hash（哈希）
    • 2.2、 Range（范围） 
    • 2.3、Key（键值）
    • 2.4、List（预定义列表）
    • 2.5、Composite（复合模式）
  • 3 测试Range策略
    • 3.1 建立总表与分表
      • 3.1.1 总表语句
      • 3.1.2 分表语句
    • 3.2 初始化表数据
    • 3.3 同步数据至完整表中
    • 3.4 测试执行SQL的效率
    • 3.5 使用Explain执行计划分析
    • 3.6 建索引提效
    • 3.7 跨区执行效率分析
    • 3.8 总结
  • 4 分区策略详解
    • 4.1 、HASH（哈希）
    • 4.2、 RANGE（范围）
    • 4.3 、LIST（预定义列表）
    • 4.4 KEY（键值）
    • 4.5 嵌套分区（子分区）
  • 5 分区管理
    • 5.1 删除分区
    • 5.2 重建分区
      • 5.2.1 RANGE 分区重建
      • 5.2.2 LIST 分区重建
      • 5.2.3 HASH/KEY 分区重建
    • 5.3 新增分区
      • 5.3.1 新增 RANGE 分区
      • 5.3.2 新增 HASH/KEY 分区
      • 5.3.3 给已有的表加上分区
  • 6 去除分区主键限制

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        25,585,696 part_tab#P#p4.MYD
2008-05-24 09:26         8,159,232 part_tab#P#p4.MYI
2008-05-24 09:26        25,585,216 part_tab#P#p5.MYD
2008-05-24 09:26         8,159,232 part_tab#P#p5.MYI
2008-05-24 09:26        25,655,740 part_tab#P#p6.MYD
2008-05-24 09:26         8,181,760 part_tab#P#p6.MYI
2008-05-24 09:26        25,586,528 part_tab#P#p7.MYD
2008-05-24 09:26         8,160,256 part_tab#P#p7.MYI
2008-05-24 09:26        25,586,752 part_tab#P#p8.MYD
2008-05-24 09:26         8,160,256 part_tab#P#p8.MYI
2008-05-24 09:26        25,585,824 part_tab#P#p9.MYD
2008-05-24 09:26         8,159,232 part_tab#P#p9.MYI
2008-05-24 09:25             8,608 part_tab.frm
2008-05-24 09:25                68 part_tab.par

再次测试SQL性能

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

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

这边可以看到，在关键的字段添加索引并重启（net stop mysql，net start mysql）之后，分区的表性能有略微提升。而未分区的全表性能提升最明显，几乎接近分区的效率。

3.7 跨区执行效率分析

通过上面的分析可以看出，在单个区内执行，比不分区效率又很明显的差距，这是因为分区之后扫描非范围缩小了。

那如果我们上面条件增加出生年份的范围，让他产生跨区域的情况，效果会怎么样呢，我们测试一下。

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

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

可见，跨区之后性能会差一些。这边应该这样理解，跨区的越多，性能越差，所以做分区设计的时候应该意识到，避免那种频繁的跨区情况发生，谨慎判断分区边界条件。

3.8 总结

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

2、查询语句中关键字段未建立索引字段时，分区时间远远优于未分区时间

3、如果查询语句中字段建立了索引，分区和未分区的差别缩小，但是仍然优于未分区情况，而且随着数据量增加，这个优势会更明显。

4、对于大数据量，还是建议使用分区功能，无论他有没有建立索引。

5、根据MySQL手册， 增加myisam_max_sort_file_size 会增加分区性能(mysql重建索引时允许使用的临时文件最大大小)

6、对分区进行设计时，谨慎判断分区边界条件，避免有过度频繁的跨区操作，否则性能不会理想。

4 分区策略详解

4.1 、HASH（哈希）

HASH分区主要用来确保数据在预先确定数目的分区中平均分布，而在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，

而在HASH分区中，MySQL自动完成这些工作，

你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 示例如下：

/*Hash*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 4(
     PARTITION P0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
     PARTITION P1 DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
     PARTITION P2 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
     PARTITION P3 DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

上面的例子，使用HASH函数对createtime日期进行HASH运算，并根据这个日期来分区数据，这里共分为10个分区。

建表语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回整数的表达式，它可以是字段类型为MySQL 整型的一列的名字，也可以是返回非负数的表达式。

另外，可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

每个分区都有自己独立的数据、索引文件的存放目录，并且这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘IO吞吐量。

4.2、 RANGE（范围）

基于属于一个给定连续区间的列值，把多行分配给同一个分区，这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。示例如下：

/*Range*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(gwcode) (
PARTITION P0 VALUES LESS THAN(101) DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION P1 VALUES LESS THAN(201) DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
PARTITION P2 VALUES LESS THAN(301) DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
PARTITION P3 VALUES LESS THAN MAXVALUE DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

上面的示例，使用了范围RANGE函数对岗位编号进行分区，共分为4个分区，

岗位编号为1~100 的对应在分区P0中，101~200的编号在分区P1中，依次类推即可。那么类别编号大于300，可以使用MAXVALUE来将大于300的数据统一存放在分区P3中即可。

每个分区都有自己独立的数据、索引文件的存放目录，并且这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘IO吞吐量。

4.3 、LIST（预定义列表）

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择分区的。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，

然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。 示例如下：

/*List*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(`gwcode`) (
PARTITION P0 VALUES IN (46,77,89) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION P1 VALUES IN (106,125,177) DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
PARTITION P2 VALUES IN (205,219,289) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
PARTITION P3 VALUES IN (302,317,458,509,610) DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

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

不同于RANGE的是，LIST分区的数据必须匹配列表中的岗位编号才能进行分区，所以这种方式只是适合比较区间值确定并少量的情况。

每个分区都有自己独立的数据、索引文件的存放目录，并且这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘IO吞吐量。

4.4 KEY（键值）

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 示例如下：

/*key*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(gwcode)
PARTITIONS 4(
     PARTITION P0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
     PARTITION P1 DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
     PARTITION P2 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
     PARTITION P3 DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

注意：此种分区算法目前使用的比较少，使用服务器提供的哈希函数有不确定性，对于后期数据统计、整理存在会更复杂，所以我们更倾向于使用由我们定义表达式的Hash，大家知道其存在和怎么使用即可。

4.5 嵌套分区（子分区）

嵌套分区（子分区）是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。

drop table if EXISTS `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) SUBPARTITION BY HASH (id% 4) SUBPARTITIONS 2(
     PARTITION p0 VALUES LESS THAN (5000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
     PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx'

);

如上，对RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

5 分区管理

5.1 删除分区

/*删除分区 P1*/
2  ALERT TABLE users_part DROP PARTITION P1;

5.2 重建分区

5.2.1 RANGE 分区重建

/*这边将原来的 P0,P1 分区合并起来，放到新的 P0 分区中，并重新设定条件为少于5000000。*/
ALTER TABLE users_part REORGANIZE PARTITION P0,P1 INTO (PARTITION P0 VALUES LESS THAN (5000000));

用于因空间过于浪费而产生的合并情况。

5.2.2 LIST 分区重建

/*将原来的 P0,P1 分区合并起来，放到新的 P0 分区中,跟上一个的意思有点像。*/
ALTER TABLE users_part REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(1,4,5,8,9,12,13,101,555));

5.2.3 HASH/KEY 分区重建

/*用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。*/
ALTER TABLE users_part REORGANIZE PARTITION COALESCE PARTITION 2;

5.3 新增分区

5.3.1 新增 RANGE 分区

 /*新增一个RANGE分区*/
 ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
 DATA DIRECTORY = '/data8/data'
 INDEX DIRECTORY = '/data8/idx');

5.3.2 新增 HASH/KEY 分区

/* 将分区总数扩展到n个。n请用数值代替 */
ALTER TABLE users_part ADD PARTITION PARTITIONS n;

5.3.3 给已有的表加上分区

alter tableuser_part partition by RANGE (month(birth))
(
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) ,
PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) ,
PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) ,
PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) ,
PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) ,
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13)
);

6 去除分区主键限制

默认分区限制分区字段必须是主键（PRIMARY KEY)的一部分，需要去除此限制。

如果表中设立主键，会报出如下提示：A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

一种解决方式就是使用主键来做为分区条件：

ALTER TABLE users_part  PARTITION BY HASH(id)  PARTITIONS 4;

另外一种方式就是把分区条件字段加入主键中，变成联合主键。如下，id和gwcode 组成了联合主键：

 alter table users_part drop PRIMARY KEY;
 alter table users_part add PRIMARY KEY(id, gwcode);

到此这篇关于MySQL使用Partition功能实现水平分区的文章就介绍到这了,更多相关mysql水平分区内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql数据表分区技术PARTITION浅析

    在这一章节里, 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)   Mysql 的分区技术与水平分表有点类似, 但是它是在逻辑层进行的水平分表, 对于应用而言它还是一张表, 换句话说: 分区不是实际真正的对一张表进行拆分,分区之后表还是一个表,它是把存储文件进行拆分. 在 Mysql 5.1(后) 有了几种分区类型:   RANGE分区: 基于属于一个给定连续区间的列值, 把多行分配给分区 LIST分区: 类似于按 RANGE 分区, 区别在于 LIST 分区是基

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

    目录 1 回顾 2 水平分区的5种策略 2.1 .Hash(哈希) 2.2. Range(范围)  2.3.Key(键值) 2.4.List(预定义列表) 2.5.Composite(复合模式) 3 测试Range策略 3.1 建立总表与分表 3.1.1 总表语句 3.1.2 分表语句 3.2 初始化表数据 3.3 同步数据至完整表中 3.4 测试执行SQL的效率 3.5 使用Explain执行计划分析 3.6 建索引提效 3.7 跨区执行效率分析 3.8 总结 4 分区策略详解 4.1 .HA

  • MySQL使用Partition功能实现水平分区

    1 回顾 上一节我们详细讲解了如何对数据库进行分区操作,包括了 垂直拆分(Scale Up 纵向扩展)和水平拆分(Scale Out 横向扩展) ,同时简要整理了水平分区的几种策略,现在来回顾一下. 2 水平分区的5种策略 2.1 Hash(哈希) 这种策略是通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区.例如我们可以建立一个对表的日期的年份进行分区的策略,这样每个年份都会被聚集在一个区间. PARTITION BY HASH(YEAR(c

  • MySQL数据库表的合并与分区实现介绍

    目录 创建数据表 数据库表合并 数据库表分区 创建数据表 创建数据表的,使用字符串应该遵循的原则 从速度方面考虑,要选择固定的列,可以使用CHAR类型 要节省空间,使用动态的列,可以使用VARCHAR类型 要将列中的内容限制为一种选择,可以使用ENUM类型 允许在一列中有多个条目,可以使用SET类型 如果要搜索的内容不区分大小写,可以使用TEXT类型 如果要搜索的内容区分大小写,可以使用BLOB类型 创建数据表其实就是在已经创建好的数据库中建立新表. 数据表属于数据库,在创建数据表之前,应该使用

  • MySQL预编译功能详解

    MySQL预编译功能详解

    本文为大家分享了MySQL预编译功能,供大家参考,具体内容如下 1.预编译的好处 大家平时都使用过JDBC中的PreparedStatement接口,它有预编译功能.什么是预编译功能呢?它有什么好处呢? 当客户发送一条SQL语句给服务器后,服务器总是需要校验SQL语句的语法格式是否正确,然后把SQL语句编译成可执行的函数,最后才是执行SQL语句.其中校验语法,和编译所花的时间可能比执行SQL语句花的时间还要多. 如果我们需要执行多次insert语句,但只是每次插入的值不同,MySQL服务器也是需

  • MySQL联合索引功能与用法实例分析

    MySQL联合索引功能与用法实例分析

    本文实例讲述了MySQL联合索引功能与用法.分享给大家供大家参考,具体如下: 联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用

  • jQuery实现带延时功能的水平多级菜单效果【附demo源码下载】

    jQuery实现带延时功能的水平多级菜单效果【附demo源码下载】

    本文实例讲述了jQuery实现带延时功能的水平多级菜单效果.分享给大家供大家参考,具体如下: 运行效果图如下: 具体代码如下: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999

  • php利用scws实现mysql全文搜索功能的方法

    本文实例讲述了php利用scws实现mysql全文搜索功能的方法.分享给大家供大家参考.具体方法如下: scws这样的中文分词插件比较不错,简单的学习了一下,它包涵一些专有名称.人名.地名.数字年代等规则集合,可以直接将语句按这些规则分开成一个一个关键词,准确率在90%-95%之间,按照安装说明把scws的扩展放入php的扩展目录里,下载规则文件和词典文件,并在php配置文件中引用它们,就可以用scws进行分词了. 1) 修改 php 扩展代码以兼容支持 php 5.4.x 2) 修正 php

  • mysql实现sequence功能的代码

    mysql实现sequence功能 1.建立sequence记录表 CREATE TABLE `sys_sequence` ( `seq_name` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `min_value` int(11) NOT NULL, `max_value` int(11) NOT NULL, `current_value` int(11) NOT NULL, `increment_value` i

  • python使用Flask操作mysql实现登录功能

    用到的一些知识点:Flask-SQLAlchemy.Flask-Login.Flask-WTF.PyMySQL 这里通过一个完整的登录实例来介绍,程序已经成功运行,在未登录时拦截了success.html页面跳转到登录页面,登录成功后才能访问success. 以下是项目的整体结构图: 首先是配置信息,配置了数据库连接等基本的信息,config.py DEBUG = True SQLALCHEMY_ECHO = False SQLALCHEMY_DATABASE_URI = 'mysql+pymy

  • ThinkPHP框架实现的MySQL数据库备份功能示例

    本文实例讲述了ThinkPHP框架实现的MySQL数据库备份功能.分享给大家供大家参考,具体如下: 1.缘由 自从2010年开始试用ThinkPHP以来,的确带来了许多方便.的确给我带来了许多方便.此次应为数据频繁备份需要,而每次远程连接到服务器颇为不便.变萌生了写个ThinkPHP数据库备份SQL生成类的念头. 2.介绍 由于在数据库中有使用触发器.因此也需要一并备份.并且为了插入数据的时候不会受到触发器影响而破坏先前插入的数据,在插入数据之前生成了删除触发器的代码. 本类并不能生成数据表的创

随机推荐