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

創(chuàng)新互聯(lián)是一家專(zhuān)業(yè)提供房縣企業(yè)網(wǎng)站建設(shè),專(zhuān)注與網(wǎng)站制作、網(wǎng)站建設(shè)、H5高端網(wǎng)站建設(shè)、小程序制作等業(yè)務(wù)。10年已為房縣眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專(zhuān)業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。

一  簡(jiǎn)介

咱們先不說(shuō)cpu的頻率，內(nèi)存的大小（這個(gè)和索引一樣重要，但不是本文討論的內(nèi)容），硬盤(pán)的尋道時(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ù)庫(kù)都會(huì)為高可用提供很多大大小小的功能，大的有：復(fù)制，組復(fù)制，分區(qū)，文件鏈接：即log日志與數(shù)據(jù)文件等可分別放置不同硬盤(pán)。小的有：計(jì)算列，為列計(jì)算hash，索引合并，索引下推，MRR，BKA,Loose Index 等算法，以及填充因子等。

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

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

二 performance_schema講解

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

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

首先我們看開(kāi)啟performance_schema的開(kāi)關(guān)：

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

如果為OFF，則需要在配置文件中開(kāi)啟。

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

1 ，setup_actors表

全部用戶(hù)都可收集。

2，Setup_objects

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

3  setup_consumers

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

4setup_instruments　

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

5 最后就是setup_timers，配合performance_timers定義那些儀器分類(lèi)是的時(shí)間類(lèi)型，如下：

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

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

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

我們看上面 instrument分類(lèi)表setup_consumers中的信息，關(guān)于stage的行都是NO，那么我們需要改為YES，同時(shí)一會(huì)需要拿statements監(jiān)控表中的信息，所以也需要開(kāi)啟statements：

UPDATE setup_consumers SET ENABLED = 'YES'
       WHERE NAME LIKE '%stage%';
UPDATE setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

然后把stage的instrument開(kāi)啟

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';   -- 開(kāi)啟所有執(zhí)行步驟的監(jiān)控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

執(zhí)行依據(jù)sql

 select * from quartz.TestOne

查詢(xún)這條語(yǔ)句的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相關(guān)的事件，一般情況下，sql慢了，而這一行數(shù)值比較大，那肯定硬盤(pán)讀數(shù)據(jù)慢了或者有鎖沖突。

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

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

以上是“mysql之調(diào)優(yōu)概論的案例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)網(wǎng)站制作公司行業(yè)資訊頻道！

當(dāng)前文章：mysql之調(diào)優(yōu)概論的案例分析-創(chuàng)新互聯(lián)
網(wǎng)頁(yè)鏈接：http://www.xueling.net.cn/article/ccishj.html