date在查詢大量資料沒吃到index

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

首先你要知道 INDEX 是另外存的 也佔空間 也吃 FILE IO
也就是說 透過 INDEX 其實不一定比較快
例如你有一個 TABLE 總共佔了 10000 個 block
然後建了一個 index , 這個 index 本身佔了 2000 個 block
假設你今天要找的資料 在整個 table 的 10000 個 blocks 裡，
散布在其中的 9000 個 blocks 裡面。

這時候  你透過 index 找資料的 IO 成本就會是 2000 + 9000 = 11000
而 Full Table Scan 的 IO 成本只有 10000
像這樣的時候， DBMS 就會選擇直接做 Table Scan 因為成本比較低。
那 DBMS 怎麼知道哪條路成本比較低呢
就來自它本身對這些 表格/索引 的統計值

以 Oracle 舉例  跟這樣行為有關的參數就包括有
1. 最佳化器嘗試組出最佳路徑的嘗試次數上限
2. 最佳化器判斷 走 index 的成本 大於 Full Table Scan 成本的多少比例以上
   就走 Full Scan
3. 最佳化器優化的方向(最小IO , 最快回應等
回到你提的問題
從你下的參數中我們可以注意到幾件事


1. 在你指定的範圍內資料量較小的時候 DBMS 會覺得該走 index

2. 在你指定的範圍內資料量較大的時候 DBMS 選擇走 FULL SCAN

3. 你下了 select * 這代表所有不在INDEX上的欄位，DBMS都必須要翻
   DATA BLOCKS出來才能拿到資料

但是在你的資料中  0000-00-00  的資料量到底有多少？

如果只有很小一點點 (或是 DBMS 覺得它只佔一點點

那麼使用 FULL SCAN 是很正常的。

反正你都得翻這些資料出來，何必脫褲子放屁去 index blocks 再繞一圈呢？


但是如果 這樣的資料是佔多數的

那或許是你的表格/索引的統計值出問題了。導致 DBMS 覺得這樣比較快


* 有另一個狀況 就是  DBMS 在處理 function(col) 這些操作的時候
  因為存在 index 裡的資料是 col 而非 function(col)
  所以 DBMS 會覺得這個 index 是與需求不符的 會放棄

** 然後 資料量大的話 會慢是正常的啊 有吃 IO 吃CPU 就有開銷就是要花工夫啊
   你只能避免它繞冤枉路 該走的路還是免不了的

1

select count(*) from http_log_3 force index(time) where time >= 000000