點擊上方 "程序員小樂"關注, 星標或置頂一起成長
每天凌晨00點00分, 第一時間與你相約
每日英文
Somethings，I don't，I don't ask，don't mean I don't care．
有些事，我不說，我不問，不代表我不在乎。
每日掏心話
外表剛強的人常容易放棄，因為怕輸。外表柔弱的人卻常堅韌不拔，因為他們想贏。
來自：GrimMjx | 責編：樂樂
鏈接：cnblogs.com/GrimMjx/p/10526821.html
程序員小樂(ID:study_tech)第 845 次推文 圖片來自百度
往日回顧：“不作惡”已成往事？因非法收集用戶隱私，Google竟然被兩名小學生告上了法庭
正文
一.InnoDB邏輯存儲結構
首先要先介紹一下InnoDB邏輯存儲結構和區的概念，它的所有數據都被邏輯地存放在表空間，表空間又由段，區，頁組成。
段
段就是上圖的segment區域，常見的段有數據段、索引段、回滾段等，在InnoDB存儲引擎中，對段的管理都是由引擎自身所完成的。
區
區就是上圖的extent區域，區是由連續的頁組成的空間，無論頁的大小怎麼變，區的大小默認總是為1MB。
為了保證區中的頁的連續性，InnoDB存儲引擎一次從磁盤申請4-5個區，InnoDB頁的大小默認為16kb，即一個區一共有64（1MB/16kb=16）個連續的頁。
每個段開始，先用32頁（page）大小的碎片頁來存放數據，在使用完這些頁之後才是64個連續頁的申請。這樣做的目的是，對於一些小表或者是undo類的段，可以開始申請較小的空間，節約磁盤開銷。
頁
頁就是上圖的page區域，也可以叫塊。頁是InnoDB磁盤管理的最小單位。默認大小為16KB，可以通過參數innodb_page_size來設置。
常見的頁類型有：數據頁，undo頁，系統頁，事務數據頁，插入緩衝位圖頁，插入緩衝空閒列表頁，未壓縮的二進制大對象頁，壓縮的二進制大對象頁等。
二.分區概述
分區
這裡講的分區，此“區”非彼“區”，這裡講的分區的意思是指將同一表中不同行的記錄分配到不同的物理文件中，幾個分區就有幾個.idb文件，不是我們剛剛說的區。MySQL在5.1時添加了對水平分區的支持。
分區是將一個表或索引分解成多個更小，更可管理的部分。
每個區都是獨立的，可以獨立處理，也可以作為一個更大對象的一部分進行處理。這個是MySQL支持的功能，業務代碼無需改動。要知道MySQL是面向OLTP的數據，它不像TIDB等其他DB。
那麼對於分區的使用應該非常小心，如果不清楚如何使用分區可能會對性能產生負面的影響。
MySQL數據庫的分區是局部分區索引，一個分區中既存了數據，又放了索引。也就是說，每個區的聚集索引和非聚集索引都放在各自區的（不同的物理文件）。目前MySQL數據庫還不支持全局分區。
無論哪種類型的分區，如果表中存在主鍵或唯一索引時，分區列必須是唯一索引的一個組成部分。
三.分區類型
目前MySQL支持一下幾種類型的分區，RANGE分區，LIST分區，HASH分區，KEY分區。
如果表存在主鍵或者唯一索引時，分區列必須是唯一索引的一個組成部分。實戰十有八九都是用RANGE分區。
RANGE分區
RANGE分區是實戰最常用的一種分區類型，行數據基於屬於一個給定的連續區間的列值被放入分區。
但是記住，當插入的數據不在一個分區中定義的值的時候，會拋異常。RANGE分區主要用於日期列的分區，比如交易表啊，銷售表啊等。可以根據年月來存放數據。
如果你分區走的唯一索引中date類型的數據，那麼注意了，優化器只能對YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()這類函數進行優化選擇。實戰中可以用int類型，那麼只用存yyyyMM就好了。也不用關心函數了。
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)) ;這時候我們先插入一些數據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');
現在我們查詢一下，通過EXPLAIN PARTITION命令發現SQL優化器只需搜對應的區，不會搜索所有分區
如果sql語句有問題，那麼會走所有區。會很危險。所以分區表後，select語句必須走分區鍵。以下3種不是太常用，就一筆帶過了。
LIST分區
LIST分區和RANGE分區很相似，只是分區列的值是離散的，不是連續的。LIST分區使用VALUES IN，因為每個分區的值是離散的，因此只能定義值。
HASH分區
說到哈希，那麼目的很明顯了，將數據均勻的分佈到預先定義的各個分區中，保證每個分區的數量大致相同。
KEY分區
KEY分區和HASH分區相似，不同之處在於HASH分區使用用戶定義的函數進行分區，KEY分區使用數據庫提供的函數進行分區。
四.分區和性能
一項技術，不是用了就一定帶來益處。比如顯式鎖功能比內置鎖強大，你沒玩好可能導致很不好的情況。
分區也是一樣，不是啟動了分區數據庫就會運行的更快，分區可能會給某些sql語句性能提高，但是分區主要用於數據庫高可用性的管理。
數據庫應用分為2類，一類是OLTP（在線事務處理），一類是OLAP（在線分析處理）。
對於OLAP應用分區的確可以很好的提高查詢性能，因為一般分析都需要返回大量的數據，如果按時間分區，比如一個月用戶行為等數據，則只需掃描響應的分區即可。
在OLTP應用中，分區更加要小心，通常不會獲取一張大表的10%的數據，大部分是通過索引返回幾條數據即可。
比如一張表1000w數據量，如果一句select語句走輔助索引，但是沒有走分區鍵。那麼結果會很尷尬。
如果1000w的B+樹的高度是3，現在有10個分區。那麼不是要(3+3)*10次的邏輯IO？（3次聚集索引，3次輔助索引，10個分區）。所以在OLTP應用中請小心使用分區表。
在日常開發中，如果想查看sql語句的分區查詢結果可以使用explain partitions + select sql來獲取，partitions標識走了哪幾個分區。
mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
| 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 |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
參考：
《MySQL技術內幕》
歡迎在留言區留下你的觀點，一起討論提高。如果今天的文章讓你有新的啟發，學習能力的提升上有新的認識，歡迎轉發分享給更多人。
猜你還想看
阿里、騰訊、百度、華為、京東最新面試題彙集
5萬字長文！SpringBoot 操作 ElasticSearch 詳解
一個基於 Spring Boot 的項目骨架
分佈式事務之 RocketMQ 事務消息詳解
關注訂閱號「程序員小樂」，收看更多精彩內容
嘿，你在看嗎？
