<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
鬆哥之前寫過文章跟大家介紹過用 MyCat 實現 MySQL 的分庫分表,不知道有沒有小夥伴研究過,MySQL 其實也自帶了分割區功能,我們可以建立一個帶有分割區的表,而且不需要藉助任何外部工具,今天我們就一起來看看。
小夥伴們知道,MySQL 資料庫中的資料是以檔案的形勢存在磁碟上的,預設放在 /var/lib/mysql/
目錄下面,我們可以通過 show variables like '%datadir%';
命令來檢視:
我們進入到這個目錄下,就可以看到我們定義的所有資料庫了,一個資料庫就是一個資料夾,一個庫中,有其對應的表的資訊,如下:
在 MySQL 中,如果儲存引擎是 MyISAM,那麼在 data 目錄下會看到 3 類檔案:.frm
、.myi
、.myd
,作用如下:
*.frm
:這個是表定義,是描述表結構的檔案。*.myd
:這個是資料資訊檔案,是表的資料檔案。*.myi
:這個是索引資訊檔案。如果儲存引擎是 InnoDB
, 那麼在 data 目錄下會看到兩類檔案:.frm
、.ibd
,作用分別如下:
*.frm
:表結構檔案。*.ibd
:表資料和索引的檔案。無論是哪種儲存引擎,只要一張表的資料量過大,就會導致 *.myd
、*.myi
以及 *.ibd
檔案過大,資料的查詢就會變的很慢。
為了解決這個問題,我們可以利用 MySQL 的分割區功能,在物理上將這一張表對應的檔案,分割成許多小塊,如此,當我們查詢一條資料時,就不用在某一個檔案中進行整個遍歷了,我們只需要知道這條資料位於哪一個資料塊,然後在那一個資料塊上查詢就行了;另一方面,如果一張表的資料量太大,可能一個磁碟放不下,這個時候,通過表分割區我們就可以把資料分配到不同的磁碟裡面去。
MySQL 從 5.1 開始新增了對分割區的支援,分割區的過程是將一個表或索引分解為多個更小、更可管理的部分。對於開發者而言,分割區後的表使用方式和不分割區基本上還是一模一樣,只不過在物理儲存上,原本該表只有一個資料檔案,現在變成了多個,每個分割區都是獨立的物件,可以獨自處理,也可以作為一個更大物件的一部分進行處理。
需要注意的是,分割區功能並不是在儲存引擎層完成的,常見的儲存引擎如 InnoDB
、MyISAM
、NDB
等都支援分割區。但並不是所有的儲存引擎都支援,如 CSV
、FEDORATED
、MERGE
等就不支援分割區,因此在使用此分割區功能前,應該對選擇的儲存引擎對分割區的支援有所瞭解。
不同於 MyCat 中既可以垂直切分又可以水平切分,MySQL 資料庫支援的分割區型別為水平分割區,它不支援垂直分割區。
先來一張簡單的示意圖,大家感受一下什麼是水平切分:
假設我的 DB 中有 table-1、table-2 以及 table-3 三張表,水平切分就是拿著我 40 米大刀,對準黑色的線條,砍一劍或者砍 N 劍!
砍完之後,將砍掉的部分放到另外一個資料庫範例中,變成下面這樣:
這樣,原本放在一個 DB 中的 table 現在放在兩個 DB 中了,觀察之後我們發現:
兩個 DB 中表的個數都是完整的,就是原來 DB 中有幾張表,現在還是幾張。每張表中的資料是不完整的,資料被拆分到了不同的 DB 中去了。
這就是資料庫的水平切分,也可以理解為按照資料行進行切分,即按照表中某個欄位的某種規則來將表資料分散到多個庫之中,每個表中包含一部分資料,即水平切分不改變表結構。
先來一張簡單的示意圖,大家感受一下垂直切分:
所謂的垂直切分就是拿著我 40 米大刀,對準了黑色的線條砍。砍完之後,將不同的表放到不同的資料庫範例中去,變成下面這個樣子:
這個時候我們發現如下幾個特點:
每一個資料庫範例中的表的數量都是不完整的。每一個資料庫範例中表的資料是完整的。
這就是垂直切分。一般來說,垂直切分我們可以按照業務來劃分,不同業務的表放到不同的資料庫範例中。
MySQL 資料庫支援的分割區型別為水平分割區。
此外,MySQL 資料庫的分割區是區域性分割區索引,即一個分割區中既存放了資料又存放了索引,目前,MySQL資料庫還不支援全域性分割區(資料存放在各個分割區中,但是所有資料的索引放在一個物件中)。
分割區的限制和缺點:
說了這麼多,來個例子看一下。
首先我們先來檢視一下當前的 MySQL 是否支援分割區。
在 MySQL5.6.1 之前可以通過命令 show variables like '%have_partitioning%'
來檢視 MySQL 是否支援分割區。如果 have_partitioning
的值為 YES,則表示支援分割區。
從 MySQL5.6.1 開始,have_partitioning
引數已經被去掉了,而是用 SHOW PLUGINS
來代替。若有 partition 行且 STATUS 列的值為 ACTIVE,則表示支援分割區,如下所示:
確認我們的 MySQL 支援分割區後,我們就可以開始分割區啦!
接下來我們來看幾種不同的分割區策略。
RANGE 分割區比較簡單,就是根據某一個欄位的值進行分割區。不過這個欄位有一個要求,就是必須是主鍵或者是聯合主鍵中的某個欄位。
例如根據 user 表的 id 進行分割區:
上面的規則涉及到了 id 的所有範圍了,如果沒有第三條規則,那麼插入一個 id 為 300 的記錄時,就會報錯。
建表 SQL 如下:
create table user( id int primary key, username varchar(255) )engine=innodb partition by range(id)( partition p0 values less than(100), partition p1 values less than(200), partition p2 values less than maxvalue );
表建立成功後,我們進入到 /var/lib/mysql/test08
資料夾中,來看剛剛建立的表檔案:
可以看到,此時的資料檔案分為好幾個了。
在 information_schema.partitions
表中,我們可以檢視分割區的詳細資訊:
也可以自己寫個 SQL 去查詢:
select * from information_schema.partitions where table_schema='test08' and table_name='user'G
每一行展示一個分割區的資訊,包括分割區的方式、該區的範圍、分割區的欄位、該區目前有幾條記錄等等。
RANGE 分割區有一個比較典型的使用場景,就是按照日期對錶進行分割區,例如同一年註冊的使用者放在一個分割區中,如下:
create table user( id int, username varchar(255), password varchar(255), createDate date, primary key (id,createDate) )engine=innodb partition by range(year(createDate))( partition p2022 values less than(2023), partition p2023 values less than(2024), partition p2024 values less than(2025) );
**注意,createDate 是聯合主鍵的一員。**如果 createDate 不是主鍵,只是一個普通欄位,那麼建立時就會丟擲如下錯誤:
現在,如果我們要查詢 2022 年註冊的使用者,系統就只會去搜尋 p2022 這個分割區,通過 explain 執行計劃可以證實我們的想法:
如果想要刪除 2022 年註冊的使用者,則只需要刪除該分割區即可:
alter table user drop partition p2022;
由上圖可以看到,刪除之後,資料就沒了。
LIST 分割區和 RANGE 分割區類似,區別在於 LIST 分割區是基於列值匹配一個離散值集合中的某個值來進行選擇,而非連續的。舉個例子大家看下就明白了:
假設我有一個使用者表,使用者有性別,現在想按照性別將使用者分開儲存,男性儲存在一個分割區中,女性儲存在一個分割區中,SQL 如下:
create table user( id int, username varchar(255), password varchar(255), gender int, primary key(id, gender) )engine=innodb partition by list(gender)( partition man values in (1), partition woman values in (0));
這個表將來就兩個分割區,分別儲存男性和女性,gender 的取值為 1 或者 0,gender 如果取其他值,執行就會出錯,最終執行結果如下:
這樣分割區之後,將來查詢男性或者查詢女性效率都會比較高,刪除某一性別的使用者時刪除效率也高。
HASH 分割區的目的是將資料均勻地分佈到預先定義的各個分割區中,保證各分割區的資料量大致都是一樣的。在 RANGE 和 LIST 分割區中,必須明確指定一個給定的列值或列值集合應該儲存在哪個分割區中;而在 HASH 分割區中,MySQL 自動完成這些工作,使用者所要做的只是基於將要進行雜湊分割區的列指定一個表示式,並且分割區的數量。
使用 HASH 分割區來分割一個表,要在 CREATE TABLE 語句上新增 PARTITION BY HASH (expr)
,其中 expr 是一個欄位或者是一個返回整數的表示式;另外通過 PARTITIONS 屬性指定分割區的數量,如果沒有指定,那麼分割區的數量預設為 1,另外,HASH 分割區不能刪除分割區,所以不能使用 DROP PARTITION
操作進行分割區刪除操作。
create table user( id int, username varchar(255), password varchar(255), gender int, primary key(id, gender) )engine=innodb partition by hash(id) partitions 4;
KEY 分割區和 HASH 分割區相似,但是 KEY 分割區支援除 text 和 BLOB 之外的所有資料型別的分割區,而 HASH 分割區只支援數位分割區。
KEY 分割區不允許使用使用者自定義的表示式進行分割區,KEY 分割區使用系統提供的 HASH 函數進行分割區。
當表中存在主鍵或者唯一索引時,如果建立 KEY 分割區時沒有指定欄位系統預設會首選主鍵列作為分割區欄位,如果不存在主鍵列會選擇非空唯一索引列作為分割區欄位。
舉個例子:
create table user( id int, username varchar(255), password varchar(255), gender int, primary key(id, gender) )engine=innodb partition by key(id) partitions 4;
COLUMN 分割區是 5.5 開始引入的分割區功能,只有 RANGE COLUMN 和 LIST COLUMN 這兩種分割區;支援整形、日期、字串;這種分割區方式和 RANGE、LIST 的分割區方式非常的相似。
COLUMNS Vs RANGE Vs LIST 分割區:
COLUMNS 支援的型別
舉個例子看下:
create table user( id int, username varchar(255), password varchar(255), gender int, createDate date, primary key(id, createDate) )engine=innodb PARTITION BY RANGE COLUMNS(createDate) ( PARTITION p0 VALUES LESS THAN ('1990-01-01'), PARTITION p1 VALUES LESS THAN ('2000-01-01'), PARTITION p2 VALUES LESS THAN ('2010-01-01'), PARTITION p3 VALUES LESS THAN ('2020-01-01'), PARTITION p4 VALUES LESS THAN MAXVALUE );
這是 RANGE COLUMNS,分割區值是連續的。
再來看 LIST COLUMNS 分割區,這個就類似於列舉了:
create table user( id int, username varchar(255), password varchar(255), gender int, createDate date, primary key(id, createDate) )engine=innodb PARTITION BY LIST COLUMNS(createDate) ( PARTITION p0 VALUES IN ('1990-01-01'), PARTITION p1 VALUES IN ('2000-01-01'), PARTITION p2 VALUES IN ('2010-01-01'), PARTITION p3 VALUES IN ('2020-01-01') );
新增分割區:
alter table user add partition (partition p3 values less than (4000)); -- range 分割區
alter table user add partition (partition p3 values in (40)); -- lists分割區
刪除表分割區(會刪除資料):
alter table user drop partition p30;
刪除表的所有分割區(不會丟失資料):
alter table user remove partitioning;
重新定義 range 分割區表(不會丟失資料):
alter table user partition by range(salary)( partition p1 values less than (2000), partition p2 values less than (4000));
重新定義 hash 分割區表(不會丟失資料):
alter table user partition by hash(salary) partitions 7;
合併分割區:把 2 個分割區合併為一個,不會丟失資料:
alter table user reorganize partition p1,p2 into (partition p1 values less than (1000));
不知道小夥伴們是否還記得鬆哥 2019 年寫的 MyCat 教學(公眾號江南一點雨後臺回覆 2019 有文章索引),這些分割區策略是不是和 MyCat 中的策略非常相似呀?感興趣的小夥伴趕緊去試一把吧~
到此這篇關於一文讀懂MySQL 表分割區的文章就介紹到這了,更多相關MySQL 表分割區內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45