最近有個需求，要修改現有儲存結構，涉及查詢條件和查詢效率的考量，看了幾篇索引和HBase相關的文章，回憶了相關知識，結合專案需求，說說自己的理解和總結。
錯過的朋友可以先回顧下前3篇文章：
上一篇詳細介紹了explain命令，通過該命令，可以定位出在哪一步出現了效能問題，下一步就是通過優化索引來解決它。
部分內容摘錄了幾個博友的文章，最後會給出文章連結，感謝他們的精彩分析。
常見優化方法
聯合索引最左字首原則
複合索引遵守「最左字首」原則，查詢條件中，使用了複合索引前面的欄位，索引才會被使用，如果不是按照索引的最左列開始查詢，則無法使用索引。
比如在(a,b,c)三個欄位上建立聯合索引，那麼它能夠加快a|(a,b)|(a,b,c)三組查詢的速度，而不能加快b|(b,a)這種查詢順序。
另外，建聯合索引的時候，區分度最高的欄位在最左邊。
不要在列上使用函式和進行運算
不要在列上使用函式，這將導致索引失效而進行全表掃描。
例如下面的 SQL 語句：
select * from artile where YEAR(create_time) <= '2018';
複製程式碼即使 date 上建立了索引，也會全表掃描，可以把計算放到業務層，這樣做不僅可以節省資料庫的 CPU，還可以起到查詢快取優化效果。
負向條件查詢不能使用索引
負向條件有：!=、<>、not in、not exists、not like 等。
select * from artile where status != 1 and status != 2;
複製程式碼可以使用in進行優化：
select * from artile where status in (0,3)
複製程式碼使用覆蓋索引
所謂覆蓋索引，是指被查詢的列，資料能從索引中取得，而不用通過行定位符再到資料表上獲取，能夠極大的提高效能。
可以定義一個讓索引包含的額外的列，即使這個列對於索引而言是無用的。
避免強制型別轉換
當查詢條件左右兩側型別不匹配的時候會發生強制轉換，強制轉換可能導致索引失效而進行全表掃描。
如果phone欄位是varchar型別，則下面的SQL不能命中索引：
select * from user where phone=12345678901;
複製程式碼可以優化為：
select * from user where phone='12345678901';
複製程式碼範圍列可以用到索引
範圍條件有：<、<=、>、>=、between等。
範圍列可以用到索引，但是範圍列後面的列無法用到索引，索引最多用於一個範圍列，如果查詢條件中有兩個範圍列則無法全用到索引。
更新頻繁、資料區分度不高的欄位上不宜建立索引
更新會變更B+樹，更新頻繁的欄位建立索引會大大降低資料庫效能。
「性別」這種區分度不大的屬性，建立索引沒有意義，不能有效過濾資料，效能與全表掃描類似。
區分度可以使用 count(distinct(列名))/count(*) 來計算，在80%以上的時候就可以建立索引。
索引列不允許為null
單列索引不存null值，複合索引不存全為null的值，如果列允許為 null，可能會得到不符合預期的結果集。
避免使用or來連線條件
應該儘量避免在 where 子句中使用 or 來連線條件，因為這會導致索引失效而進行全表掃描，雖然新版的MySQL能夠命中索引，但查詢優化耗費的 CPU比in多。
模糊查詢
前導模糊查詢不能使用索引，非前導查詢可以。
優化案例
利用延遲關聯或者子查詢優化超多分頁場景
MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後返回放棄前 offset 行，返回 N 行。
當 offset 特別大的時候，效率非常低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行SQL改寫。
可以先快速定位需要獲取的id段，然後再關聯:
selecta.* from 表1 a,(select id from 表1 where 條件 limit 1000000 ,10 ) b where a.id=b.id
複製程式碼如果明確知道只有一條結果返回，limit 1 能夠提高效率
雖然自己知道只有一條結果，但資料庫並不知道，明確告訴它，讓它主動停止遊標移動。
如何建立索引
where a=1 and b=1
where b=1
where b=1 order by time desc
複製程式碼建議建立兩個索引，即 idx_ab(a,b) 和 idx_b_time(b,time)
MySQL 的查詢優化器會自動調整where子句的條件順序以使用適合的索引，對於上面的第一條 SQL，如果建立索引為idx_ba(b,a) 也是可以用到索引的。
多值匹配和範圍匹配
假如有聯合索引(empno、title、fromdate)，下面的 SQL 是否可以用到索引，如果可以的話，會使用幾個？
select * from employee.titles
where emp_no between '10001' and'10010'
and title='軟體工程師'
and from_date between '2008-01-01'and '2018-01-01'
複製程式碼可以使用索引，可以用到索引全部三個列，這個 SQL看起來是用了兩個範圍查詢，但作用於empno上的between實際上相當於in，也就是說empno 實際是多值精確匹配。
在 MySQL 中要謹慎地區分多值匹配和範圍匹配，否則會對 MySQL 的行為產生困惑。
聯合索引的最左匹配原則
假如建立聯合索引(a,b,c)，下列語句是否可以使用索引，如果可以，使用了那幾列？
where a= 3 // 是，使用了a列
where a= 3 and b = 5 // 是，使用了ab列
where a = 3 and c = 4 and b = 5 // 是，使用了 a，b，c 列
where b= 3 // 否
where a= 3 and c = 4 // 是，使用了a列
where a = 3 and b > 10 and c = 7 // 是，使用了 a，b 列
where a = 3 and b like 'xx%' andc = 7 // 是，使用了 a，b 列
複製程式碼根據區分度建立索引
有如下查詢語句，查詢指定產品已稽核(status=1)的評論：
SELECT user_id,title,content FROM `comment`
WHERE status=1 AND product_id=1
LIMIT 0,5 ;
複製程式碼可以建立聯合索引，status和product_id，但是哪個放左邊就要計算區分度：
SELECT COUNT(DISTINCT status)/COUNT(*) AS audit_rate,
COUNT(DISTINCT product_id)/COUNT(*) AS product_rate
FROM comment;
複製程式碼一般product的區分度會高點，可以建立如下索引：
CREATE INDEX idx_productID_Status ON comment(product_id,status)
複製程式碼排序欄位索引
檢視某個使用者最近20條登入記錄，按時間排序：
select * from login_history where uid = $uid order by create_time desc limit 20;
複製程式碼建立uid+timeline複合索引，將排序引入到索引結構中,資料庫負載驟降。
參考文章：
歡迎掃描下方二維碼，關注我的個人微信公眾號，檢視更多文章 ~
