關於mysql 索引自動最佳化機制: 索引選擇性（Cardinality：索引基數）

標籤：數值   自動   des   key   nbsp   問題   原因   target   分析   

1、兩個同樣結構的語句一個沒有用到索引的問題：查1到20號的就不用索引，查1到5號的就用索引，為什麼呢？不穩定？ mysql> explain select * from test where f_submit_time between ‘2009-09-01‘ and ‘2009-09-20‘ \G; *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test         type: ALLpossible_keys: PRIMARY,submit_time_index          key: NULL      key_len: NULL          ref: NULL         rows: 365628        Extra: Using where1 row in set (0.02 sec)  mysql> explain select * from test where f_submit_time between ‘2009-09-01‘ and ‘2009-09-5‘ \G;  *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test         type: rangepossible_keys: PRIMARY,submit_time_index          key: submit_time_index      key_len: 8          ref: NULL         rows: 52073        Extra: Using where1 row in set (0.00 sec)  說明：二叉樹索引本來最適合的就是點查詢，和小範圍的range查詢，當預估返回的資料量超過一定比例( 貌似當預估的查詢量達到總量的30% )的時候，再根據索引一條一條去查就慢了，反而不如全表掃描快了。Mysql有自己內部自動最佳化機制，但有些自動最佳化機制可能不是最優的。這時候就需要人工去幹預。比如長期不最佳化表，Mysql判斷出索引不優，就會不使用索引。有時候就要人工強制使用真正高效的索引(FORCE INDEX)。 

其實當本身的查詢就約等於一個全表查詢的時候，強不強制使用索引基本上沒什麼效果。

2、再看個例子：

    今天遇到一個奇怪的問題，明明已經建立了索引，select語句的explain也表明會利用這個索引，可是結果偏偏沒有用索引，最後掃描了全表。
    兩個結構完全一樣的sql語句：

     sql1: select * from table where col_a = 123 and col_b in (‘foo’,\‘bar’) order by id desc;

    sql2: select * from table where col_a = 456 and col_b in (‘foo’,\‘bar’) order by id desc;

    結果sql1選擇利用了col_a的索引，速度很快，sql2利用了主鍵ID的索引，掃描了全表(40w行)。
    仔細分析，探索資料庫中，col_a=456的記錄數有近1萬條，而col_a=123的記錄數只有幾條。
    於是就清楚了，MySQL選擇索引不僅僅依據查詢結構和索引結構，還會根據索引大概估算選擇每種索引的資料量，然後選擇他認為最快的索引。
    可能是主鍵索引會比普通index更快，所以mysql最後選擇了資料量跟大的id索引。
    那麼，如何解決這個問題呢？
     很簡單，只要在order語句裡寫多個鍵即可，比如：order by col_a, id desc

REF：mysql查詢中利用索引的機制  http://blogread.cn/it/article/5023?f=wb

3、本質原因：Cardinality（索引基數）

很關鍵的一個參數，平均數值組=索引基數/表總資料行，平均數值組越接近1就越有可能利用索引。

索引選擇性是不重複的索引值也叫基數（cardinality）表中資料行數的比值，索引選擇性=基數/資料行，基數可以通過“show index from 表名”查看。   
高索引選擇性的好處就是mysql尋找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1。

4、關於 mysql 索引最佳化與使用請見：

由淺入深探究mysql索引結構原理、效能分析與最佳化

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

關於mysql 索引自動最佳化機制: 索引選擇性（Cardinality：索引基數）