10 分鐘 MySQL 索引從入門到精通

jopen 9年前發布 | 12K 次閱讀 MySQL 數據庫服務器

10分鐘，只要10分鐘，帶你了解 MySQL 索引的精華所在，走一走看一看，看了不吃虧，看了不上當~ :-D

1、索引是做什么的?

索引用于快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然后讀完整個表直到找出相關的行。

表越大，花費的時間越多。如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要看所有數據。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。

2、索引好復雜，我該怎么理解索引，有沒一個更形象點的例子？

有，想象一下，你面前有本詞典，數據就是書的正文內容，你的大腦就是那個cpu，而索引，則是書的目錄

3、索引越多越好？

大多數情況下索引能大幅度提高查詢效率，但：

  • 數據的變更（增刪改）都需要維護索引，因此更多的索引意味著更多的維護成本

    </li>

  • 更多的索引意味著也需要更多的空間 （一本100頁的書，卻有50頁目錄？）

    </li>

  • 過小的表，建索引可能會更慢哦 ：）  （讀個2頁的宣傳手冊，你還先去找目錄？）

    </li> </ul>

    4、索引的字段類型問題

    • text類型，也可建索引（需指定長度）

      </li>

    • myisam存儲引擎索引鍵長度綜合不能超過1000字節

      </li>

    • 用來篩選的值盡量保持和索引列同樣的數據類型

      </li> </ul>

      5、like 不能用索引？

      • 盡量減少like，但不是絕對不可用，”xxxx%” 是可以用到索引的，

        想象一下，你在看一本成語詞典，目錄是按成語拼音順序建立，查詢需求是，你想找以 “一”字開頭的成語（”一%“），和你想找包含一字的成語（“%一%”）

        </li>

      • 除了like，以下操作符也可用到索引：

        <，<=，=，>，>=，BETWEEN，IN

        <>，not in ，！=則不行

        </li> </ul>

        6、什么樣的字段不適合建索引？

        • 一般來說，列的值唯一性太小（如性別，類型什么的），不適合建索引（怎樣叫太小？一般說來，同值的數據超過表的百分之15，那就沒必要建索引了）

          </li>

        • 太長的列，可以選擇只建立部分索引，（如：只取前十位做索引）

          </li>

        • 更新非常頻繁的數據不適宜建索引（怎樣叫非常？意會）

          </li> </ul>

          7、一次查詢能用多個索引嗎?

          不能。

          比如：

          我有一個Ａ表，有ｂ,ｃ,ｄ字段，其中在ｂ,ｃ上各自加了索引。（可以使用ＳＱＬ：SHOW INDEX from A 查看Ａ表的索引）

          ?

          1
          </td>

          select * from A WHERE b=x and c=x; --x表示要查詢的值
          </div> </td> </tr> </tbody> </table> </div> </div>

          通過這條SQL，我們可以看到有兩列：possible_keys和key

          pk表示此條ＳＱＬ語句可能用到的索引，ｋ表示實際用到的索引。

          你會發現，實際只用到了一個索引，至于是b還是ｃ，mysql會通過分析選擇使用給出結果集小的索引（cardinality），如通過ｂ索引查詢的記錄比較少, 則會使用b作為實際索引。

          注：嵌套、關聯查詢 explain 下可以看做多次查詢，而每次獨立的查詢都可以有單獨的索引被使用。

          8、多列查詢該如何建索引?

          一次查詢只能用到一個索引，所以 首先槍斃 a，b各建索引方案

          a還是b？ 誰的區分度更高（同值的最少），建誰！

          當然，聯合索引也是個不錯的方案，ab，還是ba，則同上，區分度高者，在前

          9、聯合索引的問題?

          where a = “xxx” 可以使用 AB 聯合索引

          where b = “xxx” 則不可 （再想象一下，這是書的目錄？）

          所以，大多數情況下，有AB索引了，就可以不用再去建一個A索引了

          10、哪些常見情況不能用索引?

          • like “%xxx”

            </li>

          • not in ， ！=

            </li>

          • 對列進行函數運算的情況（如 where md5(password) = “xxxx”）

            </li>

          • WHERE index=1 OR A=10

            </li>

          • 存了數值的字符串類型字段（如手機號），查詢時記得不要丟掉值的引號，否則無法用到該字段相關索引，反之則沒關系

            也即

            select * from test where mobile = 13711112222;

            可是無法用到mobile字段的索引的哦（如果mobile是char 或 varchar類型的話）

            btw，千萬不要嘗試用int來存手機號（為什么？自己想！要不自己試試）

            </li> </ul>

            11、覆蓋索引(Covering Indexes)擁有更高效率

            索引包含了所需的全部值的話，就只select 他們，換言之，只select 需要用到的字段，如無必要，盡量避免select *

            12、NULL 的問題

            • 要盡量避免 NULL，盡可能地把字段定義為 NOT NULL。即使應用程序無須保存 NULL（沒有值），也有許多表包含了可空列（Nullable Column）,這僅僅是因為它為默認選項。除非真的要保存 NULL，否則就把列定義為 NOT NULL。 

              </li>

            • MySQL難以優化引用了可空列的查詢，它會使索引、索引統計和值更加復雜。可空列需要更多的儲存空間，還需要在MySQL內部進行特殊處理。當可空列被索引的時候，每條記錄都需要一個額外的字節，還可能導致 MyISAM 中固定大小的索引(例如一個整數列上的索引)變成可變大小的索引。 

              </li>

            • 即使要在表中儲存「沒有值」的字段，還是有可能不使用 NULL 的。考慮使用 0、特殊值或空字符串來代替它。 

              </li>

            • 把 NULL 列改為 NOT NULL 帶來的性能提升很小，所以除非確定它引入了問題，否則就不要把它當作優先的優化措施。然而，如果計劃對列進行索引，就要盡量避免把它設置為可空。 

              </li> </ul>

              13、如何查看索引信息，如何分析是否正確用到索引?

              show index from tablename;

              explain select ……;

              關于explain，改天可以找個時間專門寫一篇入門帖，在此之前，可以嘗試 google

              14、了解自己的系統，不要過早優化!

              過早優化，一直是個非常討厭而又時刻存在的問題，大多數時候就是因為不了解自己的系統，不知道自己系統真正的承載能力

              比如：幾千條數據的新聞表，每天幾百幾千次的正文搜索，大多數時候我們可以放心的去like，而不要又去建一套全文搜索什么的，畢竟cpu還是比人腦厲害太多。

              15、后記：

              看到這兒，也許你花了不止10分鐘，但建議多看幾遍，有條件的話最好上機嘗試加深理解，紙上得來終覺淺，絕知此事要躬行嘛~

              還有這篇文章幾乎覆蓋了《高性能MYSQL》中索引一章的所有結論，建議和那本書參照起來一起看，也許那本書就不那么難讀了~

              Refer：

              [1] mysql索引需要了解的幾個注意

              http://thephper.com/?p=142

              [2] mysql 查詢可以同時使用幾條索引

              http://dwz.cn/2cJuCH

              [3] MySQL 索引詳解

              http://my.oschina.net/leejun2005/blog/134932?fromerr=DQVDBPHK

              [4] 關于mysql 索引自動優化機制: 索引選擇性（Cardinality：索引基數）

              http://my.oschina.net/leejun2005/blog/75688

              [5] 如何應對并發(1) - 關于數據索引

              http://dwz.cn/2cJwCQ

              [6] 如何應對并發(2) - 請求合并及異步處理

              http://dwz.cn/2cJxgS


              來自：http://my.oschina.net/leejun2005/blog/532956

           本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
           轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
           本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

          相關經驗

          相關資訊

          相關文檔

sesese色