一般進(jìn)行性能分析，分如下三步：
網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、重慶小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了潞州免費(fèi)建站歡迎大家使用！
首先需要使用慢查詢?nèi)罩竟δ埽カ@取所有查詢時(shí)間比較長(zhǎng)的SQL語(yǔ)句
其次查看執(zhí)行計(jì)劃查看有問題的SQL的執(zhí)行計(jì)劃 explain
最后可以使用show profile查看有問題的SQL的性能使用情況
慢查詢?nèi)罩痉治?/p>
首先我們要使用慢查詢?nèi)罩?，因?yàn)樗占瞬樵儠r(shí)間比較長(zhǎng)的SQL語(yǔ)句，但使用之前必須開啟慢查詢?nèi)罩?，在配置文件my.cnf（一般為/etc/my.cnf）中的[mysqld] 增加如下參數(shù)：
slow_query_log=ONlong_query_time=3slow_query_log_file=/var/lib/mysql/slow-log.log復(fù)制代碼
增加這些參數(shù)之后，重啟MySQL，可以進(jìn)行查詢慢查詢?nèi)罩臼欠耖_啟。
1. 任何地方都不要使用 select * from t，用具體的字段列表代替“*“，不要返回用不到的任何字段。
2. 索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
3. 并不是所有索引對(duì)查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。
4. 盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。
5. 盡可能的使用 varchar 代替 char ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間， 其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。
6. 如果使用到了臨時(shí)表，在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。
7. 對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where和order by相關(guān)的列上建立索引。
8. 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
例如： select * from t where num is null
我們可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：select * from t where num=0。
問題
我們有一個(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ì)信息。
那我們來(lái) 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è)，猜中了萬(wàn)事大吉，猜不中就無(wú)法做出好的診斷。
MySQL 在崩潰恢復(fù)時(shí)，會(huì)遍歷打開所有 ibd 文件的 header page 驗(yàn)證數(shù)據(jù)字典的準(zhǔn)確性，如果 MySQL 中包含了大量表，這個(gè)校驗(yàn)過程就會(huì)比較耗時(shí)。 MySQL 下崩潰恢復(fù)確實(shí)和表數(shù)量有關(guān)，表總數(shù)越大，崩潰恢復(fù)時(shí)間越長(zhǎng)。另外磁盤 IOPS 也會(huì)影響崩潰恢復(fù)時(shí)間，像這里開發(fā)庫(kù)的 HDD IOPS 較低，因此面對(duì)大量的表空間，校驗(yàn)速度就非常緩慢。另外一個(gè)發(fā)現(xiàn)，MySQL 8 下正常啟用時(shí)居然也會(huì)進(jìn)行表空間校驗(yàn)，而故障恢復(fù)時(shí)則會(huì)額外再進(jìn)行一次表空間校驗(yàn)，等于校驗(yàn)了 2 遍。不過 MySQL 8.0 里多了一個(gè)特性，即表數(shù)量超過 5W 時(shí)，會(huì)啟用多線程掃描，加快表空間校驗(yàn)過程。
如何跳過校驗(yàn)MySQL 5.7 下有方法可以跳過崩潰恢復(fù)時(shí)的表空間校驗(yàn)過程嘛？查閱了資料，方法主要有兩種：
1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那么 validate = false，即可以跳過表空間校驗(yàn)。實(shí)際測(cè)試的時(shí)候設(shè)置 innodb_force_recovery =1，也就是強(qiáng)制恢復(fù)跳過壞頁(yè)，就可以跳過校驗(yàn)，然后重啟就是正常啟動(dòng)了。通過這種臨時(shí)方式可以避免崩潰恢復(fù)后非常耗時(shí)的表空間校驗(yàn)過程，快速啟動(dòng) MySQL，個(gè)人目前暫時(shí)未發(fā)現(xiàn)有什么隱患。2. 使用共享表空間替代獨(dú)立表空間這樣就不需要打開 N 個(gè) ibd 文件了，只需要打開一個(gè) ibdata 文件即可，大大節(jié)省了校驗(yàn)時(shí)間。自從聽了姜老師講過使用共享表空間替代獨(dú)立表空間解決 drop 大表時(shí)性能抖動(dòng)的原理后，感覺共享表空間在很多業(yè)務(wù)環(huán)境下，反而更有優(yōu)勢(shì)。
臨時(shí)冒出另外一種解決想法，即用 GDB 調(diào)試崩潰恢復(fù)，通過臨時(shí)修改 validate 變量值讓 MySQL 跳過表空間驗(yàn)證過程，然后讓 MySQL 正常關(guān)閉，重新啟動(dòng)就可以正常啟動(dòng)了。但是實(shí)際測(cè)試發(fā)現(xiàn)，如果以 debug 模式運(yùn)行，確實(shí)可以臨時(shí)修改 validate 變量，跳過表空間驗(yàn)證過程，但是 debug 模式下代碼運(yùn)行效率大打折扣，反而耗時(shí)更長(zhǎng)。而以非 debug 模式運(yùn)行，則無(wú)法修改 validate 變量，想法破滅。
問題
我們有一個(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ì)信息。
那我們來(lái) 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è)，猜中了萬(wàn)事大吉，猜不中就無(wú)法做出好的診斷。
重慶分公司
重慶分公司
