現在有兩種方法可以在 MySQL 中建立表的索引：

1. 首先創建表結構，然後導入數據並稍後添加索引。
2. 創建帶有索引的表結構，然後導入數據。

在第一個過程中，我們將有連續的數據（所有欄位）頁面，然後我們有索引頁面。所以當我們使用索引查詢時，MySQL 必須首先載入索引頁面並找出匹配的鍵，然後必須在數據頁面上查找那些主鍵。為此，它必須再次載入數據頁面以獲取數據。當我們有更大的索引掃描時，這很有用，因為我們已經連續載入了所有索引。

在索引創建的第二種方式中，過濾後的索引頁面很可能包含在它附近的數據頁面，因為它們是同時創建的。所以我猜對於小範圍掃描，查找會更快。

我的理解正確嗎？

更新：

我應該在導入數據的第一種方式中提到“已啟用主鍵”（自動遞增 id 列）。因此不會生成內部 rowid 並且會保存大量 IO，因為我們不會添加 PRIMARY KEY。

正如您所指出的，當我們使用第二種方法導入數據時會出現碎片。

考慮到我的要求是更大範圍的掃描（掃描約 100M 行），我想我會採用第一種導入數據的方式。

6月8日11:30更新

CREATE TABLE `table_dummy` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `column1` bigint(20) DEFAULT NULL,
 `column2` bigint(20) DEFAULT NULL,
 `column3` bigint(20) DEFAULT NULL,
 `created_at` datetime DEFAULT NULL,
 `column4` tinyint(1) DEFAULT NULL,
 `column5` tinyint(4) DEFAULT NULL,
 `column6` bigint(20) DEFAULT NULL,
 `column6_created_at` datetime DEFAULT NULL,
 `column7` int(11) DEFAULT NULL,
 `column8` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `twtaccount_id_2` (`column1`,`column2`),
 KEY `twtaccount_id` (`column1`,`created_at`,`column5`),
 KEY `twt_user_id` (`column3`,`created_at`,`column5`),
 KEY `original_status_id` (`column6`,`column3`,`followers_count`)
)

ENGINE是INNODB

該表有大約 800M 記錄，我進行了轉儲並以兩種方式導入它。

方法1：僅使用主鍵創建表並導入整個數據。然後我給出了一個 alter 語句來添加剩餘的索引。

方法 2：使用索引創建表並完成轉儲。這花費了更長的時間。

PS 與通過“方法 2”轉儲的表的大小相比，通過“方法 1”生成的表的大小小了約 30GB。方法 1 比“方法 2”花費的時間少得多，幾乎是速度的兩倍。

我主要關心的是當我選擇掃描廣泛的索引（“僅索引掃描”）時表的性能。

我可以在這里為您澄清幾件事：

• 是的，將二級索引創建延遲到導入數據之後（從 MySQL 5.5 開始 - 而不是之前）是一個很好的做法。 Mysqlpump預設執行此操作。
• 當您延遲創建二級索引時，MySQL 內部會讀取、排序然後創建索引（減少碎片）。對於 MySQL 5.7，這裡有額外的優化。
• 當您涓流載入索引時，它們可能會有更多的頁面拆分和較低的頁面填充效率（碎片化）。這在一定程度上取決於數據，以及數據是否有序。你沒有給我很多線索column1, column2，而是說例如column1它可能是一個時間戳。
• 索引中的頁面在邏輯上是有序的，不一定是物理上的。如果您的工作集無法適應記憶體中的範圍並且使用旋轉磁碟作為備份儲存，則這種區別可能很重要。這也有點難以回答，因為對於旋轉磁碟，您可能正在使用具有一定條帶大小的 RAID，並且文件系統級別的塊也可能是不連續的。
• 另請注意，優化器可能不會考慮通過 tablescan 對大範圍進行範圍掃描（因為在 5.7 之前，成本模型假定頁面不在記憶體中）。如果您可以依賴這種情況，您可能想要FORCE INDEX比較實際的執行時間。（有關新成本模型的更多資訊。）

在第一種方式中，您是正確的。這就是我說軟的原因。

InnoDB 有一個聚集索引，內部稱為 gen_clust_index 。基本上，它是rowid在引擎蓋下為 InnoDB 表創建的索引（或者如果您像我一樣來自新澤西州澤西市，則在引擎蓋內創建）。這些 rowid 附加到所有二級索引。

如果構成KEY的列的寬度比 . 更寬，則PRIMARY KEY事後創建.PRIMARY KEY``PRIMARY``rowid

對於 InnoDB，創建索引的第二種方法會更有意義，因為您沒有在底層交換PRIMARY KEY’ 的定義。這是您已經提到的好處（data page near to it as they were created at the same time）的補充。在這方面，第一種索引方法沒有任何好處，充其量是收支平衡。

雖然第二種方法更適合需求（look up will be faster for a small range scans），但我有一個非常強烈的警告：如果你PRIMARY KEY用已經排序的列數據填充，你很可能最終會出現一些索引碎片（請參閱我對Oct 26, 2012文章的回答How badly does面對一些亂序插入的innodb片段？）如果你知道你正在進行小範圍掃描，這不是一個糟糕的權衡。

更新 2015-05-26 16:30 EST

在這種情況下，同樣的警告也適用。你會有一個支離破碎的PRIMARY KEY. May 01, 2014我對文章 的回答中表達了這一點，為什麼 MySQL MyISAM 表索引（又名 MYI 文件）的大小在 mysqldump 導入後不匹配？

要手動證明它，請執行以下操作

步驟 01：從數據庫伺服器 mysqldump 數據

步驟02：將其載入到測試伺服器中

執行查詢

SELECT IFNULL(ENGINE,'Total') "Storage Engine",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT ENGINE,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT engine,data_length,index_length
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY ENGINE WITH ROLLUP) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

步驟 04：來自測試伺服器的 mysqldump

步驟 05 : 再次將測試伺服器的轉儲重新載入回測試伺服器

步驟 06：從步驟 03 執行查詢

有時，大量數據在重新載入mysql數據時會產生不同的大小

底線：與 InnoDB 相比，這兩種方法都沒有顯著優勢。

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