通用表空間 - General Tablespace

通用表空間的作用和特性？

• 通用表空間是使用 CREATE tablespace 語法創(chuàng)建的共享InnoDB表空間
• 通用表空間能夠存儲多個表的數據，與系統(tǒng)表空間類似也是共享表空間；
• 服務器運行時會把表空間元數據保存在內存中，在表的數量相同的情況下，通用表空間比獨立表空間的數量更少，所以消耗的內存也就更少；
• 數據文件可以放置在數據目錄或數據目錄之外的其他位置，對于單獨管理關鍵表非常有用；
• 支持所有的表格式和行格式的相關特性；

怎么創(chuàng)建通用表空間？

創(chuàng)建通用表空間可以使用 CREATE TABLESPACE 語法：

• tablespace_name：通用表空間的名字
• DATAFILE 'file_name'：指定通用表空間在磁盤的文件名
• FILE_BLOCK_SIZE：數據行格式是壓縮格式時才涉及

CREATE TABLESPACE tablespace_name [ADD DATAFILE 'file_name'] [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]

注意：

tablespace_name 表空間名區(qū)分大小寫

總結：

創(chuàng)建通用表空間可以使用 CREATE TABLESPACE 語法，與創(chuàng)建表類似，語句里用TABLESPACE 關鍵字指明創(chuàng)建的是表空間

創(chuàng)建通用表空間的示例

示例：在 data 目錄下創(chuàng)建通用表空間

# 指定表空間文件名
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
# 或使用隨機文件名
CREATE TABLESPACE `ts2` Engine=InnoDB;

ADD DATAFILE 子句在MySQL 8.0.14及以后的版本是可選的，之前是必需的。如果沒有指定ADD DATAFILE 子句，則自動創(chuàng)建一個以 UUID 為文件名的表空間數據文件，通用表空間數據文件以 .ibd 為擴展名。

# 在數據目錄中查看通用表空間數據文件
root@yudukai:/var/lib/mysql# ll *.ibd
# 沒有指定ADD DATAFILE子句，隨機生成的通用表空間數據文件，指定了就沒有這行
-rw-r-----  1 mysql mysql   114688 Jun  7 14:05  d4b703a0-6236-11f1-841a-fa163e0bcb5f.ibd # ts2
# 系統(tǒng)自帶，存放mysql系統(tǒng)表和數據字典表的表空間數據文件
-rw-r-----  1 mysql mysql 26214400 May 29 19:06  mysql.ibd
# 使用了ADD DATAFILE子句，使用指定的通用表空間數據文件
-rw-r-----  1 mysql mysql   147456 May 29 19:06  ts1.ibd

 創(chuàng)建通用表空間時要注意什么？

• 可以在數據目錄中創(chuàng)建通用表空間，也可以在數據目錄之外創(chuàng)建通用表空間。為避免與隱式創(chuàng)建的獨立表文件表空間沖突，不支持在data目錄的子目錄中創(chuàng)建通用表空間。（因為我們每創(chuàng)建一個數據庫，都會在數據目錄中生成一個與數據庫名相同的子目錄，為了避免自已在數據目錄中創(chuàng)建的子目錄與以后將要創(chuàng)建的數據庫重名，所以不允許把通用表空間創(chuàng)建在數據目錄下的子目錄中）

  當在數據目錄之外創(chuàng)建通用表空間時，該目錄必須存在，并且必須在創(chuàng)建表空間之前讓InnoDB識別，要使用自定義的目錄可以通過系統(tǒng) innodb_directories 指定。

  Innodb_directories 是一個只讀啟動選項，配置后需要重新啟動服務器。

• Innodb_directories 默認值是 NULL ，同時 innodb_data_home_dir , innodb_undo_directory 和 datadir 定義的目錄會被附加到 innodb_directories 參數值中，在InnoDB啟動時會自動被識別(包括子目錄)，手動指定目錄的方式，如下所示：

# 通過啟動選項指定，多個目錄用分號隔開
mysqld --innodb-directories="directory_path_1;directory_path_2"
# 通過選項文件指定，多個目錄用分號隔開
[mysqld]
innodb_directories="directory_path_1;directory_path_2"

示例：不能在數據目錄的子目錄下創(chuàng)建通用表空間

# 在數據目錄下創(chuàng)建子目錄
root@yudukai:/var/lib/mysql# mkdir my_tablespace
root@yudukai:/var/lib/mysql# ll
total 92892
# ... 省略
drwxr-xr-x 2 root root 4096 10月 30 10:59 my_tablespace/
# ... 省略

CREATE TABLESPACE `ts3` ADD DATAFILE './my_tablespace/ts3.ibd' Engine=InnoDB;
# 提示錯誤，因為子目錄名有可能和數據庫名重名
ERROR 3121 (HY000): The DATAFILE location cannot be under the datadir.

InnoDB不是默認存儲引擎的情況下，必須指定 ENGINE = InnoDB 子句

如何向通用表空間中添加表？

示例：向通用表空間中添加表，在創(chuàng)建表時使用 TABLESPACE 子句指定通用表空間即可

# 在ts1表空間中添加t1表
mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.02 sec)

# 在ts1表空間中添加t2表
mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.02 sec)

# 把t1表移動到ts1表空間
mysql> ALTER TABLE t1 TABLESPACE ts1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

總結：

首先創(chuàng)建通用表空間，之后使用 CREATE 語句創(chuàng)建表時通過 TABLESPACE 子句指定通用表空間，語句執(zhí)行成功后即在指定的通用表空間下創(chuàng)建了表

怎么刪除通用表空間？

DROP TABLESPACE 語句用于刪除一個InnoDB通用表空間。

在刪除通用表空間之前，必須將所有表從表空間中刪除，如果表空間不為空，將返回錯誤。

查詢通用表空間中的表，可以使用下面的語句：

mysql> SELECT a.NAME AS space_name, b.NAME AS table_name FROM 
    -> INFORMATION_SCHEMA.INNODB_TABLESPACES a, INFORMATION_SCHEMA.INNODB_TABLES b 
    -> WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test_db/t2 |
| ts1        | test_db/t3 |
| ts1        | test_db/t1 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> 

示例：一個完整的通用表空間刪除流程

# 創(chuàng)建通用表空間ts1
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
# 在通用表空間中創(chuàng)建t1表
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;
# 刪除t1表
DROP TABLE t1;
# 刪除通用表空間ts1
DROP TABLESPACE ts1;

總結：

可以使用 DROP TABLESPACE 語句用于刪除一個通用表空間，與刪除表類似，語句里用TABLESPACE 關鍵字指明刪除的是表空間

使用通用表空間時要注意什么？

• 使用 TRUNCATE 或 DROP 語句截斷或刪除表時，通用表空間的空閑容量并不會釋放，并且只能用于新的InnoDB的新表而不能用于其他的引擎；
• 通用表空間不屬于任何數據庫，使用 DROP DATABASE 操作數據庫和屬于該數據庫所有的表時，并不會刪除通用表空間。
• tablespace_name 表空間名區(qū)分大小寫

臨時表空間 - Temporary Tablespaces

什么是臨時表？

臨時表存儲的是臨時數據，不能永久的存儲數據，一般在復雜的查詢或計算過程中用來存儲過渡的中間結果。

MySQL在執(zhí)行查詢與計算的過程中會自動生成臨時表，比如表連接查詢時得到的結果集就是一張臨時表，因為結果中可能包含多個表中的字段并沒有一張真實的表與之完全對應。

例如：student和score表是存在的，但是下面查詢的大表是不存在磁盤中的，是兩張表連接起來的結果，是在臨時表里的結果。

當查詢的結果所包含的列，并沒有一個真實的表與之對應，那么這個結果集就用一個臨時表把數據組織起來。

除了系統(tǒng)自動創(chuàng)建的臨時表，可以手動創(chuàng)建臨時表嗎？

• 用戶可以通過使用 CREATE TEMPORARY TABLE 語句手動創(chuàng)建臨時表
• 用戶創(chuàng)建的臨時表也稱為外部臨時表；MySQL在執(zhí)行查詢與計算的過程中自動生成的臨時表稱為內部臨時表。

什么是外部臨時表？

使用 CREATE TEMPORARY TABLE 語句創(chuàng)建的臨時表是外部臨時表

# 創(chuàng)建一個名稱為t1的臨時表
CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

通過 INNODB_TEMP_TABLE_INFO 查詢臨時表元數據。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
TABLE_ID: 1295              # 臨時表的表ID
    NAME: #sql288f17_30_b   # 臨時表的名稱
  N_COLS: 4                 # 臨時表中的列數(包含3個默認隱藏列)
   SPACE: 4243767290        # 臨時表所在的臨時表空間ID
1 row in set (0.00 sec)

mysql> 

1. TEMPORARY 表只在當前會話中可見，并且在會話關閉時自動刪除。
2. 這意味著兩個不同的會話可以使用相同的臨時表名，而不會相互沖突。
3. 臨時表也不會與已有的非臨時表名沖突，如果創(chuàng)建了與現有表同名的臨時表，則現有表被隱藏，直到臨時表被刪除。

把臨時表刪了，就可以查看之前的同名的真實表了。

重啟MySQL服務器后，再次查詢臨時表信息，得到空集合

總結：

使用 CREATE TEMPORARY TABLE 語句創(chuàng)建的臨時表是外部臨時表，表只在當前會話中可見，并且在會話關閉時自動刪除

什么是內部臨時表？

• 由服務器自動創(chuàng)建的臨時表是內部臨時表
• 服務器在以下情況會自動創(chuàng)建臨時表，這個過程用戶不能直接控制：
  • 使用 UNION 語句合并查詢結果
  • 對視圖時的一些操作，比如使用 UNION 或聚合函數
  • 使用子查詢
  • 使用 DISTINCT 和 ORDER BY 的查詢可能需要一個臨時表
  • 使用 INSERT…SELECT 語句向表中寫入數據時，需要先用一個內部臨時表來保存 SELECT 語句查詢出來的行，然后將這些行插入到目標表中
  • 使用 COUNT(DISTINCT) 和 GROUP_CONCAT() 表達式時
  • 使用窗口函數時

總結：

由服務器自動創(chuàng)建的臨時表是內部臨時表，通常MySQL在執(zhí)行查詢與計算的過程中會自動生成的內部臨時表

如何確認服務器創(chuàng)建了臨時表？

要確定SQL語句是否需要臨時表，使用 EXPLAIN 并檢查 Extra 列，在優(yōu)化專題中我們再詳細介紹

臨時表都有哪些設置？

• 系統(tǒng)變量 internal_tmp_mem_storage_engine 用于指定內存中內部臨時表的存儲引擎，值為 TempTable (默認值)或 MEMORY ；
• TempTable 存儲引擎為 VARCHAR 和 VARBINARY 列以及其他二進制大對象類型進行了優(yōu)化；
• 從MySQL 8.0.28開始 tmp_table_size 定義了由 TempTable 存儲引擎創(chuàng)建的單個內部臨時表允許使用內存的最大值，當達到 tmp_table_size 限制時，MySQL自動將內存中的內部臨時表轉換為磁盤上的InnoDB內部臨時表。 tmp_table_size 的默認值是 16MB ；
• 系統(tǒng)變量 temptable_max_ram 定義 TempTable 存儲引擎創(chuàng)建的所有臨時表可以使用的最大內存，默認為 1GB ，超出限制后將內存中的內部臨時表轉換為磁盤上內部臨時表；
• 當內存臨時表使用內存存儲引擎 internal_tmp_mem_storage_engine=MEMORY 時，系統(tǒng)變量 max_heap_table_size 可以限制內存內部臨時表的最大行數，默認 16777216 ，
• 內存存儲引擎臨時表變得太大，MySQL會自動將其轉換為磁盤上的臨時表，內存中臨時表的大小由 tmp_table_size 和 max_heap_table_size 這兩個系統(tǒng)變量中最小的值決定。

總結：

通過配置對應的系統(tǒng)變量來指定臨時表使用的存儲引擎、使用內存的大小、表中的最大行數等選項。

臨時表中的數據存在哪里？

磁盤上的臨時表數據存儲在臨時表空間中，MySQL8.0版本中磁盤上的臨時表存儲引擎支持InnoDB ，分為兩種類型分別是：

1. 會話臨時表空間( session temporary tablespaces )
2. 全局臨時表空間( global temporary tablespace )

會話臨時表空間的作用？

磁盤上的會話臨時表空間存儲由用戶創(chuàng)建的外部臨時表和優(yōu)化器創(chuàng)建的內部臨時表；

會話臨時表空間的數據存在哪里？

• 當MySQL接收到第一個創(chuàng)建磁盤臨時表的請求時，從臨時表空間池中分配會話臨時表空間。

  一個會話最多分配兩個表空間，一個用于用戶創(chuàng)建的臨時表，另一個用于優(yōu)化器創(chuàng)建的內部臨時表。

  會話的臨時表空間用于存儲會話創(chuàng)建的所有磁盤臨時表，當會話斷開連接時，臨時表空間將被截斷并釋放回池中；

• 服務器啟動時會創(chuàng)建一個包含 10 個臨時表空間的臨時表空間池，表空間會根據需要自動添加到池中，臨時表空間池在MySQL正常關閉或中止初始化時被刪除；

• 會話臨時表空間文件擴展名為 .ibt ；

• 系統(tǒng)變量 innodb_temp_tablespaces_dir 可以指定會話臨時表空間的位置。默認數據目錄下的 #innodb_temp 目錄(開頭的 # 號是為了避免與數據庫目錄命名沖突)，如果無法創(chuàng)建臨時表空間池，服務器則拒絕啟動；

# 數據目錄下的臨時表空間目錄，以#開頭就是為了與真實目錄名稱起沖突
root@yudukai:/var/lib/mysql# cd /var/lib/mysql/#innodb_temp 

# 自動創(chuàng)建的臨時表空間
root@yudukai:/var/lib/mysql/#innodb_temp# ls
temp_10.ibt  temp_1.ibt  temp_2.ibt  temp_3.ibt  temp_4.ibt  temp_5.ibt  temp_6.ibt  temp_7.ibt  temp_8.ibt  temp_9.ibt

默認會創(chuàng)建10個會話臨時表空間。

外部臨時表和內部臨時表分別保存在不同的表空間中。

臨時表空間類似于通用表空間，每個臨時表空間中可以保存多個臨時表中的數據。

并不是說，10個臨時表空間只能支持5個會話。只是一個會話使用了兩個臨時表空間而已，一個表空間里面其實可以保存很多個臨時表，支持很多會話。

全局臨時表空間的作用？

全局臨時表空間存儲對用戶創(chuàng)建的臨時表所做的更改，以便以后回滾操作

全局臨時表空間的數據存在哪里？

系統(tǒng)變量 innodb_temp_data_file_path 指定了全局臨時表空間數據文件的相對路徑、名稱、大小和屬性。如果沒有指定，則默認在系統(tǒng)表空間目錄(系統(tǒng)變量innodb_data_home_dir 指定的目錄)中創(chuàng)建，默認名為 ibtmp1 ，初始文件大小略大于12MB ；

# 數據目錄
root@yudukai:/var/lib/mysql# ll
total 92932
# ... 省略
-rw-r----- 1 mysql mysql 12582912 10月 30 12:08 ibtmp1 # 全局臨時表空間
# ... 省略

全局臨時表空間在正常關閉或中止初始化時被刪除，并在每次啟動服務器時重新創(chuàng)建，如果無法創(chuàng)建全局臨時表空間，則拒絕啟動；如果服務器意外停止，重啟服務器時會自動刪除并重新創(chuàng)建全局臨時表空間。

總結：

磁盤上的臨時表數據存儲在臨時表空間中，臨時表空間分為兩種分別是：

• 會話臨時表空間( session temporary tablespaces )，默認數據目錄下的#innodb_temp 目錄中
• 全局臨時表空間( global temporary tablespace )，默認在數據目錄下中創(chuàng)建，名為ibtmp1

怎么查看全局臨時表空間的信息和大??？

可以通過 INFORMATION_SCHEMA.FILES 查看全局臨時表空間的元數據：

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE 
    -> TABLESPACE_NAME='innodb_temporary'\G
*************************** 1. row ***************************
             FILE_ID: 4294967293
           FILE_NAME: ./ibtmp1
           FILE_TYPE: TEMPORARY
     TABLESPACE_NAME: innodb_temporary
       TABLE_CATALOG: 
        TABLE_SCHEMA: NULL
          TABLE_NAME: NULL
  LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
              ENGINE: InnoDB
       FULLTEXT_KEYS: NULL
        DELETED_ROWS: NULL
        UPDATE_COUNT: NULL
        FREE_EXTENTS: 2
       TOTAL_EXTENTS: 12
         EXTENT_SIZE: 1048576
        INITIAL_SIZE: 12582912
        MAXIMUM_SIZE: NULL
     AUTOEXTEND_SIZE: 67108864
       CREATION_TIME: NULL
    LAST_UPDATE_TIME: NULL
    LAST_ACCESS_TIME: NULL
        RECOVER_TIME: NULL
 TRANSACTION_COUNTER: NULL
             VERSION: NULL
          ROW_FORMAT: NULL
          TABLE_ROWS: NULL
      AVG_ROW_LENGTH: NULL
         DATA_LENGTH: NULL
     MAX_DATA_LENGTH: NULL
        INDEX_LENGTH: NULL
           DATA_FREE: 6291456
         CREATE_TIME: NULL
         UPDATE_TIME: NULL
          CHECK_TIME: NULL
            CHECKSUM: NULL
              STATUS: NORMAL
               EXTRA: NULL
1 row in set (0.00 sec)

mysql> 

要檢查全局臨時表空間數據文件的大小，可以查詢 INFORMATION_SCHEMA.FILES 中的具體字段

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, 
    -> TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM 
    -> INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1           # 全局表空間數據文件名
TABLESPACE_NAME: innodb_temporary   # 全局表空間名
         ENGINE: InnoDB             # 存儲引擎
   INITIAL_SIZE: 12582912           # 初始化的大小
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456            # 可用容量
   MAXIMUM_SIZE: NULL               # 最大允許擴容的容量
1 row in set (0.00 sec)

mysql> 

默認情況下，全局臨時表空間數據文件會自動擴展并根據需要增加大小，要確定全局臨時表空間數據文件是否自動擴展，可以檢查 innodb_temp_data_file_path 變更設置：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)

mysql> 

可以通過 INFORMATION_SCHEMA.FILES 查看全局臨時表空間的元數據

全局臨時表空間數據文件的大小可以設置嗎？

可以通過系統(tǒng)變量 innodb_temp_data_file_path 指定最大文件大小，并重新啟動服務器，語法與配置系統(tǒng)表空間文件相同

# mysqld節(jié)點
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

到此這篇關于MySQL通用臨時表空間的具體使用的文章就介紹到這了,更多相關MySQL通用臨時表空間內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論