Mysql索引(究極無敵細節版)

參考了：
//www.jianshu.com/p/ace3cd6526c4
推薦up主//space.bilibili.com/377905911
推薦書籍《mysql是怎樣運行的》
推薦極客時間《MySQL實戰45講》——林曉斌

系列文章目錄和關於我

一丶什麼是索引

索引是存儲引擎快速找到記錄的一種數據結構。資料庫中的數據可以理解成字典中的單詞，而索引就是目錄，顯而易見這是一種空間換時間的做法，目錄佔用了空間，但是加快了我們找到單詞的速度，正如索引需要空間存儲，但是利用索引我們可以快速的找到想要的數據。

InnoDB存儲引擎存在幾種常見的索引：

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

本文主要討論B+樹索引

二丶索引的數據結構

可以加快查找速度的數據結構很多，為什麼mysql使用B+樹來實現昵，換句話說哈希表，有序數組，跳錶，平衡二叉搜索樹，B-樹等都可以優化搜索效率，為什麼偏偏使用B+樹

1.哈希表

哈希表，可以聯想Java中的HashMap，在HashMap源碼學習中，我們了解到Hash表的數據結構。如下圖

image-20220910125424477

哈希表通過hash演算法將key映射到數組對應的下標進行存儲，不可避免的會產生hash衝突（多個不同的key散列到相同的數組下標中），解決hash衝突常用拉鏈法，顧名思義，就是把相同hash值的節點組成鏈表串起來。在根據key查找value的過程中，只需要再次使用相同的hash演算法那麼就能拿到對應的數組下表，然後遍歷鏈表找到目標值即可,查找的效率是o(1)。

明明存在鏈表需要遍歷為什麼說時間複雜度o(1)
首先hash演算法計算是常數時間，
hash表會在需要的時候進行擴容，
維持鏈表長度盡量在一個常數範圍，從而保證遍歷常數個鏈表節點

mysql中存在自適應哈希索引，由innodb存儲引擎自己控制，利用查找O(1)的性質優化等值查詢。我們可以看出，hash表並不適合範圍查詢，對於Id<10這種範圍查詢只能遍歷hash表中每一個數據，相當於要進行一次全表掃描。我還有一個想法：從擴容的角度看，每次擴大數組大小後都需要移動元素到新的數組空間中，這部分的複製移動的開銷也許也是hash表不合適的原因（redis為了解決這個問題，使用漸進式hash的方式，在擴容的時候生成更大的數組，但不是一次移動所以數據，而是插入的新元素都放到新數組，老數組使用到的數據才會慢慢移動到新數組）redis基於記憶體的資料庫都需要通過漸進式hash優化擴容操作，基於磁碟的mysql若使用hash將慘不忍睹

2.有序數組

有序數組就是數據中元素有序，正因為有序，所以其在範圍查找上非常優秀，正因為要維持有序，在更改數據的時候，也許需要移動大量數組元素（比如插入一個較小的值，大於此值的數據都需要後移動），所以有序數據只適用於靜態數據（比如2020年人口資訊，這種不會改變的數據）

3.跳錶

image-20220911121331617

為了解決有序數組需要移動元素的問題，我們可以使用鏈表來維護元素，從而使更改元素效率為o(1)，但是鏈表的查找非常慢。由於鏈表整體是有序的，那麼我們可以使用二分查找優化查找效率，如上我們建立多級的節點，在查找的時候我們首先通過多級的索引依次找到最下層。對於範圍查找，由於底層數據是有序的，查找id<7的數組，首先我們找到id=7然後向左遍歷集合（可以把跳錶最下面一層優化為雙向鏈表，從而讓範圍查找速度也很快）

哪為什麼不使用跳錶來做索引昵？

跳錶是鏈表結構，一條數據一個結點，如果最底層要存放2kw數據，且每次查詢都要能達到二分查找的效果，2kw大概在2的24次方左右，所以，跳錶大概高度在24層左右。最壞情況下，這24層數據會分散在不同的數據頁里，也即是查一次數據會經歷24次磁碟IO。

image-20220911122320194

4.平衡二叉搜索樹

二叉搜索樹，即左子樹小於根，根小於右子樹，這種結構在查找的時候可以進行二分，根據根節點的值就可以確定期望的數據在左樹還是右樹。

img

但是二叉搜索樹在插入，刪除節點的時候可能出現樹極度不平衡的情況，出現樹退化成鏈表。

img

這個時候就需要維持樹的平衡——AVL:在滿足二叉搜索樹的條件下，要求任何節點的兩個子樹高度差不超過1。平衡二叉樹的查找是趨近於O(log(N))，但是需要維護一顆樹為AVL需要進行左旋，右旋的操作，更新的時間複雜度也是 O(log(N))。

為什麼不使用AVL做索引：節點存儲的數據內容太少。因為作業系統和磁碟之間一次數據交換是以頁為單位的，一頁 = 4K，即每次IO作業系統會將4K數據載入進記憶體。但是，在二叉樹每個節點的結構只保存一個關鍵字，一個數據區，兩個子節點的引用，並不能夠填滿4K的內容。倖幸苦苦做了一次的IO操作，卻只載入了一個關鍵字，在樹的高度很高，恰好又搜索的關鍵字位於葉子節點或者支節點的時候，取一個關鍵字要做很多次的IO。

5.B-樹，B+樹

B-樹就是B樹，英文是B-Tree，所以中國有許多人稱之為B-樹。B樹和B+樹是多路平衡查找樹，之所以多路，是為了契合磁碟的io操作——作業系統和磁碟之間一次數據交換是以頁為單位的，多路能讓讀取一頁能獲取更多的數據，讓樹的高度更低。

img

image-20220911143850373

上面兩圖，我們可以看出B樹和B+樹的區別

  1. B+樹葉子節點使用雙向指針串聯起來，這讓B+樹相比於B樹更加適合範圍查找
  2. B+樹非葉子節點並不存數據，所以每次查找數據都必須遍歷到葉子節點，時間複雜度穩定為O(logN)，B-樹在運氣好的時候可以在根節點直接拿到數據。但是正是因為非葉子節點不存儲數據，可以讓一次磁碟讀取一頁中包含的索引數據更多，每個節點能索引的範圍更大更精確，讓我們可以更改定位到期望的數據。由於B+樹的葉子節點的數據都是使用鏈表連接起來的，而且他們在磁碟里是順序存儲的，所以當讀到某個值的時候，磁碟預讀原理就會提前把這些數據都讀進記憶體，使得範圍查詢和排序都很快

B+樹在更改數據的時候，為了保證樹的平衡可能存在節點的分裂和合併，所以我們一般建議使用自增主鍵，在插入的時候，不會頻繁的發生節點的分裂。

三丶InnoDB索引方案

1.InnoDB行結構

InnoDB存儲引擎存儲一行數據使用的數據結構稱為行結構。

  • COMPACT

    img

    • 變長欄位列表：如varchar(m),Text，Blob類型的列，稱為變長欄位，由於其位元組數量不固定，需要在變成欄位列表中存儲這些欄位的長度，在記錄的真實數據中存儲內容
    • null值列表：如果表中沒用允許為null的列，那麼null值列表就不存在，否則把每一個允許為null的列使用一個二進位位來表示，二進位為1的時候表示值為null
    • 記錄頭資訊：佔用五個位元組，其中包含delete_flag(標記記錄是否被刪除)，next_record(下一條記錄的相對位置)等資訊
    • 記錄的真實資訊：如果表沒用定義主鍵，也沒有唯一不可重複不可為null的列，那麼innodb為我們生成一個隱藏列row_id,如果定義了主鍵那麼此列不存在，並且還有trx_id,roll_pointer兩個隱藏列，後續便是每一個列的真實數據。（char(M)類型的列，如果使用定長字元編碼，那麼位元組數不會加到變長欄位列表中，如果使用變長編碼，佔用長度會加入到變成欄位列表中（變長編碼那麼必須佔用M個位元組，varchar(M）則沒用這個要求）

  • REDUNDANT

    image-20221019072008079

    • 欄位長度偏移列表：此種行格式會把記錄所有列長度的偏移資訊存儲
    • null值的處理方式：先看偏移量的null比特位是否為1，如果為了那麼表示為null

  • 溢出列

    如果一個列太長，並不會傻乎乎的存儲所有數據在行記錄中，而是使用溢出列，COMPACT和REDUNDANT只會存儲該列的前768位元組然後存儲指向其他頁的地址，剩下的數據存在其他頁中。

  • DYNAMIC和COMPRESSED

    和COMPACT類似，但是二者不會存儲過長列的前768位元組，而是把真實數據都存儲到溢出中，記錄只存儲溢出頁的地址。COMPRESSED 還會使用壓縮演算法對頁面進行壓縮

2.InnoDB頁結構

頁是InnoDB管理存儲空間的基本單位，其默認大小為16k,InnoDB設計了很多不同的頁結構：存放Change Buffer的頁，存儲undo log 日誌的頁等等。對於表中數據，也存在在頁中

image-20221019073749192

最開始的時候UserRecords並不存在，隨著數據的插入，會從FreeSpace中申請一個記錄大小的空間，將其劃分到UserRecords部分，當FreeSpace用完只會繼續插入就需要申請新的頁。

2.1行結構中記錄頭資訊的作用

  • deleted_flag:標記是否被刪除，1表示被刪除，被刪除的列表通過next_record串聯起來，並且會記錄被刪除記錄的空火箭，這部分空間可以重複使用

  • min_rec_flag: B+樹每層非葉子節點，最小的目錄項記錄會被添加此標記

  • n_owned:

  • heap_no:UserRecords中存儲的用戶記錄是緊湊如同堆一樣排布的,heap_no是堆中記錄的編號，從2開始（0和1 被infimum+supremum佔用，infimum虛擬的最小記錄，supremum虛擬最大記錄）

  • next_record:表示當前記錄的真實數據，到下一條記錄的距離

    image-20221019221306491

    next_record左邊是變長欄位列表和null值列表（二者都是逆序存放資訊，也就是說距離next_record最近的是第一個欄位是否為null，第一個變長欄位的長度）右邊是記錄的真實數據（順序存放），且可以使記錄中靠前欄位和對應的欄位資訊在記憶體中更近，提高高速快取的命中率。這裡我們可以看到被刪除的記錄沒用立即被清除，只是不會被next_record串聯起來

    記錄按照主鍵從小到大形成單向鏈表

2.2頁目錄

上面我們知道了記錄在頁中按照主鍵從小到大的順序串聯成單向鏈表，那麼怎麼在一個頁中根據主鍵找到目標記錄昵——通過頁目錄進行二分查找。

  1. 頁目錄生成過程

    1. 將infimum，supermum以及所有未被刪除的記錄，分成多個組
    2. 每一個組中最大的記錄的n_owned存儲組中記錄條數
    3. 將每一個組中最後的記錄在頁面中的地址偏移量，存儲到頁面尾部——Page Directory中，這些地址偏移量稱為槽

    image-20221019221622236

  2. 根據查詢頁面記錄的過程

    通過二分法找到目標記錄中的槽，然後遍歷槽所在組的所有記錄

3.InnoDB索引方案

3.1為頁建立目錄項

InnoDB使用也作為管理和存儲空間的基本單位，最多只能保證16k的連續存儲。

image-20221019222637000
目錄項記錄的只是主鍵值和頁號兩個列，最下方是我們剛剛講到的innoDB存儲用戶記錄的頁。如果頁面數據量很大，可以繼續為目錄項建立目錄項

image-20221019223241323

3.2 根據目錄項定位數據行的過程

例如查找主鍵為10記錄

  1. 根據目錄項中的內容，確定目標記錄所在的頁

    如上圖頁33 存在記錄(1,30)，(320 32)，可以判斷主鍵位於1~320範圍的記錄在頁30，大於320的記錄在頁32

  2. 找到頁30後還要繼續在頁30中，通過目錄項記錄的頁確定目標記錄真正所在的頁

  3. 在真正存儲用戶記錄的頁（頁28）中通過槽定位到組，然後遍歷槽所在組的所有記錄

三丶聚集索引和非聚集索引

InnoDB存儲引擎是索引組織表——表中的數據按照主鍵順序存放。非聚集索引也稱做輔助索引，無論是聚集還是非聚集，其原理都是一顆B+樹，葉子節點都存儲數據，不同的是聚集索引葉子節點存儲的是一整行的數據，非聚集索引葉子節點存儲的是聚集索引值（主鍵值）。

image-20221019223857734

image-20221019224243999

如果數據表定義了主鍵，那麼這個主鍵索引就是聚集索引，如果沒有定義主鍵，mysql會選擇該表的第一個非空唯一的索引構建聚集索引，如果都沒有那麼mysql會生成一個隱藏的列（6位元組的列，並且插入自增）

自增主鍵會把數據自動向後插入，避免了插入過程中聚集索引節點分裂的問題。節點分裂會帶來大範圍的數據物理移動，帶來磁碟IO的性能損耗，並且我們一般建議盡量不要改動主鍵，主鍵的更改也會帶來page分裂，產生碎片。

四丶回表查詢

image-20221019224622320

image-20220911153409074

如上圖，假如我們有一張表存在三個欄位id,age,name我們在id上建立了主鍵索引，這時候id主鍵索引也是聚集索引，在age上建立了普通索引，這時候age索引就是非聚集索引。如果我們執行select * from table where age=1這時候先走age索引（如果數據量較大，數據量少直接全表掃描了）那麼會找到對應的主鍵id，繼續到主鍵id索引中找到目標數據，這個操作叫做回表。

這就是為什麼根據主鍵查找快於根據其他索引列查找，因為如果其他索引列沒有包含我們select語句中需要的列（如果是select id from table where age<10,那麼age索引是可以覆蓋到需要的數據的(葉子節點存儲了id)，那麼也不會回表），那麼會走主鍵索引拿到需要的數據，多了一步回表操作。

這裡我們也可以看到為什麼建議使用select * ,這意味著查找所有列，如果配合上普通索引，那麼大概率這個普通索引不會覆蓋到索引列，導致需要回表查詢。並且select*這種”我全都要”大概率會查詢到我們不需要的列，造成不必要的網路資源消耗，增加不必要的io，增加不必要的記憶體消耗。

五丶聯合索引

image-20221019224622320

image-20220911155318982

聯合索引是指對錶上的多個列建立索引，如上圖表存在四個欄位id,address,name,age，我們在name和age上建立索引，上圖我們粗略的展示了聯合索引的B+樹結構。我們可以觀察到在葉子節點中name是有序的，但是age無序，聯合索引是按照索引定義的順序排序的，這就導致select xxx from table where name='b'是可以根據上面定義的聯合索引查找數據的，但是“select xxx from table where age=12是無法走上面定義的聯合索引的。這就是常說的最左前綴匹配原則`的原理。

  • 聯合索引可以減少回表

    如果我們執行select age,id from table where name='a' and age=10,這個時候由於我們定義的聚集索引一級包含了需要的數據就不需要進行回表操作了（這其實也被稱為覆蓋索引，即非聚集索引中可以查詢到全部需要的列，那麼就不需要走聚集索引回表查詢數據）

  • 聯合索引可以優化排序

    上圖中的聯合索引，我們可以看到，名稱相同的節點，其年齡是有序的

    也就是說select * from table where name='a' order by age這個語句將避免多一次的排序操作（select* from table where id=1 order by age會走主鍵索引拿到所有符合數據進行排序，這裡說的避免一次排序操作指拿到的數據本身就是有序的 所有不需要再次排序）

  • 索引下推ICP

    全稱Index Condition PushDown,mysql 5.6後支援的一種根據索引進行查詢優化的操作。mysql資料庫會在取出所有數據的同時判斷是否進行where條件的過濾，將where的部分過濾放在存儲引擎層。

    image-20220911162152397

    mysql5.6之前如果執行select * from table where name like '張%' and age=10這時候會先從name age的聯合索引中拿到name滿足張開頭的數據，然後回表，mysql支援ICP後，效果圖如下

    image-20220911162202226

    mysql會根據聯合索引中記錄的age對數據進行過濾，這時候age不等於10的數據將不會回表，將回表次數從4優化到了2，這就是索引下推。

  • 如何安排聯合索引的順序

    第一原則是，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的，比如業務中存在兩個高頻查詢，根據name，以及根據name查詢後根據age排序，這個時候我們應該建立name age的聯合索引，上面我們說過name,age的所有其中name是有序的，age只在name相同的情況下才是有序的，這樣可以減少建立name的普通索引，並且優化排序，甚至利用索引下推減少回表。如果還存在根據age進行的查詢，那麼需要單獨維護一個age的普通索引

六丶索引與排序和分組

1.索引用於排序

假設我們有一張表存儲id，姓名，年齡以及城市，我們在城市欄位上建立索引

執行select city,name,age from t where city='杭州' order by name limit 1000 ;city上具備索引，那麼可以通過city欄位拿到符合要求的數據

image-20221020225216892

拿到城市和主鍵的資訊之後，還需要回表，來到主鍵索引上查詢到需要的列，接下來需要排序

  • 如果sort_buffer(MySQL 會給每個執行緒分配一塊記憶體用於排序，稱為 sort_buffer)可以容納下目標記錄，那麼mysql會使用sort_buffer進行快速排序，這個過程叫做全欄位排序(全部的欄位都在sort_buffer中)

    image-20221020225712565

  • 如果sort_buffer無法容納下這麼多記錄，將使用外部文件排序，mysql把需要排序的數據分為多個文件，分別快排然後合併

  • 如果mysql認為行太長，那麼會使用row_id排序——從city索引找到一條數據，回表拿到索引需要排序的欄位以及主鍵id，在sort_buffer中只存儲需要排序的欄位和主鍵，然後排序後，再次回表查詢全部需要的列，組成結構集返回

    image-20221020225927566

  • 如果直到的limit 比較小，比如limit 3，也許mysql會維護一個大小為3的堆，進行排序獲得前3條

上面講了mysql是如何排序的，可以看到上述的排序方式，都需要利用記憶體或者利用磁碟文件進行排序，總體來說是浪費空間以及效率不高的，那麼如何可以讓order by更快昵——創建一個 city 和 name 的聯合索引

image-20221020230336309

有了這個聯合索引，mysql可以找到城市為杭州的數據，然後回標查詢需要的欄位，然後向右取下一條，並不需要排序，因為city=杭州的數據name自然是有序的。這就是索引對排序的優化

  • 聯合索引排序順序需要符合最左前綴原則

  • 聯合索引排序，不能將ACS和DESC混合使用（mysql8降序索引似乎可以解決這個問題）

  • 如果形成掃描區間的列 和排序的列不是同一個索引，可能也不能使用到索引優化排序

    select * from key1 = a oder by key2 key1，key2不是聯合索引，各自包含一個索引，那麼mysql選擇key1索引數據。

  • 排序列如何使用了函數，那麼不能排序，函數也許會改變索引的單調性

2.索引用於分組

select key1,key2,key3 ,count(*) from table group by key1,key2,key3 如果key1，key2，key3沒有建立聯合索引，那麼需要建立用於統計的臨時表，將掃描的數據加入到臨時表進行統計，但是如果我們按照 key1,key2,key3的順序建立了聯合索引，那麼索引中的主鍵自然就是分好組的。索引用於分組的注意事項基本上和排序相同，這裡不做過多贅述

七丶索引建立和使用原則

1.為搜索，排序，分組的列建立索引

一般只為出現在where後面的列，連接子句中的列，出現在order by,或者group by的列進創建索引。不要無腦建立索引，索引是需要存儲在磁碟上的，佔用空間，並且在新增，刪除，修改的時候還需要維護索引，是需要時間的。

比如select xxx from table where name= 'a' order by user_no,這條查詢語句可以選擇在name上建立索引，也可以選擇在user_no 上建立索引，後者可以優化排序。

2.考慮列中不重複的個數建立索引

select xxxx from table where sex=1 這裡不要為sex性別建立索引，性別通常只有男和女，為其建立索引，b+樹只有兩個節點，查找之後還要對一半的進行回表，不如直接走全表掃描

3.索引列儘可能小

mysql基本數據類型十分豐富，整數類型有tinyint,mediumint,int，bigint，我們應該盡量使用佔用位元組數小的數據類型，這樣可以讓每次讀取磁碟獲取一頁的數據，可以獲得更多的範圍資訊

4.為列前綴進行索引

比如說有英文名可能很長，每次都是根據FirstName 進行like查找，這時候可以選擇為列的前10個字元建立索引(alter table user add index idx_name(name(10)))。但是十個字元之後將無法使用索引。且前綴索引會無法使用到覆蓋索引減少回表的功能，比如select name id,where name=abc123，加入為name前三個字元建立了索引，會在前綴索引中找到符合的數據比如abc111,abc121等等這個時候name的前綴索引還是需要獲取主鍵回表然後判斷name是否符合要求。

5.合理的建立覆蓋索引

在聯合索引小節中，我們總結了聯合索引的好處，減少回表，優化排序和分組，索引下推。

6.不要在uuid上建立索引

首先uuid佔用位元組大，導致每一頁範圍資訊少，並且uuid無序，這會導致插入數據的時候節點的分裂。這裡也說明了自增主鍵優秀的點，不會頻繁的節點分裂，並且不要修改主鍵，避免不必要的節點分裂。相比於uuid作為主鍵，不如使用分散式自增主鍵生成的方案

7.存在聯合索引的情況下，不要重複建立索引

存在name,age的聯合索引，那麼不需要再為name單獨建立索引了，但是可以為age建立索引，原理在聯合索引中進行了講解。

8.盡量使用自增主鍵

自增主鍵能減少聚簇索引的頁分裂，如果插入的主鍵一會兒天一會兒底，會造成頁面的分裂，同樣更新主鍵也會導致移動複製

9.普通索引和唯一索引如何做出抉擇

如果業務邏輯可以保證索引列的唯一，不需要依賴唯一索引保證唯一性的話，盡量使用普通索引。

9.1普通索引唯一索引等值查詢的性能差異微乎其微，唯一索引略勝一籌

對於普通索引來說，查找到滿足條件的第一個記錄 (5,500) 後，需要查找下一個記錄，直到碰到第一個不滿足 k=5 條件的記錄。對於唯一索引來說，由於索引定義了唯一性，查找到第一個滿足條件的記錄後，就會停止繼續檢索。InnoDB 的數據是按數據頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候，並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在InnoDB 中，每個數據頁的大小默認是 16KB，也就是說只有唯一索引滿足等值條件的數據跨頁的時候，才需要再一次io，這個概率是比較小的

9.2插入和更新的效率，普通索引由於唯一索引

對於唯一索引來說，需要將數據頁讀入記憶體，判斷到沒有衝突，插入這個值，語句執行束；對於普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了

當需要更新一個數據頁時，如果數據頁在記憶體中就直接更新，而如果這個數據頁還沒有在內
存中的話，在不影響數據一致性的前提下，InooDB 會將這些更新操作快取在 change
buffer 中，這樣就不需要從磁碟中讀入這個數據頁了。在下次查詢需要訪問這個數據頁的
時候，將數據頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。通過這種方
式就能保證這個數據邏輯的正確性.
change buffer 在記憶體中有拷貝，也會被寫入到磁碟上。
將 change buffer 中的操作應用到原數據頁，得到最新結果的過程稱為 merge。除了訪問
這個數據頁會觸發 merge 外，系統有後台執行緒會定期 merge。在資料庫正常關（shutdown）的過程中，也會執行 merge 操作

八丶索引失效

image-20220911171245100

上圖是mysql的基本架構，其中存在優化器，其作用是不改變sql執行j結果的情況下，讓sql更加簡單，並且根據成本分析，制定執行計劃。是否走索引，走什麼索引也是優化器來決定的（sql中可以提示使用什麼索引，強制使用某一個索引）。

image-20220911171629875

image-20220911171736651

常見索引失效的原因有

1.不滿足最左前綴原則

如果存在a,b,c的聯合索引，select * from table where b=2 and a=1這種時候還是可能走聯合索引的，mysql會優化語句,但是select * from table where b=1 and c=2是無法走聯合索引的，因為b，c在b+樹中整體無序

2.使用了select*

使用select*需要回表，也許mysql優化器評估後覺得走非聚集索引，不如直接全表掃描

3.like查詢左邊有%

以xxx開頭是可以走索引的，因為是有序的，但是以xxx結尾和包含xxx是無法走索引的。因為字元串的比較是從最左的字元開始比較的

4.order by 使用了聯合索引中不存在的列，或者順序不符合最左前綴匹配

5.group by 使用了聯合索引中不存在的列，或者順序不符合最左前綴匹配

6.不要在條件欄位函數操作，注意隱式類型轉換，小心隱式字元編碼轉換

例如在A表的key1上建立索引，key1是int類型

6.1條件欄位函數操作

select xxx from A where key1+1<10 理論上說mysql可以進行優化，但是最好不要這麼做，更不要select xxx from key1 where abs(key1)<10，mysql任何索引列上進行這些操作是會影響單調性的，直接無腦不走索引，分組排序也一樣 ，select * from B left join A on B.key2 = A.key1+1 這個語句也一樣（連表查詢的原理見Mysql單表訪問方法，索引合併，多表連接原理，基於規則的優化，子查詢優化）

6.2 注意隱式類型轉換

select * from A where key1>'10' 這個語句中key1是int類型，通樣無法走索引，因為是將key1轉化為字元串比較，還是將'10' 轉化為數字比較昵，如果是前者那麼key=9符合要求，如果是後者key1=9不符合要求。

6.3 小心隱式字元編碼轉換

如果兩個表的字符集不同，那麼做表連接查詢的時候用不上關聯欄位的索引，比如字符集 utf8mb4 是 utf8 的超集，所以當這兩個類型的字元串在做比較的時候，MySQL 內部的操作是，先把 utf8 字元串轉成 utf8mb4 字符集，再做比較，相當於其中一列需要使用convert函數導致索引失效

Tags:

VirMach 便宜 VPS

QNews

QNews