作者： GrimMjx
一.InnoDB邏輯存儲(chǔ)結(jié)構(gòu)
- 段
- 區(qū)
- 頁(yè)
二.分區(qū)概述
- 分區(qū)
三.分區(qū)類(lèi)型
- RANGE分區(qū)
- LIST分區(qū)
- HASH分區(qū)
- KEY分區(qū)
四.分區(qū)和性能
一.InnoDB邏輯存儲(chǔ)結(jié)構(gòu)
首先要先介紹一下InnoDB邏輯存儲(chǔ)結(jié)構(gòu)和區(qū)的概念， 它的所有數(shù)據(jù)都被邏輯地存放在表空間，表空間又由段，區(qū)，頁(yè)組成。
段
段就是上圖的segment區(qū)域，常見(jiàn)的段有數(shù)據(jù)段、索引段、回滾段等，在InnoDB存儲(chǔ)引擎中，對(duì)段的管理都是由引擎自身所完成的。
區(qū)
區(qū)就是上圖的extent區(qū)域， 區(qū)是由連續(xù)的頁(yè)組成的空間，無(wú)論頁(yè)的大小怎么變，區(qū)的大小默認(rèn)總是為1MB。為了保證區(qū)中的頁(yè)的連續(xù)性，InnoDB存儲(chǔ)引擎一次從磁盤(pán)申請(qǐng)4-5個(gè)區(qū)，InnoDB頁(yè)的大小默認(rèn)為16kb，即一個(gè)區(qū)一共有64（1MB/16kb=16）個(gè)連續(xù)的頁(yè)。 每個(gè)段開(kāi)始，先用32頁(yè)（page）大小的碎片頁(yè)來(lái)存放數(shù)據(jù)，在使用完這些頁(yè)之后才是64個(gè)連續(xù)頁(yè)的申請(qǐng)。這樣做的目的是，對(duì)于一些小表或者是undo類(lèi)的段，可以開(kāi)始申請(qǐng)較小的空間，節(jié)約磁盤(pán)開(kāi)銷(xiāo)。
頁(yè)
頁(yè)就是上圖的page區(qū)域，也可以叫塊。頁(yè)是InnoDB磁盤(pán)管理的最小單位。默認(rèn)大小為16KB，可以通過(guò)參數(shù)innodb_page_size來(lái)設(shè)置。常見(jiàn)的頁(yè)類(lèi)型有：數(shù)據(jù)頁(yè)，undo頁(yè)，系統(tǒng)頁(yè)，事務(wù)數(shù)據(jù)頁(yè)，插入緩沖位圖頁(yè)，插入緩沖空閑列表頁(yè)，未壓縮的二進(jìn)制大對(duì)象頁(yè)，壓縮的二進(jìn)制大對(duì)象頁(yè)等。
二.分區(qū)概述分區(qū)
這里講的分區(qū)，此“區(qū)”非彼“區(qū)”，這里講的分區(qū)的意思是指將同一表中不同行的記錄分配到不同的物理文件中，幾個(gè)分區(qū)就有幾個(gè).idb文件，不是我們剛剛說(shuō)的區(qū)。MySQL在5.1時(shí)添加了對(duì)水平分區(qū)的支持。分區(qū)是將一個(gè)表或索引分解成多個(gè)更小，更可管理的部分。每個(gè)區(qū)都是獨(dú)立的，可以獨(dú)立處理，也可以作為一個(gè)更大對(duì)象的一部分進(jìn)行處理。這個(gè)是MySQL支持的功能，業(yè)務(wù)代碼無(wú)需改動(dòng)。要知道MySQL是面向OLTP的數(shù)據(jù)，它不像TIDB等其他DB。那么對(duì)于分區(qū)的使用應(yīng)該非常小心，如果不清楚如何使用分區(qū)可能會(huì)對(duì)性能產(chǎn)生負(fù)面的影響。
MySQL數(shù)據(jù)庫(kù)的分區(qū)是局部分區(qū)索引，一個(gè)分區(qū)中既存了數(shù)據(jù)，又放了索引。也就是說(shuō)，每個(gè)區(qū)的聚集索引和非聚集索引都放在各自區(qū)的（不同的物理文件）。目前MySQL數(shù)據(jù)庫(kù)還不支持全局分區(qū)。
無(wú)論哪種類(lèi)型的分區(qū)，如果表中存在主鍵或唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分。
三.分區(qū)類(lèi)型
目前MySQL支持以下幾種類(lèi)型的分區(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ū)類(lèi)型，行數(shù)據(jù)基于屬于一個(gè)給定的連續(xù)區(qū)間的列值被放入分區(qū)。但是記住，當(dāng)插入的數(shù)據(jù)不在一個(gè)分區(qū)中定義的值的時(shí)候，會(huì)拋異常。RANGE分區(qū)主要用于日期列的分區(qū)，比如交易表啊，銷(xiāo)售表啊等。可以根據(jù)年月來(lái)存放數(shù)據(jù)。如果你分區(qū)走的唯一索引中date類(lèi)型的數(shù)據(jù)，那么注意了，優(yōu)化器只能對(duì) YEAR , TO_DAYS , TO_SECONDS , UNIX_TIMESTAMP 這類(lèi)函數(shù)進(jìn)行優(yōu)化選擇。實(shí)戰(zhàn)中可以用int類(lèi)型，那么只用存yyyyMM就好了。也不用關(guān)心函數(shù)了。
CREATETABLE`m_test_db`. `Order`(
`id`INTNOTNULLAUTO_INCREMENT,
`partition_key`INTNOTNULL,
`amt`DECIMAL( 5) NULL,
PRIMARY KEY( `id`, `partition_key`)) PARTITIONBYRANGE(partition_key) PARTITIONS5( PARTITIONpart0 VALUESLESSTHAN( 201901), PARTITIONpart1 VALUESLESSTHAN( 201902), PARTITIONpart2 VALUESLESSTHAN( 201903), PARTITIONpart3 VALUESLESSTHAN( 201904), PARTITIONpart4 VALUESLESSTHAN( 201905)) ;
這時(shí)候我們先插入一些數(shù)據(jù)
INSERTINTO`m_test_db`. `Order`( `id`, `partition_key`, `amt`) VALUES( '1', '201901', '1000');
INSERTINTO`m_test_db`. `Order`( `id`, `partition_key`, `amt`) VALUES( '2', '201902', '800');
INSERTINTO`m_test_db`. `Order`( `id`, `partition_key`, `amt`) VALUES( '3', '201903', '1200');
現(xiàn)在我們查詢(xún)一下，通過(guò)EXPLAIN PARTITION命令發(fā)現(xiàn)SQL優(yōu)化器只需搜對(duì)應(yīng)的區(qū)，不會(huì)搜索所有分區(qū)
如果sql語(yǔ)句有問(wèn)題，那么會(huì)走所有區(qū)。會(huì)很危險(xiǎn)。所以分區(qū)表后，select語(yǔ)句必須走分區(qū)鍵。
以下3種不是太常用，就一筆帶過(guò)了。
LIST分區(qū)
LIST分區(qū)和RANGE分區(qū)很相似，只是分區(qū)列的值是離散的，不是連續(xù)的。LIST分區(qū)使用VALUES IN，因?yàn)槊總€(gè)分區(qū)的值是離散的，因此只能定義值。
HASH分區(qū)
說(shuō)到哈希，那么目的很明顯了，將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保證每個(gè)分區(qū)的數(shù)量大致相同。
KEY分區(qū)
KEY分區(qū)和HASH分區(qū)相似，不同之處在于HASH分區(qū)使用用戶(hù)定義的函數(shù)進(jìn)行分區(qū)，KEY分區(qū)使用數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)。
四.分區(qū)和性能
一項(xiàng)技術(shù)，不是用了就一定帶來(lái)益處。比如顯式鎖功能比內(nèi)置鎖強(qiáng)大，你沒(méi)玩好可能導(dǎo)致很不好的情況。分區(qū)也是一樣，不是啟動(dòng)了分區(qū)數(shù)據(jù)庫(kù)就會(huì)運(yùn)行的更快，分區(qū)可能會(huì)給某些sql語(yǔ)句性能提高，但是分區(qū)主要用于數(shù)據(jù)庫(kù)高可用性的管理。數(shù)據(jù)庫(kù)應(yīng)用分為2類(lèi)，一類(lèi)是OLTP（在線事務(wù)處理），一類(lèi)是OLAP（在線分析處理）。
對(duì)于OLAP應(yīng)用分區(qū)的確可以很好的提高查詢(xún)性能，因?yàn)橐话惴治龆夹枰祷卮罅康臄?shù)據(jù)，如果按時(shí)間分區(qū)，比如一個(gè)月用戶(hù)行為等數(shù)據(jù)，則只需掃描響應(yīng)的分區(qū)即可。在OLTP應(yīng)用中，分區(qū)更加要小心，通常不會(huì)獲取一張大表的10%的數(shù)據(jù)，大部分是通過(guò)索引返回幾條數(shù)據(jù)即可。
比如一張表1000w數(shù)據(jù)量，如果一句select語(yǔ)句走輔助索引，但是沒(méi)有走分區(qū)鍵。那么結(jié)果會(huì)很尷尬。如果1000w的B+樹(shù)的高度是3，現(xiàn)在有10個(gè)分區(qū)。那么不是要(3+3)*10次的邏輯IO？（3次聚集索引，3次輔助索引，10個(gè)分區(qū)）。所以在OLTP應(yīng)用中請(qǐng)小心使用分區(qū)表。
在日常開(kāi)發(fā)中，如果想查看sql語(yǔ)句的分區(qū)查詢(xún)結(jié)果可以使用explain partitions + select sql來(lái)獲取，partitions標(biāo)識(shí)走了哪幾個(gè)分區(qū)。
mysql> explain partitions select * from TxnList where startTime> '2016-08-25 00:00:00'andstartTime< '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 inset ( 0. 00sec)
參考：
《MySQL技術(shù)內(nèi)幕》
