面試官:聊一聊索引吧

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 協議》,轉載必須註明作者和本文連結

相關文章