欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql使用教程之分区表的使用方法(删除分区表)

程序员文章站 2024-02-27 22:17:39
mysql使用分区表的好处: 1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。2,方便维护,通过删除分区来删除老的数据。3,分区数据可以被分...

mysql使用分区表的好处:

1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。
2,方便维护,通过删除分区来删除老的数据。
3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。

mysql可以建立四种分区类型的分区:

range 分区:基于属于一个给定连续区间的列值,把多行分配给分区。

list 分区:类似于按range分区,区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择。  www.jb51.net 

hash分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含mysql 中有效的、产生非负整数值的任何表达式。

key 分区:类似于按hash分区,区别在于key分区只支持计算一列或多列,且mysql 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

一般用得多的是range分区和list分区。
range分区
这里以一个销售的业务来做测试
销售表有日期/商品/销售额三个字段
测试数据从2010年1月1日至2010年9月31日
以“月”为单位进行分区
初期分区定义
首先需要查看,当前数据库是否支持分区

复制代码 代码如下:

mysql>show variables like '%partition%';
+-------------------+-------+
| variable_name     | value |
+-------------------+-------+
| have_partitioning | yes   |

+-------------------+-------+
1 row in set (0.03 sec)

创建分区表,按照年月的方式分区。

复制代码 代码如下:

mysql> create table sale_data (
    ->   sale_date  datetime not null,

    ->   sale_item  varchar(2) not null ,

    ->   sale_money decimal(10,2) not null

    -> )  www.jb51.net 

    -> partition by range (year(sale_date)*100+month(sale_date)) (

    ->   partition p201001 values less than (201002),

    ->   partition p201002 values less than (201003),

    ->   partition p201003 values less than (201004),

    ->   partition p201004 values less than (201005),

    ->   partition p201005 values less than (201006),

    ->   partition p201006 values less than (201007),

    ->   partition p201007 values less than (201008),

    ->   partition p201008 values less than (201009),

    ->   partition p201009 values less than (201010),

    ->   partition pcatchall vlaues less than maxvalue
    -> );

query ok, 0 rows affected (0.20 sec)

新增分区

复制代码 代码如下:

mysql> alter table sale_data
    ->   add partition (partition p201010 values less than (201011));

query ok, 0 rows affected (0.36 sec)
records: 0  duplicates: 0  warnings: 0

删除分区

复制代码 代码如下:

--当删除了一个分区,也同时删除了该分区中所有的数据。
mysql> alter table sale_data drop partition p201010;
query ok, 0 rows affected (0.22 sec)  www.jb51.net 
records: 0  duplicates: 0  warnings: 0

分区的合并

下面的sql,将p201001 - p201009 合并为3个分区p2010q1 - p2010q3

复制代码 代码如下:

mysql> alter table sale_data
    ->   reorganize partition p201001,p201002,p201003,

    ->                        p201004,p201005,p201006,

    ->                        p201007,p201008,p201009 into

    -> (

    ->   partition p2010q1 values less than (201004),

    ->   partition p2010q2 values less than (201007),

    ->   partition p2010q3 values less than (201010)

    -> );

query ok, 0 rows affected (1.14 sec)
records: 0  duplicates: 0  warnings: 0

分区的拆分

下面的sql,将p2010q1 分区,拆分为s2009 与s2010 两个分区

复制代码 代码如下:

mysql> alter table sale_data reorganize partition p2010q1 into (

    ->     partition s2009 values less than (201001),
             www.jb51.net 
    ->     partition s2010 values less than (201004)

    -> );

query ok, 0 rows affected (0.36 sec)
records: 0  duplicates: 0  warnings: 0

一个利用不同物理位置数据源做分区的例子:

复制代码 代码如下:

create table ts (id int, purchased date)
    engine=innodb
    partition by range(year(purchased))
    subpartition by hash(id)
    (
        partition p0 values less than (1990)
        (
            subpartition s0                  //在大的分区下又有小的分区
            data directory='/usr/local/mysql/data0'      //数据源
            index directory='/usr/local/mysql/index0',   //索引数据源
            subpartition s1
            data directory='/usr/local/mysql/data1'
            index directory='/usr/local/mysql/index1'
        ),
        partition p1 values less than (maxvalue)
        (
            subpartition s2
            data directory='/usr/local/mysql/data1'
            index directory='/usr/local/mysql/index1',
            subpartition s3
            data directory='/usr/local/mysql/data2'
            index directory='/usr/local/mysql/index2'
        )
    );

分区索引的局限:
1,所有分区都要使用同样的引擎。
2,分区表的每一个唯一索引必须包含由分区函数引用的列。
3,mysql能避免查询所有的分区,但仍然锁定了所有分区。
4,分区函数能使用的函数和表达式有限,例如函数有上面的4种。
5,分区不支持外键。  www.jb51.net 
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)