在列上進行運算或使用函數會使索引失效，從而進行全表掃描。如下面例子在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會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 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中，有兩種方式生成有序結果集：

通過有序索引順序掃描直接返回有序數據

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 (0, 3, 4)
建立覆蓋索引

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

如有下面這個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 改寫，單開一文來講

推薦閱讀

MySQL索引為什么要用B+樹實現？

圖解： EXPLAIN 實戰-1

你確定真正理解聯合索引和最左前綴原則？
轉載于https://www.javazhiyin.com/16260.html

轉載于:https://blog.51cto.com/14237164/2365999

總結

以上是生活随笔為你收集整理的MySQL索引优化实战的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。