問題
讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡(jiǎn)單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名申請(qǐng)、虛擬空間、營銷軟件、網(wǎng)站建設(shè)、績(jī)溪網(wǎng)站維護(hù)、網(wǎng)站推廣。
我們有一個(gè) SQL，用于找到?jīng)]有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運(yùn)行特別慢，怎么辦?
實(shí)驗(yàn)
我們搭建一個(gè) MySQL 5.7 的環(huán)境，此處省略搭建步驟。
寫個(gè)簡(jiǎn)單的腳本，制造一批帶主鍵和不帶主鍵的表：
執(zhí)行一下腳本：
現(xiàn)在執(zhí)行以下 SQL 看看效果：
...
執(zhí)行了 16.80s，感覺是非常慢了。
現(xiàn)在用一下 DBA 三板斧，看看執(zhí)行計(jì)劃：
感覺有點(diǎn)慘，由于 information_schema.columns 是元數(shù)據(jù)表，沒有必要的統(tǒng)計(jì)信息。
那我們來 show warnings 看看 MySQL 改寫后的 SQL：
我們格式化一下 SQL：
可以看到 MySQL 將
select from A where A.x not in (select x from B) //非關(guān)聯(lián)子查詢
轉(zhuǎn)換成了
select from A where not exists (select 1 from B where B.x = a.x) //關(guān)聯(lián)子查詢
如果我們自己是 MySQL，在執(zhí)行非關(guān)聯(lián)子查詢時(shí)，可以使用很簡(jiǎn)單的策略：
select from A where A.x not in (select x from B where ...) //非關(guān)聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時(shí)表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時(shí)表 C 中的記錄進(jìn)行比對(duì)，直接在索引里比對(duì)，
而關(guān)聯(lián)子查詢就需要循環(huán)迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //關(guān)聯(lián)子查詢掃描 A 表的每一條記錄 rA： ? ? 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。
顯然，關(guān)聯(lián)子查詢的掃描成本會(huì)高于非關(guān)聯(lián)子查詢。
我們希望 MySQL 能先"緩存"子查詢的結(jié)果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認(rèn)為不緩存更快，我們就需要給予 MySQL 一定指導(dǎo)。
...
可以看到執(zhí)行時(shí)間變成了 0.67s。
整理
我們?cè)\斷的關(guān)鍵點(diǎn)如下：
\1. 對(duì)于 information_schema 中的元數(shù)據(jù)表，執(zhí)行計(jì)劃不能提供有效信息。
\2. 通過查看 MySQL 改寫后的 SQL，我們猜測(cè)了優(yōu)化器發(fā)生了誤判。
\3. 我們?cè)黾恿?hint，指導(dǎo) MySQL 正確進(jìn)行優(yōu)化判斷。
但目前我們的實(shí)驗(yàn)僅限于猜測(cè)，猜中了萬事大吉，猜不中就無法做出好的診斷。
導(dǎo)入mysql最快的方法：可以用infile語句來操作導(dǎo)入數(shù)據(jù)。MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQLAB公司開發(fā)，目前屬于Oracle旗下產(chǎn)品。MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，在WEB應(yīng)用方面，MySQL是最好的rdbms應(yīng)用軟件之一。
更多關(guān)于如何導(dǎo)入mysql最快，進(jìn)入：查看更多內(nèi)容
1.當(dāng)我們請(qǐng)求mysql服務(wù)器的時(shí)候,MySQL前端會(huì)有一個(gè)監(jiān)聽,請(qǐng)求到了之后,服務(wù)器得到相關(guān)的SQL語句,執(zhí)行之前(虛線部分為執(zhí)行),還會(huì)做權(quán)限的判斷
2.通過權(quán)限之后,SQL就到MySQL內(nèi)部,他會(huì)在查詢緩存中,看該SQL有沒有執(zhí)行過,如果有查詢過,則把緩存結(jié)果返回,說明在MySQL內(nèi)部,也有一個(gè)查詢緩存.但是這個(gè)查詢緩存,默認(rèn)是不開啟的,這個(gè)查詢緩存,和我們的Hibernate，Mybatis的查詢緩存是一樣的,因?yàn)椴樵兙彺嬉骃QL和參數(shù)都要一樣,所以這個(gè)命中率是非常低的（沒什么卵用的意思）。
3.如果我們沒有開啟查詢緩存,或者緩存中沒有找到對(duì)應(yīng)的結(jié)果,那么就到了解析器,解析器主要對(duì)SQL語法進(jìn)行解析
4.解析結(jié)束后就變成一顆解析樹,這個(gè)解析樹其實(shí)在Hibernate里面也是有的,大家回憶一下,在以前做過Hibernate項(xiàng)目的時(shí)候,是不是有個(gè)一個(gè)antlr.jar。這個(gè)就是專門做語法解析的工具.因?yàn)樵贖ibernate里面有HQL，它就是通過這個(gè)工具轉(zhuǎn)換成SQL的,我們編程語言之所以有很多規(guī)范、語法,其實(shí)就是為了便于這個(gè)解析器解析,這個(gè)學(xué)過編譯原理的應(yīng)該知道.
5.得到解析樹之后,不能馬上執(zhí)行,這還需要對(duì)這棵樹進(jìn)行預(yù)處理,也就是說,這棵樹,我沒有經(jīng)過任何優(yōu)化的樹,預(yù)處理器會(huì)這這棵樹進(jìn)行一些預(yù)處理,比如常量放在什么地方,如果有計(jì)算的東西,把計(jì)算的結(jié)果算出來等等...
6.預(yù)處理完畢之后,此時(shí)得到一棵比較規(guī)范的樹,這棵樹就是要拿去馬上做執(zhí)行的樹,比起之前的那棵樹,這棵得到了一些優(yōu)化
7.查詢優(yōu)化器，是MySQL里面最關(guān)鍵的東西,我們寫任何一條SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它會(huì)怎么去執(zhí)行?它是先執(zhí)行username = toby還是password = 1?每一條SQL的執(zhí)行順序查詢優(yōu)化器就是根據(jù)MySQL對(duì)數(shù)據(jù)統(tǒng)計(jì)表的一些信息,比如索引,比如表一共有多少數(shù)據(jù),MySQL都是有緩存起來的,在真正執(zhí)行SQL之前,他會(huì)根據(jù)自己的這些數(shù)據(jù),進(jìn)行一個(gè)綜合的判定,判斷這一次在多種執(zhí)行方式里面,到底選哪一種執(zhí)行方式,可能運(yùn)行的最快.這一步是MySQL性能中,最關(guān)鍵的核心點(diǎn),也是我們的優(yōu)化原則.我們平時(shí)所講的優(yōu)化SQL,其實(shí)說白了,就是想讓查詢優(yōu)化器,按照我們的想法,幫我們選擇最優(yōu)的執(zhí)行方案,因?yàn)槲覀儽萂ySQL更懂我們的數(shù)據(jù).MySQL看數(shù)據(jù),僅僅只是自己收集到的信息,這些信息可能是不準(zhǔn)確的,MySQL根據(jù)這些信息選了一個(gè)它自認(rèn)為最優(yōu)的方案,但是這個(gè)方案可能和我們想象的不一樣.
8.這里的查詢執(zhí)行計(jì)劃,也就是MySQL查詢中的執(zhí)行計(jì)劃,比如要先執(zhí)行username = toby還是password = 1
9.這個(gè)執(zhí)行計(jì)劃會(huì)傳給查詢執(zhí)行引擎,執(zhí)行引擎選擇存儲(chǔ)引擎來執(zhí)行這一份傳過來的計(jì)劃,到磁盤中的文件中去查詢,這個(gè)時(shí)候重點(diǎn)來了,影響這個(gè)查詢性能最根本的原因是什么?就是硬盤的機(jī)械運(yùn)動(dòng),也就是我們平時(shí)熟悉的IO,所以一條查詢語句是快還是慢,就是根據(jù)這個(gè)時(shí)間的IO來確定的.那怎么執(zhí)行IO又是什么來確定的?就是傳過來的這一份執(zhí)行計(jì)劃.(優(yōu)化就是制定一個(gè)我們認(rèn)為最快的執(zhí)行方案,最節(jié)省IO,和執(zhí)行最快)
10.如果開了查詢緩存,則返回結(jié)果給客戶端,并且查詢緩存也放一份。
問題
我們有一個(gè) SQL，用于找到?jīng)]有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運(yùn)行特別慢，怎么辦？
實(shí)驗(yàn)
我們搭建一個(gè) MySQL 5.7 的環(huán)境，此處省略搭建步驟。
寫個(gè)簡(jiǎn)單的腳本，制造一批帶主鍵和不帶主鍵的表：
執(zhí)行一下腳本：
現(xiàn)在執(zhí)行以下 SQL 看看效果：
...
執(zhí)行了 16.80s，感覺是非常慢了。
現(xiàn)在用一下 DBA 三板斧，看看執(zhí)行計(jì)劃：
感覺有點(diǎn)慘，由于 information_schema.columns 是元數(shù)據(jù)表，沒有必要的統(tǒng)計(jì)信息。
那我們來 show warnings 看看 MySQL 改寫后的 SQL：
我們格式化一下 SQL：
可以看到 MySQL 將
select from A where A.x not in (select x from B) //非關(guān)聯(lián)子查詢
轉(zhuǎn)換成了
select from A where not exists (select 1 from B where B.x = a.x) //關(guān)聯(lián)子查詢
如果我們自己是 MySQL，在執(zhí)行非關(guān)聯(lián)子查詢時(shí)，可以使用很簡(jiǎn)單的策略：
select from A where A.x not in (select x from B where ...) //非關(guān)聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時(shí)表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時(shí)表 C 中的記錄進(jìn)行比對(duì)，直接在索引里比對(duì)，
而關(guān)聯(lián)子查詢就需要循環(huán)迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //關(guān)聯(lián)子查詢掃描 A 表的每一條記錄 rA： ? ? 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。
顯然，關(guān)聯(lián)子查詢的掃描成本會(huì)高于非關(guān)聯(lián)子查詢。
我們希望 MySQL 能先"緩存"子查詢的結(jié)果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認(rèn)為不緩存更快，我們就需要給予 MySQL 一定指導(dǎo)。
...
可以看到執(zhí)行時(shí)間變成了 0.67s。
整理
我們?cè)\斷的關(guān)鍵點(diǎn)如下：
\1. 對(duì)于 information_schema 中的元數(shù)據(jù)表，執(zhí)行計(jì)劃不能提供有效信息。
\2. 通過查看 MySQL 改寫后的 SQL，我們猜測(cè)了優(yōu)化器發(fā)生了誤判。
\3. 我們?cè)黾恿?hint，指導(dǎo) MySQL 正確進(jìn)行優(yōu)化判斷。
但目前我們的實(shí)驗(yàn)僅限于猜測(cè)，猜中了萬事大吉，猜不中就無法做出好的診斷。
重慶分公司
重慶分公司
