網(wǎng)站訪問量越來越大，MySQL自然成為瓶頸。因此MySQL 的優(yōu)化成為我們需要考慮的問題，第一步自然想到的是 MySQL 系統(tǒng)參數(shù)的優(yōu)化，作為一個訪問量很大的網(wǎng)站（日20萬人次以上）的數(shù)據(jù)庫系統(tǒng)，不可能指望 MySQL 默認(rèn)的系統(tǒng)參數(shù)能夠讓 MySQL運(yùn)行得非常順暢。
(1)、back_log： 要求MySQL 能有的連接數(shù)量。當(dāng)主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求，這就起作用，然后主線程花些時間(盡管很短)檢查連接并且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。只有如果期望在一個短時間內(nèi)有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊(duì)列的大小。你的操作系統(tǒng)在這個隊(duì)列大小上有它自己的限制。 試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效。當(dāng)你觀察你的主機(jī)進(jìn)程列表，發(fā)現(xiàn)大量 264084 | unauthenticated user |xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時，就要加大 back_log的值了。默認(rèn)數(shù)值是50，我把它改為500。
(2)、interactive_timeout： 服務(wù)器在關(guān)閉它前在一個交互連接上等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用 CLIENT_INTERACTIVE 選項(xiàng)的客戶。默認(rèn)數(shù)值是28800，我把它改為7200。
(3)、key_buffer_size： 索引塊是緩沖的并且被所有的線程共享。key_buffer_size是用于索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負(fù)擔(dān)得起那樣多。如果你使它太大，系統(tǒng)將開始換頁并且真的變慢了。默認(rèn)數(shù)值是8388600(8M)，我的MySQL主機(jī)有2GB內(nèi)存，所以我把它改為402649088(400MB)。
(4)、max_connections： 允許的同時客戶的數(shù)量。增加該值增加mysqld 要求的文件描述符的數(shù)量。這個數(shù)字應(yīng)該增加，否則，你將經(jīng)常看到 Toomany connections 錯誤。 默認(rèn)數(shù)值是100，我把它改為1024 。
(5)、record_buffer： 每個進(jìn)行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。默認(rèn)數(shù)值是131072(128K)，我把它改為16773120 (16M)
(6)、sort_buffer： 每個需要進(jìn)行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。默認(rèn)數(shù)值是2097144(2M)，我把它改為16777208 (16M)。
(7)、table_cache： 為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。MySQL對每個唯一打開的表需要2個文件描述符。默認(rèn)數(shù)值是64，我把它改為512。
(8)、thread_cache_size： 可以復(fù)用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當(dāng)斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個變量的作用。我把它設(shè)置為 80。
(10)、wait_timeout： 服務(wù)器在關(guān)閉它之前在一個連接上等待行動的秒數(shù)。默認(rèn)數(shù)值是28800，我把它改為7200。
注：參數(shù)的調(diào)整可以通過修改/etc/my.cnf文件并重啟 MySQL 實(shí)現(xiàn)。這是一個比較謹(jǐn)慎的工作，上面的結(jié)果也僅僅是我的一些看法，你可以根據(jù)你自己主機(jī)的硬件情況（特別是內(nèi)存大小）進(jìn)一步修改。
mysql> showglobal status;
可以列出mysql服務(wù)器運(yùn)行各種狀態(tài)值，另外，查詢mysql服務(wù)器配置信息語句：
mysql> showvariables;
一、慢查詢
mysql> showvariables like ‘%slow%‘;
+------------------+-------+
| variable_name | value |
+------------------+-------+
|log_slow_queries | on |
|slow_launch_time | 2 |
+------------------+-------+
mysql> showglobal status like ‘%slow%‘;
+---------------------+-------+
| variable_name | value |
+---------------------+-------+
|slow_launch_threads | 0 |
| slow_queries | 4148 |
+---------------------+-------+ |
配置中打開了記錄慢查詢，執(zhí)行時間超過2秒的即為慢查詢，系統(tǒng)顯示有4148個慢查詢，你可以分析慢查詢日志，找出有問題的sql語句，慢查詢時間不宜設(shè)置過長，否則意義不大，最好在5秒以內(nèi)，如果你需要微秒級別的慢查詢，可以考慮給mysql打補(bǔ)丁：http://www.percona.com/docs/wiki/release:start，記得找對應(yīng)的版本。
打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點(diǎn)點(diǎn)影響，如果你的mysql是主－從結(jié)構(gòu)，可以考慮打開其中一臺從服務(wù)器的慢查詢?nèi)罩荆@樣既可以監(jiān)控慢查詢，對系統(tǒng)性能影響又小。
二、連接數(shù)
經(jīng)常會遇見”mysql: error 1040: too many connections”的情況，一種是訪問量確實(shí)很高，mysql服務(wù)器抗不住，這個時候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是mysql配置文件中max_connections值過小：
mysql> show variables like ‘max_connections‘;
+-----------------+-------+
| variable_name | value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+ |
這臺mysql服務(wù)器最大連接數(shù)是256，然后查詢一下服務(wù)器響應(yīng)的最大連接數(shù)：
mysql服務(wù)器過去的最大連接數(shù)是245，沒有達(dá)到服務(wù)器連接數(shù)上限256，應(yīng)該沒有出現(xiàn)1040錯誤，比較理想的設(shè)置是
最大連接數(shù)占上限連接數(shù)的85％左右，如果發(fā)現(xiàn)比例在10%以下，mysql服務(wù)器連接數(shù)上限設(shè)置的過高了。
三、key_buffer_size
key_buffer_size是對myisam表性能影響最大的一個參數(shù)，下面一臺以myisam為主要存儲引擎服務(wù)器的配置：
mysql> show variables like‘key_buffer_size‘;+-----------------+------------+
| variable_name | value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+ |
分配了512mb內(nèi)存給key_buffer_size，我們再看一下key_buffer_size的使用情況：
mysql> show global status like ‘key_read%‘;
+------------------------+-------------+
| variable_name | value |
+------------------------+-------------+
| key_read_requests | 27813678764 |
| key_reads | 6798830 |
+------------------------+-------------+ |
一共有27813678764個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計(jì)算索引未命中緩存的概率：
key_cache_miss_rate ＝ key_reads / key_read_requests * 100%
比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬盤，已經(jīng)很bt了，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬盤），如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當(dāng)減少。
mysql服務(wù)器還提供了key_blocks_*參數(shù)：
mysql> show global status like ‘key_blocks_u%‘;
+------------------------+-------------+
| variable_name | value |
+------------------------+-------------+
| key_blocks_unused | 0 |
| key_blocks_used | 413543 |
+------------------------+-------------+ |
key_blocks_unused表示未使用的緩存簇(blocks)數(shù)，key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)，比如這臺服務(wù)器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置：
key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80% |
四、臨時表
mysql> show global status like ‘created_tmp%‘;
+-------------------------+---------+
| variable_name | value |
+-------------------------+---------+
| created_tmp_disk_tables | 21197 |
| created_tmp_files | 58 |
| created_tmp_tables | 1771587 |
+-------------------------+---------+ |
每次創(chuàng)建臨時表，created_tmp_tables增加，如果是在磁盤上創(chuàng)建臨時表，created_tmp_disk_tables也增加,created_tmp_files表示mysql服務(wù)創(chuàng)建的臨時文件文件數(shù)，比較理想的配置是：
created_tmp_disk_tables /created_tmp_tables * 100% <= 25%比如上面的服務(wù)器created_tmp_disk_tables / created_tmp_tables * 100% ＝ 1.20%，應(yīng)該相當(dāng)好了。我們再看一下mysql服務(wù)器對臨時表的配置：
mysql> show variables where variable_name in (‘tmp_table_size‘,‘max_heap_table_size‘);
+---------------------+-----------+
| variable_name | value |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+---------------------+-----------+ |
只有256mb以下的臨時表才能全部放內(nèi)存，超過的就會用到硬盤臨時表。
五、open table情況
mysql> show global status like ‘open%tables%‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| open_tables | 919 |
| opened_tables | 1951 |
+---------------+-------+ |
open_tables表示打開表的數(shù)量，opened_tables表示打開過的表數(shù)量，如果opened_tables數(shù)量過大，說明配置中table_cache(5.1.3之后這個值叫做table_open_cache)值可能太小，我們查詢一下服務(wù)器table_cache值：
mysql> show variables like ‘table_cache‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+ |
比較合適的值為：
open_tables / opened_tables * 100% >= 85%
open_tables / table_cache * 100% <= 95% |
六、進(jìn)程使用情況
mysql> show global status like ‘thread%‘;
+-------------------+-------+
| variable_name | value |
+-------------------+-------+
| threads_cached | 46 |
| threads_connected | 2 |
| threads_created | 570 |
| threads_running | 1 |
+-------------------+-------+ |
如果我們在mysql服務(wù)器配置文件中設(shè)置了thread_cache_size，當(dāng)客戶端斷開之后，服務(wù)器處理此客戶的線程將會緩存起來以響應(yīng)下一個客戶而不是銷毀（前提是緩存數(shù)未達(dá)上限）。threads_created表示創(chuàng)建過的線程數(shù)，如果發(fā)現(xiàn)threads_created值過大的話，表明mysql服務(wù)器一直在創(chuàng)建線程，這也是比較耗資源，可以適當(dāng)增加配置文件中thread_cache_size值，查詢服務(wù)器thread_cache_size配置：
mysql> show variables like ‘thread_cache_size‘;
+-------------------+-------+
| variable_name | value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+ |
示例中的服務(wù)器還是挺健康的。
七、查詢緩存(query cache)
mysql> show global status like ‘qcache%‘;
+-------------------------+-----------+
| variable_name | value |
+-------------------------+-----------+
| qcache_free_blocks | 22756 |
| qcache_free_memory | 76764704 |
| qcache_hits | 213028692 |
| qcache_inserts | 208894227 |
| qcache_lowmem_prunes | 4010916 |
| qcache_not_cached | 13385031 |
| qcache_queries_in_cache | 43560 |
| qcache_total_blocks | 111212 |
+-------------------------+-----------+ |
mysql查詢緩存變量解釋：
qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)。數(shù)目大說明可能有碎片。flush query cache會對緩存中的碎片進(jìn)行整理，從而得到一個空閑塊。
qcache_free_memory：緩存中的空閑內(nèi)存。
qcache_hits：每次查詢在緩存中命中時就增大
qcache_inserts：每次插入一個查詢時就增大。命中次數(shù)除以插入次數(shù)就是不中比率。
qcache_lowmem_prunes：緩存出現(xiàn)內(nèi)存不足并且必須要進(jìn)行清理以便為更多查詢提供空間的次數(shù)。這個數(shù)字最好長時間來看；如果這個數(shù)字在不斷增長，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少。（上面的 free_blocks和free_memory可以告訴您屬于哪種情況）
qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 select 語句或者用了now()之類的函數(shù)。
qcache_queries_in_cache：當(dāng)前緩存的查詢（和響應(yīng)）的數(shù)量。
qcache_total_blocks：緩存中塊的數(shù)量。
我們再查詢一下服務(wù)器關(guān)于query_cache的配置：
mysql> show variables like ‘query_cache%‘;
+------------------------------+-----------+
| variable_name | value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | on |
| query_cache_wlock_invalidate | off |
+------------------------------+-----------+ |
各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小大小
query_cache_size：查詢緩存大小
query_cache_type：緩存類型，決定緩存什么樣的查詢，示例中表示不緩存 select sql_no_cache 查詢
query_cache_wlock_invalidate：當(dāng)有其他客戶端正在對myisam表進(jìn)行寫操作時，如果查詢在query cache中，是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，默認(rèn)是4kb，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
查詢緩存碎片率 = qcache_free_blocks /qcache_total_blocks * 100%
如果查詢緩存碎片率超過20%，可以用flush query cache整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。
查詢緩存利用率 = (query_cache_size -qcache_free_memory) / query_cache_size * 100%
查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當(dāng)減小；查詢緩存利用率在80％以上而且qcache_lowmem_prunes> 50的話說明query_cache_size可能有點(diǎn)小，要不就是碎片太多。
查詢緩存命中率 = (qcache_hits - qcache_inserts) /qcache_hits * 100%
示例服務(wù)器查詢緩存碎片率＝ 20.46％，查詢緩存利用率＝ 62.26％，查詢緩存命中率＝ 1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
八、排序使用情況
mysql> show global status like ‘sort%‘;
+-------------------+------------+
| variable_name | value |
+-------------------+------------+
| sort_merge_passes | 29 |
| sort_range | 37432840 |
| sort_rows | 9178691532 |
| sort_scan | 1860569 |
+-------------------+------------+ |
sort_merge_passes 包括兩步。mysql 首先會嘗試在內(nèi)存中做排序，使用的內(nèi)存大小由系統(tǒng)變量 sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到內(nèi)存中，mysql 就會把每次在內(nèi)存中排序的結(jié)果存到臨時文件中，等 mysql 找到所有記錄之后，再把臨時文件中的記錄做一次排序。這再次排序就會增加 sort_merge_passes。實(shí)際上，mysql 會用另一個臨時文件來存再次排序的結(jié)果，所以通常會看到 sort_merge_passes 增加的數(shù)值是建臨時文件數(shù)的兩倍。因?yàn)橛玫搅伺R時文件，所以速度可能會比較慢，增加 sort_buffer_size 會減少 sort_merge_passes 和創(chuàng)建臨時文件的次數(shù)。但盲目的增加 sort_buffer_size 并不一定能提高速度，見 how fast can you sort data with mysql?（引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html，貌似被墻）
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點(diǎn)的好處，參見http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/
九、文件打開數(shù)(open_files)
mysql> show global status like ‘open_files‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| open_files | 1410 |
+---------------+-------+
mysql> show variables like ‘open_files_limit‘;
+------------------+-------+
| variable_name | value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+ |
比較合適的設(shè)置：open_files / open_files_limit * 100%<= 75％
十、表鎖情況
mysql> show global status like ‘table_locks%‘;
+-----------------------+-----------+
| variable_name | value |
+-----------------------+-----------+
| table_locks_immediate | 490206328 |
| table_locks_waited | 2084912 |
+-----------------------+-----------+ |
table_locks_immediate表示立即釋放表鎖數(shù)，table_locks_waited表示需要等待的表鎖數(shù)，如果table_locks_immediate /table_locks_waited > 5000，最好采用innodb引擎，因?yàn)閕nnodb是行鎖而myisam是表鎖，對于高并發(fā)寫入的應(yīng)用innodb效果會好些。示例中的服務(wù)器table_locks_immediate/ table_locks_waited ＝ 235，myisam就足夠了。
十一、表掃描情況
mysql>showglobalstatuslike‘handler_read%‘;
+-----------------------+-------------+
|variable_name|value|
+-----------------------+-------------+
|handler_read_first|5803750|
|handler_read_key|6049319850|
|handler_read_next|94440908210|
|handler_read_prev|34822001724|
|handler_read_rnd|405482605|
|handler_read_rnd_next|18912877839|
+-----------------------+-------------+ |
各字段解釋參見http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，調(diào)出服務(wù)器完成的查詢請求次數(shù)：
mysql> show global status like ‘com_select‘;
+---------------+-----------+
| variable_name | value |
+---------------+-----------+
| com_select | 222693559 |
+---------------+-----------+ |
計(jì)算表掃描率：
表掃描率＝ handler_read_rnd_next / com_select
如果表掃描率超過4000，說明進(jìn)行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8mb。
后記：
文中提到一些數(shù)字都是參考值，了解基本原理就可以，除了mysql提供的各種status值外，操作系統(tǒng)的一些性能指標(biāo)也很重要，比如常用的top,iostat等，尤其是iostat，現(xiàn)在的系統(tǒng)瓶頸一般都在磁盤io上，關(guān)于iostat的使用
網(wǎng)站訪問量越來越大，MySQL自然成為瓶頸。因此MySQL 的優(yōu)化成為我們需要考慮的問題，第一步自然想到的是 MySQL 系統(tǒng)參數(shù)的優(yōu)化，作為一個訪問量很大的網(wǎng)站（日20萬人次以上）的數(shù)據(jù)庫系統(tǒng)，不可能指望 MySQL 默認(rèn)的系統(tǒng)參數(shù)能夠讓 MySQL運(yùn)行得非常順暢。
(1)、back_log： 要求MySQL 能有的連接數(shù)量。當(dāng)主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求，這就起作用，然后主線程花些時間(盡管很短)檢查連接并且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。只有如果期望在一個短時間內(nèi)有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊(duì)列的大小。你的操作系統(tǒng)在這個隊(duì)列大小上有它自己的限制。 試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效。當(dāng)你觀察你的主機(jī)進(jìn)程列表，發(fā)現(xiàn)大量 264084 | unauthenticated user |xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時，就要加大 back_log的值了。默認(rèn)數(shù)值是50，我把它改為500。
(2)、interactive_timeout： 服務(wù)器在關(guān)閉它前在一個交互連接上等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用 CLIENT_INTERACTIVE 選項(xiàng)的客戶。默認(rèn)數(shù)值是28800，我把它改為7200。
(3)、key_buffer_size： 索引塊是緩沖的并且被所有的線程共享。key_buffer_size是用于索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負(fù)擔(dān)得起那樣多。如果你使它太大，系統(tǒng)將開始換頁并且真的變慢了。默認(rèn)數(shù)值是8388600(8M)，我的MySQL主機(jī)有2GB內(nèi)存，所以我把它改為402649088(400MB)。
(4)、max_connections： 允許的同時客戶的數(shù)量。增加該值增加mysqld 要求的文件描述符的數(shù)量。這個數(shù)字應(yīng)該增加，否則，你將經(jīng)常看到 Toomany connections 錯誤。 默認(rèn)數(shù)值是100，我把它改為1024 。
(5)、record_buffer： 每個進(jìn)行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。默認(rèn)數(shù)值是131072(128K)，我把它改為16773120 (16M)
(6)、sort_buffer： 每個需要進(jìn)行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。默認(rèn)數(shù)值是2097144(2M)，我把它改為16777208 (16M)。
(7)、table_cache： 為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。MySQL對每個唯一打開的表需要2個文件描述符。默認(rèn)數(shù)值是64，我把它改為512。
(8)、thread_cache_size： 可以復(fù)用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當(dāng)斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個變量的作用。我把它設(shè)置為 80。
(10)、wait_timeout： 服務(wù)器在關(guān)閉它之前在一個連接上等待行動的秒數(shù)。默認(rèn)數(shù)值是28800，我把它改為7200。
注：參數(shù)的調(diào)整可以通過修改/etc/my.cnf文件并重啟 MySQL 實(shí)現(xiàn)。這是一個比較謹(jǐn)慎的工作，上面的結(jié)果也僅僅是我的一些看法，你可以根據(jù)你自己主機(jī)的硬件情況（特別是內(nèi)存大小）進(jìn)一步修改。