前置問題

• where條件的順序會影響索引的使用？
• 聯(lián)合索引(idx_a_b ) 條件：“where a > 1 and b < 2”和“where a in (1,3) and b < 2” 有什么區(qū)別？
• 使用uid作為主鍵有什么問題？
• 一個表有兩個索引灶芝，idx_a, idx_b，查詢語句"where a > 1 and b < 2"，會使用到幾個索引
• 查詢條件肯定是越多谓松，查詢效率越快涡匀？
• select * 和select field 差別其實沒有多大盯腌，主要體現(xiàn)在網(wǎng)絡Io上？
• 一行SQL除了where條件陨瘩，order by操作也是可以利用索引的腕够？
• 關于limit offset，SQL效率跟limit大小有關系舌劳，跟offset也有很大關系?
• 如何優(yōu)化uid字段索引帚湘？
• select 1 > null 結(jié)果是什么？
• Django ORM的弊端甚淡？

Innodb索引結(jié)構(gòu)

索引我們都很熟悉大诸，可以通過把要索引的key建立一個平衡二叉樹，進行二分查找贯卦，使時間復雜度來到O(log2n)资柔，定位到key再通過內(nèi)存指針找到自己的data，整個過程在內(nèi)存中很快撵割，但是對于數(shù)據(jù)庫來說贿堰，這樣的數(shù)據(jù)結(jié)構(gòu)卻不行，因為數(shù)據(jù)庫是建立在硬盤上的啡彬。

圖片來源網(wǎng)絡侵刪.png

圖片來源網(wǎng)絡侵刪.png

圖片來自高性能MySQL.png

聚簇索引

聚簇索引就是咱們經(jīng)常說的主鍵索引达舒、pk值朋。如果沒有主鍵呢，Innodb會選擇第一個沒空的唯一索引作為聚簇索引巩搏，如果這個唯一索引也沒有昨登，這個也是Innodb有而MyIsam所沒有的設計。聚簇索引也是索引贯底，也是前面的B+Tree的結(jié)構(gòu)丰辣，但是聚簇索引和非聚簇索引不同的地方在于，非聚簇索引葉子節(jié)點保存的數(shù)據(jù)是聚簇索引id的地址禽捆，而聚簇索引葉子節(jié)點保存的是實際行的值笙什，也就是說，實際行的值是按照聚簇索引排列的方式進行存儲的胚想。而myisam的結(jié)構(gòu)則是數(shù)據(jù)和索引分開的琐凭，結(jié)構(gòu)可以參照下圖。

圖片來自高性能MySQL.png

一條查詢語句是怎么工作的浊服？

一條查詢語句會經(jīng)過分析器進行詞法分析统屈、語法分析胚吁，經(jīng)過優(yōu)化器生成執(zhí)行計劃、索引選擇愁憔，最后會操作引擎腕扶，返回結(jié)果，所以前面的問題惩淳，where條件的順序會影響索引的使用嗎？答案是不會乓搬，因為優(yōu)化器已經(jīng)幫你優(yōu)化了思犁。

image.png

索引覆蓋

上一節(jié)提到了“回表”策泣，如果有回表動作，那么一行sql就要走兩遍索引抬吟，只查詢索引就可以把數(shù)據(jù)取出來的做法就叫做“索引覆蓋”萨咕。這也是為什么大廠都禁止select *的寫法，因為select *一定會回表火本。

前綴索引和索引選擇性

有時候需要索引很長的字符串危队，這會讓索引變得又大又慢，一種策略是使用哈希索引钙畔，把很長的字符串弄成一個hash值茫陆，但是這樣做還不夠，通城嫖觯可以只索引開始的部分字符簿盅，這樣可以大大節(jié)約索引空間，從而提高索引效率揍魂，但是這樣會降低索引的選擇性挪鹏，選擇性越高，代表使用索引后篩選到的值越少愉烙，所以怎么在選擇性和索引長度之間做權衡讨盒。

可以比較:
count(distinct filed) / count(field) 與count(distinct left(field, n)) / count(field) 的比率
以uuid為例子，uuid有32位步责，在單表700w的場景下：
count(distinct uid) / count(uid)  = 1 VS count(distinct left(uid, 10)） / count(uid)  = 1
這時候就可以alter table xx add index idx_uid (uid(10)) 

前綴索引使索引更小返顺、更快禀苦，但是另一方面前綴索引也有其缺點：MySQL無法使用前綴索引做order by和group by，也無法使用前綴索引做索引覆蓋遂鹊。

判斷一個索引是否適合某一條查詢振乏？

• 索引將相關記錄放在一起
• 索引中數(shù)據(jù)順序和查詢中的排列順序一致
• 索引中的列包含需要查詢的全部列

索引可以既滿足查找又滿足排序

前提是：索引的順序與order by的順序一致，并且所有列的排序方向一致（不能是一個升序一個倒序）秉扑，這樣就可以使用索引進行排序了慧邮，而不是按索引順序去數(shù)據(jù)庫里把數(shù)據(jù)拉到服務器里（隨機io了），再在一個臨時文件里進行排序舟陆，這時候explain大多會出現(xiàn)filesort误澳，這時候的效率是非常慢的。例子如下：

idx_a_b_c select a,b,c from table order by a,b,c  全部利用到索引 Using index
idx_a_b_c select * from table order by a,b,c 無法利用到索引 Using filesort
idx_a_b_c select a,b,c,d from table order by a,b,c 無法利用到索引 Using filesort
idx_a_b_c select a,b,c from table order by a,b desc,c  利用到一部分索引 Using index; Using filesort
idx_a_b_c select a, b, c from table where a=100 order by b,c  可以利用到索引排序 Using where; Using index
idx_a_b_c select a, b, c from table where a>100 order by b,c  利用到一部分索引 Using where; Using index; Using filesort

這里要說下 a in (1,2,3) 和 a >= 1 and a <= 3的區(qū)別秦躯；在聯(lián)合索引的場景下如idx_a_b忆谓，如果是a in (1,2,3)，不影響后面的b使用索引踱承，如果是a >= 1 and a <= 3那么后面的b無法使用到索引倡缠。

索引合并

當在一張表建立多個字段的索引時候，一行SQL一般是只能使用到一個索引茎活，直到MySQL5.0以后昙沦，有了索引合并這么的機制，一定程度上可以利用多條索引载荔。

• Using union 索引取并集
• Using sort_union 索引排序取并集
• Using intersect 索引取交集
  索引合并策略是一種優(yōu)化結(jié)果桅滋，但實際上更多時候說明了表上的索引建的很糟糕，才會不得不使用索引合并進行優(yōu)化身辨。
  當在explain的執(zhí)行計劃里面出現(xiàn)上述索引合并時候丐谋，我們要知道以下問題：
• 可以通過建立聯(lián)合索引的方式解決Using intersect
• 服務器對多個索引索引做聯(lián)合操作時候，通常會耗費大量的CPU和內(nèi)存資源在緩存煌珊、排序合并操作上号俐，尤其是在索引的利用率不高，返回大量數(shù)據(jù)的情況下
• 優(yōu)化器不會把這些服務器的排序合并計算到“查詢成本”中定庵，優(yōu)化器只關心隨即頁面讀取吏饿。所以這些成本是被“低估”的成本
• 一行SQL上利用多個索引做索引合并update時候，會提高死鎖的概率蔬浙。

關于NULL

• NULL在MySQL中的意思是“未知”而不是False或者""
• SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);; 結(jié)果都是NULL
• 當使用DISTINCT, GROUP BY, or ORDER BY, 所有的 NULL字段會被當成是相等的
• 針對UNIQUE index猪落，兩個NULL值會被看到是相等的
• 帶有NULL值的唯一索引，無法被當做
• order by的時候NULL會放在第一個(asc)或者最后一個(desc)
• 當使用Aggregate (summary) 如COUNT() MIN() SUM()會忽略NULL
• NULL會有些特殊的處理畴博，如timestamp笨忌、AUTO-INCREMENT字段
• 針對NULL字段建立索引，需要使用額外的空間

未來期望MySQL可以做到的

• where條件可以傳到存儲引擎層index condition pushdown
• 松散索引掃描