查看内容

MySQL分区表的使用

  • 2019-12-12 14:15
  • 新浦京计算机网络
  • Views

MySQL Sharding可扩展设计简析

MySQL使用分区表的好处:

 

1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。 2,方便维护,通过删除分区来删除老的数据。 3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。
MySQL可以建立四种分区类型的分区:           RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。 ·         LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。  www.2cto.com   ·         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          sale_date      
        sale_item  ()    
        sale_money (,)  


      PARTITION  RANGE ((sale_date)  
        PARTITION p201001  LESS THAN (  
        PARTITION p201002  LESS THAN (  
        PARTITION p201003  LESS THAN (  
        PARTITION p201004  LESS THAN (  
        PARTITION p201005  LESS THAN (  
        PARTITION p201006  LESS THAN (  
        PARTITION p201007  LESS THAN (  
        PARTITION p201008  LESS THAN (  
        PARTITION p201009  LESS THAN (  

 Query OK,  rows affected ( sec)

    

新增分区

    我们知道,当数据库中的数据量越来越大时,不论是读还是写,压力都会变得越来越大。采用MySQL Replication多master多slave方案,在上层做负载均衡,虽然能够一定程度上缓解压力。但是当一张表中的数据变得非常庞大时,压力还是非常大的。试想,如果一张表中的数据量达到了千万甚至上亿级别的时候,不管是建索引,优化缓存等,都会面临巨大的性能压力。 www.2cto.com  

mysql      PARTITION (PARTITION p201010  LESS THAN ( rows affected (  Duplicates:   Warnings: 

 

删除分区

二.定义

--当删除了一个分区,也同时删除了该分区中所有的数据。
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)  www.2cto.com  
Records: 0  Duplicates: 0  Warnings: 0

 

 分区的合并

    数据sharding,也称作数据切分,或分区。是指通过某种条件,把同一个数据库中的数据分散到多个数据库或多台机器上,以减小单台机器压力。

下面的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.2cto.com  
    ->     PARTITION s2010 VALUES LESS THAN (201004)

    -> );

Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

    (1)垂直分区:以表为单位,把不同的表分散到不同的数据库或主机上。特点是规则简单,实施方便,适合业务之间耦合度低的系统。

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

    (2)水平分区:以行为单位,将同一个表中的数据按照某种条件拆分到不同的数据库或主机上。特点是相对复杂,适合单表巨大的系统。

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.2cto.com   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)

 

1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。2,方便维护,通过删除分...

四.示意图

 

    下面通过几张图来给出上面三种分区方式的直观效果。  

    (1)垂直分区  www.2cto.com  

       
图片 1  

    (2)水平分区

       
图片 2  

    (3)联合分区

      
图片 3  

五.注意事项

 

     下面我们所说的分区,主要是指水平分区。  

上一篇:mysql表结构表空间和索引的查询 下一篇:没有了