備注:測(cè)試數(shù)據(jù)庫(kù)版本為MySQL 8.0

一. 優(yōu)化InnoDB表的存儲(chǔ)布局

一旦您的數(shù)據(jù)達(dá)到穩(wěn)定的大小境蔼，或者一個(gè)不斷增長(zhǎng)的表增加了幾十或幾百兆字節(jié)仑最，請(qǐng)考慮使用OPTIMIZE table語(yǔ)句來(lái)重新組織表并壓縮任何浪費(fèi)的空間。重組后的表需要更少的磁盤I/O來(lái)執(zhí)行全表掃描龄广。這是一種簡(jiǎn)單的技術(shù)瘪弓，可以在其他技術(shù)(如改進(jìn)索引使用或調(diào)優(yōu)應(yīng)用程序代碼)不實(shí)用時(shí)提高性能。

OPTIMIZE TABLE復(fù)制表的數(shù)據(jù)部分并重新構(gòu)建索引违孝。其好處在于改進(jìn)了索引內(nèi)的數(shù)據(jù)打包，減少了表空間和磁盤上的碎片苏研。好處因每個(gè)表中的數(shù)據(jù)而異等浊。您可能會(huì)發(fā)現(xiàn)一些人獲得了顯著的收益，而另一些人卻沒(méi)有摹蘑，或者收益會(huì)隨著時(shí)間的推移而減少，直到下一次優(yōu)化表為止轧飞。如果表很大衅鹿，或者正在重建的索引不適合緩沖池，則此操作可能會(huì)很慢过咬。向表添加大量數(shù)據(jù)后的第一次運(yùn)行通常比以后的運(yùn)行要慢得多大渤。

在InnoDB中，如果主鍵很長(zhǎng)(一個(gè)列有一個(gè)很長(zhǎng)的值掸绞，或者幾個(gè)列組成一個(gè)很長(zhǎng)的復(fù)合值)會(huì)浪費(fèi)大量的磁盤空間泵三。一行的主鍵值在指向同一行的所有輔助索引記錄中重復(fù)。

使用VARCHAR數(shù)據(jù)類型而不是CHAR來(lái)存儲(chǔ)可變長(zhǎng)度的字符串或具有許多NULL值的列衔掸。一個(gè)CHAR(N)列總是使用N個(gè)字符來(lái)存儲(chǔ)數(shù)據(jù)烫幕，即使這個(gè)字符串更短或者它的值是NULL。較小的表更適合緩沖池敞映，并減少磁盤I/O较曼。

當(dāng)使用COMPACT行格式(默認(rèn)InnoDB格式)和可變長(zhǎng)度字符集時(shí)，例如utf8或sjis, CHAR(N)列占用可變數(shù)量的空間振愿，但仍然至少N字節(jié)捷犹。

對(duì)于大表弛饭，或者包含大量重復(fù)文本或數(shù)字?jǐn)?shù)據(jù)的表，可以考慮使用COMPRESSED行格式萍歉。將數(shù)據(jù)帶入緩沖池或執(zhí)行全表掃描所需的磁盤I/O更少侣颂。在做出永久決定之前，度量通過(guò)使用COMPRESSED和COMPACT行格式可以實(shí)現(xiàn)的壓縮量枪孩。

二.優(yōu)化InnoDB事務(wù)管理

要優(yōu)化InnoDB事務(wù)處理憔晒，需要在事務(wù)特性的性能開(kāi)銷和服務(wù)器的工作負(fù)載之間找到理想的平衡。例如销凑，如果應(yīng)用程序每秒提交數(shù)千次丛晌，就可能遇到性能問(wèn)題;如果應(yīng)用程序每2-3小時(shí)提交一次，就可能遇到不同的性能問(wèn)題斗幼。

默認(rèn)MySQL設(shè)置AUTOCOMMIT=1可能會(huì)對(duì)繁忙的數(shù)據(jù)庫(kù)服務(wù)器施加性能限制澎蛛。在實(shí)際情況下，通過(guò)發(fā)出SET AUTOCOMMIT=0或START transaction語(yǔ)句蜕窿，將幾個(gè)相關(guān)的數(shù)據(jù)更改操作封裝到一個(gè)事務(wù)中谋逻，然后在完成所有更改后執(zhí)行COMMIT語(yǔ)句。

InnoDB必須在每個(gè)事務(wù)提交時(shí)將日志刷新到磁盤桐经，如果該事務(wù)對(duì)數(shù)據(jù)庫(kù)進(jìn)行了修改毁兆。當(dāng)每個(gè)更改之后都有一個(gè)提交(與默認(rèn)的自動(dòng)提交設(shè)置一樣)時(shí)，存儲(chǔ)設(shè)備的I/O吞吐量將為每秒的潛在操作數(shù)設(shè)置一個(gè)上限阴挣。

另外气堕，對(duì)于只包含一個(gè)SELECT語(yǔ)句的事務(wù)，打開(kāi)AUTOCOMMIT可以幫助InnoDB識(shí)別只讀事務(wù)并優(yōu)化它們畔咧。

避免在插入茎芭、更新或刪除大量行之后執(zhí)行回滾。如果一個(gè)大事務(wù)正在降低服務(wù)器性能誓沸，那么回滾它可能會(huì)使問(wèn)題變得更糟梅桩，可能需要比原始數(shù)據(jù)更改操作多幾倍的時(shí)間來(lái)執(zhí)行。終止數(shù)據(jù)庫(kù)進(jìn)程沒(méi)有幫助拜隧，因?yàn)榛貪L將在服務(wù)器啟動(dòng)時(shí)再次啟動(dòng)宿百。

為了盡量減少這個(gè)問(wèn)題的發(fā)生:

1. 增加buffer pool的大小，以便所有數(shù)據(jù)更改都可以緩存洪添，而不是立即寫入磁盤垦页。
2. 設(shè)置innodb_change_buffering=all以便在插入操作之外緩沖更新和刪除操作。
3. 考慮在大數(shù)據(jù)更改操作期間周期性地發(fā)出COMMIT語(yǔ)句薇组，可能會(huì)將單個(gè)刪除或更新分解為多個(gè)語(yǔ)句外臂，這些語(yǔ)句對(duì)較小的行進(jìn)行操作。

為了避免發(fā)生失控回滾，可以增加緩沖池宋光，使回滾變成cpu綁定的貌矿，并快速運(yùn)行，或者終止服務(wù)器并使用innodb_force_recovery=3重啟罪佳。

對(duì)于默認(rèn)設(shè)置innodb_change_buffering=all逛漫，這個(gè)問(wèn)題預(yù)計(jì)不會(huì)很常見(jiàn)，因?yàn)槟J(rèn)設(shè)置允許將更新和刪除操作緩存在內(nèi)存中赘艳，這使得它們?cè)趫?zhí)行時(shí)更快酌毡，在需要時(shí)回滾時(shí)也更快。請(qǐng)確保在處理具有許多插入蕾管、更新或刪除的長(zhǎng)時(shí)間運(yùn)行的事務(wù)的服務(wù)器上使用此參數(shù)設(shè)置枷踏。

如果您能夠承受在意外退出時(shí)丟失一些最新提交的事務(wù)，那么可以將innodb_flush_log_at_trx_commit參數(shù)設(shè)置為0掰曾。InnoDB嘗試每秒刷新一次日志旭蠕，盡管不能保證刷新。

當(dāng)修改或刪除行時(shí)旷坦，不會(huì)立即物理地刪除行和相關(guān)的撤銷日志掏熬，甚至在事務(wù)提交后也不會(huì)立即刪除。舊數(shù)據(jù)被保留秒梅，直到較早啟動(dòng)或并發(fā)的事務(wù)完成旗芬，以便這些事務(wù)可以訪問(wèn)已修改或已刪除的行以前的狀態(tài)。因此捆蜀，一個(gè)長(zhǎng)時(shí)間運(yùn)行的事務(wù)可以阻止InnoDB清除由不同事務(wù)更改的數(shù)據(jù)疮丛。

當(dāng)在長(zhǎng)時(shí)間運(yùn)行的事務(wù)中修改或刪除行時(shí)，使用READ COMMITTED和REPEATABLE READ隔離級(jí)別的其他事務(wù)如果讀取相同的行辆它，則必須做更多的工作來(lái)重構(gòu)舊的數(shù)據(jù)这刷。

當(dāng)一個(gè)長(zhǎng)期運(yùn)行的事務(wù)修改一個(gè)表時(shí)，來(lái)自其他事務(wù)的對(duì)該表的查詢不會(huì)使用覆蓋索引技術(shù)娩井。通常可以從二級(jí)索引檢索所有結(jié)果列的查詢似袁，而不是從表數(shù)據(jù)中查找適當(dāng)?shù)闹怠?/p>

如果次要索引頁(yè)的PAGE_MAX_TRX_ID太新洞辣，或者次要索引中的記錄被刪除，InnoDB可能需要使用聚集索引來(lái)查找記錄昙衅。

三.優(yōu)化InnoDB只讀事務(wù)

InnoDB可以避免為只讀事務(wù)設(shè)置事務(wù)ID (TRX_ID字段)的開(kāi)銷扬霜。事務(wù)ID只適用于可能執(zhí)行寫操作或鎖定讀操作(如SELECT…FOR UPDATE)的事務(wù)。消除不必要的事務(wù)id可以減少每次查詢或數(shù)據(jù)更改語(yǔ)句構(gòu)造讀視圖時(shí)需要咨詢的內(nèi)部數(shù)據(jù)結(jié)構(gòu)的大小而涉。

InnoDB在以下情況下檢測(cè)只讀事務(wù):

1. 事務(wù)由START transaction READ ONLY語(yǔ)句啟動(dòng)著瓶。在這種情況下，試圖對(duì)數(shù)據(jù)庫(kù)(InnoDB, MyISAM啼县，或其他類型的表)進(jìn)行更改會(huì)導(dǎo)致錯(cuò)誤材原，事務(wù)繼續(xù)處于只讀狀態(tài):

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

您仍然可以在只讀事務(wù)中更改特定于會(huì)話的臨時(shí)表沸久，或者對(duì)它們發(fā)出鎖定查詢，因?yàn)檫@些更改和鎖定對(duì)任何其他事務(wù)都是不可見(jiàn)的余蟹。

2. 自動(dòng)提交設(shè)置是打開(kāi)的卷胯，這樣事務(wù)就保證是單個(gè)語(yǔ)句，組成事務(wù)的單個(gè)語(yǔ)句是一個(gè)“非鎖定”SELECT語(yǔ)句威酒。也就是說(shuō)窑睁，不使用FOR UPDATE或LOCK IN SHARED MODE子句的SELECT。

3. 事務(wù)啟動(dòng)時(shí)沒(méi)有READ ONLY選項(xiàng)葵孤，但是還沒(méi)有執(zhí)行顯式鎖定行的更新或語(yǔ)句担钮。在需要更新或顯式鎖之前，事務(wù)保持只讀模式尤仍。

因此,報(bào)告等操作應(yīng)用程序生成器,您可以調(diào)整一系列InnoDB查詢通過(guò)分組內(nèi)啟動(dòng)事務(wù)只讀和提交,或通過(guò)將autocommit設(shè)置在運(yùn)行SELECT語(yǔ)句之前,或者只是通過(guò)避免任何數(shù)據(jù)變化穿插的查詢語(yǔ)句箫津。

四. 優(yōu)化InnoDB重做日志

考慮以下優(yōu)化重做日志的指導(dǎo)原則:

1. 讓你的重做日志文件變大，甚至和緩沖池一樣大吓著。InnoDB寫滿重做日志文件后鲤嫡，必須在檢查點(diǎn)中將緩沖池中修改過(guò)的內(nèi)容寫入磁盤。小的重做日志文件會(huì)導(dǎo)致很多不必要的磁盤寫操作绑莺。雖然歷史上大的重做日志文件會(huì)導(dǎo)致很長(zhǎng)的恢復(fù)時(shí)間暖眼，但現(xiàn)在恢復(fù)速度快得多，您可以放心地使用大的重做日志文件纺裁。

2. 重做日志文件的大小和數(shù)量通過(guò)innodb_log_file_size和innodb_log_files_in_group配置選項(xiàng)進(jìn)行配置诫肠。有關(guān)修改現(xiàn)有重做日志文件配置的信息，請(qǐng)參見(jiàn)更改重做日志文件的數(shù)量或大小欺缘。

3. 考慮增加日志緩沖區(qū)的大小栋豫。大型日志緩沖區(qū)使大型事務(wù)能夠運(yùn)行，而無(wú)需在事務(wù)提交之前將日志寫入磁盤谚殊。因此丧鸯，如果您有更新、插入或刪除許多行的事務(wù)嫩絮，那么使日志緩沖區(qū)更大可以節(jié)省磁盤I/O丛肢。日志緩沖區(qū)的大小是通過(guò)innodb_log_buffer_size配置選項(xiàng)來(lái)配置的，可以在MySQL 8.0中動(dòng)態(tài)配置剿干。

4. 配置innodb_log_write_ahead_size配置選項(xiàng)蜂怎，避免“read-on-write”。此選項(xiàng)定義重做日志的預(yù)寫塊大小置尔。設(shè)置innodb_log_write_ahead_size與操作系統(tǒng)或文件系統(tǒng)緩存塊大小匹配杠步。當(dāng)重做日志的預(yù)寫塊大小與操作系統(tǒng)或文件系統(tǒng)緩存塊大小不匹配時(shí)，就會(huì)發(fā)生“寫時(shí)讀”。

5. innodb_log_write_ahead_size的取值為InnoDB日志塊大小(2n)的倍數(shù)幽歼。最小值為InnoDB日志文件塊大小(512)朵锣。當(dāng)指定了最小值時(shí)，不會(huì)發(fā)生預(yù)寫试躏。最大值等于innodb_page_size的值猪勇。如果設(shè)置的innodb_log_write_ahead_size大于innodb_page_size，則innodb_log_write_ahead_size會(huì)被截?cái)酁閕nnodb_page_size颠蕴。

6. innodb_log_write_ahead_size相對(duì)于操作系統(tǒng)或文件系統(tǒng)緩存塊大小設(shè)置過(guò)低泣刹，會(huì)導(dǎo)致讀寫。設(shè)置過(guò)高的值可能會(huì)對(duì)日志文件寫入的fsync性能有輕微的影響犀被，因?yàn)闀?huì)同時(shí)寫入多個(gè)塊椅您。

7. MySQL 8.0.11引入了專門的日志寫線程，用于將重做日志記錄從日志緩沖區(qū)寫到系統(tǒng)緩沖區(qū)寡键，并將系統(tǒng)緩沖區(qū)刷新到重做日志文件中掀泳。以前，單個(gè)用戶線程負(fù)責(zé)這些任務(wù)西轩。從MySQL 8.0.22開(kāi)始员舵，你可以使用innodb_log_writer_threads變量來(lái)啟用或禁用日志寫線程。專用日志寫入線程可以提高高并發(fā)系統(tǒng)的性能藕畔，但對(duì)于低并發(fā)系統(tǒng)马僻，禁用專用日志寫入線程可以提供更好的性能。

8. 通過(guò)用戶線程等待刷新重做來(lái)優(yōu)化spin延遲的使用注服。旋轉(zhuǎn)延遲有助于減少延遲韭邓。在低并發(fā)的時(shí)期，減少延遲的優(yōu)先級(jí)可能更低溶弟，并且在這些時(shí)期避免使用旋轉(zhuǎn)延遲可以減少能源消耗女淑。在高并發(fā)性期間，您可能希望避免在旋轉(zhuǎn)延遲上耗費(fèi)處理能力辜御，以便將其用于其他工作鸭你。以下系統(tǒng)變量允許設(shè)置高水位和低水位值，這些值定義了使用自旋延遲的邊界擒权。

9. innodb_log_wait_for_flush_spin_hwm:定義用戶線程在等待刷新重做時(shí)不再旋轉(zhuǎn)的最大平均日志刷新時(shí)間苇本。缺省值是400微秒。

10. innodb_log_spin_cpu_abs_lwm:定義了在等待刷新時(shí)用戶線程不再旋轉(zhuǎn)的最小CPU占用量菜拓。該值表示為CPU核心使用率的總和。例如笛厦，“80”的默認(rèn)值為單個(gè)CPU核的80%纳鼎。在使用多核處理器的系統(tǒng)上，值150表示一個(gè)CPU核的100%使用率加上第二個(gè)CPU核的50%使用率。

11. innodb_log_spin_cpu_pct_hwm:定義在等待刷新時(shí)用戶線程不再旋轉(zhuǎn)的最大CPU使用率贱鄙。該值表示為所有CPU核的總處理能力之和的百分比劝贸。缺省值是50%。例如逗宁，在具有4個(gè)CPU核的服務(wù)器上映九，2個(gè)CPU核的100%使用率是總CPU處理能力的50%。

12. innodb_log_spin_cpu_pct_hwm配置選項(xiàng)尊重處理器親和性瞎颗。例如件甥，如果一個(gè)服務(wù)器有48個(gè)核，但是mysqld進(jìn)程被固定為只有4個(gè)CPU核哼拔，那么其他44個(gè)CPU核將被忽略引有。

五. InnoDB表的批量數(shù)據(jù)加載

當(dāng)將數(shù)據(jù)導(dǎo)入InnoDB時(shí)，關(guān)閉自動(dòng)提交模式倦逐，因?yàn)槊看尾迦攵紩?huì)對(duì)磁盤執(zhí)行日志刷新譬正。要在導(dǎo)入操作期間禁用自動(dòng)提交，請(qǐng)使用SET autocommit和COMMIT語(yǔ)句包圍它:

SET autocommit=0;
... SQL import statements ...
COMMIT;

mysqldump選項(xiàng)--opt創(chuàng)建的轉(zhuǎn)儲(chǔ)文件可以快速導(dǎo)入到InnoDB表中檬姥，即使不使用SET autocommit和COMMIT語(yǔ)句包裝它們曾我。

如果你對(duì)輔助鍵有UNIQUE約束，你可以通過(guò)在導(dǎo)入會(huì)話期間暫時(shí)關(guān)閉唯一性檢查來(lái)加快表導(dǎo)入:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

對(duì)于大表健民，這節(jié)省了大量的磁盤I/O抒巢，因?yàn)镮nnoDB可以使用它的更改緩沖區(qū)來(lái)批量寫入二級(jí)索引記錄。確保數(shù)據(jù)不包含重復(fù)的鍵荞雏。

如果你的表中有FOREIGN KEY約束虐秦，你可以通過(guò)關(guān)閉導(dǎo)入會(huì)話期間的外鍵檢查來(lái)加速表導(dǎo)入:

SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

這個(gè)技巧適用于任何表的插入，而不僅僅是InnoDB表凤优。

當(dāng)對(duì)具有自動(dòng)增量列的表進(jìn)行批量插入時(shí)悦陋，將innodb_autoinc_lock_mode設(shè)置為2(交叉插入)而不是1(連續(xù)插入)。

在執(zhí)行批量插入時(shí)筑辨，按照PRIMARY KEY順序插入行更快俺驶。InnoDB表使用聚集索引，這使得按PRIMARY KEY的順序使用數(shù)據(jù)相對(duì)較快棍辕。對(duì)于不完全適合緩沖池的表暮现，按照PRIMARY KEY順序執(zhí)行批量插入特別重要。

為了在InnoDB FULLTEXT索引中加載數(shù)據(jù)時(shí)獲得最佳性能楚昭，請(qǐng)遵循以下步驟:

1. 在表創(chuàng)建時(shí)定義列FTS_DOC_ID栖袋，類型為BIGINT UNSIGNED NOT NULL，具有唯一索引FTS_DOC_ID_INDEX抚太。例如:

CREATE TABLE t1 (
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT '',
text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

2. 將數(shù)據(jù)加載到表中塘幅。
3. 加載數(shù)據(jù)后創(chuàng)建FULLTEXT索引昔案。

六. 優(yōu)化InnoDB查詢

要調(diào)優(yōu)InnoDB表的查詢，請(qǐng)?jiān)诿總€(gè)表上創(chuàng)建一組適當(dāng)?shù)乃饕缦薄Ｒ韵率荌nnoDB索引的指導(dǎo)原則:

1. 因?yàn)槊總€(gè)InnoDB表都有一個(gè)主鍵(無(wú)論請(qǐng)求與否)踏揣，所以為每個(gè)表指定一組主鍵列，這些列將用于最重要和時(shí)間緊迫的查詢匾乓。

2. 不要在主鍵中指定太多或太長(zhǎng)的列捞稿，因?yàn)檫@些列值會(huì)在每個(gè)輔助索引中重復(fù)。當(dāng)索引包含不必要的數(shù)據(jù)時(shí)拼缝，讀取該數(shù)據(jù)的I/O和緩存該數(shù)據(jù)的內(nèi)存會(huì)降低服務(wù)器的性能和可伸縮性娱局。

3. 不要為每個(gè)列創(chuàng)建單獨(dú)的輔助索引，因?yàn)槊總€(gè)查詢只能使用一個(gè)索引珍促。很少測(cè)試的列或只有幾個(gè)不同值的列上的索引可能對(duì)任何查詢都沒(méi)有幫助铃辖。如果對(duì)同一個(gè)表有許多查詢，并測(cè)試不同的列組合猪叙，則嘗試創(chuàng)建少量的連接索引娇斩，而不是創(chuàng)建大量的單列索引。如果索引包含結(jié)果集所需的所有列(稱為覆蓋索引)穴翩，則查詢可能完全可以避免讀取表數(shù)據(jù)犬第。

4. 如果索引列不能包含任何NULL值，在創(chuàng)建表時(shí)將其聲明為NOT NULL芒帕。當(dāng)優(yōu)化器知道每個(gè)列是否包含NULL值時(shí)歉嗓，它可以更好地確定對(duì)查詢使用哪個(gè)索引最有效。

5. 你可以使用InnoDB只讀事務(wù)的技術(shù)來(lái)優(yōu)化InnoDB表的單查詢事務(wù)背蟆。

七. 優(yōu)化InnoDB DDL操作

對(duì)表和索引的許多DDL操作(CREATE鉴分、ALTER和DROP語(yǔ)句)都可以在線執(zhí)行。

添加二級(jí)索引的在線DDL支持意味著带膀，通過(guò)創(chuàng)建沒(méi)有二級(jí)索引的表志珍，然后在數(shù)據(jù)加載后添加二級(jí)索引，通扯膺叮可以加快創(chuàng)建和加載表及其關(guān)聯(lián)索引的過(guò)程伦糯。

使用TRUNCATE TABLE清空表，而不是DELETE FROM tbl_name嗽元。外鍵約束可以使TRUNCATE語(yǔ)句像常規(guī)的DELETE語(yǔ)句一樣工作敛纲，在這種情況下，像DROP TABLE和CREATE TABLE這樣的命令序列可能是最快的剂癌。

因?yàn)橹麈I是不可或缺的每個(gè)InnoDB表的存儲(chǔ)布局,和改變主鍵的定義涉及重組整個(gè)表,總是設(shè)置主鍵作為CREATE table語(yǔ)句的一部分,和提前計(jì)劃,這樣你不需要改變或刪除主鍵淤翔。

八. 優(yōu)化InnoDB磁盤I/O

如果您遵循SQL操作的數(shù)據(jù)庫(kù)設(shè)計(jì)和調(diào)優(yōu)技術(shù)的最佳實(shí)踐，但由于大量磁盤I/O活動(dòng)佩谷，您的數(shù)據(jù)庫(kù)仍然很慢办铡，請(qǐng)考慮這些磁盤I/O優(yōu)化辞做。如果Unix top工具或Windows任務(wù)管理器顯示工作負(fù)載中的CPU使用率低于70%，則工作負(fù)載可能是磁盤綁定的寡具。

1. 增加緩沖池大小
   當(dāng)表數(shù)據(jù)緩存在InnoDB緩沖池中時(shí)，可以通過(guò)查詢重復(fù)訪問(wèn)它稚补，而不需要任何磁盤I/O童叠。使用innodb_buffer_pool_size選項(xiàng)指定緩沖池的大小。這個(gè)內(nèi)存區(qū)域非常重要课幕，因此通常建議將innodb_buffer_pool_size配置為系統(tǒng)內(nèi)存的50%到75%厦坛。

2. 調(diào)整平齊方法
   在某些版本的GNU/Linux和Unix中，使用Unix的fsync()調(diào)用(InnoDB默認(rèn)使用)和類似的方法將文件刷新到磁盤是非常慢的乍惊。如果數(shù)據(jù)庫(kù)寫性能有問(wèn)題杜秸，可以將innodb_flush_method參數(shù)設(shè)置為O_DSYNC進(jìn)行基準(zhǔn)測(cè)試。

3. 配置fsync閾值
   默認(rèn)情況下润绎，當(dāng)InnoDB創(chuàng)建一個(gè)新的數(shù)據(jù)文件(比如新的日志文件或表空間文件)時(shí)撬碟，文件在被刷新到磁盤之前會(huì)被完全寫入到操作系統(tǒng)緩存中，這可能會(huì)導(dǎo)致一次發(fā)生大量的磁盤寫活動(dòng)莉撇。要從操作系統(tǒng)緩存強(qiáng)制更小的、定期的數(shù)據(jù)刷新，可以使用innodb_fsync_threshold變量定義一個(gè)閾值(以字節(jié)為單位)灶体。當(dāng)達(dá)到字節(jié)閾值時(shí)鲫剿，操作系統(tǒng)緩存的內(nèi)容將被刷新到磁盤。默認(rèn)值0強(qiáng)制執(zhí)行默認(rèn)行為涂佃，即只有在將文件完全寫入緩存后才將數(shù)據(jù)刷新到磁盤励翼。
   在多個(gè)MySQL實(shí)例使用相同存儲(chǔ)設(shè)備的情況下，指定一個(gè)閾值強(qiáng)制更小的定期刷新可能是有益的辜荠。例如汽抚，創(chuàng)建一個(gè)新的MySQL實(shí)例及其相關(guān)的數(shù)據(jù)文件可能會(huì)導(dǎo)致磁盤寫活動(dòng)激增，從而阻礙使用相同存儲(chǔ)設(shè)備的其他MySQL實(shí)例的性能侨拦。配置閾值有助于避免寫入活動(dòng)的激增殊橙。

4. 在Linux上使用帶有原生AIO的noop或deadline I/O調(diào)度程序
   InnoDB使用Linux上的異步I/O子系統(tǒng)(本機(jī)AIO)來(lái)執(zhí)行對(duì)數(shù)據(jù)文件頁(yè)的預(yù)讀和寫請(qǐng)求。此行為由innodb_use_native_aio配置選項(xiàng)控制狱从，該配置選項(xiàng)默認(rèn)啟用膨蛮。對(duì)于本機(jī)AIO, I/O調(diào)度器的類型對(duì)I/O性能的影響更大。通常季研，推薦使用noop和deadline I/O調(diào)度器敞葛。執(zhí)行基準(zhǔn)測(cè)試以確定哪個(gè)I/O調(diào)度器為您的工作負(fù)載和環(huán)境提供了最佳結(jié)果。

5. 在Solaris 10的x86_64架構(gòu)上使用直接I/O
   在Solaris 10 for x86_64架構(gòu)(AMD Opteron)上使用InnoDB存儲(chǔ)引擎時(shí)与涡，建議對(duì)InnoDB相關(guān)的文件使用直接I/O惹谐，以避免InnoDB性能下降持偏。如果要對(duì)整個(gè)UFS文件系統(tǒng)使用直接I/O，用于存儲(chǔ)innodb相關(guān)的文件氨肌，請(qǐng)使用forcedirectio選項(xiàng)掛載它;看到mount_ufs(1米)鸿秆。(Solaris 10/x86_64上的默認(rèn)設(shè)置是不使用此選項(xiàng)。)設(shè)置innodb_flush_method = O_DIRECT怎囚，可以使innodb_flush_method只對(duì)InnoDB文件進(jìn)行直接I/O操作卿叽，而不對(duì)整個(gè)文件系統(tǒng)進(jìn)行直接I/O操作。使用這個(gè)設(shè)置恳守，InnoDB調(diào)用directio()來(lái)代替fcntl()來(lái)處理I/O到數(shù)據(jù)文件(而不是I/O到日志文件)考婴。

6. 在Solaris 2.6或更高版本中，為數(shù)據(jù)和日志文件使用原始存儲(chǔ)
   當(dāng)使用InnoDB存儲(chǔ)引擎,通過(guò)innodb_buffer_pool_size價(jià)值任何Solaris 2.6的發(fā)布,和任何平臺(tái)(sparc / x86 / x64 / amd64),進(jìn)行基準(zhǔn)InnoDB數(shù)據(jù)文件和日志文件原始設(shè)備或在一個(gè)單獨(dú)的直接I / O UFS文件系統(tǒng),使用forcedirectio掛載選項(xiàng)如前所述催烘。(如果您希望對(duì)日志文件進(jìn)行直接I/O沥阱，那么必須使用掛載選項(xiàng)，而不是設(shè)置innodb_flush_method伊群。)Veritas文件系統(tǒng)VxFS的用戶應(yīng)該使用convosync=direct mount選項(xiàng)考杉。
   不要將其他MySQL數(shù)據(jù)文件(如MyISAM表)放在直接I/O文件系統(tǒng)上≡谄瘢可執(zhí)行文件或庫(kù)不能放在直接I/O文件系統(tǒng)上奔则。

7. 使用其他存儲(chǔ)設(shè)備
   可以使用其他存儲(chǔ)設(shè)備來(lái)設(shè)置RAID配置。
   InnoDB表空間的數(shù)據(jù)文件和日志文件也可以放在不同的物理磁盤上蔽午。

8. 考慮non-rotational存儲(chǔ)
   非旋轉(zhuǎn)存儲(chǔ)通常為隨機(jī)I/O操作提供更好的性能;以及用于順序I/O操作的旋轉(zhuǎn)存儲(chǔ)易茬。在跨旋轉(zhuǎn)和非旋轉(zhuǎn)存儲(chǔ)設(shè)備分布數(shù)據(jù)和日志文件時(shí)，請(qǐng)考慮主要在每個(gè)文件上執(zhí)行的I/O操作的類型及老。

隨機(jī)的面向I/ o的文件通常包括每個(gè)表的文件和一般的表空間數(shù)據(jù)文件抽莱、undo表空間文件和臨時(shí)表空間文件。順序I/ o導(dǎo)向的文件包括InnoDB系統(tǒng)表空間文件(由于MySQL 8.0.20之前的doublewrite緩沖和change緩沖)骄恶，MySQL 8.0.20引入的doublewrite文件食铐，以及日志文件，如二進(jìn)制日志文件和重做日志文件僧鲁。

1. 使用非旋轉(zhuǎn)存儲(chǔ)時(shí)虐呻，請(qǐng)檢查以下配置選項(xiàng)的設(shè)置:
   1)innodb_checksum_algorithm
   crc32選項(xiàng)使用更快的校驗(yàn)和算法，推薦用于快速存儲(chǔ)系統(tǒng)寞秃。
   2)innodb_flush_neighbors
   為旋轉(zhuǎn)存儲(chǔ)設(shè)備優(yōu)化I/O斟叼。禁用非旋轉(zhuǎn)存儲(chǔ)或混合旋轉(zhuǎn)和非旋轉(zhuǎn)存儲(chǔ)。默認(rèn)關(guān)閉春寿。
   3)innodb_idle_flush_pct
   允許在空閑期間限制頁(yè)面刷新朗涩，這有助于延長(zhǎng)非旋轉(zhuǎn)存儲(chǔ)設(shè)備的壽命。在MySQL 8.0.18中介紹绑改。
   4)innodb_io_capacity
   對(duì)于低端非旋轉(zhuǎn)存儲(chǔ)設(shè)備谢床，缺省值200就足夠了兄一。對(duì)于更高端的總線連接設(shè)備，請(qǐng)考慮更高的設(shè)置识腿，例如1000出革。
   5)innodb_io_capacity_max
   默認(rèn)值2000用于使用非旋轉(zhuǎn)存儲(chǔ)的工作負(fù)載。對(duì)于一個(gè)高端的渡讼，總線連接的非旋轉(zhuǎn)存儲(chǔ)設(shè)備蹋盆，考慮一個(gè)更高的設(shè)置，如2500硝全。
   6)innodb_log_compressed_pages
   如果重做日志在非旋轉(zhuǎn)存儲(chǔ)上，請(qǐng)考慮禁用此選項(xiàng)以減少日志記錄楞抡。參見(jiàn)禁用壓縮頁(yè)面的日志記錄伟众。
   7)innodb_log_file_size
   如果重做日志在非旋轉(zhuǎn)存儲(chǔ)上，則配置此選項(xiàng)以最大化緩存和寫入組合召廷。
   8)innodb_page_size
   考慮使用與磁盤內(nèi)部扇區(qū)大小匹配的頁(yè)大小凳厢。早期的SSD設(shè)備通常有4KB的扇區(qū)大小。一些較新的設(shè)備有16KB的扇區(qū)大小竞慢。InnoDB頁(yè)面的默認(rèn)大小是16KB先紫。保持頁(yè)面大小接近存儲(chǔ)設(shè)備塊大小可以最小化重寫到磁盤的未更改數(shù)據(jù)量。
   9)binlog_row_image
   如果二進(jìn)制日志是非旋轉(zhuǎn)存儲(chǔ)并且所有表都有主鍵筹煮，請(qǐng)考慮將此選項(xiàng)設(shè)置為最小以減少日志記錄遮精。

確保為您的操作系統(tǒng)啟用TRIM支持。它通常是默認(rèn)啟用的败潦。

9. 增加I/O容量以避免積壓
   如果由于InnoDB檢查點(diǎn)操作導(dǎo)致吞吐量周期性下降本冲，可以考慮增加innodb_io_capacity配置選項(xiàng)的值。較高的值會(huì)導(dǎo)致更頻繁的沖洗劫扒，避免可能導(dǎo)致吞吐量下降的工作積壓檬洞。

10. 如果刷新沒(méi)有落后，則會(huì)降低I/O容量
    如果系統(tǒng)沒(méi)有在InnoDB刷新操作上落后沟饥，可以考慮降低innodb_io_capacity配置選項(xiàng)的值添怔。通常情況下，您應(yīng)該盡可能地降低該選項(xiàng)的值贤旷，但不要低到像前面提到的那樣導(dǎo)致吞吐量周期性下降广料。在一個(gè)典型的場(chǎng)景中，你可以降低選項(xiàng)的值遮晚，你可能會(huì)在SHOW ENGINE INNODB STATUS的輸出中看到這樣的組合:

歷史名單長(zhǎng)度低性昭，幾千以下。
插入緩沖區(qū)合并靠近插入的行县遣。
緩沖池中修改的頁(yè)面始終低于緩沖池的innodb_max_dirty_pages_pct糜颠。(在服務(wù)器不進(jìn)行批量插入時(shí)進(jìn)行測(cè)量;在批量插入期間汹族，被修改的頁(yè)面百分比通常會(huì)顯著上升。)
日志序號(hào)-最后的檢查點(diǎn)小于7/8其兴，理想情況下小于InnoDB日志文件總大小的6/8顶瞒。

11. 在Fusion-io設(shè)備上存儲(chǔ)系統(tǒng)表空間文件
    通過(guò)將包含doublewrite存儲(chǔ)區(qū)域的文件存儲(chǔ)在支持原子寫的Fusion-io設(shè)備上，您可以利用doublewrite緩沖區(qū)相關(guān)的I/O優(yōu)化元旬。(在MySQL 8.0.20之前榴徐，doublewrite緩沖區(qū)存儲(chǔ)在系統(tǒng)表空間數(shù)據(jù)文件中。從MySQL 8.0.20開(kāi)始匀归，存儲(chǔ)區(qū)域位于doublewrite文件中坑资。參見(jiàn)15.6.4節(jié)，“Doublewrite Buffer”穆端。)當(dāng)doublewrite存儲(chǔ)區(qū)域文件放置在支持原子寫的Fusion-io設(shè)備上時(shí)袱贮，自動(dòng)關(guān)閉doublewrite緩沖區(qū)，所有數(shù)據(jù)文件都使用Fusion-io原子寫体啰。該特性僅支持Fusion-io硬件攒巍，僅支持Linux下的Fusion-io nvvmfs。為了充分利用這個(gè)特性荒勇，建議使用O_DIRECT的innodb_flush_method設(shè)置柒莉。

12. 禁用壓縮頁(yè)面的日志記錄
    當(dāng)使用InnoDB表壓縮特性時(shí)，當(dāng)對(duì)壓縮數(shù)據(jù)進(jìn)行更改時(shí)沽翔，重新壓縮頁(yè)面的映像會(huì)被寫入重做日志兢孝。這種行為由innodb_log_compressed_pages控制，默認(rèn)情況下是啟用的搀擂，以防止在恢復(fù)過(guò)程中使用不同版本的zlib壓縮算法時(shí)發(fā)生損壞西潘。如果你確定zlib版本不會(huì)改變，禁用innodb_log_compressed_pages來(lái)減少修改壓縮數(shù)據(jù)的工作負(fù)載的重做日志生成哨颂。

九. 優(yōu)化InnoDB配置變量

不同的設(shè)置最適合于具有輕喷市、可預(yù)測(cè)負(fù)載的服務(wù)器，而不是始終幾乎滿負(fù)荷運(yùn)行的服務(wù)器威恼，或者經(jīng)歷高活動(dòng)峰值的服務(wù)器品姓。

因?yàn)镮nnoDB存儲(chǔ)引擎會(huì)自動(dòng)執(zhí)行許多優(yōu)化，所以很多性能調(diào)優(yōu)任務(wù)都涉及監(jiān)控以確保數(shù)據(jù)庫(kù)運(yùn)行良好箫措，以及在性能下降時(shí)更改配置選項(xiàng)腹备。

可以執(zhí)行的主要配置步驟包括:

1. 控制數(shù)據(jù)更改操作的類型，InnoDB會(huì)為這些更改的數(shù)據(jù)進(jìn)行緩沖斤蔓，以避免頻繁的小磁盤寫操作植酥。參見(jiàn)配置更改緩沖。因?yàn)槟J(rèn)是緩沖所有類型的數(shù)據(jù)更改操作，所以只有在需要減少緩沖量時(shí)才更改此設(shè)置友驮。

2. 使用innodb_adaptive_hash_index選項(xiàng)打開(kāi)和關(guān)閉自適應(yīng)散列索引特性漂羊。

3. 設(shè)置InnoDB進(jìn)程并發(fā)線程數(shù)的限制，如果上下文切換是一個(gè)瓶頸卸留。innodb_thread_concurrency

4. InnoDB通過(guò)預(yù)讀操作來(lái)控制預(yù)取的數(shù)量走越。當(dāng)系統(tǒng)有未使用的I/O容量時(shí)，更多的預(yù)讀可以提高查詢的性能耻瑟。在負(fù)載沉重的系統(tǒng)上旨指，過(guò)多的預(yù)讀可能會(huì)導(dǎo)致周期性的性能下降。innodb_read_ahead_threshold.

5. 如果您有一個(gè)未被默認(rèn)值充分利用的高端I/O子系統(tǒng)喳整，那么將增加用于讀或?qū)懖僮鞯暮笈_(tái)線程的數(shù)量谆构。innodb_read_io_threads and innodb_write_io_threads .

6. 控制InnoDB在后臺(tái)執(zhí)行多少I/O。如果您觀察到周期性的性能下降框都，您可以縮減此設(shè)置低淡。innodb_io_capacity .

7. 控制算法，決定InnoDB何時(shí)執(zhí)行某些類型的后臺(tái)寫操作瞬项。該算法適用于某些類型的工作負(fù)載，但不適用于其他類型的工作負(fù)載何荚，因此如果您觀察到周期性的性能下降囱淋，可以禁用此特性。innodb_page_cleaners .

8. 利用多核處理器及其緩存內(nèi)存配置餐塘，以最小化上下文切換的延遲妥衣。innodb_spin_wait_delay .

9. 防止一次性操作(如表掃描)干擾InnoDB緩存中頻繁訪問(wèn)的數(shù)據(jù)。innodb_old_blocks_pct .

10. 將日志文件調(diào)整到對(duì)可靠性和崩潰恢復(fù)有意義的大小戒傻。InnoDB的日志文件通常都很小税手，以避免崩潰后啟動(dòng)時(shí)間過(guò)長(zhǎng)。MySQL 5.5中引入的優(yōu)化加快了崩潰恢復(fù)過(guò)程中的某些步驟需纳。特別是芦倒，由于改進(jìn)了內(nèi)存管理算法，掃描重做日志和應(yīng)用重做日志的速度更快不翩。如果您人為地保持日志文件的小兵扬，以避免長(zhǎng)時(shí)間的啟動(dòng)時(shí)間，那么現(xiàn)在可以考慮增加日志文件的大小口蝠，以減少由于重做日志記錄回收而產(chǎn)生的I/O器钟。

11. 為InnoDB緩沖池配置實(shí)例的大小和數(shù)量，這對(duì)于具有多gb緩沖池的系統(tǒng)尤為重要妙蔗。innodb_buffer_pool_instances .

12. 增加并發(fā)事務(wù)的最大數(shù)量傲霸，這極大地提高了最繁忙數(shù)據(jù)庫(kù)的可伸縮性。innodb_rollback_segments .

13. 將清除操作(一種垃圾收集)移動(dòng)到后臺(tái)線程中。要有效地度量此設(shè)置的結(jié)果昙啄，請(qǐng)首先調(diào)優(yōu)其他I/ o相關(guān)和線程相關(guān)的配置設(shè)置穆役。innodb_purge_threads .

14. 減少InnoDB在并發(fā)線程之間的切換量，使繁忙服務(wù)器上的SQL操作不會(huì)排隊(duì)并形成“阻塞”跟衅。為innodb_thread_concurrency選項(xiàng)設(shè)置一個(gè)值孵睬，對(duì)于高性能的現(xiàn)代系統(tǒng)，最高設(shè)置為32伶跷。將innodb_concurrency_tickets選項(xiàng)的值增加到5000左右掰读。這種選項(xiàng)的組合為InnoDB進(jìn)程的線程數(shù)量設(shè)置了上限，并允許每個(gè)線程在被換出之前做大量的工作叭莫，這樣等待線程的數(shù)量保持在較低的水平蹈集，操作可以在不過(guò)度上下文切換的情況下完成。

十. 優(yōu)化InnoDB系統(tǒng)與許多表

如果你配置了非持久性優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)(非默認(rèn)配置)雇初，InnoDB會(huì)在啟動(dòng)后第一次訪問(wèn)一個(gè)表時(shí)計(jì)算索引基數(shù)值拢肆，而不是將這些值存儲(chǔ)在表中。對(duì)于將數(shù)據(jù)劃分到多個(gè)表的系統(tǒng)靖诗，這個(gè)步驟可能會(huì)花費(fèi)大量時(shí)間郭怪。因?yàn)檫@個(gè)開(kāi)銷只適用于初始的表打開(kāi)操作，為了“預(yù)熱”一個(gè)表以供以后使用刊橘，可以在啟動(dòng)后立即訪問(wèn)它鄙才，通過(guò)發(fā)出一個(gè)語(yǔ)句，例如SELECT 1 FROM tbl_name LIMIT 1促绵。

默認(rèn)情況下攒庵，優(yōu)化器統(tǒng)計(jì)信息被持久化到磁盤，這是通過(guò)innodb_stats_persistent配置選項(xiàng)啟用的败晴。

參考:

1. https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb.html