MySQL分區表使用總結

MySQL分區表概述

我們經常遇到一張表裡面儲存了上億甚至過十億的記錄，這些表裡面儲存了大量的曆史記錄。 對于這些曆史資料的清理是一個非常頭疼事情，由于所有的資料都一個普通的表裡。是以隻能是啟用一個或多個帶where條件的delete語句去删除（一般where條件是時間）。 這對資料庫的造成了很大壓力。即使我們把這些删除了，但底層的資料檔案并沒有變小。面對這類問題，最有效的方法就是在使用分區表。最常見的分區方法就是按照時間進行分區。 分區一個最大的優點就是可以非常高效的進行曆史資料的清理。

分區類型

目前MySQL支援範圍分區（RANGE），清單分區（LIST），哈希分區（HASH）以及KEY分區四種。

分區适用場景和優勢

表資料量非常大而且大部分為曆史資料，不經常被通路；

簡單的業務場景，不會産生跨分區查詢或跨分區關聯；

分區表資料更容易維護（可獨立對分區進行優化、檢查、修複及批量删除大資料可以采用drop分區的形式等）；

分區表的資料可以分布在不同的實體裝置上，進而高效地利用多個硬體裝置；

分區表可以避免某些特殊的瓶頸（ps: InnoDB的單個索引的互斥通路、ext3檔案系統的inode鎖競争等）；

可以備份和恢複獨立的分區，非常适用于大資料集的場景；

分區表限制

按照時間戳range分區隻支援increasing規則；

drop分區後資料會全部被清空，不會做資料遷移；

分區字段必須包含在主鍵字段内，唯一鍵必須包含在分區字段；

分區字段不支援timestamps類型，需要使用datetime；

單表最多支援1024個分區；

分區表無法使用外鍵限制；

分區必須使用相同的Engine；

對于MyISAM分區表，不能在使用LOAD INDEX INTO CACHE操作；

對于MyISAM分區表，使用時會打開更多的檔案描述符（單個分區是一個獨立的檔案）；

分區鍵最好預設設定為NOT NULL

分區表不支援除了主鍵外的唯一鍵

Oracle分區表的坑

oracle分區表删除後會導緻索引失效，查詢計劃變化性能很差

自動建立分區表

-- 按日期格式自動添加分區存儲過程

CREATE PROCEDURE `pro_sys_logByWeekDay`(IN tableName VARCHAR(50),IN timeColName VARCHAR(50),IN DateFormat VARCHAR(10) )

    COMMENT '按日期格式(年YEAR，月month，周week，日day)添加表分區的存儲過程，由定時任務調用'

BEGIN

    DECLARE p_id int;

    DECLARE lasttime VARCHAR(20);

    DECLARE nexttime varchar(20);

    SELECT COUNT(partition_name),max(partition_description) des into p_id,lasttime

    from INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;

    set @v_add_a=CONCAT('select adddate(str_to_date(',lasttime,',\'%Y%c%d\'),Interval 1 ',DateFormat,') into @nexttime from dual ');

    -- select @v_add_a;

    PREPARE stm from @v_add_a;

    EXECUTE stm;

    DEALLOCATE PREPARE stm;

    set nexttime=@nexttime;

    set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),' values less than (\'',replace(nexttime,'-',''),'\'))');

    -- select @v_add;

    PREPARE stmt from @v_add;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

END

-- 建立每周生成一次表分區的定時任務

create EVENT event_sysLogWeek on SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP

on COMPLETION PRESERVE

ENABLE

DO call  pro_sys_logByWeekDay('dispatcher_send_msg_par','SENDDATE','Week');

-- 建立每天生成一次表分區的定時任務

create EVENT event_sysLogDay on SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP

DO call  pro_sys_logByWeekDay('dispatcher_send_msg_par','SENDDATE','day');

總結

分區表看上去很帥氣，但使用上存在限制，分區鍵設計不太靈活，如果不走分區鍵很容易出現全表鎖，适用于簡單的大資料量業務場景，複雜的大資料量場景可以考慮分表分庫水準擴充方案。

參考

MySQL最佳實踐分區表基本類型 ：

http://mysql.taobao.org/monthly/2017/11/09/

MySQL存儲過程按月建立表分區：

https://blog.csdn.net/aofavx/article/details/50378360

分區表不建議建立主鍵的原因 ：

https://yq.aliyun.com/articles/48791

MySQL表分區操作錯誤1503解決方案：

http://google3030.blog.163.com/blog/static/16172446520103591023745/