MySQL最佳實踐之分區(qū)表基本類型

更新時間：2020年05月31日 14:53:29 投稿：daisy

這篇文章主要給大家介紹了關于MySQL最佳實踐之分區(qū)表基本類型的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧

MySQL分區(qū)表概述

隨著MySQL越來越流行，Mysql里面的保存的數(shù)據也越來越大。在日常的工作中，我們經常遇到一張表里面保存了上億甚至過十億的記錄。這些表里面保存了大量的歷史記錄。對于這些歷史數(shù)據的清理是一個非常頭疼事情，由于所有的數(shù)據都一個普通的表里。所以只能是啟用一個或多個帶where條件的delete語句去刪除（一般where條件是時間）。這對數(shù)據庫的造成了很大壓力。即使我們把這些刪除了，但底層的數(shù)據文件并沒有變小。面對這類問題，最有效的方法就是在使用分區(qū)表。最常見的分區(qū)方法就是按照時間進行分區(qū)。分區(qū)一個最大的優(yōu)點就是可以非常高效的進行歷史數(shù)據的清理。

分區(qū)類型

目前MySQL支持范圍分區(qū)（RANGE），列表分區(qū)（LIST），哈希分區(qū)（HASH）以及KEY分區(qū)四種。下面我們逐一介紹每種分區(qū)：

RANGE分區(qū)

基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。最常見的是基于時間字段. 基于分區(qū)的列最好是整型，如果日期型的可以使用函數(shù)轉換為整型。本例中使用to_days函數(shù)

CREATE TABLE my_range_datetime(
 id INT,
 hiredate DATETIME
) 
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
 PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),
 PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),
 PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),
 PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),
 PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),
 PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),
 PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),
 PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),
 PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),
 PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') )，
 PARTITION p11 VALUES LESS THAN (MAXVALUE) 
);

p11是一個默認分區(qū)，所有大于20171211的記錄都會在這個分區(qū)。MAXVALUE是一個無窮大的值。p11是一個可選分區(qū)。如果在定義表的沒有指定的這個分區(qū)，當我們插入大于20171211的數(shù)據的時候，會收到一個錯誤。

我們在執(zhí)行查詢的時候，必須帶上分區(qū)字段。這樣可以使用分區(qū)剪裁功能

mysql> insert into my_range_datetime select * from test;                                  
Query OK, 1000000 rows affected (8.15 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from my_range_datetime where hiredate >= '20171207124503' and hiredate<='20171210111230'; 
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | partitions  | type | possible_keys | key | key_len | ref | rows  | Extra    |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE   | my_range_datetime | p7,p8,p9,p10 | ALL | NULL     | NULL | NULL  | NULL | 400061 | Using where |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec)

注意執(zhí)行計劃中的partitions的內容，只查詢了p7，p8，p9，p10三個分區(qū)，由此來看，使用to_days函數(shù)確實可以實現(xiàn)分區(qū)裁剪。

上面是基于datetime的，如果是timestamp類型，我們遇到上面問題呢？

事實上，MySQL提供了一種基于UNIX_TIMESTAMP函數(shù)的RANGE分區(qū)方案，而且，只能使用UNIX_TIMESTAMP函數(shù)，如果使用其它函數(shù)，譬如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函數(shù)的RANGE分區(qū)方案，看其能否實現(xiàn)分區(qū)裁剪。

針對TIMESTAMP的分區(qū)方案

創(chuàng)表語句如下：

CREATE TABLE my_range_timestamp (
  id INT,
  hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
  PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),
  PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),
  PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),
  PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),
  PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),
  PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),
  PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),
  PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),
  PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),
  PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )
);

插入數(shù)據并查看上述查詢的執(zhí)行計劃

mysql> insert into my_range_timestamp select * from test;
Query OK, 1000000 rows affected (13.25 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from my_range_timestamp where hiredate >= '20171207124503' and hiredate<='20171210111230';
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | partitions  | type | possible_keys | key | key_len | ref | rows  | Extra    |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE   | my_range_timestamp | p7,p8,p9,p10 | ALL | NULL     | NULL | NULL  | NULL | 400448 | Using where |
+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

同樣也能實現(xiàn)分區(qū)裁剪。

在5.7版本之前，對于DATA和DATETIME類型的列，如果要實現(xiàn)分區(qū)裁剪，只能使用YEAR() 和TO_DAYS()函數(shù)，在5.7版本中，又新增了TO_SECONDS()函數(shù)。

LIST 分區(qū)

LIST分區(qū)

LIST分區(qū)和RANGE分區(qū)類似，區(qū)別在于LIST是枚舉值列表的集合，RANGE是連續(xù)的區(qū)間值的集合。二者在語法方面非常的相似。同樣建議LIST分區(qū)列是非null列，否則插入null值如果枚舉列表里面不存在null值會插入失敗，這點和其它的分區(qū)不一樣，RANGE分區(qū)會將其作為最小分區(qū)值存儲，HASH\KEY分為會將其轉換成0存儲，主要LIST分區(qū)只支持整形，非整形字段需要通過函數(shù)轉換成整形.

create table t_list( 
　　a int(11), 
　　b int(11) 
　　)(partition by list (b) 
　　partition p0 values in (1,3,5,7,9), 
　　partition p1 values in (2,4,6,8,0) 
　　);

Hash 分區(qū)

我們在實際工作中經常遇到像會員表的這種表。并沒有明顯可以分區(qū)的特征字段。但表數(shù)據有非常龐大。為了把這類的數(shù)據進行分區(qū)打散mysql 提供了hash分區(qū)。基于給定的分區(qū)個數(shù)，將數(shù)據分配到不同的分區(qū)，HASH分區(qū)只能針對整數(shù)進行HASH，對于非整形的字段只能通過表達式將其轉換成整數(shù)。表達式可以是mysql中任意有效的函數(shù)或者表達式，對于非整形的HASH往表插入數(shù)據的過程中會多一步表達式的計算操作，所以不建議使用復雜的表達式這樣會影響性能。

Hash分區(qū)表的基本語句如下：

CREATE TABLE my_member (
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30),
  created DATE NOT NULL DEFAULT '1970-01-01',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  job_code INT,
  store_id INT
)
PARTITION BY HASH(id)
PARTITIONS 4;

注意：

HASH分區(qū)可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，則默認分區(qū)數(shù)為1。
不允許只寫PARTITIONS，而不指定分區(qū)數(shù)。
同RANGE分區(qū)和LIST分區(qū)一樣，PARTITION BY HASH (expr)子句中的expr返回的必須是整數(shù)值。
HASH分區(qū)的底層實現(xiàn)其實是基于MOD函數(shù)。譬如，對于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 如果你要插入一個col3為“2017-09-15”的記錄，則分區(qū)的選擇是根據以下值決定的：

MOD(YEAR(‘2017-09-01'),4) = MOD(2017,4) = 1

LINEAR HASH分區(qū)

LINEAR HASH分區(qū)是HASH分區(qū)的一種特殊類型，與HASH分區(qū)是基于MOD函數(shù)不同的是，它基于的是另外一種算法。

格式如下：

CREATE TABLE my_members (
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  job_code INT,
  store_id INT
)
PARTITION BY LINEAR HASH( id )
PARTITIONS 4;

說明：它的優(yōu)點是在數(shù)據量大的場景，譬如TB級，增加、刪除、合并和拆分分區(qū)會更快，缺點是，相對于HASH分區(qū)，它數(shù)據分布不均勻的概率更大。

KEY分區(qū)

KEY分區(qū)其實跟HASH分區(qū)差不多，不同點如下：

KEY分區(qū)允許多列，而HASH分區(qū)只允許一列。
如果在有主鍵或者唯一鍵的情況下，key中分區(qū)列可不指定，默認為主鍵或者唯一鍵，如果沒有，則必須顯性指定列。
KEY分區(qū)對象必須為列，而不能是基于列的表達式。
KEY分區(qū)和HASH分區(qū)的算法不一樣，PARTITION BY HASH (expr)，MOD取值的對象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

格式如下：

CREATE TABLE k1 (
  id INT NOT NULL PRIMARY KEY,  
  name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

在沒有主鍵或者唯一鍵的情況下，格式如下：

CREATE TABLE tm1 (
  s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;

總結：

MySQL分區(qū)中如果存在主鍵或唯一鍵，則分區(qū)列必須包含在其中。

對于原生的RANGE分區(qū)，LIST分區(qū)，HASH分區(qū)，分區(qū)對象返回的只能是整數(shù)值。

分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL

到此這篇關于MySQL最佳實踐之分區(qū)表基本類型的文章就介紹到這了,更多相關MySQL分區(qū)表基本類型內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL雙層游標嵌套循環(huán)實現(xiàn)方法
要實現(xiàn)逐行獲取數(shù)據,需要用到MySQL中的游標,一個游標相當于一個for循環(huán),這里需要用到2個游標,如何在MySQL中實現(xiàn)游標雙層循環(huán)呢,下面小編給大家分享MySQL雙層游標嵌套循環(huán)方法,感興趣的朋友跟隨小編一起看看吧
2024-05-05
MySQL修改lower_case_table_names參數(shù)的方法實踐
本文主要介紹了MySQL修改lower_case_table_names參數(shù),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-05-05
MYSQL刪除重復數(shù)據的簡單方法
業(yè)務中遇到要從表里刪除重復數(shù)據的需求，使用了下面的方法，執(zhí)行成功，大家可以參考使用
2013-11-11
Navicat Premium如何導入SQL文件的方法步驟
這篇文章主要介紹了Navicat Premium如何導入SQL文件的方法步驟，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-03-03
mysql字符串拼接并設置null值的實例方法
在本文中小編給大家整理的是關于mysql 字符串拼接+設置null值的實例內容以及具體方法，需要的朋友們可以學習下。
2019-09-09
詳解Mysql命令大全（推薦）
本篇文章詳細的介紹了Mysql命令，MySQL是一個關系型數(shù)據庫管理系統(tǒng)，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發(fā)都選擇MySQL作為網站數(shù)據庫。
2016-11-11
mysql limit 分頁的用法及注意要點
limit在mysql語句中使用的頻率非常高，一般分頁查詢都會使用到limit語句，本文章向碼農們介紹mysql limit 分頁的用法與注意事項，需要的朋友可以參考下
2016-12-12
mysql修改sql_mode報錯的解決
今天在Navicat中運行sql語句創(chuàng)建數(shù)據表出現(xiàn)了錯誤Err 1067。本文主要介紹了mysql修改sql_mode報錯的解決，感興趣的可以了解一下
2021-09-09
C#實現(xiàn)MySQL命令行備份和恢復
MySQL數(shù)據庫的備份有很多工具可以使用,今天介紹一下使用C#調用MYSQL的mysqldump命令完成MySQL數(shù)據庫的備份與恢復
2018-03-03
MySQL外鍵約束(Foreign?Key)案例詳解
MySQL外鍵約束(FOREIGN KEY)是表的一個特殊字段,經常與主鍵約束一起使用,下面這篇文章主要給給大家介紹了關于MySQL外鍵約束(Foreign?Key)的相關資料,需要的朋友可以參考下
2022-06-06