MySQL基于时间字段进行分区的方案总结_MySQL
MySQL支持的分区类型一共有四种：RANGE，LIST，HASH，KEY。其中，RANGE又可分为原生RANGE和RANGE COLUMNS，LIST分为原生LIST和LIST COLUMNS，HASH分为原生HASH和LINEAR HASH，KEY包含原生KEY和LINEAR HASH。关于这些分区之间的差别，改日另写文章进行阐述。
最近，碰到一个需求，要对表的时间字段（类型：datetime）基于天进行分区。于是遍历MySQL官方文档分区章节，总结如下：
实现方式
主要是以下几种：
1. 基于RANGE
2. 基于RANGE COLUMNS
3. 基于HASH
测试数据
为了测试以上三种方案，特构造了100万的测试数据，放在test表中，test表只有两列：id和hiredate，其中hiredate只包含10天的数据，从2015-12-01到2015-12-10。具体信息如下：
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select min(hiredate),max(hiredate) from test;
+---------------------+---------------------+
| min(hiredate) | max(hiredate) |
+---------------------+---------------------+
| 2015-12-01 00:00:00 | 2015-12-10 23:59:56 |
+---------------------+---------------------+
1 row in set (0.44 sec)
mysql> select date(hiredate),count(*) from test group by date(hiredate);
+----------------+----------+
| date(hiredate) | count(*) |
+----------------+----------+
| 2015-12-01 | 99963 |
| 2015-12-02 | 100032 |
| 2015-12-03 | 100150 |
| 2015-12-04 | 99989 |
| 2015-12-05 | 99908 |
| 2015-12-06 | 99897 |
| 2015-12-07 | 100137 |
| 2015-12-08 | 100171 |
| 2015-12-09 | 99851 |
| 2015-12-10 | 99902 |
+----------------+----------+
10 rows in set (0.98 sec)
测试的维度
测试的维度主要从两个方面进行，
一、分区剪裁
针对特定的查询，是否能进行分区剪裁（即只查询相关的分区，而不是所有分区）
二、查询时间
鉴于该批测试数据是静止的（即没有并发进行的insert，update和delete操作），数据量也不太大，从这个维度来考量貌似意义也不是很大。
因此，重点测试第一个维度。
基于RANGE的分区方案
在这里，选用了TO_DAYS函数
CREATE TABLE range_datetime(
id INT,
hiredate DATETIME
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),
PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
PARTITION p3 VALUES LESS THAN ( TO_DAYS('20151204') ),
PARTITION p4 VALUES LESS THAN ( TO_DAYS('20151205') ),
PARTITION p5 VALUES LESS THAN ( TO_DAYS('20151206') ),
PARTITION p6 VALUES LESS THAN ( TO_DAYS('20151207') ),
PARTITION p7 VALUES LESS THAN ( TO_DAYS('20151208') ),
PARTITION p8 VALUES LESS THAN ( TO_DAYS('20151209') ),
PARTITION p9 VALUES LESS THAN ( TO_DAYS('20151210') ),
PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') )
);
插入数据并查看特定查询的执行计划
mysql> insert into range_datetime select * from test; Query OK, 1000000 rows affected (8.15 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from range_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | range_datetime | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400061 | Using where | +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.03 sec)
注意执行计划中的partitions的内容，只查询了p7，p8，p9，p10三个分区，由此来看，使用to_days函数确实可以实现分区裁剪。
基于RANGE COLUMNS的分区方案
RANGE COLUMNS可以直接基于列，而无需像上述RANGE那种，分区的对象只能为整数。
创表语句如下：
CREATE TABLE range_columns (
id INT,
hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) (
PARTITION p1 VALUES LESS THAN ( '20151202' ),
PARTITION p2 VALUES LESS THAN ( '20151203' ),
PARTITION p3 VALUES LESS THAN ( '20151204' ),
PARTITION p4 VALUES LESS THAN ( '20151205' ),
PARTITION p5 VALUES LESS THAN ( '20151206' ),
PARTITION p6 VALUES LESS THAN ( '20151207' ),
PARTITION p7 VALUES LESS THAN ( '20151208' ),
PARTITION p8 VALUES LESS THAN ( '20151209' ),
PARTITION p9 VALUES LESS THAN ( '20151210' ),
PARTITION p10 VALUES LESS THAN ('20151211' )
);
插入数据并查看上述查询的执行计划
mysql> insert into range_columns select * from test; Query OK, 1000000 rows affected (9.20 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from range_columns where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | range_columns | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400210 | Using where | +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.11 sec)
同样，使用该分区方案也实现了分区剪裁。
基于HASH的分区方案
因HASH分区对象同样只能为整数，所以我们无法像上述RANGE COLUMNS那种直接引用列，在这里，同样用了TO_DAYS函数进行转换。
创表语句如下：
CREATE TABLE hash_datetime ( id INT, hiredate DATETIME ) PARTITION BY HASH( TO_DAYS(hiredate) ) PARTITIONS 10;
插入数据并查看上述查询的执行计划
mysql> insert into hash_datetime select * from test; Query OK, 1000000 rows affected (9.43 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from hash_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | hash_datetime | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ALL | NULL | NULL | NULL | NULL | 1000500 | Using where | +----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
不难看出，使用hash分区并不能有效的实现分区裁剪，至少在本例，基于天的需求中如此。
以上三种方案都是基于datetime的，那么，对于timestamp类型，又该如何选择呢？
事实上，MySQL提供了一种基于UNIX_TIMESTAMP函数的RANGE分区方案，而且，只能使用UNIX_TIMESTAMP函数，如果使用其它函数，譬如to_days，会报如下错误：“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。
而且官方文档中也提到“Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)”。
下面来测试一下基于UNIX_TIMESTAMP函数的RANGE分区方案，看其能否实现分区裁剪。
针对TIMESTAMP的分区方案
创表语句如下：
CREATE TABLE range_timestamp (
id INT,
hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-02 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-03 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-04 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-05 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-06 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-07 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-08 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-09 00:00:00') ),
PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-10 00:00:00') ),
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00') )
);
插入数据并查看上述查询的执行计划
mysql> insert into range_timestamp select * from test; Query OK, 1000000 rows affected (13.25 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from range_timestamp where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | range_timestamp | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400448 | Using where | +----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
同样也能实现分区裁剪。
总结：
1. 经过对比，个人倾向于第二种方案，即基于RANGE COLUMNS的分区实现。
2. 在5.7版本之前，对于DATA和DATETIME类型的列，如果要实现分区裁剪，只能使用YEAR() 和TO_DAYS()函数，在5.7版本中，又新增了TO_SECONDS()函数。
3. 其实LIST也能实现基于天的分区方案，但在这个需求上，相比于RANGE，还是显得很鸡肋。
4. TIMESTAMP类型的列，只能基于UNIX_TIMESTAMP函数进行分区，切记！
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
抖音10級燈牌多少錢？ 10級粉絲燈牌需要幾天？
Mar 11, 2024 pm 05:37 PM
在抖音平台上，許多用戶都渴望獲得等級認證，其中10級燈牌更是展示用戶在抖音上的影響力和認可度。本文將深入探討抖音10級燈牌的價格以及達到此等級所需的時間，以幫助使用者更了解此過程。一、抖音10級燈牌多少錢？抖音10級燈牌的價格會受市場波動和供需情況的影響而有所差異，一般價格在幾千元到萬元之間。這個價格主要包括燈牌本身的成本和可能的服務費用。用戶可透過抖音官方管道或第三方服務機構購買10級燈牌，但在購買時要留意選擇合法管道，以免遭遇虛假或詐欺交易。二、10級粉絲燈牌要幾天？達到10級燈牌
邁向可驗證的 AI：形式化方法的五大挑戰
Apr 09, 2023 pm 02:01 PM
人工智慧試圖模仿人類智慧的運算系統，包括人類一些與智慧具有直覺聯繫的功能，例如學習、解決問題以及理性地思考和行動。在廣義地解釋上，AI 一詞涵蓋了許多密切相關的領域如機器學習。那些大量使用 AI 的系統在醫療保健、交通運輸、金融、社交網路、電子商務和教育等領域都產生了重大的社會影響。這種日益增長的社會影響，也帶來了一系列風險和擔憂，包括人工智慧軟體中的錯誤、網路攻擊和人工智慧系統安全等面向。因此，AI 系統的驗證問題以及更廣泛的可信 AI 的話題已經開始引起研究界的關注。 「可驗證 AI」已經確
艾爾登法環通關需要多久
Mar 11, 2024 pm 12:50 PM
玩家在艾爾登法環中進行遊戲時可以體驗遊戲主線劇情，以及收集遊戲成就，有很多玩家不知道艾爾登法環通關需要多久，玩家的通關流程在30個小時。艾爾登法環通關需要多久答：30小時。 1、這個30小時的通關時長指的雖然不是高手般的速通，但也省略了許多的流程。 2.如果你想獲得更好的遊戲體驗或是體驗完整的劇情，那麼時長上肯定要花費更多的時間。 3.如果玩家是全收集大約要100-120小時。 4.如果是只走主線刷BOSS大約：50-60小時。 5.如果是想全部體驗：150小時打底。
linux 可以重置系統時間嗎
Mar 13, 2023 am 10:50 AM
linux可以重置系統時間，其重置方法為：1、使用date指令查看時間；2、使用「yum install ntp」指令安裝ntp；3、透過「ntpdate -u ntp.api.bz」指令實現網路時間同步即可。
php 怎麼實現時間把時分秒去掉
Mar 13, 2023 am 11:20 AM
php實作時間把時分秒去掉的方法：1、建立一個php範例檔；2、使用strtotime函數將日期時間轉換為時間戳記；3、透過date函數對日期或時間進行格式化即可去掉時分秒。
小紅書發佈作品時間怎麼設定？發布作品時間準確嗎？
Mar 24, 2024 pm 01:31 PM
小紅書，一個充滿生活氣息與知識分享的平台，讓越來越多的創作者在這裡暢所欲言。要想在小紅書上獲得更多的關注和點贊，除了內容品質之外，發布作品的時間也是至關重要的。那麼，如何設定小紅書發布作品的時間呢？一、小紅書發布作品時間怎麼設定？ 1.了解用戶活躍時間首先，需要先明確小紅書用戶的活躍時間。通常來說，晚上8點到10點以及週末下午是用戶活躍度較高的時段。然而，這個時段也會受到受眾群體和地理等因素的影響而有所不同。因此，為了更能掌握用戶活躍時段，建議對不同族群的行為習慣進行更詳細的分析。透過了解用戶的活
為什麼我的Go程式需要更長的時間來編譯？
Jun 09, 2023 pm 06:00 PM
近年來，Go語言已經成為了越來越多開發者的選擇。但是，相較於其他程式語言而言，Go語言的編譯速度卻不夠快。很多開發者在編譯Go程式時都會遇到這樣的問題：為什麼我的Go程式需要更長時間來編譯？本文將會從幾個面向探討這個問題。 Go語言的編譯器架構Go語言的編譯器架構採用的是三階段設計，分別是前端、中間層、後端。前端負責將原始碼翻譯成Go語言的中間程式碼，中間層則會中
Linux 檔案時間查看技巧詳解
Feb 21, 2024 pm 01:15 PM
Linux檔案時間檢視技巧詳解在Linux系統中，檔案的時間資訊對於檔案管理和追蹤變更非常重要。 Linux系統透過三種主要時間屬性來記錄檔案的變更訊息，分別是存取時間（atime）、修改時間（mtime）和變更時間（ctime）。本文將詳細介紹如何查看和管理這些文件時間信息，並提供具體的程式碼範例。 1.查看文件時間資訊透過使用ls指令結合參數-l可以列出文
