我創建了一個在虛擬機上執行的 django 應用程序。所有 db 表都相當小（最多 1M 行），但最近我被要求添加一個大表（460M 行）。

CREATE TABLE `genescorrelation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`correlation` double NOT NULL,
`gene1_id` int(10) unsigned NOT NULL,
`gene2_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `c_gene1_id_6b1d81605661118_fk_genes_gene_entrez`   (`gene1_id`),
KEY `gene2_gene1_idx1` (`gene2_id`,`gene1_id`)) 
ENGINE=InnoDB AUTO_INCREMENT=92345005 DEFAULT CHARSET=latin1

使用者將執行的查詢（最多 10 個基因）：

SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene1_id ORDER BY NULL 

目前我已經導入了大約 89M 行並且查詢很慢。3 或 4 個基因大約需要 30-40 秒。

我認為我的虛擬機太弱了，因為它是預設設置（對於較小的項目/數據庫）。目前它只有 2GB RAM 和 36GB 空間。

我需要要求設置產品機器。

這種設置的記憶體/空間建議是什麼？

在 MySQL 配置中有什麼可以做的嗎？

我還能做些什麼來加快查詢速度嗎？

任何提示表示讚賞，謝謝。

僅供參考，我問了我最初的問題：https ://stackoverflow.com/questions/31969964/mysql-slow-avg-query-for-411m-rows 。之後我嘗試添加建議的索引 (gene2,gene1) 但沒有足夠的空間，所以刪除了表並重新導入了 90M 行。

看著查詢

SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene1_id ORDER BY NULL 

我有四 (4) 條建議

建議 #1

我看到三 (3) 列。我建議製作一個覆蓋索引

ALTER TABLE genescorrelation
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

StackOverflow 接受的答案已經提到過製作這個 index。這樣，聚合永遠不會觸及表格。所有需要的數據都來自索引。

既然你已經在gene2_gene1_idx1這裡做了修復

ALTER TABLE genescorrelation
DROP INDEX gene2_gene1_idx1,
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

建議 #2

您可能需要更多閱讀執行緒

將此添加到my.cnf

[mysqld]
innodb_read_io_threads = 8

需要重啟mysql

建議#3

如果伺服器只是一個 DB 伺服器，您應該為InnoDB 緩衝池或所有 InnoDB 數據和索引的總和 ( SELECT SUM(data_length+index_length) InnoDBDataAndIndex FROM information_schema.tables WHERE engine='InnoDB';) 保留最多 75% 的 RAM，以較小者為準。

建議 #4（可選）

更改查詢以查看細分gene2_id

SELECT
   IFNULL(gene2_id,'All Gene2 IDs') gene2,
   IFNULL(gene1_id,'All Gene1 IDs') gene1,
   AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene2_id,gene1_id WITH ROLLUP;

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