mysql如何建表空間

在mysql中，也出現了類似oracle中的表空間概念。

創新互聯公司是專業的潼關網站建設公司，潼關接單;提供成都網站建設、成都做網站,網頁設計,網站設計,建網站,PHP網站建設等專業做網站服務;采用PHP框架,可快速的進行潼關網站開發網頁制作和功能擴展;專業做搜索引擎喜愛的網站,專業的做網站團隊,希望更多企業前來合作!

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

mysql表空間是什么概念呢？

開啟了Innodb的innodb_file_per_table這個參數之后【innodb_file_per_table = 1】，也就是啟用InnoDB的獨立表空間模式，便于管理。此時，在新建的innodb表的數據庫目錄下會多出來一個.ibd這個文件。這個就是此時的數據文件了。mysql會把這個innodb表的數據存放在這個文件中。并且每個innodb表此時都會對應這么一個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.

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

可以實現單表在不同的數據庫之間移動。具體怎么移動呢？假設有兩個數據庫，一個test，一個tt。

InnoDB 默認會將所有的數據庫InnoDB引擎的表數據存儲在一個共享空間中：ibdata1，這樣就感覺不爽，增刪數據庫的時候，ibdata1文件不會自動收縮，單個數據庫的備份也將成為問題。通常只能將數據使用mysqldump 導出，然后再導入解決這個問題。共享表空間在Insert操作上少有優勢。其它都沒獨立表空間表現好。當啟用獨立表空間時，請合理調整一 下innodb_open_files 的值。

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

需要說明的是：

1、設置了獨立表空間之后，如果改成了共享表空間，那么，此時如果執行表的插入操作，數據會存放在哪里呢？

對于之前已經存在了的表，還是存放在獨立表空間。對于新建的表，就會存放在共享表空間了。

2、如果一開始用了獨立表空間，后來改了innodb_file_per_table變量的值，改成獨立表空間了，那么數據如何存儲？

對于已經存在了的innodb引擎的表來說，數據還是存放在共享表空間的，而此時如果創建了新的表，那么就會在數據庫的目錄中多出一個.ibd的文件用于存儲這個新表的數據。

總結上面的1、2，就是：原來的還是按照原來的方式存儲。新的表按照新的規則來存儲。

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分區：

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

2. LIST分區：

解讀：以上為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會自動完成這些工作，要做的只是基于將要被哈希的列值指定一個表達式，以及指定被分區的表將要被分割成的分區數量，如：

解讀：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分區表的時候，可以不指定分區鍵，默認會選擇使用主鍵或唯一鍵作為分區鍵，沒有主鍵或唯一鍵，就必須指定分區鍵。

解讀：根據分區鍵來進行分區

5. 子分區

子分區是分區表中，每個分區的再次分割，適合保存非常大量的數據。

解讀：主分區使用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) 在每個子分區內，子分區的名字必須是惟一的，目前在整個表中，也要保持唯一。例如：

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

九、MySQL分區處理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. 如果希望能不丟失數據的條件下重新定義分區，可以使用如下語句：

REORGANIZE會對分區的數據進行重構。

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 innodb引擎深入講解

表空間(ibd文件),一個MySQL實例可以對應多個表空間，用于存儲記錄，索引等數據。

段，分為數據段、索引段、回滾段，innodb是索引組織表，數據段就是B+Tree的葉子節點，索引段為非葉子節點，段用來管理多個區。

區，表空間的單元結構，每個區的大小為1M,默認情況下，innodb存儲引擎頁大小為16K，即一個區中一共有64個連續的頁。

頁，是innodb存儲引擎磁盤管理的最小單元，每個頁的大小為16K，為了保證頁的連續性，innodb存儲引擎每次從磁盤申請4~5個區。

行，innodb存儲引擎數據是按行進行存儲的。Trx_id 最后一次事務操作的id、roll_pointer滾動指針。

i nnodb的內存結構 ，由Buffer Pool、Change Buffer和Log Buffer組成。

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

緩沖池以page頁為單位，底層采用鏈表數據結構管理page，根據狀態，將page分為三種類型:

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

2、clean page 被使用page，數據沒有被修改過。

3、dirty page 臟頁，被使用page，數據被修改過，這個page當中的數據和磁盤當中的數據 不一致。說得簡單點就是緩沖池中的數據改了，磁盤中的沒改，因為還沒刷寫到磁盤。

Change Buffer ：更改緩沖區(針對于非唯一二級索引頁)，在執行DML語句時，如果這些數據page沒有在Buffer Pool中，不會直接操作磁盤，而會將數據變更存在更改緩沖區Change Buffer中，在未來數據被讀取時。再將數據合并恢復到Buffer Pool中，再將合并后的數據刷新到磁盤中。

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

Adaptive Hash Index： 自適應hash索引，用于優化對Buffer Pool數據的查詢，InnoDB存儲引擎會監控對表上各索引頁的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引。無需人工干預，系統根據情況自動完成。

參數：innodb_adaptive_hash_index

Log Buffer: 日志緩沖區，用來保存要寫入到磁盤中的log日志數據(redo log、undo log)，默認大小為16M，日志緩沖區的日志會定期刷新到磁盤中，如果需要更新，插入或刪除許多行的事務，增加日志緩沖區的大小可以節省磁盤IO。

參數: innodb_log_buffer_size 緩沖區大小

innodb_flush_log_at_trx_commit 日志刷新到磁盤時機

innodb_flush_log_at_trx_commit=1 表示日志在每次事務提交時寫入并刷新到磁盤

2 表示日志在每次事務提交后寫入，并每秒刷新到磁盤一次

0 表示每秒將日志寫入并刷新到磁盤一次。

InnoDB 的磁盤結構，由系統表空間(ibdata1)，獨立表空間(*.ibd),通用表空間，撤銷表空間(undo tablespaces), 臨時表空間(Temporary Tablespaces), 雙寫緩沖區(Doublewrite Buffer files), 重做日志(Redo Log).

系統表空間(ibdata1)： 系統表空間是更改緩沖區的存儲區域，如果表是在系統表空間而不是每個表文件或者通用表空間中創建的，它也可能包含表和索引數據。

參數為: innodb_data_file_path

獨立表空間(*.ibd): 每個表的文件表空間包含單個innodb表的數據和索引，并存儲在文件系 統上的單個數據文件中。 參數: innodb_file_per_table

通用表空間: 需要通過create tablespace 語法創建，創建表時 可以指定該表空間。

create tablespace xxx add datafile 'file_name' engine=engine_name

create table table_name .... tablespace xxx

撤銷表空間(undo tablespaces): MySQL實例在初始化時會自動創建兩個默認的undo表空間(初始大小16K，undo_001,undo_002)，用于存儲undo log 日志

臨時表空間(Temporary Tablespaces)： innodb使用會話臨時表空和全局表空間，存儲用 戶創建的臨時表等數據。

雙寫緩沖區(Doublewrite Buffer files)： innodb引擎將數據頁從Buffer Pool刷新到磁盤前，先將數據頁寫入緩沖區文件中，便于系統異常時恢復數據。

重做日志(Redo Log)： 是用來實現事務的持久性，該日志文件由兩部分組成，重做日志緩沖區(redo log buffer)以及重做日志文件(redo log)，前者是在內存中，后者在磁盤中，當事務提交之后會把修改信息都會存儲到該日志中，用于在刷新臟頁到磁盤時，發送錯誤時，進行數據恢復使用。以循環方式寫入重做日志文件，涉及兩個文件ib_logfile0,ib_logfile1。

那內存結構中的數據是如何刷新到磁盤中的? 在MySQL中有4個線程負責刷新日志到磁盤。

1、Master Thread， mysql核心后臺線程，負責調度其它線程，還負責將緩沖池中的數據異 步刷新到磁盤中，保持數據的一致性，還包括臟頁的刷新，合并插入緩沖、undo頁的回 收。

2、IO Thread，在innodb存儲引擎中大量使用了AIO來處理IO請求，這樣可以極大地提高數 據庫的性能，而IO Thead主要負責這些IO請求的回調。

4個讀線程 Read thread負責讀操作

4個寫線程write thread負責寫操作

1個Log thread線程 負責將日志緩沖區刷新到磁盤

1個insert buffer線程 負責將寫入緩沖區內容刷新到磁盤

3、Purge Thread，主要用于回收事務已經提交了的undo log，在事務提交之后，undo log 可能不用了，就用它來回收。

4、Page Cleaner Thread， 協助Master Thread 刷新臟頁到磁盤的線程，它可以減輕主線程 的壓力，減少阻塞。

事務就是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要么同時成功，要么同時失效。

事務的4大特性分為:

如何保證事務的4大特性，原子性，一致性和持久性是由innodb存儲引擎底層的兩份日志來保證的，分別是redo log和undo log。對于隔離性是由鎖機制和MVCC(多版本并發控制)來實現的。

redo log，稱為重做日志，記錄的是事務提交時數據頁的物理修改，是用來實現事務的持久性。該日志文件由兩部分組成: 重做日志緩沖redo log buffer及重做日志文件redo log file，前者是在內存中，后者是在磁盤中，當事務提交之后會把所有修改信息都存到該日志文件中，用于在刷新臟頁到磁盤，發送錯誤時，進行數據的恢復使用，從而保證事務的持久性。

具體的操作流程是：

1、客戶端發起事務操作，包含多條DML語句。首先去innodb中的buffer pool中的數據頁去查找有沒有我們要更新的這些數據，如果沒有則通過后臺線程從磁盤中加載到buffer pool對應的數據頁中，然后就可以在緩沖池中進行數據操作了。

2、此時緩沖池中的數據頁發生了變更，還沒刷寫到磁盤，這個數據頁稱為臟頁。臟頁不是實時刷新到磁盤的，而是根據你配置的刷寫策略進行刷寫到磁盤的（innodb_flush_log_at_trx_commit，0,1,2三個值）。如果臟頁在往磁盤刷新的時候出現了故障，會丟失數據，導致事務的持久性得不到保證。為了避免這種現象，當對緩沖池中的數據進行增刪改操作時，會把增刪改記錄到redo log buffer當中，redo log buffer會把數據頁的物理變更持久化到磁盤文件中(ib_logfile0/ib_logfile1)。如果臟頁刷新失敗，就可以通過這兩個日志文件進行恢復。

undo log，它是用來解決事務的原子性的，也稱為回滾日志。用于記錄數據被修改前的信息，作用包括:提供回滾和MVCC多版本并發控制。

undo log和redo log的記錄物理日志不一樣，它是邏輯日志。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，當update一條記錄時，它記錄一條對應相反的update記錄，當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容并進行回滾。

undo log銷毀: undo log 在事務執行時產生，事務提交時，并不會立即刪除undo log，因為這些日子可能用于MVCC。

undo log存儲: undo log 采用段的方式進行管理和記錄，存放在前面介紹的rollback segment回滾段中，內部包含1024個undo log segment。

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

read committed 每次select 都生成一個快照讀

repeatable read 開啟事務后第一個select語句才是快照讀的地方

serializable 快照讀會退化為當前讀。

mvcc的實現原理

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

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

DB_ROW_ID: 隱藏主鍵，如果表結構沒有指定主鍵，將會生成該隱藏字段。

m_ids當前活躍的事務ID集合

min_trx_id: 最小活躍事務id

max_trx_id: 預分配事務ID，當前最大事務id+1，因為事務id是自增的

creator_trx_id: ReadView創建者的事務ID

版本鏈數據訪問規則:

trx_id: 表示當前的事務ID

1、trx_id == creator_trx_id? 可以訪問讀版本--成立的話,說明數據是當前這個事務更改的

2、trx_id 成立,說明數據已經提交了。

3、trx_idmax_trx_id？不可用訪問讀版本- 成立的話，說明該事務是在ReadView生成后才開啟的。

4、min_trx_id

mysql 如何分配內存

我們仍然使用兩個會話，一個會話 run，用于運行主 SQL；另一個會話 ps，用于進行 performance_schema 的觀察：

主會話線程號為 29，

將 performance_schema 中的統計量重置，

臨時表的表大小限制取決于參數? tmp_table_size 和 max_heap_table_size 中較小者，我們實驗中以設置 max_heap_table_size 為例。

我們將會話級別的臨時表大小設置為 2M（小于上次實驗中臨時表使用的空間），執行使用臨時表的 SQL：

查看內存的分配記錄：

會發現內存分配略大于 2M，我們猜測臨時表會比配置略多一點消耗，可以忽略。

查看語句的特征值：

可以看到語句使用了一次需要落磁盤的臨時表。

那么這張臨時表用了多少的磁盤呢？

我們開啟 performance_schema 中 waits 相關的統計項：

重做實驗，略過。

再查看 performance_schema 的統計值：

可以看到幾個現象：

1. 臨時表空間被寫入了 7.92MiB 的數據。

2. 這些數據是語句寫入后，慢慢逐漸寫入的。

來看看這些寫入操作的特征，該方法我們在?實驗 03?使用過：

可以看到寫入的線程是 page_clean_thread，是一個刷臟操作，這樣就能理解數據為什么是慢慢寫入的。

也可以看到每個 IO 操作的大小是 16K，也就是刷數據頁的操作。

結論：

我們可以看到，

1. MySQL 會基本遵守 max_heap_table_size 的設定，在內存不夠用時，直接將表轉到磁盤上存儲。

2. 由于引擎不同（內存中表引擎為 heap，磁盤中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實驗寫磁盤的數據量和?實驗 05?中使用內存的數據量不同。

3. 如果臨時表要使用磁盤，表引擎配置為 InnoDB，那么即使臨時表在一個時間很短的 SQL 中使用，且使用后即釋放，釋放后也會刷臟頁到磁盤中，消耗部分 IO。

mysql內存分配最小單元是多少，為什么命名varchar類型的時候，長度最好是2的N次方

(1) 10*1024*1024*1024

(2)其實長度最好的是（2^n）-1

因為計算機是二進制計算的，1 bytes = 8 bit ,一個字節最多可以代表的數據長度是2的8次方 11111111 在計算機中也就是-128到127

而varchar類型存儲變長字段的字符類型，當存儲的字符串長度小于255字節時，其需要1字節的空間，當大于255字節時，需要2字節的空間。

使用2 ^ n長度是更好的磁盤或內存塊對齊。對齊塊更快。今天“塊”的大小更大，內存和磁盤足夠快，可以忽略對齊，對于非常大的塊來說是非常重要的。

所以使用（2^n）-1 可以更好的利用磁盤空間和內存，使數據庫可以在最大限度內存儲更多的數據

MySQL 數據表優化設計（三）：CHAR 和 VARCHAR 怎么選？

VARCHAR 和 CHAR 是兩種主要的字符串類型，用于存儲字符。不幸的是，由于實現的方式依賴于存儲引擎，因此很難解釋這些字符串在磁盤和內存中如何存儲，除了除了常用的 InnoDB 和 MyISAM 外，假設你使用了其他存儲引擎，應當仔細閱讀存儲引擎的文檔。

VARCHAR 存儲可變長度的字符串，也是最常用的字符數據類型。相比固定長度的類型，VARCHAR 所需的存儲空間更小，它會盡可能少地使用存儲空間（例如，短的字符串占據的空間）。對于 MyISAM 來說，如果創建表的時候指定了 ROW_FORMAT=FIXED 的話，那么會使用固定的空間存儲字段而導致空間浪費。VARCHAR 使用1-2個額外的字節存儲字符串的長度：當最大長度低于255字節的時候使用1個字節，如果更多的話就使用2個字節。因此，拉丁字符集的 VARCHAR(10)會使用11個字節的存儲空間，而 VARCHAR(1000)則會使用1002個字節的存儲空間。

VARCHAR 由于能夠節省空間，因此可以改善性能。但是，由于長度可變，當更新數據表的時候數據行的存儲空間會變化，這一定程度上會帶來額外的開銷。如果數據行的長度導致原有的存儲位置無法存放，那么不同的存儲引擎會做不同的處理。例如 MyISAM 可能產生數據行的碎片，而 InnoDB 需要進行磁盤分頁來存放更新后的數據行。

通常，如果最大的列長度遠遠高于平均長度的話（例如可選的備注字段），使用 VARCHAR 是劃算的，同時如果更新的頻次很低，那么碎片化也不會是一個問題。需要注意的是，如果使用的是 UTF-8字符集，則實際存儲的字節長度是根據字符定的。對于中文，推薦的存儲字符集是 utf8mb4。

CHAR 類型的長度是固定的，MySQL 會對每個字段分配足夠的存儲空間。 存儲CHAR 類型值的時候，MySQL 會移除后面多出來的空字符 。值是使用空字符進行對齊以便進行比較。對于短的字符串來說，使用 CHAR 更有優勢，而如果所有的值的長度幾乎一致的話，就可以使用 CHAR。例如存儲用戶密碼的MD5值時使用 CHAR 就更合適，這是因為 MD5的長度總是固定的。同時，對于字段值經常改變的數據類型來說，CHAR 相比 VARCHAR 也更有優勢，因為 CHAR 不會產生碎片。對于很短的數據列，使用 CHAR 比 VARCHAR更高效，例如使用CHAR(1)存儲邏輯值的 Y 和 N，這種情況下只需要1個字節，而 VARCHAR 需要2個字節。

對于移除空字符這個特性會感覺奇怪，我們舉個例子：

按上面的結果插入數據表后，string2中的前置空格不會移除，但使用 CHAR 類型存儲時，string3尾隨空格會被移除，使用 SQL 查詢結果來檢驗一下：

得出來的結果如下，可以看到 CHAR 類型的 string3后面的空格被移除了，而 VARCHAR類型的沒有。這種情況大多數時候不會有什么問題，實際在應用中也經常會使用 trim 函數移除兩端的空字符，但是如果確實需要存儲空格的時候，那就需要注意不要選擇使用 CHAR 類型：

數據如何存儲是由存儲引擎決定的，而且存儲引擎處理固定長度和可變長度的數據的方式并不相同。Memory 引擎使用固定大小的行，因此它需要分配最大可能的存儲空間——即便數據長度是可變的。但是，對于字符串的對齊和空字符截斷是由 MySQL 服務端完成的，因此所有存儲引擎都是一樣的。

與 CHAR 和 VARCHAR 相似的是 BINARY和 VARBINARY，用于存儲二進制字節字符，BINARY 的對齊使用字符0的字節值來對齊，并且再獲取值的時候不會截斷。如果需要使用字符的字節值而不是字符的話，使用 BINARY 會更高效，這是因為比較時，一方面不需要考慮大小寫，另一方面是MySQL一次只比較一個字節。