目錄

• 一.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 在記憶體中，直接更新記憶體;
  1. Page 2 沒有在記憶體中，就在記憶體的 change buffer 區域，記錄下“我要往 Page 2 插
     入一行”這個信息
  2. 將上述兩個動作記入 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