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

• 段

• 區(qū)

• 頁(yè)

二.分區(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ū)，頁(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ǔ)引擎一次從磁盤申請(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類的段，可以開(kāi)始申請(qǐng)較小的空間，節(jié)約磁盤開(kāi)銷。

頁(yè)

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

常見(jiàn)的頁(yè)類型有：數(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ú)論哪種類型的分區(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ù)年月來(lái)存放數(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ù)了。

														

															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)在我們查詢一下，通過(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ū)使用用戶定義的函數(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類，一類是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ù)，大部分是通過(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ū)查詢結(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' 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)幕》

End

作者：GrimMjx , 本文版權(quán)歸作者所有

cnblogs.com/GrimMjx/p/10526821.html

特別推薦一個(gè)分享架構(gòu)+算法的優(yōu)質(zhì)內(nèi)容，還沒(méi)關(guān)注的小伙伴，可以長(zhǎng)按關(guān)注一下：
																		



																	

																	

																		
																	

長(zhǎng)按訂閱更多精彩▼
																	

																		
如有收獲，點(diǎn)個(gè)在看，誠(chéng)摯感謝