目錄

• 一.InnoDB索引
• 二.B+樹
• 三.聚集索引和輔助索引
• 四.索引實戰
• 五.索引操作與規則
  • 重建索引
  • 索引覆寫
  • 最左前綴原則
  • 索引下推
  • 用索引和用索引快速定位卻別
• 六.普通索引和唯一索引如何選擇
  • 前提
  • 普通索引和唯一索引下的查詢
  • 普通索引和唯一索引下的更新
    • change buffer
    • 使用change buffer
    • 更新流程
    • change buffer使用場景
  • change buffer和 redo log
    • 插入程序
    • 查詢程序
• 七.給字串加索引
  • 給字串加索引的方式
  • 完整索引和前綴索引的分析
  • 前綴索引對覆寫索引的影響
  • 倒序存盤和hash欄位分析
• 八.mysql選錯索引原因及處理方法
  • 現象
  • 優化器選擇索引邏輯
  • 索引選擇例外及處理
• 九.mysql對索引欄位進行函式操作導致不走索引搜索樹功能
  • 條件欄位做函式操作
  • 隱式型別轉換
  • 隱式字符編碼轉換

一.InnoDB索引

InnoDB支持以下幾種索引：

• B+樹索引
• 全文索引
• 哈希索引

本文將著重介紹B+樹索引，其他兩個全文索引和哈希索引只是做簡單介紹一筆帶過，

哈希索引是自適應的，也就是說這個不能人為干預在一張表生成哈希索引，InnoDB會根據這張表的使用情況來自動生成，

全文索引是將存在資料庫的整本書的任意內容資訊查找出來的技術，InnoDB從1.2.x版本支持，每張表只能有一個全文檢索的索引，

B+樹索引是傳統意義上的索引，B+樹索引并不能根據鍵值找到具體的行資料，B+樹索引只能找到行資料所在的頁，然后通過把頁讀到記憶體，再在記憶體中查找到行資料，B+樹索引也是最常用的最為頻繁使用的索引，

二.什么是B+樹

前提

• 葉子節點: 沒有子節點的節點

• 非葉子節點: 有子節點的節點

概念

　　B+樹是一種平衡查找樹，其實先想想看為什么要用平衡查找樹，不用二叉樹？普通的二叉樹可能因為插入的資料最后變成一個很長的鏈表，怎么能提高搜索的速度呢？你可以想想，為什么HashMap和ConcurrentHashMap在JDK8的時候，當鏈表大于8的時候把鏈表轉成紅黑樹（紅黑樹也是平衡查找樹），技術思維是想通的，那么答案無非是加快速度，性能咯，

一個B+樹有以下特征：

• 有n個子樹的中間節點包含n個元素，每個元素不保存資料，只用來索引，所有資料都保存在葉子節點，
• 所有葉子節點包含元素的資訊以及指向記錄的指標，且葉子節點按關鍵字自小到大順序鏈接，
• 所有的中間節點元素都同時存在于子節點，在子節點元素中是最大（或最小）元素，

　　那么我們先來看一個B+樹的圖

所有的資料都在葉子節點，且每一個葉子節點都帶有指向下一個節點的指標，形成了一個有序的鏈表，為什么要有序呢？其實是為了范圍查詢，比如說select * from Table where id > 1 and id < 100; 當找到1后，只需順著節點和指標順序遍歷就可以一次性訪問到所有資料節點，極大提到了區間查詢效率，是不是范圍查詢的話hash就搞不定這個事情了？以下為B+樹的優勢：

• 單一節點存盤更多元素，減少IO
• 所有查詢都要找到葉子節點，查詢穩定
• 所有葉子節點形成有序鏈表，方便范圍查詢

一般性情況，資料庫的B+樹的高度一般在2~4層，這就是說找到某一鍵值的行記錄最多需要2到4次邏輯IO，相當于0.02到0.04s，

三.聚集索引和輔助索引

聚集索引(聚簇索引)

　　聚集索引是按表的主鍵構造的B+樹，葉子節點存放的為整張表的行記錄資料，每張表只能有一個聚集索引，優化器更傾向采用聚集索引，因為直接就能獲取行資料，

　　請選擇自增id來做主鍵，不要非空UK列，避免大量分頁碎片，下面來看一個聚集索引的圖：

? 那么很簡單了，每個葉子節點，都存有完整的行記錄，對于主鍵的查找速度那是相當的快，美滋滋，

輔助索引

　　輔助索引也叫非聚集索引，葉子節點除了鍵值以外還包含了一個bookmark，用來告訴InnoDB在哪里可以找到對應的行資料，InnoDB的輔助索引的bookmark就是相對應行資料的聚集索引鍵，也就是先獲取指向主鍵索引的主鍵，然后通過主鍵索引來找到一個完整的行，如果輔助索引的樹和聚集索引的樹的高度都是3，如果不是走主鍵索引走輔助索引的話，那么需要6次邏輯IO訪問得到最終的資料頁，輔助索引和聚集索引的概念關系圖如下：

基于主鍵索引和普通索引的查詢有什么區別？

• 如果陳述句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹;
• 如果陳述句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引 樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次，這個程序稱為回表，

四.索引實戰

設計索引

　　設計索引的時候，無論是組合索引還是普通索引等，一般經驗是，選擇經常被用來過濾記錄的欄位，高選擇性，高區分性，別把性別欄位設計索引，性別屬于低選擇性的，你可以選擇名字嘛，你好我大名叫苗嘉杏：）

　　知道加索引快，但是也別亂加索引，插入以及更新索引的操作InnoDB都會維護B+樹的，多加很多索引只會導致效率降低！

　　不要用重復的索引，比如有個聯合索引是a，b，你又整個a列的普通索引，那不是搞事么？

　　不要在索引上用函式和like

一顆聚集索引B+樹可以放多少行資料？

　　這里我們先假設B+樹高為2，即存在一個根節點和若干個葉子節點，那么這棵B+樹的存放總記錄數為：根節點指標數*單個葉子節點記錄行數，假設一行記錄的資料大小為1k，那么單個葉子節點（頁）中的記錄數=16K/1K=16，

　　那么現在我們需要計算出非葉子節點能存放多少指標，我們假設主鍵ID為bigint型別，長度為8位元組，而指標大小在InnoDB原始碼中設定為6位元組，這樣一共14位元組，我們一個頁中能存放多少這樣的單元，其實就代表有多少指標，頁大小默認16K，即16kb/14b=1170，那么可以算出一棵高度為2的B+樹，大概能存放1170*16=18720條這樣的資料記錄，

　　根據同樣的原理我們可以算出一個高度為3的B+樹大概可以存放：1170*1170*16=21902400行資料，所以在InnoDB中B+樹高度一般為1-3層，它就能滿足千萬級的資料存盤，在查找資料時一次頁的查找代表一次IO，所以通過主鍵索引查詢通常只需要1-3次邏輯IO操作即可查找到資料，

Cardinality值

　　如何判斷一個索引建立的是否好呢？可以用show index from指令查看Cardinality值，這個值是一個預估值，而不是一個準確值，每次對Cardinality值的統計都是隨機取8個葉子節點得到的，

　　對于innodb來說，達到以下2點就會重新計算cardinality

• 如果表中1/16的資料發生變化
• 如果stat_modified_counter>200 000 0000

　　實際應用中，（Cardinality/行數）應該盡量接近1，如果非常小則要考慮是否需要此索引，實戰一下，比如有一張表，我們來show index一下

mysql> show index from Order;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Order   |          0 | PRIMARY          |            1 | id          | A         |       99552 |     NULL | NULL   |      | BTREE      |         |               |
| Order   |          1 | IDX_orderId      |            1 | orderId     | A         |       96697 |     NULL | NULL   |      | BTREE      |         |               |
| Order   |          1 | IDX_productId    |            1 | productId   | A         |          52 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
rows in set (0.00 sec)

那么可以看到IDX_productId這個索引的Cardinality比較低，　

需要強制重繪Cardinality值的話可以用：

analyze local table xxx;

五.索引操作與規則

重建索引

重建普通索引

alter table T drop index k;

alter table T add index(k);

重建主鍵索引

可行:

	alter table T engine=InnoDB 

不可行:

	alter table T drop primary key;
	alter table T add primary key(id);

覆寫索引

ID為主鍵索引,k為普通索引.

如果執行的陳述句是 select ID from T where k between 3 and 5，這時只需要查 ID 的 值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表，也就是 說，在這個查詢里面，索引 k 已經“覆寫了”我們的查詢需求，我們稱為覆寫索引，

由于覆寫索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆寫索引是一個常用的性能優化手段，

最左前綴原則

B+ 樹這種索引結構，可以利用索引的“最左前綴”，來定位 記錄，

(name,age,sex)利用最左前綴可以實作以下的索引(name)(name,age)(name,age,sex))

在建立聯合索引的時候，如何安 排索引內的欄位順序，

第一原則是，如果通過調整順序，可以少維護一個索引，那么這個順序往往就是需要優先考慮采用的，

第二,考慮的原則就是空間

	比如:name 欄位是比 age 欄位大的 ，那我就建議你創建一個(name,age) 的聯合索引和一個 (age) 的單欄位 索引，這樣比(age,name)(name)占用空間少

索引下推

聯合索引(name, age)為例

mysql> select * from tuser where name like '張 %' and age=10 and ismale=1;

已經知道了最左前綴索引規則，所以這個陳述句在搜索索引樹的時候，只能用索引name來搜索 “張”,age是沒法用的,因為'zhang%'查詢的是一個范圍.

MySQL 5.6 引入的索引下推優化(index condition pushdown)， 可以在索引遍歷過 程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數，

用索引和用索引快速定位卻別

前提

• 表格260萬資料
• id自增主鍵
• Account普通索引
• (Account,Cmd)聯合索引
• 其實按照最左前綴原則Account普通索引與(Account,Cmd)聯合索引,只保留(Account,Cmd)聯合索引即可,因為通過(Account,Cmd)聯合索引也可以對Account進行索引. 此處都保留是想測驗,如果兩者都存在的某些情況下,優化器如何選擇

EXPLAIN SELECT id from mt4order WHERE Account like '1';

EXPLAIN SELECT id from mt4order WHERE Account like '1%';

EXPLAIN SELECT id from mt4order WHERE Account like '%1%';

EXPLAIN SELECT id from mt4order WHERE Account like '%1';

總結:

1. 第一個使用account索引快速定位到一行資料,extra為Using where; Using index,說明優先使用索引中覆寫索引獲取了id資訊,避免了回表(使用索引,并且使用索引快速查找)
2. 第二個使用account索引快速定位,但是因為后面包含一個%,所以按照最左前綴原則,對'1%'中的'1'進行索引快速查找,查詢了1716行資料,extra為Using where; Using index,說明優先使用索引中覆寫索引獲取了id資訊,避免了回表 (使用索引,并且使用索引快速查找)
3. 第三個有使用account索引,因為是'%1%'是范圍查找,所以在account索引樹上進行了全面的查找,掃描了2649814行資料,extra中為Using where; Using index,此時只是使用了索引和覆寫索引避免了回表,但是沒有使用索引快速定位查找,因為基于account索引掃描了全部的行(使用索引,沒有使用索引快速查找)
4. 第四個有使用account索引,因為是'%1'是范圍查找,所以在account索引樹上進行了全面的查找,掃描了2649814行資料,extra中為Using where; Using index,此時只是使用了索引和覆寫索引避免了回表,但是沒有使用索引快速定位查找,因為基于account索引掃描了全部的行(使用索引,沒有使用索引快速查找)

六.普通索引和唯一索引如何選擇

前提條件

普通索引和唯一索引下的查詢

• 執行查詢的陳述句是 select id from T where k=5，

• 對于普通索引來說，查找到滿足條件的第一個記錄 (5,500) 后，需要查找下一個記錄，直到碰到第一個不滿足 k=5 條件的記錄，

• 對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續檢索，

• 這個不同帶來的性能差距會有多少呢?答案是，微乎其微，

• InnoDB 的資料是按資料頁為單位來讀寫的，也就是說，當需要讀一條記錄的 時候，并不是將這個記錄本身從磁盤讀出來，而是以頁為單位，將其整體讀入記憶體，在 InnoDB 中，每個資料頁的大小默認是 16KB，

• 所以說，當找到 k=5 的記錄的時候，它所在的資料頁就都在記憶體 里了，那么，對于普通索引來說，要多做的那一次“查找和判斷下一條記錄”的操作，就 只需要一次指標尋找和一次計算，

• 如果 k=5 這個記錄剛好是這個資料頁的最后一個記錄，那么要取下一個記錄，必須 讀取下一個資料頁，這個操作會稍微復雜一些，但是，我們之前計算過，對于整型欄位，一個資料頁可以放近千個 key，因此出現這種情 況的概率會很低，

• 所以，我們計算平均性能差異時，仍可以認為這個操作成本對于現在的 CPU 來說可以忽略不計，

普通索引和唯一索引下的更新

change buffer

• 當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，
• 而如果這個資料頁還沒有在 記憶體中的話，在不影響資料一致性的前提下，InooDB 會將這些更新操作快取在 change buffer 中，這樣就不需要從磁盤中讀入這個資料頁了，
• 在下次查詢需要訪問這個資料頁的 時候，將資料頁讀入記憶體，然后執行 change buffer 中與這個頁有關的操作，將 change buffer 中的操作應用到原資料頁，得到最新結果的程序稱為 merge，除了訪 問這個資料頁會觸發 merge 外，系統有后臺執行緒會定期 merge，在資料庫正常關閉 (shutdown)的程序中，也會執行 merge 操作，
• 雖然名字叫作 change buffer，實際上它是可以持久化的資料，也就是 說，change buffer 在記憶體中有拷貝，也會被寫入到磁盤上，
• 顯然，如果能夠將更新操作先記錄在 change buffer，減少讀磁盤，陳述句的執行速度會得 到明顯的提升，而且，資料讀入記憶體是需要占用 buffer pool 的，所以這種方式還能夠避 免占用記憶體，提高記憶體利用率，

使用change buffer

• 只有普通索引才能使用change buffer,唯一索引不能使用

• change buffer 用的是 buffer pool 里的記憶體，因此不能無限增大，

• change buffer 的大 小，可以通過引數 innodb_change_buffer_max_size 來動態設定，這個引數設定為 50 的時候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%，

更新流程

• 第一種情況是，這個記錄要更新的目標頁在記憶體中，
  • 對于唯一索引來說，找到 3 和 5 之間的位置，判斷到沒有沖突，插入這個值，陳述句執行結束;
  • 對于普通索引來說，找到 3 和 5 之間的位置，插入這個值，陳述句執行結束，
  • 普通索引和唯一索引對更新陳述句性能影響的差別，只是一個判斷，只會耗費微 小的 CPU 時間，
• 第二種情況是，這個記錄要更新的目標頁不在記憶體中，
  • 對于唯一索引來說，需要將資料頁讀入記憶體，判斷到沒有沖突，插入這個值，陳述句執行結束;
  • 對于普通索引來說，則是將更新記錄在 change buffer，陳述句執行就結束了，
  • 將資料從磁盤讀入記憶體涉及隨機 IO 的訪問，是資料庫里面成本最高的操作之一，change buffer 因為減少了隨機磁盤訪問，所以對更新性能的提升是會很明顯的，

change buffer使用場景

• 對于寫多讀少的業務來說，頁面在寫完以后馬上被訪問到的概率比較小，此時 change buffer 的使用效果最好，這種業務模型常見的就是賬單類、日志類的系統，
• 反過來，假設一個業務的更新模式是寫入之后馬上會做查詢，那么即使滿足了條件，將更 新先記錄在 change buffer，但之后由于馬上要訪問這個資料頁，會立即觸發 merge 過 程，這樣隨機訪問 IO 的次數不會減少，反而增加了 change buffer 的維護代價，所以， 對于這種業務模式來說，change buffer 反而起到了副作用，
• 如果所有的更新后面，都馬上伴隨著對這個記錄的查詢，那么你應該關閉 change buffer，
• 在使用機械硬碟時，盡量使用普通索引，然后把 change buffer 盡量開大，以確保這個“歷 史資料”表的資料寫入速度，

change buffer和 redo log

插入程序

• 假設: 當前 k 索引樹的狀態，查找到位置后，k1 所在的資料頁在記憶體 (InnoDB buffer pool) 中，k2 所在的資料頁不在記憶體中

• 在上面表的前提下執行下面陳述句: mysql> insert into t(id,k) values(id1,k1),(id2,k2);

• 分析這條更新陳述句，你會發現它涉及了四個部分:記憶體、redo log(ib_log_fileX)、 資料表空間(t.ibd)、系統表空間(ibdata1)，

• 這條更新陳述句做了如下的操作(按照圖中的數字順序):

  • Page 1 在記憶體中，直接更新記憶體;
  2. Page 2 沒有在記憶體中，就在記憶體的 change buffer 區域，記錄下“我要往 Page 2 插
     入一行”這個資訊
  3. 將上述兩個動作記入 redo log 中(圖中 3 和 4)，

• 做完上面這些，事務就可以完成了，

• 所以，你會看到，執行這條更新陳述句的成本很低，就是寫了兩處記憶體，然后寫了一處磁盤(兩次操作合在一起寫了一次磁盤,redo log)，而且還是順序寫的，

• 同時，圖中的兩個虛線箭頭，是后臺操作，不影響更新的回應時間，

查詢程序

• 如果讀陳述句發生在更新陳述句后不久，記憶體中的資料都還在，那么此時的這兩個讀操作就與 系統表空間(ibdata1)和 redo log(ib_log_fileX)無關了，所以，我在圖中就沒畫出 這兩部分，

• 要執行 select * from t where k in (k1, k2)

• 讀 Page 1 的時候，直接從記憶體回傳，有幾位同學在前面文章的評論中問到，WAL 之后 如果讀資料，是不是一定要讀盤，是不是一定要從 redo log 里面把資料更新以后才可以回傳?其實是不用的，你可以看一下圖 3 的這個狀態，雖然磁盤上還是之前的資料， 但是這里直接從記憶體回傳結果，結果是正確的，

• 要讀 Page 2 的時候，需要把 Page 2 從磁盤讀入記憶體中，然后應用 change buffer 里 面的操作日志，生成一個正確的版本并回傳結果，

• 可以看到，直到需要讀 Page 2 的時候，這個資料頁才會被讀入記憶體，

• redo log 主要節省的是隨機寫磁盤的 IO 消耗(轉成順序寫)，而 change buffer 主要節省的則是隨機讀磁盤 的 IO 消耗，

  七.給字串加索引

字串欄位增加索引的方式

1. 直接創建完整索引，這樣可能比較占用空間;
2. 創建前綴索引，節省空間，但會增加查詢掃描次數，并且不能使用覆寫索引;
3. 倒序存盤，再創建前綴索引，用于繞過字串本身前綴的區分度不夠的問題，不能使用覆寫索引，不支持范圍掃描;
4. 創建 hash 欄位索引，查詢性能穩定，有額外的存盤和計算消耗，跟第三種方式一樣，不能使用覆寫索引，都不支持范圍掃描，

第一二種分析

完整索引和前綴索引的分析

你現在維護一個支持郵箱登錄的系統，用戶表是這么定義的:

mysql> create table SUser(
 ID bigint unsigned primary key, 
 email varchar(64),
 ...
 )engine=innodb;

由于要使用郵箱登錄，所以業務代碼中一定會出現類似于這樣的陳述句:

mysql> select f1, f2 from SUser where email='xxx';

分別創建兩種索引

mysql> alter table SUser add index index1(email); 
或
mysql> alter table SUser add index index2(email(6));

第一個陳述句創建的 index1 索引里面，包含了每個記錄的整個字串;
而第二個陳述句創建 的 index2 索引里面，對于每個記錄都是只取前 6 個位元組，
占用的空間會更小，這就是使用前綴索引的優勢

加入執行下面的sql陳述句,兩種索引該如何執行

select id,name,email from SUser where email='[email protected]';

完整索引

• 從 index1 索引樹找到滿足索引值是’[email protected]’的這條記錄，取得 ID2 的值;
• 到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正確的，將這行記錄加入結果集; (為什么還要判斷email的正確性,這個是server 層的行為，以防email欄位回傳的值不對, 其實反正肯定要讀入資料，順手判斷一下, 這個成本也并不大就是了)
• 取 index1 索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足 email='[email protected]’的條件了，回圈結束，
• 這個程序中，只需要回主鍵索引取一次資料，所以系統認為只掃描了一行，

前綴索引

• 從 index2 索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是 ID1;
• 到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不 是’[email protected]’，這行記錄丟棄;
• 取 index2 上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判斷，這次值對了，將這行記錄加入結果集;
• 重復上一步，直到在 idxe2 上取到的值不是’zhangs’時，回圈結束，
• 在這個程序中，要回主鍵索引取 4 次資料，也就是掃描了 4 行，

對比結果

• 通過這個對比，你很容易就可以發現，使用前綴索引后，可能會導致查詢陳述句讀資料的次數變多，
• 但是，對于這個查詢陳述句來說，如果你定義的 index2 不是 email(6) 而是 email(7)，也 就是說取 email 欄位的前 7 個位元組來構建索引的話，即滿足前綴’zhangss’的記錄只有 一個，也能夠直接查到 ID2，只掃描一行就結束了，
• 也就是說使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查 詢成本，

使用前綴索引,如何確定應該使用多長的前綴

• 我們在建立索引時關注的是區分度，區分度越高越好，因為區分度越高，意味著重復的鍵值越少，

• 依次選取不同長度的前綴來看這個值，比如我們要看一下 4~7 個位元組的前綴索引， 可以用這個陳述句:

  mysql> select
    count(distinct left(email,4))as L4, 
    count(distinct left(email,5))as L5, 
    count(distinct left(email,6))as L6, 
    count(distinct left(email,7))as L7,
  from SUser;
  

• 使用前綴索引很可能會損失區分度，所以你需要預先設定一個可以接受的損失比 例，比如 5%，然后，在回傳的 L4~L7 中，找出不小于 L * 95% 的值，假設這里 L6、L7 都滿足，你就可以選擇前綴長度為 6，

前綴索引對覆寫索引的影響

select id,email from SUser where email='[email protected]';

如果使用 index1(即 email 整個字串的索引結構)的話，可以利用覆寫索引， 從 index1 查到結果后直接就回傳了，不需要回到 ID 索引再去查一次，而如果使用 index2(即 email(6) 索引結構)的話，就不得不回到 ID 索引再去判斷 email 欄位的值，

即使你將 index2 的定義修改為 email(18) 的前綴索引，這時候雖然 index2 已經包含了 所有的資訊，但 InnoDB 還是要回到 id 索引再查一下，因為系統并不確定前綴索引的定義 是否截斷了完整資訊，

結論: 前綴索引無法使用覆寫索引

其他方式使用前綴索引

比如，我們國家的身份證號，一共 18 位，其中前 6 位是地址碼，所以同一個縣的人的身 份證號前 6 位一般會是相同的，

假設你維護的資料庫是一個市的公民資訊系統，這時候如果對身份證號做長度為 6 的前綴 索引的話，這個索引的區分度就非常低了，

方法三: 使用倒序存盤

• 如果你存盤身份證號的時候把它倒過來存，每次查詢的時 候，你可以這么寫:

  mysql> select field_list from t where id_card = reverse('input_id_card_string');
  

• 由于身份證號的最后 6 位沒有地址碼這樣的重復邏輯，所以最后這 6 位很可能就提供了足 夠的區分度，當然了，實踐中你不要忘記使用 count(distinct) 方法去做個驗證，

方法四: 使用 hash 欄位

• 可以在表上再創建一個整數欄位，來保存身份證的校驗 碼，同時在這個欄位上創建索引，

  mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
  

• 然后每次插入新記錄的時候，都同時用 crc32() 這個函式得到校驗碼填到這個新欄位，

• 由于校驗碼可能存在沖突，也就是說兩個不同的身份證號通過 crc32() 函式得到的結果可能是相同的，所以你的查詢陳述句 where 部分要判斷 id_card 的值是否精確相同，

  mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='****'
  

• 這樣，索引的長度變成了 4 個位元組，比原來小了很多，

第三種和第四種的異同點

相同點

• 都不支持范圍查詢，倒序存盤的欄位上創建的索引是按照倒序字 符串的方式排序的，已經沒有辦法利用索引方式查出身份證號碼在 [ID_X, ID_Y] 的所有市 民了，同樣地，hash 欄位的方式也只能支持等值查詢，

不同點

• 從占用的額外空間來看，倒序存盤方式在主鍵索引上，不會消耗額外的存盤空間，而 hash 欄位方法需要增加一個欄位，當然，倒序存盤方式使用 4 個位元組的前綴長度應該 是不夠的，如果再長一點，這個消耗跟額外這個 hash 欄位也差不多抵消了，

• 在 CPU 消耗方面，倒序方式每次寫和讀的時候，都需要額外呼叫一次 reverse 函式， 而 hash 欄位的方式需要額外呼叫一次 crc32() 函式，如果只從這兩個函式的計算復雜 度來看的話，reverse 函式額外消耗的 CPU 資源會更小些，

• 從查詢效率上看，使用 hash 欄位方式的查詢性能相對更穩定一些，因為 crc32 算出來 的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近 1， 而倒序存盤方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數，

八.mysql選錯索引原因及處理方法

現象

應該使用某個索引的時候,但是卻使用了別的索引或者沒有使用索引

優化器選擇索引邏輯

• 掃描的行數
• 否使用臨時表
• 是否排序

索引選擇例外和處理

• 一種方法是，采用 force index 強行選擇一個索引，

  • # a是索引
    select * from t force index(a) where a between 10000 and 20000;
    

• 第二種方法就是，我們可以考慮 修改陳述句，引導 MySQL 使用我們期望的索引，

• 第三種方法是，在有些場景下，我們可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引，

九.mysql對索引欄位進行函式操作導致不走索引搜索樹功能

1.條件欄位做函式操作

①.現象

假設你現在維護了一個交易系統，其中交易記錄表 tradelog 包含交易流水號 (tradeid)、交易員 id(operator)、交易時間(t_modified)等欄位，為了便于描 述，我們先忽略其他欄位，這個表的建表陳述句如下:

CREATE TABLE tradelog (

id int(11) NOT NULL,

tradeid varchar(32) DEFAULT NULL,

operator int(11) DEFAULT NULL,

t_modified datetime DEFAULT NULL,

PRIMARY KEY (id),

KEY tradeid (tradeid),
KEY t_modified (t_modified)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假設，現在已經記錄了從 2016 年初到 2018 年底的所有資料，運營部門有一個需求是， 要統計發生在所有年份中 7 月份的交易記錄總數，這個邏輯看上去并不復雜，你的 SQL 陳述句可能會這么寫:

mysql> select count(*) from tradelog where month(t_modified)=7;

由于 t_modified 欄位上有索引，于是你就很放心地在生產庫中執行了這條陳述句，但卻發現執行了特別久，才回傳了結果，

如果你問 DBA 同事為什么會出現這樣的情況，他大概會告訴你:如果對欄位做了函式計 算，就用不上索引了，這是 MySQL 的規定，

②.原因

現在你已經學過了 InnoDB 的索引結構了，可以再追問一句為什么?為什么條件是 where t_modified='2018-7-1’的時候可以用上索引，而改成 where month(t_modified)=7 的時候就不行了?

下面是這個 t_modified 索引的示意圖，方框上面的數字就是 month() 函式對應的值，

如果你的 SQL 陳述句條件用的是 where t_modified='2018-7-1’的話，引擎就會按照上面 綠色箭頭的路線，快速定位到 t_modified='2018-7-1’需要的結果，

實際上，B+ 樹提供的這個快速定位能力，來源于同一層兄弟節點的有序性，

但是，如果計算 month() 函式的話，你會看到傳入 7 的時候，在樹的第一層就不知道該怎 么辦了，

也就是說，對索引欄位做函式操作，可能會破壞索引值的有序性，因此優化器就決定放棄 走樹搜索功能，

需要注意的是，優化器并不是要放棄使用這個索引，

在這個例子里，放棄了樹搜索功能，優化器可以選擇遍歷主鍵索引，也可以選擇遍歷索引 t_modified，優化器對比索引大小后發現，索引 t_modified 更小，遍歷這個索引比遍歷 主鍵索引來得更快，因此最侄訓是會選擇索引 t_modified，

接下來，我們使用 explain 命令，查看一下這條 SQL 陳述句的執行結果，

key="t_modified"表示的是，使用了 t_modified 這個索引;我在測驗表資料中插入了 10 萬行資料，rows=100335，說明這條陳述句掃描了整個索引的所有值;Extra 欄位的 Using index，表示的是使用了覆寫索引，

③.解決方法

由于在 t_modified 欄位加了 month() 函式操作，導致了全索引掃描，為了能 夠用上索引的快速定位能力，我們就要把 SQL 陳述句改成基于欄位本身的范圍查詢，按照下 面這個寫法，優化器就能按照我們預期的，用上 t_modified 索引的快速定位能力了，

select count(*) from tradelog where

(t_modified >= '2016-7-1' and t_modified<'2016-8-1') or

(t_modified >= '2017-7-1' and t_modified<'2017-8-1') or

(t_modified >= '2018-7-1' and t_modified<'2018-8-1');

當然，如果你的系統上線時間更早，或者后面又插入了之后年份的資料的話，你就需要再把其他年份補齊，

到這里我給你說明了，由于加了 month() 函式操作，MySQL 無法再使用索引快速定位功 能，而只能使用全索引掃描，

不過優化器在個問題上確實有“偷懶”行為，即使是對于不改變有序性的函式，也不會考慮使用索引，比如，對于 select * from tradelog where id + 1 = 10000 這個 SQL 語 句，這個加 1 操作并不會改變有序性，但是 MySQL 優化器還是不能用 id 索引快速定位 到 9999 這一行，所以，需要你在寫 SQL 陳述句的時候，手動改寫成 where id = 10000 -1 才可以，

2.隱式型別轉換

①.隱式型別轉換規則

我們一起看一下這條 SQL 陳述句:

mysql> select * from tradelog where tradeid=110717;

交易編號 tradeid 這個欄位上，本來就有索引，但是 explain 的結果卻顯示，這條陳述句需 要走全表掃描，你可能也發現了，tradeid 的欄位型別是 varchar(32)，而輸入的引數卻是 整型，所以需要做型別轉換，

那么，現在這里就有兩個問題:

• 資料型別轉換的規則是什么?
• 為什么有資料型別轉換，就需要走全索引掃描?

先來看第一個問題，你可能會說，資料庫里面型別這么多，這種資料型別轉換規則更多，
我記不住，應該怎么辦呢?

這里有一個簡單的方法，看 select “10” > 9 的結果:

1. 如果規則是“將字串轉成數字”，那么就是做數字比較，結果應該是 1;
2. 如果規則是“將數字轉成字串”，那么就是做字串比較，結果應該是 0，

驗證結果如圖 3 所示，

從圖中可知，select “10” > 9 回傳的是 1，所以你就能確認 MySQL 里的轉換規則了: 在 MySQL 中，字串和數字做比較的話，是將字串轉換成數字，

②.原因

這時，你再看這個全表掃描的陳述句:

mysql> select * from tradelog where tradeid=110717;

就知道對于優化器來說，這個陳述句相當于:

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

也就是說，這條陳述句觸發了我們上面說到的規則:對索引欄位做函式操作，優化器會放棄走樹搜索功能，

3.隱式字符編碼轉換

①.現象

假設系統里還有另外一個表 trade_detail，用于記錄交易的操作細節，為了便于量化分析和復現，我往交易日志表 tradelog 和交易詳情表 trade_detail 這兩個表里插入一些數 據，

mysql> CREATE TABLE trade_detail (

id int(11) NOT NULL,

tradeid varchar(32) DEFAULT NULL,

trade step int(11) DEFAULT NULL, /* 操作步驟 */

step_info varchar(32) DEFAULT NULL, /* 步驟資訊 */

PRIMARY KEY (id),

KEY tradeid (tradeid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());

insert into tradelog values(2, 'aaaaaaab', 1000, now());

insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');

insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

這時候，如果要查詢 id=2 的交易的所有操作步驟資訊，SQL 陳述句可以這么寫:

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

我們一起來看下這個結果:

• 第一行顯示優化器會先在交易記錄表 tradelog 上查到 id=2 的行，這個步驟用上了主 鍵索引，rows=1 表示只掃描一行;
• 第二行 key=NULL，表示沒有用上交易詳情表 trade_detail 上的 tradeid 索引，進行 了全表掃描，

在這個執行計劃里，是從 tradelog 表中取 tradeid 欄位，再去 trade_detail 表里查詢匹 配欄位，因此，我們把 tradelog 稱為驅動表，把 trade_detail 稱為被驅動表，把 tradeid 稱為關聯欄位，

接下來，我們看下這個 explain 結果表示的執行流程:

圖中得執行流程:

• 第 1 步，是根據 id 在 tradelog 表里找到 L2 這一行;
• 第 2 步，是從 L2 中取出 tradeid 欄位的值;
• 第 3 步，是根據 tradeid 值到 trade_detail 表中查找條件匹配的行，explain 的結果里 面第二行的 key=NULL 表示的就是，這個程序是通過遍歷主鍵索引的方式，一個一個 地判斷 tradeid 的值是否匹配，

進行到這里，你會發現第 3 步不符合我們的預期，因為表 trade_detail 里 tradeid 欄位上 是有索引的，我們本來是希望通過使用 tradeid 索引能夠快速定位到等值的行，但，這里 并沒有，

②.原因

如果你去問 DBA 同學，他們可能會告訴你，因為這兩個表的字符集不同，一個是 utf8， 一個是 utf8mb4，所以做表連接查詢的時候用不上關聯欄位的索引，這個回答，也是通常 你搜索這個問題時會得到的答案，

但是你應該再追問一下，為什么字符集不同就用不上索引呢? 我們說問題是出在執行步驟的第 3 步，如果單獨把這一步改成 SQL 陳述句的話，那就是:

mysql> select * from trade_detail where tradeid=$L2.tradeid.value;

其中，$L2.tradeid.value 的字符集是 utf8mb4，

參照前面的兩個例子，你肯定就想到了，字符集 utf8mb4 是 utf8 的超集，所以當這兩個 型別的字串在做比較的時候，MySQL 內部的操作是，先把 utf8 字串轉成 utf8mb4 字符集，再做比較，

這個設定很好理解，utf8mb4 是 utf8 的超集，類似地，在程式設計語言里 面，做自動型別轉換的時候，為了避免資料在轉換程序中由于截斷導致資料 錯誤，也都是“按資料長度增加的方向”進行轉換的，

因此， 在執行上面這個陳述句的時候，需要將被驅動資料表里的欄位一個個地轉換成 utf8mb4，再跟 L2 做比較，

也就是說，實際上這個陳述句等同于下面這個寫法:

select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

CONVERT() 函式，在這里的意思是把輸入的字串轉成 utf8mb4 字符集，

這就再次觸發了我們上面說到的原則:對索引欄位做函式操作，優化器會放棄走樹搜索功能，到這里，你終于明確了，字符集不同只是條件之一，連接程序中要求在被驅動表的索引欄位上加函式操作，是直接導致對被驅動表做全表掃描的原因，

作為對比驗證，我給你提另外一個需求，“查找 trade_detail 表里 id=4 的操作，對應的 操作者是誰”，再來看下這個陳述句和它的執行計劃，

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

這個陳述句里 trade_detail 表成了驅動表，但是 explain 結果的第二行顯示，這次的查詢操 作用上了被驅動表 tradelog 里的索引 (tradeid)，掃描行數是 1，

這也是兩個 tradeid 欄位的 join 操作，為什么這次能用上被驅動表的 tradeid 索引呢?我 們來分析一下，

假設驅動表 trade_detail 里 id=4 的行記為 R4，那么在連接的時候(圖 5 的第 3 步)， 被驅動表 tradelog 上執行的就是類似這樣的 SQL 陳述句:

select operator from tradelog where traideid =$R4.tradeid.value;

這時候 $R4.tradeid.value 的字符集是 utf8, 按照字符集轉換規則，要轉成 utf8mb4，所 以這個程序就被改寫成:

select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

你看，這里的 CONVERT 函式是加在輸入引數上的，這樣就可以用上被驅動表的 traideid 索引，

③.解決方法

優化陳述句的方法:

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

• 比較常見的優化方法是，把 trade_detail 表上的 tradeid 欄位的字符集也改成 utf8mb4，這樣就沒有字符集轉換的問題了，

  • alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

• 修改 SQL 陳述句的方法

  mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and d.id=2;

  

  我主動把 l.tradeid 轉成 utf8，就避免了被驅動表上的字符編碼轉換，從 explain

  結果可以看到，這次索引走對了，

站在巨人的肩膀上摘蘋果:

https://time.geekbang.org/column/intro/100020801
https://www.cnblogs.com/lonelyxmas/p/10668426.html

標籤：MySQL

上一篇：神奇的 SQL 之 ICP → 索引條件下推

下一篇：Oracle事務Transaction