用於提升資料庫效能的頂級 ySQL 架構檢查-mysql教程-PHP中文網

資料庫模式定義了資料庫的邏輯結構，包括表、列、關係、索引以及影響資料組織和存取方式的限制。這不僅涉及資料的儲存方式，還涉及資料如何與查詢、事務和其他操作互動。

這些檢查可以幫助您在任何新的或揮之不去的問題像滾雪球一樣發展成更大的問題之前掌握它們。您可以深入研究下面的這些架構檢查，並確切了解如何解決資料庫未通過的問題。請記住，在進行任何架構更改之前，請務必備份數據，以防止修改期間可能發生的潛在風險。

1.主鍵檢查（缺少主鍵）

主鍵是任何表的關鍵部分，它唯一標識每一行並實現高效查詢。如果沒有主鍵，表可能會遇到效能問題，複製和架構變更實用程式等某些工具可能無法正常運作。

設計模式時定義主鍵可以避免幾個問題：

如果未指定主鍵或唯一鍵，MySQL 會建立一個內部主鍵或唯一鍵，該主鍵或唯一鍵無法使用。
缺少主鍵可能會降低複製效能，尤其是基於行或混合的複製。
主鍵允許可擴展的資料歸檔和清除。 pt-online-schema-change 等工具需要主鍵或唯一鍵。
主鍵唯一標識行，這從應用程式的角度來看至關重要。

例子

要在建立表格時在「ID」欄位上建立 PRIMARY KEY 約束，請使用下列 SQL：

ALTER TABLE Persons ADD PRIMARY KEY (ID);

登入後複製

要在多個欄位上定義主鍵：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

登入後複製

注意：如果使用 ALTER TABLE 命令，則在首次建立表格時必須聲明主鍵列不包含 NULL 值。

2.表引擎檢查（已棄用的表引擎）

MyISAM儲存引擎已被棄用，仍在使用它的表應遷移到InnoDB。由於其卓越的效能、資料復原功能和事務支持，InnoDB 是大多數用例的預設和推薦引擎。從 MyISAM 遷移到 InnoDB 可以顯著提高寫入密集型應用程式的效能，提供更好的容錯能力，並允許更高級的 MySQL 功能，例如全文搜尋和外鍵。

為什麼首選 InnoDB：

崩潰恢復功能使其能夠從資料庫伺服器或主機崩潰中自動恢復，而不會損壞資料。
僅鎖定受查詢影響的行，從而在高並發環境中獲得更好的效能。
將資料和索引快取在記憶體中，這對於讀取繁重的工作負載來說是首選。
完全符合 ACID，確保資料完整性並支援事務。
InnoDB 引擎受到了 MySQL 開發社群的大部分關注，使其成為最新且支援良好的引擎。

如何遷移到InnoDB

ALTER TABLE Persons ADD PRIMARY KEY (ID);

登入後複製

3.表格排序規則檢查（混合排序規則）

跨表甚至在表內使用不同的排序規則可能會導致效能問題，特別是在字串比較和連接期間。如果兩個字串列的排序規則不同，MySQL 可能需要在執行時間轉換字串，這會阻止使用索引並減慢查詢速度。

當您對混合排序規則表進行更改時，可能會出現一些問題：

列層級的排序規則可能不同，因此如果聯結中的相關列具有符合的排序規則，則表層級的不符不會導致問題。
更改表的排序規則，尤其是使用字元集切換，並不總是那麼簡單。可能需要進行資料轉換，且不支援的字元可能會變成損壞的資料。
如果建立表格時未指定排序規則或字元集，它將繼承資料庫預設值。如果在資料庫層級未設定任何內容，則將套用伺服器預設值。為了避免這些問題，標準化整個資料集的排序規則非常重要，特別是對於連接操作中經常使用的列。

如何更改排序規則設定

在對資料庫的排序規則設定進行任何變更之前，請在非生產環境中測試您的方法，以避免意外後果。如果您有任何不確定的地方，最好諮詢 DBA。

擷取所有資料庫的預設字元集和排序規則：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

登入後複製

檢查特定表格的排序規則：

ALTER TABLE <table_name> ENGINE=InnoDB;

登入後複製

尋找伺服器的預設字元集：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

登入後複製

尋找伺服器的預設排序規則：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

登入後複製

更新特定資料庫的排序規則：

SELECT @@GLOBAL.character_set_server;

登入後複製

更新特定表格的排序規則：

SELECT @@GLOBAL.collation_server;

登入後複製

4.表字符集檢查（混合字符集）

混合字元集與混合排序規則類似，因為它們可能會導致效能和相容性問題。當不同的欄位或表格使用不同的編碼格式來儲存資料時，就會出現混合字元集。

混合字元集可能會阻止索引使用或需要值轉換，從而損害字串列的連接效能。
可以在列層級定義字元集，只要連接涉及的列具有相符的字元集，效能就不會受到表格層級不匹配的影響。
更改表的字符集可能涉及資料轉換，如果遇到不支援的字符，可能會導致資料損壞。
如果未指定字元集或排序規則，表將繼承資料庫的預設值，資料庫將繼承伺服器的預設字元集和排序規則。

如何更改角色設定

在調整資料庫的字元設定之前，請務必在臨時環境中測試更改，以防止任何意外問題。如果您對任何步驟不確定，請諮詢 DBA 以獲得指導。

擷取所有資料庫的預設字元集和排序規則：

ALTER TABLE Persons ADD PRIMARY KEY (ID);

登入後複製

取得某列的字元集：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

登入後複製

尋找伺服器的預設字元集：

ALTER TABLE <table_name> ENGINE=InnoDB;

登入後複製

尋找伺服器的預設排序規則：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

登入後複製

查看表格的結構：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

登入後複製

範例輸出：

SELECT @@GLOBAL.character_set_server;

登入後複製

要更改列字元集：

SELECT @@GLOBAL.collation_server;

登入後複製

5. 列自增檢查（自增列型）

對於預計無限增長並使用主鍵自動遞增的表，建議切換到 UNSIGNED BIGINT 資料類型。這允許列處理更大範圍的值，從而防止將來達到最大值後需要進行昂貴的表更改。透過指定 UNSIGNED，僅儲存正值，從而有效地將資料類型的範圍加倍。

如何更改角色設定

要將欄位類型修改為 UNSIGNED BIGINT：

ALTER DATABASE <db-name> COLLATE=<collation-name>;

登入後複製

6. 表外鍵檢查（外鍵是否存在）

外鍵透過維護父表和子表之間的關係來提供資料一致性，但它們也會影響資料庫效能。每次發生寫入操作時，都需要進行額外的查找來驗證相關資料的完整性。這可能會導致速度變慢，尤其是在高流量環境中。

如果效能是一個問題，您可能需要考慮刪除外鍵，特別是在可以在應用程式層級處理資料一致性的場景中。

如何刪除外鍵

從表中刪除外鍵約束：

ALTER TABLE Persons ADD PRIMARY KEY (ID);

登入後複製

7. 重複索引檢查

MySQL 中的重複索引會消耗不必要的磁碟空間，並在寫入作業期間產生額外的開銷，因為每個索引都必須更新。這可能會使查詢最佳化複雜化，可能導致執行計劃效率低下，而不會帶來任何實際好處。

識別並刪除重複索引以簡化查詢最佳化並減少開銷。但在刪除索引之前，請確保索引沒有用於關鍵查詢。

8. 未使用的索引檢查

MySQL 中未使用的索引可能會消耗磁碟空間，增加插入、更新和刪除期間的處理開銷，並減慢整體操作，從而對資料庫效能產生負面影響。雖然索引對於加快查詢速度很有價值，但不使用的索引可能會對您的系統造成不必要的壓力。
刪除未使用或重複的索引的其他好處包括：

索引越少，MySQL 最佳化器評估的選擇就越少，從而簡化查詢執行並減少 CPU/記憶體使用。
刪除未使用的索引可以釋放寶貴的磁碟空間，可用於更關鍵的數據，同時提高 I/O 效率。
當索引數量最小化時，索引維護任務（例如重建或重新組織）會變得更快且資源佔用更少。這使得操作更加順暢，特別是在需要 24/7 正常運作時間的環境中。

要辨識 MySQL 或 MariabDB 中未使用的索引，請使用下列 SQL 語句：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

登入後複製

如何刪除未使用或重複的索引

在 MySQL 8.0 及更高版本中，您可以使索引不可見以測試它們是否需要，而無需完全刪除它們：

ALTER TABLE <table_name> ENGINE=InnoDB;

登入後複製

如果效能不受影響，則可以安全刪除索引：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

登入後複製

如果需要，您可以將索引還原為可見：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

登入後複製