精华内容
下载资源
问答
  • Mysql分区 Mysql从5.1版本开始支持...Mysql分区的好处: 和单个磁盘或者文件系统相比,分区可以存储更多数据 优化查询,在where子句中包含分区条件,可以只扫描必要的一个或者多个分区而非全表扫描,同时在涉及su

    Mysql分区

    Mysql从5.1版本开始支持分区的功能,分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分,就访问数据库而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由数个物理分区对象组成,每个分区都是一个独立的对象,可以作为表的一部分独立的处理。分区对应用来说完全透明的,不影响应用业务逻辑。

    Mysql分区的好处

    • 和单个磁盘或者文件系统相比,分区可以存储更多数据
    • 优化查询,在where子句中包含分区条件,可以只扫描必要的一个或者多个分区而非全表扫描,同时在涉及sum()和count()这类聚合函数查询时候,也可以并行在多个分区处理然后汇总,提高效率。
    • 删除数据可以通过删除与这些数据有关的分区来快速删除
    • 跨多个磁盘来分散数据查询,提高查询吞吐量

    分区概述

    在MySQL中,InnoDB存储引擎长期支持表空间的概念,并且MySQL服务器甚至在分区引入之前,就能配置为存储不同的数据库使用不同的物理路径,分区又把这个概念推进了一步,它允许根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数。

    分区有利于管理非常大的表,分区通过引入分区键(paritition key)的概念,分区键用于根据某个区间值(范围值)、特定值或者HASH函数值执行数据的聚集,让数据根据规则分布在不同分区中,让一个大对象变成一些小对象。

    通过使用show variables命令(MYSQL5.6以下版本)来确定当前的MYSQL是否支持分区,如果看到have_paritition_engine的值为YES,则表示MYSQL的版本不支持分区,5.6以上的版本使用show plugins;命令查看,如下即代表支持分区:

    MYSQL支持大部分存储引擎(MyISAM、InnoDB、Memory等存储引擎)创建分区表,不支持MERGE或CSV存储引擎创建分区表,在MYSQL5.1版本中,同一个分区表的所有分区必须使用同一个存储引擎,同一库不同分区表可以使用不同存储引擎。

    分区类型

    MySQL 5.1中可用的分区类型。这些类型包括:

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

    ·         LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

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

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

    无论使用何种类型的分区,分区总是在创建时就自动的顺序编号,且从0开始记录。当有一新行插入到一个分区表中时,就是使用这些分区编号来识别正确的分区。例如,如果你的表使用4个分区,那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型,确认每个分区编号都定义了一个分区,很有必要。对HASH分区,使用的用户函数必须返回一个大于0的整数值。对于KEY分区,这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。分区的名字是不区分大小写的。例如,下面的CREATE TABLE语句将会产生如下的错误:这是因为MySQL认为分区名字mypart和MyPart没有区别。

    CREATE TABLE t2 (val INT)
        PARTITION BY LIST(val)(
        PARTITION mypart VALUES IN (1,3,5),
        PARTITION MyPart VALUES IN (2,4,6)
        );

    在MYSQL5.1版本中,RANGE分区、HASH分区、LIST分区都要求分区键必须是INT类型,或者通过表达式返回INT类型,即是MYSQL5.1仅仅支持整数分区,唯一的例外就是分区类型是KEY时,可以使用其他类型列作为分区键(BLOB和TEXT列类型除外),但是在MYSQL5.5版本中,RANGE和LIST分区支持非整型了。

    除此之外,无论是那种MYSQL分区类型,要么分区表上没有主键或唯一键,要么分区表的主键和唯一键都必须包含分区键,即不能使用主键或唯一键字段之外的其他字段分区。

     

    RANGE分区

    利用RANGE分区的表是利用取值范围将数据分成分区,区间要连续并且不能互相重叠,使用VALUE LESS THAN 操作符进行分区定义。如下员工表按照STORE_ID进行分区:

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT NOT NULL,
        store_id INT NOT NULL
    )
    PARTITION BY RANGE (store_id) (
        PARTITION p0 VALUES LESS THAN (6),
        PARTITION p1 VALUES LESS THAN (11),
        PARTITION p2 VALUES LESS THAN (16),
        PARTITION p3 VALUES LESS THAN (21)
    );

    这种分区方案:商店编号1-5的employee保存在分区p0,商店编号6-10的employee保存在分区p1,由此类推,每个分区都是按照顺序定义,这是PRATITION BY RANGE语法要求,类似JAVA的switch case语句,注意如果此时增加的employee的STORE_ID大于21,则会出现错误,可以通过设置分区的时候使用VALUE LESS THAN MAXVALUE子句。(MAXVALUE 表示最大的可能的整数值)

    MYSQL支持在设置分区使用VALUES LESS THAN 子句中使用一个表达式。比如使用日期作为RANGE分区的分区列,

    雇员表的store_id列已经被定义为非空(NOT NULL),当然除了使用STORE_ID来分割数据之外,还可以使用基于两个DATE (日期)中的一个的表达式来分割表数据。例如想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示:

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )
    PARTITION BY RANGE (YEAR(separated)) (
        PARTITION p0 VALUES LESS THAN (2010),
        PARTITION p1 VALUES LESS THAN (2014),
        PARTITION p2 VALUES LESS THAN (2018),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

    在RANGE分区中,分区键如果是NULL值会被当作是最小值来处理

    MYSQL5.1支持整数列分区,所以使用日期或者字符串的列进行分区的时候必须使用函数转换,查询的时候也需要函数转换后才能利用RANGE分区的特性来提高查询效率。MYSQL5.1分区的日期处理仅支持YEAR()和TO_DAYS(),

    MYSQL5.5改进了RANGE分区功能,提供了RANGE COLUMNS分区支持非整数分区,这时候使用日期分区就不需要通过函数转换了。MYSQL5.5分区日期处理上增加了TO_SECONDS()把日期编程秒,从而实现比天分区更细化的分区。

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )
    PARTITION BY RANGE (YEAR(separated)) (
        PARTITION p0 VALUES LESS THAN ('2010-01-01'),
        PARTITION p1 VALUES LESS THAN ('2014-01-01'),
        PARTITION p2 VALUES LESS THAN ('2018-01-01')
    );

    RANGE分区在如下场合特别有用:

    • 当需要删除过期数据时。只需简单地使用 ALTER TABLE employees DROP PARTITION p0来删除所有p0分区的数据。对于有大量行的表,这比运行一个如DELETE FROM employees WHERE YEAR(separated) <= 2019这样的一个DELETE查询要快的多。
    • 经常运行分区键的查询。如SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2012 GROUP BY store_id这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

    LIST分区

    LIST分区是建立离散的值列表,高速数据库特定的值属于哪个分区,LIST分区在很多方面类似RANGE,区别在LIST分区是从属于一个枚举列表的值的集合,RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用PARTITION BY LIST(expr)来实现,其中expr是某列值或一个基于某个列值返回一个整数值的表达式,然后通过VALUES IN (value_list)的方式来定义每个分区,其中value_list是一个通过逗号分隔的整数列表。与RANGE不同的是,LIST分区不必申明任何特定的顺序,如下:

    CREATE TABLE employees (
        id INT NOT NULL,
        name VARCHAR(30),
        category INT
    )
    PARTITION BY LIST(category)
        PARTITION pNorth VALUES IN (3,5,6,9,17),
        PARTITION pEast VALUES IN (1,2,10,11,19,20),
        PARTITION pWest VALUES IN (4,12,13,14,18),
        PARTITION pCentral VALUES IN (7,8,15,16)
    );

    在MYSQL5.1中,LIST分区只能匹配整数列表,CATEGORY只能是INT类型,所以需要额外的转换表来记录CATEGORY编号和名称,当然如果插入的列值或者分区表达式的返回值不包含在分区值列表中,则插入失败,LIST分区没有类似RANGE中的MAXVALUE的方式,插入的值在LIST列表中一定要存在。

    在MYSQL5.5中,LIST分区支持非整数列表,使用COLUMNS分区,如下:

    CREATE TABLE employees (
        id INT NOT NULL,
        name VARCHAR(30),
        category VARCHAR(30)
    )
    PARTITION BY LIST COLUMNS(category)
        PARTITION pNorth VALUES IN ('beijing','tianjin'),
        PARTITION pEast VALUES IN ('xian','xinjiang'),
        PARTITION pWest VALUES IN ('shanghai','suzhou','hefei','hangzhou'),
        PARTITION pCentral VALUES IN ('wuhan','zhenzhou')
    );

    COLUMNS分区

    COLUMNS分区实在MYSQL5.5中引入的分区类型,引入COLUMNS分区的目的是为了解决MYSQL5.5之前版本的RANGE分区和LIST分区只支持整数分区,需要通过额外函数计算得到整数或者额外的转换表转换成整数在分区,COLUMNS分区可以细分为RANGE COLUMNS分区和LIST COLUMNS分区,它们都支持整数、日期、字符串三大数据类型:

    所有整数类型:tinyint、smallint、mediumint、int、bigint,其他数值类型都不支持,如Decimal和Float

    日期时间类型:date、datetime

    字符类型:char、varchar、binary、varbinary,不支持text和blob类型作为分区键

    注意:COLUMNS分区仅支持一个或者多个字段名作为分区键,不支持表达式作为分区键。(和RANGE分区和LIST分区不同),如下使用多列组合的RANGE COLUMNS分区:

    ​CREATE TABLE employees (
        a INT ,
        b INT 
    )
    PARTITION BY LIST COLUMNS(a,b)(
        PARTITION p01 VALUES LESS THAN (0,10),
        PARTITION p02 VALUES LESS THAN (10,10),
        PARTITION p03 VALUES LESS THAN (10,20),
        PARTITION p04 VALUES LESS THAN (10,35),
        PARTITION p05 VALUES LESS THAN (10,MAXVALUE),
        PARTITION p06 VALUES LESS THAN (MAXVALUE,MAXVALUE)
    );
    ​

    需要注意的是,RANGE COLUMNS 分区键的比较是基于元组类型的比较,如下:

    当插入a=1,b=10,数据会被写入p02分区,即元组(1,10)<(10,10)

    当插入a=10,b=9,数据会被写入p02分区,即元组(10,9)<(10,10)

    当插入a=10,b=10,数据会被写入p03分区,即元组(10,10)<=(10,10)<(10,20)

    可以通过在数据库中直接查询来判断元组数据的大小:

    select (1,10) < (10,10) 

    其实RANGE COLUMNS分区键的比较(元组的比较)其实就是多列排序,先根据a字段排序再根据b字段排序,然后根据排序结果来分区存放数据,和RANGE单字段分区排序的规则其实是一致的。

    HASH分区

    HASH分区主要用来分散热点读,确保数据在预先确定个数的分区中尽可能平均分布,对一个表执行HASH分区时,MYSQL会对分区键应用一个散列函数,以此确定数据应当放在N个分区的那个分区中。MYSQL支持两种HASH分区:

    • 常规HASH分区
    • 线程HASH分区(LINEAR HASH分区),

    常规HASH分区使用取模算法,线性HASH分区使用一个线性的2的幂的运算法则;

    使用PARTITION BY HASH (expr) PARTITIONS num子句来创建一个HASH分区表empolyees,其中expr是某整数列值或者一个基于某列值返回一个整数的表达式。num 是一个非负的整数,它表示表分区的数量。默认值是1(不写PARTITIONS num时)

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )
    PARTITION BY HASH(store_id)
    PARTITIONS 4;

    上面的SQL是基于store_id列的HASH分区表,表被分成4个分区,这里的expr就是store_id,当新增一条记录的时候会根据算法:MOD(expr,num),即如果插入一个store_id=234的记录,则他会保存在N=MOD(234,4)=2分区中,即第二个分区。可以通过执行计划查询这条记录被保存在那个分区:

    explain partition select * from employees where store_id=234

    当然expr也可以是MYSQL中任意有效的函数或者表达式,只要返回一个返回一个既非常数、也非随机数的整数(既是变化的但又是确定的),需要注意的是,每当插入或更新(或者可能删除)一行,这个表达式都要计算一次,这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时候。同时MYSQL也不推荐使用涉及多列的哈希表达式。最有效率的哈希函数是只对单个表列进行计算,并且它的值随列值进行一致地增大或减小,因为这考虑了在分区范围上的“修剪”。也就是说,表达式值和它所基于的列的值变化越接近,MySQL就可以越有效地使用该表达式来进行HASH分区。换句话说,如果列值与表达式值之比的曲线图越接近由等式y=nx(其中n为非零的常数)描绘出的直线,则该表达式越适合于哈希。这是因为,表达式的非线性越严重,分区中数据产生非均衡分布的趋势也将越严重。

    常规HASH分区的弊端:

    常规HASH分区是通过取模的方式尽可能的平均分布数据到每个分区的,但是每当需要增加分区或者合并分区的时候,就会出现问题:在原来5个常规分区中,取模算法为N=MOD(expr,5),根据余数0-4判断数据保存在5个分区中的哪一个,但是当新增一个分区,此时的算法就会变成N=MOD(expr,6),根据余数0-5判断数据保存在6个分区中的哪一个,原来5个分区中的数据大部分需要重新计算重新分区,这样常规HASH分区在分区管理上的代价太大了,不适合灵活变动,为了降低分区管理的代价,MYSQL提供了线性HASH分区,分区函数是一个线性的2的幂的运算法则。

    线性HASH分区

    线性哈希分区和常规哈希分区在语法上的唯一区别在于,在PARTITION BY 子句中添加LINEAR关键字,如下:

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )
    PARTITION BY LINEAR HASH(YEAR(hired))
    PARTITIONS 4

    同样使用线性HASH时,计算分区时:假设分区编号为N,num表示非负的分区数量,N的计算步骤如下:

    1.  找到下一个大于num的2的幂,我们把这个值称为V ,V可以通过下面的公式得到:

    •  V = POWER(2, CEILING(LOG(2, num)))

    (假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)=4,则V = POWER(2,4)=16)

    2.    设置 N = F(column_list) & (V - 1),此时对于hired='2009-01-01'这个记录,N=2009 & (4-1) = 2

    3.    当 N >= num:

    •  设置 V = CEIL(V / 2)
    •  设置 N = N & (V - 1)

    此时hired='2009-01-01'这个记录的N=2 < 4,可以直接判断保存在第二个分区。 

    线性HASH分区的优点:在分区维护(包括增加、删除、合并、拆分分区)时,MYSQL能够处理的更加迅速,缺点是对于常规HASH分区的取模算法,线性HASH各个分区之间的数据分布不太均衡。

     

    KEY分区

    按照KEY进行分区类似于HASH分区,只不过HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MYSQL 服务器提供(不支持用户自定义表达式)。MySQL 簇(Cluster)使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。同时HASH分区只支持整数分区,而KEY分区支持使用除了BLOB和TEXT类型之外的其他类型的列作为分区键。

    通过PARTITION BY KEY(expr)的子句创建一个KEY分区表,expr是零个或者多个字段名的列表,如下:

    CREATE TABLE tk (
        col1 INT NOT NULL,
        col2 CHAR(5),
        col3 DATE
    ) 
    PARTITION BY LINEAR KEY (col1)
    PARTITIONS 3;

    与HASH分区不同,创建KEY分区表时,可以不指定分区键,默认会首先选择使用主键作为分区键,如下:

    CREATE TABLE tk (
        col1 INT NOT NULL,
        col2 CHAR(5),
        col3 DATE,
        PRIMARY KEY(col1)
    ) 
    PARTITION BY LINEAR KEY ()
    PARTITIONS 3;

    当然没有主键的情况下,会选择非空唯一键作为分区键。如果既没有主键也没有非空唯一键且没有指定分区键则会报错。

    在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,也就是LINEAR KEY分区时,分区的编号是通过2的幂算法得到,而不是通过模数算法。KEY分区和HASH分区类似,处理大量记录时,能够有效地分散热点。

     

    子分区

    子分区是分区表中队每个分区进行再次分割,又称为复合分区,MYSQL从MYSQL5.1开始支持对通过RANGE或者LIST分区的表再进行子分区,子分区既可以使用HASH分区又可以使用KEY分区,如下:

    CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE(YEAR(purchased))
        SUBPARTITION BY HASH(TO_DAYS(purchased))
        SUBPARTITIONS 2
        (
            PARTITION p0 VALUES LESS THAN (1990),
            PARTITION p1 VALUES LESS THAN (2000),
            PARTITION p2 VALUES LESS THAN MAXVALUE
        );

    ts表有3个分区,每个分区p0,p1,p2又被进一步分成2个子分区,实际上整个表被分成6个分区了,由于PARTITION BY RANGE子句的作用,第一和第二个分区只保存purchased列中值小于1990的记录,复合分区适用于保存非常大量的数据记录。当然也可以使用SUBPARTITION 子句来明确定义子分区也是可以的,如下:

    CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE(YEAR(purchased))
        SUBPARTITION BY HASH(TO_DAYS(purchased))
        (
            PARTITION p0 VALUES LESS THAN (1990)
            (
                SUBPARTITION s0,
                SUBPARTITION s1
            ),
            PARTITION p1 VALUES LESS THAN (2000)
            (
                SUBPARTITION s2,
                SUBPARTITION s3
            ),
            PARTITION p2 VALUES LESS THAN MAXVALUE
            (
                SUBPARTITION s4,
                SUBPARTITION s5
            )
        )

    需要注意的是:

    • 每个分区必须有相同数量的子分区。
    • 如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区,那么就必须定义所有的子分区。
    • 在每个分区内,子分区的名字必须是唯一的,但是在整个表中,没有必要保持唯一

    子分区可以用于特别大的表,在多个磁盘间分配数据和索引。假设有6个磁盘,分别为/disk0, /disk1, /disk2等。现在考虑下面的例子:

    CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE(YEAR(purchased))
        SUBPARTITION BY HASH(TO_DAYS(purchased))
        (
            PARTITION p0 VALUES LESS THAN (1990)
            (
                SUBPARTITION s0a 
                    DATA DIRECTORY = '/disk0' 
                    INDEX DIRECTORY = '/disk1',
                SUBPARTITION s0b 
                    DATA DIRECTORY = '/disk2' 
                    INDEX DIRECTORY = '/disk3'
            ),
            PARTITION p1 VALUES LESS THAN (2000)
            (
                SUBPARTITION s1a 
                    DATA DIRECTORY = '/disk4/data' 
                    INDEX DIRECTORY = '/disk4/idx',
                SUBPARTITION s1b 
                    DATA DIRECTORY = '/disk5/data' 
                    INDEX DIRECTORY = '/disk5/idx'
            ),
            PARTITION p2 VALUES LESS THAN MAXVALUE
            (
                SUBPARTITION s2a,
                SUBPARTITION s2b
            )
        )

    购买日期在1990年前的记录占了大量的存储空间,所以把它分为了四个部分进行存储,组成p0分区的两个子分区(s0a 和s0b)的数据和索引都分别用一个单独的磁盘进行存储。换句话说:

    o        子分区s0a 的数据保存在磁盘/disk0中。

    o        子分区s0a 的索引保存在磁盘/disk1中。

    o        子分区s0b 的数据保存在磁盘/disk2中。

    o        子分区s0b 的索引保存在磁盘/disk3中。

    ·         保存购买日期从1990年到1999年间的记录(分区p1)不需要保存购买日期在1990年之前的记录那么大的存储空间。这些记录分在2个磁盘(/disk4和/disk5)上保存,而不是4个磁盘:

    o        属于分区p1的第一个子分区(s1a)的数据和索引保存在磁盘/disk4上 — 其中数据保存在路径/disk4/data下,索引保存在/disk4/idx下。

    o        属于分区p1的第二个子分区(s1b)的数据和索引保存在磁盘/disk5上 — 其中数据保存在路径/disk5/data下,索引保存在/disk5/idx下。

    ·         保存购买日期从2000年到现在的记录(分区p2)不需要前面两个RANGE分区那么大的空间。当前,在默认的位置能够足够保存所有这些记录。

    MYSQL分区处理NULL值

    MYSQL不禁止分区间上使用NULL,分区键可能是一个字段或者一个用户定义的表达式,一般情况下MYSQL的分区都会把NULL当做零值,或者当最小值进行处理。在RANGE分区中NULL值会被当做最小值,LIST分区中NULL值必须出现在枚举中,否则不会接受,而HASH/KEY分区中,NULL会被当做零值处理。当然还是推荐在设置字段的时候设置非空约束或者默认值来避开MYSQL对NULL值的处理。

     

    分区管理

    RANGE和LIST分区

    RANGE和LIST分区的在添加、删除和重新定义分区的处理上非常相似,具体如下:

    删除分区:ALTER TABLE DROP PARTTION partition_name;

    CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
         PARTITION BY RANGE(YEAR(purchased))
            (
                    PARTITION p0 VALUES LESS THAN (1990),
                    PARTITION p1 VALUES LESS THAN (1995),
                    PARTITION p2 VALUES LESS THAN (2000),
                    PARTITION p3 VALUES LESS THAN (2005)
            );
    Query OK, 0 rows affected (0.01 sec)
     
    INSERT INTO tr VALUES
        (1, 'desk organiser', '2003-10-15'),
        (2, 'CD player', '1993-11-05'),
        (3, 'TV set', '1996-03-10'),
        (4, 'bookcase', '1982-01-10'),
        (5, 'exercise bike', '2004-05-09'),
        (6, 'sofa', '1987-06-05'),
        (7, 'popcorn maker', '2001-11-22'),
        (8, 'aquarium', '1992-08-04'),
        (9, 'study desk', '1984-09-16'),
        (10, 'lava lamp', '1998-12-25');
    Query OK, 10 rows affected (0.01 sec)                  
    
    /*
    可以通过使用下面的命令查看那些记录已经插入到了分区p2中:*/
    mysql> SELECT * FROM tr
        -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
    +------+-----------+------------+
    | id   | name      | purchased  |
    +------+-----------+------------+
    |    3 | TV set    | 1996-03-10 |
    |   10 | lava lamp | 1998-12-25 |
    +------+-----------+------------+
    2 rows in set (0.00 sec)

    删除p2这个分区:当删除了一个分区,也同时删除了该分区中所有的数据

    ALTER TABLE tr DROP PARTITION p2;
    Query OK, 0 rows affected (0.03 sec)

    可以通过重新运行前面的SELECT查询来验证:可以看出删除一个分区时会连同分区内的数据一起删除。

    SELECT * FROM tr WHERE purchased 
        BETWEEN '1995-01-01' AND '1999-12-31';
    Empty set (0.00 sec)

    此时如果新增一条记录purchased在1995-01-01 到1999-12-31之间的数据,这个数据会被放在p3分区,此时的p3分区的范围是1995-01-01 到2004-12-31;通过命令查看p3分区的记录增加了一个。

    SELECT 
        partition_name part, 
        parttion_expression expr, 
        partition_description descr, 
        table_rows 
    FROM information_schema.PARTITIONS 
    WHERE
    TABLE_SCHEMA =schema() 
    AND TABLE_NAME ='tr'

    通过执行一个SHOW CREATE TABLE命令,可以观察到表的分区结构是如何被改变的:

    SHOW CREATE TABLE tr
    
    Create Table: CREATE TABLE `tr` (
      `id` int(11) default NULL,
      `name` varchar(50) default NULL,
      `purchased` date default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 
    PARTITION BY RANGE (YEAR(purchased)) (
      PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 
      PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 
     
      PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
    )
    1 row in set (0.01 sec)

    删除LIST分区和RANGE分区语法完全一样,需要注意的是删除LIST分区会导致LIST分区不在包含被删除的分区的值列表,所以后续无法写入包含删除分区值列表的数据。而RANGE分区可能会由下一个分区兜底。

    如果希望从所有分区删除所有的数据,但是又保留表的定义和表的分区模式,使用TRUNCATE TABLE命令:

    如果希望改变表的分区而又不丢失数据,使用ALTER TABLE ... REORGANIZE PARTITION语句:

    增加分区:ALTER TABLE ... ADD PARTITION partition_name

    对于使用RANGE分区的表,可以用这个语句添加新的区间到已有分区列表的最大一段。我们添加一个purchased 在2005-01-01 AND 2009-12-31之间的记录

    ALTER TABLE ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010));

    注意的是:对于通过RANGE分区的表,只可以使用ADD PARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区,比如添加PARTITION p5 VALUES LESS THAN (1985),将会导致下面的一个错误

    错误1463 (HY000): 对每个分区,VALUES LESS THAN 值必须严格增长

    同理,LIST分区添加时不能添加一个包含现有分区值列表中任意值的分区,也就是说一个固定的分区键值必须制定唯一一个分区,否则也会报错,

    重新定义分区:ALTER TABLE ... REORGANIZE PARTITION

    重新定义分区就是在不丢失数据的情况下,重新编辑分区,以上面的tr表为例,将p0分区重新拆分成s0和s1分区

    ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
        PARTITION s0 VALUES LESS THAN (1985),
        PARTITION s1 VALUES LESS THAN (1990)
    );

    当然除了拆分一个RANGE分区为多个RANGE分区,还可以合并多个相邻的RANGE分区为一个RANGE分区或多个RANGE分区,注意只能合并相邻2个分区并且重新定义的分区区间必须与原来的分区区间覆盖相同,分区类型合并过程中不能变,不能把RANGE分区定义成HASH分区。

    ALTER TABLE tr REORGANIZE PARTITION s0,s1 INTO (
        PARTITION p0 VALUES LESS THAN (1990)
    );

    LIST分区的变更同理。

    HASH分区和KEY分区管理

    HASH分区和KEY分区在操作上非常相似,具体如下:

    减少分区:ALTER TABLE COALESCE PARTTION partition_name;(coalesce 合并的意思)

    CREATE TABLE clients (
        id INT,
        fname VARCHAR(30),
        lname VARCHAR(30),
        signed DATE
    )
    PARTITION BY HASH( MONTH(signed) )
    PARTITIONS 12;
    

    减少HASH分区数量,从12个减少到6个,使用

    ALTER TABLE clients COALESCE PARTITION 6;
    Query OK, 0 rows affected (0.02 sec)

    增加分区:ALTER TABLE  ADD PARTITION PARTITIONS num;num代表增加num个分区,而不是增加到num个分区

    ALTER TABLE clients ADD PARTITION PARTITIONS 4;

    对于KEY分区以及线性的HASH、KEY分区都是一样的,使用上面的命令来进行分数数量的控制。

     

    分表和分库

    分表和分区的区别

    实现方式上:mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完整的一张表,都对应三个文件(MyISAM引擎:一个.MYD数据文件,.MYI索引文件,.frm表结构文件)。

    数据处理上:分表后数据都是存放在分表里,总表只是一个外壳,存取数据发生在一个一个的分表里面。分区则不存在分表的概念,分区只不过把存放数据的文件分成了许多小块,分区后的表还是一张表,数据处理还是由自己来完成。

    提高性能上:分表后,单表的并发能力提高了,磁盘I/O性能也提高了。分区突破了磁盘I/O瓶颈,想提高磁盘的读写能力,来增加mysql性能。

    在这一点上,分区和分表的测重点不同,分表重点是存取数据时,如何提高mysql并发能力上;而分区呢,如何突破磁盘的读写能力,从而达到提高mysql性能的目的。

    实现的难易度上:分表的方法有很多,用merge来分表,是最简单的一种方式。这种方式和分区难易度差不多,并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。分区实现是比较简单的,建立分区表,跟建平常的表没什么区别,并且对代码端来说是透明的。

    分区的适用场景

    1. 一张表的查询速度已经慢到影响使用的时候。

    2.表中的数据是分段的

    3.对数据的操作往往只涉及一部分数据,而不是所有的数据

    CREATE TABLE sales (

        id INT AUTO_INCREMENT,

        amount DOUBLE NOT NULL,

        order_day DATETIME NOT NULL,

        PRIMARY KEY(id, order_day)

    ) ENGINE=Innodb

    PARTITION BY RANGE(YEAR(order_day)) (

        PARTITION p_2010 VALUES LESS THAN (2010),

        PARTITION p_2011 VALUES LESS THAN (2011),

        PARTITION p_2012 VALUES LESS THAN (2012),

    PARTITION p_catchall VALUES LESS THAN MAXVALUE);

    分表的适用场景

    1. 一张表的查询速度已经慢到影响使用的时候。

    2.当频繁插入或者联合查询时,速度变慢。

    分表的实现需要业务结合实现和迁移,较为复杂。
     

    展开全文
  • 分库分表

    2021-01-20 12:26:02
    # # 分库分表的几种形式  水平分库,本质是把相同的表放在不同的机器上。  垂直分库:本质是将多个表拆分到不同的机器上。  水平分表,本质是分表数据。  垂直分表: 本质是将不同的字段分成成多个表。 # # ...
  • 大厂都在使用的分库分表方案

    千次阅读 2020-11-15 16:37:51
    想必各位小伙伴在面试大厂的时候都遇到过这个面试题,要是提前有准备还好,没准备的话可能就被打了个措手不及,本文将从为什么使用分库分表技术、分库分表的基本概念、分库分表的好处以及分库分表的注意事项来聊一聊...

    想必各位小伙伴在面试大厂的时候都遇到过这个面试题,要是提前有准备还好,没准备的话可能就被打了个措手不及,本文将从为什么使用分库分表技术分库分表的基本概念分库分表的好处以及分库分表的注意事项来聊一聊这个话题。

    数据库架构演变

    刚开始我们只用单机数据库就够了,随后面对越来越多的请求,我们将数据库的写操作和读操作进行分离, 使用主库(Master)负责写,使用多个从库副本(Slaver Replication)负责读,从库从主库同步更新数据,保持数据一致。架构上就是数据库主从同步。从库可以水平扩展,所以更多的读请求不成问题。

    但是当用户量级上来后,写请求越来越多,该怎么办?加一个Master是不能解决问题的, 因为数据要保存一致性,写操作需要2个master之间同步,相当于是重复了,而且更加复杂。

    这时就需要用到分库分表(sharding),对写操作进行切分。

    为什么要使用分库分表

    在业务系统中关系型数据库(以MySQL为例)本身比较容易成为系统的瓶颈,比如单机存储容量、连接数、处理能力有限等等的原因非常容易让我们的系统变得“迟缓”,甚至崩溃。即使我们的数据库架构采用主从模式也依然存在这个问题,随着系统越来越复杂,不可能去用多主多从的模式,而且多主多从模式中数据一致性的问题处理起来比较棘手,所以也依然存在很多查询不理想的情况。总的来说,分库分表是为了解决数据库存储大量数据时出现的性能瓶颈。

    • IO瓶颈

      • 磁盘读IO瓶颈。由于热点数据太多,数据库缓存完全放不下,查询时会产生大量的磁盘IO,查询速度会比较慢,这样会导致产生大量活跃连接,最终可能会发展成无连接可用的后果。可以采用一主多从,读写分离的方案,用多个从库分摊查询流量。或者采用分库+水平分表(把一张表的数据拆成多张表来存放,比如订单表可以按user_id来拆分)的方案。
      • 第二种:磁盘写IO瓶颈。由于数据库写入频繁,会产生频繁的磁盘写入IO操作,频繁的磁盘IO操作导致产生大量活跃连接,最终同样会发展成无连接可用的后果。这时只能采用分库方案,用多个库来分摊写入压力。再加上水平分表的策略,分表后,单表存储的数据量会更小,插入数据时索引查找和更新的成本会更低,插入速度自然会更快。
    • CPU瓶颈

      • SQL问题。如果SQL中包含join,group by,order by,非索引字段条件查询等增加CPU运算的操作,会对CPU产生明显的压力。 这时可以考虑SQL优化,创建适当的索引,也可以把一些计算量大的SQL逻辑放到应用中处理。
      • 单表数据量太大。由于单张表数据量过大,比如超过一亿,查询时遍历树的层次太深或者扫描的行太多,SQL效率会很低,也会非常消耗CPU。这时可以根据业务场景水平分表。
    分库分表的基本概念

    分库分表的方式有垂直切分水平切分两种

    • 垂直分库
      在这里插入图片描述

    以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。每个库的结构都不一样,每个库的表结构也不一样,并且没有交集,最后将不同的库部署在不同的服务器上。数据库业务层面的拆分,和服务的“治理”,“降级”机制类似,也能对不同业务的数据分别的进行管理,维护,监控,扩展等。 数据库往往最容易成为应用系统的瓶颈,而数据库本身属于“有状态”的,相对于Web和应用服务器来讲,是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限,在高并发场景下,垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

    • 垂直分表
      在这里插入图片描述

    以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。一般是将热点数据作为主表,这样更多的热点数据就能被缓存下来,进而减少了随机读IO。将不常用的, 数据较大,长度较长(比如text类型字段)的拆分到“扩展表“。但是查询的时候不要去join表,因为join会增加CPU的负担,而且join必须两个表在同一台实例上。正确的做法是:分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

    • 水平分库

    在这里插入图片描述
    以字段为依据,按照一定策略(RANGE、HASH取模等),将一个库中的数据拆分到多个库中。每个库的结构都一样;每个库的数据都不一样,没有交集;所有库的并集是全量数据。一般水平分库后,多个数据库是部署到不同服务器上,这样能够有效的缓解单机和单库的性能瓶颈和压力,突破IO、连接数、硬件资源等的瓶颈。

    • 水平分表

    在这里插入图片描述
    针对数据量巨大的单张表(比如订单表),按照某种规则(RANGE、HASH取模等),切分到多张表里面去。 但是这些表还是在同一个库中,所以数据库操作还是有IO瓶颈,不建议采用。

    分库分表存在的问题

    当然任何解决方案方案都是有利有弊,我们应该尽量发挥它的优势,分库分表能带来的好处也是显而易见的:分库分表之后可以减少单库IO/CPU的瓶颈,解决了单表访问慢的问题,但是也不能够忽略它的劣势。

    • 跨库join的问题

    分库分表之后因为不同的表在不同的库中,我们就无法join不同库中的表。通常的解决方案是分别查询不同库中不同的表,然后在service中聚合起来。

    • 事物的问题

    因为分库分表之后不同的业务表全都分散开来了,所以也就演变成了分布式事务,那么就要对应的解决方案来处理,常见的TCC、2PC来解决。

    • 唯一ID的问题

    分库分表之后我们就不能再使用数据库自增ID,因为非常容易发生ID重复的问题。业内常见的解决方案有:UUID、雪花算法等。

    • 新老数据兼容的问题

    通常来说分库分表改造完成之后,新来的数据可以直接写入新的数据来源,而旧的数据可以通过DBA团队来帮忙处理,一点一点的清洗数据,最终同步到新的数据源。

    展开全文
  • 亿级数据的分库分表

    2020-03-25 22:01:07
    一、分库分表的基本概念 当表的数量或大小超过一定限度,对业务效率产生了一定的影响,可以考虑分库分表。阿里的推荐是,当数据库超过500万行或者单张表大小超过2G,建议分库分表。 二、好处 减少数据库的压力; ...

    一、分库分表的基本概念

    当表的数量或大小超过一定限度,对业务效率产生了一定的影响,可以考虑分库分表。阿里的推荐是,当数据库超过500万行或者单张表大小超过2G,建议分库分表。

    二、好处

    • 减少数据库的压力;
    • 提高查询的效率;

    三、分库分表的两种方式

        1、 垂直方式:将一个多字段的表,拆分成多个有关联的表;

        2、 水平方式:将表按行数进行拆分。

    四、问题解决层次

    五、其他

        1、 避免跨库的关联查询的方式:库表字段冗余、数据同步、RPC(不推荐)

        2、 全局ID生成的方式:数据库(for update)、UUID、Redis、ZK、雪花算法、Leaf

     

    展开全文
  • 分库分表 一、为什么要分库分表 单机mysql带来的问题: ...二、分库分表的中间件 数据库中间件:用来做数据的分发。 有两种:proxy类和client类 cobar、TDDL、sharding-jdbc、atlas、mycat。 cobar

    分库分表

    一、为什么要分库分表

    单机mysql带来的问题:
    1、mysql单机,扛不住并发
    2、mysql单机磁盘容量快满
    3、mysql单表数据量太大,sql执行越跑越慢
    利用分库分表将单机拆分成多机,带来的好处。
    1、可承受的并发增加数倍;
    2、磁盘容量增加数倍,使用率降低;
    3、单表数据量缩小,sql语句执行速度加快。

    二、分库分表的中间件

    数据库中间件:用来做数据的分发。
    有两种:proxy类和client类
    在这里插入图片描述
    cobar、TDDL、sharding-jdbc、atlas、mycat。
    cobar :阿里开源,代理中间件,不支持读写分离,存储过程、跨库join和分页等,已经没啥人用了。
    TDDL:淘宝,client。不支持join、多表查询,只支持curd,读写分离,用的人不多。
    atlas:360开园,proxy。用的人不多。
    sharding-jdbc:当当开源 client。sql语法支持的多,支持分库分表、读写分离、分布式id生成等等。大部分公司的选择。不要部署,运维成本低,但是各个系统都要耦合sharding-jdbc依赖。
    mycat:proxy方案。势头很猛。

    二、垂直拆分和水平拆分

    水平拆分:一个表的数据分到多个库的多个表里去,表结构相同,数据不同。
    垂直拆分:把一个表的字段拆成多个表。每个库表结构都不一样。

    根据主键拆分,根据时间拆分等。

    三、单库单表 -> 多库多表 如何迁移?

    (1)、长时间停机分库分表
    第一步,停掉系统,拒绝访问,如晚上12点到凌晨3点进行维护,无法访问。
    第二步,启动后台临时程序,从旧的数据库里读取数据,基于分库分表中间件,将数据分离到其他库表中去。
    第三步,修改系统配置,通过数据库中间件,将数据写入到新库中。
    在这里插入图片描述
    缺点:
    1、一定会停机几个小时。
    2、时间很赶,如果失败,回滚代码。

    (2)、不停机双写方案
    第一步,修改系统中写库代码,同时写入到老库和新的分库分表。
    第二步,编写一个后台迁移工具,从老的库里读数据,写入到新库中去
    第三步,迁移完成之后,去比较一下新旧库表的数据,如果一模一样,则迁移完成,否则继续执行迁移。
    第四步,新旧库表数据无差异之后,将代码中写入旧的数据库代码删掉,只写新库。
    在这里插入图片描述

    展开全文
  • 分表的好处:提高数据操作的效率:降低写入、更新、删除(一般项目中不会对数据库中的数据物理删除,只会做逻辑删除)时候建立索引的开销。提高运行时候的效率,提高并发量。 分库分表带来的挑战 分布式id的创建...
  • MySQL(7)分库分表

    2020-03-20 19:44:38
    一、分库分表理论篇 什么是分库分表? 垂直分库 把数据库按照业务分库 垂直分表 将表按照列进行拆解 水平分表 为什么要搞分库分表数据库表太多----分库----微服务(应用和数据库都是单独,比如说用户...
  • MySQL分库分表

    2016-08-06 16:36:00
    MySQL分库分表【转】http://blog.itpub.net/29254281/viewspace-1819422/ ...这样做的好处是拆分简单,并且没有破坏数据库事务.但是随着业务的增长,比如图书分类的订单数据表已经到达了10个T的规模.就需要考虑做...
  • 做数据热备,在主数据库故障后,从可以继续工作,避免数据丢失; 提高业务支撑能力,单机扩为多机,分担系统压力; 读写分离,更好并发支持能力。 二、主从形式 一主多从 主主复制 多主一从 联级复制(即...
  • 分库分表就是为了解决由于数据来量过大而导致数据库性能降低问题,将原来独立的数据库拆分成若干个数据库组成,将数据大表拆分成若干个数据表组成,使得单一数据库、单一数据表数据量变小,从而达到提高数据库...
  • 分库分表的好处 1.提升数据的访问速度 2.缓解数据库的压力 3.提升存储容量 消费金融核心系统 客户、账户、商户、产品、放款、还款、合同、信审、资金 拆分这些模块,单独开发,部署 单张表数据量超载 数据水平...
  • 下面从数据库的读写分离和分库分表两方面讲讲如何进行数据库优化 数据库的读写分离 基本原理:让主数据库处理事务性查询,从数据库处理Select查询,当然主数据库也可以提供查询服务。读写分离最大作用是降低...
  • 其实这是分库分表之后你必然要面对一个问题,需要一个全局唯一 id 来支持 一.全局唯一id–基于数据库的实现方案 1.1 数据库自增 id 这个就是说你系统里每次得到一个 id,都是往一个库一个表里插入一条没...
  • 垂直分库: 微服务中盛行, 每个业务子系统有自己独立的数据库好处:结构清晰 缺点:增加系统复杂度,需要各个系统对外暴露接口。 水平切分: 水平分表: 表结构一致。 将原有记录按一定规律拆分到多...
  • 拿到这个id之后再往对应的分库分表里去写入。  这个方案的好处就是方便简单;缺点就是单库生成自增id,要是高并发的话,就会有瓶颈的;  适合的场景:你分库分表就俩原因,要不就是单库并发太高,要不就是单库...
  • 垂直分库:解决时表过多问题 垂直分表:解决单表列过多问题 水平切分 大数据表拆成小表 常见拆分策略: 垂直拆分: 考虑因素包括技术支撑, 业务是否有必要, er分片(相关联表放在同一个库中,...
  • 分库分表之后,id 主键如何处理? 面试官心理分析 其实这是分库分表之后你必然要面对一个问题,就是 id 咋生成?因为要是分成多个表之后,每个表都是从 1 开始累加,那肯定不对啊,需要一个全局唯一 id 来支持...
  • MySQL分库分表——保持数据一致性

    千次阅读 2018-05-07 15:48:04
    MySQL分库分表 分类: MySQL2015-10-30 21:58:33MySQL处理大规模业务数据的方案一般都是分库分表.最开始一般都选择垂直拆分.比如电商网站,可能按照家电,图书,母婴等商品分类进行拆分.这样做的好处是拆分简单,并且...
  • 分库分表SQL优化

    2017-05-24 17:10:59
    分库拆表: 好处: 1. 数据库容量问题; 2. 解决性能压力的最优选择;...将高安全性数据与低安全性数据分库,这样的好处第一是便于维护,第二是 高安全性数据的数据库参数配置可以以安全优先,而低安...
  • 分库分表原因一般是:1、单库并发过高2、单库数据量过大 一、什么是主从复制? 主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库; 二、主从复制作用/好处 1、做数据热备,主数据库...
  • 分库、垂直分表:分散系统负载,让原来一台机器做事,变成几台机器来做。 水平分表、分区:缩小索引大小,使查找更快。 使用场景 读写分离:打开一个帖子内容页,需要select帖子表,和帖子评论表,每个耗时10ms...
  • 拿到这个 id 之后再往对应的分库分表里去写入。 这个方案的好处就是方便简单,谁都会用;缺点就是单库生成自增 id,要是高并发的话,就会有瓶颈的;如果你硬是要改进一下,那么就专门开一个服务出来,这个服务每次...
  • 拿到这个 id 之后再往对应的分库分表里去写入。 这个方案的好处就是方便简单,谁都会用;缺点就是单库生成自增 id,要是高并发的话,就会有瓶颈的;如果你硬是要改进一下,那么就专门开一个服务出来,这个服务每次...
  • 现有互联网业务模式下,数据库分库分表已经成为解决数据库瓶颈一个普遍解决方案。分库分表有多种好处,比如高容量、大并发等,但是在拆分过程中也引入了一些使用限制,比如多维查询,非拆分键查询请求会分发...
  • 拿到这个id之后再往对应的分库分表里去写入。 这个方案的好处就是方便简单,谁都会用;缺点就是单库生成自增id,要是高并发的话,就会有瓶颈的;如果你硬是要改进一下,那么就专门开一个服务出来,这个服务每次就...
  • 使用中间件的好处  使用中间件对于主读写分离新增一个从数据库节点来说,可以不用修改代码,达到新增节点数据库而不影响到代码的修改。因为如果不用中间件,那么在代码中自己是先读写分离,如果新增节点, 你进行...

空空如也

空空如也

1 2 3 4
收藏数 73
精华内容 29
关键字:

数据库分库分表的好处