一文帶你搞懂 MySQL 中的分區(qū)!

作者 |?GrimMjx

一.InnoDB邏輯存儲結(jié)構(gòu)

首先要先介紹一下InnoDB邏輯存儲結(jié)構(gòu)和區(qū)的概念，它的所有數(shù)據(jù)都被邏輯地存放在表空間，表空間又由段，區(qū)，頁組成。

段

段就是上圖的segment區(qū)域，常見的段有數(shù)據(jù)段、索引段、回滾段等，在InnoDB存儲引擎中，對段的管理都是由引擎自身所完成的。

區(qū)

區(qū)就是上圖的extent區(qū)域，區(qū)是由連續(xù)的頁組成的空間，無論頁的大小怎么變，區(qū)的大小默認(rèn)總是為1MB。

為了保證區(qū)中的頁的連續(xù)性，InnoDB存儲引擎一次從磁盤申請4-5個(gè)區(qū)，InnoDB頁的大小默認(rèn)為16kb，即一個(gè)區(qū)一共有64（1MB/16kb=16）個(gè)連續(xù)的頁。

每個(gè)段開始，先用32頁（page）大小的碎片頁來存放數(shù)據(jù)，在使用完這些頁之后才是64個(gè)連續(xù)頁的申請。這樣做的目的是，對于一些小表或者是undo類的段，可以開始申請較小的空間，節(jié)約磁盤開銷。

頁

頁就是上圖的page區(qū)域，也可以叫塊。頁是InnoDB磁盤管理的最小單位。默認(rèn)大小為16KB，可以通過參數(shù)innodb_page_size來設(shè)置。

常見的頁類型有：數(shù)據(jù)頁，undo頁，系統(tǒng)頁，事務(wù)數(shù)據(jù)頁，插入緩沖位圖頁，插入緩沖空閑列表頁，未壓縮的二進(jìn)制大對象頁，壓縮的二進(jìn)制大對象頁等。

二.分區(qū)概述

分區(qū)

這里講的分區(qū)，此“區(qū)”非彼“區(qū)”，這里講的分區(qū)的意思是指將同一表中不同行的記錄分配到不同的物理文件中，幾個(gè)分區(qū)就有幾個(gè).idb文件，不是我們剛剛說的區(qū)。

MySQL在5.1時(shí)添加了對水平分區(qū)的支持。分區(qū)是將一個(gè)表或索引分解成多個(gè)更小，更可管理的部分。

每個(gè)區(qū)都是獨(dú)立的，可以獨(dú)立處理，也可以作為一個(gè)更大對象的一部分進(jìn)行處理。這個(gè)是MySQL支持的功能，業(yè)務(wù)代碼無需改動(dòng)。

要知道MySQL是面向OLTP的數(shù)據(jù)，它不像TIDB等其他DB。那么對于分區(qū)的使用應(yīng)該非常小心，如果不清楚如何使用分區(qū)可能會對性能產(chǎn)生負(fù)面的影響。

MySQL數(shù)據(jù)庫的分區(qū)是局部分區(qū)索引，一個(gè)分區(qū)中既存了數(shù)據(jù)，又放了索引。也就是說，每個(gè)區(qū)的聚集索引和非聚集索引都放在各自區(qū)的（不同的物理文件）。目前MySQL數(shù)據(jù)庫還不支持全局分區(qū)。

無論哪種類型的分區(qū)，如果表中存在主鍵或唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分?！　?/p>

三.分區(qū)類型

目前MySQL支持以下幾種類型的分區(qū)，RANGE分區(qū)，LIST分區(qū)，HASH分區(qū)，KEY分區(qū)。

如果表存在主鍵或者唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分。實(shí)戰(zhàn)十有八九都是用RANGE分區(qū)。

RANGE分區(qū)

RANGE分區(qū)是實(shí)戰(zhàn)最常用的一種分區(qū)類型，行數(shù)據(jù)基于屬于一個(gè)給定的連續(xù)區(qū)間的列值被放入分區(qū)。

但是記住，當(dāng)插入的數(shù)據(jù)不在一個(gè)分區(qū)中定義的值的時(shí)候，會拋異常。

RANGE分區(qū)主要用于日期列的分區(qū)，比如交易表啊，銷售表啊等。可以根據(jù)年月來存放數(shù)據(jù)。

如果你分區(qū)走的唯一索引中date類型的數(shù)據(jù)，那么注意了，優(yōu)化器只能對YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()這類函數(shù)進(jìn)行優(yōu)化選擇。實(shí)戰(zhàn)中可以用int類型，那么只用存yyyyMM就好了。也不用關(guān)心函數(shù)了。

CREATE?TABLE?\`m\_test\_db\`.\`Order\`?(??
??\`id\`?INT?NOT?NULL?AUTO_INCREMENT,??
??\`partition_key\`?INT?NOT?NULL,??
??\`amt\`?DECIMAL(5)?NULL,??
??PRIMARY?KEY?(\`id\`,?\`partition_key\`))?PARTITION?BY?RANGE(partition_key)?PARTITIONS?5(?PARTITION?part0?VALUES?LESS?THAN?(201901),??PARTITION?part1?VALUES?LESS?THAN?(201902),??PARTITION?part2?VALUES?LESS?THAN?(201903),??PARTITION?part3?VALUES?LESS?THAN?(201904),??PARTITION?part4?VALUES?LESS?THAN?(201905))?;??

這時(shí)候我們先插入一些數(shù)據(jù)

INSERT?INTO?\`m\_test\_db\`.\`Order\`?(\`id\`,?\`partition_key\`,?\`amt\`)?VALUES?('1',?'201901',?'1000');??
INSERT?INTO?\`m\_test\_db\`.\`Order\`?(\`id\`,?\`partition_key\`,?\`amt\`)?VALUES?('2',?'201902',?'800');??
INSERT?INTO?\`m\_test\_db\`.\`Order\`?(\`id\`,?\`partition_key\`,?\`amt\`)?VALUES?('3',?'201903',?'1200');??

現(xiàn)在我們查詢一下，通過EXPLAIN PARTITION命令發(fā)現(xiàn)SQL優(yōu)化器只需搜對應(yīng)的區(qū)，不會搜索所有分區(qū)。關(guān)注微信公眾號：Java技術(shù)棧，在后臺回復(fù)：MySQL，可以獲取我整理的 N 篇最新 MySQL 教程，都是干貨。

如果sql語句有問題，那么會走所有區(qū)。會很危險(xiǎn)。所以分區(qū)表后，select語句必須走分區(qū)鍵。

以下3種不是太常用，就一筆帶過了。

LIST分區(qū)

LIST分區(qū)和RANGE分區(qū)很相似，只是分區(qū)列的值是離散的，不是連續(xù)的。LIST分區(qū)使用VALUES IN，因?yàn)槊總€(gè)分區(qū)的值是離散的，因此只能定義值。

HASH分區(qū)

說到哈希，那么目的很明顯了，將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保證每個(gè)分區(qū)的數(shù)量大致相同。

KEY分區(qū)

KEY分區(qū)和HASH分區(qū)相似，不同之處在于HASH分區(qū)使用用戶定義的函數(shù)進(jìn)行分區(qū)，KEY分區(qū)使用數(shù)據(jù)庫提供的函數(shù)進(jìn)行分區(qū)。

四.分區(qū)和性能

一項(xiàng)技術(shù)，不是用了就一定帶來益處。比如顯式鎖功能比內(nèi)置鎖強(qiáng)大，你沒玩好可能導(dǎo)致很不好的情況。

分區(qū)也是一樣，不是啟動(dòng)了分區(qū)數(shù)據(jù)庫就會運(yùn)行的更快，分區(qū)可能會給某些sql語句性能提高，但是分區(qū)主要用于數(shù)據(jù)庫高可用性的管理。數(shù)據(jù)庫應(yīng)用分為2類，一類是OLTP（在線事務(wù)處理），一類是OLAP（在線分析處理）。

對于OLAP應(yīng)用分區(qū)的確可以很好的提高查詢性能，因?yàn)橐话惴治龆夹枰祷卮罅康臄?shù)據(jù)，如果按時(shí)間分區(qū)，比如一個(gè)月用戶行為等數(shù)據(jù)，則只需掃描響應(yīng)的分區(qū)即可。在OLTP應(yīng)用中，分區(qū)更加要小心，通常不會獲取一張大表的10%的數(shù)據(jù)，大部分是通過索引返回幾條數(shù)據(jù)即可。

比如一張表1000w數(shù)據(jù)量，如果一句select語句走輔助索引，但是沒有走分區(qū)鍵。那么結(jié)果會很尷尬。如果1000w的B+樹的高度是3，現(xiàn)在有10個(gè)分區(qū)。那么不是要(3+3)*10次的邏輯IO？（3次聚集索引，3次輔助索引，10個(gè)分區(qū)）。所以在OLTP應(yīng)用中請小心使用分區(qū)表。

在日常開發(fā)中，如果想查看sql語句的分區(qū)查詢結(jié)果可以使用explain partitions + select sql來獲取，partitions標(biāo)識走了哪幾個(gè)分區(qū)。

mysql>?explain?partitions?select?*?from?TxnList?where?startTime>'2016-08-25?00:00:00'?and?startTime<'2016-08-25?23:59:00';????
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+????
|?id?|?select_type?|?table?????????????|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows??|?Extra???????|????
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+????
|??1?|?SIMPLE??????|?ClientActionTrack?|?p20160825??|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|?33868?|?Using?where?|????
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+????
row?in?set?(0.00?sec)??

參考：《MySQL技術(shù)內(nèi)幕》?

文末重磅消息：

阿里的Java開發(fā)手冊讓眾多開發(fā)人員養(yǎng)成了編寫好代碼的習(xí)慣，不得不說真的是無私的奉獻(xiàn)。

阿里發(fā)布了最新的泰山版本，這次的泰山版發(fā)布，新增34條規(guī)約，修改描述 90 處，其中錯(cuò)誤碼規(guī)則更是第一次提出完整的解決方案，大家可參考錯(cuò)誤碼示例表。手冊涵蓋編程規(guī)約、異常日志、單元測試、安全規(guī)約、MySQL數(shù)據(jù)庫、工程規(guī)約、設(shè)計(jì)規(guī)約七大維度。此次泰山版發(fā)布，將帶來三大亮點(diǎn)：新增5條日期時(shí)間規(guī)約；新增2條表別名sql規(guī)約；新增統(tǒng)一錯(cuò)誤碼規(guī)約。

Java開發(fā)人員應(yīng)該好好看看，值得推薦。掃描下方二維碼，回復(fù)?泰山?獲取官方下載鏈接。

點(diǎn)贊是最大的支持?