我知道有很多這樣的文章，但是在閱讀了 InnoDB 性能問題之後，我嘗試過的沒有任何幫助。

我有一個包含四個 InnoDB 表的數據庫。目前最大的表有 700m 行。我正在執行一個 java 應用程序，該應用程序始終通過單個連接插入/更新數據庫。

最初查詢性能還可以，但我注意到偶爾它會變得難以忍受。正如在回答這個問題時向我指出的那樣，問題出在表的記憶體上，可以通過手動將 .idb 文件載入到伺服器記憶體中來解決。然而，這不僅感覺不對，而且當我像這樣擴展我的 MySQL 伺服器時它也不起作用：我複製了初始數據庫的結構 9 次，這樣我就有十個結構相同的表（但不同的數據）。然後我執行我的 java 應用程序十次，這樣每個數據庫就有一個連接，每個連接都持續插入或更新。

由於實現了一個數據庫 -> 十個數據庫的變化，手動載入 .idb 文件只能解決很短時間的慢查詢問題，並且查詢有時會花費更長的時間（峰值為幾秒，平均 200 毫秒為 80 行在 ~70 已經存在並被忽略的地方插入，另外 10 個觸發器每個更新 3 個）。與我之前使用的單個數據庫相比，即使在十個數據庫中使用了大約 2% 的數據總量，並且十個 Java 應用程序共同發送的查詢與單個應用程序之前所做的一樣多（實際上它甚至更少，因為查詢需要很長時間）。在為十個數據庫執行程序時，伺服器本身的響應也慢得多。

我的.cnf

$$ mysqld $$小節：

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

innodb_file_per_table
innodb_autoinc_lock_mode = 0
innodb_fast_shutdown=0

innodb_thread_concurrency=0
innodb_buffer_pool_size=12G
innodb_log_file_size=1600M
innodb_additional_mem_pool_size=1M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0

innodb_write_io_threads=20

key_buffer_size     = 1024M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

伺服器有 24G 記憶體和 4 個 cpu 核心。如果查詢非常慢，CPU 使用率會下降到個位數百分比。

表結構：

CREATE TABLE `table1` (
 `table1_id` int(13) NOT NULL DEFAULT '0',
 `epoch` bigint(13) NOT NULL,
 ...
 [a few enum columns]
 PRIMARY KEY (`table1_id`),
 KEY `epoch` (`epoch`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `table2` (
 `performanceId` bigint(20) NOT NULL AUTO_INCREMENT,
 `table4_id` int(10) NOT NULL,
 `column3` int(3) NOT NULL,
 `column4` enum(...) NOT NULL,
 `table1_id` int(12) NOT NULL,
 PRIMARY KEY (`performanceId`),
 UNIQUE KEY `uniqueKey` (`table4_id`,`table1_id`),
 KEY `secondaryKey` (`table1_id`,`table4_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TRIGGER `myTrigger` AFTER INSERT ON `table2`
FOR EACH ROW BEGIN
UPDATE table4
SET counter=counter+1
WHERE table4.table4_id=NEW.table4_id;
UPDATE table4
SET column5=100-column5
WHERE table4.table4_id=NEW.table4_id AND counter >= 7;
END


CREATE TABLE `table3` (
 `performanceId` bigint(20) NOT NULL AUTO_INCREMENT,
 `table4_id` int(10) NOT NULL,
 `table1_id` int(12) NOT NULL,
 ...
 [about 20 more NOT NULL integers columns]
 PRIMARY KEY (`performanceId`),
 UNIQUE KEY `uniqueKey` (`table4_id`,`table1_id`),
 KEY `secondaryKey` (`table1_id`,`table4_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `table4` (
 `table4_id` int(10) NOT NULL,
 `column2` bigint(13) NOT NULL DEFAULT '0',
 `column3` bigint(13) NOT NULL DEFAULT '0',
 `column4` varchar(30) NOT NULL DEFAULT '',
 `column5` bigint(13) NOT NULL DEFAULT '500',
 `counter` int(3) NOT NULL DEFAULT '1',
 PRIMARY KEY (`table4_id`),
 KEY `key1` (`column4`),
 KEY `key2` (`column2`),
 KEY `key3` (`column5`,`column3`),
 KEY `key4` (`counter`,`column3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我的應用程序的典型查詢週期如下所示：

INSERT IGNORE INTO table1 ([columns]) VALUES ([10 rows])
INSERT IGNORE INTO table2 ([columns except primary]) VALUES ([~80 rows])
INSERT IGNORE INTO table3 ([columns except primary]) VALUES ([10 rows])
INSERT IGNORE INTO table4 (table4_id) VALUES ([~80rows])
UPDATE table4 SET column2=UNIX_TIMESTAMP()*1000, column3=0, counter=0, column5=[some value] WHERE table4_id=[some value]

其中大約 15% 的 table1 和 table2 行，50-90% 的 table3 和低於 1% 的 table4 行實際上被插入，其餘的被忽略，因為它已經存在。

編輯：我忘了添加一些關於數據的內容：如您所見，table2 和 table3 每個都通過 n:m 關係連接 table1 和 table4 的實體。table4 每天增長不到 50k 行，table1 每天增長大約 1m 行。table1 中的每個實體在 table2 和 table3 中最多有 10 行（平均 8 行）。隨著時間的推移，table4 中的每個實體在 table2 和 table3 中的行數越來越多。

編輯：為了顯示查詢的不可靠性，我附上了這個圖，它顯示了自程序啟動以來每 5 分鐘間隔的查詢週期數。

這是在十個數據庫伺服器上，我禁用了 10 個連接中的 8 個，這是剩下的兩個之一：

這是單個數據庫伺服器上的最後 24 小時（那個只有 6GB 的 innodb_buffer_pool_size，也許這就是它更不穩定的原因？）：

創新數據庫

對 dml 語句不利，#MyISAM 更好。Innodb 嘗試將所有 INSERTing 數據送出到磁碟並在 pin 後返回成功。

當您需要大量插入數據時，您可以避免 auto-commit=0 以獲得更快的速度。或者您可以檢查此提示。

您的表確實有幾個要更新的索引，您還記得不時執行優化表嗎？

/usr/bin/mysqlcheck -o --auto-repair MyDatabaseName

這將優化數據庫中的所有表和索引MyDatabaseName。

根據您的數據的外觀，這可能有助於INSERT提高SELECT速度。我想既然你已經嘗試了所有的系統優化，那麼你就一直在經歷所有的系統優化。如果真的只有一個程序更新數據庫，那麼使用 InnoDB 可能沒有更大的好處，而 MyISAM 可能會做得更好。

通常，當 MySQL 伺服器上的語句很慢時，可能會發現瓶頸，這是您目前最受限制的因素。有時這些會出現在毫無戒心的地方，比如 linux IO 調度程序（deadline 或 noop 是數據庫伺服器唯一明智的選擇，具體取決於使用的​​驅動器和控制器），查詢記憶體可能會大大減慢執行速度，因為有時管理記憶體遠遠超過了它的用處（經常刷新或禁用），啟用名稱解析和 DNS 伺服器速度慢……只是列舉一些流行的。

我的個人清單：

RAM（有足夠的空閒/記憶體 RAM，MySQL 合理使用 RAM ( mysql-tuning.sh)）SWAP 應該只包含“未使用的數據和程序”，在正常操作期間不應該發生與交換的主動傳輸！

CPU（負載少於核心，0% idel 始終沒有單個核心 => 多執行緒/鎖定問題，所有 CPU 使用率高（低空閒百分比）需要更快的 CPU 或更好的優化）

IOload / IOwait 如果這具有明顯的高價值，則您的儲存系統或網路最有可能是罪魁禍首（RAID 5 或 RAID 6 卷上的 DB 卷、蹩腳的 NAS、愚蠢的 SAN 設置等）

查詢數、連接數、每秒新連接數……與執行的 SQL 程式碼（鎖定、事務隔離）或 MySQL 配置相關的任何內容。

由於 TCP 套接字用完，每分鐘大量的新連接是一個真正的問題……

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