• MySQL优化表分区


     转载于:https://www.bilibili.com/video/BV1E7411q7Nx?p=1

    一、RANGE分区

    • 基于属于一个给定连续区间的列值,把多行分配给分区。
    • 这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。
    -- RANGE分区案例
    create table t_student
    (
        id varchar(50) not null,
        name varchar(20),
        age int,
        primary key (id,age)
    )
    partition by range(age)
    (
        partition p01 VALUES less than (10),
        partition p02 VALUES less than (20),
        partition p03 VALUES less than (30),
        partition p04 VALUES less than (MAXVALUE)
    );

    二、LIST分区

    • 类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
    • LIST分区通过使用"PARTITION BY LIST(expr)"来实现,其中"expr" 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过"VALUES IN (value list)"的方式来定义每个分区,其中"value list"是一个通过逗号分隔的整数列表。
    -- LIST分区案例
    -- LIST分区案例对于一个综合性的网店来说,商品分为诸多种类。我们可以按照商品ID进行range分区,也可以按照商品的类型划分分区。在这个例子中, LIST分区给了我们更多的选择。
    create table t_commodity 
    (
        id varchar(50) not null,
        cid int,
        name varchar(20),
        date datetime
    )
    partition by list (cid) 
    (
        partition po1 values in (1,2,3),
        partition po2 values in (4,5,6), 
        partition po3 values in (7,8,9) 
    );    

    三、HASH分区

    • 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
    • 要使用HASH分区来分割一个表,要在CREATE TABLE语句上添加一个"PARTITION BY HASH (expr)"子旬,其中"expr"是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外,你很可能需要在后面再添加一个"PARTITIONS num"子句,其中num是一个非负的整数,它表示表将要被分割成分区的数量。如果没有"PARTITIONS num"子句,那么分区的数量将默认为1。
    • 使用HASH分区的优点在于数据分布较为均匀。
    -- HASH分区案例
    -- 另外,在MySQL Cluster中,分区行为是自动的。默认情况下,分区的数量和ndb node数量相同。通常在节点数很多的情况下,会通过配置分区数和node group搭配进行调整。
    create table t_company1 
    (
      id varchar(50) not null,
      cid int,
      name varchar(20),
      date datetime
    ) 
    partition by hash (cid)
    partitions 4;

    四、LINEAR HASH分区

    • 线性与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two )运算法则,而常规哈希使用的是求哈希函数值的模数。
    • 按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1T)数据的表。不过, MysQL的线性哈希算法导致相比较常规哈希,数据可能分布的不那么均衡,容易产生"hotspot nodes"。
    • 关于LINEAR HASH算法参见MySQL官方文档:http://dev.mysql.com/doc/refman/5.1/zh/partitioninq.html#partitioning-linear-hash
    -- LINEAR HASH分区案例
    -- 线性哈希分区和常规哈希分区在语法上的唯一区别在于,在"PARTITION BY" 子句中添加"LINEAR"关键字。
    create table t_company2 
    (
      id varchar(50) not null,
      cid int,
      name varchar(20),
      date datetime
    ) 
    partition by linear hash (cid)
    partitions 4;

    五、KEY分区

    • 按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MysQL服务器提供。MySQL簇( Cluster )使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
    -- KEY分区案例
    create table t_company3 
    (
      id varchar(50) not null,
      cid int,
      name varchar(20),
      date datetime
    ) 
    partition by linear key (cid)
    partitions 4;

    六、多列分区

    • COLUMNS关键字允许字符串和日期列作为分区定义列,同时还允许使用多个列定义一个分区。
    -- 多列分区案例
    create table t_order
    (
        a int,
        b int,
        c int
    )
    partition by range columns(a,b)
    (
        partition p01 values less than (10,10),
        partition po2 values less than (10,20),
        partition p03 values less than (10,30), 
        partition p04 values less than (10,maxvalue),
        partition p05 values less than (maxvalue,maxvalue)
    );
    -- 多列分区案例 -- 多列分区案例第一个分区用来存储雇佣于1990年以前的女职员,第二个分区存储股用于1990-2000年之间的女职员,第三个分区存储所有剩下的女职员。对于分区p04到p06 ,我们策略是一样的,只不过存储的是男职员。最后一个分区是控制情况。 create table t_emplovees ( emp_no int, birth_date datetime, first_name varchar(20), last_name varchar(20), gender char(1), hire_date datetime ) engine=myisam partition by range columns(gender,hire_date) ( partition p01 values less than ('F','1990-01-01'), partition p02 values less than ('F','2000-01-01'), partition p03 values less than ('F',maxvalue), partition p04 values less than ('M', '1990-01-01'), partition p05 values less than ('M','2000-01-01'), partition p06 values less than ('M',maxvalue), partition p07 values less than (maxvalue,maxvalue) );

    七、子分区

    • 子分区是分区表中每个分区的再次分割。子分区可以用于特别大的表,在多个磁盘间分配数据和索引。
    -- 子分区案例
    create table t_order_details 
    (
       id int, 
       udate datetime
     )
     partition by range(year(udate))
     subpartition by hash(to_days(udate))
     subpartitions 2
     (
       partition po values less than (1990),
       partition p1 values less than (2000),
       partition p2 values less than maxvalue
     )
    -- 子分区案例
    -- 将每个子分区保存在不同的存储上,优化1/0性能。
    -- 注释:data directory 后跟的是数据存放的文件位置,index directory后跟的是索引存放的文件位置
    create table t_order_content
    (
        id int,
        udate datetime
    )
    partition by range(year(udate))
    subpartition by hash(to_days(udate))
    (
      partition p01 values less than (1990)
       (
         subpartition s0
         data directory ='/var/a/data'
         index directory = '/var/a/idx',
         subpartition s1
         data directory = '/var/b/data'
         index directory = '/var/b/idx'
       ),
       partition p02 values less than (2000)
       (
      subpartition s2
      data directory ='/var/c/data'
      index directory = '/var/c/idx',
      subpartition s3
      data directory = '/var/d/data'
      index directory = '/var/d/idx'
      )
    );
  • 相关阅读:
    java并发编程
    Linux C++服务器端进程SDK
    定时器SDK
    Redis3.0集群方案分析
    DES
    生成验证码
    MVC Ajax.BeginForm 提交上传图片
    EF LIKE 查询
    JS 60秒后重发送验证码
    EF 指定字段修改
  • 原文地址:https://www.cnblogs.com/nginxTest/p/15639218.html
Copyright © 2020-2023  润新知