適時的使用 OPTIMIZE TABLE 語句來重組表，壓縮浪費的表空間。這是在其它優化技術不可用的情況下最直接的方法。OPTIMIZE TABLE 語句通過拷貝表資料並重建表索引，使得索引資料更加緊湊，減少空間碎片。語句的執行效果會因表的不同而不同。過大的表或者過大的索引及初次新增大量資料的情況下都會使得這一操作變慢。
InnoDB表，如果主鍵過長（長資料列做主鍵，或者多個列組合做主鍵）會浪費很多空間。同時，二級索引也包含主鍵。這種情況，可以考慮建立自增列作為主鍵，或者使用字首索引。
對於需要儲存長度不定或者包含很多NULL值的字串列，使用 VARCHAR 代替 CHAR 。在小表應用上，快取使用及磁碟 I/O 消耗會更小。
對於包含大量重複文字或者數位的大表，可以考慮採用壓縮的行格式儲存。這樣資料載入會減少對快取及 I/O 的需求。在使用壓縮行格式前，需要考慮壓縮行格式 COMPRESSED 和的不同效能影響。
優化 InnoDB 事務處理，主要需要找到事務特性和伺服器負載間的某個平衡點。例如，一秒需要提交幾千事務的，或者每隔2-3個小時提交一次事務的不同應用表現。
MySQL 的預設設定 AUTOCOMMIT=1 會限制繁忙資料庫的效能。如果可以的話，可以在應用中使用 SET AUTOCOMMIT=0 或者 START TRANSACTION ，然後將多個相關的資料變更操作新增到同一事務中，然後執行 COMMIT 語句來提交事務，提交資料變更。
InnoDB 對於引發資料庫變更的操作，必須將其進行紀錄檔刷盤。
對於只包含 SELECT 語句的事務，啟用 AUTOCOMMIT ，使得 InnoDB 能夠識別唯讀事務，然後進行相應的優化。
避免對巨量資料量操作插入，更新和刪除之後的回滾操作。如果一個大的事務拖慢了伺服器，那麼回滾將是伺服器效能變得更糟。可以分批次處理巨量資料量操作。通過殺程序方式終止的回滾操作會在伺服器啟動時重新啟動。
可以通過如下策略減少此類問題發生：
增大快取，避免頻繁磁碟I/O。
設定 innodb_change_buffering=all，這樣 update 和 delete 操作也會和 insert 一樣進行快取，回滾也更快。
手動commit，分割巨量資料操作。
為了避免時空的回滾。增大快取，使得回滾程序可以應用到最大的資源以便快速執行。或者殺掉回滾程序，然後使用innodb_force_recovery=3選項重啟。
對於較多執行耗時inserts, updates, 及 deletes 操作的伺服器，確保innodb_change_buffering=all開啟。
InnoDB 如會每秒刷盤一次紀錄檔，如果可以承受最新事務崩潰的資料損失，可以設定innodb_flush_log_at_trx_commit = 0。雖然紀錄檔的刷盤操作也不是保證的，同時也可以設定innodb_support_xa = 0，減少磁碟和二進位制紀錄檔的同步操作。
Note
innodb_support_xa 已被棄用，將來版本會被移除。MySQL 5.7.10版本，InnoDB XA事務的兩階段提交是預設支援的，不能設定禁用innodb_support_xa。
行修改或刪除後，行資料及 undo logs 在物理上並沒有立刻被變更。即使在事務立刻提交後。舊資料會保持直到之前啟動的事務或者並行執行的事務完成後。這樣，這些事務可以一直存取到相關的舊資料。所以耗時的事務會阻止 InnoDB 清除其它相關事務的資料。
如果一個耗時的事務修改或者刪除了某些行。那麼其它使用這些資料的事務，如果事務級別設定在READ COMMITTED 或者 REPEATABLE READ 級別，則需要額外的處理來重建舊資料。
當一個耗時的事務修改了某個表，其它使用此表的事務將不會使用覆蓋索引。如果二級索引包含比較新的PAGE_MAX_TRX_ID，或者某些記錄被標記為已刪除，InnoDB 可能需要使用聚簇索引來查詢相應的記錄。
覆蓋索引查詢（使用二級索引即可獲得所需的資料，而不需要存取表資料）
InnoDB 可以避免給唯讀事務賦 transaction ID (TRX_ID )。事務ID只對執行寫操作，或者含鎖讀操作，如 SELECT ... FOR UPDATE有用。去除不必要的事務ID，有助於減少每次讀寫操作必須存取的內部資料結構大小。
InnoDB 在以下情景能夠識別唯讀操作：
事務以語句 START TRANSACTION READ ONLY 開始，這種情況下，資料變更操作會引發錯誤，事務仍會以唯讀性質執行：
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
對於事務中的臨時表可以進行任何操作。
autocommit = on，並且事務只包含一個語句，且語句為沒有使用FOR UPDATE 或者 LOCK IN SHARED MODE 的SELECT 語句。
事務以READ ONLY 選項開始。
這樣，對於讀繁忙的應用，如報表應用，可以將一系列的查詢語句綜合到一個唯讀的事務中，或者在執行查詢前設定 autocommit = on，或者在應用中避免將變更操作和查詢操作相互影響。.
可以考慮遵循以下優化指引：
確保重做紀錄檔足夠大，即使和快取池（buffer pool）一樣大。當 InnoDB 寫滿 redo log 時，伺服器會基於一個檢查點（checkpoint）就會將紀錄檔中的變更內容寫到磁碟。如果 redo log 檔案過小，那麼就會引發伺服器頻繁的寫盤。雖然之前，設定過大的 redo log 會引起恢復時間的過長，但是現在，恢復機制已經在速度上有很大的優化，因此不用再考慮此因素。
檔案的大小和數量可以使用： innodb_log_file_size 和 innodb_log_files_in_group 進行設定。
可以考慮增大紀錄檔快取（log buffer）。大的紀錄檔快取可以容納更大的事務執行，避免不必要的寫盤操作。設定變數：innodb_log_buffer_size 。
設定 innodb_log_write_ahead_size 變數以避免 「read-on-write」（就是當修改的位元組不足一個洗系統 block時，需要將整個 block 讀進記憶體，修改對應的位置，然後再寫進去；如果我們以 block 為單位來寫入的話，直接完整覆蓋寫入即可）。這個設定定義了 redo log 的 write-ahead 塊大小。
設定innodb_log_write_ahead_size 的大小以匹配作業系統或者檔案系統的快取塊大小。
Read-on-write 的產生是因為在 write-ahead 塊大小和作業系統或者檔案系統的快取塊大小不匹配的情況下，redo log 塊無法完全的寫入到作業系統，或者檔案系統引起的。
innodb_log_write_ahead_size 的值可以設定為 InnoDB 紀錄檔檔案塊大小的倍數(2n)。最小的值為(512)。設定為最小值時 Write-ahead 不會發生。最大值為 innodb_page_size 。如果設定的值大於innodb_page_size，那麼伺服器會使用innodb_page_size值。
innodb_log_write_ahead_size 值設定的太小，會導致 read-on-write；設定過大，則會影響 fsync 效能，因為一次需要些多個資料塊。
快速插入通用指引：
匯入資料時，關閉 autocommit 模式，避免每次行插入導致的紀錄檔刷盤。在執行開始及結束使用 SET AUTOCOMMIT 及 COMMIT 語句：
SET autocommit=0;
...SQLimport statements ...
COMMIT;
mysqldump 選項 --opt （預設啟用）會建立 dump 轉儲 檔案，以執行快速資料匯入，避免將所有的資料載入記憶體引發問題。即使不使用SET autocommit 和 COMMIT。
如果在二級索引鍵上有 UNIQUE 限制，可以在載入時暫時關閉此檢查：
SET unique_checks=0;
...SQLimport statements ...
SET unique_checks=1;
對於較大的表，此操作可以節省大量的磁碟 I/O，因為 InnoDB 可以使用它的 change buffer（change buffer 的主要目的是將對二級索引的資料操作快取下來，以此減少二級索引的隨機IO，並達到操作合併的效果）來批次寫二級索引記錄。確保資料不包含重複鍵。
如果表鍵包含 FOREIGN KEY 限制。可以再匯入期間關閉此限制。
使用批次多行插入，以減少不必要使用者端伺服器間通訊：
SET foreign_key_checks=0;
...SQLimport statements ...
SET foreign_key_checks=1;
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
適用於任何型別表。
當批次插入涉及自增列時，設定 innodb_autoinc_lock_mode = 2 （預設1，0：traditional；1：consecutive；2：interleaved）。
以主鍵的順序進行批次插入會更快。InnoDB 表主鍵索引為聚簇索引（clustered index, 以主鍵的順序存取會很快）。特別是對於無法完全載入快取的大表。
全文索引匯入：
表建立時定義新列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=latin1;CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
載入資料。
資料載入後，在相應的列上建立全文索引。
Note
在 innodb 儲存引擎中，為了支援全文檢索，必須有一個列與 word 進行對映，在 innodb 中這個列被命名為FTS_DOC_ID，其型別必須為 BIGINT UNSIGNED NOT NULL,並且 innodb 儲存引擎會在該列上加上一個名為FTS_DOC_ID_INDEX 的唯一索引。上述操作由 innodb 儲存引擎自己完成，使用者也可以在建立表時手動新增，主要對應的約束條件。
常規的索引是檔案到關鍵詞的對映：檔案——>關鍵詞
倒排索引是關鍵詞到檔案的對映：關鍵詞——>檔案
全文索引通過關鍵字找到關鍵字所在檔案，可以提高查詢效率
建立適當的索引以優化查詢，通用指引如下：
將關鍵查詢最常用的的列包含進表主鍵中。
主鍵列不要使用過多的列或者過長的列。因為二級索引包含主鍵，過大的主鍵會造成磁碟I/O及記憶體的浪費。
不要在每個列上建立二級索引，一個查詢只能使用一個索引。對於極少使用的列及列選擇性不大的列建立索引對於查詢優化不會有太大幫助。如果針對一個表的查詢非常多，則需要找到能夠有助於最多查詢的多列主鍵。如果索引列能夠覆蓋所需要查詢的資料列，那麼就可以只使用索引進行資料查詢，而不需要從表中獲取資料。
如果某一列的資料不能為NULL，那麼在建立表的時候將其生命為 NOT NULL 。優化器以此可以更高的決定最優使用索引。
可以針對但查詢事務進行相應的優化。
許多DDL操作，如表和索引的(CREATE, ALTER, 及DROP 語句) 可以線上執行。
使用 TRUNCATE TABLE 代替 DELETE FROM tbl_name 來清空表，外來鍵限制可以使得 TRUNCATE 語句如普通的 DELETE 語句般操作。這種情景下，一系列如 DROP TABLE 及 CREATE TABLE 語句會執行的很快。
因為主鍵InnoDB表的儲存結構是高度整合的，主鍵的變更會引起整張表的重構。最好將主鍵定義包含在表建立語句中，避免不必要的後期更改。
如果資料庫的設計及 sql 操作優化都遵循了最佳實踐，資料庫依然因為 I/O 負載而反應非常慢，那麼就需要針對 I/O 進行專門的優化。可以通過 Unix 的 top 工具，或者 Windows 的工作管理員來檢視工作負載，如果低 於70%，那麼負載則主要在磁碟。
InnoDB 快取中的資料存取不需要磁碟I/O，使用innodb_buffer_pool_size 設定。建議設定為系統記憶體的 50 ~ 75%。
某些版本 GNU/Linux 系統，使用fsync() 或者相關方法進行刷盤時，速度會非常慢。這時，如果影響到資料庫效能，那麼可以通過設定innodb_flush_method = O_DSYNC 來變更刷盤策略。
InnoDB 在 Linux 系統上使用非同步 I/O 子系統（本地AIO）通過預讀和寫請求來執行資料檔案讀寫。設定變數innodb_use_native_aio 預設啟用。磁碟排程策略對本地 AIO 影響比較大。通常建議設定為 noop 或者 deadline。
Non-rotational 儲存適用於隨機讀寫；rotational 儲存相反適用於順序讀寫。不同的儲存裝置對資料及紀錄檔的操作型別不同。
資料庫隨機讀寫類檔案包括：file-per-table 和 general tablespace 資料檔案, undo tablespace檔案和temporary tablespace 檔案。順序讀寫類檔案包括：InnoDB system tablespace 檔案(基於 doublewrite buffering and change buffering) 及紀錄檔檔案（ binary log 檔案和redo log 檔案等）。
使用 non-rotational 儲存時，需要對以下設定進行優化：
innodb_checksum_algorithm：crc32 演演算法使用了一種更快的一致性檢查演演算法，對於高速儲存裝置，推薦使用。
innodb_flush_neighbors：針對rotational儲存裝置優化。對於non-rotational裝置或者混用情景，則需禁用。
innodb_io_capacity：預設的200設定對於低端non-rotational儲存裝置已經足夠。其它，酌情設定。
innodb_io_capacity_max：預設2000 針對non-rotational 儲存。
innodb_log_compressed_pages：如果redo logs儲存在non-rotational裝置，可以開率禁用詞選項來減少紀錄檔。
innodb_log_file_size：如果redo logs 儲存在non-rotational 儲存裝置，設定此選項最大讀寫快取。
innodb_page_size：設定此值以匹配磁碟internal sector size。早期的SSD裝置為4KB，一些新版本的SSD能夠支援到16KB。預設的額InnoDB 也大小為16KB。儘量使得資料庫頁大小和儲存裝置的塊大小接近，減少無法一次寫入磁碟的資料大小。
binlog_row_image：binary logs 儲存在non-rotational 裝置情況下，如果所有的表都有主鍵，那麼可以將此變數設定為最小來減少紀錄檔。
如果吞吐量會因為檢測點操作而不間斷的降低，那麼可以開率增加 innodb_io_capacity 的值。值越大，資料庫刷盤頻率會增大，從而避免了因為 backlog 的操作帶來的吞吐量的影響。
如果系統能夠滿足 InnoDB 刷盤操作。可以考慮減小innodb_io_capacity 設定。通常需要將此變數儘量設定低一些。（SHOW ENGINE INNODB STATUS）
如果使用支援原子寫的 Fusion-io 裝置儲存系統表空間檔案(「ibdata files」) ，那麼可以對 doublewrite buffer-related I/O進行相應的優化。這種情況下，會自動使用 Fusion-io 裝置的原子寫替代 doublewrite buffering (innodb_doublewrite)進行資料的讀寫。這種特性只支援 Fusion-io 硬體裝置及 Fusion-io NVMFS Linux 應用。可以通過變數 innodb_flush_method = O_DIRECT 進行設定。
Note
設定是全域性性的，影響所有裝置上的資料讀寫。
使用 InnoDB 表壓縮特性時，重新壓縮的圖片資料頁，如果資料有變化，則會寫入 redo log。設定變數innodb_log_compressed_pages 預設啟用，防止資料庫恢復期間，因為 zlib 演演算法的變化引發資料庫崩潰。如果可以確認 zlib 版本不會發生變化，那麼可以關閉 innodb_log_compressed_pages 變數來減少重壓縮產生的 redo log 負載。