1. 分区表的原理
2. 分区表的类型
水平分区:
水平分区也叫范围分区,每个分区存储落在某个范围的记录。假如有1000W条数据,按日期分成十份,2010年之前的数据放到第一个分区,2011年之前的数据放到第二个分区,依此类推。也就是把表分成了十份。
垂直分区:
假设在设计用户信息表时,把所有用户信息都放到了一张表里面去,这样这张表里面就会有比较大的字段,如个人说明,而这个字段,可能不会有很多人去看,通常是有人要看的时候,才去查找。分表的时候,就可以把这样的大字段,分出来。也就是改变了表结构,把一张表拆分成了两张。
3. 创建分区表
3.1 新表创建分区
MySQL在创建表时使用PARTTITION BY子句实现分区表,举例:
CREATE TABLE t_part ( id int not NULL, name varchar(30) not NULL, add_time date not NULL) engine=myisam
PARTITION BY RANGE (year(add_time)) (
PARTITION p0 VALUES LESS THAN (2010), PARTITION p1 VALUES LESS THAN (2011) ,
PARTITION p2 VALUES LESS THAN (2012) , PARTITION p3 VALUES LESS THAN (2013) ,
PARTITION p4 VALUES LESS THAN (2014) , PARTITION p5 VALUES LESS THAN (2015) ,
PARTITION p6 VALUES LESS THAN (2016) , PARTITION p7 VALUES LESS THAN (2017) ,
PARTITION p8 VALUES LESS THAN (2018) , PARTITION p9 VALUES LESS THAN (2019) ,
PARTITION p11 VALUES LESS THAN MAXVALUE );
说明:PARTTITION BY分区子句可以使用各种函数,但有一个要求,表达式返回的值必须是一个确定的整数。本例中使用函数YEAR(),根据时间间隔进行分区,这也是一种很常见的分区方式。MySQL还支持键值、哈希和列表分区。
执行上面的建表语句后,去到MySQL的数据存储目录,可以看到如下数据文件:
3.2 已有表创建分区
alter table p_no_part partition by RANGE (year(add_time))(
PARTITION p0 VALUES LESS THAN (2010), PARTITION p1 VALUES LESS THAN (2011) ,
PARTITION p2 VALUES LESS THAN (2012) , PARTITION p3 VALUES LESS THAN (2013) ,
PARTITION p4 VALUES LESS THAN (2014) , PARTITION p5 VALUES LESS THAN (2015) ,
PARTITION p6 VALUES LESS THAN (2016) , PARTITION p7 VALUES LESS THAN (2017) ,
PARTITION p8 VALUES LESS THAN (2018) , PARTITION p9 VALUES LESS THAN (2019) ,
PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 10000000 rows affected (15.20 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
4. 性能测试
4.1 数据准备
<?php
$conn = mysqli_connect($servername, $username, $password,$dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
for($i = 0; $i < 10000000 ; $i++){
$sql = "insert into t_part values (".$i.",'test',date('".(rand(2010,2020)."-01-01")."'))";
if ($conn->query($sql) != TRUE) {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
$conn->close();
insert into t_no_part select * from t_part;
4.2 SQL性能测试
4.2.1 SQL耗时对比
select count(*) from t_part where add_time > date '2010-01-01' and add_time < date '2011-01-01';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.32 sec)
select count(*) from t_no_part where add_time > date '2010-01-01' and add_time < date '2011-01-01';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (3.06 sec)
4.2.2 EXPLAIN分析
explain select count(*) from t_part where add_time > date '2010-01-01' and add_time < date '2011-01-01' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_part
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992155
Extra: Using where
1 row in set (0.00 sec)
explain select count(*) from t_no_part where add_time > date '2010-01-01' and add_time < date '2011-01-01' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_no_part
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000000
Extra: Using where
1 row in set (0.00 sec)
通过EXPIAIN工具分析的结果可以得知,同一查询,分区表比非分区表查询rows要少很多,这也是我们使用分区表原因之一。
4.2.3 索引测试
在分区表和非分区表上分别创建索引:
create index idx_time on t_part (add_time);
create index idx_time on t_no_part (add_time);
创建索引之后,再次进行 SQL查询测试,
select count(*) from t_part where add_time > date '2010-01-01' and add_time < date '2012-12-01';
+----------+
| count(*) |
+----------+
| 1983485 |
+----------+
1 row in set (1.19 sec)
select count(*) from t_no_part where add_time > date '2010-01-01' and add_time < date '2012-12-01';
+----------+
| count(*) |
+----------+
| 1983485 |
+----------+
1 row in set (1.28 sec)
可以看到,创建索引之后,分区表和未分区表的查询时间差不多, 分区表略优于非分区表。
5.总结
在数据量超大的时候,B-Tree索引就无法起作用了,除非是索引覆盖查询,否则数据库服务器需要根据索引扫描的结果回表,查询所有符合条件的记录,如果数据量巨大,这将产生大量随机I/O,严重影响服务器的性能。
当数据量当数据量非常大的时候,不能再每次查询的时候都扫描全表,由于索引也无法使用,就可以考虑是否分区表的方案。理解分区时还可以将其当做索引的最初形态,以代价非常小的方式定位到需要的数据在那一片区域。在这片区域中,你可以做顺序扫描,可以建索引,还可以将数据都缓存到内存 等等。因为分区无需额外的数据结构记录每个分区有哪些数据——分区不需要精准定位每条数据的位置,也就无需额外的数据结构,所以代价非常低。