MySql性能优化—分区

1 什么是分区？

对外只展示一张表，但是表内部分区到不同的磁盘上，只需要其中一部分数据的时候可直接映射相应的区进行查找，避免了全表扫描，提升了查找、插入、删除数据的性能。一般是数据库层面实现的，如下图所示：

2 为什么要分区？

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象碧昂城一些小对象。

3 如何分区？

3.1 分区类型

水平分区：例如10万条数据按照年份来分，可以分为很多分区，10年的数据，11年的数据，12年的数据分区...

垂直分区：例如一个大表，一些blob和text字段不经常被访问，就可以将这些不经常使用的列划分到另一个分区

3.1 常见的分区

range分区、list分区、hash分区、key分区

3.1.1 range分区：

基于一个给定的连续区间范围(区间要求连续并且不能重叠)，按照年龄(age)，把数据分配到不同的分区

[0,10) [10,20)    [20,30) [30,40) [40,50)

partition p10 10

partition p20 20

partition p30 30           

partition p40 40

partition p50 50

早期版本RANGE主要是基于整数的分区。在5.7版本中DATE、DATETIME、TIMESTAMP列也可以使用RANGE分区，同时在5.5以上的版本提供了基于非整形的RANGE COLUMN分区。

3.1.2 range分区的使用：

查询分区

查询表分区情况：

SELECT partition_name,table_rows,a.*  FROM

INFORMATION_SCHEMA.partitions a WHERE

TABLE_SCHEMA = schema()

  AND TABLE_NAME='realtimealarm_bak'

当前查询所在分区：

explain partitions select * from realtimealarm_bak where addTime='2019-07-20 23:59:59' ;

5.7之后可以直接:

explain  select * from realtimealarm_bak where addTime='2019-07-20 23:59:59' ;

查看当前数据库版本

select version();

查询某个分区的数据：

select * from realtimealarm_bak PARTITION(p20190720)

创建分区

未分区的表中创建分区：

分区键为timestamp:

alter table realtimealarm_bak PARTITION BY RANGE ( UNIX_TIMESTAMP(addTime) )

(

PARTITION p20190625 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-25 23:59:59')),

PARTITION p20190630 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-30 23:59:59')),

PARTITION p20190705 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-05 23:59:59')),

PARTITION p20190710 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-10 23:59:59')),

PARTITION p20190715 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-15 23:59:59')),

PARTITION p20190720 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-20 23:59:59')),

PARTITION p20190726 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-26 23:59:59'))

);

分区键为varchar使用range columns分区：

alter table realtimealarm_bak PARTITION BY RANGE Columns (eventTime)

(

PARTITION p20190625 VALUES LESS THAN ('20190625235959'),

PARTITION p20190630 VALUES LESS THAN ('20190630235959'),

PARTITION p20190705 VALUES LESS THAN ('20190705235959'),

PARTITION p20190710 VALUES LESS THAN ('20190710235959'),

PARTITION p20190715 VALUES LESS THAN ('20190715235959'),

PARTITION p20190716 VALUES LESS THAN ('20190716235959')

);

分区键为date类型：TO_DAYS('2019-09-16')

alter table realtimealarm_bak PARTITION BY RANGE (TO_DAYS(addTime) )

(

PARTITION p20190915 VALUES LESS THAN (TO_DAYS('2019-09-16'))

)

新增分区

alter table  realtimealarm_bak add partition (partition p20190727 values less than (UNIX_TIMESTAMP('2019-07-27 23:59:59')));

alter table  realtimealarm_bak add partition (partition p20190727 values less than ('20190727235959'));

删除分区

删除分区并会将表数据真正删除：

ALTER TABLE `realtimealarm_bak` DROP partition p20190727;

清除表分区，只是将表中的数据还原到删除分区前的状态，不会真正删除数据：

ALTER TABLE `realtimealarm_bak` REMOVE partitioning;

删除某个分区的数据：

delete  from realtimealarm_bak PARTITION(p20190720); 

总结：

1. 一个表最多有1024个分区
2. 添加分区只能递增添加
3. 不能插入超出分区范围以外的数据
4. 不支持外键
5. 分区字段不能为NULL
6. 看你原表里是否有主键字段，如果有的话，MYSQL的分区字段就必须包含在主键字段内，需要创建联合主键，如果原表没有主键，就不需要

例如直接创建有联合主键的分区：

CREATE TABLE `access_log` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`access_time` datetime NOT NULL,

PRIMARY KEY (`id`,`access_time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (to_days(access_time))

(PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,

 PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) ;

ALTER TABLE oneuser_stb_day_copy1

ADD CONSTRAINT pk_union  PRIMARY KEY(ID,reportingTime);

查看有哪些任务被执行

select * from  information_schema.events

查看定时任务是否执行：

select @@event_scheduler;

存储过程+定时任务(eventTime分区)：

drop procedure autopartition_procedure1;

DROP event autopartition_event;

DELIMITER //

CREATE PROCEDURE autopartition_procedure1()

BEGIN

-- 表 realtimealarm_bak

-- 自动创建分区

/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */

SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS

WHERE table_name='realtimealarm_bak' ORDER BY partition_ordinal_position DESC LIMIT 1;

SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;

/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */

SET @s1=CONCAT('ALTER TABLE realtimealarm_bak  ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (''',date_format(@Max_date,'%Y%m%d'),'235959','''))');

/* 输出查看增加分区语句*/

SELECT @s1;

PREPARE stmt2 FROM @s1;

EXECUTE stmt2;

DEALLOCATE PREPARE stmt2;

COMMIT ;

END

//

-- 定时任务  autopartition

CREATE DEFINER=`root`@`%` EVENT

`autopartition_event`

ON SCHEDULE EVERY 1 DAY STARTS '2019-10-13 05:00:00'

ON COMPLETION NOT PRESERVE ENABLE DO

call autopartition_procedure1();

Mysql数据库恢复

mysql数据库的备份与恢复_数据库的备份和恢复-CSDN博客