MySQL中怎么實現(xiàn)分區(qū)

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

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

首先要先介紹一下InnoDB邏輯存儲結(jié)構(gòu)和區(qū)的概念，它的所有數(shù)據(jù)都被邏輯地存放在表空間，表空間又由段，區(qū)，頁組成。

段

段就是上圖的segment區(qū)域，常見的段有數(shù)據(jù)段、索引段、回滾段等，在InnoDB存儲引擎中，對段的管理都是由引擎自身所完成的。

區(qū)

區(qū)就是上圖的extent區(qū)域，區(qū)是由連續(xù)的頁組成的空間，無論頁的大小怎么變，區(qū)的大小默認總是為1MB。

為了保證區(qū)中的頁的連續(xù)性，InnoDB存儲引擎一次從磁盤申請4-5個區(qū)，InnoDB頁的大小默認為16kb，即一個區(qū)一共有64（1MB/16kb=16）個連續(xù)的頁。

每個段開始，先用32頁（page）大小的碎片頁來存放數(shù)據(jù)，在使用完這些頁之后才是64個連續(xù)頁的申請。這樣做的目的是，對于一些小表或者是undo類的段，可以開始申請較小的空間，節(jié)約磁盤開銷。

頁

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

常見的頁類型有：數(shù)據(jù)頁，undo頁，系統(tǒng)頁，事務(wù)數(shù)據(jù)頁，插入緩沖位圖頁，插入緩沖空閑列表頁，未壓縮的二進制大對象頁，壓縮的二進制大對象頁等。

二.分區(qū)概述

分區(qū)

這里講的分區(qū)，此“區(qū)”非彼“區(qū)”，這里講的分區(qū)的意思是指將同一表中不同行的記錄分配到不同的物理文件中，幾個分區(qū)就有幾個.idb文件，不是我們剛剛說的區(qū)。MySQL在5.1時添加了對水平分區(qū)的支持。

分區(qū)是將一個表或索引分解成多個更小，更可管理的部分。

每個區(qū)都是獨立的，可以獨立處理，也可以作為一個更大對象的一部分進行處理。這個是MySQL支持的功能，業(yè)務(wù)代碼無需改動。要知道MySQL是面向OLTP的數(shù)據(jù)，它不像TIDB等其他DB。

那么對于分區(qū)的使用應(yīng)該非常小心，如果不清楚如何使用分區(qū)可能會對性能產(chǎn)生負面的影響。

MySQL數(shù)據(jù)庫的分區(qū)是局部分區(qū)索引，一個分區(qū)中既存了數(shù)據(jù)，又放了索引。也就是說，每個區(qū)的聚集索引和非聚集索引都放在各自區(qū)的（不同的物理文件）。目前MySQL數(shù)據(jù)庫還不支持全局分區(qū)。

無論哪種類型的分區(qū)，如果表中存在主鍵或唯一索引時，分區(qū)列必須是唯一索引的一個組成部分。

三.分區(qū)類型

目前MySQL支持一下幾種類型的分區(qū)，RANGE分區(qū)，LIST分區(qū)，HASH分區(qū)，KEY分區(qū)。

如果表存在主鍵或者唯一索引時，分區(qū)列必須是唯一索引的一個組成部分。實戰(zhàn)十有八九都是用RANGE分區(qū)。

RANGE分區(qū)

RANGE分區(qū)是實戰(zhàn)最常用的一種分區(qū)類型，行數(shù)據(jù)基于屬于一個給定的連續(xù)區(qū)間的列值被放入分區(qū)。

但是記住，當(dāng)插入的數(shù)據(jù)不在一個分區(qū)中定義的值的時候，會拋異常。RANGE分區(qū)主要用于日期列的分區(qū)，比如交易表啊，銷售表啊等。可以根據(jù)年月來存放數(shù)據(jù)。

如果你分區(qū)走的唯一索引中date類型的數(shù)據(jù)，那么注意了，優(yōu)化器只能對YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()這類函數(shù)進行優(yōu)化選擇。實戰(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ù)據(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)化器只需搜對應(yīng)的區(qū)，不會搜索所有分區(qū)

如果sql語句有問題，那么會走所有區(qū)。會很危險。所以分區(qū)表后，select語句必須走分區(qū)鍵。

以下3種不是太常用，就一筆帶過了。

LIST分區(qū)

LIST分區(qū)和RANGE分區(qū)很相似，只是分區(qū)列的值是離散的，不是連續(xù)的。LIST分區(qū)使用VALUES IN，因為每個分區(qū)的值是離散的，因此只能定義值。

HASH分區(qū)

說到哈希，那么目的很明顯了，將數(shù)據(jù)均勻的分布到預(yù)先定義的各個分區(qū)中，保證每個分區(qū)的數(shù)量大致相同。

KEY分區(qū)

KEY分區(qū)和HASH分區(qū)相似，不同之處在于HASH分區(qū)使用用戶定義的函數(shù)進行分區(qū)，KEY分區(qū)使用數(shù)據(jù)庫提供的函數(shù)進行分區(qū)。

四.分區(qū)和性能

一項技術(shù)，不是用了就一定帶來益處。比如顯式鎖功能比內(nèi)置鎖強大，你沒玩好可能導(dǎo)致很不好的情況。

分區(qū)也是一樣，不是啟動了分區(qū)數(shù)據(jù)庫就會運行的更快，分區(qū)可能會給某些sql語句性能提高，但是分區(qū)主要用于數(shù)據(jù)庫高可用性的管理。

數(shù)據(jù)庫應(yīng)用分為2類，一類是OLTP（在線事務(wù)處理），一類是OLAP（在線分析處理）。

對于OLAP應(yīng)用分區(qū)的確可以很好的提高查詢性能，因為一般分析都需要返回大量的數(shù)據(jù)，如果按時間分區(qū)，比如一個月用戶行為等數(shù)據(jù)，則只需掃描響應(yīng)的分區(qū)即可。

在OLTP應(yīng)用中，分區(qū)更加要小心，通常不會獲取一張大表的10%的數(shù)據(jù)，大部分是通過索引返回幾條數(shù)據(jù)即可。

比如一張表1000w數(shù)據(jù)量，如果一句select語句走輔助索引，但是沒有走分區(qū)鍵。那么結(jié)果會很尷尬。

如果1000w的B+樹的高度是3，現(xiàn)在有10個分區(qū)。那么不是要(3+3)*10次的邏輯IO？（3次聚集索引，3次輔助索引，10個分區(qū)）。所以在OLTP應(yīng)用中請小心使用分區(qū)表。

在日常開發(fā)中，如果想查看sql語句的分區(qū)查詢結(jié)果可以使用explain partitions + select sql來獲取，partitions標(biāo)識走了哪幾個分區(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中怎么實現(xiàn)分區(qū)，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。