TABLE PARTITIONS 分區

為了讓LOG TABLE能分區PARTITION，就用了這個指令....
當然也有能自動化新增PARTITION的指令啦，不過我為了保險起見還是用這種手動的：

ALTER TABLE SMS_LOG
PARTITION BY RANGE(yearmonth DIV 100)
SUBPARTITION BY HASH(yearmonth MOD 100)
(
  PARTITION p2014 VALUES LESS THAN (2015) (SUBPARTITION s0a,SUBPARTITION s0b,SUBPARTITION s0c,SUBPARTITION s0d,SUBPARTITION s0e,SUBPARTITION s0f,SUBPARTITION s0g,SUBPARTITION s0h,SUBPARTITION s0i,SUBPARTITION s0j,SUBPARTITION s0k,SUBPARTITION s0l),
  PARTITION p2015 VALUES LESS THAN (2016) (SUBPARTITION s1a,SUBPARTITION s1b,SUBPARTITION s1c,SUBPARTITION s1d,SUBPARTITION s1e,SUBPARTITION s1f,SUBPARTITION s1g,SUBPARTITION s1h,SUBPARTITION s1i,SUBPARTITION s1j,SUBPARTITION s1k,SUBPARTITION s1l),
  PARTITION p2016 VALUES LESS THAN (2017) (SUBPARTITION s2a,SUBPARTITION s2b,SUBPARTITION s2c,SUBPARTITION s2d,SUBPARTITION s2e,SUBPARTITION s2f,SUBPARTITION s2g,SUBPARTITION s2h,SUBPARTITION s2i,SUBPARTITION s2j,SUBPARTITION s2k,SUBPARTITION s2l),
  PARTITION p2017 VALUES LESS THAN (2018) (SUBPARTITION s3a,SUBPARTITION s3b,SUBPARTITION s3c,SUBPARTITION s3d,SUBPARTITION s3e,SUBPARTITION s3f,SUBPARTITION s3g,SUBPARTITION s3h,SUBPARTITION s3i,SUBPARTITION s3j,SUBPARTITION s3k,SUBPARTITION s3l),
  PARTITION p2018 VALUES LESS THAN (2019) (SUBPARTITION s4a,SUBPARTITION s4b,SUBPARTITION s4c,SUBPARTITION s4d,SUBPARTITION s4e,SUBPARTITION s4f,SUBPARTITION s4g,SUBPARTITION s4h,SUBPARTITION s4i,SUBPARTITION s4j,SUBPARTITION s4k,SUBPARTITION s4l),
  PARTITION p2019 VALUES LESS THAN (2020) (SUBPARTITION s5a,SUBPARTITION s5b,SUBPARTITION s5c,SUBPARTITION s5d,SUBPARTITION s5e,SUBPARTITION s5f,SUBPARTITION s5g,SUBPARTITION s5h,SUBPARTITION s5i,SUBPARTITION s5j,SUBPARTITION s5k,SUBPARTITION s5l),
  PARTITION p2020 VALUES LESS THAN (2021) (SUBPARTITION s6a,SUBPARTITION s6b,SUBPARTITION s6c,SUBPARTITION s6d,SUBPARTITION s6e,SUBPARTITION s6f,SUBPARTITION s6g,SUBPARTITION s6h,SUBPARTITION s6i,SUBPARTITION s6j,SUBPARTITION s6k,SUBPARTITION s6l),
  PARTITION p2021 VALUES LESS THAN (2022) (SUBPARTITION s7a,SUBPARTITION s7b,SUBPARTITION s7c,SUBPARTITION s7d,SUBPARTITION s7e,SUBPARTITION s7f,SUBPARTITION s7g,SUBPARTITION s7h,SUBPARTITION s7i,SUBPARTITION s7j,SUBPARTITION s7k,SUBPARTITION s7l),
  PARTITION p2022 VALUES LESS THAN (2023) (SUBPARTITION s8a,SUBPARTITION s8b,SUBPARTITION s8c,SUBPARTITION s8d,SUBPARTITION s8e,SUBPARTITION s8f,SUBPARTITION s8g,SUBPARTITION s8h,SUBPARTITION s8i,SUBPARTITION s8j,SUBPARTITION s8k,SUBPARTITION s8l),
  PARTITION p2023 VALUES LESS THAN (2024) (SUBPARTITION s9a,SUBPARTITION s9b,SUBPARTITION s9c,SUBPARTITION s9d,SUBPARTITION s9e,SUBPARTITION s9f,SUBPARTITION s9g,SUBPARTITION s9h,SUBPARTITION s9i,SUBPARTITION s9j,SUBPARTITION s9k,SUBPARTITION s9l),
  PARTITION p2024 VALUES LESS THAN (2025) (SUBPARTITION s10a,SUBPARTITION s10b,SUBPARTITION s10c,SUBPARTITION s10d,SUBPARTITION s10e,SUBPARTITION s10f,SUBPARTITION s10g,SUBPARTITION s10h,SUBPARTITION s10i,SUBPARTITION s10j,SUBPARTITION s10k,SUBPARTITION s10l),
  PARTITION p2025 VALUES LESS THAN (2026) (SUBPARTITION s11a,SUBPARTITION s11b,SUBPARTITION s11c,SUBPARTITION s11d,SUBPARTITION s11e,SUBPARTITION s11f,SUBPARTITION s11g,SUBPARTITION s11h,SUBPARTITION s11i,SUBPARTITION s11j,SUBPARTITION s11k,SUBPARTITION s11l),
  PARTITION p2026 VALUES LESS THAN (2027) (SUBPARTITION s12a,SUBPARTITION s12b,SUBPARTITION s12c,SUBPARTITION s12d,SUBPARTITION s12e,SUBPARTITION s12f,SUBPARTITION s12g,SUBPARTITION s12h,SUBPARTITION s12i,SUBPARTITION s12j,SUBPARTITION s12k,SUBPARTITION s12l),
  PARTITION p2027 VALUES LESS THAN (2028) (SUBPARTITION s13a,SUBPARTITION s13b,SUBPARTITION s13c,SUBPARTITION s13d,SUBPARTITION s13e,SUBPARTITION s13f,SUBPARTITION s13g,SUBPARTITION s13h,SUBPARTITION s13i,SUBPARTITION s13j,SUBPARTITION s13k,SUBPARTITION s13l),
  PARTITION p2028 VALUES LESS THAN (2029) (SUBPARTITION s14a,SUBPARTITION s14b,SUBPARTITION s14c,SUBPARTITION s14d,SUBPARTITION s14e,SUBPARTITION s14f,SUBPARTITION s14g,SUBPARTITION s14h,SUBPARTITION s14i,SUBPARTITION s14j,SUBPARTITION s14k,SUBPARTITION s14l),
  PARTITION p2029 VALUES LESS THAN (2030) (SUBPARTITION s15a,SUBPARTITION s15b,SUBPARTITION s15c,SUBPARTITION s15d,SUBPARTITION s15e,SUBPARTITION s15f,SUBPARTITION s15g,SUBPARTITION s15h,SUBPARTITION s15i,SUBPARTITION s15j,SUBPARTITION s15k,SUBPARTITION s15l),
  PARTITION p2030 VALUES LESS THAN (2031) (SUBPARTITION s16a,SUBPARTITION s16b,SUBPARTITION s16c,SUBPARTITION s16d,SUBPARTITION s16e,SUBPARTITION s16f,SUBPARTITION s16g,SUBPARTITION s16h,SUBPARTITION s16i,SUBPARTITION s16j,SUBPARTITION s16k,SUBPARTITION s16l),
  PARTITION p_MAXVALUE VALUES LESS THAN (MAXVALUE) (SUBPARTITION s17a,SUBPARTITION s17b,SUBPARTITION s17c,SUBPARTITION s17d,SUBPARTITION s17e,SUBPARTITION s17f,SUBPARTITION s17g,SUBPARTITION s17h,SUBPARTITION s17i,SUBPARTITION s17j,SUBPARTITION s17k,SUBPARTITION s17l)

);

ref:
http://article.denniswave.com/6342
http://www.codedata.com.tw/database/mysql-performance-tuning
https://mariadb.com/kb/en/mariadb/create-table/#partitions
https://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html

dynamic partition:
http://stackoverflow.com/questions/13839177/dynamic-mysql-partitioning-based-on-unixtime
http://code.openark.org/blog/mysql/your-magical-range-partitioning-maintenance-query
http://datacharmer.blogspot.co.il/2008/12/partition-helper-improving-usability.html