本篇文章為大家展示了MySQL中怎么實(shí)現(xiàn)分區(qū)，內(nèi)容簡明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

創(chuàng)新互聯(lián)公司2013年開創(chuàng)至今，先為蒸湘等服務(wù)建站，蒸湘等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為蒸湘企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

 一.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ù)了。

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)化器只需搜對(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' 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 |    +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+    1 row in set (0.00 sec)

上述內(nèi)容就是MySQL中怎么實(shí)現(xiàn)分區(qū)，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。