共計 3009 個字符，預計需要花費 8 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
丸趣 TV 小編給大家分享一下 mysql 之調(diào)優(yōu)概論的案例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
一 簡介
咱們先不說 cpu 的頻率，內(nèi)存的大小（這個和索引一樣重要，但不是本文討論的內(nèi)容），硬盤的尋道時間。想起 mysql 的調(diào)優(yōu)，最起碼的必須知道 explain 執(zhí)行計劃，慢 sql 日志，老舊的 profile 命令，新的 performance_schema 性能視圖和 information_schema 中當前事務和內(nèi)存占用信息的相關表，還有 show engine innodb status 的診斷信息，以及某些 metrix 中的 tps，qps,iops 的指標。（相關推薦：《MySQL 教程》）
以上是為調(diào)優(yōu)準備的一些工具，而數(shù)據(jù)庫都會為高可用提供很多大大小小的功能，大的有：復制，組復制，分區(qū)，文件鏈接：即 log 日志與數(shù)據(jù)文件等可分別放置不同硬盤。小的有：計算列，為列計算 hash，索引合并，索引下推，MRR，BKA,Loose Index 等算法，以及填充因子等。
當然，沒有視圖索引和分布式分區(qū)視圖，以及 join 僅僅只支持 nested 這是 mysql 的不足，而 sql server join 的算法支持三種，loop while hash，極大的改善 join 的速度。mysql 自帶提升性能的功能并不多，其他的就是經(jīng)驗之談，比如靜態(tài)表，不要在子查詢中使用函數(shù)，盡量將子查詢變?yōu)?join 查詢，非字符串和 blob 列永遠比其他的數(shù)字或者時間列要慢，join |order by|group 一定不要讓其在硬盤生成臨時表，當然這個和內(nèi)存有關，窄表和寬表設計等，當然最后還是取決你的業(yè)務類型。
優(yōu)化入手有兩種方法，一種是運行時的，即在運行的服務器上優(yōu)化，一種是開發(fā)過程中。而無論哪種，performance_schema 都會需要。
二 performance_schema 講解
性能視圖是每個數(shù)據(jù)庫中都會有的，sql server 是 dm_* 開頭的一系列內(nèi)存表。而 mysql 就是 performance_schema 庫中的各種表，先看入口的幾個表：
SELECT * FROM setup_timers; -- 計時定義表
select * from setup_actors; -- 那些用戶需要收集信息
select * from Setup_objects; -- 那些對象需要收集信息，比如 mysql 表，select * from setup_consumers; -- 那些儀器的分類需要收集
select * from setup_instruments; -- 收集儀器，每一個功能點都會有儀器的事件，開始和結(jié)束，然后開啟那個儀器，就會收集那個儀器的數(shù)據(jù) 首先我們看開啟 performance_schema 的開關：
show variables like performance_schema -- 這是一個 read only 變量 
如果為 OFF，則需要在配置文件中開啟。
那么下面就一個一個介紹這幾個入口表。
1，setup_actors 表
全部用戶都可收集。
2，Setup_objects
那些對象可以收集，是 table 還是 trigger 等。至于關閉兩個列控制，enabled 和 timed 字段設置為 No，這幾個表都是如此。
3 setup_consumers
事件的分類，stages 是步驟，一個語句在服務器執(zhí)行的過程步驟，結(jié)果和 profile 一樣，profile 方式不推薦，因為后面會去掉。transaction 是事務的事件收集等。
4 setup_instruments
這個就是主要的事件監(jiān)控儀器，如下：
5 最后就是 setup_timers，配合 performance_timers 定義那些儀器分類是的時間類型，如下：
CYCLE:cpu 時鐘，TIMER_FREQUENCY 是一秒有多少，TIMER_RESOLUTION 是每次增加多少，最后是多久獲取一次這個時間。
三 利用 performance_schema 獲取 priofile 數(shù)據(jù)
開啟相關的 instrument:
我們看上面 instrument 分類表 setup_consumers 中的信息，關于 stage 的行都是 NO，那么我們需要改為 YES，同時一會需要拿 statements 監(jiān)控表中的信息，所以也需要開啟 statements：
UPDATE setup_consumers SET ENABLED = YES
WHERE NAME LIKE %stage%
UPDATE setup_consumers SET ENABLED = YES
WHERE NAME LIKE %statements%
然后把 stage 的 instrument 開啟
UPDATE performance_schema.setup_instruments SET ENABLED = YES , TIMED = YES
WHERE NAME LIKE %stage/% -- 開啟所有執(zhí)行步驟的監(jiān)控
UPDATE performance_schema.setup_instruments SET ENABLED = YES , TIMED = YES
WHERE NAME LIKE %statement/%執(zhí)行依據(jù) sql
select * from quartz.TestOne查詢這條語句的 queryid：
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like %quartz%
那么 id 就是 509
然后執(zhí)行性能監(jiān)控表：
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=509
內(nèi)容和老版本的 profile 結(jié)果一樣。
主要看下 stage/sql/Sending data 這一行，這一行是主要 io 相關的事件，一般情況下，sql 慢了，而這一行數(shù)值比較大，那肯定硬盤讀數(shù)據(jù)慢了或者有鎖沖突。
那么就是用 error log，有死鎖，mysql 會將死鎖信息打入 error 日志，show engine innodb status 只是全局的一些信息，如果要想看詳細的再去監(jiān)控對應的 instrument。
而且目前 mysql8 多支持 NOWAIT 和 skiplocked 兩個語句，用法還是 select.. from 表明 for update/for nowait 等，非常靈活的解決了死鎖的處理方式，當然你也可以讓其事務隔離級別為臟讀級別，但是并不能解決更多的業(yè)務類型，設置死鎖超時也是一個可行的辦法。
以上是“mysql 之調(diào)優(yōu)概論的案例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業(yè)資訊頻道！
向 AI 問一下細節(jié)丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！
