如何優化我的 mysql 設置以更快地創建索引？

我有一台執行 Ubuntu 10.04 的伺服器，通過包安裝了 Mysql 5.1x。該系統有 128GB 記憶體，8 個核心，並有 4TB 的可用空間用於儲存 Mysql 和 Mysql tmp。

我有一個這樣的 MyISAM：

CREATE TABLE `data_store` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `uniqname` varchar(150) NOT NULL,
 `data` blob,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

我插入了 8 億條記錄（插入前大約 350gb 數據），然後嘗試添加以下索引：

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

（關於DISABLE KEYS命令，我在其他地方看到建議在插入數據之前使用，並且由於命令從未到達ENABLE KEYS，我認為它對我沒有任何用途。我主要只是在我對我的描述中包含了徹底的內容。我在做。）

當我啟動索引作業時，顯示的第一個狀態SHOW PROCESSLIST是“正在復製到 tmp 表”。

幾個小時後，我檢查 bac，即使在 24 小時後，狀態仍會顯示在“Repair With Keycache”消息中。我嘗試在稍舊的伺服器上執行該作業，3 天后，它仍然保持“使用 keycache 修復”狀態。因此，我取消了這台較新機器上的 create-index 命令。

我讀過“使用 Keycache 修復”可能非常慢，在許多情況下，“通過排序修復”是首選。

基於一些 Stack Exchange 和網上的隨機文章，我在 Mysql 伺服器中添加了以下設置：

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

我重新啟動了作業，同樣的過程再次發生（複製到 tmp 文件，然後通過密鑰記憶體修復）。

在我終止作業後，我注意到在 mysql/error.log 中有一條消息，“myisam_sort_buffer_size 太小”。這發生在當天早些時候，而不是我殺死工作的時候。

問題

• 我會走錯路嗎？我只是希望能夠通過某個鍵（uniqname）快速查找我的數據。
• 從我的表開始，從頭開始添加索引，使用DISABLE KEYS命令，插入我的 8 億條記錄，然後再開始，有什麼好處ENABLE KEYS嗎？我在其他地方讀到這可以防止複制 tmp 表（這可能只能節省我幾個小時？）
• 我想要這個“通過排序修復”嗎？

對於初學者，我暫時不會觸及緩衝區大小。您在問題中的尺寸太大了。

這是另一個觀察結果：您有 BLOB 數據。哎呀，您的臨時表將很快佔用空間。你可以這樣做：

通過將此行添加到 /etc/fstab 創建一個名為 /var/tmpfs 的 32GB RAM 磁碟

none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

接下來，創建一個名為 /mysqltmp 的文件夾並在其上掛載 RAM 磁碟

mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

將此添加到 my.cnf 並重新啟動 mysql

[mysqld]
tmpdir=/mysqltmp

現在，任何通過 DDL 生成的 tmp 表都會進入 RAM 磁碟。

這是另一個觀察結果：為什麼不創建一個單獨的表來使 BLOB 數據遠離唯一名稱？

CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

這將防止在索引時移動 BLOB 數據。

從這裡開始，您必須始終使用它的名稱加入 data_store，如下所示：

SELECT
   A.uniqname,B.data
FROM
   (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
   LEFT JOIN
   data_store B USING (id)
;

進行這些更改將避開處理 keycache、RAM 磁碟和 tmp 表的整個混亂局面。

試一試 ！！！

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