面試官：聊一聊索引吧

ligkwww發表於2021-09-14
面試索引

記錄一下Mysql索引相關材料，隨時更新，有不正確的歡迎評論區指出。

一、索引的三星規則

  • 一星：索引將相關記錄放到一起;
  • 二星：索引中的資料順序和查詢中的排序一致；
  • 三星：索引中的列包含了查詢中所需要的全部列。

    星級越高，代表索引應用效率越高。

二、雜湊索引

雜湊索引的特點：

1.只支援等值查詢
2.查詢速度更快
3.索引列是根據雜湊值進行排序的，而不是索引列的值，所以無法應用排序
4.雜湊索引只儲存雜湊值和行指標，然後根據行指標來獲取對應行資料，不過記憶體中這一過程很快，所以對效能的影響很小
5.可能會存在雜湊衝突的情況，即兩個不同的值生成的雜湊值一樣，出現這種情況後儲存引擎會為匹配到的雜湊值建立連結串列，然後遍歷連結串列中的所有指標，逐行比較行資料，以此來獲取符合條件的行。


三、BTREE索引

索引生效條件：針對聯合索引，查詢時需要滿足最左匹配原則

建立索引： idx_first_sec(‘first’, ‘second’, ‘third’)
1.查詢條件必須包含first欄位才會生效
示例： WHERE first='xxx' AND second = 'xxx';
2.如果從second開始查，則不會應用索引
示例：WHERE second='xxx'
3.如果中間跳了一列，則只會應用到部分索引
示例： WHERE first='xxx' and third='xxx'; #只會應用first索引
4.如果某一列使用了範圍查詢，則右邊所有列就不會應用索引了。
示例：WHERE first='xxx' and second like 'xxx%' and third='xxx'; #最終只會應用first和second索引


索引失效的情況
1.索引應該是一個獨立的列，不能是表示式的一部分或者函式的引數。

select * from table where active_id + 1 = 5;
select * from table where to_days(current_day) - to_days(date_col) <= 10;

2.範圍查詢

select * from table where age != 18;
select * from table where name like '%張三%';


多列索引（聯合索引）

當查詢語句的where條件中，出現了多個索引的相交操作(通常有多個AND條件)，那麼就應該考慮使用多列索引了。

例如：select * from table where col1='xx' and col2='xx';
col1 和 col2 是兩個獨立的單列索引(5.0版本之前，這種查詢會導致全表掃描，解決方式是用 union all 兩個列的單獨查詢來代替 AND)

此時，可以為 col1 和 col2 建立多列索引。
alter table table add index idx_c1_c2(col1,col2);


InnoDB中，為什麼要按主鍵順序插入行？(主鍵自增)

因為按照順序插入，只需要將每一條記錄儲存在上一條記錄的後面即可，無論是執行速度還是儲存空間都比隨機主鍵效能要好，主要在3個方面：

1.寫入的目標頁是隨機的，有可能已經已經刷到磁碟並從快取中清除，或還沒有載入到快取中。在插入之前不得不從磁碟中讀取目標頁到快取中，會帶來大量的隨機IO；
2.因為寫入是亂序的，InnoDB不得不做大量的頁分裂，以便為新的行分配空間，頁分裂會導致大量資料移動。
3.因為頻繁的頁分裂，頁會變得稀疏不規則，所以最終資料會有碎片。


什麼是覆蓋索引？什麼情況下無法使用？

根據索引查詢的葉子節點，不需要進行二次查詢即可滿足本次查詢的過程，稱為覆蓋索引查詢。

正常的一次查詢：根據 where 條件中的索引列，找到對應主鍵ID，然後再根據主鍵ID查詢到對應行資料。(二次查詢/回表)

覆蓋索引查詢：根據 where 條件中的索引列，可以直接查出本次結果，不需要再根據主鍵ID查詢對應行資料。

注意事項：

1.禁止使用select * ，因為沒有索引會覆蓋全欄位，使用 * 將勢必會造成回表查詢。
2.where 條件中禁止使用會導致索引失效的情況(例如：like ‘%xx%’, !=等操作),否則即便是查詢指定列，也會導致全表掃描。


索引排序

當一個查詢及排序語句，完全符合表索引順序時，也是可以利用索引進行排序。

比如：表 table 的索引：idx_date_user(date,user_id);

下面的查詢則可以利用到索引排序：

select user_id,user_name from table where date='2021-06-12' order by user_id;
select user_id,user_name from table order by date,user_id;

所以，不管是where還是order by 語句，或兩者同時使用，對於單表來說只要查詢順序符合索引順序，則可以利用索引。

有一種情況，如果order by 語句中兩個排序欄位，排序方向不一致，就無法使用索引排序。
比如：select user_id from table order by date desc, user_id asc;


四、優化技巧

1. 使用範圍查詢的索引列，最好放在聯合索引的最後一個。

比如：age欄位，往往是根據範圍來進行查詢的(18-25)這樣，所以放在最後一列比較合理，否則會導致其他索引無法使用。

其中的一個解決方案是可以使用 in(18,19,20,21,22,23,24,25) 這樣來代替，但並不是所有條件都適用。而且每增加一個 in 條件，優化器需要做的組合都將以指數級增長，最終也會導致效能問題。


2. 大分頁查詢優化

例如limit 10000,10

示例：select * from table where sex=1 order by age limit 10000,10;

優化方法1
select * from table inner join (select id from table where sex=1 order by age limit 10000,10) as tmp using(id);

說明：此方法利用關聯查詢中的覆蓋索引查詢，返回資料的主鍵ID，然後根據主鍵ID獲取對應行資料。比直接limit查詢後再回表查詢速度要快。

優化方法2
select * from table where id>10000 and sex=1 order by age limit 10;

說明：此方法需要配合業務程式碼實現，在翻頁過程中記錄當前頁面的最大ID，然後在查詢時將ID作為查詢條件傳入，可以準確過濾不需要匹配的行資料，不過弊端就是無法實現頁碼跳轉了。


3. 將一個複雜查詢拆分成多個簡單查詢。

例如一個JOIN查詢可以拆分成多個單表查詢： 

select * from table1 join table2 on table1.id=table2.tid join table3 on table2.id = table3.aid 
where table1.name = 'xxx' and table2.age=32 and table3.nid=56;

#拆分後
select * from table1 where name='xxx';
select * from table2 where age=32;
select * from table3 where nid=56;

這麼做的好處：

  1. 讓快取效率更高。在客戶端應用中可以對不同表做單獨快取，而不必每次都快取所有結果。對於mysql快取來說，關聯查詢中如果其中一個表發生了變化，那麼就會使快取失效，但如果是拆分成多個簡單查詢，那麼即使某個表快取失效，但其他的查詢仍然是有快取的。
  2. 將查詢拆分後，可以減少鎖的競爭。
  3. 在應用層做關聯，可以更方便的對資料進行擴充套件和拆分。
  4. 單表查詢可以更好的應用索引查詢。
  5. 減少冗餘記錄的查詢。在應用層做關聯，對於某條記錄可能只需要訪問一次，但在資料庫做關聯查詢，則可能會重複查詢一部分資料。


4. 為超長欄位模擬雜湊索引列(實際上還是b-tree索引)，以加快查詢速度。

如果表中存在url欄位索引，可以為其設定一個雜湊索引列，目的在於更快的匹配到對應的行，即使有部分衝突，也會比原本只查詢url快很多。

比如：建立欄位：url_crc欄位，使用crc32為url生成雜湊值。表的索引有兩個：url_crc、url

在查詢時就可以這樣：
SELECT id FROM table WHERE url_crc=CRC32('http://www.baidu.com') AND url = 'http://www.baidu.com';

這樣查詢會比以前只查url快很多。

注意：因為雜湊可能會衝突，即同樣的雜湊值對應不同的值，所以在查詢時必須帶上查詢的真實列。


5. 建立字首索引

如果表結構不方便進行修改，那麼可以在超長欄位上建立字首索引，以加快查詢速度。

例如：alter table add key idx_city(city(7)); # 為city欄位的前7個字元建立索引

缺點就是Mysql無法對字首索引做ORDER BY 和GROUP BY。

如何判斷出字首的長度？

第一種方式：人工匹配
1.資料量不大的情況下，可以人工匹配，即首先統計該欄位的次數

select count(*) as cnt, city from table group by city order by cnt desc limit 10;
image

2.依次遞增字首數量，最終達到和完整列的選擇性相接近的程度即可。

select count(*) as cnt, LEFT(city,3) as pref  from table group by pref order by cnt desc limit 10;

image

select count(*) as cnt, LEFT(city,7) as  pref from table group by pref order by cnt desc limit 10;

image

第二種方式：計算完整列的選擇性
1.首先計算完整列值的選擇性

select count(distinct(city))/count(*) from table; # 最終字首索引的選擇性值應與這裡得到的越接近越好
image
2.計算不同字首的選擇性，找出與完整列的選擇性最接近的值。（示例中字首長度為7的最接近）

select count(distinct left(city, 3)) / count(*) as sel3,
       count(distinct left(city, 4)) / count(*) as sel4,
       count(distinct left(city, 5)) / count(*) as sel5,
       count(distinct left(city, 6)) / count(*) as sel6,
       count(distinct left(city, 7)) / count(*) as sel7
       from table;

image

索引的選擇性：指不重複的索引值與資料表的記錄總數的比值。索引的選擇性越高，查詢效率就越高。因為高選擇性的索引，可以在查詢時過濾更多的行。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章