歡迎光臨
每天分享高質量文章

MySQL 索引優化實戰

來自:Java識堂(微信號:erlieStar)

索引的種類

眾所周知,索引類似於字典的目錄,可以提高查詢的效率。
索引從物理上可以分為:聚集索引,非聚集索引
從邏輯上可以分為:普通索引,唯一索引,主鍵索引,聯合索引,全文索引

索引優化策略

不要在索引列上進行運算或使用函式

在列上進行運算或使用函式會使索引失效,從而進行全表掃描。如下麵例子在publish_time,id列上分別加上索引,publish_time為datetime型別,id為int型別

-- 全表掃描
select * from article where year(publish_time) 2019
-- 走索引
select * from article where publish_time '2019-01-01'
-- 全表掃描
select * from article where id  + 1 = 5
-- 走索引
select * from article where id = 4

小心隱式型別轉換

假設id為varchar型別

-- 全表掃描
select * from article where id = 100
-- 走索引
select * from article where id = '100'

為什麼呢?

select * from article where id = 100
-- 等價於
select * from article where CAST(id AS signed int) = 100

上一條規則說過,不要在索引列上使用函式,隱式型別轉換在索引欄位上做了函式操作,因此會全表掃描

那麼如果id是int,執行下麵這個陳述句是否會導致全表掃描呢?

select * from article where id = '100'

答案是會用到索引

前導模糊查詢不會使用索引

-- 全表掃描
select * from article where author like '%李'

%李,%李%都會導致全表掃描,非前導模糊查詢可以使用索引

-- 走索引
select * from article where author like '李%'

聯合索引最左前綴原則

mysql會一直向右匹配直到遇到範圍查詢(>、 3 and d = 4 如果建立(a,b,c,d)順序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引則都可以用到,a,b,d的順序可以任意調整

1.將區分度最高的欄位放在最左邊

當不需要考慮排序和分組時,將區分度最高的列放在前面通常是很好的。這時候索引的作用只是用於優化WHERE條件的查找

如果在a b列上建立聯合索引,該如何建立,才能使查詢效率最高

select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table

執行如下陳述句,假設3個輸出依次為0.001,0.373,16049,可以看到b列的選擇性最高,因此將其作為聯合索引的第一列,即建立(b, a)的聯合索引

2.查詢時=可以亂序
如果建立了聯合索引(a, b)。例如下麵的2個寫法是等價的,因為MySQL會將查詢的順序優化成和聯合索引的順序一致

select * from table where a = '1' and b = '1'
select * from table where b = '1' and a = '1'

3.優化查詢,避免出現filesort

select * from table where a = ? and b = ? order by c

最左前綴原則不僅用在查詢中,還能用在排序中。MySQL中,有兩種方式生成有序結果集:

  1. 通過有序索引順序掃描直接傳回有序資料
  2. Filesort排序,對傳回的資料進行排序

因為索引的結構是B+樹,索引中的資料是按照一定順序進行排列的,所以在排序查詢中如果能利用索引,就能避免額外的排序操作。EXPLAIN分析查詢時,Extra顯示為Using index。

所有不是通過索引直接傳回排序結果的操作都是Filesort排序,也就是說進行了額外的排序操作。EXPLAIN分析查詢時,Extra顯示為Using filesort,當出現Using filesort時對性能損耗較大,所以要儘量避免Using filesort

對於如下sql

select * from table where a = ? and b = ? order by c

可以建立聯合索引(a, b, c)

如果索引中有範圍查找,那麼索引有序性無法利用,如

select * from table where a > 10 order by b

索引(a,b)無法排序。

放幾個例子

-- 使用了a列
where a = 3

-- 使用了a b列
where a = 3 and b = 5

-- 使用了a b c列
where a = 3 and c = 4 and b = 5

-- 沒有使用索引
where b = 3

-- 使用了a列 
where a = 3 and c = 4

-- 使用了a b列 
where a = 3 and b > 10 and c = 7

-- 使用了a b 列
where a = 3 and b like 'xx%' and c = 7

union,or,in都能命中索引,建議使用in

select * from article where id = 1
union all
select * from article where id = 2
select * from article where id in (1 , 2)

新版MySQL的or可以命中索引

select * from article where id = 1 or id = 2

效率從高到低為union,in,or。in和union的效率差別可以忽略不計,建議使用in

負向條件索引不會使用索引,建議用in

負向條件有:!=、<>、not in、not exists、not like 等

-- 全表掃描
select * from article where id != 1 and id != 2

知道id的所有取值範圍,可以改為類似如下形式

-- 走索引
select * from article where id in (034)

建立改寫索引

眾所周知,表資料是放在一個聚集索引上的,而建立的索引為非聚集索引,非聚集索引的葉子節點存放索引鍵值,以及該索引鍵指向的主鍵。一般查找的過程是從非聚集索引上找到資料的主鍵,然後根據該主鍵到聚集索引上查找記錄,這個過程稱為回表,不清楚的看推薦閱讀。

如有下麵這個sql

select uid, login_time from user where username = ? and passwd = ?

可以建立(username, passwd, login_time)的聯合索引,由於 login_time的值可以直接從索引中拿到,不用再回表查詢,提高了查詢效率

經常更改,區分度不高的列上不宜加索引

更新會變更 B+ 樹,更新頻繁的欄位建立索引會大大降低資料庫性能。

“性別”這種區分度不大的屬性,建立索引是沒有什麼意義的,不能有效過濾資料,性能與全表掃描類似。

一般區分度在80%以上的時候就可以建立索引,區分度可以使用 count(distinct(列名))/count(*) 來計算

明確知道只會傳回一條記錄,可以加limit1

當查詢確定只有一條記錄時,可以加liimit1,讓MySQL停止游標移動,提高查詢效率

select uid from user where username = ? and passwd = ?

可改為

select uid from user where username = ? and passwd = ? limit 1

對文本建立前綴索引

用郵箱登錄是一個常見的問題,如果對email整個欄位建立索引,會讓索引變得大且慢

select username from user where email='xxx';

這時我們可以索引開始的部分字符,這樣可以大大節約索引空間,從而提高索引效率,但這樣也會降低索引的區分度。索引的區分度是指,不重覆的索引值和資料表的記錄總數的比值。索引的區分度越高則查詢效率越高,因為區分度高的索引可以讓MySQL在查找時過濾掉更多的行。

因此我們選擇足夠長的前綴保證較高的區分度,同時又不能太長(以便節約空間)

可以進行如下實驗

select count(distinct left(email, 5)) / count(*) as col5,
count(distinct left(email, 6)) / count(*) as col6,
count(distinct left(email, 7)) / count(*) as col7
from user

假設輸出依次為0.0305,0.0309,0.0310
查詢顯示當前綴長度達到7的時候,再增加前綴長度,區分度提升的幅度已經很小了,因此創建email(7)的前綴索引即可

需要註意的一點是,前綴索引不能使用改寫索引,因為從索引中獲取不到完整的資料,還得回表查詢

建立索引的列不為NULL

只要列中包含有 NULL 值都將不會被包含在索引中,複合索引中只要有一列含有 NULL值,那麼這一列對於此複合索引就是無效的。

因此,在資料庫設計時,除非有一個很特別的原因使用 NULL 值,不然儘量不要讓欄位的預設值為 NULL。

分頁查詢優化

MySQL 並不是跳過 offset 行,而是取 offset+N 行,然後傳回放棄前 offset 行,傳回 N 行,那當 offset 特別大的時候,效率就非常的低下,要麼控制傳回的總頁數,要麼對超過特定閾值的頁數進行 SQL 改寫,單開一文來講

赞(0)

分享創造快樂