MySQL-mysql分区合理建立

MySQL-mysql分区合理建立

归属感 发布于 2017-04-14 字数 54 浏览 1018 回复 1

怎么样合理建立和使用mysql分区查询提高检索效率?

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

灵芸 2017-04-18 1 楼

MySQL从5.1.3开始支持Partition,MySQL支持RANGE,LIST,HASH,KEY分区类型,主要还是根据业务来选择分区类型。

水平分区的几种模式:

1、Range(范围) — 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980′s)的数据,90年代(1990′s)的数据以及任何在2000年(包括2000年)后的数据。
2、Hash(哈希) — 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。
3、Key(键值) — 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
4、List(预定义列表) — 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。
5、Composite(复合模式) — 是以上模式的组合使用。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。
垂直分区(按列分)

举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。
其中以RANGE最为常用,下面主要介绍下RANGE分区类型的用法:

mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+

1. RANGE类型
mysql> CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
created DATETIME,
PRIMARY KEY(id, created)
) ENGINE=INNODB PARTITION BY RANGE (TO_DAYS(created))
(
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01')),
PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01')),
PARTITION foo_4 VALUES LESS THAN (TO_DAYS('2012-01-01'))
)
mysql> INSERT INTO `foo` (`id`, `created`) VALUES
(1, '2008-01-02 00:00:00'),
(2, '2009-01-02 00:00:00'),
(3, '2010-01-02 00:00:00'),
(4, '2010-01-02 00:00:00'),
(5, '2011-01-02 00:00:00');

mysql> SELECT * FROM foo;
+----+---------------------+
| id | created |
+----+---------------------+
| 1 | 2008-01-02 00:00:00 |
| 2 | 2009-01-02 00:00:00 |
| 3 | 2010-01-02 00:00:00 |
| 4 | 2010-01-02 00:00:00 |
| 5 | 2011-01-02 00:00:00 |
+----+---------------------+

mysql> EXPLAIN PARTITIONS select * from foo where created>'2009-01-01 00:00:00' and created<'2011-01-01 00:00:00';
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | foo | foo_1,foo_2,foo_3 | index | NULL | PRIMARY | 12 | NULL | 4 | Using where; Using index |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+

可以看到这条查询只用了foo_1,foo_2,foo_3这三个分区,而不用去全表扫描,数据量越大查询速度能得到明显提高