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

語言: CN / TW / HK

theme: smartblue

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

開發俏皮話

讓我996不算啥,我只怕測試也996給我提bug!

筆者矚望

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

技術金字塔

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

技術梗概

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


研發成本角度

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

技術可行性和效果角度

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

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

調優白皮書

業務需求和業務架構

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

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

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

SQL技術調優

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

調優的思路

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

發現問題(慢SQL的優化和分析)

發現慢SQL以及查詢日誌

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

參數與默認值:

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

一般我們就設置“老三樣”即可!

  1. 修改我們安裝後的配置文件my.cnf,在[mysqld]段落中加入以上參數配置: [mysqld] log_output='FILE,TABLE'; slow_query_log=ON // 代表開啟慢sql參數進行開啟 long_query_time=0.001 //查詢時間(秒)

  2. 之後進行重啟服務

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:

使用案例:
  1. 如果要查詢出返回結果行數最多的20條SQL: mysqldumpslow -s r -t 20 /path/show.log

  2. 根據查詢時間進行排序,並且帶有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計算公式

可以參考博客: https://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需要額外的一次傳遞,以找出如何按排序順序檢索行。

  1. 通過根據聯接類型瀏覽所有行,併為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。
  2. 然後關鍵字被排序,並按排序順序檢索行。

  3. 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緩存快的機制緩存這一批次對比檢索的數據,大大提高了效率!

具體有興趣的小夥伴可以推薦參考: https://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調優金字塔」相信也許你擁有了它,你就很可能擁有了全世界。】進行深入介紹,此外還會伴有對索引原理的深入理解和分析。

「其他文章」