theme: smartblue

本文已參與「掘力星計劃」，贏取創作大禮包，挑戰創作激勵金。

開發俏皮話

【讓我996不算啥，我只怕測試也996給我提bug！】

筆者矚望

你好，無論我們在現實生活中是否相識，在InfoQ的世界裡終會快樂相遇，在此提前預祝國慶節快樂，並且在屬於我們的“1024”那天不在加班，早點回家陪陪老婆和孩子啊。

技術金字塔

本篇文章會按照自上而下以及自下而上的兩種方向去“遊覽”【MySQL技術金字塔】，兩個方向分別是從成本出發的（潛臺詞就是便宜越好，照顧公司成本哦！），本章內容，可能有點多，希望大家慢慢消化，實在不行來片“嗎丁啉”，哈哈，開玩笑了！

技術梗概

主要技術分佈為6大部分，如下圖金子圖所示：

研發成本角度

從軟體的【研發成本】的角度而言：伴隨著優化的方向，從金字塔頂部像金字塔底部的方向進行過度，伴隨著高度越來越低，成本會越來越低，這個方向其實是非常考驗技術人員與專案管理者的能力的，但是它確實，老闆物件看到的，哈哈。

技術可行性和效果角度

從軟體的【技術可行性和效果】的角度而言：伴隨著優化的方向，從金字塔低部像金字塔頂部的方向進行過度，伴隨著高度越來越高，成本會越來越高，耗費的財力和人力也會相對的有所降低，但是如果多花錢，老闆肯定不願意，比如，請一些行業大牛或者一些牛掰的伺服器等，可以看出來正好與上面的方向相反。

總結一下，以上這兩點的方針，遵循著研發成本的越來越低+效果方案越來越高，那麼我們就劃分出一個公式，作為系統服務調優方法論，我們就按照金字塔層面，進行自下而上進行調優！我們接下來就來按照這個方向進行分析。

調優白皮書

業務需求和業務架構

產品中單要支援，需求出裝要全面。——王者榮耀之產品篇

調整一下合適的需求，其實是一個很不錯的方案，所以如果可以從根本上去出發，進行調整需求是一個很有效果的方案哦！並且對一些不合理的需求說不！在做架構設計的時候，應該充分考慮業務實際場景，考慮好資料庫的選項和引入一些其他的方案是非常重要的，例如NoSQL或者NewSQL等。所以，調整好一個一個系統架構是一個非常不錯的方案。

儘量將請求攔截在系統上游傳統業務系統之所以掛，請求都壓倒了後端資料層，資料讀寫鎖衝突嚴重，併發高響應慢，幾乎所有請求都超時，流量雖大，下單成功的有效流量甚小。
讀多寫少的常用多使用快取這是一個典型的讀多寫少的應用場景，非常適合使用快取。

SQL技術調優

根據業務需求，不單純的寫好SQL語句，還要對SQL語句進行調優，使得其效能變得最佳化。

調優的思路

調優主要有三個部分組成：發現問題、分析問題和解決問題。

發現問題（慢SQL的優化和分析）

發現慢SQL以及查詢日誌

查詢慢SQL的日誌是MySQL內建的一個功能，可以記錄執行時間超過我們配置閾值的SQL語句。

引數與預設值：

使用方式-修改MySQL服務配置

一般我們就設定“老三樣”即可！

修改我們安裝後的配置檔案my.cnf，在[mysqld]段落中加入以上引數配置： [mysqld] log_output='FILE,TABLE'; slow_query_log=ON // 代表開啟慢sql引數進行開啟 long_query_time=0.001 //查詢時間（秒）
之後進行重啟服務

service mysqld restart

使用方式-修改全域性服務配置

set global log_output='TABLE,FILE'; set global slow_query_log = 'NO'; set long_query_time = 0.001;

這種方式，不需要重啟就可以生效，但是當伺服器重啟的時候，又會重新丟失配置。

以上的配置可以將慢查詢SQL記錄到mysql資料庫中的slow_log表中以及對應的slow_sql的檔案中去。

分析慢SQ的查詢日誌

查詢slow_log表，當根據上面的設定，當log_output設定為TABLE的時候，就會將mysql的慢查詢日誌記錄到mysql.slow_log表中去，我們可以採用select * from mysql.slow_log去進行查詢，可以根據此方面進行分析和統計sql的執行效能。

分析慢SQL日誌檔案

當log_output設定為FILE的時候，因為檔案過大，不方便檢視，所以可以採用專門的工具進行分析，這裡主要介紹原生的mysqldumpslow工具進行分析，如下圖所示：

mysqldumpslow --help：

使用案例：

如果要查詢出返回結果行數最多的20條SQL： mysqldumpslow -s r -t 20 /path/show.log
根據查詢時間進行排序，並且帶有left join的20條SQL： mysqldumpslow -s t -t -g "left join" /path/show.log

當然還有其他相關的MySQL慢查詢分析日誌工具，例如mysql profiles或者pt-query-digest也可以專門進行分析。有興趣的小夥伴可以搜搜看。

執行計劃分析慢SQL

explain關鍵字進行執行慢SQL語句，進行指標分析：

案例分析

最簡單的案例就是：

explain sql語句

id欄位

它表示代表著語句SQL中每一個部分原子查詢（維護）操作的標識單位，如果explain中的有多個id對應的資料項，那麼切記一定要按照：倒敘進行執行，也就是說：

數字越大的，越先執行分析
數字編號相同，從上到下進行分析

select_type欄位

查詢型別，如下幾組值：

table欄位

它表示當前這一行正在訪問哪張表，如果SQL定義了別名，則展示表的別名

partitions欄位

當前查詢匹配記錄的分割槽。對於未分割槽的表，返回null。

type欄位

連線型別，有如下幾種取值，效能從好到壞排序如下:

system：

該表只有一行(相當於系統表)，system是const型別的特例。

const:

針對主鍵或唯一索引的等值查詢掃描, 最多隻返回一行資料. const查詢速度非常快, 因為它僅僅讀取一次即可。

eq_ref：

當使用了索引的全部組成部分，並且索引是PRIMARY KEY或UNIQUE NOT NULL 才會使用該型別，效能僅次於system及const。

多表關聯查詢，單行匹配

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

多表關聯查詢，聯合索引，多行匹配

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

ref

當滿足索引的最左字首規則，或者索引不是主鍵也不是唯一索引時才會發生。如果使用的索引只會匹配到少量的行，效能也是不錯的。

根據索引(非主鍵，非唯一索引)，匹配到多行

SELECT * FROM ref_table WHERE key_column=expr;

多表關聯查詢，單個索引，多行匹配

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

多表關聯查詢，聯合索引，多行匹配

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

TIPS

最左字首原則，指的是索引按照最左優先的方式匹配索引。比如建立了一個組合索引(column1, column2, column3)，那麼，如果查詢條件是:

WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用該索引;
WHERE column2 = 2、WHERE column2 = 1 AND column3 = 3就無法匹配該索引。

fulltext：全文索引

ref_or_null

該型別類似於ref，但是MySQL會額外搜尋哪些行包含了NULL。這種型別常⻅於解析子查詢。

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge

此型別表示使用了索引合併優化，表示一個查詢裡面用到了多個索引。

unique_subquery

該型別和eq_ref類似，但是使用了IN查詢，且子查詢是主鍵或者唯一索引。例如: value in ( select primary_key from single_table where some_condition)

index_subquery

value in ( select key_column from single_table where some_condition)

和unique_subquery類似，只是子查詢使用的是非唯一索引

range

範圍掃描，表示檢索了指定範圍的行，主要用於有限制的索引掃描。比較常⻅的範圍掃描是帶有 BETWEEN子句或WHERE子句裡有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。

sql SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30);

index

全索引掃描，和ALL類似，只不過index是全盤掃描了索引的資料。當查詢僅使用索引中的一部分列時，可使用此型別。有兩種場景會觸發:

如果索引是查詢的覆蓋索引，並且索引查詢的資料就可以滿足查詢中所需的所有資料，則只掃描索引樹。此時，explain的Extra 列的結果是Using index。index通常比ALL快，因為索引的大小通常小於表資料。
按索引的順序來查詢資料行，執行了全表掃描。此時，explain的Extra列的結果不會出現Uses index。

ALL

全表掃描，效能最差。

possible_keys

展示當前查詢可以使用哪些索引，這一列的資料是在優化過程的早期建立的，因此有些索引可能對於後續優化過程是沒用的。

key

表示MySQL實際選擇的索引

key_len

索引使用的位元組數。由於儲存格式，當欄位允許為NULL時，key_len比不允許為空時大1位元組。

key_len計算公式

可以參考部落格： http://www.cnblogs.com/gomysql/p/4004244.html

ref

表示將哪個欄位或常量和key列所使用的欄位進行比較。如果ref是一個函式，則使用的值是函式的結果。要想檢視是哪個函式，可在EXPLAIN語句之後緊跟一個SHOW WARNING語句。

rows

MySQL估算SQL執行後會掃描的行數，數值越小越好。

filtered

符合查詢條件的資料百分比，最大100。用rows × filtered可獲得和下一張表連線的行數。例如rows = 1000， filtered = 50%，則和下一張表連線的行數是500。

TIPS

在MySQL 5.7之前，想要顯示此欄位需使用explain extended命令; MySQL.5.7及更高版本，explain預設就會展示filtered

Extra(重點分析)

展示有關本次查詢的附加資訊，取值如下:

Child of 'table' pushed join@1

此值只會在NDB Cluster下出現。

const row not found

查詢語句SELECT ... FROM tbl_name，而表是空的

Deleting all rows

對於DELETE語句，某些引擎(例如MyISAM)支援以一種簡單而快速的方式刪除所有的資料，如果使用了這種優化，則顯示此值.

Distinct

查詢distinct值，當找到第一個匹配的行後，將停止為當前行組合搜尋更多行 FirstMatch(tbl_name)當前使用了半連線FirstMatch策略.

Full scan on NULL key

子查詢中的一種優化方式，在無法通過索引訪問null值的時候使用

Impossible HAVING

HAVING子句始終為false，不會命中任何行

Impossible WHERE

WHERE子句始終為false，不會命中任何行

Impossible WHERE noticed after reading const tables

MySQL已經讀取了所有const(或system)表，並發現WHERE子句始終為false LooseScan(m..n)當前使用了半連線LooseScan策略，

No matching min/max row

沒有任何能滿足例如 SELECT MIN(...) FROM ... WHERE condition 中的condition的行 160

no matching row in const table

對於關聯查詢，存在一個空表，或者沒有行能夠滿足唯一索引條件

No matching rows after partition pruning

對於DELETE或UPDATE語句，優化器在partition pruning(分割槽修剪)之後，找不到要delete或update的內容

No tables used

當此查詢沒有FROM子句或擁有FROM DUAL子句時出現。例如:explain select 1

Not exists

MySQL能對LEFT JOIN優化，在找到符合LEFT JOIN的行後，不會為上一行組合中檢查此表中的更多行。例如: SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

MySQL8以前的版本會這麼分析：

例如，t2.id定義成了 NOT NULL ，此時，MySQL會掃描t1，並使用t1.id的值查詢t2中的行。如果MySQL在t2中找到一個匹配的行，它會知道t2.id永遠不會為NULL，並且不會掃描t2中具有相同id值的其餘行。也就是說，對於t1中的每一行，MySQL只需要在t2中只執行一次查詢，而不考慮在t2中實際匹配的行數。

MySQL 8.0.17及更高版本中：

如果出現此提示，還可表示形如 NOT IN (subquery) 或 NOT EXISTS (subquery) 的WHERE條件已經在內部轉換為反連線。
這將刪除子查詢並將其表放入最頂層的查詢計劃中，從而改進查詢的開銷。通過合併半連線和反聯接，優化器可以更加自由地對執行計劃中的表重新排序，在某些情況下，可讓查詢提速。

可以通過在EXPLAIN語句後緊跟一個SHOW WARNING語句，並分析結果中Message列，從而檢視何時對該查詢執行了反聯接轉換。

反連線：兩表關聯只返回主表的資料，並且只返回主表與子表沒關聯上的資料，這種的連線方式。

Plan isn't ready yet

使用了EXPLAIN FOR CONNECTION，當優化器尚未完成為在指定連線中為執行的語句建立執行計劃時，就會出現此值。

Range checked for each record (index map: N)
- MySQL沒有找到合適的索引去使用，但是去檢查是否可以使用range或index_merge來檢索行時，會出現此提示。
- index map N索引的編號從1開始，按照與表的SHOW INDEX所示相同的順序。索引對映值N是指示哪些索引是候選的位掩碼值。例如0x19(二進位制11001)的值意味著將考慮索引1、4和5。

好了看到這裡你是否會覺得已經眼花繚亂了？現在開始重頭戲，上面的可以作為知識擴充套件和了解，但下面的內容建議你一定要理解哦，會對效能優化有很大的幫助哦！

unique row not found

對於形如 SELECT ... FROM tbl_name 的查詢，但沒有行能夠滿足唯一索引或主鍵查詢的條件。

Using filesort（重點）
- 出現的原因：當SQL查詢中包含 ORDER BY子句的操作後，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應的排序演算法來實現。
- 資料較少時從記憶體排序，當超過Memory_Sort的閾值的時候就會從磁碟排序，效能超級低哦！
- 並且，Explain命令並不會顯示的告訴MySQL資料庫客戶端用哪種排序。

官方解釋：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。

通過根據聯接型別瀏覽所有行，併為所有匹配WHERE子句的行儲存排序關鍵字和行的指標來完成排序。
然後關鍵字被排序，並按排序順序檢索行。
Using index（重點）
- (俗稱：單覆蓋索引哦！)，僅使用索引樹中的資訊從表中檢索列資訊，而不必進行其他查詢以讀取實際行。
- 當查詢僅使用屬於單個索引的列時，可以使用此策略。例如: select id from table
- Using index condition（重點）
- (俗稱：覆蓋下推哦！)，表示先按條件過濾索引，過濾完索引後找到所有符合索引條件的資料行，隨後用 WHERE 子句中的其他條件去過濾這些資料行。通過這種方式，除非有必要，否則索引資訊將可以延遲“下推”讀取整個行的資料。例如: SELECT * FROM people where id > 10 and age = 10 or address > 100000 ; 可以通過開關進行調整開或者關閉索引條件下推 SET optimizer_switch = 'index_condition_pushdown=off'; SET optimizer_switch = 'index_condition_pushdown=on';

多說一句，MySQL分成了Server層和Engine層，下推指的是將請求交給引擎層處理，相比較而言效能差異有不同的引擎決定。

Using index for group-by（次重點）

資料訪問和 Using index 一樣，所需資料只須要讀取索引，當Query 中使用GROUP BY或DISTINCT 子句時，如果分組欄位也在索引中，Extra中的資訊就會是 Using index for group-by，例如：

explain SELECT name FROM t1 group by name

Using index for skip scan（不是特別重要，記住它算是SQL索引其效果了）

表示使用了Skip Scan。底層採用了“Skip Scan Range Access Method演算法機制”，意思說是提前做出了索引查詢定位，並且減少了很多的掃描，其實和using Index區別不大！

Using join buffer (Block Nested Loop)，不是特別重要，表示採用了巢狀子查詢的快取批次處理技術！

Using join buffer 使用Block Nested Loop或Batched Key Access演算法提高join的效能，此部分屬於採用了Batched Key Access（批次關鍵字進行檢索），意思是不會再進行一行一行對比，而是進行一批一批的方式進行比較，並且他們的記憶體行類似相鄰的位置，所以採用buffer快取快的機制快取這一批次對比檢索的資料，大大提高了效率！

具體有興趣的小夥伴可以推薦參考： http://www.cnblogs.com/chenpingzhao/p/6720531.html

Using MRR

使用了Multi-Range Read優化策略。詳⻅ “Multi-Range Read Optimization” Using sort_union(...), Using union(...), Using intersect(...)，這些指示索引掃描如何合併為index_merge連線型別。詳⻅參考官方的“Index Merge Optimization” 。

Using temporary（非常重要）

上面說了當出現排序或者分組的時候資料需要進行進一步的計算，此時無法利用索引那天然的資料模型來解決的時候該咋辦!為了解決該查詢，MySQL需要建立一個臨時表來儲存結果。如果查詢包含不同列的GROUP BY和 ORDER BY子句，通常會發生這種情況。

name欄位有索引
- explain SELECT name FROM t1 group by name
name無索引
- explain SELECT name FROM t1 group by name。
Using where（比較重要）

如果我們不是讀取表的所有資料，或者不是僅僅通過索引就可以獲取所有需要的資料，則會出現using where資訊。閾值相對應的就是“覆蓋索引哦”！

SELECT * FROM t1 where id = 1

Zero limit

該查詢有一個limit 0子句，不能選擇任何行。

explain SELECT name FROM resource_template limit 0

Explain的命令講解

EXPLAIN可產生額外的擴充套件資訊，可通過在EXPLAIN語句後緊跟一條SHOW WARNING語句檢視擴充套件資訊。

在MySQL 8.0.12及更高版本，擴充套件資訊可用於SELECT、DELETE、INSERT、REPLACE、UPDATE語句;
在MySQL 8.0.12之前，擴充套件資訊僅適用於SELECT語句;
在MySQL 5.6及更低版本，需使用EXPLAIN EXTENDED xxx語句;而從MySQL 5.7開始，無需新增 EXTENDED關鍵詞。

SHOW WARNING的結果並不一定是一個有效SQL，也不一定能夠執行(因為裡面包含了很多特殊標記)

特殊取值含義介紹

：自動生成的臨時表key。
(expr)：表示式(例如標量子查詢)執行了一次，並且將值儲存在了記憶體中以備以後使用。對於包括多個值的結果，可能會建立臨時表，你將會看到的字樣。
(query fragment)：子查詢被轉換為 EXISTS，效能會變得更加好。
(query fragment)：這是一個內部優化器物件，對使用者沒有任何意義
(query fragment)：使用索引查詢來處理查詢片段，從而找到合格的行
(condition, expr1, expr2)：如果條件是true，則取expr1，否則取expr2
(expr)：驗證表示式不為NULL的測試
(query fragment)：使用子查詢實現
materialized-subquery.col_name，在內部物化臨時表中對col_name的引用，以儲存子查詢的結果
(query fragment)：使用主鍵來處理查詢片段，從而找到合格的行
(expr)：這是一個內部優化器物件，對使用者沒有任何意義
/ select#N / select_stmt：SELECT與非擴充套件的EXPLAIN輸出中id=N的那行關聯
outer_tables semi join (inner_tables)：半連線操作。
：表示建立了內部臨時表而快取中間結果

估計查詢效能

多數情況下，你可以通過計算磁碟的搜尋次數來估算查詢效能。對於比較小的表，通常可以在一次磁碟搜尋中找到行 (因為索引可能已經被快取了)，而對於更大的表，你可以使用B-tree索引進行估算:你需要進行多少次查詢才能找到行: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 在MySQL中，index_block_length通常是1024位元組，資料指標一般是4位元組。比方說，有一個500,000的表，key是3字節，那麼根據計算公式 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜尋。該索引將需要500,000 7 3/2 = 5.2MB的儲存空間(假設典型的索引快取的填充率是2/3)，因此你可以在記憶體中存放更多索引，可能只要一到兩個呼叫就可以找到想要的行了。但是，對於寫操作，你需要四個搜尋請求來查詢在何處放置新的索引值，然後通常需要2次搜尋來更新索引並寫入行。前面的討論並不意味著你的應用效能會因為log N而緩慢下降。只要內容被OS或MySQL伺服器快取，隨著表的變大，只會稍微變慢。在資料量變得太大而無法快取後，將會變慢很多，直到你的應用程式受到磁碟搜尋約束(按照log N增⻓)。為了避免這種情況，可以根據資料的增⻓而增加key的。對於MyISAM表，key的快取大小由名為key_buffer_size 的系統變數控制，詳⻅ Section 5.1.1, “Configuring the Server”

SQL效能分析

SQL效能分析的手段我們主要介紹一下三種： - SHOW PROFILE - INFORMATION_SCHEMA.PROFILING - PERFORMANCE_SCHEMA

SHOW PROFILE（舊版本的MySQL服務可使用,新版本已廢棄）

SHOW PROFILE是MySQL的一個性能分析命令，可以跟蹤SQL各種資源消耗。使用格式如下:

SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] - type的選值範圍： - ALL：顯示所有資訊 - BLOCK IO：顯示阻塞的輸入輸出次數 - CONTEXT SWITCHES：顯示自願及非自願的上下文切換次數 - CPU：顯示使用者與系統CPU使用時間 - IPC：顯示訊息傳送與接收的次數 - MEMORY：顯示記憶體相關的開銷，目前未實現此功能 - PAGE FAULTS：顯示⻚錯誤相關開銷資訊 - SOURCE：列出相應操作對應的函式名及其在原始碼中的位置(行) - SWAPS：顯示swap交換次數

預設情況下，SHOW PROFILE只展示Status和Duration兩列，如果想展示更多資訊，可指定type，使用步驟如下:

檢視是否支援SHOW PROFILE功能，yes標誌支援。從MySQL 5.0.37開始，MySQL支援SHOW PROFILE。

select @@have_profiling;

檢視當前是否啟用了SHOW PROFILE，0表示未啟用，1表示已啟用 select @@profiling;
設定為當前會話開啟或關閉效能分析，設成1表示開啟，0表示關閉

set profiling=1

為最近傳送的SQL語句做一個概要的效能分析。展示的條目數目由 profiling_history_size會話變數控制，該變數的預設值為15。最大值為100。將值設定為0具有禁用分析的實際效果。
Show profiles 命令

sql -- 預設展示15條 show profiles -- 使用profiling_history_size調整展示的條目數 set profiling_history_size = 100;

首先使用show profiles分析指定查詢:

使用show profile進行分析，預設情況下，只展示Status和Duration兩列，如果想展示更多資訊，可指定type。

使用SHOW PROFILE FOR QUERY 1;，1代表的query_id(show profiles)

展示CPU相關的開銷

分析完成後，記得關閉掉SHOW PROFILE功能: set profiling = 1

NFORMATION_SCHEMA.PROFILING

INFORMATION_SCHEMA.PROFILING用來做效能分析，它的內容對應SHOW PROFILE和SHOW PROFILES 語句產生的資訊。除非設定了 set profiling = 1; ，否則該表不會有任何資料。

該表包括以下欄位:

QUERY_ID:語句的唯一標識
SEQ:一個序號，展示具有相同QUERY_ID值的行的顯示順序
STATE:分析狀態
DURATION:在這個狀態下持續了多久(秒)
CPU_USER，CPU_SYSTEM:使用者和系統CPU使用情況(秒)
CONTEXT_VOLUNTARY，CONTEXT_INVOLUNTARY:發生了多少自願和非自願的上下文轉換
BLOCK_OPS_IN，BLOCK_OPS_OUT:塊輸入和輸出操作的數量
MESSAGES_SENT，MESSAGES_RECEIVED:傳送和接收的訊息數 PAGE_FAULTS_MAJOR，PAGE_FAULTS_MINOR:主要和次要的⻚錯誤資訊
SWAPS:發生了多少SWAP SOURCE_FUNCTION，SOURCE_FILE，SOURCE_LINE:當前狀態是在原始碼的哪裡執行的

SHOW PROFILE本質上使用的也是INFORMATION_SCHEMA.PROFILING表;

INFORMATION_SCHEMA.PROFILING表已被廢棄，在未來可能會被刪除。未來將可使用Performance Schema替代，

採用show profile方式進行查詢

sql SHOW PROFILE FOR QUERY 2;

INFORMATION_SCHEMA.PROFILING的查詢方式

sql SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;

PERFORMANCE_SCHEMA(未來的繼承者)

PERFORMANCE_SCHEMA是MySQL建議的效能分析方式，未來SHOW PROFILE/PROFILES、 INFORMATION_SCHEMA.PROFILING都會廢棄。

PERFORMANCE_SCHEMA在MySQL 5.6引入，因此，在MySQL 5.6及更高版本才能使用。可使用 SHOW VARIABLES LIKE 'performance_schema';

sql SHOW VARIABLES LIKE 'performance_schema';

下面來用PERFORMANCE_SCHEMA去實現SHOW PROFILE類似的效果: 檢視是否開啟效能監控

檢視啟用情況，MySQL 5.7開始預設啟用。

你也可以執行類似如下的SQL語句，只監控指定使用者執行的SQL:

這樣，就只會監控localhost機器上test_user使用者傳送過來的SQL。其他主機、其他使用者發過來的SQL統統不監控，執行如下SQL語句，開啟相關監控項:

使用開啟監控的使用者，執行SQL語句，比如:

執行如下SQL，獲得語句的EVENT_ID。

這一步類似於 SHOW PROFILES。執行如下SQL語句做效能分析，這樣就可以知道這條語句各種階段的資訊了。

MySQL官方文件宣告SHOW PROFILE已被廢棄，並建議使用Performance Schema作為替代品。

三種方式對比與選擇

SHOW PROFILE:簡單、方便，已廢棄
INFORMATION_SCHEMA.PROFILING，它和SHOW PROFILE本質一樣
PERFORMANCE_SCHEMA:未來之光，但目前來說使用不夠方便

因此:目前可以繼續用SHOW PROFILE瞭解PERFORMANCE_SCHEMA，為未來做好準備

OPTIMIZER_TRACE相關引數

optimizer_trace總開關，預設值: enabled=off,one_line=off enabled:是否開啟optimizer_trace;on表示開啟，off表示關閉。
one_line:是否開啟單行儲存。on表示開啟;off表示關閉，將會用標準的JSON格式化儲存。設定成on將會有良好的格式，設定成off可節省一些空間。
optimizer_trace_features：控制optimizer_trace跟蹤的內容，預設值:greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on greedy_search:是否跟蹤貪心搜尋。
range_optimizer:是否跟蹤範圍優化器 dynamic_range:是否跟蹤動態範圍優化，表示開啟所有跟蹤項。
repeated_subselect:是否跟蹤子查詢，如果設定成off，只跟蹤第一條Item_subselect的執行。
optimizer_trace_limit:控制optimizer_trace展示多少條結果，預設1
optimizer_trace_max_mem_size:optimizer_trace堆疊資訊允許的最大記憶體，預設1048576
optimizer_trace_offset:第一個要展示的optimizer trace的偏移量，預設-1。
end_markers_in_json:如果JSON結構很大，則很難將右括號和左括號配對。為了幫助讀者閱讀，可將其設定成 on，這樣會在右括號附近加上註釋，預設off。

總結分析

具體的分析效能介紹後續會在【舉世無雙的「MySQL調優金字塔」相信也許你擁有了它，你就很可能擁有了全世界。】進行深入介紹，此外還會伴有對索引原理的深入理解和分析。

獨一無二的「MySQL調優金字塔」相信也許你擁有了它，你就很可能擁有了全世界。

theme: smartblue

開發俏皮話

筆者矚望

技術金字塔

技術梗概

研發成本角度

技術可行性和效果角度

調優白皮書

業務需求和業務架構

SQL技術調優

調優的思路

發現問題（慢SQL的優化和分析）

發現慢SQL以及查詢日誌

使用方式-修改MySQL服務配置

使用方式-修改全域性服務配置

分析慢SQ的查詢日誌

分析慢SQL日誌檔案

使用案例：

執行計劃分析慢SQL

案例分析

id欄位

select_type欄位

table欄位

partitions欄位

type欄位

system：

const:

eq_ref：

多表關聯查詢，單行匹配

多表關聯查詢，聯合索引，多行匹配

ref

根據索引(非主鍵，非唯一索引)，匹配到多行

多表關聯查詢，單個索引，多行匹配

多表關聯查詢，聯合索引，多行匹配

TIPS

fulltext：全文索引

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

possible_keys

key

key_len

key_len計算公式

ref

rows

filtered

TIPS

Extra(重點分析)

Explain的命令講解

特殊取值含義介紹

估計查詢效能

SQL效能分析

SHOW PROFILE（舊版本的MySQL服務可使用,新版本已廢棄）

NFORMATION_SCHEMA.PROFILING

該表包括以下欄位:

採用show profile方式進行查詢

INFORMATION_SCHEMA.PROFILING的查詢方式

PERFORMANCE_SCHEMA(未來的繼承者)

三種方式對比與選擇

OPTIMIZER_TRACE相關引數

總結分析