萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql數據庫中索引的利弊分析
mysql數據庫中索引的利弊分析
索引，可以說是數據庫相關優化尤其是在Query 優化中最常用的優化手段之一了，雖然如此，但索引也並非是解決query優化的聖經，不能只要發現Query 運行不夠快就將where子句中的條件全部放在索引中。什麼事都是適而可止，過猶不及，一起來看過究竟吧。
索引的利處
索引能夠提高數據檢索的效率，降低數據庫的IO 成本和排序成本。在數據庫中個表的某個字段創建索引，所帶來的最大益處就是將該字段作為檢索條件的時候 可以極大的提高檢索效率，加快檢索時間，降低檢索過程中所需要讀取的數據量。
索引不是多多益善
索引能夠極大的提高數據檢索效率，也能夠改善排序分組操作的性能，但是我們不能忽略的 一個問題就是索引是完全獨立於基礎數據之外的一部分數據。假設我們在Table ta 中的Column ca 創 建了索引idx_ta_ca，那麼任何更新Column ca 的操作，MySQL 都需要在更新表中Column ca 的同時， 也更新Column ca 的索引數據，調整因為更新所帶來鍵值變化後的索引信息。而如果我們沒有對 Column ca 進行索引的話,MySQL 所需要做的僅僅只是更新表中Column ca 的信息。這樣，所帶來的最 明顯的資源消耗就是增加了更新所帶來的IO 量和調整索引所致的計算量。此外，Column ca 的索引 idx_ta_ca 是需要占用存儲空間的，而且隨著Table ta 數據量的增長，idx_ta_ca 所占用的空間也會 不斷增長。所以索引還會帶來存儲空間資源消耗的增長。
如何判定是否需要創建索引
較頻繁的作為查詢條件的字段應該創建索引；
索引正是我們減少通過索引鍵字段作為查詢條件的Query的IO 量的最有效手段。
唯一性太差的字段不適合單獨創建索引，即使頻繁作為查詢條件；
唯一性太差的字段,如狀態字段，類型字段等。因為mysql首先會將索引中的鍵值取出來與內存中存儲表數據的頁中的數據相比較，但是數據頁中的數據的順序和索引隊列中鍵值的順序並不是一致的。假如索引中的鍵值a先在數據頁x中找到了符合的數據，然後又在數據頁y中找到了符合條件的數據，這時mysql便會把數據頁x銷毀掉，把數據頁Y讀到內存中。如果這時候還有鍵值b，然後鍵值b找的數據又在數據頁x上，則mysql又要把數據頁x讀到內存中。也就是說從索引去尋找對應的表數據的時候是隨機訪問的。（實際情況應該是內存中緩存了好幾頁的數據，應該不只一頁，但是這裡假定線程內存中只存在一張頁表）。這樣的隨機訪問所造成的io消耗是比全表掃描的io消耗來得大的。（還不如遍歷整張表）
假如索引字段唯一性好的話，比如是唯一的，則最多只需要換一次頁表。
假如索引字段唯一性差的話，需要進行的換頁次數也就相應的提高了。
更新非常頻繁的字段不適合創建索引；
索引中的字段被更新的時候，不僅僅需要更新表中的 數據，同時還要更新索引數據，以確保索引信息是准確的。這個問題所帶來的是IO 訪問量的較大 增加，不僅僅影響更新Query 的響應時間，還會影響整個存儲系統的資源消耗，加大整個存儲系統 的負載。當然，並不是存在更新的字段就不適合創建索引，從上面判定策略的用語上面也可以看出，是 “非常頻繁”的字段。
不會出現在WHERE 子句中的字段不該創建索引；
索引給Query優化帶來好處，但我們也不忽略它的憋端，可以說是利憋參半，需要根據實際應用把握好適度。
下面我們就以MyISAM 來談索引。
索引是在存儲引擎層實現的，而不是服務器層。因此，它們並不是標准化的，每個引擎的索引工作方式略有不同。即使多個引擎支持同樣的索引，它們的實現方式也可能有所不同。下面我們針對MyISAM存儲引擎談談最頻繁使用的B-Tree索引類型。
（這種索引在 Innodb 存儲引擎中被稱為 Secondary Index，後面稍加介紹）
•B-Tree定義
1970年，R.Bayer和E.mccreight提出了一種適用於外查找的樹，它是一種平衡的多叉樹，稱為B樹。
主鍵索引和非主鍵索引除了葉子結點上存儲的分別是PK value 和普通key value之外，結構完全一致。
•我們可以創建的一些索引類型。（MyISAM）
1) 主鍵索引（PRIMARY KEY）
每張表只有一個，保證數據記錄集的唯一性。
2)唯一性索引
主鍵索引與唯一索引的唯一區別是：前者在定義時使用的關鍵字是PRIMARY KEY而不是UNIQUE。
3)普通索引（由關鍵字KEY或INDEX定義的索引）
4)全文索引（innodb不支持）（FULLTEXT）
可以在 CHAR、VARCHAR 或 TEXT 列上創建
5)多列索引（或復合索引）
根據查詢的需求設置，可以完成多條件查詢，尤其大數據量查詢時可以大大提升查詢速度。
•淺析索引優化及SQL優化
我們來看一張示意圖，只是表示關系圖，請注意結點上寫上數據並不是代表數據實際存儲位置，只是為了更直觀形象。
看圖說話及提問：
•圖上建立了（uid,catid,day）的復合索引
•在表建立後，有了數據，MYSQL會按著你建立的索引對應數據去生成上面的結構，每一個根到葉子到根的距離都是一樣的，是有序的。
•從上圖可以看出如果你的uid是定值（=），catid是定值（=），day（=）是定值，是最理想狀態，完全用到索引
•如果你的uid是定值，catid是定值，day是范圍，也可以完全用到
•uid定值，catid范圍，day是范圍，則可以用到uid和catid，因為catid您使用了范圍，後面的day就用不到啦，因為排好的樹不能滿足您的查詢需求啦。
•？1—-如果uid定值，catid范圍，day范圍，那可以用到哪個索引，為什麼呢？
•？2—-如果表中的數據更新或是新增，那麼想問，上述排好序的索引會不會改變呢？
能使用B-Tree索引的查詢
•匹配全名
•匹配最左前綴
•匹配范圍值
•精確匹配一部分並且匹配某個范圍中的另一部分。
•只訪問索引的查詢
局限性
•如果查找沒有從索引列的最左邊開始，它就沒什麼用處。
•不能跳過索引中的列。
•存儲引擎不能優化訪問任何在第一個范圍條件右邊的列。
我們來詳細說明一些我們在寫SQL時或使用索引經常會模糊的地方及要遵守的約定。
最完美的您設定的索引完全使用，而非全表掃描。
1.索引的順序規范。
注：建立索引時的順序非常關鍵，如果是復合索引，就是多列的，請一定要仔細分析業務查詢，將限定查詢的字段放在最前面，最常用必用的放在最前面，范圍查詢的字段盡量後靠。
因為索引是不能跳過索引中的列，所以一個不常用的或范圍列放在前面會導致後面的索引字段無法使用。
2.索引的順序和where條件中字段的順序請盡量保持一致
通常mysql會調整自己where語句中查詢條件字段的順序，而不是以查詢字段的順序為准，它是進行查詢優化，去選擇合適的索引。
目前我的mysql 5.0.45或77中會根據索引情況去選擇合適的索引使用，所以where條件用的順序與索引並不完全相關，
但之前有些版本要求兩者一致，所以我們盡量保持一致，這樣符合我們業務及可讀性，同樣避免一些版本處理不同的問題。
當然您可以通過測試來看看執行計劃進行索引分析。
3.不是索引越多越好。
索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。（上面的問題2應該知道答案了吧）
4.盡量避免NULL（根據實際情況慎重選擇default NULL）
應該指定列為NOT NULL，除非你想存儲NULL。www.111cn.net在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值。
5.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
如select a from t where a-5>100;
如果a上有索引則用不到，會進行全表掃描；
Select a from t where a>105;
則可以用上索引啦。
6.如若對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
7.請避免使用select * 去查詢，請列出你所實際要詢的字段列，不要返回任何你用不到的字段，慷慨不是好事。
說一個強制使用某索引的方法：
EXPLAIN SELECT uid
FROM tablename
FORCE INDEX ( uid_cid )
WHERE uid = 1
AND cid > 1
（自己試試吧，呵呵！）好啦，目前先說這些，都是一些歸納總結，主要就是先鋪個概念，大家了解一下存儲引擎，支持的索引類型，以及實際建立及使用時的注意事項。
後續如果有時間的話，會介紹查看執行計劃的explain工具，以及mysql 中show 命令，你可以想show 什麼就show 什麼。或者是建立表時“set utf8 collate utf8_bin NOT NULL default ””這麼一行有什麼作用，呵呵，後續探討。
後續也會討論一下InnoDB中的的B+Tree索引，聚集索引等相關方面的內容，慢慢來呵呵！
