為什麼我使用了索引,查詢還是慢?
來源:rrd.me/f9jAG
經常有同學問我,我的一個SQL語句使用了索引,為什麼還是會進入到慢查詢之中呢?今天我們就從這個問題開始來聊一聊索引和慢查詢。
另外插入一個題外話,個人認為團隊要合理的使用ORM,可以參考 ORM的權衡和抉擇()。合理利用的是ORM在物件導向和寫操作方面的優勢,避免聯合查詢上可能產生的坑(當然如果你的Linq查詢能力很強另當別論),因為ORM遮蔽了太多的DB底層的知識內容,對程式設計師不是件好事,對效能有極致追求,但是ORM理解不透徹的團隊更加要謹慎。
案例剖析
言歸正傳,為了實驗,我建立瞭如下表:
CREATE TABLE `T`(
`id` int(11) NOT NULL,
`a` int(11) DEFAUT NULL,
PRIMARY KEY(`id`),
KEY `a`(`a`)
) ENGINE=InnoDB;
該表有三個欄位,其中用id是主鍵索引,a是普通索引。
首先SQL判斷一個語句是不是慢查詢語句,用的是語句的執行時間。他把語句執行時間跟long_query_time這個系統引數作比較,如果語句執行時間比它還大,就會把這個語句記錄到慢查詢日誌裡面,這個引數的預設值是10秒。當然在生產上,我們不會設定這麼大,一般會設定1秒,對於一些比較敏感的業務,可能會設定一個比1秒還小的值。
語句執行過程中有沒有用到表的索引,可以透過explain一個語句的輸出結果來看KEY的值不是NULL。
我們看下 explain select * from t;
的KEY結果是NULL
(圖一)
explain select * from t where id=2;
的KEY結果是PRIMARY,就是我們常說的使用了主鍵索引
(圖二)
explain select a from t;
的KEY結果是a,表示使用了a這個索引。
(圖三)
雖然後兩個查詢的KEY都不是NULL,但是最後一個實際上掃描了整個索引樹a。
假設這個表的資料量有100萬行,圖二的語句還是可以執行很快,但是圖三就肯定很慢了。如果是更極端的情況,比如,這個資料庫上CPU壓力非常的高,那麼可能第2個語句的執行時間也會超過long_query_time,會進入到慢查詢日誌裡面。
所以我們可以得出一個結論:是否使用索引和是否進入慢查詢之間並沒有必然的聯絡。使用索引只是表示了一個SQL語句的執行過程,而是否進入到慢查詢是由它的執行時間決定的,而這個執行時間,可能會受各種外部因素的影響。換句話來說,使用了索引你的語句可能依然會很慢。
全索引掃描的不足
那如果我們在更深層次的看這個問題,其實他還潛藏了一個問題需要澄清,就是什麼叫做使用了索引。
我們都知道,InnoDB是索引組織表,所有的資料都是儲存在索引樹上面的。比如上面的表t,這個表包含了兩個索引,一個主鍵索引和一個普通索引。在InnoDB裡,資料是放在主鍵索引裡的。如圖所示:
可以看到資料都放在主鍵索引上,如果從邏輯上說,所有的InnoDB表上的查詢,都至少用了一個索引,所以現在我問你一個問題,如果你執行select from t where id>0
,你覺得這個語句有用上索引嗎?
我們看上面這個語句的explain的輸出結果顯示的是PRIMARY。其實從資料上你是知道的,這個語句一定是做了全面掃描。但是最佳化器認為,這個語句的執行過程中,需要根據主鍵索引,定位到第1個滿足ID>0的值,也算用到了索引。
所以即使explain的結果裡寫的KEY不是NULL,實際上也可能是全表掃描的,因此InnoDB裡面只有一種情況叫做沒有使用索引,那就是從主鍵索引的最左邊的葉節點開始,向右掃描整個索引樹。
也就是說,沒有使用索引並不是一個準確的描述。
你可以用全表掃描來表示一個查詢遍歷了整個主鍵索引樹;
也可以用全索引掃描,來說明像select a from t;這樣的查詢,他掃描了整個普通索引樹;
而select * from t where id=2這樣的語句,才是我們平時說的使用了索引。他表示的意思是,我們使用了索引的快速搜尋功能,並且有效的減少了掃描行數。
索引的過濾性要足夠好
根據以上解剖,我們知道全索引掃描會讓查詢變慢,接下來就要來談談索引的過濾性。
假設你現在維護了一個表,這個表記錄了中國14億人的基本資訊,現在要查出所有年齡在10~15歲之間的姓名和基本資訊,那麼你的語句會這麼寫,select * from t_people where age between 10 and 15
。
你一看這個語句一定要在age欄位上開始建立索引了,否則就是個全面掃描,但是你會發現,在你建立索引以後,這個語句還是執行慢,因為滿足這個條件的資料可能有超過1億行。
我們來看看建立索引以後,這個表的組織結構圖:
這個語句的執行流程是這樣的:
從索引上用樹搜尋,取到第1個age等於10的記錄,得到它的主鍵id的值,根據id的值去主鍵索引取整行的資訊,作為結果集的一部分返回;
在索引age上向右掃描,取下一個id的值,到主鍵索引上取整行資訊,作為結果集的一部分返回;
重複上面的步驟,直到碰到第1個age大於15的記錄;
你看這個語句,雖然他用了索引,但是他掃描超過了1億行。所以你現在知道了,當我們在討論有沒有使用索引的時候,其實我們關心的是掃描行數。
對於一個大表,不止要有索引,索引的過濾性還要足夠好。
像剛才這個例子的age,它的過濾性就不夠好,在設計表結構的時候,我們要讓所有的過濾性足夠好,也就是區分度足夠高。
回表的代價
那麼過濾性好了,是不是表示查詢的掃描行數就一定少呢?
我們再來看一個例子:
如果你的執行語句是 select * from t_people where name='張三' and age=8
t_people表上有一個索引是姓名和年齡的聯合索引,那這個聯合索引的過濾性應該不錯,可以在聯合索引上快速找到第1個姓名是張三,並且年齡是8的小朋友,當然這樣的小朋友應該不多,因此向右掃描的行數很少,查詢效率就很高。
但是查詢的過濾性和索引的過濾性可不一定是一樣的,如果現在你的需求是查出所有名字的第1個字是張,並且年齡是8歲的所有小朋友,你的語句會怎麼寫呢?
你的語句要怎麼寫?很顯然你會這麼寫:select * from t_people where name like '張%' and age=8;
在MySQL5.5和之前的版本中,這個語句的執行流程是這樣的:
首先從聯合索引上找到第1個年齡欄位是張開頭的記錄,取出主鍵id,然後到主鍵索引樹上,根據id取出整行的值;
判斷年齡欄位是否等於8,如果是就作為結果集的一行返回,如果不是就丟棄。
在聯合索引上向右遍歷,並重復做回表和判斷的邏輯,直到碰到聯合索引樹上名字的第1個字不是張的記錄為止。
我們把根據id到主鍵索引上查詢整行資料這個動作,稱為回表。你可以看到這個執行過程裡面,最耗費時間的步驟就是回表,假設全國名字第1個字是張的人有8000萬,那麼這個過程就要回表8000萬次,在定位第一行記錄的時候,只能使用索引和聯合索引的最左字首,最稱為最左字首原則。
你可以看到這個執行過程,它的回表次數特別多,效能不夠好,有沒有最佳化的方法呢?
在MySQL5.6版本,引入了index condition pushdown的最佳化。我們來看看這個最佳化的執行流程:
首先從聯合索引樹上,找到第1個年齡欄位是張開頭的記錄,判斷這個索引記錄裡面,年齡的值是不是8,如果是就回表,取出整行資料,作為結果集的一部分返回,如果不是就丟棄;
在聯合索引樹上,向右遍歷,並判斷年齡欄位後,根據需要做回表,直到碰到聯合索引樹上名字的第1個字不是張的記錄為止;
這個過程跟上面的差別,是在遍歷聯合索引的過程中,將年齡等於8的條件下推到所有遍歷的過程中,減少了回表的次數,假設全國名字第1個字是張的人裡面,有100萬個是8歲的小朋友,那麼這個查詢過程中在聯合索引裡要遍歷8000萬次,而回表只需要100萬次。
虛擬列
可以看到這個最佳化的效果還是很不錯的,但是這個最佳化還是沒有繞開最左字首原則的限制,因此在聯合索引你還是要掃描8000萬行,那有沒有更進一步的最佳化方法呢?
我們可以考慮把名字的第一個字和age來做一個聯合索引。這裡可以使用MySQL5.7引入的虛擬列來實現。對應的修改表結構的SQL語句:
alter table t_people add name_first varchar(2) generated (left(name,1)),add index(name_first,age);
我們來看這個SQL語句的執行效果:
CREATE TABLE `t_people`(
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAUT NULL,
`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,KEY `name_first`(`name_first`,'age')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
首先他在people上建立一個欄位叫name_first的虛擬列,然後給name_first和age上建立一個聯合索引,並且,讓這個虛擬列的值總是等於name欄位的前兩個位元組,虛擬列在插入資料的時候不能指定值,在更新的時候也不能主動修改,它的值會根據定義自動生成,在name欄位修改的時候也會自動修改。
有了這個新的聯合索引,我們在找名字的第1個字是張,並且年齡為8的小朋友的時候,這個SQL語句就可以這麼寫:select * from t_people where name_first='張' and age=8。
這樣這個語句的執行過程,就只需要掃描聯合索引的100萬行,並回表100萬次,這個最佳化的本質是我們建立了一個更緊湊的索引,來加速了查詢的過程。
總結
本文給你介紹了索引的基本結構和一些查詢最佳化的基本思路,你現在知道了,使用索引的語句也有可能是慢查詢,我們的查詢最佳化的過程,往往就是減少掃描行數的過程。
慢查詢歸納起來大概有這麼幾種情況:
全表掃描
全索引掃描
索引過濾性不好
頻繁回表的開銷
思考
假設業務要求的就是要統計年齡在10-15歲的14億人的數量,不能增加過濾因子,那該怎麼辦?(select * from t_people where age between 10 and 15
)
假設該統計必須是OLTP,實時展示統計資料,又該怎麼解決?
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69940568/viewspace-2672347/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 為什麼 SQL 語句使用了索引,但卻還是慢查詢?SQL索引
- 為什麼所有的查詢條件都命中索引還是那麼慢?記一次慢查詢優化過程索引優化
- 為什麼所有的查詢條件都命中索引還是那麼慢?記一次慢查詢最佳化過程索引
- 索引為什麼能提供查詢效能...索引
- [破解]為什麼hibernate插入快,查詢慢
- MongoDB慢查詢與索引MongoDB索引
- 我的sql沒問題為什麼還是這麼慢|MySQL加鎖規則MySql
- 為什麼使用了高匿代理IP還是會被限制?
- Oracle 查詢行數很少,為什麼不走索引?Oracle索引
- 走索引掃描的慢查詢索引
- 什麼情況下需要建立索引? 索引的作用?為什麼能夠提高查詢速度?(索引的原理) 索引有什麼副作用嗎?索引
- MySQL索引原理及慢查詢優化MySql索引優化
- MySQL索引那些事:什麼是索引?為什麼加索引就查得快了?MySql索引
- 【MOS:1549181.1】為何在查詢中索引未被使用--為什麼索引沒有被使用索引
- 一個很簡單的查詢,為什麼用不到索引索引
- 為什麼我還在使用JavaEE?Java
- 筆記 mongo查詢慢日誌,建立索引筆記Go索引
- MySQL索引原理及慢查詢最佳化MySql索引
- 聊聊什麼是慢查、如何監控?如何排查?
- 為什麼要這樣設計,還是我理解錯誤
- MySQL查詢為什麼沒走索引?這篇文章帶你全面解析MySql索引
- 明明加了唯一索引,為什麼還是產生了重複資料?索引
- 明明加了唯一索引,為什麼還是產生重複資料?索引
- 為什麼我還在程式設計程式設計
- 為什麼反射慢?反射
- (轉)Oracle為什麼不使用我的索引?Oracle索引
- 慢查詢
- 查詢快捷鍵ctrl加什麼 wordexcel表格查詢快捷鍵是ctrl和什麼Excel
- 什麼是並行查詢及其原理並行
- 為什麼有時Oracle資料庫不用索引來查詢資料?(轉)Oracle資料庫索引
- SQL:我為什麼慢你心裡沒數嗎?SQL
- ClickHouse為什麼查詢速度快?
- MySQL索引憑什麼能讓查詢效率提高這麼多?MySql索引
- 關於 MyBatis-Plus 分頁查詢的探討 → count 都為 0 了,為什麼還要查詢記錄?MyBatis
- GC是什麼?為什麼我們要去使用它GC
- 什麼是Web workers?為什麼我們需要他Web
- MySQL 慢查詢MySql
- MySQL慢查詢MySql