當我們安裝好MySQL後，首要的問題就是對MySQL的各種參數進行調整以適應我們的要求。其實，MySQL本身已經為我們提供了很多現成的模板，我們可以選擇合適的直接使用，比如：我們在MySQL源代碼裡能找到的配置文件模板有如下幾種：
my-small.cnf：
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the MySQLd daemon
# doesn't use much resources.
my-medium.cnf：
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
my-large.cnf：
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
my-huge.cnf：
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
my-innodb-heavy-4G.cnf：
# This is a MySQL example config file for systems with 4GB of memory
# running mostly MySQL using InnoDB only tables and performing complex
# querIEs with few connections.
我們可以根據內存的大小選擇相應的配置文件，復制為/etc/my.cnf，重啟MySQL服務即可。
不過MySQL參數那麼多，很多時候我們還是要知道他們具體的含義才能根據實際問題做出具體的調整。
-----------------------------------
我們可以通過SHOW VARIABLES;來查看系統參數，通過SHOW STATUS;來判斷系統狀態。
-----------------------------------
先來看看table_cache參數對性能的影響。摘錄my-innodb-heavy-4G.cnf中對它的描述：
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that MySQLd requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [MySQLd_safe]
table_cache = 2048
比如：當系統比較繁忙的時候，我們show variables;查到table_cache的值，再show status;發現open_tables的值和table_cache差不多，而且opened_tables還一直再增加，就說明我們的table_cache設置的太小了。
-----------------------------------
下面看看key_buffer_size參數對性能的影響。摘錄my-innodb-heavy-4G.cnf中對它的描述：
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size = 32M
比如當我們show status;的時候，發現key_reads / key_read_requests的值很小，比如：1/100，或者1/1000，就說明key_buffer_size設置是合理的。另外，key_buffer_size選項只對myisam起作用，且大小不應該超過內存的30%，如果不使用myisam表類型，那麼設置成8~64M就差不多了。
-----------------------------------
下面看看query_cache_size參數對性能的影響。摘錄my-innodb-heavy-4G.cnf中對它的描述：
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical querIEs and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your querIEs are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size = 64M
比如當我們show status;的時候，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那麼可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。
與查詢緩沖有關的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查詢緩沖，可以設置為0、1、2，該變量是SESSION級的變量。query_cache_limit指定單個查詢能夠使用的緩沖區大小，缺省為1M。query_cache_min_res_unit是在4.1版本以後引入的，它指定分配緩沖區空間的最小單位，缺省為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit
-----------------------------------
此外，針對InnoDB表類型，還有很多調節參數。摘錄my-innodb-heavy-4G.cnf中對它的描述：
# *** INNODB Specific options ***
# Use this option if you have a MySQL Server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb
# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent Operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 16M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# Access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the Machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the Operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size = 2G
# InnoDB stores data in one or more data files forming the tablespace.
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well - please refer to the manual for more info
# about this.
innodb_data_file_path = ibdata1:10M:autoextend
# Set this option if you would like the InnoDB tablespace files to be
# stored in another location. By default this is the MySQL datadir.
#innodb_data_home_dir = <directory>
# Number of IO threads to use for async IO Operations. This value is
# hardcoded to 4 on Unix, but on Windows disk I/O may benefit from a
# larger number.
innodb_file_io_threads = 4
# If you run into InnoDB tablespace corruption, setting this to a nonzero
# value will likely help you to dump your tables. Start from value 1 and
# increase it until you're able to dump the table successfully.
#innodb_force_recovery=1
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler propertIEs. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 16
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 1
# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size = 8M
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size = 256M
# Total number of files in the log group. A value of 2-3 is usually good
# enough.
innodb_log_files_in_group = 3
# Location of the InnoDB log files. Default is the MySQL datadir. You
# may wish to point it to a dedicated hard drive or a RAID1 volume for
# improved performance
#innodb_log_group_home_dir
# Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
# If it is reached, InnoDB will start flushing them out agressively to
# not run out of clean pages at all. This is a soft limit, not
# guaranteed to be held.
innodb_max_dirty_pages_pct = 90
# The flush method InnoDB will use for Log. The tablespace always uses
# doublewrite flush logic. The default value is "fdatasync", another
# option is "O_DSYNC".
#innodb_flush_method=O_DSYNC
# How long an InnoDB transaction should wait for a lock to be granted
# before being rolled back. InnoDB automatically detects transaction
# deadlocks in its own lock table and rolls back the transaction. If you
# use the LOCK TABLES command, or other transaction-safe storage engines
# than InnoDB in the same transaction, then a deadlock may arise which
# InnoDB cannot notice. In cases like this the timeout is useful to
# resolve the situation.
innodb_lock_wait_timeout = 120
其中，我覺得最重要的就是innodb_buffer_pool_size選項，應該盡可能設置大點，至少是內存的50%。其他參數的描述請參考上面文檔內容。
