目錄
一、優化概述
二、查詢與索引優化分析
1性能瓶頸定位
Show命令
慢查詢日志
explain分析查詢
profiling分析查詢
2索引及查詢優化
三、配置優化
1) max_connections
2) back_log
3) interactive_timeout
4) key_buffer_size
5) query_cache_size
6) record_buffer_size
7) read_rnd_buffer_size
8) sort_buffer_size
9) join_buffer_size
10) table_cache
11) max_heap_table_size
12) tmp_table_size
13) thread_cache_size
14) thread_concurrency
15) wait_timeout
一、 優化概述
MySQL數據庫是常見的兩個瓶頸是CPU和I/O的瓶頸，CPU在飽和的時候一般發生在數據裝入內存或從磁盤上讀取數據時候。磁盤I/O瓶頸發生在裝入數據遠大於內存容量的時候，如果應用分布在網絡上，那麼查詢量相當大的時候那麼平瓶頸就會出現在網絡上，我們可以用mpstat, iostat, sar和vmstat來查看系統的性能狀態。
除了服務器硬件的性能瓶頸，對於MySQL系統本身，我們可以使用工具來優化數據庫的性能，通常有三種：使用索引，使用EXPLAIN分析查詢以及調整MySQL的內部配置。
在優化MySQL時，通常需要對數據庫進行分析，常見的分析手段有慢查詢日志，EXPLAIN 分析查詢，profiling分析以及show命令查詢系統狀態及系統變量，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能。
我們可以通過show命令查看MySQL狀態及變量，找到系統的瓶頸：
Mysql> show status ——顯示狀態信息（擴展show status like 'XXX'）
Mysql> show variables ——顯示系統變量（擴展show variables like 'XXX'）
Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態
Mysql> show processlist ——查看當前SQL執行，包括執行狀態、是否鎖表等
Shell> mysqladmin variables -u username -p password——顯示系統變量
Shell> mysqladmin extended-status -u username -p password——顯示狀態信息
查看狀態變量及幫助：
Shell> mysqld --verbose --help [|more #逐行顯示]
比較全的Show命令的使用可參考： http://blog.phpbean.com/a.cn/18/
慢查詢日志開啟：
在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個配置參數
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=2
注：log-slow-queries參數為慢查詢日志存放的位置，一般這個目錄要有mysql的運行帳號的可寫權限，一般都將這個目錄設置為mysql的數據存放目錄；
long_query_time=2中的2表示查詢超過兩秒才記錄；
在my.cnf或者my.ini中添加log-queries-not-using-indexes參數，表示記錄下沒有使用索引的查詢。
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=10
log-queries-not-using-indexes
慢查詢日志開啟方法二：
我們可以通過命令行設置變量來即時啟動慢日志查詢。由下圖可知慢日志沒有打開，slow_launch_time=# 表示如果建立線程花費了比這個值更長的時間,slow_launch_threads 計數器將增加
設置慢日志開啟
MySQL後可以查詢long_query_time 的值 。
為了方便測試，可以將修改慢查詢時間為5秒。
慢查詢分析mysqldumpslow
我們可以通過打開log文件查看得知哪些SQL執行效率低下
[root@localhost mysql]# more slow-query.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;
從日志中，可以發現查詢時間超過5 秒的SQL，而小於5秒的沒有出現在此日志中。
如果慢查詢日志中記錄內容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來對慢查詢日志進行分類匯總。mysqldumpslow對日志文件進行了分類匯總，顯示匯總後摘要結果。
進入log的存放目錄，運行
[root@mysql_data]#mysqldumpslow slow-query.log
Reading mysql slow query log from slow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
這會輸出記錄次數最多的10條SQL語句，其中：
-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
-t, 是top n的意思，即為返回前面多少條的數據；
-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；
例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回記錄集最多的10個查詢。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照時間排序的前10條裡面含有左連接的查詢語句。
使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢語句，對MySQL查詢語句的監控、分析、優化是MySQL優化非常重要的一步。開啟慢查詢日志後，由於日志記錄操作，在一定程度上會占用CPU資源影響mysql的性能，但是可以階段性開啟來定位性能瓶頸。
使用 EXPLAIN 關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。通過explain命令可以得到:
– 表的讀取順序
– 數據讀取操作的操作類型
– 哪些索引可以使用
– 哪些索引被實際使用
– 表之間的引用
– 每張表有多少行被優化器查詢
EXPLAIN字段：
ØTable：顯示這一行的數據是關於哪張表的
Øpossible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
Økey：實際使用的索引。如果為NULL，則沒有使用索引。MYSQL很少會選擇優化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引
Økey_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
Øref：顯示索引的哪一列被使用了，如果可能的話，是一個常數
Ørows：MySQL認為必須檢索的用來返回請求數據的行數
Øtype：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq_reg、ref、range、index和ALL
nsystem、const：可以將查詢的變量轉為常量. 如id=1; id為 主鍵或唯一鍵.
neq_ref：訪問索引,返回某單一行的數據.(通常在聯接時出現，查詢使用的索引為主鍵或惟一鍵)
nref：訪問索引,返回某個值的數據.(可以返回多行) 通常使用=時發生
nrange：這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西，並且該字段上建有索引時發生的情況(注:不一定好於index)
nindex：以索引的順序進行全表掃描，優點是不用排序,缺點是還要全表掃描
nALL：全表掃描，應該盡量避免
ØExtra：關於MYSQL如何解析查詢的額外信息，主要有以下幾種
nusing index：只用到索引,可以避免訪問表.
nusing where：使用到where來過慮數據. 不是所有的where clause都要顯示using where. 如以=方式訪問索引.
nusing tmporary：用到臨時表
nusing filesort：用到額外的排序. (當使用order by v1,而沒用到索引時,就會使用額外的排序)
nrange checked for eache record(index map:N)：沒有好的索引.
通過慢日志查詢可以知道哪些SQL語句執行效率低下，通過explain我們可以得知SQL語句的具體執行情況，索引使用等，還可以結合show命令查看執行狀態。
如果覺得explain的信息不夠詳細，可以同通過profiling命令得到更准確的SQL執行消耗系統資源的信息。
profiling默認是關閉的。可以通過以下語句查看
打開功能： mysql>set profiling=1; 執行需要測試的sql 語句：
mysql> show profiles\G; 可以得到被執行的SQL語句的時間和ID
mysql>show profile for query 1; 得到對應SQL語句執行的詳細信息
Show Profile命令格式：
SHOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
以上的16rows是針對非常簡單的select語句的資源信息，對於較復雜的SQL語句，會有更多的行和字段，比如converting HEAP to MyISAM 、Copying to tmp table等等，由於以上的SQL語句不存在復雜的表操作，所以未顯示這些字段。通過profiling資源耗費信息，我們可以采取針對性的優化措施。
測試完畢以後 ，關閉參數：mysql> set profiling=0
摘自：軌跡
