MySql優化之InnoDB,4GB內存,多查詢的my.ini中文配置方案詳解

本文是一個針對 4G 內存系統（主要運行只有 InnoDB 表的 MySQL 并使用幾個連接數執行復雜的查詢）的 MySQL 配置文件方案

#開始配置信息
#描述：4GB 內存、只有 InnoDB、ACID、幾個連接數、繁重的查詢
#類型：系統
#結束配置信息

# 你可以復制該文件到 /etc/my.cnf 以設置全局的選項，復制到 mysql-data-dir/my.cnf 以設置服務器特有的選項（在本安裝中該目錄是 C:mysqldata ），復制到 ~/.my.cnf 以設置用戶特有的選項。
#
# 在該文件中，你可以使用一個程序所支持的全部永久選項。
# 如果你想了解哪些選項是程序支持的，在運行程序時使用“--help”選項。
#
# 更多有關個別選項的詳細信息也可以在手冊中找到。

# 下面的選項將被 MySQL 客戶端應用程序所讀取。
# 注意，只有 MySQL 標準的客戶端應用程序是被保證能讀取到該章節的。
# 如果你希望你自己的 MySQL 客戶端程序能夠承兌這些值，你需要在 MySQL 客戶端庫初始化中作為一個選項來指定它。
#
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

# *** 應用程序特定的選項在下面 ***

# MySQL 服務器。
[mysqld]

# 通用配置選項
port = 3306
socket = /tmp/mysql.sock

# back_log 是指保持在操作系統監聽隊列中的連接數量，即在 MySQL 連接管理器線程處理它們之前的連接數量。
# 如果你有一個非常高的連接率并見到過“拒絕連接”的錯誤，你可能需要提高該值。
# 在你的系統文檔中檢查該參數的最大值。
# 試圖將 back_log 設置得高于你操作系統的限制將不會起到任何作用。
back_log = 50

# 根本不用監聽一個 TCP/IP 端口。
# 如果運行在相同主機上的所有進程都需要連接到 mysqld，這可能是一個安全增強。
# 所有與 mysqld 的互動都必須通過 Unix sockets（套接字）或命名管道進行。
# 注意，在 Windows 上使用該選項但卻不啟用命名管道（通過“enable-named-pipe”選項）將使得 mysqld 變得無用。
#
#skip-networking

# MySQL 允許的并發會話的最大數量。
# 其中的一個連接將被保留給擁有 SUPER 特權的用戶，即使已經到達了連接限制，仍可以允許管理者登錄。
max_connections = 100

# 每個主機允許的最大錯誤數量。
# 如果已到達該限制，主機將阻止對 MySQL 服務器的連接，直到運行“FLUSH HOSTS”或者服務器被重啟。
# 在連接階段的無效密碼和其它錯誤將導致該值被提高。
# 請看全局計數器的“Aborted_connects”狀態變量。
max_connect_errors = 10

# 所有線程打開表的數量。
# 提高該值將提高 mysqld 需要的文件描述符的數量。
# 因此，你必須確定要設置的打開文件數量，在“mysqld 安全”章節的“open-file-limit”變量中，允許到至少為 4096。
table_open_cache = 2048

# 啟用外部文件級鎖定。
# 啟用文件鎖定將有一個性能上的負面影響，因此，只有在如果你有多個數據庫實例運行在相同的文件上（注意，有些限制仍舊被應用）或者如果你使用其它軟件依靠在文件級上鎖定 MyISAM 表時，才使用。
#external-locking

# 服務器可以處理的一個查詢包的最大容量，以及服務器可以處理的最大查詢大小（當工作在大型 BLOB 字段時很重要）。
# 動態擴大，對于每一個連接。
max_allowed_packet = 16M

# 在一個事務中能夠為二進制日志 SQL 語句保持的緩存大小。
# 如果你經常使用大的、多語句的事務，你可以提高該值以獲得更好的性能。
# 來自事務的所有語句被緩沖到二進制日志緩存，并在 COMMIT 之后立即被寫入到二進制日志中。
# 如果事務大于該值，磁盤上的臨時文件將被替代使用。
# 該緩沖在事務中第一個更新語句時分配給每個連接。
binlog_cache_size = 1M

# 一個單一的 HEAP（在內存中）表的最大允許大小。
# 該選項對偶然創建的一個非常大的 HEAP 表起保護作用，否則它將會使用完所有的內存資源。
max_heap_table_size = 64M

# 排序緩沖被用來執行一些 ORDER BY 和 GROUP BY 查詢的排序。
# 如果已排序的數據沒有進入到排序緩沖，一個基于磁盤的合并排序將被替代使用 - 請看“Sort_merge_passes”狀態變量。
# 如果排序是需要的，將分配給每個線程。
sort_buffer_size = 8M

# 該緩沖被用來優化 FULL JOIN（沒有索引的 JOIN）。
# 無論如何，該 JOIN 在大多數情況下對性能是非常壞的，但是設置該變量為一個大值將減少對性能的影響。
# 請看針對一定數量的 FULL JOIN 的“Select_full_join”狀態變量。
# 如果 FULL JOIN 被發現，將分配給每個線程。
join_buffer_size = 8M

# 我們保持在一個緩存中的可重用的線程有好多。
# 當一個客戶端斷開連接時，如果在這之前的線程沒有超過 thread_cache_size，客戶端的線程將放在緩存中。
# 如果你有很多新的連接，這將大幅減少創建所需線程的數量。
# （如果你有一個很好的線程實現，這通常不會給出一個顯著的性能改善。）
thread_cache_size = 8

# 這允許應用程序給予線程系統一個針對運行在相同時間的線程所需數量的提示。
# 該值只在支持 thread_concurrency() 函數調用的系統上有意義（例如 Sun Solaris）。
# 你應該對 thread_concurrency 嘗試 CPU 數量的 2/4/6/... 倍。
thread_concurrency = 8

# 查詢緩存被用來緩存 SELECT 結果并在稍后返回它們，不會再次實際執行相同的查詢。
# 如果你有很多相同的查詢并且很少改變表的話，查詢緩存的啟用將導致顯著的速度改善。
# 請看“Qcache_lowmem_prunes”狀態變量，以檢查當前值對于你的加載是否足夠高。
# 注意：如果你的表經常改變，或者如果你的查詢每次是不同的原文，那么查詢緩存將導致變慢，替代性能的改善。
query_cache_size = 64M

# 只有緩存結果集是小于該限制的。
# 這可以保護一個非常大結果集的查詢緩存覆蓋所有其它查詢結果。
query_cache_limit = 2M

# 編制到全文檢索索引的最小單詞長度。
# 如果你需要檢索更短的單詞，你可能希望減小它。
# 注意，在你修改了該值以后，你需要重建你的 FULLINDEX 索引。
ft_min_word_len = 4

# 如果你的系統支持 memlock() 函數調用，你可能想要啟用該選項（運行 MySQL 以保持它鎖定到內存，并在出現高內存壓力時避免潛在的交換輸出）。
# 這對性能是很有益的。
#memlock

# 如果在 CREATE TABLE 語句期間沒有指定不同的，當創建一個新表時所使用的默認表類型。
default-storage-engine = MYISAM

# 使用的線程堆棧大小。
# 該內存量總是在連接時間被保留的。
# MySQL 自己通常需要不超過 64K 的內存，然而如果你使用的是你自己的堆棧 UDF 函數或者你的系統針對某些操作需要更多堆棧，你可能需要設置該值為一個更高的值。
thread_stack = 192K

# 設置默認的事務隔離等級。
# 可用的級別有：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
transaction_isolation = REPEATABLE-READ

# 內部（內存中的）臨時表的最大容量。
# 如果一個表的增長超過該值，它將自動地轉換到基于磁盤的表。
# 該限制是針對一個單一的表，但可以有很多這樣的表。
tmp_table_size = 64M

# 啟用二進制日志。
# 這在一個復制配置中，對于充當 MASTER 的是必要的。
# 如果你需要有能力及時從你最后的備份點中進行恢復，你也需要二進制日志。
log-bin = mysql-bin

# 推薦的二進制日志格式 - mixed。
binlog_format = mixed

# 如果你正在使用連鎖從服務器（A-〉B-〉C）進行復制，你需要在服務器 B 上啟用該選項。
# 它允許通過從服務器線程將日志記錄到從服務器的二進制日志中來實現日志的更新。
#log_slave_updates

# 啟用完整的查詢日志。服務器接收到的每一個查詢（甚至是錯誤的語法）都將被記錄。
# 這對于調試是很有用的，它通常在產品使用時被禁用。
#log

# 打印警告到錯誤日志文件。
# 如果你有任何 MySQL 的問題，你應該啟用警告日志并檢查錯誤日志中可能的解釋。
#log_warnings

# 記錄慢查詢。
# 慢查詢是指消耗時間超過“long_query_time”中定義的總時間的查詢，或者如果 log_short_format 沒有啟用，不使用索引的查詢。
# 如果你頻繁地添加新查詢到系統中，打開這個是一個比較好的注意。
slow_query_log

# 所有消耗時間超過該總時間的查詢都將被視為是緩慢的。
# 不要在這里使用“1”值，因為這會導致甚至非常快的查詢都會被不時地被記錄（MySQL 當前的度量時間只精確到秒）。
long_query_time = 2

# 被 MySQL 用來存儲臨時文件的目錄。
# 例如，它被用來執行基于磁盤的大的排序，以及內部和顯式的臨時表。
# 如果你不會創建一個非常大的臨時文件，將它放在一個 swapfs/tmpfs 文件系統中是有好處的。
# 另外，你可以把它放在一個專用的磁盤上。
# 你可以指定以“;”分隔的多個路徑 - 它們將在稍后被用在一個循環方式中。
#tmpdir = /tmp

# *** 與復制有關的設置

# 1 到 2^32-1 之間的唯一服務器標識號。
# 該值對于主服務器和從服務器都是必須的。
# 如果“master-host”沒有設置則默認為 1，但若是忽略，MySQL 將不會作為一個主服務器的功能。
server-id = 1

# 復制從服務器（注釋掉主服務器章節以便使用這個）。
#
# 要配置該主機為一個復制從服務器，你可以選擇以下兩種方法：
#
# 1）使用 CHANGE MASTER TO 命令（在我們的手冊中有完整的描述） - 其語法是：
#
# CHANGE MASTER TO MASTER_HOST = 〈host〉, MASTER_PORT = 〈port〉, MASTER_USER = 〈user〉, MASTER_PASSWORD = 〈password〉;
#
# 使用帶引號的字符串替換 〈host〉、〈user〉、〈password〉，并且 〈port〉 是主服務器的端口號（默認為 3306）。
#
# 例子：
#
# CHANGE MASTER TO MASTER_HOST = '125.564.12.1', MASTER_PORT = 3306, MASTER_USER = 'joe', MASTER_PASSWORD = 'secret';
#
# 或者
#
# 2）設置下面的變量。然而，如果你選擇了該方法，請在第一時間內啟動復制（就算不成功，例如，如果你在 MASTER_PASSWORD 中未鍵入密碼，并且從服務器連接失敗），從服務器將創建一個 master.info 文件，稍后在該文件中對下面變量值的任何改變都將被忽略，并被 master.info 文件中的連接所覆蓋，除非你關閉從服務器、刪除 master.info 并重新啟動從服務器。
# 基于這種因素，你可能想要離開下面未接觸的行（已注釋的）并替代使用 CHANGE MASTER TO（請看上面）。
#
# 需要 2 到 2^32-1 之間的唯一 id（與主服務器不同）。
# 如果“master-host”已被設置，默認設置為 2。
# 但若是忽略，將不會作為一個從服務器的功能。
#server-id = 2
#
# 針對該從服務器的復制主服務器 - 必須的。
#master-host = 〈hostname〉
#
# 用戶名，當連接到主服務器時，從服務器將用此來進行認證 - 必須的。
#master-user = 〈username〉
#
# 密碼，當連接到主服務器時，從服務器將用此來進行認證 - 必須的。
#master-password = 〈password〉
#
# 端口，主服務器正在監聽的。
# 可選的 - 默認為 3306。
#master-port = 〈port〉

# 讓從服務器只讀。
# 只有擁有 SUPER 特權的用戶和復制從服務器線程能夠修改它的數據。
# 你可以使用這個來確保不會有應用程序在無意中替代主服務器修改從服務器上的數據。
#read_only

#*** MyISAM 特有的選項

# 鍵緩沖區的大小，用來為 MyISAM 表緩存索引塊。
# 不要將它設置為超過你可用內存的 30% 以上，因為操作系統也需要一些內存來緩存行。
# 即使你不使用 MyISAM 表，你仍應該將它設置為 8-64M，因為它也被用于內部的臨時磁盤表。
key_buffer_size = 32M

# 用于進行 MyISAM 表全表掃描的緩沖區大小。
# 如果全表掃描是需要的，將分配給每個線程。
read_buffer_size = 2M

# 當在一個有序的排序中讀取行時，可以通過該緩沖區來讀取行，以避免對磁盤的查找。
# 如果將該值設置為一個很高的值，你可以大幅度提高 ORDER BY 的性能。
# 當需要時，分配給每個線程。
read_rnd_buffer_size = 16M

# MyISAM 使用特殊的類似于樹的緩存來讓大批量插入（亦即 INSERT ... SELECT、INSERT ... VALUES(...) 和 LOAD DATA INFILE）操作變得更快。
# 該變量限制每個線程的緩存樹的字節大小。
# 將它設置為 0 將禁用該優化。
# 為了優化性能，不要將它設置得比“key_buffer_size”大。
# 當檢測到大量的插入時，該緩沖區被分配。
bulk_insert_buffer_size = 64M

# 當 MySQL 需要通過 REPAIR、OPTIMIZE、ALTER 表語句重建索引，以及 LOAD DATA INFILE 到一個空表時，該緩沖區被分配。
# 它是給每個線程分配的，因此小心比較大的設置。
myisam_sort_buffer_size = 128M

# 當重建索引（在 REPAIR、ALTER TABLE 或 LOAD DATA INFILE 期間）時，MySQL 允許使用的臨時文件的最大大小。
# 如果“file-size”比這個值大，索引將通過鍵緩存（更慢一些）創建。
myisam_max_sort_file_size = 10G

# 如果一個表有超過一個的索引，MyISAM 能夠在排序時并行地使用超過一個的線程來修復它們。
# 如果你有多個 CPU 和足夠的內存，這是很有意義的。
myisam_repair_threads = 1

# 自動地檢查和修復沒有正確關閉的 MyISAM 表。
myisam_recover

# *** INNODB 特定的選項 ***

# 如果你有一個支持 InnoDB 啟用的 MySQL 服務器，而你卻并不計劃使用它，請使用該選項。
# 這可以保存一些內存和磁盤空間，并提高速度。
#skip-innodb

# 附加的內存池，InnoDB 用來存儲元數據信息。
# 如果 InnoDB 因該目的而需要更多的內存，它將開始從操作系統來分配它。
# 由于這在大多數最近的操作系統上是足夠快的，你通常不需要改變這個值。
# SHOW INNODB STATUS 將顯示當前使用總量。
innodb_additional_mem_pool_size = 16M

# InnoDB，不像 MyISAM，使用一個緩沖池來緩存索引和行數據。
# 你將該值設得越大，在表中訪問需要的數據時，磁盤 I/O 就越少。
# 在一個專用的數據庫服務器上，你可以設置該參數到機器物理內存大小的 80%。
# 不要把它設置得太大，因為物理內存的競爭可能導致操作系統中的分頁。
# 注意，在 32 位的系統上，你可能在每個處理器的用戶級內存上被限制在 2-3.5G，因此不要把它設置得太高。
innodb_buffer_pool_size = 2G

# InnoDB 存儲數據到一個或多個數據文件，形成表空間。
# 如果對于你對你的數據有一個單一的物理設備，那么一個單一的自動擴展文件就已經足夠了。
# 在其它情況下，每設備一個單一文件是一個非常好的選擇。
# 你也可以配置 InnoDB 來使用原始的磁盤分區 - 請參考手冊以獲取更多有關這個的信息。
innodb_data_file_path = ibdata1:10M:autoextend

# 如果你希望 InnoDB 表空間文件存儲到其它的地方，設置該選項。
# 默認的是 MySQL 數據目錄。
#innodb_data_home_dir = 〈directory〉

# 異步 IO 操作所使用的 IO 線程數。
# 該值在 Unix 系統上被硬編碼為 4，但在 Windows 上，磁盤 I/O 可能受益于一個更大的數字。
innodb_file_io_threads = 4

# 如果你遇到 InnoDB 表空間腐爛，設置該值為一個非零值，將很容易地幫助你導出你的表。
# 以值 1 開始并提高它，直到你能夠成功地導出表。
#innodb_force_recovery=1

# InnoDB 內核里面允許的線程數量。
# 最佳的值高度取決于應用程序、硬件以及操作系統的調度屬性。
# 一個太高的值可能導致線程顛簸。
innodb_thread_concurrency = 16

# 如果設置為 1，InnoDB 在每次提交（提供完整的 ACID 行為）時刷新事務日志到磁盤。
# 如果你想安全地進行折中，并且你正在運行小事務，你可以為 0 或 2 來減少日志的磁盤 I/O。
# 值 0 表示日志只被寫入到日志文件，并且日志文件大約每秒一次刷新到磁盤。
# 值 2 表示日志在每次提交時被寫入到日志文件，但是日志文件只是大約每秒一次被刷新到磁盤。
innodb_flush_log_at_trx_commit = 1

# 加速 InnoDB 的關閉。
# 這在關閉時將禁用 InnoDB 做一個完整的清除和插入緩沖合并。
# 它可能會提高不少關閉的時間，但替代的是 InnoDB 將在下一次啟動時來完成它。
#innodb_fast_shutdown

# InnoDB 緩沖日志數據所使用的緩沖區大小。
# 一旦它滿了，InnoDB 將刷新它到磁盤。
# 因為不管怎么它都是每秒刷新一次，所以沒有必要讓它變得很大（甚至是很長的事務）。
innodb_log_buffer_size = 8M

# 一個日志組中每個日志文件的大小。
# 你可以設置日志文件的聯合大小為你的緩沖池大小的 25%-100%，以避免對日志文件不必要的緩沖池動態刷新重寫。
# 然而，注意，一個更大的日志文件大小將增加恢復處理所需的時間。
innodb_log_file_size = 256M

# 日志組中文件的總數。
# 通常值為 2-3 就已足夠了。
innodb_log_files_in_group = 3

# InnoDB 日志文件的位置。
# 默認為 MySQL 的數據目錄。
# 你可能希望指定它到一個專用的硬盤或一個 RAID1 卷標來改善性能。
#innodb_log_group_home_dir

# InnoDB 緩沖池中允許的臟頁面的最大百分比。
# 如果它到達了，InnoDB 將開始積極地清理它們，以避免消耗完所有的干凈頁面。
# 這是一個軟限制，不保證能夠一直保持。
innodb_max_dirty_pages_pct = 90

# InnoDB 對日志使用的刷新方法。
# 表空間總是使用雙寫刷新邏輯。
# 默認值為“fdatasync”，其它選項是“O_DSYNC”。
#innodb_flush_method = O_DSYNC

# 一個 InnoDB 事務應等待的在回滾之前被授權鎖定的時長。
# InnoDB 在它自己的鎖定表中自動地檢測事務死鎖，并回滾事務。
# 如果你在相同的事務中使用 LOCK TABLES 命令，或者其它比 InnoDB 更加事務安全的存儲引擎，那么稍后會出現 InnoDB 不能提示的死鎖。
# 如果像這樣，超時對于解決問題是很有用的。
innodb_lock_wait_timeout = 120

[mysqldump]
# 在寫入到文件之前，不要緩沖整個結果集。
# 導出非常大的表時是必須的。
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# 只允許 UPDATE 和 DELETE 使用鍵。
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# 增加每次處理所允許打開的文件數量。
# 警告：確保你已經設置全局系統限制足夠高！
# 對于一個大數量的打開表，高值是必須的。
open-files-limit = 8192

以上的參數設置大家可以根據自己實現情況參考一下