MySQL性能优化分区

简述

MySQL采用分区的优点：

1.和单个磁盘或单个文件系统比较，可以存储更多的数据。

2.优化查询，采用‘分而治之’的思想，例如在where子句中包含分区条件时，可以只扫描必要的一个或几个分区，避免全表扫描带来的性能缺失。此外，在使用SUM和COUNT等聚合函数时，可以采用归并的思想，只需汇总每一个分区的查询结果来提高效率。

3.对于过期或垃圾数据的清除，可以通过删除特定分区来减少查询。

特别注意:

1.大部分存储引擎(InnoDB, MyISAM, Memory等)均支持分区，MERGE和CSV不支持分区；

2.可以使用SHOW VARIABLES LIKE '%partition%'(版本在5.6以下，5.6以上使用SHOW PLUGINS)来查看当前版本的MySQL是否支持分区；

3.同一个分区表必须使用同一个存储引擎，但是对同一个MySQL服务器中，同一个数据库中对不同分区表使用不同的存储引擎；

4.MySQL分区适用于一个表的所有数据和索引，不能只对其数据(或索引)进行分区。分区表上创建的所有一定是LOCAL索引。

分区类型

RANGE	对分区字段限定区间范围来分配分区, 例如`LESS THAN (100)`
LIST	对分区字段使用枚举集合来分配分区，例如 `IN (1, 2, 3)`
HASH	对分区字段执行哈希算法，得到其在分区表列表的索引，实现分散热点数据
KEY	类似HASH分区，不支持自定义表达式作为分区键。

RANGE分区

格式：

# 建表
CREATE TABLE emp(
    # 字段集
) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 默认配置
PARTITION BY RANGE (分区字段) (
    PARTITION 分区名称 VALUES LESS THAN (值1),
    PARTITION 分区名称 VALUES LESS THAN (值2),
    PARTITION 分区名称 VALUES LESS THAN (max_value)
);

例子，对emp表进行分区，分区参照字段为stored_id。

CREATE TABLE emp(
    id INT NOT NULL,
    ename VARCHAR(20),
    job VARCHAR(30),
    stored_id INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (stored_id) (
    PARTITION p01 VALUES LESS THAN (10),
    PARTITION P02 VALUES LESS THAN (20),
    PARTITION p03 VALUES LESS THAN (30)
);

emp表中分区字段为stored_id，其最高分区为p03，接收的最大值为30，所以当进行INSERT操作，若stored_id>30则会因为找不到分区而出错。

mysql> insert into emp values(3, "123", "worker", 33);
ERROR 1526 (HY000): Table has no partition for value 33

查看表分区信息：

查看当前表分区的信息是在information_schema的partition表中得到的，schema()函数获取当前数据库库实例

SELECT
    partition_name,
    partition_expression,
    partition_description,
    table_rows
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = SCHEMA()
    AND TABLE_NAME = '表名';

+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p01            | stored_id            | 10                    |          0 |
| P02            | stored_id            | 20                    |          0 |
| p03            | stored_id            | 30                    |          0 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.00 sec)

注意：

1.在RANGE分区中，分区键如果是NULL值则会被当成最小值来处理

2.RANGE分区支持整数列分区，如果想要对日期列或字符串列进行分区的话，就需要使用函数来讲其他类型转换为整数类型，例如YEAR()、TO_DAYS()、TO_SECONDS()函数能够将时间转换为整数，CONVERT('12345', SIGNED)、CAST('12345', SIGNED)实现字符串向数字的转换

3.若要删除过期数据可以通过ALTER TABLE [表名] DROP PARTITION [分区名]来实现。

LIST分区

LIST分区通过使用PARTITION BY LIST(expr)子句实现，expr是某列或某一个基于某列的函数表达式，该表达式总是返回一个整型数据，最后通过VALUES IN (values_list)的方式来定义分区，其中value_list是一个整型列表，与RANGE分区不同之处在于不必按照特定的顺序，

格式：

# 建表
CREATE TABLE xxx(
    # 字段
) ENGINE=InnoDB DEFAULT xxxx
PARTITION BY LIST(expr)(
    PARTITION p1 VALUES IN (1, 2, 4),
    PARTITION P2 VALUES IN (3, 5, 7),
    PARTITION p3 VALUES IN (6)
);

如果视图插入的列值不包含在values_list中会报错。

COLUMNS分区

此外，COLUMNS分区支持多列字段作为复合分区键。大致和order by后跟两个字段的意思相似，但是不支持表达式作为分区键

CREATE TABLE t1(
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p_t1_1 VALUES LESS THAN (0, 10),
    PARTITION P_t1_2 VALUES LESS THAN (10, 10),
    PARTITION P_t1_3 VALUES LESS THAN (20, 20),
    PARTITION P_t1_4 VALUES LESS THAN (25, 30)
);

例如，此时如果插入a、b分别为1,15, 就会被插入到P_t1_3分区表中，(1,10)会被插入到P_t1_2中。

HASH分区

使用 PARTITION BY [linear] HASH (expr) PARTITIONS p_num, 其中p_num为分区个数，linear为可选字，加上linear则视为线性分区，就不在采用取模算法来确定数据的分区位置，而是采用线性的2的幂的算法。

CREATE TABLE T2(
    ID INT NOT NULL
)
PARTITION BY HASH (ID) PARTITIONS 4;

例如插入一条id=234的值到T2表中，他将会被存储到p2表中。这里采用的是

mysql> select partition_name, partition_expression, table_rows from information_schema.partitions where table_schema=schema() and table_name='t2';
+----------------+----------------------+------------+
| partition_name | partition_expression | table_rows |
+----------------+----------------------+------------+
| p0             | ID                   |          0 |
| p1             | ID                   |          0 |
| p2             | ID                   |          1 |
| p3             | ID                   |          0 |
+----------------+----------------------+------------+
4 rows in set (0.00 sec)

取模：

假设分区总数量为num, 分区编号为N，则N=MOD(expr, num)，其中expr代表分区键。

线性的2的幂的运算法则：

假设分区总数量为num=4, 分区编号是N, 插入的分区键是234

其次要用到几个数学函数：POWER(x, y)代表X的y次方, CEILING(x)代表x向上取整

第一步，获取下一个大于或等于num的2的幂m，再获得2^m，这个值设置为V：即V=Power(2, Ceiling(Log(2, num)))

2^x >= num -> x=2

2的幂的值就是 x^2 = 4

第二步，设置N=F(columns_list)&(V-1) , F(columns_list)是分区键的键值234。

        N = 234&3=11101010&11 = 2

Key分区

格式

CREATE TABLE xx(
    # 字段
)
PARTITION BY KEY (expr) PARTITIONS P_NUM

p_num依然是分区个数，是一个非负整数。

分区