1. 首頁 > 生活百科 > >

4 MySQL學習---MySQL索引( 五 )

生活百科

  • 數據量小的表最好不要使用索引 ， 由于數據較少，查詢花費的時間可能比遍歷索引的時間還要短 ， 索引可能不會產生優化效果 。
  • 在條件表達式中經常用到的不同值較多的列上建立索引，在不同值很少的列上不要建立索引 。比如在學生表的“性別”字段上只有“男”與“女”兩個不同值，因此就無須建立索引 。如果建立索引，不但不會提高查詢效率，反而會嚴重降低數據更新速度 。
  • 當唯一性是某種數據本身的特征時，指定唯一索引 。使用唯一索引需能確保定義的列的數據完整性，以提高查詢速度 。
  • 在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引 。
  • 搜索的索引列，不一定是所要選擇的列 。換句話說，最適合索引的列是出現在WHERE子句中的列，或連接子句中指定的列，而不是出現在SELECT關鍵字后的選擇列表中的列 。
  • 使用短索引 。如果對字符串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做 。例如，有一個CHAR(200)列 ， 如果在前10個或20個字符內，多數值是唯一的，那么就不要對整個列進行索引 。對前10個或20個字符進行索引能夠節省大量索引空間 ， 也可能會使查詢更快 。較小的索引涉及的磁盤 IO 較少，較短的值比較起來更快 。更為重要的是，對于較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL 也可以在內存中容納更多的值 。這樣就增加了找到行而不用讀取索引中較多塊的可能性 。
  • 利用最左前綴 。在創建一個n列的索引時，實際是創建了MySQL可利用的n個索引 。多列索引可起幾個索引的作用 ， 因為可利用索引中最左邊的列集來匹配行 。這樣的列集稱為最左前綴 。
  • 對于InnoDB存儲引擎的表，記錄默認會按照一定的順序保存 ， 如果有明確定義的主鍵，則按照主鍵順序保存 。如果沒有主鍵，但是有唯一索引，那么就是按照唯一索引的順序保存 。如果既沒有主鍵又沒有唯一索引，那么表中會自動生成一個內部列 ， 按照這個列的順序保存 。按照主鍵或者內部列進行的訪問是最快的 ， 所以InnoDB表盡量自己指定主鍵，當表中同時有幾個列都是唯一的 ， 都可以作為主鍵的時候，要選擇最常作為訪問條件的列作為主鍵，提高查詢的效率 。另外，還需要注意，InnoDB 表的普通索引都會保存主鍵的鍵值，所以主鍵要盡可能選擇較短的數據類型 ， 可以有效地減少索引的磁盤占用，提高索引的緩存效果
    • 索引優化前置知識：聯合索引的最左匹配原則在MySQL中，聯合索引的最左匹配原則為：最左優先，以最左邊的為起點任何連續的索引都能匹配上，同時遇到范圍查詢（>、<、between、like）就會停止匹配 。
    如果在(a,b,c)三個字段上建立聯合索引，那么它能夠加快a|(a,b)|(a,b,c)三組的查詢速度 。
    注：這里限于篇幅，就對聯合索引的最左匹配原則做一個簡要的概括 。感興趣可到MySQL學習(7)---最左匹配原則詳解查看 。
    覆蓋索引覆蓋索引指select的數據列僅從索引樹中就能獲得，不必讀取數據行 ， 不需要回表進行二次查詢 ， 也就是說，查詢列要被所使用的索引覆蓋 。
    覆蓋索引的優勢：
    • 避免了輔助索引對聚簇索引（在InnoDB引擎中對應主鍵索引）的二次查詢 。
    • 索引條目通常遠小于數據行大小，只需要讀取索引 ， 則MySQL會極大地減少數據訪問量 。
    • 輔助索引不包含整行記錄的所有信息 ， 故其大小要遠小于聚簇索引，因此可以減少大量的IO操作 。
    注：覆蓋索引技術最早是在InnoDB Plugin完成并實現 。這意味著對于InnoDB版本小于1.0的，或者MySQL數據庫版本為5.0或以下的，InnoDB存儲引擎不支持覆蓋索引特性 。
    Multi-Range Read（MRR）優化Multi-Range Read優化的目的就是為了減少磁盤的隨機訪問，并且將隨機訪問轉化為較為順序的數據訪問，這對于IO-bound類型的SQL查詢語句可帶來性能極大的提升 。Multi-Range Read優化可適用于range、ref、eq_ref類型的查詢 。
    Multi-Range Read的好處：
    • MRR使數據訪問變得較為順序 。在查詢輔助索引時，首先根據得到的查詢結果，按照主鍵進行排序，并按照主鍵排序的順序進行書簽查找 。
    • 減少緩沖池中頁被替換的次數 。
    • 批量處理對鍵值的查詢操作
    MRR的工作方式如下：

    推薦閱讀