mysql如何建表空間

在mysql中，也出現(xiàn)了類(lèi)似oracle中的表空間概念。

創(chuàng)新互聯(lián)公司是專(zhuān)業(yè)的潼關(guān)網(wǎng)站建設(shè)公司，潼關(guān)接單;提供成都網(wǎng)站建設(shè)、成都做網(wǎng)站,網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專(zhuān)業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行潼關(guān)網(wǎng)站開(kāi)發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專(zhuān)業(yè)做搜索引擎喜愛(ài)的網(wǎng)站,專(zhuān)業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!

不過(guò)二者好像不同？具體不太清楚oracle是怎么回事。

mysql表空間是什么概念呢？

開(kāi)啟了Innodb的innodb_file_per_table這個(gè)參數(shù)之后【innodb_file_per_table = 1】，也就是啟用InnoDB的獨(dú)立表空間模式，便于管理。此時(shí)，在新建的innodb表的數(shù)據(jù)庫(kù)目錄下會(huì)多出來(lái)一個(gè).ibd這個(gè)文件。這個(gè)就是此時(shí)的數(shù)據(jù)文件了。mysql會(huì)把這個(gè)innodb表的數(shù)據(jù)存放在這個(gè)文件中。并且每個(gè)innodb表此時(shí)都會(huì)對(duì)應(yīng)這么一個(gè)ibd文件。

看官方文檔：

If innodb_file_per_table is disabled (the default), InnoDB creates tables in the system tablespace. Ifinnodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the system tablespace.

那么這樣做有什么好處呢？

可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫(kù)之間移動(dòng)。具體怎么移動(dòng)呢？假設(shè)有兩個(gè)數(shù)據(jù)庫(kù)，一個(gè)test，一個(gè)tt。

InnoDB 默認(rèn)會(huì)將所有的數(shù)據(jù)庫(kù)InnoDB引擎的表數(shù)據(jù)存儲(chǔ)在一個(gè)共享空間中：ibdata1，這樣就感覺(jué)不爽，增刪數(shù)據(jù)庫(kù)的時(shí)候，ibdata1文件不會(huì)自動(dòng)收縮，單個(gè)數(shù)據(jù)庫(kù)的備份也將成為問(wèn)題。通常只能將數(shù)據(jù)使用mysqldump 導(dǎo)出，然后再導(dǎo)入解決這個(gè)問(wèn)題。共享表空間在Insert操作上少有優(yōu)勢(shì)。其它都沒(méi)獨(dú)立表空間表現(xiàn)好。當(dāng)啟用獨(dú)立表空間時(shí)，請(qǐng)合理調(diào)整一 下innodb_open_files 的值。

-------------------------------------------------------------------------------

需要說(shuō)明的是：

1、設(shè)置了獨(dú)立表空間之后，如果改成了共享表空間，那么，此時(shí)如果執(zhí)行表的插入操作，數(shù)據(jù)會(huì)存放在哪里呢？

對(duì)于之前已經(jīng)存在了的表，還是存放在獨(dú)立表空間。對(duì)于新建的表，就會(huì)存放在共享表空間了。

2、如果一開(kāi)始用了獨(dú)立表空間，后來(lái)改了innodb_file_per_table變量的值，改成獨(dú)立表空間了，那么數(shù)據(jù)如何存儲(chǔ)？

對(duì)于已經(jīng)存在了的innodb引擎的表來(lái)說(shuō)，數(shù)據(jù)還是存放在共享表空間的，而此時(shí)如果創(chuàng)建了新的表，那么就會(huì)在數(shù)據(jù)庫(kù)的目錄中多出一個(gè).ibd的文件用于存儲(chǔ)這個(gè)新表的數(shù)據(jù)。

總結(jié)上面的1、2，就是：原來(lái)的還是按照原來(lái)的方式存儲(chǔ)。新的表按照新的規(guī)則來(lái)存儲(chǔ)。

mysql之表分區(qū)

分區(qū)介紹：

一、什么是分區(qū)？

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

二、分區(qū)作用

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

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

3.提升性能，提高每個(gè)分區(qū)的讀寫(xiě)速度，提高分區(qū)范圍查詢(xún)的速度。

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

5.通過(guò)跨多個(gè)磁盤(pán)來(lái)分散數(shù)據(jù)查詢(xún)，從而提高磁盤(pán)I/O的性能。

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

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

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

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

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

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

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

新版本用：show plugins;

五、分區(qū)類(lèi)型

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

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

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

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

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

通過(guò)HASH運(yùn)算來(lái)進(jìn)行分區(qū)，分布的比較均勻

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

按照KEY進(jìn)行分區(qū)類(lèi)似于按照HASH分區(qū)

六、使用分區(qū)注意事項(xiàng)

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

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

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

七、分區(qū)命名

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

2. 無(wú)論使用何種類(lèi)型的分區(qū)，分區(qū)總是在創(chuàng)建時(shí)就自動(dòng)的順序編號(hào)，且從0開(kāi)始記錄。

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

1. RANGE分區(qū)：

解讀：以上為 uuid小于5時(shí)放到p0分區(qū)下，uuid大于5且小于10放到p1分區(qū)下，uuid大于10且小于15放到p2分區(qū)下，uuid大于15 一直到最大值的存在p3分區(qū)下

2. LIST分區(qū)：

解讀：以上為uuid 等于1/2/3/5時(shí)放到p0分區(qū)，7/9/10放到p1分區(qū)，11/15放到p2分區(qū)。當(dāng)時(shí)用insert into時(shí) 如果uuid的值不存在p0/p1/p2分區(qū)時(shí)，則會(huì)插入失敗而報(bào)錯(cuò)。

3. HASH分區(qū):

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

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

注意：

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

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

3.1：線(xiàn)性HASH分區(qū)

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

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

4. KEY分區(qū)

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

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

解讀：根據(jù)分區(qū)鍵來(lái)進(jìn)行分區(qū)

5. 子分區(qū)

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

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

注意：

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

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

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

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

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

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

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

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

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

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

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

注意：

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

(2) 可以通過(guò)show create table tbl_test;來(lái)查看新的創(chuàng)建表的語(yǔ)句。

(3) 如果是LIST分區(qū)的話(huà)，刪除的數(shù)據(jù)不能新增進(jìn)來(lái)，因?yàn)檫@些行的列值包含在已經(jīng)刪除了的分區(qū)的值列表中。

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

注意：

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

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

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

REORGANIZE會(huì)對(duì)分區(qū)的數(shù)據(jù)進(jìn)行重構(gòu)。

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ù)量語(yǔ)句如：ALTER TABLE tbl_name COALESCE PARTITION 2;

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

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

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

2. 優(yōu)化分區(qū)：如果從分區(qū)中刪除了大量的行，或者對(duì)一個(gè)帶有可變長(zhǎng)度的行（也就是說(shuō)，有VARCHAR，BLOB或TEXT類(lèi)型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來(lái)收回沒(méi)有使用的空間，并整理分區(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. 修補(bǔ)分區(qū)：修補(bǔ)被破壞的分區(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會(huì)操作的分區(qū)：explain partitions select * from tbl_test where uuid = 2;

十一、 局限性

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

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

3. 不支持外鍵。

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

5. 按日期進(jìn)行分區(qū)很合適，因?yàn)楹芏嗳掌诤瘮?shù)可以用。但是對(duì)字符串來(lái)說(shuō)合適的分區(qū)函數(shù)不太多。

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

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

8. 分區(qū)表對(duì)于單條記錄的查詢(xún)沒(méi)有優(yōu)勢(shì)。

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

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

MySQL innodb引擎深入講解

表空間(ibd文件),一個(gè)MySQL實(shí)例可以對(duì)應(yīng)多個(gè)表空間，用于存儲(chǔ)記錄，索引等數(shù)據(jù)。

段，分為數(shù)據(jù)段、索引段、回滾段，innodb是索引組織表，數(shù)據(jù)段就是B+Tree的葉子節(jié)點(diǎn)，索引段為非葉子節(jié)點(diǎn)，段用來(lái)管理多個(gè)區(qū)。

區(qū)，表空間的單元結(jié)構(gòu)，每個(gè)區(qū)的大小為1M,默認(rèn)情況下，innodb存儲(chǔ)引擎頁(yè)大小為16K，即一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁(yè)。

頁(yè)，是innodb存儲(chǔ)引擎磁盤(pán)管理的最小單元，每個(gè)頁(yè)的大小為16K，為了保證頁(yè)的連續(xù)性，innodb存儲(chǔ)引擎每次從磁盤(pán)申請(qǐng)4~5個(gè)區(qū)。

行，innodb存儲(chǔ)引擎數(shù)據(jù)是按行進(jìn)行存儲(chǔ)的。Trx_id 最后一次事務(wù)操作的id、roll_pointer滾動(dòng)指針。

i nnodb的內(nèi)存結(jié)構(gòu) ，由Buffer Pool、Change Buffer和Log Buffer組成。

Buffer Pool : 緩沖池是主內(nèi)存中的一個(gè)區(qū)域，里面可以緩存磁盤(pán)上經(jīng)常操作的真實(shí)數(shù)據(jù)，在執(zhí)行增刪改查操作時(shí)，先操作緩沖池中的數(shù)據(jù)(若緩沖池么有數(shù)據(jù)，則從磁盤(pán)加載并緩存)，然后再以一定頻率刷新磁盤(pán)，從而減少磁盤(pán)IO，加快處理速度。

緩沖池以page頁(yè)為單位，底層采用鏈表數(shù)據(jù)結(jié)構(gòu)管理page，根據(jù)狀態(tài)，將page分為三種類(lèi)型:

1、free page 即空閑page，未被使用。

2、clean page 被使用page，數(shù)據(jù)沒(méi)有被修改過(guò)。

3、dirty page 臟頁(yè)，被使用page，數(shù)據(jù)被修改過(guò)，這個(gè)page當(dāng)中的數(shù)據(jù)和磁盤(pán)當(dāng)中的數(shù)據(jù) 不一致。說(shuō)得簡(jiǎn)單點(diǎn)就是緩沖池中的數(shù)據(jù)改了，磁盤(pán)中的沒(méi)改，因?yàn)檫€沒(méi)刷寫(xiě)到磁盤(pán)。

Change Buffer ：更改緩沖區(qū)(針對(duì)于非唯一二級(jí)索引頁(yè))，在執(zhí)行DML語(yǔ)句時(shí)，如果這些數(shù)據(jù)page沒(méi)有在Buffer Pool中，不會(huì)直接操作磁盤(pán)，而會(huì)將數(shù)據(jù)變更存在更改緩沖區(qū)Change Buffer中，在未來(lái)數(shù)據(jù)被讀取時(shí)。再將數(shù)據(jù)合并恢復(fù)到Buffer Pool中，再將合并后的數(shù)據(jù)刷新到磁盤(pán)中。

二級(jí)索引通常是非唯一的，并且以相對(duì)隨機(jī)的順序插入二級(jí)索引頁(yè)，同樣，刪除和更新可能會(huì)影響索引樹(shù)中不相鄰的二級(jí)索引頁(yè)。如果每一次都操作磁盤(pán)，會(huì)造成大量磁盤(pán)IO，有了Change Buffer之后，我們可以在緩沖池中進(jìn)行合并處理，減少磁盤(pán)IO。

Adaptive Hash Index： 自適應(yīng)hash索引，用于優(yōu)化對(duì)Buffer Pool數(shù)據(jù)的查詢(xún)，InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上各索引頁(yè)的查詢(xún)，如果觀察到hash索引可以提升速度，則建立hash索引，稱(chēng)之為自適應(yīng)hash索引。無(wú)需人工干預(yù)，系統(tǒng)根據(jù)情況自動(dòng)完成。

參數(shù)：innodb_adaptive_hash_index

Log Buffer: 日志緩沖區(qū)，用來(lái)保存要寫(xiě)入到磁盤(pán)中的log日志數(shù)據(jù)(redo log、undo log)，默認(rèn)大小為16M，日志緩沖區(qū)的日志會(huì)定期刷新到磁盤(pán)中，如果需要更新，插入或刪除許多行的事務(wù)，增加日志緩沖區(qū)的大小可以節(jié)省磁盤(pán)IO。

參數(shù): innodb_log_buffer_size 緩沖區(qū)大小

innodb_flush_log_at_trx_commit 日志刷新到磁盤(pán)時(shí)機(jī)

innodb_flush_log_at_trx_commit=1 表示日志在每次事務(wù)提交時(shí)寫(xiě)入并刷新到磁盤(pán)

2 表示日志在每次事務(wù)提交后寫(xiě)入，并每秒刷新到磁盤(pán)一次

0 表示每秒將日志寫(xiě)入并刷新到磁盤(pán)一次。

InnoDB 的磁盤(pán)結(jié)構(gòu)，由系統(tǒng)表空間(ibdata1)，獨(dú)立表空間(*.ibd),通用表空間，撤銷(xiāo)表空間(undo tablespaces), 臨時(shí)表空間(Temporary Tablespaces), 雙寫(xiě)緩沖區(qū)(Doublewrite Buffer files), 重做日志(Redo Log).

系統(tǒng)表空間(ibdata1)： 系統(tǒng)表空間是更改緩沖區(qū)的存儲(chǔ)區(qū)域，如果表是在系統(tǒng)表空間而不是每個(gè)表文件或者通用表空間中創(chuàng)建的，它也可能包含表和索引數(shù)據(jù)。

參數(shù)為: innodb_data_file_path

獨(dú)立表空間(*.ibd): 每個(gè)表的文件表空間包含單個(gè)innodb表的數(shù)據(jù)和索引，并存儲(chǔ)在文件系 統(tǒng)上的單個(gè)數(shù)據(jù)文件中。 參數(shù): innodb_file_per_table

通用表空間: 需要通過(guò)create tablespace 語(yǔ)法創(chuàng)建，創(chuàng)建表時(shí) 可以指定該表空間。

create tablespace xxx add datafile 'file_name' engine=engine_name

create table table_name .... tablespace xxx

撤銷(xiāo)表空間(undo tablespaces): MySQL實(shí)例在初始化時(shí)會(huì)自動(dòng)創(chuàng)建兩個(gè)默認(rèn)的undo表空間(初始大小16K，undo_001,undo_002)，用于存儲(chǔ)undo log 日志

臨時(shí)表空間(Temporary Tablespaces)： innodb使用會(huì)話(huà)臨時(shí)表空和全局表空間，存儲(chǔ)用 戶(hù)創(chuàng)建的臨時(shí)表等數(shù)據(jù)。

雙寫(xiě)緩沖區(qū)(Doublewrite Buffer files)： innodb引擎將數(shù)據(jù)頁(yè)從Buffer Pool刷新到磁盤(pán)前，先將數(shù)據(jù)頁(yè)寫(xiě)入緩沖區(qū)文件中，便于系統(tǒng)異常時(shí)恢復(fù)數(shù)據(jù)。

重做日志(Redo Log)： 是用來(lái)實(shí)現(xiàn)事務(wù)的持久性，該日志文件由兩部分組成，重做日志緩沖區(qū)(redo log buffer)以及重做日志文件(redo log)，前者是在內(nèi)存中，后者在磁盤(pán)中，當(dāng)事務(wù)提交之后會(huì)把修改信息都會(huì)存儲(chǔ)到該日志中，用于在刷新臟頁(yè)到磁盤(pán)時(shí)，發(fā)送錯(cuò)誤時(shí)，進(jìn)行數(shù)據(jù)恢復(fù)使用。以循環(huán)方式寫(xiě)入重做日志文件，涉及兩個(gè)文件ib_logfile0,ib_logfile1。

那內(nèi)存結(jié)構(gòu)中的數(shù)據(jù)是如何刷新到磁盤(pán)中的? 在MySQL中有4個(gè)線(xiàn)程負(fù)責(zé)刷新日志到磁盤(pán)。

1、Master Thread， mysql核心后臺(tái)線(xiàn)程，負(fù)責(zé)調(diào)度其它線(xiàn)程，還負(fù)責(zé)將緩沖池中的數(shù)據(jù)異 步刷新到磁盤(pán)中，保持?jǐn)?shù)據(jù)的一致性，還包括臟頁(yè)的刷新，合并插入緩沖、undo頁(yè)的回 收。

2、IO Thread，在innodb存儲(chǔ)引擎中大量使用了AIO來(lái)處理IO請(qǐng)求，這樣可以極大地提高數(shù) 據(jù)庫(kù)的性能，而IO Thead主要負(fù)責(zé)這些IO請(qǐng)求的回調(diào)。

4個(gè)讀線(xiàn)程 Read thread負(fù)責(zé)讀操作

4個(gè)寫(xiě)線(xiàn)程write thread負(fù)責(zé)寫(xiě)操作

1個(gè)Log thread線(xiàn)程 負(fù)責(zé)將日志緩沖區(qū)刷新到磁盤(pán)

1個(gè)insert buffer線(xiàn)程 負(fù)責(zé)將寫(xiě)入緩沖區(qū)內(nèi)容刷新到磁盤(pán)

3、Purge Thread，主要用于回收事務(wù)已經(jīng)提交了的undo log，在事務(wù)提交之后，undo log 可能不用了，就用它來(lái)回收。

4、Page Cleaner Thread， 協(xié)助Master Thread 刷新臟頁(yè)到磁盤(pán)的線(xiàn)程，它可以減輕主線(xiàn)程 的壓力，減少阻塞。

事務(wù)就是一組操作的集合，它是一個(gè)不可分割的工作單位，事務(wù)會(huì)把所有的操作作為一個(gè)整體一起向系統(tǒng)提交或撤銷(xiāo)操作請(qǐng)求，即這些操作要么同時(shí)成功，要么同時(shí)失效。

事務(wù)的4大特性分為:

如何保證事務(wù)的4大特性，原子性，一致性和持久性是由innodb存儲(chǔ)引擎底層的兩份日志來(lái)保證的，分別是redo log和undo log。對(duì)于隔離性是由鎖機(jī)制和MVCC(多版本并發(fā)控制)來(lái)實(shí)現(xiàn)的。

redo log，稱(chēng)為重做日志，記錄的是事務(wù)提交時(shí)數(shù)據(jù)頁(yè)的物理修改，是用來(lái)實(shí)現(xiàn)事務(wù)的持久性。該日志文件由兩部分組成: 重做日志緩沖redo log buffer及重做日志文件redo log file，前者是在內(nèi)存中，后者是在磁盤(pán)中，當(dāng)事務(wù)提交之后會(huì)把所有修改信息都存到該日志文件中，用于在刷新臟頁(yè)到磁盤(pán)，發(fā)送錯(cuò)誤時(shí)，進(jìn)行數(shù)據(jù)的恢復(fù)使用，從而保證事務(wù)的持久性。

具體的操作流程是：

1、客戶(hù)端發(fā)起事務(wù)操作，包含多條DML語(yǔ)句。首先去innodb中的buffer pool中的數(shù)據(jù)頁(yè)去查找有沒(méi)有我們要更新的這些數(shù)據(jù)，如果沒(méi)有則通過(guò)后臺(tái)線(xiàn)程從磁盤(pán)中加載到buffer pool對(duì)應(yīng)的數(shù)據(jù)頁(yè)中，然后就可以在緩沖池中進(jìn)行數(shù)據(jù)操作了。

2、此時(shí)緩沖池中的數(shù)據(jù)頁(yè)發(fā)生了變更，還沒(méi)刷寫(xiě)到磁盤(pán)，這個(gè)數(shù)據(jù)頁(yè)稱(chēng)為臟頁(yè)。臟頁(yè)不是實(shí)時(shí)刷新到磁盤(pán)的，而是根據(jù)你配置的刷寫(xiě)策略進(jìn)行刷寫(xiě)到磁盤(pán)的（innodb_flush_log_at_trx_commit，0,1,2三個(gè)值）。如果臟頁(yè)在往磁盤(pán)刷新的時(shí)候出現(xiàn)了故障，會(huì)丟失數(shù)據(jù)，導(dǎo)致事務(wù)的持久性得不到保證。為了避免這種現(xiàn)象，當(dāng)對(duì)緩沖池中的數(shù)據(jù)進(jìn)行增刪改操作時(shí)，會(huì)把增刪改記錄到redo log buffer當(dāng)中，redo log buffer會(huì)把數(shù)據(jù)頁(yè)的物理變更持久化到磁盤(pán)文件中(ib_logfile0/ib_logfile1)。如果臟頁(yè)刷新失敗，就可以通過(guò)這兩個(gè)日志文件進(jìn)行恢復(fù)。

undo log，它是用來(lái)解決事務(wù)的原子性的，也稱(chēng)為回滾日志。用于記錄數(shù)據(jù)被修改前的信息，作用包括:提供回滾和MVCC多版本并發(fā)控制。

undo log和redo log的記錄物理日志不一樣，它是邏輯日志。可以認(rèn)為當(dāng)delete一條記錄時(shí)，undo log中會(huì)記錄一條對(duì)應(yīng)的insert記錄，當(dāng)update一條記錄時(shí)，它記錄一條對(duì)應(yīng)相反的update記錄，當(dāng)執(zhí)行rollback時(shí)，就可以從undo log中的邏輯記錄讀取到相應(yīng)的內(nèi)容并進(jìn)行回滾。

undo log銷(xiāo)毀: undo log 在事務(wù)執(zhí)行時(shí)產(chǎn)生，事務(wù)提交時(shí)，并不會(huì)立即刪除undo log，因?yàn)檫@些日子可能用于MVCC。

undo log存儲(chǔ): undo log 采用段的方式進(jìn)行管理和記錄，存放在前面介紹的rollback segment回滾段中，內(nèi)部包含1024個(gè)undo log segment。

mvcc(multi-Version Concurrency Control),多版本并發(fā)控制，指維護(hù)一個(gè)數(shù)據(jù)的多個(gè)版本，使得讀寫(xiě)操作沒(méi)有沖突，快照讀為MySQL實(shí)現(xiàn)MVCC提供了一個(gè)非阻塞讀功能，MVCC的具體實(shí)現(xiàn)，還需要依賴(lài)于數(shù)據(jù)庫(kù)記錄中的三個(gè)隱式字段，undo log日志、readView。

read committed 每次select 都生成一個(gè)快照讀

repeatable read 開(kāi)啟事務(wù)后第一個(gè)select語(yǔ)句才是快照讀的地方

serializable 快照讀會(huì)退化為當(dāng)前讀。

mvcc的實(shí)現(xiàn)原理

DB_TRX_ID: 最近修改事務(wù)ID，記錄插入這條記錄或最后一次修改該記錄的事務(wù)ID

DB_ROLL_PTR: 回滾指針，指向這條記錄的上一個(gè)版本，用于配合undo log，指向上一個(gè) 版本

DB_ROW_ID: 隱藏主鍵，如果表結(jié)構(gòu)沒(méi)有指定主鍵，將會(huì)生成該隱藏字段。

m_ids當(dāng)前活躍的事務(wù)ID集合

min_trx_id: 最小活躍事務(wù)id

max_trx_id: 預(yù)分配事務(wù)ID，當(dāng)前最大事務(wù)id+1，因?yàn)槭聞?wù)id是自增的

creator_trx_id: ReadView創(chuàng)建者的事務(wù)ID

版本鏈數(shù)據(jù)訪(fǎng)問(wèn)規(guī)則:

trx_id: 表示當(dāng)前的事務(wù)ID

1、trx_id == creator_trx_id? 可以訪(fǎng)問(wèn)讀版本--成立的話(huà),說(shuō)明數(shù)據(jù)是當(dāng)前這個(gè)事務(wù)更改的

2、trx_id 成立,說(shuō)明數(shù)據(jù)已經(jīng)提交了。

3、trx_idmax_trx_id？不可用訪(fǎng)問(wèn)讀版本- 成立的話(huà)，說(shuō)明該事務(wù)是在ReadView生成后才開(kāi)啟的。

4、min_trx_id

mysql 如何分配內(nèi)存

我們?nèi)匀皇褂脙蓚€(gè)會(huì)話(huà)，一個(gè)會(huì)話(huà) run，用于運(yùn)行主 SQL；另一個(gè)會(huì)話(huà) ps，用于進(jìn)行 performance_schema 的觀察：

主會(huì)話(huà)線(xiàn)程號(hào)為 29，

將 performance_schema 中的統(tǒng)計(jì)量重置，

臨時(shí)表的表大小限制取決于參數(shù)? tmp_table_size 和 max_heap_table_size 中較小者，我們實(shí)驗(yàn)中以設(shè)置 max_heap_table_size 為例。

我們將會(huì)話(huà)級(jí)別的臨時(shí)表大小設(shè)置為 2M（小于上次實(shí)驗(yàn)中臨時(shí)表使用的空間），執(zhí)行使用臨時(shí)表的 SQL：

查看內(nèi)存的分配記錄：

會(huì)發(fā)現(xiàn)內(nèi)存分配略大于 2M，我們猜測(cè)臨時(shí)表會(huì)比配置略多一點(diǎn)消耗，可以忽略。

查看語(yǔ)句的特征值：

可以看到語(yǔ)句使用了一次需要落磁盤(pán)的臨時(shí)表。

那么這張臨時(shí)表用了多少的磁盤(pán)呢？

我們開(kāi)啟 performance_schema 中 waits 相關(guān)的統(tǒng)計(jì)項(xiàng)：

重做實(shí)驗(yàn)，略過(guò)。

再查看 performance_schema 的統(tǒng)計(jì)值：

可以看到幾個(gè)現(xiàn)象：

1. 臨時(shí)表空間被寫(xiě)入了 7.92MiB 的數(shù)據(jù)。

2. 這些數(shù)據(jù)是語(yǔ)句寫(xiě)入后，慢慢逐漸寫(xiě)入的。

來(lái)看看這些寫(xiě)入操作的特征，該方法我們?cè)?實(shí)驗(yàn) 03?使用過(guò)：

可以看到寫(xiě)入的線(xiàn)程是 page_clean_thread，是一個(gè)刷臟操作，這樣就能理解數(shù)據(jù)為什么是慢慢寫(xiě)入的。

也可以看到每個(gè) IO 操作的大小是 16K，也就是刷數(shù)據(jù)頁(yè)的操作。

結(jié)論：

我們可以看到，

1. MySQL 會(huì)基本遵守 max_heap_table_size 的設(shè)定，在內(nèi)存不夠用時(shí)，直接將表轉(zhuǎn)到磁盤(pán)上存儲(chǔ)。

2. 由于引擎不同（內(nèi)存中表引擎為 heap，磁盤(pán)中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實(shí)驗(yàn)寫(xiě)磁盤(pán)的數(shù)據(jù)量和?實(shí)驗(yàn) 05?中使用內(nèi)存的數(shù)據(jù)量不同。

3. 如果臨時(shí)表要使用磁盤(pán)，表引擎配置為 InnoDB，那么即使臨時(shí)表在一個(gè)時(shí)間很短的 SQL 中使用，且使用后即釋放，釋放后也會(huì)刷臟頁(yè)到磁盤(pán)中，消耗部分 IO。

mysql內(nèi)存分配最小單元是多少，為什么命名varchar類(lèi)型的時(shí)候，長(zhǎng)度最好是2的N次方

(1) 10*1024*1024*1024

(2)其實(shí)長(zhǎng)度最好的是（2^n）-1

因?yàn)橛?jì)算機(jī)是二進(jìn)制計(jì)算的，1 bytes = 8 bit ,一個(gè)字節(jié)最多可以代表的數(shù)據(jù)長(zhǎng)度是2的8次方 11111111 在計(jì)算機(jī)中也就是-128到127

而varchar類(lèi)型存儲(chǔ)變長(zhǎng)字段的字符類(lèi)型，當(dāng)存儲(chǔ)的字符串長(zhǎng)度小于255字節(jié)時(shí)，其需要1字節(jié)的空間，當(dāng)大于255字節(jié)時(shí)，需要2字節(jié)的空間。

使用2 ^ n長(zhǎng)度是更好的磁盤(pán)或內(nèi)存塊對(duì)齊。對(duì)齊塊更快。今天“塊”的大小更大，內(nèi)存和磁盤(pán)足夠快，可以忽略對(duì)齊，對(duì)于非常大的塊來(lái)說(shuō)是非常重要的。

所以使用（2^n）-1 可以更好的利用磁盤(pán)空間和內(nèi)存，使數(shù)據(jù)庫(kù)可以在最大限度內(nèi)存儲(chǔ)更多的數(shù)據(jù)

MySQL 數(shù)據(jù)表優(yōu)化設(shè)計(jì)（三）：CHAR 和 VARCHAR 怎么選？

VARCHAR 和 CHAR 是兩種主要的字符串類(lèi)型，用于存儲(chǔ)字符。不幸的是，由于實(shí)現(xiàn)的方式依賴(lài)于存儲(chǔ)引擎，因此很難解釋這些字符串在磁盤(pán)和內(nèi)存中如何存儲(chǔ)，除了除了常用的 InnoDB 和 MyISAM 外，假設(shè)你使用了其他存儲(chǔ)引擎，應(yīng)當(dāng)仔細(xì)閱讀存儲(chǔ)引擎的文檔。

VARCHAR 存儲(chǔ)可變長(zhǎng)度的字符串，也是最常用的字符數(shù)據(jù)類(lèi)型。相比固定長(zhǎng)度的類(lèi)型，VARCHAR 所需的存儲(chǔ)空間更小，它會(huì)盡可能少地使用存儲(chǔ)空間（例如，短的字符串占據(jù)的空間）。對(duì)于 MyISAM 來(lái)說(shuō)，如果創(chuàng)建表的時(shí)候指定了 ROW_FORMAT=FIXED 的話(huà)，那么會(huì)使用固定的空間存儲(chǔ)字段而導(dǎo)致空間浪費(fèi)。VARCHAR 使用1-2個(gè)額外的字節(jié)存儲(chǔ)字符串的長(zhǎng)度：當(dāng)最大長(zhǎng)度低于255字節(jié)的時(shí)候使用1個(gè)字節(jié)，如果更多的話(huà)就使用2個(gè)字節(jié)。因此，拉丁字符集的 VARCHAR(10)會(huì)使用11個(gè)字節(jié)的存儲(chǔ)空間，而 VARCHAR(1000)則會(huì)使用1002個(gè)字節(jié)的存儲(chǔ)空間。

VARCHAR 由于能夠節(jié)省空間，因此可以改善性能。但是，由于長(zhǎng)度可變，當(dāng)更新數(shù)據(jù)表的時(shí)候數(shù)據(jù)行的存儲(chǔ)空間會(huì)變化，這一定程度上會(huì)帶來(lái)額外的開(kāi)銷(xiāo)。如果數(shù)據(jù)行的長(zhǎng)度導(dǎo)致原有的存儲(chǔ)位置無(wú)法存放，那么不同的存儲(chǔ)引擎會(huì)做不同的處理。例如 MyISAM 可能產(chǎn)生數(shù)據(jù)行的碎片，而 InnoDB 需要進(jìn)行磁盤(pán)分頁(yè)來(lái)存放更新后的數(shù)據(jù)行。

通常，如果最大的列長(zhǎng)度遠(yuǎn)遠(yuǎn)高于平均長(zhǎng)度的話(huà)（例如可選的備注字段），使用 VARCHAR 是劃算的，同時(shí)如果更新的頻次很低，那么碎片化也不會(huì)是一個(gè)問(wèn)題。需要注意的是，如果使用的是 UTF-8字符集，則實(shí)際存儲(chǔ)的字節(jié)長(zhǎng)度是根據(jù)字符定的。對(duì)于中文，推薦的存儲(chǔ)字符集是 utf8mb4。

CHAR 類(lèi)型的長(zhǎng)度是固定的，MySQL 會(huì)對(duì)每個(gè)字段分配足夠的存儲(chǔ)空間。 存儲(chǔ)CHAR 類(lèi)型值的時(shí)候，MySQL 會(huì)移除后面多出來(lái)的空字符 。值是使用空字符進(jìn)行對(duì)齊以便進(jìn)行比較。對(duì)于短的字符串來(lái)說(shuō)，使用 CHAR 更有優(yōu)勢(shì)，而如果所有的值的長(zhǎng)度幾乎一致的話(huà)，就可以使用 CHAR。例如存儲(chǔ)用戶(hù)密碼的MD5值時(shí)使用 CHAR 就更合適，這是因?yàn)?MD5的長(zhǎng)度總是固定的。同時(shí)，對(duì)于字段值經(jīng)常改變的數(shù)據(jù)類(lèi)型來(lái)說(shuō)，CHAR 相比 VARCHAR 也更有優(yōu)勢(shì)，因?yàn)?CHAR 不會(huì)產(chǎn)生碎片。對(duì)于很短的數(shù)據(jù)列，使用 CHAR 比 VARCHAR更高效，例如使用CHAR(1)存儲(chǔ)邏輯值的 Y 和 N，這種情況下只需要1個(gè)字節(jié)，而 VARCHAR 需要2個(gè)字節(jié)。

對(duì)于移除空字符這個(gè)特性會(huì)感覺(jué)奇怪，我們舉個(gè)例子：

按上面的結(jié)果插入數(shù)據(jù)表后，string2中的前置空格不會(huì)移除，但使用 CHAR 類(lèi)型存儲(chǔ)時(shí)，string3尾隨空格會(huì)被移除，使用 SQL 查詢(xún)結(jié)果來(lái)檢驗(yàn)一下：

得出來(lái)的結(jié)果如下，可以看到 CHAR 類(lèi)型的 string3后面的空格被移除了，而 VARCHAR類(lèi)型的沒(méi)有。這種情況大多數(shù)時(shí)候不會(huì)有什么問(wèn)題，實(shí)際在應(yīng)用中也經(jīng)常會(huì)使用 trim 函數(shù)移除兩端的空字符，但是如果確實(shí)需要存儲(chǔ)空格的時(shí)候，那就需要注意不要選擇使用 CHAR 類(lèi)型：

數(shù)據(jù)如何存儲(chǔ)是由存儲(chǔ)引擎決定的，而且存儲(chǔ)引擎處理固定長(zhǎng)度和可變長(zhǎng)度的數(shù)據(jù)的方式并不相同。Memory 引擎使用固定大小的行，因此它需要分配最大可能的存儲(chǔ)空間——即便數(shù)據(jù)長(zhǎng)度是可變的。但是，對(duì)于字符串的對(duì)齊和空字符截?cái)嗍怯?MySQL 服務(wù)端完成的，因此所有存儲(chǔ)引擎都是一樣的。

與 CHAR 和 VARCHAR 相似的是 BINARY和 VARBINARY，用于存儲(chǔ)二進(jìn)制字節(jié)字符，BINARY 的對(duì)齊使用字符0的字節(jié)值來(lái)對(duì)齊，并且再獲取值的時(shí)候不會(huì)截?cái)唷Ｈ绻枰褂米址淖止?jié)值而不是字符的話(huà)，使用 BINARY 會(huì)更高效，這是因?yàn)楸容^時(shí)，一方面不需要考慮大小寫(xiě)，另一方面是MySQL一次只比較一個(gè)字節(jié)。

網(wǎng)頁(yè)題目：mysql怎么分配空間,mysql內(nèi)存分配方式
當(dāng)前路徑：http://chinadenli.net/article29/dsigpjh.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、用戶(hù)體驗(yàn)、標(biāo)簽優(yōu)化、移動(dòng)網(wǎng)站建設(shè)、響應(yīng)式網(wǎng)站、電子商務(wù)

廣告

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