首頁  >  文章  >  資料庫  >  mysql之調優概論

mysql之調優概論

藏色散人
藏色散人轉載
2019-05-16 09:17:412135瀏覽

 一  簡介

咱們先不說cpu的頻率,記憶體的大小(這個和索引一樣重要,但不是本文討論的內容),硬碟的尋道時間。想起mysql的調優,最起碼的必須知道explain執行計劃,慢sql日誌,老舊的profile指令,新的performance_schema效能視圖和information_schema中目前事務和記憶體佔用資訊的相關表,還有show engine innodb status的診斷訊息,以及某些metrix中的tps,qps,iops的指標。 (相關推薦:《MySQL教學》)

以上是為調優準備的一些工具,而資料庫都會為高可用提供很多大大小小的功能,大的有:複製,群組複製,分區,檔案連結:即log日誌與資料檔案等可分別放置不同硬碟。小的有:計算列,為列計算hash,索引合併,索引下推,MRR,BKA,Loose Index 等演算法,以及填充因子等。

當然,沒有視圖索引和分散式分區視圖,以及join只支援nested這是mysql的不足,而sql server join的演算法支援三種,loop while hash,極大的改善join的速度。 mysql自帶提升效能的功能不多,其他的就是經驗之談,例如靜態表,不要在子查詢中使用函數,盡量將子查詢變為join查詢,非字串和blob列永遠比其他的數字或者時間列要慢,join |order by|group一定不要讓其在硬碟上產生臨時表,當然這個和記憶體有關,窄表和寬表設計等,當然最後還是取決你的業務類型。

優化入手有兩種方法,一種是運行時的,即在運行的伺服器上優化,一種是開發過程中。而無論哪一種,performance_schema都會需要。

二 performance_schema解釋

#效能檢視是每個資料庫中都會有的,sql server是dm_*開頭的一系列記憶體表。而mysql就是performance_schema庫裡的各種表,先看入口的幾個表:

SELECT * FROM setup_timers; -- 计时定义表
select * from setup_actors; -- 那些用户需要收集信息
select * from Setup_objects; -- 那些对象需要收集信息,比如mysql表,
select * from setup_consumers; -- 那些仪器的分类需要收集
select * from setup_instruments; -- 收集仪器,每一个功能点都会有仪器的事件,开始和结束,然后开启那个仪器,就会收集那个仪器的数据

首先我們來看開啟performance_schema的開關:

show variables like 'performance_schema' -- 这是一个read only变量

#如果為OFF,則需要在設定檔中開啟。

那麼下面就一個一個介紹這幾個入口表。

1 ,setup_actors表

#全部使用者都可收集。

2Setup_objects

#  

那些物件可以收集,是table還是trigger等。 至於關閉兩個欄位控制,enabled和timed欄位設定為No,這幾個表格都是如此。

3  setup_consumers

  

事件的分類,stages是步驟,語句在伺服器執行的流程步驟,結果和profile一樣,profile方式不推薦,因為後面會去掉。 transaction是事務的事件收集等。

setup_instruments 

#這個是主要的事件監控儀器,如下:

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

##CYCLE:cpu時鐘,TIMER_FREQUENCY是一秒數有多少,TIMER_RESOLUTION是每次增加多少,最後是多久取得一次這個時間。

三利用performance_schema取得priofile資料

開啟相關的instrument:

我們看上面instrument分類表

setup_consumers中的信息,關於stage的行都是NO,那麼我們需要改為YES,同時一會需要拿statements監控表中的信息,所以也需要開啟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/%';   -- 开启所有执行步骤的监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

執行依據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

然后执行性能监控表:

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=509

 

内容和老版本的profile结果一样。

主要看下stage/sql/Sending data这一行,这一行是主要io相关的事件,一般情况下,sql慢了,而这一行数值比较大,那肯定硬盘读数据慢了或者有锁冲突。

那么就是用error log,有死锁,mysql会将死锁信息打入error日志,show engine innodb status只是全局的一些信息,如果要想看详细的再去监控对应的instrument。

而且目前mysql8多支持NOWAIT和skiplocked两个语句,用法还是select.. from 表明 for update/for nowait等,非常灵活的解决了死锁的处理方式,当然你也可以让其事务隔离级别为脏读级别,但是并不能解决更多的业务类型,设置死锁超时也是一个可行的办法。

以上是mysql之調優概論的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:cnblogs.com。如有侵權,請聯絡admin@php.cn刪除