一條sql的具體執(zhí)行過(guò)程

連接

我們?cè)趺床榭碝ySQL當(dāng)前有多少個(gè)連接？

可以用show status命令，模糊匹配Thread，

Show global status like "Thread%"

show global variables like 'wait timeout';—非交互式超時(shí)時(shí)間，如JDBC程序

show global variables like 'interactive timeout';"交互式超時(shí)時(shí)間，如數(shù)據(jù)庫(kù)工具默認(rèn)都是28800秒，8小時(shí)。

既然連接消耗資源，MySQL服務(wù)允許的最大連接數(shù)（也就是并發(fā)數(shù)）默認(rèn)是多少呢？在5.7版本中默認(rèn)是151個(gè)，最大可以設(shè)置成100000

MySQL中的參數(shù)（變量）分為session和global級(jí)別，分別是在當(dāng)前會(huì)話中生效和 全局生效,但是并不是每個(gè)參數(shù)都有兩個(gè)級(jí)別，比如max_connections就只有全局級(jí)別。當(dāng)沒(méi)有帶參數(shù)的時(shí)候，默認(rèn)是session級(jí)別，包括查詢(xún)和修改。比如修改了一個(gè)參數(shù)以后，在本窗口査詢(xún)已經(jīng)生效，但是其他窗口不生效：所以，如果只是臨時(shí)修改，建議修改session級(jí)別。如果需要在其他會(huì)話中生效，必須顯式地加上global參數(shù)。

緩存

緩存沒(méi)有生效，為什么？MySQL的緩存默認(rèn)是關(guān)閉的。show variables like? “query_cache%”!;

主要是因?yàn)镸ySQL自帶的緩存的應(yīng)用場(chǎng)景有限，第一個(gè)是它要求SQL語(yǔ)句必須一 模一樣，中間多一個(gè)空格，字母大小寫(xiě)不同都被認(rèn)為是不同的的SQL。第二個(gè)是表里面任何一條數(shù)據(jù)發(fā)生變化的時(shí)候，這張表所有緩存都會(huì)失效，所以對(duì) 于有大量數(shù)據(jù)更新的應(yīng)用，也不適合。

詞法解析與語(yǔ)法解析

詞法分析

就是把一個(gè)完整的SQL語(yǔ)句打碎成一個(gè)個(gè)的單詞。

select name from user where id = 1;

它會(huì)打碎成8個(gè)符號(hào)，每個(gè)符號(hào)是什么類(lèi)型，從哪里開(kāi)始到哪里結(jié)束。

語(yǔ)法分析 ?

語(yǔ)法分析會(huì)對(duì)SQL做一些語(yǔ)法檢查，比如單引號(hào)有沒(méi)有閉合,然后根據(jù)MySQL定義的語(yǔ)法規(guī)則，根據(jù)SQL語(yǔ)句生成一個(gè)數(shù)據(jù)結(jié)構(gòu)。這個(gè)數(shù)據(jù)結(jié)構(gòu)我 們把它叫做解析樹(shù)

預(yù)處理器

實(shí)際上還是在解析的時(shí)候報(bào)錯(cuò)，解析SQL的環(huán)節(jié)里面有個(gè)預(yù)處理器。它會(huì)檢査生成的解析樹(shù)，解決解析器無(wú)法解析的語(yǔ)義。

它會(huì)檢査表和列名是否存在，檢査名字和別名，保證沒(méi)有歧義。預(yù)處理之后得到一個(gè)新的解析樹(shù)。

査詢(xún)優(yōu)化器

査詢(xún)優(yōu)化器的模塊(Optimizer)。

査詢(xún)優(yōu)化器的目的就是根據(jù)解析樹(shù)生成不同的執(zhí)行計(jì)劃(Execution Plan),然后選擇一種最優(yōu)的執(zhí)行計(jì)劃，MySQL里面使用的是基于開(kāi)銷(xiāo)(cost)的優(yōu)化器，那種執(zhí)行計(jì)劃開(kāi)銷(xiāo)最小，就用哪種?？梢允褂眠@個(gè)命令査看査詢(xún)的開(kāi)銷(xiāo)：

show status like 'Last_query_cost';

error 1040: Too many connections的錯(cuò)誤。這個(gè)是超

過(guò)了服務(wù)端設(shè)置的最大并發(fā)連接數(shù)。

優(yōu)化的思路

1、從服務(wù)端來(lái)說(shuō)，我們可以增加服務(wù)端的可用連接數(shù)。

如果有多個(gè)應(yīng)用或者很多請(qǐng)求同時(shí)訪問(wèn)數(shù)據(jù)庫(kù)，連接數(shù)不夠的時(shí)候，我們可以:

(1)增加可用連接數(shù)，修改max connections的大小:

show variables like max_connections;?? ??修改最大連接數(shù)，當(dāng)有多個(gè)應(yīng)用連接的時(shí)候

(2)或者，或者及時(shí)釋放不活動(dòng)的連接。交互式和非交互式的客戶端的默認(rèn)超時(shí)時(shí)

間都是28800秒，8小時(shí)，我們可以把這個(gè)值調(diào)小。

show global variables like ‘wait_timeout’

引入連接池，實(shí)現(xiàn)對(duì)連接重用

2? ?緩存層面

使用緩存 redis?

集群

主從復(fù)制

（1）master服務(wù)器將數(shù)據(jù)的改變記錄二進(jìn)制binlog日志，當(dāng)master上的數(shù)據(jù)發(fā)生改變時(shí)，則將其改變寫(xiě)入二進(jìn)制日志中；????????????

?????????????? （2）slave服務(wù)器會(huì)在一定時(shí)間間隔內(nèi)對(duì)master二進(jìn)制日志進(jìn)行探測(cè)其是否發(fā)生改變，如果發(fā)生改變，則開(kāi)始一個(gè)I/OThread請(qǐng)求master二進(jìn)制事件

?????????????? （3）同時(shí)主節(jié)點(diǎn)為每個(gè)I/O線程啟動(dòng)一個(gè)dump線程，用于向其發(fā)送二進(jìn)制事件，并保存至從節(jié)點(diǎn)本地的中繼日志中，從節(jié)點(diǎn)將啟動(dòng)SQL線程從中繼日志中讀取二進(jìn)制日志，在本地重放，使得其數(shù)據(jù)和主節(jié)點(diǎn)的保持一致，最后I/OThread和SQLThread將進(jìn)入睡眠狀態(tài)，等待下一次被喚醒。

也就是說(shuō)：

- 從庫(kù)會(huì)生成兩個(gè)線程,一個(gè)I/O線程,一個(gè)SQL線程;

- I/O線程會(huì)去請(qǐng)求主庫(kù)的binlog,并將得到的binlog寫(xiě)到本地的relay-log(中繼日志)文件中;

- 主庫(kù)會(huì)生成一個(gè)log dump線程,用來(lái)給從庫(kù)I/O線程傳binlog;

- SQL線程,會(huì)讀取relay log文件中的日志,并解析成sql語(yǔ)句逐一執(zhí)行;

修改配置

修改配置的工作一般由專(zhuān)業(yè)的DBA完成。也有一些工具可以給出

推薦值。

慢日志查詢(xún)

1 打開(kāi)慢日志開(kāi)關(guān)

因?yàn)殚_(kāi)啟慢查詢(xún)?nèi)罩臼怯写鷥r(jià)的（跟binlog—樣），所以它默認(rèn)

是關(guān)閉的：

show variables like ‘slow_query’

除了這個(gè)開(kāi)關(guān)，還有一個(gè)參數(shù)，控制執(zhí)行超過(guò)多長(zhǎng)時(shí)間的SQL才記錄到慢日志，默

認(rèn)是10秒。如果改成0秒的話就是記錄所有的SQL。

show variables like ‘%long_query%’;

set @@global.slow_query_log=l;?.1開(kāi)啟，0關(guān)閉，重啟后失效

set @@global.long_query_time=3;-默認(rèn)10秒，另開(kāi)一個(gè)窗口后才會(huì)查到最新值 show variables like ,%long_queiy%1; show variables like '%slow_query%‘;

修改配置文件my.cnfo

以下配置定義了慢査詢(xún)?nèi)罩镜拈_(kāi)關(guān)、慢査詢(xún)的時(shí)間、日志文件的存放路徑。

slowquerylog = ON long_qu eiy_time=2 slowqueiylogfile =/var/lib/mysql/localhost-slow.log

模擬慢査詢(xún)：

慢日志分析 1、日志內(nèi)容

less /var/lib/mysql/localhost-slow.log

MySQL提供了 mysqldumpslow的工具，在MySQL的bin目錄下。

Mysqldumpslow – help

show engine存儲(chǔ)引擎運(yùn)行信息

show engine用來(lái)顯示存儲(chǔ)引擎的當(dāng)前運(yùn)行信息，包括事務(wù)持有的表鎖、行鎖信息;

事務(wù)的鎖等待情況；線程信號(hào)量等待；文件IO請(qǐng)求；buffer pool統(tǒng)計(jì)信息。

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-744027.html

到了這里，關(guān)于MySql優(yōu)化經(jīng)驗(yàn)分享的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！