MySQL表操作：建立表、分析表、維護表、檢查表、最佳化表

檢查表的作用是什麼

資料庫是用來儲存資料的，資料又是儲存在表中的，一個數據庫中可以包含多張表。

在表中定義列時，應該指定列的名稱、資料型別（整型、浮點型、字串等）和預設值（如果有的話）。

下面是所有資料型別的概述，其中JSON資料型別是一個新的擴充套件型別

1。數字：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT和BIT。

2。浮點數：DECIMAL、FLOAT和DOUBLE。

3。字串：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

4。Spatial 資料型別。

5。JSON資料型別。

建立一張客戶資料表：

CREATE TABLE IF NOT EXISTS `customers` （

`id` int（11） NOT NULL AUTO_INCREMENT，

`first_name` varchar（20） DEFAULT NULL，

`last_name` varchar（20） DEFAULT NULL，

`country` varchar（20） DEFAULT NULL，

PRIMARY KEY （`id`）

） ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci；

其中的選項解釋如下

IF NOT EXISTS：

如果存在一個具有相同名字的表，並且你指定了這個子句，MySQL只會丟擲一個警告，告知表已經存在。否則，MySQL將丟擲一個錯誤。

id：

它被宣告為一個整型數，因為它只包含整型數。除此之外，還有兩個關鍵字，AUTO_INCREMENT和PRIMARY KEY。

AUTO_INCREMENT：

自動生成線性遞增序列，因此不必擔心為每一行的id分配值。

PRIMARY KEY：

每行都由一個非空的UNIQUE列標識。只有一列應該在表中定義。如果一個表包含AUTO_INCREMENT列，則它會被視為PRIMARY KEY。

first_name、last_name和country：

它們包含字串，因此它們被定義為varchar，且字元長度為20個位元組。

Engine：

與列定義一起，還應該指定儲存引擎。在MySQL中常用的儲存引擎包括InnoDB、MyISAM、FEDERATED、BLACKHOLE、CSV和MEMORY。在所有引擎中，InnoDB是唯一的事務引擎，也是預設引擎。

CHARSET：

設定表的預設字符集，如果不單獨定義則會保持與當前庫字符集相同。

COLLATE：

字符集排序規則，同樣如果不定義也是會繼承當前資料庫預設字符集排序規則。

列出所有儲存引擎：

mysql> SHOW ENGINES；

建立資料表常用語句彙總：

CREATE TABLE

new_tbl

LIKE

orig_tbl

；

CREATE TABLE

new_tbl

AS SELECT * FROM

orig_tbl

；

檢視庫中所有表：

SHOW FULL TABLES WHERE Table_type ！= ‘VIEW’

mysql> SHOW TABLES；

+————————+

| Tables_in_test |

+————————+

| customers |

| payments |

+————————+

2 rows in set （0。00 sec）

模糊查詢資料庫對應表：

比如查詢以rs結尾的表可以這樣

mysql> SHOW TABLES LIKE ‘%rs’；

+————————————+

| Tables_in_test （%rs） |

+————————————+

| customers |

+————————————+

1 row in set （0。00 sec）

表維護章

分析表

語法：

ANALYZE ［NO_WRITE_TO_BINLOG | LOCAL］ TABLE tbl_name ［， tbl_name］ ……

功能：

用於收集最佳化器統計資訊、和tuning相關

支援引擎：

InnoDB，NDB和 MyISAM表。它不適用於檢視。

示例圖：

分析表主要作用是分析關鍵字的分佈。檢查表主要作用是檢查表是否存在錯誤。最佳化表主要作用是消除刪除或者更新造成的空間浪費。

使用ANALYZE TABLE分析表的過程中，

資料庫

系統

會對錶加一個只讀鎖。在分析期間，只能讀取表中的記錄，不能更新和插入記錄。

執行結束後ANALYZE TABLE需要從鎖表中刪除該表。如果執行時間過長或者有事務正在使用該表，則後續的語句和事務必須等待它執行結束後才能繼續操作。一般情況下很快就完成了，重新整理鎖表其實並不明顯。

預設情況下，伺服器會將ANALYZE TABLE表語句寫入二進位制日誌，要禁止記錄日誌，請在執行語法中指定可選引數NO_WRITE_TO_BINLOG關鍵字或其別名LOCAL。

ANALYZE TABLE從INFORMATION_SCHEMA。INNODB_TABLESTATS 表中清除表統計資訊 並將STATS_INITIALIZED列設定為Uninitialized。下次訪問該表時，將再次收集統計資訊。

檢查表

語法：CHECK TABLE `tablename` { QUICK | FAST | CHANGED| MEDIUM | EXTENDED }

引數解釋：

功能：

CHECK TABLE

檢查一個或多個表或檢視是否有錯誤

支援引擎：

CHECK TABLE

適用於

InnoDB

，

MyISAM

，

ARCHIVE

，和

CSV

表格

示例圖：

下面我們將表文件開啟隨便編輯一下，前提條件是我們需要先停止MySQL要麼會有程序佔用無法編輯解釋：檢查表或檢視是否有錯誤，資訊欄位輸出OK表示正常。

上圖我們刪除了customer。idb檔案的一行二進位制內容，然後我們在啟動MySQL伺服器。

此時我們就可以看到我們資料表出錯了（這裡只是試驗，實際開發千萬別這麼幹，很危險，而且修復很麻煩，嚴重情況下還得重新建立表）。

NOTE：

檢查InnoDB表

如果CHECK TABLE遇到損壞的頁面，伺服器將退出以防止錯誤傳播（錯誤＃10132）。

如果損壞發生在輔助索引中，但表資料是可讀的，則執行CHECK TABLE仍會導致伺服器退出。

如果CHECK TABLE遇到損壞的索引DB_TRX_ID或 DB_ROLL_PTR聚集索引中的欄位， CHECK TABLE可能導致 InnoDB訪問無效的撤消日誌記錄，從而導致與 MVCC相關的伺服器退出。

如果CHECK TABLE在InnoDB表或索引中遇到錯誤，它將報告錯誤，並且通常會標記索引，有時還會將表標記為已損壞，從而阻止進一步使用索引或表。此類錯誤包括輔助索引中條目的數量不正確或連結不正確。

如果CHECK TABLE在輔助索引中發現條目數不正確，則會報告錯誤，但不會導致伺服器退出或阻止訪問檔案。

CHECK TABLE調查索引頁結構，然後調查每個鍵條目。它不會驗證指向群集記錄的鍵指標，也不會遵循BLOB 指標的路徑。

當InnoDB表被儲存在它自己的

.ibd 檔案

，。ibd檔案包含標題資訊，而不是表或索引資料。該

CHECK TABLE

語句不會檢測到僅影響標頭資料的不一致。要驗證InnoDB 。ibd檔案的全部內容 ，請使用

innochecksum

命令。

CHECK TABLE

在大型InnoDB表上 執行時，其他執行緒可能在

CHECK TABLE

執行期間被阻止。為避免超時，操作的訊號量等待閾值（600秒）會延長2小時（7200秒）

CHECK TABLE

。如果InnoDB 檢測到240秒或更長時間的訊號量等待，它將開始將InnoDB監視器輸出列印到錯誤日誌。如果鎖定請求超出了訊號燈等待閾值，則InnoDB中止該過程。為了完全避免訊號燈等待超時，請執行

CHECK TABLE QUICK

而不是

CHECK TABLE

。

從MySQL 8。0。14開始，InnoDB支援並行聚集索引讀取，這可以提高

CHECK TABLE

效能。 InnoDB在

CHECK TABLE

操作期間兩次讀取聚簇索引。可以並行執行第二次讀取。

innodb_parallel_read_threads

會話變數必須被設定為一個大於1的值用於並行聚簇索引讀取發生。預設值為4。用於執行並行聚集索引讀取的實際執行緒數取決於

innodb_parallel_read_threads

設定或要掃描的索引子樹的數量，以較小者為準。

檢查MyIsAm表

CHECK TABLE更新MyISAM表的關鍵統計資訊。

如果CHECK TABLE輸出未返回OK或Table is already up to date，則通常應修復該表。

如果 未指定CHECK TABLE 選項QUICK， MEDIUM或EXTENDED，則動態格式MyISAM表 的預設檢查型別為 MEDIUM。

最佳化表

語法：OPTIMIZE ［NO_WRITE_TO_BINLOG | LOCAL］ TABLE `tablename` ［， tbl_name］ ……

功能：重新組織表資料和關聯索引資料的物理儲存，回收表空間 、減少表碎片、提高I/O。對每個表所做的確切更改取決於該表使用的儲存引擎。

支援引擎：InnoDB、MyIsAm、ARCHIVE

示例圖：

解釋：上面語句執行後customers表文件會變的更小（前提條件是表存在碎片有可回收空間）。

在什麼情況下用：

OPTIMIZE TABLE的使用取決於表的型別。對於InnoDB建立的表在進行實質性的插入，更新或刪除操作之後使用。因為因為該檔案是在innodb_file_per_table 啟用了該選項的情況下建立的 。重組表和索引，並可以回收磁碟空間以供作業系統使用。

對錶中FULLTEXT全文索引的列進行實質性的插入，更新或刪除操作之後使用。首先設定配置選項 innodb_optimize_fulltext_only=1。為了將索引維護期保持在一個合理的時間內，請設定 innodb_ft_num_word_optimize 選項，以指定要在搜尋索引中更新的單詞數，並執行一系列OPTIMIZE TABLE語句，直到完全更新搜尋索引。

執行過大量刪除的MyISAM 或ARCHIVE表使用，因為刪除的行標記會保留在索引列表中，在磁碟地址上並沒有清楚，後續 INSERT操作將重用舊的行位置。您可以OPTIMIZE TABLE用來回收未使用的空間和對資料檔案進行碎片整理。在對錶進行大量更改之後，該語句還可以顯著提高使用該表的語句的效能。

NOTE：Msg_text中並不是報錯，而是MySQL直接會將InnoDB引擎的表對映一下：

ALTER TABLE `customers` engine=’InnoDB’；

MyIsAm引擎的表不會有這條資訊。

維修表

語法：REPAIR ［NO_WRITE_TO_BINLOG | LOCAL］ TABLE tbl_name ［， tbl_name］ …… ［QUICK］ ［EXTENDED］ ［USE_FRM］

功能：修復可能損壞的表

支援引擎：MyISAM， ARCHIVE和 CSV表，對於 MyISAM表，預設情況下它與myisamchk ——recover tbl_name具有相同的效果

示意圖：

我們先將我們的 customers 表修改成 MyIsAm 儲存引擎，然後對它進行了修復檢查。

NOTE：

Repair 一般用於修復MyIsAm 、ARCHIVE和 CSV表。

在執行此操作前一定要備份表，以免造成資料丟失。

如果在執行repair伺服器宕機後，啟動後必須對所執行表立即執行repair table，然後在執行其他操作。在最壞的情況下，可能有一個新的乾淨索引檔案，而沒有有關資料的資訊，然後執行的下一個操作可能會覆蓋資料檔案。所以前面說了一定要備份備份備份。

如果主伺服器上的表損壞並REPAIR TABLE在其上執行，則對原始表所做的任何更改都 不會傳播到從屬伺服器。

此語法需要具有SELECT 和INSERT 對錶的操作許可權。通常情況下我們是不會執行此語句的，但如果真的發生表損壞了，此語句很可能會幫你從MyIsAm表中找回所有資料。

如果你的表經常損壞，那請你找出根本原因解決問題。

下面說說InnoDB強制恢復

注意：一定要在修復之前備份表或者使用SELECT …… INTO OUTFILE ‘PATCH’轉存表。

如果你的表是InnoDB引擎並且出現了嚴重損壞情況下可以執行強制恢復。

可以在配置檔案中增加如下選項：

［mysqld］

innodb_force_recovery = 1

在緊急情況下此引數設定為大於0的值，就可以啟動InnoDB和轉儲表。這樣做之前，請確保你擁有資料庫的備份副本，以防萬一需要重新建立它。

innodb_force_recovery

預設情況下為0（正常啟動而不強制恢復）。允許的非零值

innodb_force_recovery

是1到6。較大的值包括較小值的功能。

1（SRV_FORCE_IGNORE_CORRUPT）。 使伺服器即使檢測到損壞的

頁面

也可以執行 。嘗試 跳過損壞的索引記錄和頁，這有助於轉儲表。 SELECT * FROM

tbl_name

2（SRV_FORCE_NO_BACKGROUND）。阻止

tbl_name

和任何

主執行緒

執行。如果在

清除執行緒

操作期間發生崩潰，則此恢復值可防止崩潰 。

3 （SRV_FORCE_NO_TRX_UNDO）

清除

後 不執行事務

崩潰恢復

。

4 （SRV_FORCE_NO_IBUF_MERGE）防止

回滾

合併操作。如果它們會導致崩潰，請不要這樣做。不計算表

插入緩衝區

。此值可能會永久損壞資料檔案。使用此值後，準備刪除並重新建立所有二級索引。設定 InnoDB為只讀。

5 （SRV_FORCE_NO_UNDO_LOG_SCAN）啟動資料庫時 不檢視

統計資訊

： InnoDB甚至將未完成的事務也視為已提交。此值可能會永久損壞資料檔案。設定InnoDB為只讀。

6 （SRV_FORCE_NO_LOG_REDO）不進行與

撤消日誌

有關的

恢復

滾。此值可能會永久損壞資料檔案。使資料庫頁面處於過時狀態，從而可能導致B樹和其他資料庫結構遭受更多破壞。設定 InnoDB為只讀。

查看錶狀態

語法：SHOW TABLE STATUS ［{FROM | IN} db_name］ ［LIKE ‘pattern’ | WHERE expr］

解釋：上面我們說過資料庫各表的資訊都儲存在INFORMATION_SCHEMA。TABLES表裡。可以直接在此表查詢也可以透過SHOW TABLE STATUS命令來查詢。

SHOW TABLE STATUS 其實和 SHOW TABLES一樣，區別就是提供了每個非TEMPORARY 表的很多資訊。

輸出欄位解釋：

結語

上面我們學會了表分析、檢查、最佳化（此最佳化只是最佳化資料檔案）、維修操作，下篇我來學習表常規操作。