來看MySQL內部的觀測，常用的觀測手段是這樣的，從上往下看，第一部分是Processlist，看一下哪個SQL壓力不太正常，第二步是explain，解釋一下它的執行計劃，第三步我們要做Profilling，如果這個SQL能再執行一次的話, 就做一個Profilling，然後高階的DBA會直接動用performance_schema ，MySQL 5.7 以後直接動用sys_schema，sys_schema是一個檢視，裡面有便捷的各類資訊，幫助大家來診斷效能。再高階一點，我們會動用innodb_metrics進行一個對引擎的診斷。

除了這些手段以外，大家還提出了一些亂七八糟的手段，我就不列在這了，這些是常規的一個MySQL的內部的狀態觀測的思路。除了這些以外，MySQL還陸陸續續提供了一些暴露自己狀態的方案，但是這些方案並沒有在實踐中形成套路，原因是學習成本比較高。

1.uptime，uptime告訴我們這個機器活了多久，以及它的平均的負載是多少。2.dmesg -T | tail，告訴我們系統日誌裡邊有沒有什麼報錯。

3.vmstat 1，告訴我們虛擬記憶體的狀態，頁的換進換出有沒有問題，swap有沒有使用。

4. mpstat -P ALL，告訴我們CPU壓力在各個核上是不是均勻的。

5.pidstat 1，告訴我們各個程序的對資源的佔用大概是什麼樣子。

我們來看一下後五條：

首先是iostat-xz 1，檢視IO的問題，然後是free-m記憶體使用率，之後兩個sar，按裝置網絡卡裝置的維度，看一下網路的消耗狀態，以及總體看TCP的使用率和錯誤率是多少。最後一條命令top，看一下大概的程序和執行緒的問題。

這個就是對於外部資源的診斷，這十條命令揭示了應該去診斷哪些外部資源。

1.3 外部需求改造

第三個診斷思路是外部的需求改造，我在這裡引用了一篇文件，這篇文件是MySQL的官方文件中的一章，這一章叫Examples of Common Queries，文件中介紹了常規的SQL怎麼寫, 給出了一些例子。文章的連結二維碼在slide上。

我們來看一下它其中提到的一個例子。

它做的事情是從一個表裡邊去選取，這張表有三列，article、dealer、price，選取每個作者的最貴的商品列在結果集中，這是它的最原始的SQL，非常符合業務的寫法，但是它是個關聯子查詢。

關聯子查詢成本是很貴的，所以上面的文件會教你快速地把它轉成一個非關聯子查詢，大家可以看到中間的子查詢和外邊的查詢之間是沒有關聯性的。

第三步，會教大家直接把子查詢拿掉，然後轉成這樣一個SQL，這個就叫業務改造，前後三個SQL的成本都不一樣，把關聯子查詢拆掉的成本，拆掉以後SQL會跑得非常好，但這個SQL已經不能良好表義了，只有在診斷到SQL成本比較高的情況下才建議大家使用這種方式。

為什麼它能夠把一個關聯子查詢拆掉呢？

這背後的原理是關係代數，所有的SQL都可以被表達成等價的關係代數式，關係代數式之間有等價關係，這個等價關係透過變換可以把關聯子查詢拆掉。

上面的這篇文件是一個大學的教材，它從頭教了關於代數和SQL之間的關係。然後一步步推導怎麼去簡化這句SQL。

第一，MySQL本身提供了很多命令來觀察MySQL自身的各類狀態，大家從上往下檢一般能檢到SQL的問題或者伺服器的問題。

第二，從伺服器的角度，我們從巡檢的指令碼角度入手，伺服器的資源就這幾種，觀測手法也就那麼幾種，我們把伺服器的資源全部都觀察一圈就可以了。

第三，如果實在搞不定，需求方一定要按照資料庫容易接受的方式去寫SQL，這個成本會下降的非常快，這個是常規的MySQL慢的診斷思路。

1、對SQL語句、索引、表結構等進行最佳化。

2、開啟查詢快取，Query Cache快取了SELECT查詢及其結果資料集，當執行一個同樣的SELECT查詢時，MySQL會從記憶體中直接取出結果，加快了查詢執行速度、減小了資料庫的壓力。執行SHOW VARIABLES LIKE "have_query_cache";可以檢視MySQL查詢快取是否開啟，開啟查詢快取只需配置my.cnf檔案即可，具體如下：

query_cache_type = 1

query_cache_size = 128M

query_cache_limit = 1M

儲存好後重啟MySQL。

3、選用InnoDB儲存引擎，MySQL常用儲存引擎是MyISAM和InnoDB，二者區別如下：

MyISAM

查詢速度快；

支援表級鎖，在上鎖期間表上不能進行其他操作；

支援全文檢索；

支援資料壓縮、自我複製、查詢快取、資料加密；

不支援外來鍵；

不支援事務，所以也就沒有COMMIT和ROLLBACK操作；

不支援叢集資料庫。

InnoDB

支援行級鎖；

支援外來鍵，對外來鍵約束強制；

支援事務，可執行COMMIT和ROLLBACK操作；

支援資料壓縮、自我複製、查詢快取、資料加密；

可用在叢集環境，但並不完全支援。InnoDB表可以轉換為NDB儲存引擎，這樣就能用在叢集環境。

準備知識

瞭解select的執行順序有助於理解語句的結果並對其進行最佳化，執行順序如下：

可以透過show status like "XXX", show global status like "XXX"，show variables like "XXX" 檢視很多重要的資料，eg：Connections 連線數，Slow_queries 慢查詢（可以用 show variables like ‘%slow%’來查詢是否開啟慢查詢日誌）等等。

可以透過 desc sql命令 （同樣可是使用explain命令，用法相同）獲取這個查詢的各種屬性，檢查這個語句是否達到效能標準。著重看重點要看這幾列：

rows（影響行數）

select_type（查詢型別，是單表查詢還是多表查詢）

type、possible_key和key（可能用到的索引，以及真正用到索引等）

[ possible_key說明可能用到索引competition_id，但是key為null表明最終沒有使用索引，進行了全表掃描，rows為全表數量 ]

[ 本次查詢是巢狀查詢，兩張表的主鍵都是id，透過desc命令可以看出，player_unique_id的查詢時使用的主鍵索引，影響條數rows為1，但是外部查詢雖然id是主鍵但是沒有使用索引，進行了全表查詢 ]

（1）有些時候即便你加了索引，資料庫查詢的時候也不會使用：

like的%如果只有一個，並且放在開頭，則不會使用索引；eg ‘%user’不會使用索引，但是’user%’,’%user%’都會使用索引；

查詢的時候and和or 如果想使用索引的話需要前後都加索引，如果只有一個則不會使用索引；

如果查詢的時候該列是varchar，但是寫的時候沒有帶引號（寫成2018，而不是’2018’）,則不會使用索引；

反向條件查詢不能使用索引，儘量少用 !=,not in,not exists；

複合索引最左字首，eg: 複合索引(name`, age)：

select * from XXX where age=? and name=? 使用索引

select * from XXX where name=? 使用索引

select from XXX where age=? *不使用索引

（2）儘量避免用巢狀查詢，外層的就算是主鍵也不會使用索引（可以參看準備知識中的例子），可以使用左連線、右連線或者相同的功能的其他寫法代替。

（3）如果明確知道只有一條結果返回，limit 1能夠提高效率

（4）所有不清楚的操作可以透過?查詢，類似linux的man操作，? view 就可以查詢檢視基本語法

（5）開始資料庫慢日誌：

vim /etc/my.cnf 新增log_slow_queries=slow.log 以及long_query_time=5(設定慢日誌的時長)， 然後重啟mysql

綜上，一般的查詢最佳化步驟如下：

（1）檢視慢查詢日誌，或者透過show status命令檢視資料庫各項指標是否正常，其中 show status like ‘%handler_read%’ 中 Handler_read_rnd_next如果很高的話，說明需要檢查索引了，看看是索引加的不對還是用法不對。

（2）找到問題的語句，透過desc定位這個語句到底哪裡有問題，是語句寫法問題、索引問題、還是表結構問題等等。

（3）最佳化語句，重複第1步，直到符合業務需求

可以從以下幾個方面對mysql進行最佳化：

1.對於select * 要時刻保持謹慎的態度

絕大多數情況，是不需要select *的。select * 為全表查詢，建議查詢指定的列資訊

2.count()函式最佳化

count(列名)是不統計值為NULL的欄位的！如果想要統計結果集，就使用count(*)，效能也會很好。

3.合理建立索引

並不是表的索引建立的越多越好

4.儘量不使用子查詢

子查詢在資料量大的情況下，效能會很低

5.儘量使用exist/not exist代替 in/not in

6.能避免使用join的避免使用，越簡單的sql一般效率是最高的