MySQL使用分区表的好处: 1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。 2,方便维护,通过删除分区来删除老的数据。 3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。 MySQL可以建立四种分区类型的分区: RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。 · LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 · HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。 · KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 一般用得多的是range分区和list分区。 RANGE分区 这里以一个销售的业务来做测试 销售表有日期/商品/销售额三个字段 测试数据从2010年1月1日至2010年9月31日 以“月”为单位进行分区 初期分区定义 首先需要查看,当前数据库是否支持分区 mysql>SHOW VARIABLES LIKE '%partition%'; +-------------------+-------+
| Variable_name
+-------------------+-------+
| have_partitioning | YES +-------------------+-------+ 1 row in set (0.03 sec) 创建分区表,按照年月的方式分区。 mysql> CREATE TABLE sale_data (
-> PARTITION pcatchall VLAUES LESS THAN MAXVALUE
Query OK, 0 rows affected (0.20 sec) 新增分区 mysql> ALTER TABLE sale_data
Query OK, 0 rows affected (0.36 sec)
Records: 0 删除分区 --当删除了一个分区,也同时删除了该分区中所有的数据。 mysql> ALTER TABLE sale_data DROP PARTITION p201010; Query OK, 0 rows affected (0.22 sec)
Records: 0 分区的合并
下面的SQL,将p201001 - p201009 mysql> ALTER TABLE sale_data
Query OK, 0 rows affected (1.14 sec)
Records: 0 分区的拆分
下面的SQL,将p2010Q1
mysql> ALTER TABLE sale_data REORGANIZE PARTITION
Query OK, 0 rows affected (0.36 sec)
Records: 0 一个利用不同物理位置数据源做分区的例子:
CREATE TABLE ts (id INT, purchased DATE) 分区索引的局限: 1,所有分区都要使用同样的引擎。 2,分区表的每一个唯一索引必须包含由分区函数引用的列。 3,mysql能避免查询所有的分区,但仍然锁定了所有分区。 4,分区函数能使用的函数和表达式有限,例如函数有上面的4种。 5,分区不支持外键。 6,不能使用LOAD INDEX INTO CACHE 7,分区并不能总是改善性能,要进行性能评测。 例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据: mysql> explain partitions select * from fenqubiao where day<'2011-09-12'; +----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | fenqubiao | p_2010,p_2011 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) (责任编辑:IT) |