為什麼在 MySQL 表上添加索引會顯著減慢它，但在 SQL Server 和 PostgreSQL 上可以

版本 MySQL 5.7.2

我正在處理帶有 int 列的虛擬數據。

時間：1-10mm

產品：70,000 個隨機整數，另外 30,000 個是來自 70,000 個的欺騙

音量：範圍從 500 - 1000

價格：範圍為 10 - 50，但每個產品行數據的價格保持在 1-5 差異的範圍內。

從上面，通過隨機選擇一個產品並生成所需的行數據來創建 10mm 行

執行範圍查詢，例如…

select * from productdata where product >= 1500 and product <= 2000

大約需要 4 秒。

當我使用…添加產品索引時

create index productindex on productdata(product)

現在查詢大約需要 30 秒。時間是表中唯一的唯一列，但將其設置為主鍵也無濟於事。

在 SQL Server 和 PostgreSQL 上，我沒有看到相同的數據和使用非聚集索引進行查詢的相同問題。我只有真正為 SQL Server 編寫查詢的經驗，所以對此有點困惑。我也嘗試使用 PostgreSQL 來比較另一個數據庫。

所有數據庫都是可用的最新穩定版本。

操作輸出（由於原始查詢花費的時間太長，我不得不減小範圍）…

沒有索引..

帶索引..

表狀態…

緩衝…

解釋選擇…

顯示創建表…

CREATE TABLE `products` (
 `time` int(11) DEFAULT NULL,
 `product` int(11) DEFAULT NULL,
 `quantity` int(11) DEFAULT NULL,
 `price` int(11) DEFAULT NULL,
 KEY `productindex` (`product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

嘗試以時間和產品為關鍵…

我的ISAM？創新數據庫？記憶體大小是多少？( SHOW VARIABLES LIKE '%buffer%';) 您是否再次執行查詢（以消除記憶體的影響）？你有TEXT或BLOB列？（請提供SHOW CREATE TABLE。）多少記憶體？表 ( ) 有多大 (GB SHOW TABLE STATUS)。

可能…… 肯定……**數據的記憶體不足以容納有問題的產品。而且，由於產品大多是“隨機的”，因此存在大量 I/O。

以下是該查詢在任何（？）數據庫供應商中發生的情況：

1. 在索引中找到 1500 的“行”。
2. 向前掃描索引直到 2000 年。（由於 MySQL 中的 BTree 組織，這非常有效。）
3. 對於每一行，進入“數據”以獲取所有列 ( SELECT *)。（這就是供應商不同的地方。MySQL 中的“引擎”會有所不同。）由於數據行是一種順序，但索引行是一種不同的順序，MySQL 將有效地執行“隨機 I/O”來獲取行。（MySQL 根據需要獲取行。其他供應商可能首先對行地址進行排序——也許是一個好處，也許是一個成本。）
4. 對於具有 InnoDB 和大型 TEXT/BLOB 列的 MySQL，這些列可能儲存在其他地方，因此需要額外的 I/O。（因此告誡不要使用*，而只拼出必要的列。）

但…

如果需要獲取“太多”的表——特別是超過 20% 的表product在所需範圍內——MySQL 將進行表掃描而不是使用索引。（我不了解非 MySQL 供應商。）這種優化通常是有益的，但有時是錯誤的。會EXPLAIN SELECT ..告訴我們它做了什麼。

InnoDB 通過與數據PRIMARY KEY“聚集”的“進入數據”。所以找到每一行是一個BTree-probe。MyISAM 在數據文件中有一個字節地址，所以它是一個fseek. 其他供應商的工作方式不同。

那麼……比較“公平”嗎？您是否將 PK 與所有供應商測試的數據“聚集”在一起？（ETC）

而且我沒有進入記憶體細節。這可能是經濟放緩的一個主要組成部分。

在您提供更多詳細資訊後，我將填寫更多詳細資訊。

詳細後

您使用的是 InnoDB，而不是 MyISAM（好）。 太小了innodb_buffer_pool_size = 8M。對於 16GB 的 RAM，建議使用. 但即使是 1G 也會顯示出顯著的加速，因為表比這小。11G

您說您正在執行 MySQL 5.7，但 8M 預設值與此相矛盾。您是否覆蓋了預設值？請提供版本 ( SELECT @@version;)。

似乎沒有大TEXT或BLOB專欄，所以我對此的評論不適用。

底線：要獲得“公平”比較，請增加 MySQL 的 buffer_pool 設置。這是性能最重要的可調參數。它不會自動設置，因為它取決於可用RAM 的數量。

緩衝池小，設置較大的值。

SELECT @@innodb_buffer_pool_size; SET GLOBAL innodb_buffer_pool_size=402653184; SELECT @@innodb_buffer_pool_size; select * from productdata where product >= 1500 and product <= 2000

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