如何將MySQL大表分解為多個以解決操作問題

這是我目前在 MySQL 中的表模式，我們的應用程序沒有遇到任何性能問題，但存在操作問題。該表的大小為7 TB（6 TB 數據和 1 TB 索引）並且有 40 億行。

由於這個表有這麼大的尺寸，我們不能在這個表上做任何改變表。我們必須使用 percona 需要 1 週才能完成。

所以為了處理這個問題，我們決定把這張桌子分成 3 份。有兩列儲存我們要移動到單獨表的 xml 文件，僅這兩列就需要 2.5 TB 的儲存空間。

DETAILSlongtext 和SUMMARYvarchar(4000) DEFAULT NULL，

除此之外，我們還希望將更多列移動到另一個表，以便所有三個表都像第三個表一樣變得更輕，我們要移動**USES_TYPEvarchar(255) NOT NULL、 STEP_TYPEvarchar(255) NOT NULL、NAMEvarchar(1500) DEFAULT NULL、和REMARKSvarchar(1000) 預設為 NULL，**

CREATE TABLE `app_data` (
`ID` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`STEP_TYPE` varchar(255) NOT NULL,
`CUST_ID` varchar(255) DEFAULT NULL,
`DETAILS` longtext,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP_ID` varchar(255) DEFAULT NULL,
`SYSTEM_ID` varchar(255) DEFAULT NULL,
`NAME` varchar(1500) DEFAULT NULL,
`CUSTOMER_ID` varchar(255) DEFAULT NULL,
`REMARKS` varchar(1000) DEFAULT NULL,
`SUMMARY` varchar(4000) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_DATA_CID_OT` (`CUST_ID`,`USES_TYPE`) USING BTREE,
KEY `IDX_APP_DATA_SYSTEM_ID` (`SYSTEM_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Now my final table will be comething like 

Table one 

CREATE TABLE `app_data_table1` (
`ID` varchar(255) NOT NULL,
`DETAILS` longtext,
`SUMMARY` varchar(4000) DEFAULT NULL;
)

Table Two would be like 

CREATE TABLE `app_data_table2` (
`ID` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`STEP_TYPE` varchar(255) NOT NULL,
`NAME` varchar(1500) DEFAULT NULL,
`REMARKS` varchar(1000) DEFAULT NULL;
)

and table three 

CREATE TABLE `app_data` (
`ID` varchar(255) NOT NULL,
`CUST_ID` varchar(255) DEFAULT NULL,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP_ID` varchar(255) DEFAULT NULL,
`SYSTEM_ID` varchar(255) DEFAULT NULL,
`CUSTOMER_ID` varchar(255) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_DATA_CID_OT` (`CUST_ID`) USING BTREE,
KEY `IDX_APP_DATA_SYSTEM_ID` (`SYSTEM_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

我是數據庫的新手，但這就是我要提出的，我知道這不是優化的，所以我謙虛的請求是請指導我。

一旦我們這樣做了，我們需要使用 join 來顯示 UI 或我們獲取的任何地方，這樣會更慢嗎？

還有其他你可以而且應該做的事情。

• 正常化。例如，GROUP_IDor有多少個不同的值SYSTEM_ID？不要使用字元串，而是使用TINYINT UNSIGNED– 1 字節和 0..255 的範圍，或者使用SMALLINT UNSIGNED– 2 字節和 0.65K。等等。
• 是否有任何列“稀有”？我的意思是它們在許多行中都是 NULL 嗎？如果是這樣，請設計拆分，以使新行不是完整的 40 億行，而是僅包含所需的行。然後LEFT JOIN在需要取回 NULL 時使用。
• UUID 是如何生成的？如果它們是標準的，則很容易從 36 字節壓縮到BINARY(16)（16 字節）。
• 不要盲目使用(255)，找到一個明智但保守的限制。（這對性能的影響很小。）
• 現在主要的查詢是什麼？搬到3張桌子後？我們應該確保索引對所有這些都是“正確的”。如果拆分完成“錯誤”，某些查詢將變得更慢，並且無法通過索引修復。
• DETAILS大約需要2.5TB？如果你壓縮它，它可能會縮小到不到 1TB。但是在客戶端而不是伺服器中進行。然後將結果放入MEDIUMBLOB. 並在獲取後在客戶端解壓縮。
• 考慮PARTITION。它不太可能幫助任何事情。但是，如果您清除“舊”數據，則該過程比DELETE. 這將無濟於事ALTER TABLE。
• 你是用來做什麼ALTER的？對於某些任務，有一些便宜的技巧可以執行。
• PK 是 varchar(255) 聽起來很糟糕；讓我們討論替代方案。其他列的任何組合是否提供唯一鍵？
• SELECT AVG(LENGTH(...)), AVG(LENGTH(...)), ... FROM t看看每列可能有什麼好處。

“Alter主要用於增加列和增加索引”——

• MySQL 8.0 具有更快的 ALTER 操作。有些是“即時的”。您需要仔細查看細節，看看哪些是快速的，哪些需要副本。有些口味比其他口味更能阻止其他行為。
• 如果唯一的目標是添加新列，請考慮添加新表。這與您最初的想法相似，但沒有任何停機時間。它還可以僅在必要時利用行（請參閱我LEFT JOIN上面的評論）。
• 但是…如果您需要搜尋新集合（具有多個表），有些查詢可能很有效，有些則效率極低。請提供一些您過去做過或將來可能做的具體例子；我將更詳細地解釋。也許開始一個新問題並顯示新舊問題CREATE TABLE，以及SELECT涉及新列的問題；然後詢問性能。

引用自：https://dba.stackexchange.com/questions/289841