溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

mysql之調(diào)優(yōu)概論的案例分析

發(fā)布時(shí)間:2020-12-01 11:57:25 來源:億速云 閱讀:163 作者:小新 欄目:MySQL數(shù)據(jù)庫

小編給大家分享一下mysql之調(diào)優(yōu)概論的案例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!

一  簡介

咱們先不說cpu的頻率,內(nèi)存的大?。ㄟ@個(gè)和索引一樣重要,但不是本文討論的內(nèi)容),硬盤的尋道時(shí)間。想起mysql的調(diào)優(yōu),最起碼的必須知道explain執(zhí)行計(jì)劃,慢sql日志,老舊的profile命令,新的performance_schema性能視圖和information_schema中當(dāng)前事務(wù)和內(nèi)存占用信息的相關(guān)表,還有 show engine innodb status的診斷信息,以及某些metrix中的tps,qps,iops的指標(biāo)。(相關(guān)推薦:《MySQL教程》)

以上是為調(diào)優(yōu)準(zhǔn)備的一些工具,而數(shù)據(jù)庫都會(huì)為高可用提供很多大大小小的功能,大的有:復(fù)制,組復(fù)制,分區(qū),文件鏈接:即log日志與數(shù)據(jù)文件等可分別放置不同硬盤。小的有:計(jì)算列,為列計(jì)算hash,索引合并,索引下推,MRR,BKA,Loose Index 等算法,以及填充因子等。

當(dāng)然,沒有視圖索引和分布式分區(qū)視圖,以及join僅僅只支持nested這是mysql的不足,而sql server join的算法支持三種,loop while hash,極大的改善join的速度。mysql自帶提升性能的功能并不多,其他的就是經(jīng)驗(yàn)之談,比如靜態(tài)表,不要在子查詢中使用函數(shù),盡量將子查詢變?yōu)閖oin查詢,非字符串和blob列永遠(yuǎn)比其他的數(shù)字或者時(shí)間列要慢,join |order by|group一定不要讓其在硬盤生成臨時(shí)表,當(dāng)然這個(gè)和內(nèi)存有關(guān),窄表和寬表設(shè)計(jì)等,當(dāng)然最后還是取決你的業(yè)務(wù)類型。

優(yōu)化入手有兩種方法,一種是運(yùn)行時(shí)的,即在運(yùn)行的服務(wù)器上優(yōu)化,一種是開發(fā)過程中。而無論哪種,performance_schema都會(huì)需要。

二 performance_schema講解

性能視圖是每個(gè)數(shù)據(jù)庫中都會(huì)有的,sql server是dm_*開頭的一系列內(nèi)存表。而mysql就是performance_schema庫中的各種表,先看入口的幾個(gè)表:

SELECT * FROM setup_timers; -- 計(jì)時(shí)定義表
select * from setup_actors; -- 那些用戶需要收集信息
select * from Setup_objects; -- 那些對象需要收集信息,比如mysql表,
select * from setup_consumers; -- 那些儀器的分類需要收集
select * from setup_instruments; -- 收集儀器,每一個(gè)功能點(diǎn)都會(huì)有儀器的事件,開始和結(jié)束,然后開啟那個(gè)儀器,就會(huì)收集那個(gè)儀器的數(shù)據(jù)

首先我們看開啟performance_schema的開關(guān):

show variables like 'performance_schema' -- 這是一個(gè)read only變量

mysql之調(diào)優(yōu)概論的案例分析

如果為OFF,則需要在配置文件中開啟。

那么下面就一個(gè)一個(gè)介紹這幾個(gè)入口表。

1 ,setup_actors表

mysql之調(diào)優(yōu)概論的案例分析

全部用戶都可收集。

2,Setup_objects

  mysql之調(diào)優(yōu)概論的案例分析

那些對象可以收集,是table還是trigger等。至于關(guān)閉兩個(gè)列控制,enabled和timed字段設(shè)置為No,這幾個(gè)表都是如此。

3  setup_consumers

  mysql之調(diào)優(yōu)概論的案例分析

事件的分類,stages是步驟,一個(gè)語句在服務(wù)器執(zhí)行的過程步驟,結(jié)果和profile一樣,profile方式不推薦,因?yàn)楹竺鏁?huì)去掉。transaction是事務(wù)的事件收集等。

4 setup_instruments 

這個(gè)就是主要的事件監(jiān)控儀器,如下:

mysql之調(diào)優(yōu)概論的案例分析

5 最后就是setup_timers,配合performance_timers定義那些儀器分類是的時(shí)間類型,如下:

mysql之調(diào)優(yōu)概論的案例分析mysql之調(diào)優(yōu)概論的案例分析

CYCLE:cpu時(shí)鐘,TIMER_FREQUENCY是一秒有多少,TIMER_RESOLUTION是每次增加多少,最后是多久獲取一次這個(gè)時(shí)間。

三 利用performance_schema獲取priofile數(shù)據(jù)

開啟相關(guān)的instrument:

我們看上面 instrument分類表setup_consumers中的信息,關(guān)于stage的行都是NO,那么我們需要改為YES,同時(shí)一會(huì)需要拿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%';

mysql之調(diào)優(yōu)概論的案例分析

那么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

mysql之調(diào)優(yōu)概論的案例分析

內(nèi)容和老版本的profile結(jié)果一樣。

主要看下stage/sql/Sending data這一行,這一行是主要io相關(guān)的事件,一般情況下,sql慢了,而這一行數(shù)值比較大,那肯定硬盤讀數(shù)據(jù)慢了或者有鎖沖突。

那么就是用error log,有死鎖,mysql會(huì)將死鎖信息打入error日志,show engine innodb status只是全局的一些信息,如果要想看詳細(xì)的再去監(jiān)控對應(yīng)的instrument。

而且目前mysql8多支持NOWAIT和skiplocked兩個(gè)語句,用法還是select.. from 表明 for update/for nowait等,非常靈活的解決了死鎖的處理方式,當(dāng)然你也可以讓其事務(wù)隔離級(jí)別為臟讀級(jí)別,但是并不能解決更多的業(yè)務(wù)類型,設(shè)置死鎖超時(shí)也是一個(gè)可行的辦法。

以上是“mysql之調(diào)優(yōu)概論的案例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!

向AI問一下細(xì)節(jié)

免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI