本文目錄一覽：
mysql表分區使用及詳細介紹
一、分區概念
分區是將一個表分成多個區塊進行操作和保存，從而降低每次操作的數據，提高性能。而對於應用來說則是透明的，從邏輯上看只有一張表，但在物理上這個表可能是由多個物理分區組成的，每個分區都是獨立的對象，可以進行獨立處理。
二、分區作用
1.可以邏輯數據分割，分割數據能夠有多個不同的物理文件路徑。
2.可以存儲更多的數據，突破系統單個文件最大限制。
3.提升性能，提高每個分區的讀寫速度，提高分區範圍查詢的速度。
4.可以通過刪除相關分區來快速刪除數據
5.通過跨多個磁盤來分散數據查詢，從而提高磁盤I/O的性能。
6.涉及到例如SUM()、COUNT()這樣聚合函數的查詢，可以很容易的進行並行處理。
7.可以備份和恢復獨立的分區，這對大數據量很有好處。
三、分區能支持的引擎
MySQL支持大部分引擎創建分區，入MyISAM、InnoDB等；不支持MERGE和CSV等來創建分區。同一個分區表中的所有分區必須是同一個存儲引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分區。
四、確認MySQL支持分區
從MySQL5.1開始引入分區功能，可以如下方式查看是否支持：
老版本用：SHOW VARIABLES LIKE ‘%partition%’;
新版本用：show plugins;
五、分區類型
1. RANGE分區：基於屬於一個給定連續區間的列值，把多行分配給分區。
例如，可以將一個表通過年份劃分成兩個分區，2001 -2010年、2011-2020。
2. LIST分區：類似於RANGE分區，LIST是列值匹配一個離散值集合中的某個值來進行選擇。
比如 根據字段 把值為1、3、5的放到一起，2、4、6的另外放到一起 等等…
3. HASH分區：基於用戶定義的表達式的返回值來進行選擇分區，該表達式使用將要插入到表中的這些行的列值來進行計算，這個函數必須產生非負整數值。
通過HASH運算來進行分區，分布的比較均勻
4. KEY分區：類似於按HASH分區，由MySQL服務器提供其自身的哈希函數。
按照KEY進行分區類似於按照HASH分區
六、分區創建注意事項
1. 如果表中存在primary key 或者 unique key 時，分區的列必須是paimary key或者unique key的一個組成部分，也就是說，分區函數的列只能從pk或者uk這些key中取子集
2. 如果表中不存在任何的paimary key或者unique key，則可以指定任何一個列作為分區列
3. 5.5版本前的RANGE、LIST、HASH分區要求分區鍵必須是int；MySQL5.5及以上，支持非整形的RANGE和LIST分區，即：range columns 和 list columns (可以用字符串來進行分區)。
七、分區命名
1. 分區的名字基本上遵循其他MySQL 標識符應當遵循的原則，例如用於表和數據庫名字的標識符。應當注意的是， 分區的名字是不區分大小寫的 。
2. 無論使用何種類型的分區，分區總是在創建時就自動的順序編號，且從0開始記錄。
八、 創建分區
1. RANGE分區：
CREATE TABLE `test01` (
`dayid` int(11) DEFAULT NULL,
`mac` varchar(32) NOT NULL DEFAULT ”,
`dtype` varchar(50) NOT NULL DEFAULT ”
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (dayid)
(PARTITION p20171205 VALUES IN (20171205) ENGINE = InnoDB,
PARTITION p20171204 VALUES IN (20171204) ENGINE = InnoDB,
PARTITION p20171206 VALUES IN (20171206) ENGINE = InnoDB,
PARTITION p20171207 VALUES IN (20171207) ENGINE = InnoDB) */
解讀：以上為 uuid小於5時放到p0分區下，uuid大於5且小於10放到p1分區下，uuid大於10且小於15放到p2分區下，uuid大於15 一直到最大值的存在p3分區下
2. LIST分區：
CREATE TABLE tbl_test (
uuid INT NOT NULL,
title VARCHAR(20)
)
)
PARTITION BY List (uuid) (
PARTITION p0 VALUES in (1,2,3,5),
PARTITION p1 VALUES in (7,9,10),
PARTITION p2 VALUES in (11,15)
)
);
解讀：以上為uuid 等於1/2/3/5時放到p0分區，7/9/10放到p1分區，11/15放到p2分區。當時用insert into時 如果uuid的值不存在p0/p1/p2分區時，則會插入失敗而報錯。
3. HASH分區:
HASH分區主要用來確保數據在預先確定數目的分區中平均分布。在RANGE分區和LIST分區中必須明確指定一個指定的列值或列值集合以指定應該保存在哪個分區中。而在HASH分區中，MySQL會自動完成這些工作，要做的只是基於將要被哈希的列值指定一個表達式，以及指定被分區的表將要被分割成的分區數量，如：
CREATE TABLE tbl_test (
uuid INT NOT NULL,
title VARCHAR(20)
))
PARTITION BY HASH (uuid) (
PARTITIONS 3
));
解讀：MySQL自動創建3個分區，在執行insert into時，根據插入的uuid通過算法來自動分配區間。
注意：
(1) 由於每次插入、更新、刪除一行，這個表達式都要計算一次，這意味着非常複雜的表達式可能會引起性能問題，尤其是在執行同時影響大量行的運算（例如批量插入）的時候。
(2) 最有效率的哈希函數是只對單個表列進行計算，並且它的值隨列值進行一致的增大或減小，因為這考慮了在分區範圍上的“修剪”。也就是說，表達式值和它所基於的列的值變化越接近，就越能有效地使用該表達式來進行HASH分區。
3.1：線性HASH分區
線性HASH分區在“PARTITION BY”子句中添加“LINEAR”關鍵字。
線性HASH分區的有點在於增加、刪除、合併和拆分分區將變得更加快捷，有利於處理含有及其大量數據的表。它的缺點在於各個分區間數據的分布不大可能均衡。
4. KEY分區
類似於HASH分區，HASH分區允許用戶自定義的表達式，而KEY分區則不允許使用用戶自定義的表達式；HASH分區只支持整數分區，KEY分區支持除了blob和text類型之外的其他數據類型分區。
與HASH分區不同，創建KEY分區表的時候，可以不指定分區鍵，默認會選擇使用主鍵或唯一鍵作為分區鍵，沒有主鍵或唯一鍵，就必須指定分區鍵。
CREATE TABLE tbl_test (
uuid INT NOT NULL,
title VARCHAR(20)
))
PARTITION BY LINEAR Key (uuid)
PARTITIONS 3;
解讀：根據分區鍵來進行分區
5. 子分區
子分區是分區表中，每個分區的再次分割，適合保存非常大量的數據。
CREATE TABLE tbl_test (
registerTime Date
))
PARTITION BY GANGE(YEAR(registerTime))
SUBPARTITION BY HASH (TO_DAYS(registerTime))
SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2020),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
解讀：主分區使用RANGE按照年來進行分區，有3個RANGE分區。這3個分區中又被進一步分成了2個子分區，實際上，整個表被分成了3 * 2 = 6個分區。每個子分區按照天進行HASH分區。小於2017的放在一起，2017-2020的放在一起，大於2020的放在一起。
注意：
(1) 在MySQL5.1中，對於已經通過RANGE或LIST分區了的表在進行子分區是可能的。子分區既可以使用HASH分區，也可以使用KEY分區。這也被稱為複合分區。
(2) 每個分區必須有相同數量的子分區。
(3) 如果在一個分區表上的任何分區上使用SUBPARTITION來明確定義任何子分區，那麼就必須定義所有的子分區。
(4) 每個SUBPARTITION子句必須包含（至少）子分區的一個名字。
(5) 在每個子分區內，子分區的名字必須是惟一的，目前在整個表中，也要保持唯一。例如：
PARTITION BY RANGE(YEAR(registerTime))
SUBPARTITION BY HASH(TO_DAYS(registerTime))
(
PARTITION p0 VALUES LESS THAN (2017) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2020) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
)
子分區可以用於特別大的表，可以在多個磁盤間分配數據和索引。例如：
SUBPARTITION s0
DATA DIRECTORY = ‘/disk0/data’
INDEX DIRECTORY = ‘/disk0/idx’
,
,
SUBPARTITION s1
DATA DIRECTORY = ‘/disk1/data’
INDEX DIRECTORY = ‘/disk1/idx’
九、MySQL分區處理NULL值的方式
MySQL中的分區禁止空值NULL上沒有進行處理，無論它是一個列值還是一個用戶定義表達式的值，一般而言，在這種情況下MySQL把NULL視為0。如果你希望迴避這種做法，你應該在設計表時聲明列“NOT NULL”。
十、分區管理概述
可以對分區進行添加、刪除、重新定義、合併或拆分等管理操作。
① RANGE和LIST分區的管理
1. 刪除分區語句如：alter table tbl_test drop partition p0;
注意：
(1) 當刪除了一個分區，也同時刪除了該分區中所有的數據。
(2) 可以通過show create table tbl_test;來查看新的創建表的語句。
(3) 如果是LIST分區的話，刪除的數據不能新增進來，因為這些行的列值包含在已經刪除了的分區的值列表中。
2. 添加分區語句如：alter table tbl_test add partition(partition p3 values less than(50));
注意：
(1) 對於RANGE分區的表，只可以添加新的分區到分區列表的最高端。
(2) 對於LIST分區的表，不能添加已經包含在現有分區值列表中的任意值。
3. 如果希望能不丟失數據的條件下重新定義分區，可以使用如下語句：
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)
(1) 拆分分區如：
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10));
或者如：
ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5));
(2) 合併分區如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));
4. 刪除所有分區，但保留數據，形式：ALTER TABLE tbl_name remove partitioning;
② HASH和KEY分區的管理
1. 減少分區數量語句如：ALTER TABLE tbl_name COALESCE PARTITION 2;
2. 添加分區數量語句如：ALTER TABLE tbl_name add PARTITION partitions 2;
③ 其他分區管理語句
1. 重建分區 ：類似於先刪除保存在分區中的所有記錄，然後重新插入它們，可用於整理分區碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;
2. 優化分區 ：如果從分區中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB或TEXT類型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來收回沒有使用的空間，並整理分區數據文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;
3. 分析分區 ：讀取並保存分區的鍵分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;
4. 檢查分區 ：檢查分區中的數據或索引是否已經被破壞，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;
5. 修補分區 ：修補被破壞的分區，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;
十、查看分區信息
1. 查看分區信息：select * from information_schema.partitions where table_schema=’arch1′ and table_name = ‘tbl_test’ G;
2. 查看分區上的數據：select * from tbl_test partition(p0);
3. 查看MySQL會操作的分區：explain partitions select * from tbl_test where uuid = 2;
十一、 局限性
1. 最大分區數目不能超過1024，一般建議對單表的分區數不要超過50個。
2. 如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內。
3. 不支持外鍵。
4. 不支持全文索引，對分區表的分區鍵創建索引，那麼這個索引也將被分區。
5. 按日期進行分區很合適，因為很多日期函數可以用。但是對字符串來說合適的分區函數不太多。
6. 只有RANGE和LIST分區能進行子分區，HASH和KEY分區不能進行子分區。
7. 臨時表不能被分區。
8. 分區表對於單條記錄的查詢沒有優勢。
9. 要注意選擇分區的成本，沒插入一行數據都需要按照表達式篩選插入的分區。
10. 分區字段盡量不要可以為null
MySQL的數據文件有幾種？擴展名分別是什麼？
frm是表結構，MYD是數據，MYI是索引，如果用InnoDB只有frm數據和索引存在InnoDB的
數據文件
里。
默認MySQL
的數據庫是存放在…\MySQL\MySQL
Server
5.5\data文件夾下。一個數據庫是一個目錄，目錄下一個表對應三個文件，文件名是表名，
擴展名
分別是.frm、.MYD、.MYI（（數據文件：.
myd
）、（
索引文件
：.
MYI
）、（表
定義文件
：.
frm））。
擴展資料：
普通索引（由關鍵字
KEY
或
INDEX
定義的索引）的任務是加快對數據的訪問速度。因此，應該只為那些最經常出現查詢條件（WHERE
column
=）或排序條件（ORDER
BY
column）中的數據列創建索引。只要有可能，就應該選擇一個數據最整齊、最緊湊的數據列（如一個
整數類型
的數據列）來創建索引。
普通索引允許被索引的數據列包含重複的值。比如說，因為人有可能同名，所以同一個姓名在同一個“員工個人資料”數據表裡可能出現兩次或更多次。
參考資料來源：
百度百科
-mySQL
mysql中的表空間的概念是邏輯概念還是物理概念
一、系統表空間
在 MySQL 數據目錄下有一個名為 ibdata1 的文件，可以保存一張或者多張表。
923275 12M -rw-r—– 1 mysql mysql 12M 3月 18 10:42 ibdata1
這個文件就是 MySQL 的系統表空間文件，默認為 1 個，可以有多個，只需要在配置文件 my.cnf 裡面這樣定義即可。
innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend:max:800M系統表空間不僅可以是文件系統組成的文件，也可以是非文件系統組成的磁盤塊，比如裸設備，定義也很簡單innodb_data_file_path=/dev/nvme0n1p1:3Gnewraw;/dev/nvme0n1p2:2Gnewraw
系統表空間里都有些啥內容？
具體內容包括：double writer buffer、 change buffer、數據字典（MySQL 8.0 之前）、表數據、表索引。
那 MySQL 為什麼現在主流版本默認都不是系統表空間？
究其原因，系統表空間有三個最大的缺點：原因 1：無法做到自動收縮磁盤空間，造成很大的空間浪費。即使它包含的表都被刪掉，這部分空間也不會自動釋放。
二、單表空間
單表空間不同於系統表空間，每個表空間和表是一一對應的關係，每張表都有自己的表空間。具體在磁盤上表現為後綴為 .ibd 的文件。比如表 t1，對應的表空間文件為 t1.ibd917107 96K -rw-r—– 1 mysql mysql 96K 3月 18 16:13 t1.ibd
單表空間如何應用到具體的表呢？
有兩種方式：方式 1：在配置文件中開啟。在配置文件中開啟單表空間設置參數 innodb_filer_per_table，這樣默認對當前庫下所有表開啟單表空間。innodb_file_per_table=1另外也可以直接建表時指定單表空間mysql create table t1 (id int, r1 char(36)) tablespace innodb_file_per_table;
Query OK, 0 rows affected (0.04 sec)
單表空間除了解決之前說的系統表空間的幾個缺點外，還有其他的優點，詳細如下：
1. truncate table 操作比其他的任何錶空間都快；
2. 可以把不同的表按照使用場景指定在不同的磁盤目錄；
比如日誌表放在慢點的磁盤，把需要經常隨機讀的表放在 SSD 上等。
mysql create table ytt_dedicated (id int) data directory = ‘/var/lib/mysql-files’;
Query OK, 0 rows affected (0.04 sec)3. 可以用 optimize table 來收縮或者重建經常增刪改查的表。一般過程是這樣的：建立和原來表一樣的表結構和數據文件，把真實數據複製到臨時文件，再刪掉原始表定義和數據文件，最後把臨時文件的名字改為和原始表一樣的。
三、通用表空間
通用表空間先是出現在 MySQL Cluster 里，也就是 NDB 引擎。從 MySQL 5.7 引入到 InnoDB 引擎。通用表空間和系統表空間一樣，也是共享表空間。每個表空間可以包含一張或者多張表，也就是說通用表空間和表之間是一對多的關係。
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/201258.html
微信掃一掃 
支付寶掃一掃 