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