mysql查詢慢的因素除了索引，還有什么？

我熟練應用ctrl c和ctrl v 開發(fā)curd代碼好多年了。

mysql查詢?yōu)槭裁磿P于這個問題，在實際開發(fā)經常會遇到，而面試中，也是個高頻題。

遇到這種問題，我們一般也會想到是因為索引。

那除開索引之外，還有哪些因素會導致數據庫查詢變慢呢？

有哪些操作，可以提升mysql的查詢能力呢？

今天這篇文章，我們就來聊聊會導致數據庫查詢變慢的場景有哪些，并給出原因和解決方案。

數據庫查詢流程

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

比如我們有一張數據庫表

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)化器，在這里會根據一定的規(guī)則選擇該用什么索引。

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

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

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

現在最常用的是InnoDB。

我們就重點說這個。

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

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

這樣我們就得到了我們想要的一行行數據。

最后將得到的數據結果返回給客戶端。

慢查詢分析

如果上面的流程比較慢的話，我們可以通過開啟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í)行器開始查詢數據并將數據發(fā)送給客戶端的耗時，因為我的這張表符合條件的數據有好幾萬條，所以這塊耗時最大，也符合預期。

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

索引相關原因

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

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

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

• 選擇這個索引大概要掃描多少行（rows）
• 為了把這些行取出來，需要讀多少個16kb的頁
• 走普通索引需要回表，主鍵索引則不需要，回表成本大不大？

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

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

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

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

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

索引不符合預期

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

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

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

走了索引還是很慢

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

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

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

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

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

• 如果這個字段具有唯一的屬性，比如電話號碼等，一般是不應該有大量重復的，那可能是你代碼邏輯出現了大量重復插入的操作，你需要檢查下代碼邏輯，或者需要加個唯一索引限制下。
• 如果這個字段下的數據就是會很大，是否需要全部拿？如果不需要，加個limit限制下。如果確實要拿全部，那也不能一次性全拿，今天你數據量小，可能一次取一兩萬都沒啥壓力，萬一哪天漲到了十萬級別，那一次性取就有點吃不消了。你可能需要分批次取，具體操作是先用order by id排序一下，拿到一批數據后取最大id作為下次取數據的起始位置。

連接數過小

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

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

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

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

怎么解決呢？

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

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

數據庫連接數過小

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

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

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)

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

應用側連接數過小

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

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

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

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

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

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

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

buffer pool太小

連接數是上去了，速度也提升了。

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

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

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

也就是說，如果我的buffer pool 越大，那我們能放的數據頁就越多，相應的，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表示讀請求的次數。

Innodb_buffer_pool_reads 表示從物理磁盤中讀取數據的請求次數。

所以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層也可以加個緩存，直接將第一次查詢的結果緩存下來，這樣下次查詢就能立刻返回，聽著挺美的。

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

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

總結

• 數據查詢過慢一般是索引問題，可能是因為選錯索引，也可能是因為查詢的行數太多。
• 客戶端和數據庫連接數過小，會限制sql的查詢并發(fā)數，增大連接數可以提升速度。
• innodb里會有一層內存buffer pool用于提升查詢速度，命中率一般>99%，如果低于這個值，可以考慮增大buffer pool的大小，這樣也可以提升速度。
• 查詢緩存（query cache）確實能為查詢提速，但一般不建議打開，因為限制比較大，并且8.0以后的mysql里已經將這個功能干掉了。

最后

最近原創(chuàng)更文的閱讀量穩(wěn)步下跌，思前想后，夜里輾轉反側。

我有個不成熟的請求。

離開廣東好長時間了，好久沒人叫我靚仔了。

大家可以在評論區(qū)里，叫我一靚仔嗎？

我這么善良質樸的愿望，能被滿足嗎？

如果實在叫不出口的話，能幫我點下右下角的點贊和在看嗎？

【