Mysql创建分区说明.docx

背景当现场存在1000个检测点的情况下，对应小时表每年数据量为24*360*1000=8760000。为了提升小时表的查询效率，采用mysql的表分区技术进行优化。具体实现创建数据表CREATE TABLE test1 (id INT(11) NOT NULL,name VARCHAR(50) NULL DEFAULT NULL COLLATE utf8_bin,ttime DATETIME NULL DEFAULT NULL)COLLATE=utf8_bin;说明：该表如果存在主键（PRIMARY）时，ttime字段必须包含在主键范围内，即联合主键。建议数据表中不使用PRIMARY，使用uuid从代码逻辑上,保证id的唯一性。创建默认分区alter table test1 partition by RANGE (year(ttime)(PARTITION p2016 VALUES LESS THAN (2017);说明：1 以上sql的逻辑为：添加和修改数据时，根据ttime的具体值，将2016年的数据存入p2016这个分区中。LESS THAN (2017)等同于year(ttime)2017。2 添加分区的sql中，不能存在函数或参数。所以需要根据实际情况调整p2016和2017两个内容。创建mysql事件Drop event if exists add_hour_table_partition;CREATE EVENT add_hour_table_partitionON SCHEDULEEVERY 1 MONTH ON COMPLETION NOT PRESERVEENABLECOMMENT 该事件用于调用存储过程,创建数据表的对应分区DO BEGINcall add_hour_table_procedure();END说明：1 show variables like event_scheduler; 对应的值必须为“ON”，系统默认为“OFF”。可以通过修改f配置文件，在mysqld下面加入event_scheduler = 1实现，具体文件路径,参见中的说明。2 “EVERY 1 MONTH”表示该事件每月执行一次。3 “call add_hour_table_procedure();”表示该事件会调用一个存储过程创建存储过程通过存储过程，判断下一年的分区是否存在delimiter $drop procedure if exists add_hour_table_procedure $CREATE DEFINER=root% PROCEDURE add_hour_table_procedure ()BEGIN- 判断某个数据表对应的分区是否存在IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.PARTITIONS where table_schema=test1_db and table_name = test1 and Partition_name=concat(p, year(CURDATE()+1)thencall execute_sql_procedure(concat(ALTER TABLE , test1, ADD PARTITION (PARTITION p, year(CURDATE()+1, VALUES LESS THAN (,year(CURDATE()+2,);END IF;- 多个数据表,需要将上面的代码复制多份执行END $delimiter ;说明：1 test1_db：数据库名称test1：数据表名称year(CURDATE()：计算当前年份2 该存储过程用于判断指定的数据表，下一年的分区是否被创建。当对应分区不存在时，构建分区创建sql，并通过调用另一个存储过程完成sql的执行。通过存储过程，执行sqlDelimiter $drop procedure if exists execute_sql_procedure $CREATE DEFINER=root% PROCEDURE execute_sql_procedure(IN sqls VARCHAR(1000)LANGUAGE SQLNOT DETERMINISTICNO SQLSQL SECURITY DEFINERCOMMENT BEGINset selectSql = sqls;prepare stmt from selectSql;/*预编译prepare stmt from . 执行字符串sql语句*/EXECUTE stmt; /*执行不带有参数的sql*/DEALLOCATE PREPARE stmt;END $delimiter ; 说明：1 sqls：调用存储过程时传递的参数，实际为一条可执行的sql语句其它索引的重要性虽然针对小时表进行的分区操作，但是在每个分区中也可能存在较多的数据。这时可以通过索引优化数据的查询速度。根据实际业务需求，将表中那些经常出现在where语句中的列，加入索引中。并且索引不宜过多，一般3列以内效果最佳。针对分区的常用sql/ 针对已存在的表，创建分区规则和分区alter table test1 partition by RANGE (year(ttime) (PARTITION p2016 VALUES LESS THAN (2017); / 添加分区alter table test1 add partition(partition p2017 values LESS THAN (2018);/ 删除分区(不能删除所有分区，最少要保留一个，否则会报错)alter table test1 drop partition p2017,p2018;/ 查询指定分区下的数据select * from test1