避免回表，引入索引下推|提高索引命中率 | 提前下班啦

語言: CN / TW / HK
時間 2023-02-09 00:15:03

本文正在參加「技術專題19期 漫談資料庫技術」活動

為什麼這麼設計索引

  • 如果你仔細閱讀了上一部分，那麼你一定知道為什麼資料庫索引採用的是B+Tree， 說白了就是為了提高查詢效率。因為只有B+Tree 在滿足平衡特性的情況下能夠儲存更多的索引內容從未維持這顆樹在一定高度上。至於為什麼不用Hash表那就更不用說了。一個是它發生衝突概率太大另外一個就是他實際就是個連結串列，連結串列和樹相比肯定是樹查詢快。

有哪些索引

  • 在 mysql 中預設主鍵索引名是通過 pk_ 開頭進行標識的。

| 索引型別 | 關鍵詞 | | -------- | ------ | | 主鍵索引 | pk_ | | 唯一索引 | uk_ | | 普通索引 | idx_ |

普通索引

CREATE INDEX 索引名 ON 表名(欄位名)

  • 普通索引常常是用來進行條件快速查詢的，除此之外對欄位本身並無要求。

唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(欄位名)

主鍵索引

alter table 表名 add primary key(欄位名)

組合索引

CREATE INDEX 索引名 ON 表名(欄位名1,欄位名2);

  • 組合索引我們可以理解成多列組成的普通索引，所以和普通索引建立的語法相同。關於組合索引往往也是面試高頻題。其他索引都是單列索引命中與否也很好判斷。但是組合索引涉及到最左匹配原則往往是別人忘記的一點。
  • 關於最左匹配原則呢？這裡先簡單闡述下，比如你建立了一個聯合索引 `create index idx_abc on t (b,c,d)。 此時有個sqlselect * from t where e=1` 。這個sql是不會走索引的，最左匹配原則就是聯合索引需要從左開始匹配。

全文索引

全文索引首先是 MySQL 的一種索引型別，也是搜尋引擎的關鍵技術。

試想在1M大小的檔案中搜索一個詞，可能需要幾秒，在100M的檔案中可能需要幾十秒，如果在更大的檔案中搜索那麼就需要更大的系統開銷，這樣的開銷是不現實的。

所以在這樣的矛盾下出現了全文索引技術，有時候有人叫倒排文件技術。

  • 關於全文索引其實這裡和 es 的分詞倒是有點像。將分詞與資料掛鉤，這樣通過分詞條件查詢的時候就會非常的方便，詳細講解可以參考下這篇文章

如何命中索引

回表&索引下推

回表

  • 回表和索引下推都是基於B+Tree發生的必要場景。因為 mysql 中只有主鍵索引是聚簇索引也就是隻有主鍵索引葉子節點儲存的是真實的行資料，其他的普通索引葉子節點都儲存的是主鍵。當我們通過普通索引查詢時就極有可能需要在通過主鍵進行查詢真實資料，這就是回表。

image-20221117090710282.png

  • 但不是所有的普通索引查詢的 sql 都會發生回表的。下面我們來如何避免發生回表的且通過普通索引查詢的 sql 。
  • 想要避免回表我們就要做到 索引覆蓋 . 覆蓋索引並不是指索引結構上有啥不同，而是指我們的sql 儘量不要用普通索引以外的資訊。比如現在我們又一個索引 idx_name(username) ， 針對這個普通索引我們使用select username from t where username='xxx' 或者使用select id from t where username='xxx' ， 這兩種都是實現了索引覆蓋。因為上述兩種查詢 sql 條件和查詢分別用到了 username 和 id ，恰巧這兩個欄位都在 idx_name 這個索引中。所以這種情況時不需要回表的。
  • 但是這種情況下我們為了方便或者使用的類似 mybatis-Plus 這種框架都會產生類似這種sql select * from t where username='xxx' 。針對這種sql 對於資料庫來說是很不友好的。因為在業務上你可能只需要通過 username 查詢，而欄位你可能只需要id或者username 甚至是你不需要查詢欄位內容。但是sql卻去查詢了，況且為了其他類似於age 欄位 ，mysql 甚至得花一次IO去回表查詢，這樣對於高效能服務來說是一種IO浪費。

索引下推

  • 5.6前儲存引擎通過回表進行判斷其他索引是否符合
  • 5.6後儲存引擎繼續判斷其他索引是否符合之後在回表 這叫索引下推。
  • 好像還不是很明白什麼是索引下推。 現在我們建立一個索引idx_uas(username,age,sex) 這個聯合索引。 開始索引下推之前我們先了解下聯合索引如何命中這個問題。

sql CREATE TABLE `t` ( `id` int(11) NOT NULL, `user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `uk_ua` (`user_name`,`age`,`sex`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sql insert into t(id,user_name,age,sex) values(1,'zxhtom',11,1); insert into t(id,user_name,age,sex) values(2,'sun',15,2); insert into t(id,user_name,age,sex) values(3,'wukong',21,1); insert into t(id,user_name,age,sex) values(4,'zhu',11,3); insert into t(id,user_name,age,sex) values(5,'baijie',45,1); insert into t(id,user_name,age,sex) values(6,'sha',101,2); insert into t(id,user_name,age,sex) values(7,'heshang',1,1); insert into t(id,user_name,age,sex) values(8,'zxz',121,1);

image-20221117113100459.png

sql1

select * from t where user_name > 'zxhtom' and age<2;

  • 在 explain 之前我一度認為該sql 只能全表掃描了。雖然他是按照最左匹配原則書寫的條件匹配，但是上來就遇到了範圍查詢。而索引匹配在遇到範圍查詢時就會失效，這是我從書本上背來的，但是事實卻並不是我認為的那樣。

image-20221117113415756.png

  • 很明顯可以看到 Using index 說明使用了索引，並且可能是 uk_ua 這個索引。這是因為 mysql 會先將 user_name>'zxhtom' 這個條件到 uk_ua 構建的BTree中查詢到具體的頁節點上。因為最左匹配原則 ，我們能夠知道 user_name>'zxhtom' 的都會在 user_name='zxhtom' 的右側。在第一層查詢到的範圍之後再去查詢 age<2 的資料。此時age 這塊可以繼續上面定位到的節點繼續往下搜尋。

image-20221117114246944.png

sql2

select * from t where age<2;

image-20221117121818551.png

  • 這個sql 就更讓我疑惑了，最左匹配的 user_name 都沒有，為什麼還能走 uk_ua 這顆索引樹呢？但是仔細想想也能夠明白，畢竟age就存在這顆樹中。只不過查詢的範圍就比較廣。查了8條記錄這就相當於時全表掃描了。而且還設計回表，這就是引擎的不智慧。有的時候走索引不比全表掃描快。
  • 兩個 sql 對比能夠發現 最左匹配原則 遇到非精確查詢時會結束索引，而不是不走索引。

下推

  • 好了簡單瞭解最左匹配之後我們再來看看 索引下推 是什麼意思吧。

image-20221117085850633.png

  • 上面聯合索引中當我們通過最左匹配原則 匹配到節點時，我們知道其他欄位尤其是下一欄位age 在該內部是區域性遞增排列的。這個時候如果我們在根據age 進行定位查詢，效率是非常快的。這個過程我們稱之為 索引下推。

sql select * from t where user_name='zxhtom' and age=2

  • 在5.7之後的 mysql 伺服器中會先根據 user_name 定位到節點，然後在節點周邊去定位age 從而最終確定到所需節點。然後在根據葉子節點的主鍵資料回表查詢完整資料。
  • 而在 5.7之前並不支援 索引下推 。 在 5.7之前是先通過 user_name 定位到節點。然後將定位到的節點進行回表查詢完整資料，在根據回表查到的資料進行篩選age屬性。這樣做肯定不如 索引下推 來的效率高。畢竟精確定位好資料之後在回表這樣可以避免不必要的資料查詢。此時如果通過 explain 去分析的話你會發現即使使用的最左匹配 查詢 使用的也是 where 搜尋，而不是像5.7之後使用的是 index condition 。

哪些場景會索引失效

  • 這裡就僅僅羅列失效的場景，關於失效的原因以後慢慢單獨出篇 explain 詳解一下。

  • 未遵循最左匹配原則

  • 索引列參與計算 ： select * from t where id+1=2
  • 對索引列存在包裝 ： select * from t where concat(id,'')='1'
  • 模糊查詢中出現左側模糊的情況 ： select * from t where id like '%test' 或者 select * from t where id like '%test%'
  • 發生型別轉換 ： select * from t where id ='2'
  • sql 中出現 OR 語句 ； not null 語句 ； not in. not exist 等語句
  • 出現兩列比較，不管比較列在不在索引中 select * from t where id!=name
  • 非精確查詢即 不等於查詢 select * from t where id !=1。

本文正在參加「技術專題19期 漫談資料庫技術」活動

「其他文章」
「」
「」