Mysql创建分区说明

《Mysql创建分区说明》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、背景当现场存在1000个检测点的情况下，对应小时表每年数据量为24*360*1000=8760000。为了提升小时表的查询效率，采用mysql的表分区技术进行优化。具体实现创建数据表CREATETABLE`test1`(`id`INT(11)NOTNULL,`name`VARCHAR(50)NULLDEFAULTNULLCOLLATE'utf8_bin',`ttime`DATETIMENULLDEFAULTNULL)COLLATE='utf8_bin';说明：该表如果存在主键（PRIMARY）时，‘ttime’字段必须包含在主键范围内，即联合主键。建议数据表中不使用PRIMARY，使用uui

2、d从代码逻辑上,保证id的唯一性。创建默认分区altertabletest1partitionbyRANGE(year(ttime))(PARTITIONp2016VALUESLESSTHAN(2017));说明：1．以上sql的逻辑为：添加和修改数据时，根据‘ttime’的具体值，将2016年的数据存入‘p2016’这个分区中。‘LESSTHAN(2017)’等同于year(ttime)<2017。2．添加分区的sql中，不能存在函数或参数。所以需要根据实际情况调整‘p2016’和‘2017’两个内容。创建mysql事件Dropeventifexists`add_hour_table_pa

3、rtition`;CREATEEVENT`add_hour_table_partition`ONSCHEDULEEVERY1MONTHONCOMPLETIONNOTPRESERVEENABLECOMMENT'该事件用于调用存储过程,创建数据表的对应分区'DOBEGINcalladd_hour_table_procedure();END说明：1．showvariableslike'event_scheduler';对应的值必须为“ON”，系统默认为“OFF”。可以通过修改my.cnf配置文件，在[mysqld]下面加入‘event_scheduler=1’实现，具体文件路径,参见<

4、+mysql主从配置>>中的说明。2．“EVERY1MONTH”表示该事件每月执行一次。3．“calladd_hour_table_procedure();”表示该事件会调用一个存储过程创建存储过程通过存储过程，判断下一年的分区是否存在delimiter$$dropprocedureifexists`add_hour_table_procedure`$$CREATEDEFINER=`root`@`%`PROCEDURE`add_hour_table_procedure`()BEGIN--判断某个数据表对应的分区是否存在IFNOTEXISTS(SELECT1FROMINFORMATION_SC

5、HEMA.PARTITIONSwheretable_schema='test1_db'andtable_name='test1'andPartition_name=concat('p',year(CURDATE())+1))thencallexecute_sql_procedure(concat('ALTERTABLE','test1','ADDPARTITION(PARTITIONp',year(CURDATE())+1,'VALUESLESSTHAN(',year(CURDATE())+2,'))'));ENDIF;--多个数据表,需要将上面的代码复制多份执行END$$delimiter

6、;说明：1．‘test1_db’：数据库名称‘test1’：数据表名称‘year(CURDATE())’：计算当前年份2．该存储过程用于判断指定的数据表，下一年的分区是否被创建。当对应分区不存在时，构建分区创建sql，并通过调用另一个存储过程完成sql的执行。通过存储过程，执行sqlDelimiter$$dropprocedureifexists`execute_sql_procedure`$$CREATEDEFINER=`root`@`%`PROCEDURE`execute_sql_procedure`(IN`sqls`VARCHAR(1000))LANGUAGESQLNOTDETERMI

7、NISTICNOSQLSQLSECURITYDEFINERCOMMENT''BEGINset@selectSql=sqls;preparestmtfrom@selectSql;/**预编译preparestmtfrom...执行字符串sql语句*/EXECUTEstmt;/**执行不带有参数的sql*/DEALLOCATEPREPAREstmt;END$$delimiter;说明：1．‘sqls’：调用存储过