1、InnoDB change buffer設置 Change buffer是作為buffer pool中的一部分存在。 Innodb_change_buffering參數緩存所對應的操作：(update會被認為是delete+insert) all: 默認值，緩存insert, delete, purges操作 none: 不緩存 inserts: 緩存insert操作 deletes: 緩存delete操作 changes: 緩存insert和delete操作 purges: 緩存后臺執行的物理刪除操作 innodb_change_buffer_max_size參數配置change buffer在buffer pool中所占的最大百分比，默認是25%，最大可以設置為50%。當MySQL實例中有大量的修改操作時，要考慮增大innodb_change_buffer_max_size。 2、InnoDB線程并發度配置 InnoDB利用操作系統的線程技術達到多線程實現。 Innodb_thread_concurrency參數限制同時執行的線程數。默認值是0代表沒有限制。 Innodb_thread_sleep_delay參數確定 3、InnoDB后臺IO線程配置 通過配置innodb_read_io_threads和innodb_write_io_threads參數來指定后臺讀和寫數據頁的線程的個數，默認值是4，容許的取值范圍是1-64。 ysql> show engine innodb status\G 4、使用Linux異步IO InnoDB在Linux平臺使用異步IO子系統完成數據文件頁的讀寫請求，可以通過innodb_user_native_aio參數控制，默認是開啟狀態，并且需要libaio系統庫支持。 5、InnoDB主線程配置 InnoDB的主線程在后臺承擔了諸多的任務，絕大多數是和IO操作相關的，比如將buffer pool中的修改后的數據刷新的磁盤文件中。 Innodb_io_capacity參數設置了InnoDB的整體IO能力。該參數應該被設置為等同于操作系統每秒的IO操作數量。該參數可以設置為100及以上的任意數值，默認值是200。其中設置為100相當于7200RPM的磁盤性能。 6、InnoDB purge配置 InnoDB的purge操作是一類垃圾回收操作，是由一個或多個獨立線程自動執行。通過innodb_purge_threads參數設置purge線程的數量，如果DML操作比較復雜且涉及到多個表時，則可以考慮增加此值，最大可以設置為32。事務被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread來回收已經使用并分配的undo頁. 7、InnoDB 優化器統計信息配置 Innodb表的優化器統計信息分為永久和非永久兩種。 永久的優化器統計信息即使是服務器重啟的情況下也會存在，其用來選出更優的執行計劃以便提供更好的查詢性能。 通過配置innodb_stats_auto_recalc參數來控制統計信息是否在表發生巨大變化（超過10%的行）之后是否自動更新，但由于自動更新統計信息本身是異步的，所以有時未必能馬上更新，這是可以執行analyze table語句來同步更新統計信息。 8、Create table和alter table語句中的Stats_persistent, stats_auto_recalc, stats_sample_pages子句可用來配置單個表的優化器統計信息規則 Stats_persistent用來指定是否對此表開啟永久統計資料，1代表開啟，0代表不開啟。當開啟之后，可以執行analyze table命令來收集統計資料。 Stats_auto_recalc表示是否自動對表的永久統計資料進行重新計算，默認值和全局參數innodb_stats_auto_recalc一致。1代表當表中數據10%以上更新時重新計算，0代表不自動更新，而是通過analyze table命令重新計算 Stats_sample_pages表示當計算索引列的統計資料是需要的索引頁的樣本數量 CREATE TABLE `t1` ( `id` int(8) NOT NULL auto_increment, `data` varchar(255), `date` datetime, PRIMARY KEY (`id`), INDEX `DATE_IX` (`date`) ) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25; 優化器永久統計資料數據在系統表mysql.innodb_table_stats和mysql.innodb_index_stats表中存儲，這兩個表中有個字段last_update可以用來判斷統計信息最后更改時間。這兩個表的數據也可以被手工更改。當手工更改完數據之后，要執行flush table 表名命令來重新load此表的統計資料。innodb_table_stats表中每個目標表一行記錄，而innodb_index_stats表中每個索引會有多條記錄! Innodb_table_stats表結構：  Innodb_index_stats表結構：  9、優化器統計信息會被存儲在磁盤上，通過設置innodb_stats_persistent=ON參數（默認）。 ? MySQL的查詢優化器會基于評估好的統計資料選擇合適的索引參與到執行計劃中，而類似analyze table的語句會從索引中隨機選取數據頁參與到每個索引的基數評估中。而參數innodb_stats_persistent_sample_pages決定了參與評估的數據頁的數量，默認值是20。當語句執行的執行計劃不是最優選擇時，則考慮增加此參數，以便獲得正確的統計資料。 ? 當設置innodb_stats_persistent=OFF參數或者對單個表設置stats_persistent=0時，對應的統計資料就僅存在于內存中而非磁盤上，當服務器重啟之后統計資料丟失。當然此類統計資料也可以周期性的更新。 比如執行analyze table語句手動刷新統計資料，或者在innodb_stats_on_metadata選項打開之后執行show table status/show index或查詢information_schema.tables/statistics表時非永久統計資料會自動更新，當InnoDB檢測到1/16的表數據被修改時也會更新。 10、重置InnoDB系統表空間 最簡單的增加系統表空間的辦法就是在初始化階段配置數據文件的自增長，通過配置最后一個文件的autoextend屬性，當數據文件空間不足時默認自動增長64M大小。也可以通過修改innodb_autoextend_increment參數修改自動增長的大小。 也可以通過增加另一個數據文件方法擴展表空間，步驟如下： 關閉MySQL 檢查配置的最后一個數據文件是否是autoextend，如果是則根據當前數據文件的大小去掉自動擴展屬性，改成當前大小 在配置文件的innodb_data_file_path參數里增加一個新的數據文件，選擇是否自動擴展 啟動MySQL innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:10M:autoextend ####改成 innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 11、減小系統表空間大小的方法如下： Mysqldump出所有的InnoDB表，包括mysql系統數據庫下的五個表 mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | | slave_master_info | | slave_relay_log_info | | slave_worker_info | +----------------------+ 關閉MySQL 刪除所有InnoDB的數據文件和日志文件，包括*.ibd和ib_log文件，還有在MySQL庫文件夾下的*.ibd文件 刪除所有.frm的InnoDB表文件 在配置文件里配置新的表空間文件 啟動MySQL 導入備份出的dump文件 12、重置InnoDB redo log文件大小 關閉MySQL 通過innodb_log_file_size更改文件大小，通過innodb_log_files_in_group更改文件數量 啟動MySQL innodb_log_file_size=30M innodb_log_files_in_group=3 13、配置單表數據文件表空間 InnoDB的單表數據文件表空間代表每個InnoDB表的數據和索引數據都存放在單獨的.ibd數據文件中，每個.ibd數據文件代表獨立的表空間。此屬性通過innodb_file_per_table配置。 此配置的主要優勢： 當刪除表或者truncate表的時候，意味著對磁盤空間可以回收。而共享表空間時刪除一個表時空間不會釋放而只是文件里有空閑空間 Truncate table命令要比共享表空間快 通過定義create table …data directory=絕對路徑，可以將特定的表放在特定的磁盤或者存儲空間 可以將單獨的表物理拷貝到另外的MySQL實例中 此配置的劣勢：?每個表都有未使用的空間，意味著磁盤空間有些浪費 啟動單獨表空間的方式如下： [mysqld] innodb_file_per_table=1 當設置innodb_file_per_table=0時，所有創建的新表都會放置到共享表空間里，除非在create table命令里顯示的使用tablespace選項。 將已經存在于共享表空間的表修改為獨立表空間的方法： SET GLOBAL innodb_file_per_table=1; ALTER TABLE table_name ENGINE=InnoDB; ? 通過命令create table … data directory=絕對路徑可以將單表數據文件創建在另外的目錄里。在指定的絕對路徑下，會創建數據庫名相同的文件夾，里面含有此表的.ibd文件，同時在MySQL的默認數據文件下的數據庫名文件夾下會創建table_name.isl文件包含了此表的路徑，相當于link文件。 當沒有開啟innodb_file_per_table時，可以將tablespace和data directory兩個參數配合使用 CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table DATA DIRECTORY = '/alternative/directory'; 14、設置Undo log獨立表空間 默認情況下undo log是存儲在系統表空間里，我們也可以將其存放在一個或多個獨立表空間下。 Innodb_undo_tablespaces參數定義了有多少個undo表空間，此參數只能在建立MySQL實例時被配置 innodb_undo_directory參數定義了undo表空間的存放路徑 innodb_undo_logs參數定義了回滾段的數量 mysql> show variables like '%innodb_undo%'; Innodb_undo_log_truncate參數決定是否開啟undo表空間清空 mysql> SET GLOBAL innodb_undo_log_truncate=ON; 當設置了此參數為ON后，則代表undo文件大小超過innodb_max_undo_log_size（默認值是128M）的都標記為清空 15、創建普通表空間 通過create tablespace命令可以創建一個共享的InnoDB表空間，和系統表空間一樣，多個表可以在此表空間上存儲數據，此表空間的數據文件可以放置在任意的文件夾下。 CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name] ? mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB; ##創建在MySQL數據目錄下 mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB; 當創建完表空間之后，就可以通過create table …tablespace或者alter table … tablespace命令將表增加到此表空間上 16、通過alter table命令可以將InnoDB表在系統表空間、獨立表空間和普通表空間之間轉化： ALTER TABLE tbl_name TABLESPACE [=] tablespace_name ##從系統表空間或者獨立表空間上轉移到普通表空間 ALTER TABLE tbl_name ... TABLESPACE [=] innodb_system ##從普通表空間或者獨立表空間上轉移到系統表空間 ALTER TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table ##從系統表空間或者普通表空間轉移到獨立表空間 Alter table … tablespace語句的執行都會導致此表會重建，即使表空間的屬性和之前是一樣的。 17、當刪除一個普通表空間時，首先需要保證此表空間上的所有表都被刪除，否則會報錯。刪除表空間是用drop tablespace語句來執行。Drop database的動作會刪除所有的表，但創建的tablespace不會被自動刪除，必須通過drop tablespace顯示執行。 普通表空間不支持臨時表，而且也不支持alter table … discard tablespace和alter table …import tablespace命令。 18、修改表的存儲引擎 通過alter table語句修改已有表的存儲引擎 ALTER TABLE table_name ENGINE=InnoDB; 19、自增長字段設置 當對InnoDB表設置了自增長字段之后，表會在內存中保存一個自增長計數器。 默認情況下自增長字段的初始值是1，但也可以通過配置auto_increment_offset參數將所有的自增長字段初始值設置為另外的值，而當表中插入數值時，InnoDB會求出當前表中的該列的最大值，然后在此基礎上加1作為插入的數據。默認是以+1為增長的進度，但也可以通過auto_increment_increment配置所有自增長字段的自定義增長進度。