共計 7593 個字符，預計需要花費 19 分鐘才能閱讀完成。

本篇內容介紹了“mysql 查詢慢的因素有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

數(shù)據(jù)庫查詢流程

我們先來看下，一條查詢語句下來，會經歷哪些流程。

比如我們有一張數(shù)據(jù)庫表

CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT  主鍵 ,
 `name` varchar(100) NOT NULL DEFAULT   COMMENT  名字 ,
 `age` int(11) NOT NULL DEFAULT  0  COMMENT  年齡 ,
 `gender` int(8) NOT NULL DEFAULT  0  COMMENT  性別 ,
 PRIMARY KEY (`id`),
 KEY `idx_age` (`age`),
 KEY `idx_gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我們平常寫的應用代碼（go 或 C ++ 之類的），這時候就叫客戶端了。

客戶端底層會帶著賬號密碼，嘗試向 mysql 建立一條 TCP 長鏈接。

mysql 的連接管理模塊會對這條連接進行管理。

建立連接后，客戶端執(zhí)行一條查詢 sql 語句。比如：

select * from user where gender = 1 and age = 100;

客戶端會將 sql 語句通過網絡連接給 mysql。

mysql 收到 sql 語句后，會在分析器中先判斷下 SQL 語句有沒有語法錯誤，比如 select，如果少打一個 l，寫成 slect，則會報錯 You have an error in your SQL syntax;。這個報錯對于我這樣的手殘黨來說可以說是很熟悉了。

接下來是優(yōu)化器，在這里會根據(jù)一定的規(guī)則選擇該用什么索引。

之后，才是通過執(zhí)行器去調用存儲引擎的接口函數(shù)。

存儲引擎類似于一個個組件，它們才是 mysql 真正獲取一行行數(shù)據(jù)并返回數(shù)據(jù)的地方，存儲引擎是可以替換更改的，既可以用不支持事務的 MyISAM，也可以替換成支持事務的 Innodb。這個可以在建表的時候指定。比如

CREATE TABLE `user` ( ...) ENGINE=InnoDB;

現(xiàn)在最常用的是 InnoDB。

我們就重點說這個。

InnoDB 中，因為直接操作磁盤會比較慢，所以加了一層內存提提速，叫 buffer pool，這里面，放了很多內存頁，每一頁 16KB，有些內存頁放的是數(shù)據(jù)庫表里看到的那種一行行的數(shù)據(jù)，有些則是放的索引信息。

查詢 SQL 到了 InnoDB 中。會根據(jù)前面優(yōu)化器里計算得到的索引，去查詢相應的索引頁，如果不在 buffer pool 里則從磁盤里加載索引頁。再通過索引頁加速查詢，得到數(shù)據(jù)頁的具體位置。如果這些數(shù)據(jù)頁不在 buffer pool 中，則從磁盤里加載進來。

這樣我們就得到了我們想要的一行行數(shù)據(jù)。

最后將得到的數(shù)據(jù)結果返回給客戶端。

慢查詢分析

如果上面的流程比較慢的話，我們可以通過開啟 profiling 看到流程慢在哪。

mysql  set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql  show variables like  profiling 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set (0.00 sec)

然后正常執(zhí)行 sql 語句。

這些 SQL 語句的執(zhí)行時間都會被記錄下來，此時你想查看有哪些語句被記錄下來了，可以執(zhí)行 show profiles;

mysql  show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 1 | 0.06811025 | select * from user where age =60 |
| 2 | 0.00151375 | select * from user where gender = 2 and age = 80 |
| 3 | 0.00230425 | select * from user where gender = 2 and age = 60 |
| 4 | 0.00070400 | select * from user where gender = 2 and age = 100 |
| 5 | 0.07797650 | select * from user where age!=60 |
+----------+------------+---------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

關注下上面的 query_id，比如 select * from user where age =60 對應的 query_id 是 1，如果你想查看這條 SQL 語句的具體耗時，那么可以執(zhí)行以下的命令。

mysql  show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables | 0.000034 |
| init | 0.000032 |
| System lock | 0.000027 |
| optimizing | 0.000020 |
| statistics | 0.000058 |
| preparing | 0.000018 |
| executing | 0.000013 |
| Sending data | 0.067701 |
| end | 0.000021 |
| query end | 0.000015 |
| closing tables | 0.000014 |
| freeing items | 0.000047 |
| cleaning up | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

通過上面的各個項，大家就可以看到具體耗時在哪。比如從上面可以看出 Sending data 的耗時最大，這個是指執(zhí)行器開始查詢數(shù)據(jù)并將數(shù)據(jù)發(fā)送給客戶端的耗時，因為我的這張表符合條件的數(shù)據(jù)有好幾萬條，所以這塊耗時最大，也符合預期。

一般情況下，我們開發(fā)過程中，耗時大部分時候都在 Sending data 階段，而這一階段里如果慢的話，最容易想到的還是索引相關的原因。

索引相關原因

索引相關的問題，一般能用 explain 命令幫助分析。通過它能看到用了哪些索引，大概會掃描多少行之類的信息。

mysql 會在優(yōu)化器階段里看下選擇哪個索引，查詢速度會更快。

一般主要考慮幾個因素，比如：

選擇這個索引大概要掃描多少行（rows）

為了把這些行取出來，需要讀多少個 16kb 的頁

走普通索引需要回表，主鍵索引則不需要，回表成本大不大？

回到 show profile 中提到的 sql 語句，我們使用 explain select * from user where age =60 分析一下。

上面的這條語句，使用的 type 為 ALL，意味著是全表掃描，possible_keys 是指可能用得到的索引，這里可能使用到的索引是為 age 建的普通索引，但實際上數(shù)據(jù)庫使用的索引是在 key 那一列，是 NULL。也就是說這句 sql 不走索引，全表掃描。

這個是因為數(shù)據(jù)表里，符合條件的數(shù)據(jù)行數(shù)（rows）太多，如果使用 age 索引，那么需要將它們從 age 索引中讀出來，并且 age 索引是普通索引，還需要回表找到對應的主鍵才能找到對應的數(shù)據(jù)頁。算下來還不如直接走主鍵劃算。于是最終選擇了全表掃描。

當然上面只是舉了個例子，實際上，mysql 執(zhí)行 sql 時，不用索引或者用的索引不符合我們預期這件事經常發(fā)生，索引失效的場景有很多，比如用了不等號，隱式轉換等，這個相信大家背八股文的時候也背過不少了，我也不再贅述。

聊兩個生產中容易遇到的問題吧。

索引不符合預期

實際開發(fā)中有些情況比較特殊，比如有些數(shù)據(jù)庫表一開始數(shù)據(jù)量小，索引少，執(zhí)行 sql 時，確實使用了符合你預期的索引。但隨時時間邊長，開發(fā)的人變多了，數(shù)據(jù)量也變大了，甚至還可能會加入一些其他重復多余的索引，就有可能出現(xiàn)用著用著，用到了不符合你預期的其他索引了。從而導致查詢突然變慢。

這種問題，也好解決，可以通過 force index 指定索引。比如

通過 explain 可以看出，加了 force index 之后，sql 就選用了 idx_age 這個索引了。

走了索引還是很慢

有些 sql，用 explain 命令看，明明是走索引的，但還是很慢。一般是兩種情況：

第一種是索引區(qū)分度太低，比如網頁全路徑的 url 鏈接，這拿來做索引，一眼看過去全都是同一個域名，如果前綴索引的長度建得不夠長，那這走索引跟走全表掃描似的，正確姿勢是盡量讓索引的區(qū)分度更高，比如域名去掉，只拿后面 URI 部分去做索引。

第二種是索引中匹配到的數(shù)據(jù)太大，這時候需要關注的是 explain 里的 rows 字段了。

它是用于預估這個查詢語句需要查的行數(shù)的，它不一定完全準確，但可以體現(xiàn)個大概量級。

當它很大時，一般常見的是下面幾種情況。

如果這個字段具有唯一的屬性，比如電話號碼等，一般是不應該有大量重復的，那可能是你代碼邏輯出現(xiàn)了大量重復插入的操作，你需要檢查下代碼邏輯，或者需要加個唯一索引限制下。

如果這個字段下的數(shù)據(jù)就是會很大，是否需要全部拿？如果不需要，加個 limit 限制下。如果確實要拿全部，那也不能一次性全拿，今天你數(shù)據(jù)量小，可能一次取一兩萬都沒啥壓力，萬一哪天漲到了十萬級別，那一次性取就有點吃不消了。你可能需要分批次取，具體操作是先用 order by id 排序一下，拿到一批數(shù)據(jù)后取最大 id 作為下次取數(shù)據(jù)的起始位置。

連接數(shù)過小

索引相關的原因我們聊完了，我們來聊聊，除了索引之外，還有哪些因素會限制我們的查詢速度的。

我們可以看到，mysql 的 server 層里有個連接管理，它的作用是管理客戶端和 mysql 之間的長連接。

正常情況下，客戶端與 server 層如果只有一條連接，那么在執(zhí)行 sql 查詢之后，只能阻塞等待結果返回，如果有大量查詢同時并發(fā)請求，那么后面的請求都需要等待前面的請求執(zhí)行完成后，才能開始執(zhí)行。

因此很多時候我們的應用程序，比如 go 或 java 這些，會打印出 sql 執(zhí)行了幾分鐘的日志，但實際上你把這條語句單獨拎出來執(zhí)行，卻又是毫秒級別的。這都是因為這些 sql 語句在等待前面的 sql 執(zhí)行完成。

怎么解決呢？

如果我們能多建幾條連接，那么請求就可以并發(fā)執(zhí)行，后面的連接就不用等那么久了。

而連接數(shù)過小的問題，受數(shù)據(jù)庫和客戶端兩側同時限制。

數(shù)據(jù)庫連接數(shù)過小

mysql 的最大連接數(shù)默認是 100, 最大可以達到 16384。

可以通過設置 mysql 的 max_connections 參數(shù)，更改數(shù)據(jù)庫的最大連接數(shù)。

mysql  set global max_connections= 500;
Query OK, 0 rows affected (0.00 sec)
mysql  show variables like  max_connections 
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.00 sec)

上面的操作，就把最大連接數(shù)改成了 500。

應用側連接數(shù)過小

數(shù)據(jù)庫連接大小是調整過了，但貌似問題還是沒有變化？還是有很多 sql 執(zhí)行達到了幾分鐘，甚至超時？

那有可能是因為你應用側（go，java 寫的應用，也就是 mysql 的客戶端）的連接數(shù)也過小。

應用側與 mysql 底層的連接，是基于 TCP 協(xié)議的長鏈接，而 TCP 協(xié)議，需要經過三次握手和四次揮手來實現(xiàn)建連和釋放。如果我每次執(zhí)行 sql 都重新建立一個新的連接的話，那就要不斷握手和揮手，這很耗時。所以一般會建立一個長連接池，連接用完之后，塞到連接池里，下次要執(zhí)行 sql 的時候，再從里面撈一條連接出來用，非常環(huán)保。

我們一般寫代碼的時候，都會通過第三方的 orm 庫來對數(shù)據(jù)庫進行操作，而成熟的 orm 庫，百分之一千萬都會有個連接池。

而這個連接池，一般會有個大小。這個大小就控制了你的連接數(shù)最大值，如果說你的連接池太小，都還沒有數(shù)據(jù)庫的大，那調了數(shù)據(jù)庫的最大連接數(shù)也沒啥作用。

一般情況下，可以翻下你使用的 orm 庫的文檔，看下怎么設置這個連接池的大小，就幾行代碼的事情，改改就好。比如 go 語言里的 gorm 里是這么設置的

func Init() { db, err := gorm.Open(mysql.Open(conn), config)
 sqlDB, err := db.DB()
 // SetMaxIdleConns  設置空閑連接池中連接的最大數(shù)量
 sqlDB.SetMaxIdleConns(200)
 // SetMaxOpenConns  設置打開數(shù)據(jù)庫連接的最大數(shù)量
 sqlDB.SetMaxOpenConns(1000)
}

buffer pool 太小

連接數(shù)是上去了，速度也提升了。

曾經遇到過面試官會追問，有沒有其他辦法可以讓速度更快呢？

那必須要眉頭緊鎖，假裝思考，然后說：有的。

我們在前面的數(shù)據(jù)庫查詢流程里，提到了進了 innodb 之后，會有一層內存 buffer pool，用于將磁盤數(shù)據(jù)頁加載到內存頁中，只要查詢到 buffer pool 里有，就可以直接返回，否則就要走磁盤 IO，那就慢了。

也就是說，如果我的 buffer pool 越大，那我們能放的數(shù)據(jù)頁就越多，相應的，sql 查詢時就更可能命中 buffer pool，那查詢速度自然就更快了。

可以通過下面的命令查詢到 buffer pool 的大小，單位是 Byte。

mysql  show global variables like  innodb_buffer_pool_size 
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

也就是 128Mb。

如果想要調大一點。可以執(zhí)行

mysql  set global innodb_buffer_pool_size = 536870912;
Query OK, 0 rows affected (0.01 sec)
mysql  show global variables like  innodb_buffer_pool_size 
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.01 sec)

這樣就把 buffer pool 增大到 512Mb 了。

但是吧，如果 buffer pool 大小正常，只是別的原因導致的查詢變慢，那改 buffer pool 毫無意義。

但問題又來了。

怎么知道 buffer pool 是不是太小了？

這個我們可以看 buffer pool 的緩存命中率。

通過 show status like Innodb_buffer_pool_% 可以看到跟 buffer pool 有關的一些信息。

Innodb_buffer_pool_read_requests 表示讀請求的次數(shù)。

Innodb_buffer_pool_reads 表示從物理磁盤中讀取數(shù)據(jù)的請求次數(shù)。

所以 buffer pool 的命中率就可以這樣得到：

buffer pool  命中率  = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

比如我上面截圖里的就是，1 – (405/2278354) = 99.98%。可以說命中率非常高了。

一般情況下 buffer pool 命中率都在 99% 以上，如果低于這個值，才需要考慮加大 innodb buffer pool 的大小。

當然，還可以把這個命中率做到監(jiān)控里，這樣半夜 sql 變慢了，早上上班還能定位到原因，就很舒服。

還有哪些騷操作？

前面提到的是在存儲引擎層里加入了 buffer pool 用于緩存內存頁，這樣可以加速查詢。

那同樣的道理，server 層也可以加個緩存，直接將第一次查詢的結果緩存下來，這樣下次查詢就能立刻返回，聽著挺美的。

按道理，如果命中緩存的話，確實是能為查詢加速的。但這個功能限制很大，其中最大的問題是只要數(shù)據(jù)庫表被更新過，表里面的所有緩存都會失效，數(shù)據(jù)表頻繁的更新，就會帶來頻繁的緩存失效。所以這個功能只適合用于那些不怎么更新的數(shù)據(jù)表。

另外，這個功能在 8.0 版本之后，就被干掉了。所以這功能用來聊聊天可以，沒必要真的在生產中使用啊。

“mysql 查詢慢的因素有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！