mysql的表分区

mysql 分区表介绍

在工作中遇到这样一个问题，mysql中的一张存放数据的表很大，造成查询非常的缓慢，利用分区表解决了这个问题。
分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。
MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

判断当前Mysql是否支持分区

MySQL数据库在5.1版本时添加了对分区的支持

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql的分区类型

1. range分区

这个是最常用的分区，也是我这次用的分区类型。表的字段如下：

MariaDB [zabbix]> desc history;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | double(16,4)        | NO   |     | 0.0000  |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+

表分区我按照clock字段进行分区，每一天的数据分为一个区，目前分了后面90天的分区，分区shell脚本如下：

#!/bin/bash

#配置环境变量
SERVICEROBOT_USER="root"
SERVICEROBOT_PWD="654321"
SERVICEROBOT_DB="zabbix"
SERVICEROBOT_PORT="3306"
SERVICEROBOT_HOST="localhost"
MYSQL_BIN="mysql"

#历史数据保留时间，单位天
HISTORY_DAYS=90

#趋势数据保留时间，单位月
TREND_MONTHS=12

HISTORY_TABLE="history history_log history_str history_text history_uint"
TREND_TABLE="trends trends_uint"

#MYSQL连接命令
MYSQL_CMD=$(echo ${MYSQL_BIN} -u${SERVICEROBOT_USER} -p${SERVICEROBOT_PWD} -P${SERVICEROBOT_PORT} -h${SERVICEROBOT_HOST} ${SERVICEROBOT_DB})
echo ${MYSQL_CMD}


EXISTED_DATA_PARTITION_SQL=()
PARTITION_SQL=""


function create_partitions_history_with_existed_data() {
	#existed data
	index=0
	for((item=${HISTORY_DAYS};item>=0;item--));do
		PARTITIONS_CREATE_EVERY_DAY=$(date -d "${item} day ago" +"%Y%m%d")
		TIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_DAY} 23:59:59)" +%s)
		EXISTED_DATA_PARTITION_SQL[((index++))]="$(echo "PARTITION p${PARTITIONS_CREATE_EVERY_DAY}  VALUES LESS THAN (${TIME_PARTITIONS})")"
	done

	#future data
	for((item=1;item<=${HISTORY_DAYS};item++));do
		PARTITIONS_CREATE_EVERY_DAY=$(date +"%Y%m%d" --date="${item} days")
		TIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_DAY} 23:59:59)" +%s)
		EXISTED_DATA_PARTITION_SQL[((index++))]="$(echo "PARTITION p${PARTITIONS_CREATE_EVERY_DAY}  VALUES LESS THAN (${TIME_PARTITIONS})")"
	done
	
	for((item=0;item<=${HISTORY_DAYS};item++));do
		echo ${EXISTED_DATA_PARTITION_SQL[((item))]}
		if [ "${item}" == "0" ];then
			PARTITION_SQL=$(echo "${EXISTED_DATA_PARTITION_SQL[((item))]}")
		else
			PARTITION_SQL=$(echo "${PARTITION_SQL},${EXISTED_DATA_PARTITION_SQL[((item))]}")
		fi
	done;

	SQL2=$(echo "ALTER TABLE history PARTITION BY RANGE( clock ) (${PARTITION_SQL});")
	echo "=======================================begin do partition: ${TABLE_NAME}======================================"
	echo ${SQL2}
    RET2=$(${MYSQL_CMD} -e "${SQL2}")
	echo "=======================================end do partition: ${TABLE_NAME}======================================"

}
create_partitions_history_with_existed_data

RANGE分区存在的问题：

1. range范围覆盖问题：当插入的记录中对应的分区键的值不在分区定义的范围中的时候，插入语句会失败。

解决办法

1. 预估分区键的值，及时新增分区

2. List分区

List分区是建立离散的值列表告诉数据库特定的值属于哪个分区。 语法：

partition by list(exp)( //exp为列名或者表达式
        partition p0 values in (3,5)  //值为3和5的在p0分区
    )

例：

mysql> create table emp1(
    -> id int not null,
    -> store_id int not null
    -> )
    -> partition by list(store_id)(
    ->   partition p0 values in (3,5),
    ->   partition p1 values in (2,6,7,9)
    -> );

注意 如果插入的记录对应的分区键的值不在list分区指定的值中，将会插入失败。

3. Hash分区

Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。 MySQL支持两种Hash分区:常规Hash分区和线性Hash分区。 A. 常规Hash分区:使用取模算法 语法：

partition by hash(store_id) partitions 4;
上面的语句，根据store_id对4取模，决定记录存储位置。 比如store_id = 234的记录，MOD(234,4)=2,所以会被存储在第二个分区。

常规Hash分区的优点和不足 优点：能够使数据尽可能的均匀分布。 缺点：不适合分区经常变动的需求。假如我要新增加两个分区，现在有6个分区，那么MOD(234,6)的结果与之前MOD(234,4)的结果就会出现不一致，这样大部分数据就需要重新计算分区。

4. Key分区

类似Hash分区，Hash分区允许使用用户自定义的表达式，但Key分区不允许使用用户自定义的表达式。Hash仅支持整数分区，而Key分区支持除了Blob和text的其他类型的列作为分区键。 语法

partition by key(exp) partitions 4;//exp是零个或多个字段名的列表
key分区的时候，exp可以为空，如果为空，则默认使用主键作为分区键，没有主键的时候，会选择非空惟一键作为分区键。

分区对NULL值的处理

1. range分区： NULL值被当作最小值来处理
2. List分区中：NULL值必须出现在列表中，否则不被接受
3. Hash/Key分区中：NULL值会被当作零值来处理

数据库引擎对分区的影响

注意：引擎是innodb, 在innodb数据库引擎中要把分区技术做成功必须设置表为独立表空间特别注意这点。
开启innodb数据库引擎独立表空间。
编辑my.cnf 增加innodb_file_per_table=1

[root@localhost test]# vi /etc/my.cnf
[mysqld]
innodb_file_per_table=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=2000

分区管理

分区管理包括对于分区的增加，删除，以及查询

• 增加分区

对range/list分区：  alter table table_name add partition (partition p0 values ...(exp))
对hash/key分区：    alter table table_name add partition partitions 8;  指的是新增8个分区

• 删除分区

对range/list分区：  alter table table_name drop partition p0; //p0为要删除的分区名称
对hash/key分区：    alter table table_name coalesce partition 2; //将分区缩减到2个

• 查询分区

select partition_name,table_rows from information_schema.partitions where table_name="history";