從某次測試過程中，得到的MySQL效能最佳化的建議，和定位問題的方法

MySQL版本5.5

某次測試最佳化過程中碰到的問題和解決問題的方法，方案。以供各位參考。
一，表結構的最佳化
問題1，表與欄位使用不同的字元集會導致索引失效
我在自己的機器上，沒試出來。可能與記錄數太少有關。有時，記錄數太少，得出的結果有
些不一致。但此問題，是存在的。
問題2，表欄位太長，會導致order by時索引失效
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`(767))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select * from t2 order by name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using filesort
1 row in set (0.00 sec)
修改後：
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select * from t2 order by name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: index
possible_keys: NULL
key: idx_name
key_len: 103
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
問題3，表的一行記錄的字元數太長，排序異常慢
當表的一行的記錄數超過1K, max_length_for_sort_data預設值時，使用order by會導致排序
異常的慢。當存在這種資料表時，請設定max_length_for_sort_data=2K或更高。
對於這種情況，可以使用explain去尋找錯誤原因。當出現此情況時，會發現explain的顯示
中有如下內容
Extra: Using filesort
問題4，錯誤使用MyISAM引擎，查詢速度異常慢
由於，測試專案只使用Innodb引擎，在設定檔中關閉了關於MyISAM引擎的相關配置。
匯入資料時，由於錯誤使用了Innodb引擎，導致查詢很慢。
因為剛開始，我並不知道原因是使用了MyISAM引擎，我通過如下的命令，快速找到了查
詢慢的語句。再通過show create table t_corpcustomize 即找到了錯誤的表。
關於查詢慢，可以通過show full processlist，查看，你可以看到類似如下的查詢語句
| Id | User | Host | db | Command | Time | State | Info
| 67 | root | linux70:55737 | bi | Execute | 0 | Sending data | select whoset, specialphone,
settype, begintime,endtime from t_corpcustomize where status = 1
二，伺服器參數最佳化配置
一，去除所有不用的功能
1. 因為沒用query cache, 去除配置如下
query_cache_type=0
query_cache_size = 0
2. 因為沒用MyISAM, 去除配置如下
key_buffer_size=8M
其它所有的都注釋掉
二，停止所有與MySQL相關的服務
測試過程中，發現MySQL機器上，還有oracle, java, tomcat進程在運行。
雖然，他們沒有業務，但使用了不少記憶體。去除後，效能得到了一些提升。
三，參數合理調整
一，調整與記憶體有關的配置
innodb_file_per_table = true
# Data files must be able to hold your data and indexes
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
innodb_buffer_pool_size=5G
#When innodb_buffer_pool_size > 1G, set this parameter to divide the buffer pool size maybe just
above 1G
innodb_buffer_pool_instances = 4
innodb_additional_mem_pool_size=20M
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size = 2047M
innodb_log_files_in_group = 2
innodb_log_buffer_size=8M
max_connection
*
(
#順序讀時使用，一般不用太大
read_buffer_size = 4M
#隨機讀時使用，當有大量order by, group by操作時加大
read_rnd_buffer_size = 32M
#排序時使用， 當有大量order by, group by操作時加大
sort_buffer_size = 64M
#一般不用太大，有複雜的join操作時加大
join_buffer_size = 8M
#一般不用太大，預設即可
thread_stack = 192K
#使用binlog時，配置，一般預設即可
binlog_cache_size
)
二，調整與I/O，檔案,log緩衝等有關的配置
1. table_open_cache 調整
一般來說，可以在phpmyadmin中查看Open_tables與Opened_tables的值，也可以執行
1 mysql> show global status like 'open%_tables';
查看當前的open_tables情況
來查看這兩個參數的值。其中Open_tables是當前正在開啟表的數量，Opened_tables是所有
已經開啟表的數量。
如果Open_tables的值已經接近table_cache的值，且Opened_tables還在不斷變大，則說明
mysql正在將緩衝的表釋放以容納新的表，此時可能需要加大table_cache的值。對於大多數
情況，
比較適合的值：
Open_tables / Opened_tables >= 0.85
Open_tables / table_cache <= 0.95
2. I/O 調整
#如下兩個參數，一般設定為cpu個數即可
innodb_write_io_threads = 4
innodb_read_io_threads = 4
#下面設定為cpu個數*2
innodb_thread_concurrency = 8
#io capacity，這個參數需要根據IOPS來進行設定，需要查看硬體手冊。
3. log參數調整
#log flush mode，見參考文檔，通常在linux設為O_DIRECT,
http://dev.mysql.com/doc/refman/5.5/en/innodbparameters.
html#sysvar_innodb_flush_log_at_trx_commit
innodb_flush_method=O_DIRECT
#1=保證事務的一致性，每次commit重新整理到磁碟，0=緩衝起來，每秒重新整理磁碟，2=寫入檔案，
再每秒重新整理到磁碟。推薦為2，配置為2時，效能會有不少提升，而只會在掉電時，出現事
務丟失的情況。
innodb_flush_log_at_trx_commit=1
三，調整隔離等級
因為oracle預設隔離等級為READ-COMMITTED, 而mySQL預設為REPEAT-READ。不同的
隔離等級對於效能是有較大的出入的。因為其對於並發的控制的嚴格度不同，換句話說低的
隔離等級，需要應用程式多做很多工作保證一致性，而高的就由資料庫本身來保證。READCOMMITTED
其實也沒太多影響，只會帶來幻影資料問題。MySQL關於READCOMMITTED
的安全配置如下。
transaction_isolation = READ-COMMITTED
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog = 1
innodb_support_xa = 0
四，把Innodb data, tablespace, log files儲存於不同磁碟
經過測試，我發現系統的瓶頸是I/O。如下，通過iostat的操作，可以看到高峰時每秒io r/s
讀65, w/s寫437.67個。
系統效能在目前的位置無法提升，原因是MySQL總在等待I/O的操作。
evice: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 7.00 4.33 4.33 0.16 0.04 48.92 0.16 18.00 4.00 3.47
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdf 0.67 158.67 65.00 437.67 0.68 9.80 42.73 2.11 4.21 0.55 27.73
sdg 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
VxVM56000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 65.67 589.00 0.68 9.80 32.81 3.56 5.44 0.43 27.87

所以，建議把data file, innodb space file, innodb log file分成三個不同的盤，看效能是否有提
升。
三，常用定位問題方法
一，mysql>explain
詳見如下文檔：http://docs.oracle.com/cd/E17952_01/refman-5.5-en/explain-output.html
mysql> EXPLAIN SELECT * FROM students WHERE name='name1' or sex=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: index_name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
二，mysql>show full processlist
見第一章，問題4
三，slow log
1. 配置如下
slow_query_log=1
slow_query_log_file=/opt/upora/slow.log
#超過long_query_time的SQL寫入日誌，時間為秒
long_query_time = 1
#未使用索引的SQL寫入日誌
log-queries-not-using-indexes
2. slow log內容如下
例如：
use cust;
SET timestamp=1356921142;
select * from cust_contact ct where ct.custid in
(select c.custid from customer c, cust_group g,(select groupid from groups_defin
e where name='ROOT-P3') gr where c.custid=g.custid and g.groupid =gr.groupid);
3. 用mysqldumpslow工具統計
例如：
#mysqldumpslow slow.log
Count: 1 Time=1.06s (1s) Lock=0.02s (0s) Rows=7.0 (7), root[root]@localhost
select count(*), engine,data_length from information_schema.tables group by engine
四，mysql> show global status
這個命令得到的資料會比較多，不太直觀，需要計算後才能得出結論。
下面列出了常用的一些計算選項。
(1)QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql > show global status like 'Question%';
(2)TPS(每秒事務量)
TPS = (Com_commit + Com_rollback) / seconds
mysql > show global status like 'Com_commit';
mysql > show global status like 'Com_rollback';
(3)key Buffer 命中率
mysql>show global status like 'key%';
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
(4)InnoDB Buffer命中率
mysql> show status like 'innodb_buffer_pool_read%';
innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) *
100%
(5)Query Cache命中率
mysql> show status like 'Qcache%';
Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
(6)Table Cache狀態量
mysql> show global status like 'open%';
比較 open_tables 與 opend_tables 值
(7)Thread Cache 命中率
mysql> show global status like 'Thread%';
mysql> show global status like 'Connections';
Thread_cache_hits = (1 - Threads_created / connections ) * 100%
(8)鎖定狀態
mysql> show global status like '%lock%';
Table_locks_waited/Table_locks_immediate=0.3% 如果這個比值比較大的話，說明表鎖造成的
阻塞比較嚴重
Innodb_row_lock_waits innodb行鎖，太大可能是間隙鎖造成的
五，mysql>show engine innodb status
這個命令可以統計innodb的資訊，還可以定位如死結等問題。
具體見：http://docs.oracle.com/cd/E17952_01/refman-5.5-en/innodb-monitors.html
六，iostat, top, vmstat
此可以用來，查看cpu, 記憶體的使用方式。這也可算是所有資料庫尋找效能瓶頸的方法。在此
不再多述

本文出自 “MySQL 初學者到專家之路” 部落格，請務必保留此出處http://aichilu.blog.51cto.com/8381303/1342731