Mysql數(shù)據(jù)庫查詢好慢，除了索引，還能因?yàn)槭裁矗?/h1>
我熟練應(yīng)用ctrl c和ctrl v 開發(fā)curd代碼好多年了。
mysql查詢?yōu)槭裁磿P(guān)于這個(gè)問題，在實(shí)際開發(fā)經(jīng)常會遇到，而面試中，也是個(gè)高頻題。
遇到這種問題，我們一般也會想到是因?yàn)樗饕?/p>
那除開索引之外，還有哪些因素會導(dǎo)致數(shù)據(jù)庫查詢變慢呢？
有哪些操作，可以提升mysql的查詢能力呢？
今天這篇文章，我們就來聊聊會導(dǎo)致數(shù)據(jù)庫查詢變慢的場景有哪些，并給出原因和解決方案。
數(shù)據(jù)庫查詢流程
我們先來看下，一條查詢語句下來，會經(jīng)歷哪些流程。
比如我們有一張數(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;
我們平常寫的應(yīng)用代碼（go或C++之類的），這時(shí)候就叫客戶端了。
客戶端底層會帶著賬號密碼，嘗試向mysql建立一條TCP長鏈接。
mysql的連接管理模塊會對這條連接進(jìn)行管理。
建立連接后，客戶端執(zhí)行一條查詢sql語句。比如：
select?*?from?user?where?gender?=?1?and?age?=?100;
客戶端會將sql語句通過網(wǎng)絡(luò)連接給mysql。
mysql收到sql語句后，會在分析器中先判斷下SQL語句有沒有語法錯誤，比如select，如果少打一個(gè)l，寫成slect，則會報(bào)錯You have an error in your SQL syntax;。這個(gè)報(bào)錯對于我這樣的手殘黨來說可以說是很熟悉了。
接下來是優(yōu)化器，在這里會根據(jù)一定的規(guī)則選擇該用什么索引。
之后，才是通過執(zhí)行器去調(diào)用存儲引擎的接口函數(shù)。
Mysql架構(gòu) 存儲引擎類似于一個(gè)個(gè)組件，它們才是mysql真正獲取一行行數(shù)據(jù)并返回?cái)?shù)據(jù)的地方，存儲引擎是可以替換更改的，既可以用不支持事務(wù)的MyISAM，也可以替換成支持事務(wù)的Innodb。這個(gè)可以在建表的時(shí)候指定。比如
CREATE?TABLE?`user`?(
??...
)?ENGINE=InnoDB;
現(xiàn)在最常用的是InnoDB。
我們就重點(diǎn)說這個(gè)。
InnoDB中，因?yàn)橹苯硬僮鞔疟P會比較慢，所以加了一層內(nèi)存提提速，叫buffer pool，這里面，放了很多內(nèi)存頁，每一頁16KB，有些內(nèi)存頁放的是數(shù)據(jù)庫表里看到的那種一行行的數(shù)據(jù)，有些則是放的索引信息。
bufferPool與磁盤 查詢SQL到了InnoDB中。會根據(jù)前面優(yōu)化器里計(jì)算得到的索引，去查詢相應(yīng)的索引頁，如果不在buffer pool里則從磁盤里加載索引頁。再通過索引頁加速查詢，得到數(shù)據(jù)頁的具體位置。如果這些數(shù)據(jù)頁不在buffer pool中，則從磁盤里加載進(jìn)來。
這樣我們就得到了我們想要的一行行數(shù)據(jù)。
索引頁與磁盤頁的關(guān)系 最后將得到的數(shù)據(jù)結(jié)果返回給客戶端。
慢查詢分析
如果上面的流程比較慢的話，我們可以通過開啟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í)行時(shí)間都會被記錄下來，此時(shí)你想查看有哪些語句被記錄下來了，可以執(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)
關(guān)注下上面的query_id，比如select * from user where age>=60對應(yīng)的query_id是1，如果你想查看這條SQL語句的具體耗時(shí)，那么可以執(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)
通過上面的各個(gè)項(xiàng)，大家就可以看到具體耗時(shí)在哪。比如從上面可以看出Sending data的耗時(shí)最大，這個(gè)是指執(zhí)行器開始查詢數(shù)據(jù)并將數(shù)據(jù)發(fā)送給客戶端的耗時(shí)，因?yàn)槲业倪@張表符合條件的數(shù)據(jù)有好幾萬條，所以這塊耗時(shí)最大，也符合預(yù)期。
一般情況下，我們開發(fā)過程中，耗時(shí)大部分時(shí)候都在Sending data階段，而這一階段里如果慢的話，最容易想到的還是索引相關(guān)的原因。
索引相關(guān)原因
索引相關(guān)的問題，一般能用explain命令幫助分析。通過它能看到用了哪些索引，大概會掃描多少行之類的信息。
mysql會在優(yōu)化器階段里看下選擇哪個(gè)索引，查詢速度會更快。
一般主要考慮幾個(gè)因素，比如：
選擇這個(gè)索引大概要掃描多少行（rows）
為了把這些行取出來，需要讀多少個(gè)16kb的頁
走普通索引需要回表，主鍵索引則不需要，回表成本大不大？
回到show profile中提到的sql語句，我們使用explain select * from user where age>=60?分析一下。
explain sql 上面的這條語句，使用的type為ALL，意味著是全表掃描，possible_keys是指可能用得到的索引，這里可能使用到的索引是為age建的普通索引，但實(shí)際上數(shù)據(jù)庫使用的索引是在key那一列，是NULL。也就是說這句sql不走索引，全表掃描。
這個(gè)是因?yàn)閿?shù)據(jù)表里，符合條件的數(shù)據(jù)行數(shù)（rows）太多，如果使用age索引，那么需要將它們從age索引中讀出來，并且age索引是普通索引，還需要回表找到對應(yīng)的主鍵才能找到對應(yīng)的數(shù)據(jù)頁。算下來還不如直接走主鍵劃算。于是最終選擇了全表掃描。
當(dāng)然上面只是舉了個(gè)例子，實(shí)際上，mysql執(zhí)行sql時(shí)，不用索引或者用的索引不符合我們預(yù)期這件事經(jīng)常發(fā)生，索引失效的場景有很多，比如用了不等號，隱式轉(zhuǎn)換等，這個(gè)相信大家背八股文的時(shí)候也背過不少了，我也不再贅述。
聊兩個(gè)生產(chǎn)中容易遇到的問題吧。
索引不符合預(yù)期
實(shí)際開發(fā)中有些情況比較特殊，比如有些數(shù)據(jù)庫表一開始數(shù)據(jù)量小，索引少，執(zhí)行sql時(shí)，確實(shí)使用了符合你預(yù)期的索引。但隨時(shí)時(shí)間邊長，開發(fā)的人變多了，數(shù)據(jù)量也變大了，甚至還可能會加入一些其他重復(fù)多余的索引，就有可能出現(xiàn)用著用著，用到了不符合你預(yù)期的其他索引了。從而導(dǎo)致查詢突然變慢。
這種問題，也好解決，可以通過force index指定索引。比如
force index指定索引 通過explain可以看出，加了force index之后，sql就選用了idx_age這個(gè)索引了。
走了索引還是很慢
有些sql，用explain命令看，明明是走索引的，但還是很慢。一般是兩種情況：
第一種是索引區(qū)分度太低，比如網(wǎng)頁全路徑的url鏈接，這拿來做索引，一眼看過去全都是同一個(gè)域名，如果前綴索引的長度建得不夠長，那這走索引跟走全表掃描似的，正確姿勢是盡量讓索引的區(qū)分度更高，比如域名去掉，只拿后面URI部分去做索引。
索引前綴區(qū)分度太低 第二種是索引中匹配到的數(shù)據(jù)太大，這時(shí)候需要關(guān)注的是explain里的rows字段了。
它是用于預(yù)估這個(gè)查詢語句需要查的行數(shù)的，它不一定完全準(zhǔn)確，但可以體現(xiàn)個(gè)大概量級。
當(dāng)它很大時(shí)，一般常見的是下面幾種情況。
如果這個(gè)字段具有唯一的屬性，比如電話號碼等，一般是不應(yīng)該有大量重復(fù)的，那可能是你代碼邏輯出現(xiàn)了大量重復(fù)插入的操作，你需要檢查下代碼邏輯，或者需要加個(gè)唯一索引限制下。
如果這個(gè)字段下的數(shù)據(jù)就是會很大，是否需要全部拿？如果不需要，加個(gè)limit限制下。如果確實(shí)要拿全部，那也不能一次性全拿，今天你數(shù)據(jù)量小，可能一次取一兩萬都沒啥壓力，萬一哪天漲到了十萬級別，那一次性取就有點(diǎn)吃不消了。你可能需要分批次取，具體操作是先用order by id排序一下，拿到一批數(shù)據(jù)后取最大id作為下次取數(shù)據(jù)的起始位置。
連接數(shù)過小
索引相關(guān)的原因我們聊完了，我們來聊聊，除了索引之外，還有哪些因素會限制我們的查詢速度的。
我們可以看到，mysql的server層里有個(gè)連接管理，它的作用是管理客戶端和mysql之間的長連接。
正常情況下，客戶端與server層如果只有一條連接，那么在執(zhí)行sql查詢之后，只能阻塞等待結(jié)果返回，如果有大量查詢同時(shí)并發(fā)請求，那么后面的請求都需要等待前面的請求執(zhí)行完成后，才能開始執(zhí)行。
連接過少會導(dǎo)致sql阻塞 因此很多時(shí)候我們的應(yīng)用程序，比如go或java這些，會打印出sql執(zhí)行了幾分鐘的日志，但實(shí)際上你把這條語句單獨(dú)拎出來執(zhí)行，卻又是毫秒級別的。這都是因?yàn)檫@些sql語句在等待前面的sql執(zhí)行完成。
怎么解決呢？
如果我們能多建幾條連接，那么請求就可以并發(fā)執(zhí)行，后面的連接就不用等那么久了。
增加連接可以加快執(zhí)行sql 而連接數(shù)過小的問題，受數(shù)據(jù)庫和客戶端兩側(cè)同時(shí)限制。
數(shù)據(jù)庫連接數(shù)過小
Mysql的最大連接數(shù)默認(rèn)是100, 最大可以達(dá)到16384。
可以通過設(shè)置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。
應(yīng)用側(cè)連接數(shù)過小
數(shù)據(jù)庫連接大小是調(diào)整過了，但貌似問題還是沒有變化？還是有很多sql執(zhí)行達(dá)到了幾分鐘，甚至超時(shí)？
那有可能是因?yàn)槟銘?yīng)用側(cè)（go，java寫的應(yīng)用，也就是mysql的客戶端）的連接數(shù)也過小。
應(yīng)用側(cè)與mysql底層的連接，是基于TCP協(xié)議的長鏈接，而TCP協(xié)議，需要經(jīng)過三次握手和四次揮手來實(shí)現(xiàn)建連和釋放。如果我每次執(zhí)行sql都重新建立一個(gè)新的連接的話，那就要不斷握手和揮手，這很耗時(shí)。所以一般會建立一個(gè)長連接池，連接用完之后，塞到連接池里，下次要執(zhí)行sql的時(shí)候，再從里面撈一條連接出來用，非常環(huán)保。
連接池原理 我們一般寫代碼的時(shí)候，都會通過第三方的orm庫來對數(shù)據(jù)庫進(jìn)行操作，而成熟的orm庫，百分之一千萬都會有個(gè)連接池。
而這個(gè)連接池，一般會有個(gè)大小。這個(gè)大小就控制了你的連接數(shù)最大值，如果說你的連接池太小，都還沒有數(shù)據(jù)庫的大，那調(diào)了數(shù)據(jù)庫的最大連接數(shù)也沒啥作用。
一般情況下，可以翻下你使用的orm庫的文檔，看下怎么設(shè)置這個(gè)連接池的大小，就幾行代碼的事情，改改就好。比如go語言里的gorm里是這么設(shè)置的
func?Init()?{
??db,?err?:=?gorm.Open(mysql.Open(conn),?config)
????sqlDB,?err?:=?db.DB()
????//?SetMaxIdleConns?設(shè)置空閑連接池中連接的最大數(shù)量
????sqlDB.SetMaxIdleConns(200)
????//?SetMaxOpenConns?設(shè)置打開數(shù)據(jù)庫連接的最大數(shù)量
????sqlDB.SetMaxOpenConns(1000)
}
buffer pool太小
連接數(shù)是上去了，速度也提升了。
曾經(jīng)遇到過面試官會追問，有沒有其他辦法可以讓速度更快呢？
那必須要眉頭緊鎖，假裝思考，然后說：有的。
我們在前面的數(shù)據(jù)庫查詢流程里，提到了進(jìn)了innodb之后，會有一層內(nèi)存buffer pool，用于將磁盤數(shù)據(jù)頁加載到內(nèi)存頁中，只要查詢到buffer pool里有，就可以直接返回，否則就要走磁盤IO，那就慢了。
也就是說，如果我的buffer pool 越大，那我們能放的數(shù)據(jù)頁就越多，相應(yīng)的，sql查詢時(shí)就更可能命中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。
如果想要調(diào)大一點(diǎn)。可以執(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大小正常，只是別的原因導(dǎo)致的查詢變慢，那改buffer pool毫無意義。
但問題又來了。
怎么知道buffer pool是不是太小了？
這個(gè)我們可以看buffer pool的緩存命中率。
查看buffer pool命中率 通過?show status like 'Innodb_buffer_pool_%';可以看到跟buffer pool有關(guān)的一些信息。
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%以上，如果低于這個(gè)值，才需要考慮加大innodb buffer pool的大小。
當(dāng)然，還可以把這個(gè)命中率做到監(jiān)控里，這樣半夜sql變慢了，早上上班還能定位到原因，就很舒服。
還有哪些騷操作？
前面提到的是在存儲引擎層里加入了buffer pool用于緩存內(nèi)存頁，這樣可以加速查詢。
那同樣的道理，server層也可以加個(gè)緩存，直接將第一次查詢的結(jié)果緩存下來，這樣下次查詢就能立刻返回，聽著挺美的。
按道理，如果命中緩存的話，確實(shí)是能為查詢加速的。但這個(gè)功能限制很大，其中最大的問題是只要數(shù)據(jù)庫表被更新過，表里面的所有緩存都會失效，數(shù)據(jù)表頻繁的更新，就會帶來頻繁的緩存失效。所以這個(gè)功能只適合用于那些不怎么更新的數(shù)據(jù)表。
另外，這個(gè)功能在8.0版本之后，就被干掉了。所以這功能用來聊聊天可以，沒必要真的在生產(chǎn)中使用啊。
查詢緩存被刪除
總結(jié)
數(shù)據(jù)查詢過慢一般是索引問題，可能是因?yàn)檫x錯索引，也可能是因?yàn)椴樵兊男袛?shù)太多。
客戶端和數(shù)據(jù)庫連接數(shù)過小，會限制sql的查詢并發(fā)數(shù)，增大連接數(shù)可以提升速度。
innodb里會有一層內(nèi)存buffer pool用于提升查詢速度，命中率一般>99%，如果低于這個(gè)值，可以考慮增大buffer pool的大小，這樣也可以提升速度。
查詢緩存（query cache）確實(shí)能為查詢提速，但一般不建議打開，因?yàn)橄拗票容^大，并且8.0以后的Mysql里已經(jīng)將這個(gè)功能干掉了。
歡迎添加小編微信，進(jìn)入交流群
推薦閱讀：
我熟練應(yīng)用ctrl c和ctrl v 開發(fā)curd代碼好多年了。
mysql查詢?yōu)槭裁磿P(guān)于這個(gè)問題，在實(shí)際開發(fā)經(jīng)常會遇到，而面試中，也是個(gè)高頻題。
遇到這種問題，我們一般也會想到是因?yàn)樗饕?/p>
那除開索引之外，還有哪些因素會導(dǎo)致數(shù)據(jù)庫查詢變慢呢？
有哪些操作，可以提升mysql的查詢能力呢？
今天這篇文章，我們就來聊聊會導(dǎo)致數(shù)據(jù)庫查詢變慢的場景有哪些，并給出原因和解決方案。
數(shù)據(jù)庫查詢流程
我們先來看下，一條查詢語句下來，會經(jīng)歷哪些流程。
比如我們有一張數(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;
我們平常寫的應(yīng)用代碼（go或C++之類的），這時(shí)候就叫客戶端了。
客戶端底層會帶著賬號密碼，嘗試向mysql建立一條TCP長鏈接。
mysql的連接管理模塊會對這條連接進(jìn)行管理。
建立連接后，客戶端執(zhí)行一條查詢sql語句。比如：
select?*?from?user?where?gender?=?1?and?age?=?100;
客戶端會將sql語句通過網(wǎng)絡(luò)連接給mysql。
mysql收到sql語句后，會在分析器中先判斷下SQL語句有沒有語法錯誤，比如select，如果少打一個(gè)l，寫成slect，則會報(bào)錯You have an error in your SQL syntax;。這個(gè)報(bào)錯對于我這樣的手殘黨來說可以說是很熟悉了。
接下來是優(yōu)化器，在這里會根據(jù)一定的規(guī)則選擇該用什么索引。
之后，才是通過執(zhí)行器去調(diào)用存儲引擎的接口函數(shù)。
存儲引擎類似于一個(gè)個(gè)組件，它們才是mysql真正獲取一行行數(shù)據(jù)并返回?cái)?shù)據(jù)的地方，存儲引擎是可以替換更改的，既可以用不支持事務(wù)的MyISAM，也可以替換成支持事務(wù)的Innodb。這個(gè)可以在建表的時(shí)候指定。比如
CREATE?TABLE?`user`?(
??...
)?ENGINE=InnoDB;
現(xiàn)在最常用的是InnoDB。
我們就重點(diǎn)說這個(gè)。
InnoDB中，因?yàn)橹苯硬僮鞔疟P會比較慢，所以加了一層內(nèi)存提提速，叫buffer pool，這里面，放了很多內(nèi)存頁，每一頁16KB，有些內(nèi)存頁放的是數(shù)據(jù)庫表里看到的那種一行行的數(shù)據(jù)，有些則是放的索引信息。
查詢SQL到了InnoDB中。會根據(jù)前面優(yōu)化器里計(jì)算得到的索引，去查詢相應(yīng)的索引頁，如果不在buffer pool里則從磁盤里加載索引頁。再通過索引頁加速查詢，得到數(shù)據(jù)頁的具體位置。如果這些數(shù)據(jù)頁不在buffer pool中，則從磁盤里加載進(jìn)來。
這樣我們就得到了我們想要的一行行數(shù)據(jù)。
最后將得到的數(shù)據(jù)結(jié)果返回給客戶端。
慢查詢分析
如果上面的流程比較慢的話，我們可以通過開啟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í)行時(shí)間都會被記錄下來，此時(shí)你想查看有哪些語句被記錄下來了，可以執(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)
關(guān)注下上面的query_id，比如select * from user where age>=60對應(yīng)的query_id是1，如果你想查看這條SQL語句的具體耗時(shí)，那么可以執(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)
通過上面的各個(gè)項(xiàng)，大家就可以看到具體耗時(shí)在哪。比如從上面可以看出Sending data的耗時(shí)最大，這個(gè)是指執(zhí)行器開始查詢數(shù)據(jù)并將數(shù)據(jù)發(fā)送給客戶端的耗時(shí)，因?yàn)槲业倪@張表符合條件的數(shù)據(jù)有好幾萬條，所以這塊耗時(shí)最大，也符合預(yù)期。
一般情況下，我們開發(fā)過程中，耗時(shí)大部分時(shí)候都在Sending data階段，而這一階段里如果慢的話，最容易想到的還是索引相關(guān)的原因。
索引相關(guān)原因
索引相關(guān)的問題，一般能用explain命令幫助分析。通過它能看到用了哪些索引，大概會掃描多少行之類的信息。
mysql會在優(yōu)化器階段里看下選擇哪個(gè)索引，查詢速度會更快。
一般主要考慮幾個(gè)因素，比如：
選擇這個(gè)索引大概要掃描多少行（rows）
為了把這些行取出來，需要讀多少個(gè)16kb的頁
走普通索引需要回表，主鍵索引則不需要，回表成本大不大？
回到show profile中提到的sql語句，我們使用explain select * from user where age>=60?分析一下。
上面的這條語句，使用的type為ALL，意味著是全表掃描，possible_keys是指可能用得到的索引，這里可能使用到的索引是為age建的普通索引，但實(shí)際上數(shù)據(jù)庫使用的索引是在key那一列，是NULL。也就是說這句sql不走索引，全表掃描。
這個(gè)是因?yàn)閿?shù)據(jù)表里，符合條件的數(shù)據(jù)行數(shù)（rows）太多，如果使用age索引，那么需要將它們從age索引中讀出來，并且age索引是普通索引，還需要回表找到對應(yīng)的主鍵才能找到對應(yīng)的數(shù)據(jù)頁。算下來還不如直接走主鍵劃算。于是最終選擇了全表掃描。
當(dāng)然上面只是舉了個(gè)例子，實(shí)際上，mysql執(zhí)行sql時(shí)，不用索引或者用的索引不符合我們預(yù)期這件事經(jīng)常發(fā)生，索引失效的場景有很多，比如用了不等號，隱式轉(zhuǎn)換等，這個(gè)相信大家背八股文的時(shí)候也背過不少了，我也不再贅述。
聊兩個(gè)生產(chǎn)中容易遇到的問題吧。
索引不符合預(yù)期
實(shí)際開發(fā)中有些情況比較特殊，比如有些數(shù)據(jù)庫表一開始數(shù)據(jù)量小，索引少，執(zhí)行sql時(shí)，確實(shí)使用了符合你預(yù)期的索引。但隨時(shí)時(shí)間邊長，開發(fā)的人變多了，數(shù)據(jù)量也變大了，甚至還可能會加入一些其他重復(fù)多余的索引，就有可能出現(xiàn)用著用著，用到了不符合你預(yù)期的其他索引了。從而導(dǎo)致查詢突然變慢。
這種問題，也好解決，可以通過force index指定索引。比如
通過explain可以看出，加了force index之后，sql就選用了idx_age這個(gè)索引了。
走了索引還是很慢
有些sql，用explain命令看，明明是走索引的，但還是很慢。一般是兩種情況：
第一種是索引區(qū)分度太低，比如網(wǎng)頁全路徑的url鏈接，這拿來做索引，一眼看過去全都是同一個(gè)域名，如果前綴索引的長度建得不夠長，那這走索引跟走全表掃描似的，正確姿勢是盡量讓索引的區(qū)分度更高，比如域名去掉，只拿后面URI部分去做索引。
第二種是索引中匹配到的數(shù)據(jù)太大，這時(shí)候需要關(guān)注的是explain里的rows字段了。
它是用于預(yù)估這個(gè)查詢語句需要查的行數(shù)的，它不一定完全準(zhǔn)確，但可以體現(xiàn)個(gè)大概量級。
當(dāng)它很大時(shí)，一般常見的是下面幾種情況。
如果這個(gè)字段具有唯一的屬性，比如電話號碼等，一般是不應(yīng)該有大量重復(fù)的，那可能是你代碼邏輯出現(xiàn)了大量重復(fù)插入的操作，你需要檢查下代碼邏輯，或者需要加個(gè)唯一索引限制下。
如果這個(gè)字段下的數(shù)據(jù)就是會很大，是否需要全部拿？如果不需要，加個(gè)
limit限制下。如果確實(shí)要拿全部，那也不能一次性全拿，今天你數(shù)據(jù)量小，可能一次取一兩萬都沒啥壓力，萬一哪天漲到了十萬級別，那一次性取就有點(diǎn)吃不消了。你可能需要分批次取，具體操作是先用order by id排序一下，拿到一批數(shù)據(jù)后取最大id作為下次取數(shù)據(jù)的起始位置。
連接數(shù)過小
索引相關(guān)的原因我們聊完了，我們來聊聊，除了索引之外，還有哪些因素會限制我們的查詢速度的。
我們可以看到，mysql的server層里有個(gè)連接管理，它的作用是管理客戶端和mysql之間的長連接。
正常情況下，客戶端與server層如果只有一條連接，那么在執(zhí)行sql查詢之后，只能阻塞等待結(jié)果返回，如果有大量查詢同時(shí)并發(fā)請求，那么后面的請求都需要等待前面的請求執(zhí)行完成后，才能開始執(zhí)行。
因此很多時(shí)候我們的應(yīng)用程序，比如go或java這些，會打印出sql執(zhí)行了幾分鐘的日志，但實(shí)際上你把這條語句單獨(dú)拎出來執(zhí)行，卻又是毫秒級別的。這都是因?yàn)檫@些sql語句在等待前面的sql執(zhí)行完成。
怎么解決呢？
如果我們能多建幾條連接，那么請求就可以并發(fā)執(zhí)行，后面的連接就不用等那么久了。
而連接數(shù)過小的問題，受數(shù)據(jù)庫和客戶端兩側(cè)同時(shí)限制。
數(shù)據(jù)庫連接數(shù)過小
Mysql的最大連接數(shù)默認(rèn)是100, 最大可以達(dá)到16384。
可以通過設(shè)置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。
應(yīng)用側(cè)連接數(shù)過小
數(shù)據(jù)庫連接大小是調(diào)整過了，但貌似問題還是沒有變化？還是有很多sql執(zhí)行達(dá)到了幾分鐘，甚至超時(shí)？
那有可能是因?yàn)槟銘?yīng)用側(cè)（go，java寫的應(yīng)用，也就是mysql的客戶端）的連接數(shù)也過小。
應(yīng)用側(cè)與mysql底層的連接，是基于TCP協(xié)議的長鏈接，而TCP協(xié)議，需要經(jīng)過三次握手和四次揮手來實(shí)現(xiàn)建連和釋放。如果我每次執(zhí)行sql都重新建立一個(gè)新的連接的話，那就要不斷握手和揮手，這很耗時(shí)。所以一般會建立一個(gè)長連接池，連接用完之后，塞到連接池里，下次要執(zhí)行sql的時(shí)候，再從里面撈一條連接出來用，非常環(huán)保。
我們一般寫代碼的時(shí)候，都會通過第三方的orm庫來對數(shù)據(jù)庫進(jìn)行操作，而成熟的orm庫，百分之一千萬都會有個(gè)連接池。
而這個(gè)連接池，一般會有個(gè)大小。這個(gè)大小就控制了你的連接數(shù)最大值，如果說你的連接池太小，都還沒有數(shù)據(jù)庫的大，那調(diào)了數(shù)據(jù)庫的最大連接數(shù)也沒啥作用。
一般情況下，可以翻下你使用的orm庫的文檔，看下怎么設(shè)置這個(gè)連接池的大小，就幾行代碼的事情，改改就好。比如go語言里的gorm里是這么設(shè)置的
func?Init()?{
??db,?err?:=?gorm.Open(mysql.Open(conn),?config)
????sqlDB,?err?:=?db.DB()
????//?SetMaxIdleConns?設(shè)置空閑連接池中連接的最大數(shù)量
????sqlDB.SetMaxIdleConns(200)
????//?SetMaxOpenConns?設(shè)置打開數(shù)據(jù)庫連接的最大數(shù)量
????sqlDB.SetMaxOpenConns(1000)
}
buffer pool太小
連接數(shù)是上去了，速度也提升了。
曾經(jīng)遇到過面試官會追問，有沒有其他辦法可以讓速度更快呢？
那必須要眉頭緊鎖，假裝思考，然后說：有的。
我們在前面的數(shù)據(jù)庫查詢流程里，提到了進(jìn)了innodb之后，會有一層內(nèi)存buffer pool，用于將磁盤數(shù)據(jù)頁加載到內(nèi)存頁中，只要查詢到buffer pool里有，就可以直接返回，否則就要走磁盤IO，那就慢了。
也就是說，如果我的buffer pool 越大，那我們能放的數(shù)據(jù)頁就越多，相應(yīng)的，sql查詢時(shí)就更可能命中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。
如果想要調(diào)大一點(diǎn)。可以執(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大小正常，只是別的原因導(dǎo)致的查詢變慢，那改buffer pool毫無意義。
但問題又來了。
怎么知道buffer pool是不是太小了？
這個(gè)我們可以看buffer pool的緩存命中率。
通過?show status like 'Innodb_buffer_pool_%';可以看到跟buffer pool有關(guān)的一些信息。
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%以上，如果低于這個(gè)值，才需要考慮加大innodb buffer pool的大小。
當(dāng)然，還可以把這個(gè)命中率做到監(jiān)控里，這樣半夜sql變慢了，早上上班還能定位到原因，就很舒服。
還有哪些騷操作？
前面提到的是在存儲引擎層里加入了buffer pool用于緩存內(nèi)存頁，這樣可以加速查詢。
那同樣的道理，server層也可以加個(gè)緩存，直接將第一次查詢的結(jié)果緩存下來，這樣下次查詢就能立刻返回，聽著挺美的。
按道理，如果命中緩存的話，確實(shí)是能為查詢加速的。但這個(gè)功能限制很大，其中最大的問題是只要數(shù)據(jù)庫表被更新過，表里面的所有緩存都會失效，數(shù)據(jù)表頻繁的更新，就會帶來頻繁的緩存失效。所以這個(gè)功能只適合用于那些不怎么更新的數(shù)據(jù)表。
另外，這個(gè)功能在8.0版本之后，就被干掉了。所以這功能用來聊聊天可以，沒必要真的在生產(chǎn)中使用啊。
總結(jié)
數(shù)據(jù)查詢過慢一般是索引問題，可能是因?yàn)檫x錯索引，也可能是因?yàn)椴樵兊男袛?shù)太多。
客戶端和數(shù)據(jù)庫連接數(shù)過小，會限制sql的查詢并發(fā)數(shù)，增大連接數(shù)可以提升速度。
innodb里會有一層內(nèi)存buffer pool用于提升查詢速度，命中率一般>99%，如果低于這個(gè)值，可以考慮增大buffer pool的大小，這樣也可以提升速度。
查詢緩存（query cache）確實(shí)能為查詢提速，但一般不建議打開，因?yàn)橄拗票容^大，并且8.0以后的Mysql里已經(jīng)將這個(gè)功能干掉了。
歡迎添加小編微信，進(jìn)入交流群
推薦閱讀：