解決mysql資料庫訪問速度慢,mysql資料庫速度變慢

本文目錄一覽：

• 1、Mysql 查詢速度慢怎麼辦
• 2、訪問外網的mysql資料庫比較慢是怎麼解決
• 3、MySQL速度變慢，怎麼辦

Mysql 查詢速度慢怎麼辦

問題

我們有一個 SQL，用於找到沒有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運行特別慢，怎麼辦？

實驗

我們搭建一個 MySQL 5.7 的環境，此處省略搭建步驟。

寫個簡單的腳本，製造一批帶主鍵和不帶主鍵的表：

執行一下腳本：

現在執行以下 SQL 看看效果：

…

執行了 16.80s，感覺是非常慢了。

現在用一下 DBA 三板斧，看看執行計劃：

感覺有點慘，由於 information_schema.columns 是元數據表，沒有必要的統計信息。

那我們來 show warnings 看看 MySQL 改寫後的 SQL：

我們格式化一下 SQL：

可以看到 MySQL 將

select from A where A.x not in (select x from B) //非關聯子查詢

轉換成了

select from A where not exists (select 1 from B where B.x = a.x) //關聯子查詢

如果我們自己是 MySQL，在執行非關聯子查詢時，可以使用很簡單的策略：

select from A where A.x not in (select x from B where …) //非關聯子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時表 C 中的記錄進行比對，直接在索引里比對，

而關聯子查詢就需要循環迭代：

select from A where not exists (select 1 from B where B.x = a.x and …) //關聯子查詢掃描 A 表的每一條記錄 rA：     掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。

顯然，關聯子查詢的掃描成本會高於非關聯子查詢。

我們希望 MySQL 能先”緩存”子查詢的結果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認為不緩存更快，我們就需要給予 MySQL 一定指導。

…

可以看到執行時間變成了 0.67s。

整理

我們診斷的關鍵點如下：

\1. 對於 information_schema 中的元數據表，執行計劃不能提供有效信息。

\2. 通過查看 MySQL 改寫後的 SQL，我們猜測了優化器發生了誤判。

\3. 我們增加了 hint，指導 MySQL 正確進行優化判斷。

但目前我們的實驗僅限於猜測，猜中了萬事大吉，猜不中就無法做出好的診斷。

訪問外網的mysql資料庫比較慢是怎麼解決

伺服器放在區域網內進行測試時，資料庫的訪問速度還是很快。但當伺服器放到外網後，資料庫的訪問速度就變得非常慢。

後來在網上發現解決方法,my.ini裡面添加

[mysqld]

skip-name-resolve

這樣速度就快了!

skip-name-resolve

選項就能禁用DNS解析，連接速度會快很多。不過，這樣的話就不能在MySQL的授權表中使用主機名了而只能用ip格式。

就MySQL本身而言，問題出在在mysql dns反解析

mysqlshow processlist;

| 20681949 | unauthenticated user | 10.10.4.193:52497 | NULL | Connect | | Reading from net | NULL |

| 20681948 | unauthenticated user | 10.10.4.193:52495 | NULL | Connect | | Reading from net | NULL

發現有非常多的 unauthenticated user 嘗試做登入使用 mysql 的情況 ，當這種情況無限制發生時就會造成系統十分緩慢。

查閱mysql官方網站得知，這屬於官方一個系統上的特殊設定，就把他當成mysql的一個bug算了，不管鏈接的的方式是經過 hosts 或是 IP 的模式，他都會對 DNS 做反查。mysqld 會嘗試去反查 IP – dns ，由於反查解析過慢，就會無法應付過量的查詢。

MySQL速度變慢，怎麼辦

MySQL 在崩潰恢復時，會遍歷打開所有 ibd 文件的 header page 驗證數據字典的準確性，如果 MySQL 中包含了大量表，這個校驗過程就會比較耗時。 MySQL 下崩潰恢復確實和表數量有關，表總數越大，崩潰恢復時間越長。另外磁碟 IOPS 也會影響崩潰恢復時間，像這裡開發庫的 HDD IOPS 較低，因此面對大量的表空間，校驗速度就非常緩慢。另外一個發現，MySQL 8 下正常啟用時居然也會進行表空間校驗，而故障恢復時則會額外再進行一次表空間校驗，等於校驗了 2 遍。不過 MySQL 8.0 里多了一個特性，即表數量超過 5W 時，會啟用多線程掃描，加快表空間校驗過程。

如何跳過校驗MySQL 5.7 下有方法可以跳過崩潰恢復時的表空間校驗過程嘛？查閱了資料，方法主要有兩種：

1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那麼 validate = false，即可以跳過表空間校驗。實際測試的時候設置 innodb_force_recovery =1，也就是強制恢復跳過壞頁，就可以跳過校驗，然後重啟就是正常啟動了。通過這種臨時方式可以避免崩潰恢復後非常耗時的表空間校驗過程，快速啟動 MySQL，個人目前暫時未發現有什麼隱患。2. 使用共享表空間替代獨立表空間這樣就不需要打開 N 個 ibd 文件了，只需要打開一個 ibdata 文件即可，大大節省了校驗時間。自從聽了姜老師講過使用共享表空間替代獨立表空間解決 drop 大表時性能抖動的原理後，感覺共享表空間在很多業務環境下，反而更有優勢。

臨時冒出另外一種解決想法，即用 GDB 調試崩潰恢復，通過臨時修改 validate 變數值讓 MySQL 跳過表空間驗證過程，然後讓 MySQL 正常關閉，重新啟動就可以正常啟動了。但是實際測試發現，如果以 debug 模式運行，確實可以臨時修改 validate 變數，跳過表空間驗證過程，但是 debug 模式下代碼運行效率大打折扣，反而耗時更長。而以非 debug 模式運行，則無法修改 validate 變數，想法破滅。