在平時被問及最多的問題就是關于 mysql 數據庫mysql方面的問題，所以最近打算寫一個mysql性能優化方面的系列文章，希望對初中級 mysql dba 以及其他對 mysql 性能優化感興趣的朋友們有所幫助。
高興的是有博友mark了我的文章。我知道mark之后，很少會再來繼續關注的。但是從側面說明了在博友點開博客的同時，他感覺這篇博客是有價值的，是能夠彌補他的知識欠缺。一篇博客最重要的是對自己有用，如果再對別人有用，那是最好的結果。我堅持寫博客的目的是為了當自己遺忘知識點的時候，能夠最快的找到靠譜的解決方案。當自己的歸納的知識，再記起來就會遺忘的慢一點，等時間久了，這部分知識終于化成了自己脫口而出的話，那就再也不怕遺忘了。這篇博客將繼續講MySQL的內容，這篇講mysql優化，講的過程也是我學習的過程。
先來看下我們mysql的版本，我的mac 上裝的版本是5.7的，很多內容都已經變化掉了。這里講的主要是5.6的版本。
[root@roverliang?~]#?mysql?--version mysql?Ver?14.14?Distrib?5.6.24,?for?Linux?(x86_64)?using?EditLine?wrapper
一、MySQL緩存分類
MySQL的優化指的是一個很大的系統，面試的時候我之前是從sql的語句優化方面去說的，這種優化也有作用，不過是從邏輯方面去優化。但是當所有的邏輯層面已經無可優化，所有的mysql都已經加好，表結構也設計的合理，但是遇到高并發的時候，為什么MySQL還是扛不住呢。當然可以通過其他的方面去緩解MySQL的壓力，這里我們暫且不談。對于MySQL而言，我們要盡最大的可能去壓榨機器的性能，讓所有的計算資源都不浪費，都可以為我們服務。MySQL運行在服務器上，這里特指Linux服務器。那么服務器的硬盤、CPU,內存，網絡都有影響到MySQL的性能。MySQl是非常耗費內存的，線上服務器的MySQL內存要吃到80%左右，內存過小，其他的優化空間其實很小。
另外連接（connection）也是影響MySQL性能的重要一方面。MySQL客戶機與MySQL服務器之間的連接是MySQL客戶機與MySQL服務器反復握手的結果。每次’握手’都經歷身份驗證、權限驗證等環節，握手需要占用一定的網絡資源和MySQL服務器內存資源。
不得不提的是鎖競爭，對于并發性能要求比較高的數據庫而言，如果存在激烈的鎖競爭，對數據庫的性能將是很大的打擊。鎖競爭會明顯的增加線程上下文切換的開銷，這些開銷都與預期的需求無關。
二、show status 與 show variables
在MySQL系列的前幾篇博客，會經常的看到這些命令，那么我們分別看下，這兩個命令給MySQL系統管理員展示的是什么信息：
show status
MySQL服務運行的時候，MySQL服務實例的mysql信息是動態的。用該命令可以顯示當前MySQL服務器連接的會話狀態mysql信息。默認情況下mysql首字母大寫。
show variables
show variables 用來顯示MySQL 服務實例的各種mysql(如:全局系統變量，會話系統變量，mysql變量)，這些變量包含MySQL編譯時參數的默認值，或者是my.cnf中設置的參數值。系統變量或者參數是一個靜態的概念，默認情況下系統變量名都是小寫字母。
mysql命令show status 或者 show mysql status ,可以查看當前MySQL 服務器連接的會話變量信息，會話狀態的變量值對當前的MySQL客戶機有效，例如：Opened_tables、Opened_table_definitions狀態變量。
緩存機制
緩存之所以有效，主要是因為程序運行時對內存或者外存的訪問呈現局部性特征，局部性特征為空間局部性和時間局部性兩方面。時間局部性是指剛剛訪問過的數據近期可能再次被訪問，空間局部性是指，某個位置被訪問后，其相鄰的位置的數據很可能被訪問到。而MySQL的緩存機制就是把剛剛訪問的數據（時間局部性）以及未來即將訪問到的數據（空間局部性）保存到緩存中，甚至是高速緩存中。從而提高I/O效率。
按照緩存讀寫功能的不同，MySQL將緩存分為Buffer緩存和mysql緩存。
Buffer緩存。由于硬盤的寫入速度過慢，或者頻繁的I/O，對于硬盤來說是極大的效率浪費。那么可以等到緩存中儲存一定量的數據之后，一次性的寫入到硬盤中。Buffer 緩存主要用于寫數據，提升I/O性能。
Cache 緩存。 Cache 緩存一般是一些訪問頻繁但是變更較少的數據，如果Cache緩存已經存儲滿，則啟用LRU算法，進行數據淘汰。淘汰掉最遠未使用的數據，從而開辟新的存儲空間。不過對于特大型的網站，依靠這種策略很難緩解高頻率的讀請求，一般會把訪問非常頻繁的數據靜態化，直接由nginx返還給用戶。程序和數據庫I/O設備交互的越少，則效率越高。
三、MySQL 超時
在使用MySQL的過程中，可能會出現各種超時（mysqlout）異常，典型的有連接超時、鎖等待等。
查看超時時間的類型有哪些：
mysql>?show?variables?like?'%timeout%'; +-----------------------------+----------+ |?Variable_name????????|?Value??| +-----------------------------+----------+ |?connect_timeout???????|?10????| |?delayed_insert_timeout???|?300???| |?innodb_flush_log_at_timeout?|?1????| |?innodb_lock_wait_timeout??|?50????| |?innodb_rollback_on_timeout?|?OFF???| |?interactive_timeout?????|?28800??| |?lock_wait_timeout??????|?31536000?| |?net_read_timeout??????|?30????| |?net_write_timeout??????|?60????| |?rpl_stop_slave_timeout???|?31536000?| |?slave_net_timeout??????|?3600???| |?wait_timeout????????|?28800??| +-----------------------------+----------+
1、連接超時（connect_timeout）
connect_timeout默認為10s,獲取MySQL連接是客戶機與服務器之間握手的結果，并且是多次握手的結果，每次握手，除了驗證賬戶名和身份信息外，還需要驗證主機、域名解析。如果客戶機和服務器之間存在網絡故障，可以通過connect_timeout參數來設置，防止它們之間重復握手。
interactive_timeout指的是交互式的終端，在命令行中輸入的這種。超過了其設置的默認值就會斷開。
wait_timeout指的是非交互式的終端，比如PHP實例化的Mysql連接，一直占用著，超過了這個參數設置的值，就會自動斷開。
net_write_timeout MySQL服務器產生一個很大的mysql，MySQL客戶機在該值設置的時間內不能接受完畢，則會斷開連接。
net_read_timeout MySQL客戶機讀取了一個很大的數據，在設置值內不能讀取完畢，則會自動斷開連接。
InnoDB鎖等待超時
mysql>?show?variables?like?'innodb_lock_wait_timeout'; +--------------------------+-------+ |?Variable_name??????|?Value?| +--------------------------+-------+ |?innodb_lock_wait_timeout?|?50??| +--------------------------+-------+
InnoDB 的鎖等待時間默認為50s,設置行級鎖鎖等待的值，當出現鎖等待的時候，等待時長超過該值會導致鎖等待的SQL回滾（不是整個事務回滾）。如果希望整個事務回滾，需要開啟innodb_rollback_on_timeout參數。
mysql>?show?variables?like?'%rollback%'; +----------------------------+-------+ |?Variable_name???????|?Value?| +----------------------------+-------+ |?innodb_rollback_on_timeout?|?OFF??| |?innodb_rollback_segments??|?128??| +----------------------------+-------+
innodb_rollback_on_timeout設置為true 后，遇到事務超時，會回滾整個事務的操作。
復制連接超時
當主從配置是，從服務器（slave）從主服務器（master）讀取二進制日志失敗后，從服務器會等待 slave_net_timeout 后，從新從master機拉去二進制日志。可以設置成10s.
mysql>?show?variables?like?'slave_net_timeout'; +-------------------+-------+ |?Variable_name???|?Value?| +-------------------+-------+ |?slave_net_timeout?|?3600?| +-------------------+-------+
這部分總結，應該是周日晚上就該整理好的，結果拖到了今天。后面的計劃又要后延了，拖延癥真嚴重。
