mysql分區(qū)表怎么看 mysql分區(qū)查詢sql怎么寫

mysql分區(qū)數(shù)據(jù)如何查看

select * from 表 partition (分區(qū)表名); --不管是1級分區(qū)還是2級分區(qū)都是用partition

創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站設(shè)計、做網(wǎng)站與策劃設(shè)計,石阡網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:石阡等地區(qū)。石阡做網(wǎng)站價格咨詢:18980820575

select partition_name , subpartition_name from information_schema.partitions where table_schema='你的數(shù)據(jù)庫名' and table_name='你的表名'; (這個語句可以查到你的表的分區(qū)名是什么,1級分區(qū)和2級分區(qū)都可以看)

mysql表分區(qū)使用及詳細介紹

一、分區(qū)概念

分區(qū)是將一個表分成多個區(qū)塊進行操作和保存，從而降低每次操作的數(shù)據(jù)，提高性能。而對于應(yīng)用來說則是透明的，從邏輯上看只有一張表，但在物理上這個表可能是由多個物理分區(qū)組成的，每個分區(qū)都是獨立的對象，可以進行獨立處理。

二、分區(qū)作用

1.可以邏輯數(shù)據(jù)分割，分割數(shù)據(jù)能夠有多個不同的物理文件路徑。

2.可以存儲更多的數(shù)據(jù)，突破系統(tǒng)單個文件最大限制。

3.提升性能，提高每個分區(qū)的讀寫速度，提高分區(qū)范圍查詢的速度。

4.可以通過刪除相關(guān)分區(qū)來快速刪除數(shù)據(jù)

5.通過跨多個磁盤來分散數(shù)據(jù)查詢，從而提高磁盤I/O的性能。

6.涉及到例如SUM()、COUNT()這樣聚合函數(shù)的查詢，可以很容易的進行并行處理。

7.可以備份和恢復(fù)獨立的分區(qū)，這對大數(shù)據(jù)量很有好處。

三、分區(qū)能支持的引擎

MySQL支持大部分引擎創(chuàng)建分區(qū)，入MyISAM、InnoDB等；不支持MERGE和CSV等來創(chuàng)建分區(qū)。同一個分區(qū)表中的所有分區(qū)必須是同一個存儲引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分區(qū)。

四、確認MySQL支持分區(qū)

從MySQL5.1開始引入分區(qū)功能，可以如下方式查看是否支持：

老版本用：SHOW VARIABLES LIKE '%partition%';

新版本用：show plugins;

五、分區(qū)類型

1. RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

例如，可以將一個表通過年份劃分成兩個分區(qū)，2001 -2010年、2011-2020。

2. LIST分區(qū)：類似于RANGE分區(qū)，LIST是列值匹配一個離散值集合中的某個值來進行選擇。

比如 根據(jù)字段 把值為1、3、5的放到一起，2、4、6的另外放到一起 等等...

3. HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇分區(qū)，該表達式使用將要插入到表中的這些行的列值來進行計算，這個函數(shù)必須產(chǎn)生非負整數(shù)值。

通過HASH運算來進行分區(qū)，分布的比較均勻

4. KEY分區(qū)：類似于按HASH分區(qū)，由MySQL服務(wù)器提供其自身的哈希函數(shù)。

按照KEY進行分區(qū)類似于按照HASH分區(qū)

六、分區(qū)創(chuàng)建注意事項

1. 如果表中存在primary key 或者 unique key 時，分區(qū)的列必須是paimary key或者unique key的一個組成部分，也就是說，分區(qū)函數(shù)的列只能從pk或者uk這些key中取子集

2. 如果表中不存在任何的paimary key或者unique key，則可以指定任何一個列作為分區(qū)列

3. 5.5版本前的RANGE、LIST、HASH分區(qū)要求分區(qū)鍵必須是int；MySQL5.5及以上，支持非整形的RANGE和LIST分區(qū)，即：range columns 和 list columns (可以用字符串來進行分區(qū))。

七、分區(qū)命名

1. 分區(qū)的名字基本上遵循其他MySQL 標識符應(yīng)當(dāng)遵循的原則，例如用于表和數(shù)據(jù)庫名字的標識符。應(yīng)當(dāng)注意的是， 分區(qū)的名字是不區(qū)分大小寫的 。

2. 無論使用何種類型的分區(qū)，分區(qū)總是在創(chuàng)建時就自動的順序編號，且從0開始記錄。

八、 創(chuàng)建分區(qū)

1. RANGE分區(qū)：

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分區(qū)下，uuid大于5且小于10放到p1分區(qū)下，uuid大于10且小于15放到p2分區(qū)下，uuid大于15 一直到最大值的存在p3分區(qū)下

2. LIST分區(qū)：

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分區(qū)，7/9/10放到p1分區(qū)，11/15放到p2分區(qū)。當(dāng)時用insert into時 如果uuid的值不存在p0/p1/p2分區(qū)時，則會插入失敗而報錯。

3. HASH分區(qū):

HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE分區(qū)和LIST分區(qū)中必須明確指定一個指定的列值或列值集合以指定應(yīng)該保存在哪個分區(qū)中。而在HASH分區(qū)中，MySQL會自動完成這些工作，要做的只是基于將要被哈希的列值指定一個表達式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量，如：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY HASH (uuid) (

PARTITIONS 3

));

解讀：MySQL自動創(chuàng)建3個分區(qū)，在執(zhí)行insert into時，根據(jù)插入的uuid通過算法來自動分配區(qū)間。

注意：

(1) 由于每次插入、更新、刪除一行，這個表達式都要計算一次，這意味著非常復(fù)雜的表達式可能會引起性能問題，尤其是在執(zhí)行同時影響大量行的運算（例如批量插入）的時候。

(2) 最有效率的哈希函數(shù)是只對單個表列進行計算，并且它的值隨列值進行一致的增大或減小，因為這考慮了在分區(qū)范圍上的“修剪”。也就是說，表達式值和它所基于的列的值變化越接近，就越能有效地使用該表達式來進行HASH分區(qū)。

3.1：線性HASH分區(qū)

線性HASH分區(qū)在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字。

線性HASH分區(qū)的有點在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有及其大量數(shù)據(jù)的表。它的缺點在于各個分區(qū)間數(shù)據(jù)的分布不大可能均衡。

4. KEY分區(qū)

類似于HASH分區(qū)，HASH分區(qū)允許用戶自定義的表達式，而KEY分區(qū)則不允許使用用戶自定義的表達式；HASH分區(qū)只支持整數(shù)分區(qū)，KEY分區(qū)支持除了blob和text類型之外的其他數(shù)據(jù)類型分區(qū)。

與HASH分區(qū)不同，創(chuàng)建KEY分區(qū)表的時候，可以不指定分區(qū)鍵，默認會選擇使用主鍵或唯一鍵作為分區(qū)鍵，沒有主鍵或唯一鍵，就必須指定分區(qū)鍵。

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY LINEAR Key (uuid)

PARTITIONS 3;

解讀：根據(jù)分區(qū)鍵來進行分區(qū)

5. 子分區(qū)

子分區(qū)是分區(qū)表中，每個分區(qū)的再次分割，適合保存非常大量的數(shù)據(jù)。

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

);

解讀：主分區(qū)使用RANGE按照年來進行分區(qū)，有3個RANGE分區(qū)。這3個分區(qū)中又被進一步分成了2個子分區(qū)，實際上，整個表被分成了3 * 2 = 6個分區(qū)。每個子分區(qū)按照天進行HASH分區(qū)。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

(1) 在MySQL5.1中，對于已經(jīng)通過RANGE或LIST分區(qū)了的表在進行子分區(qū)是可能的。子分區(qū)既可以使用HASH分區(qū)，也可以使用KEY分區(qū)。這也被稱為復(fù)合分區(qū)。

(2) 每個分區(qū)必須有相同數(shù)量的子分區(qū)。

(3) 如果在一個分區(qū)表上的任何分區(qū)上使用SUBPARTITION來明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)。

(4) 每個SUBPARTITION子句必須包含（至少）子分區(qū)的一個名字。

(5) 在每個子分區(qū)內(nèi)，子分區(qū)的名字必須是惟一的，目前在整個表中，也要保持唯一。例如：

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

)

)

子分區(qū)可以用于特別大的表，可以在多個磁盤間分配數(shù)據(jù)和索引。例如：

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx'

,

,

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

INDEX DIRECTORY = '/disk1/idx'

九、MySQL分區(qū)處理NULL值的方式

MySQL中的分區(qū)禁止空值NULL上沒有進行處理，無論它是一個列值還是一個用戶定義表達式的值，一般而言，在這種情況下MySQL把NULL視為0。如果你希望回避這種做法，你應(yīng)該在設(shè)計表時聲明列“NOT NULL”。

十、分區(qū)管理概述

可以對分區(qū)進行添加、刪除、重新定義、合并或拆分等管理操作。

① RANGE和LIST分區(qū)的管理

1. 刪除分區(qū)語句如：alter table tbl_test drop partition p0;

注意：

(1) 當(dāng)刪除了一個分區(qū)，也同時刪除了該分區(qū)中所有的數(shù)據(jù)。

(2) 可以通過show create table tbl_test;來查看新的創(chuàng)建表的語句。

(3) 如果是LIST分區(qū)的話，刪除的數(shù)據(jù)不能新增進來，因為這些行的列值包含在已經(jīng)刪除了的分區(qū)的值列表中。

2. 添加分區(qū)語句如：alter table tbl_test add partition(partition p3 values less than(50));

注意：

(1) 對于RANGE分區(qū)的表，只可以添加新的分區(qū)到分區(qū)列表的最高端。

(2) 對于LIST分區(qū)的表，不能添加已經(jīng)包含在現(xiàn)有分區(qū)值列表中的任意值。

3. 如果希望能不丟失數(shù)據(jù)的條件下重新定義分區(qū)，可以使用如下語句：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)

(1) 拆分分區(qū)如：

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) 合并分區(qū)如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

4. 刪除所有分區(qū)，但保留數(shù)據(jù)，形式：ALTER TABLE tbl_name remove partitioning;

② HASH和KEY分區(qū)的管理

1. 減少分區(qū)數(shù)量語句如：ALTER TABLE tbl_name COALESCE PARTITION 2;

2. 添加分區(qū)數(shù)量語句如：ALTER TABLE tbl_name add PARTITION partitions 2;

③ 其他分區(qū)管理語句

1. 重建分區(qū) ：類似于先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，可用于整理分區(qū)碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

2. 優(yōu)化分區(qū) ：如果從分區(qū)中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB或TEXT類型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

3. 分析分區(qū) ：讀取并保存分區(qū)的鍵分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

4. 檢查分區(qū) ：檢查分區(qū)中的數(shù)據(jù)或索引是否已經(jīng)被破壞，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

5. 修補分區(qū) ：修補被破壞的分區(qū)，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;

十、查看分區(qū)信息

1. 查看分區(qū)信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G;

2. 查看分區(qū)上的數(shù)據(jù)：select * from tbl_test partition(p0);

3. 查看MySQL會操作的分區(qū)：explain partitions select * from tbl_test where uuid = 2;

十一、 局限性

1. 最大分區(qū)數(shù)目不能超過1024，一般建議對單表的分區(qū)數(shù)不要超過50個。

2. 如果含有唯一索引或者主鍵，則分區(qū)列必須包含在所有的唯一索引或者主鍵在內(nèi)。

3. 不支持外鍵。

4. 不支持全文索引，對分區(qū)表的分區(qū)鍵創(chuàng)建索引，那么這個索引也將被分區(qū)。

5. 按日期進行分區(qū)很合適，因為很多日期函數(shù)可以用。但是對字符串來說合適的分區(qū)函數(shù)不太多。

6. 只有RANGE和LIST分區(qū)能進行子分區(qū)，HASH和KEY分區(qū)不能進行子分區(qū)。

7. 臨時表不能被分區(qū)。

8. 分區(qū)表對于單條記錄的查詢沒有優(yōu)勢。

9. 要注意選擇分區(qū)的成本，沒插入一行數(shù)據(jù)都需要按照表達式篩選插入的分區(qū)。

10. 分區(qū)字段盡量不要可以為null

MySQL分區(qū)表簡介

我們的業(yè)務(wù)只存近一段時間的數(shù)據(jù)，因此有大量表需要清理 歷史 數(shù)據(jù)，目前使用的delete清理數(shù)據(jù)，存在以下問題。為避免同時支持大量delete，我們的清理任務(wù)只在低峰期串行執(zhí)行，導(dǎo)致任務(wù)過多時需要排隊，甚至失敗的情況；數(shù)據(jù)清理使用delete語句，表數(shù)據(jù)量較大時，對數(shù)據(jù)庫造成很大壓力；即使我們刪除了舊數(shù)據(jù)，已刪除的數(shù)據(jù)仍占據(jù)存儲空間，底層數(shù)據(jù)文件并沒有立刻變小，以至于形成數(shù)據(jù)空洞。

查看MySQL官方文檔時，發(fā)現(xiàn)了分區(qū)表，因此基于官方文檔總結(jié)一下。

MySQL邏輯上為一個表，物理上存儲在多個文件中，這是 MySQL 支持的功能（5.1 開始）, 8.0 版本只 InnoDB 和 NDB 支持分區(qū)表。

優(yōu)點：

缺點：

根據(jù)分區(qū)表鍵值的范圍把數(shù)據(jù)存儲到表的不同分區(qū)中，適用于以時間或日期作為分區(qū)類型，方便數(shù)據(jù)清理。

小提示：

1.當(dāng)插入數(shù)據(jù)分區(qū)不存在時會報錯：Table has no partition for value xxx;

2.Range類型分區(qū)字段必須是數(shù)值，時間類型可用函數(shù)轉(zhuǎn)換為數(shù)值；

3.分區(qū)字段列值可以為null，所有為null的數(shù)據(jù)將存在最小的分區(qū)中；

按分區(qū)鍵取值的列表進行分區(qū)，每一行數(shù)據(jù)須找到對應(yīng)的分區(qū)列表，否則數(shù)據(jù)插入失敗

小提示：

根據(jù)指定分區(qū)表達式的整數(shù)值以及分區(qū)數(shù)進行數(shù)據(jù)劃分(mod函數(shù))

小提示：

按鍵分區(qū)類似于按哈希分區(qū)，只是哈希分區(qū)使用用戶定義的表達式，用于鍵分區(qū)的哈希函數(shù)由 MySQL 服務(wù)器提供。NDB 集群為此使用 MD5() ; 對于使用其他存儲引擎的表，服務(wù)器使用自己的內(nèi)部哈希函數(shù)。

小提示：

子分區(qū)(subpartitioning)也稱為復(fù)合分區(qū)(composite partitioning) ，是已分區(qū)表中每個分區(qū)的進一步劃分

小提示：

小提示：

Mysql表分區(qū)狀態(tài)查詢

一、查詢mysql表是否為分區(qū)表：可以查看表具有哪幾個分區(qū)、分區(qū)的方法、分區(qū)中數(shù)據(jù)的記錄數(shù)等信息

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='xw_coobill_order';

二、查詢表有多少個分區(qū)

SELECT TABLE_NAME, COUNT(*) AS CNT

FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL

GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;

三、分析執(zhí)行語句

explain partitions select * from range_datetime where hiredate = '20151207124503' and hiredate='20151210111230';

四、分區(qū)管理

常規(guī)HASH和線性HASH的增加收縮分區(qū)的原理是一樣的。增加和收縮分區(qū)后原來的數(shù)據(jù)會根據(jù)現(xiàn)有的分區(qū)數(shù)量重新分布。HASH分區(qū)不能刪除分區(qū)，所以不能使用DROP PARTITION操作進行分區(qū)刪除操作；

只能通過ALTER TABLE ... COALESCE PARTITION num來合并分區(qū)，這里的num是減去的分區(qū)數(shù)量；

可以通過ALTER TABLE ... ADD PARTITION PARTITIONS num來增加分區(qū)，這里是null是在原先基礎(chǔ)上再增加的分區(qū)數(shù)量。

分享名稱：mysql分區(qū)表怎么看 mysql分區(qū)查詢sql怎么寫
轉(zhuǎn)載注明：http://muchs.cn/article48/dohiohp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標簽優(yōu)化、網(wǎng)站營銷、電子商務(wù)、網(wǎng)站導(dǎo)航、小程序開發(fā)、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)