Lou18
Lou18

18

使用索引的注意事項

  • 複合索引的欄位順序
-- index column: {a, b, c}
*-- 相當於建立 {a}, {a, b}, {a, b, c} 三個索引,但因為是*複合*索引所以不存有三顆樹*
-- 最左前綴匹配原則(用到最左邊的欄位才算複合索引,且優先對最左邊的字段進行排序)

-- good
SELECT * FROM table WHERE a = ?; 
SELECT * FROM table WHERE a = ? AND b = ?; 
SELECT * FROM table WHERE b = ? and a = ?; 

-- bad (full scan)
SELECT * FROM table WHERE b = ?;
SELECT * FROM table WHERE c = ?;
SELECT * FROM table WHERE b = ? AND c = ?; 

  • 盡可能使用索引做排序(索引本身就有排序)
-- index column: {age}
-- good usage
SELECT * FROM user WHERE age <= 30 ORDER BY age; 

-- bad:filesort (排序的时候不能用上索引),需要另外使用記憶體
SELECT * FROM user WHERE age <= 30 ORDER BY name; 

-- 複合索引
-- index column: {age, name}
-- sort by age as default
  • 索引太多,優化器混亂

對多個欄位進行多種組合索引,會使用不少空間建立 b+ 樹,也會讓 mysql 優化器選錯索引


CC BY-NC-ND 2.0

Like my work?
Don't forget to support or like, so I know you are with me..

Loading...
Loading...

Comment