[TOC]
MySQL索引和SQL調優
本文有參考網上其他相關文章，本文最後有附參考的連結
MySQL索引
MySQL支援諸多儲存引擎，而各種儲存引擎對索引的支援也各不相同，因此MySQL資料庫支援多種索引型別，如BTree索引，雜湊索引，全文索引等等。為了避免混亂，本文將只關注於BTree索引，因為這是平常使用MySQL時主要打交道的索引。
MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構。提取句子主幹，就可以得到索引的本質：索引是資料結構。
MySQL索引原理
索引目的
索引的目的在於提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。如果沒有索引，那麼你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺得如果沒有索引，這個事情根本無法完成？
我們們去圖書館借書也是一樣，如果你要借某一本書，一定是先找到對應的分類科目，再找到對應的編號，這是生活中活生生的例子，通用索引，可以加快查詢速度，快速定位。
索引原理
所有索引原理都是一樣的，通過不斷的縮小想要獲得資料的範圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查詢方式來鎖定資料。
資料庫也是一樣，但顯然要複雜許多，因為不僅面臨著等值查詢，還有範圍查詢(>、<、between)、模糊查詢(like)、並集查詢(or)、多值匹配（in【in本質上屬於多個or】）等等。資料庫應該選擇怎麼樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把資料分成段，然後分段查詢呢？最簡單的如果1000條資料，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條資料，只要找第三段就可以了，一下子去除了90%的無效資料。但如果是1千萬的記錄呢，分成幾段比較好？稍有演算法基礎的同學會想到搜尋樹，其平均複雜度是lgN，具有不錯的查詢效能。但這裡我們忽略了一個關鍵的問題，複雜度模型是基於每次相同的操作成本來考慮的，資料庫實現比較複雜，資料儲存在磁碟上，而為了提高效能，每次又可以把部分資料讀入記憶體來計算，因為我們知道訪問磁碟的成本大概是訪問記憶體的十萬倍左右，所以簡單的搜尋樹難以滿足複雜的應用場景。
索引結構
任何一種資料結構都不是憑空產生的，一定會有它的背景和使用場景，我們現在總結一下，我們需要這種資料結構能夠做些什麼，其實很簡單，那就是：每次查詢資料時把磁碟IO次數控制在一個很小的數量級，最好是常數數量級。那麼我們就想到如果一個高度可控的多路搜尋樹是否能滿足需求呢？就這樣，b+樹應運而生。
b+樹的索引結構解釋
淺藍色的塊我們稱之為一個磁碟塊，可以看到每個磁碟塊包含幾個資料項（深藍色所示）和指標（黃色所示），如磁碟塊1包含資料項17和35，包含指標P1、P2、P3，P1表示小於17的磁碟塊，P2表示在17和35之間的磁碟塊，P3表示大於35的磁碟塊。真實的資料存在於葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點不儲存真實的資料，只儲存指引搜尋方向的資料項，如17、35並不真實存在於資料表中。
b+樹的查詢過程
如圖所示，如果要查詢資料項29，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO，在記憶體中用二分查詢確定29在17和35之間，鎖定磁碟塊1的P2指標，記憶體時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指標的磁碟地址把磁碟塊3由磁碟載入到記憶體，發生第二次IO，29在26和30之間，鎖定磁碟塊3的P2指標，通過指標載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中做二分查詢找到29，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料查詢只需要三次IO，效能提高將是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。
b+樹性質
-
通過上面的分析，我們知道間越小，資料項的數量越多，樹的高度越低。這就是為什麼每個資料項，即索引欄位要儘量的小，比如int佔4位元組，要比bigint8位元組少一半。這也是為什麼b+樹要求把真實的資料放到葉子節點而不是內層節點，一旦放到內層節點，磁碟塊的資料項會大幅度下降，導致樹增高。當資料項等於1時將會退化成線性表。
-
當b+樹的資料項是複合的資料結構，比如(name,age,sex)的時候，b+數是按照從左到右的順序來建立搜尋樹的，比如當(張三,20,F)這樣的資料來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的資料；但當(20,F)這樣的沒有name的資料來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜尋樹的時候name就是第一個比較因子，必須要先根據name來搜尋才能知道下一步去哪裡查詢。比如當(張三,F)這樣的資料來檢索時，b+樹可以用name來指定搜尋方向，但下一個欄位age的缺失，所以只能把名字等於張三的資料都找到，然後再匹配性別是F的資料了， 這個是非常重要的性質，即索引的最左匹配特性。
MySQL 索引實現
在MySQL中，索引屬於儲存引擎級別的概念，不同儲存引擎對索引的實現方式是不同的，本文主要討論MyISAM和InnoDB兩個儲存引擎的索引實現方式。
MyISAM索引實現
MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是資料記錄的地址。 下圖是MyISAM索引的原理圖：
這裡設表一共有三列，假設我們以Col1為主鍵，則上圖便是一個MyISAM表的主索引（Primary key）示意圖。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：
同樣也是一顆B+Tree，data域儲存資料記錄的地址。因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。
MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的聚集索引區分。
InnoDB索引實現
雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。
第一個重大區別是InnoDB的資料檔案本身就是索引檔案。從上文知道，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。
上圖是InnoDB主索引（同時也是資料檔案）的示意圖，可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，型別為長整形。
第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，下圖為定義在Col3上的一個輔助索引：
這裡以英文字元的ASCII碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。
瞭解不同儲存引擎的索引實現方式對於正確使用和優化索引都非常有幫助，例如知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好主意，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。
如何建立合適的索引
建立索引的原理
一個最重要的原則是最左字首原理，在提這個之前要先說下聯合索引，MySQL中的索引可以以一定順序引用多個列，這種索引叫做聯合索引，一般的，一個聯合索引是一個有序元組<a1, a2, …, an>，其中各個元素均為資料表的一列。另外，單列索引可以看成聯合索引元素數為1的特例。
索引匹配的最左原則具體是說，假如索引列分別為A，B，C，順序也是A，B，C：
- 那麼查詢的時候，如果查詢【A】【A，B】 【A，B，C】，那麼可以通過索引查詢
- 如果查詢的時候，採用【A，C】，那麼C這個雖然是索引，但是由於中間缺失了B，因此C這個索引是用不到的，只能用到A索引
- 如果查詢的時候，採用【B】 【B，C】 【C】，由於沒有用到第一列索引，不是最左字首，那麼後面的索引也是用不到了
- 如果查詢的時候，採用範圍查詢，並且是最左字首，也就是第一列索引，那麼可以用到索引，但是範圍後面的列無法用到索引
複製程式碼因為索引雖然加快了查詢速度，但索引也是有代價的：索引檔案本身要消耗儲存空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在執行時也要消耗資源維護索引，因此索引並不是越多越好
在使用InnoDB儲存引擎時，如果沒有特別的需要，請永遠使用一個與業務無關的自增欄位作為主鍵。如果從資料庫索引優化角度看，使用InnoDB引擎而不使用自增主鍵絕對是一個糟糕的主意。
InnoDB使用聚集索引，資料記錄本身被存於主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點內（大小為一個記憶體頁或磁碟頁）的各條資料記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開闢一個新的頁（節點）。如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。如下：
這樣就會形成一個緊湊的索引結構，近似順序填滿。由於每次插入時也不需要移動已有資料，因此效率很高，也不會增加很多開銷在維護索引上。
如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，如下：
此時MySQL不得不為了將新記錄插到合適位置而移動資料，甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉，此時又要從磁碟上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE來重建表並優化填充頁面。
因此，只要可以，請儘量在InnoDB上採用自增欄位做主鍵。
建立索引的常用技巧
-
最左字首匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
-
=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式
-
儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄
-
索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
-
儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可，當然要考慮原有資料和線上使用情況
MySQL優化
配置優化
配置優化指的MySQL 的 server端的配置，一般對於業務方而言，可以不用關注，畢竟會有專門的DBA來處理，但是對於原理的瞭解，我想，我們開發，是需要了解的
基本配置
- innodb_buffer_pool_size
- 這是安裝完InnoDB後第一個應該設定的選項。緩衝池是資料和索引快取的地方：這個值越大越好，這能保證你在大多數的讀取操作時使用的是記憶體而不是硬碟。典型的值是5-6GB(8GB記憶體)，20-25GB(32GB記憶體)，100-120GB(128GB記憶體)。
- innodb_log_file_size
- 這是redo日誌的大小。redo日誌被用於確保寫操作快速而可靠並且在崩潰時恢復。一直到MySQL 5.1，它都難於調整，因為一方面你想讓它更大來提高效能，另一方面你想讓它更小來使得崩潰後更快恢復。幸運的是從MySQL 5.5之後，崩潰恢復的效能的到了很大提升，這樣你就可以同時擁有較高的寫入效能和崩潰恢復效能了。一直到MySQL 5.5，redo日誌的總尺寸被限定在4GB(預設可以有2個log檔案)。這在MySQL 5.6裡被提高了。如果你知道你的應用程式需要頻繁的寫入資料並且你使用的時MySQL 5.6，你可以一開始就把它這是成4G。
- max_connections
- 如果你經常看到‘Too many connections'錯誤，是因為max_connections的值太低了。這非常常見因為應用程式沒有正確的關閉資料庫連線，你需要比預設的151連線數更大的值。max_connection值被設高了(例如1000或更高)之後一個主要缺陷是當伺服器執行1000個或更高的活動事務時會變的沒有響應。在應用程式裡使用連線池或者在MySQL裡使用程式池有助於解決這一問題。
InnoDB配置
- innodb_file_per_table
- 這項設定告知InnoDB是否需要將所有表的資料和索引存放在共享表空間裡（innodb_file_per_table = OFF） 或者為每張表的資料單獨放在一個.ibd檔案（innodb_file_per_table = ON）。每張表一個檔案允許你在drop、truncate或者rebuild表時回收磁碟空間。這對於一些高階特性也是有必要的，比如資料壓縮。但是它不會帶來任何效能收益。你不想讓每張表一個檔案的主要場景是：有非常多的表（比如10k+）。MySQL 5.6中，這個屬性預設值是ON，因此大部分情況下你什麼都不需要做。對於之前的版本你必需在載入資料之前將這個屬性設定為ON，因為它只對新建立的表有影響。
- innodb_flush_log_at_trx_commit
- 預設值為1，表示InnoDB完全支援ACID特性。當你的主要關注點是資料安全的時候這個值是最合適的，比如在一個主節點上。但是對於磁碟（讀寫）速度較慢的系統，它會帶來很巨大的開銷，因為每次將改變flush到redo日誌都需要額外的fsyncs。將它的值設定為2會導致不太可靠（reliable）因為提交的事務僅僅每秒才flush一次到redo日誌，但對於一些場景是可以接受的，比如對於主節點的備份節點這個值是可以接受的。如果值為0速度就更快了，但在系統崩潰時可能丟失一些資料：只適用於備份節點。
- innodb_flush_method
- 這項配置決定了資料和日誌寫入硬碟的方式。一般來說，如果你有硬體RAID控制器，並且其獨立快取採用write-back機制，並有著電池斷電保護，那麼應該設定配置為O_DIRECT；否則，大多數情況下應將其設為fdatasync（預設值）。sysbench是一個可以幫助你決定這個選項的好工具。
- innodb_log_buffer_size
- 這項配置決定了為尚未執行的事務分配的快取。其預設值（1MB）一般來說已經夠用了，但是如果你的事務中包含有二進位制大物件或者大文字欄位的話，這點快取很快就會被填滿並觸發額外的I/O操作。看看Innodb_log_waits狀態變數，如果它不是0，增加innodb_log_buffer_size。
其他設定
- query_cache_size
- query cache（查詢快取）是一個眾所周知的瓶頸，甚至在併發並不多的時候也是如此。 最佳選項是將其從一開始就停用，設定query_cache_size = 0（現在MySQL 5.6的預設值）並利用其他方法加速查詢：優化索引、增加拷貝分散負載或者啟用額外的快取（比如memcache或redis）。如果你已經為你的應用啟用了query cache並且還沒有發現任何問題，query cache可能對你有用。這是如果你想停用它，那就得小心了。
- log_bin
- 如果你想讓資料庫伺服器充當主節點的備份節點，那麼開啟二進位制日誌是必須的。如果這麼做了之後，還別忘了設定server_id為一個唯一的值。就算只有一個伺服器，如果你想做基於時間點的資料恢復，這（開啟二進位制日誌）也是很有用的：從你最近的備份中恢復（全量備份），並應用二進位制日誌中的修改（增量備份）。二進位制日誌一旦建立就將永久儲存。所以如果你不想讓磁碟空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊檔案，或者設定 expire_logs_days 來指定過多少天日誌將被自動清除。記錄二進位制日誌不是沒有開銷的，所以如果你在一個非主節點的複製節點上不需要它的話，那麼建議關閉這個選項。
- skip_name_resolve
- 當客戶端連線資料庫伺服器時，伺服器會進行主機名解析，並且當DNS很慢時，建立連線也會很慢。因此建議在啟動伺服器時關閉skip_name_resolve選項而不進行DNS查詢。唯一的侷限是之後GRANT語句中只能使用IP地址了，因此在新增這項設定到一個已有系統中必須格外小心。
SQL 調優
一般要進行SQL調優，那麼就說有慢查詢的SQL，系統或者server可以開啟慢查詢日誌，尤其是線上系統，一般都會開啟慢查詢日誌，如果有慢查詢，可以通過日誌來過濾。但是知道了有需要優化的SQL後，下面要做的就是如何進行調優
慢查詢優化基本步驟
- 先執行看看是否真的很慢，注意設定SQL_NO_CACHE
- where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高
- explain檢視執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
- order by limit 形式的sql語句讓排序的表優先查
- 瞭解業務方使用場景
- 加索引時參照建索引的幾大原則
- 觀察結果，不符合預期繼續從0分析
常用調優手段
執行計劃explain
在日常工作中，我們有時會開慢查詢去記錄一些執行時間比較久的SQL語句，找出這些SQL語句並不意味著完事了，我們常常用到explain這個命令來檢視一個這些SQL語句的執行計劃，檢視該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來檢視。所以我們深入瞭解MySQL的基於開銷的優化器，還可以獲得很多可能被優化器考慮到的訪問策略的細節，以及當執行SQL語句時哪種策略預計會被優化器採用。
使用explain 只需要在原有select 基礎上加上explain關鍵字就可以了，如下：
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)
複製程式碼簡要解釋下explain各個欄位的含義
- id : 表示SQL執行的順序的標識,SQL從大到小的執行
- select_type：表示查詢中每個select子句的型別
- table：顯示這一行的資料是關於哪張表的，有時不是真實的表名字
- type：表示MySQL在表中找到所需行的方式，又稱“訪問型別”。常用的型別有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，效能從差到好）
- possible_keys：指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用
- Key：key列顯示MySQL實際決定使用的鍵（索引），如果沒有選擇索引，鍵是NULL。
- key_len：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引欄位的最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的）
- ref：表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值
- rows： 表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數，理論上行數越少，查詢效能越好
- Extra：該列包含MySQL解決查詢的詳細資訊
EXPLAIN的特性
- EXPLAIN不會告訴你關於觸發器、儲存過程的資訊或使用者自定義函式對查詢的影響情況
- EXPLAIN不考慮各種Cache
- EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作
- 部分統計資訊是估算的，並非精確值
- EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT後檢視執行計劃。
實戰演練
表結構和查詢語句
假如有如下表結構
circlemessage_idx_0 | CREATE TABLE `circlemessage_idx_0` (
`circle_id` bigint(20) unsigned NOT NULL COMMENT '群組id',
`from_id` bigint(20) unsigned NOT NULL COMMENT '傳送使用者id',
`to_id` bigint(20) unsigned NOT NULL COMMENT '指定接收使用者id',
`msg_id` bigint(20) unsigned NOT NULL COMMENT '訊息ID',
`type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '訊息型別',
PRIMARY KEY (`msg_id`,`to_id`),
KEY `idx_from_circle` (`from_id`,`circle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
複製程式碼通過執行計劃explain分析如下查詢語句
mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and msg_id>=6273803462253938690 and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16 | NULL | 349780 | Using where |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
複製程式碼mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16 | NULL | 30 | Using where |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
複製程式碼問題分析
通過上面兩個執行計劃可以發現當沒有msg_id >= xxx這個查詢條件的時候，檢索的rows要少很多，並且兩者查詢的時候都用到了索引，而且用到的還只是主鍵索引。那說明索引應該是不合理的，沒有發揮最大作用。
分析這個執行計劃可以看到，當包含msg_id >= xxx 查詢條件的時候，rows有34w多行，這種情況，說明檢索太多，要麼就是表裡面確實有這麼大，要麼就是索引不合理沒有用到索引，大都情況是沒用合理用到索引。列中所用到的索引也是PRIMARY，那就可能是(msg_id,to_id)的其中一個，注意我們建立表的時候msg_id索引的順序是在to_id前面的，因此MySQL查詢一定會優先用msg_id索引，在使用了msg_id索引後，就已經檢索出了34w行，並且由於msg_id的查詢條件是大於等於，因此，再這個查詢條件後，就不能再用到to_id的索引。
然後再看key_len長度為16，結合 key為PRIMARY，那麼可以分析得知，只有一個主鍵索引被用到。
最後看看 type 值，是range，那麼就說明這個查詢要麼是範圍查詢，要麼就是多值匹配。
請注意，from_id != xxx 這樣的語句，是無法用到索引的。 只有from_id = xxx就可以用到所以，因此from id 的索引其實可以不用，建立索引的時候就要考慮清楚
如何優化
既然知道索引不合理，那麼就要分析並調整索引。一般而言，我們既然要從單表裡面查詢，那麼就需要能夠知道大體，單表裡面大致會有哪些資料，現在的量級大概是多少。
然後開始下一步的分析，既然msgid是被設定為了主鍵，那一定是全域性唯一的，所有，有多少資料量就至少會有多少條msgid；那麼檢索msg_id基本就是檢索整個表了。我們要做的優化就是要儘量減少索引，減少查詢的行數；那麼就需要思考，通過查詢哪些欄位才能夠減少行數？比如，一個張表裡面，所屬某個使用者的資料，會不會比查詢msgid的行數要少？ 查詢某個使用者並且是屬於某個圈子的，那會不會就更少了？ 等等。。。
然後根據實際情況分析，單表裡面命中to_id 的行數應該是會小於命中msg_id的，因此要首先保證能夠使用到to_id的索引，為此，可以設定主鍵的時候把msg_id和to_id的順序互動一下；但是，由於已經是線上的表，已經有了大量資料，並且業務開始執行，這種情況下，修改主鍵會引發很多問題（當然修改索引是OK的），因此，不建議直接修改主鍵。那麼，為了保證有效使用to_id的索引，就要新建一個聯合索引；那麼新建的聯合索引的第一索引欄位必然是to_id，針對此業務場景，最好能夠再加上circle_id索引，這樣可以快速索引；這樣就得到了新的聯合索引(to_id,circle_id)的索引，然後，因為要找msg_id，為此，在此基礎上，再加上msg_id。最終得到的聯合索引為(to_id,circle_id,msg_id)；這樣的話，就能夠快速檢索這樣的查詢語句了：where to_id = xxx and circle_id = xxx and msgId >= xxx
當然，索引的建立，也不是說某個sql 語句需要啥索引，就建立某個聯合索引，這樣的話，索引太多的話，寫的效能受影響（插入、刪除、修改），然後儲存空間也會相應增大；另外mysql在執行時也會消耗資源維護索引，所以，索引並不是越多越好，需要結合查詢最頻繁、最影響效能的sql來建立合適的索引。需要再說明的是，一個聯合索引或者一組主鍵就是一個btree，多個索引就是多個btree
總結
首先我們需要深入理解索引的原理和實現，當理解了原理後，才能夠更有助於我們建立合適的索引。然後我們建立索引的時候，不要想當然，要先想清楚業務邏輯，再建立對應的表結構和索引。 需要再次強調如下幾點:
- 索引不是越多越好
- 區分主鍵和索引
- 理解索引結構原理
- 理解查詢索引規則
參考
感謝參考文章的原作者