Re: [SQL ] mysql date column index 問題
※ 引述《kaiyuegg (蛋頭)》之銘言：
: 大家好~!
: MYSQL VER.5.0.51b-community-nt-log
: 我最近在研究系統效能部分(有關於 mysql date index 沒反應)...
: 遇到了一些問題!
: 我一般在處理日期部分 都是直接這樣下的
: select * from `salary` where DATE < '2013-09-15'
: 但有時候筆數多的時候就算DATE 設 index
: mysql explain 的type 也是 all (索引沒發揮作用)
: 這樣效能很差 所以在找方法讓它變快。
: 如果設
: select * from salary where DATE between '2013-05-10' and '2014-09-29'
: explain 出來 type 才會變成 range
: (索引有發生作用 但是好像要設到資料裡面的上限跟下限裡面才有作用)
: 如果資料裡面包含 0000-00-00 想要抓不包含0000-00-00的資料想要這樣下
: select * from salary where DATE between '1970-01-01 and '2050-12-31'
: 這時候explain 出來 就變成all 了(索引就沒發生作用了)
: 下 != or <> 0000-00-00 也會變成all
: 結論
: 1.想請問大家 mysql 在日期欄位上面 index 的正確用法?
: 2.如上面所述 我想要抓不是0000-00-00 的資料 用什麼方法可以讓index 產生效用?
: 可能說的不是很清楚...但就是date 在 mysql 的索引好像不是那麼友善?
: 請大大指點迷津 或是提示一下 我不清楚的部分 tks
首先你要知道 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 就有開銷就是要花工夫啊
你只能避免它繞冤枉路 該走的路還是免不了的
----
會看執行計畫之後的下一步就是算執行成本 加油啊~
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 122.118.6.179
※ jeamie:轉錄至某隱形看板 09/17 22:51
※ 編輯: iFEELing 來自: 122.118.6.179 (09/17 23:02)
推
09/19 13:08, , 1F
09/19 13:08, 1F
→
09/19 13:08, , 2F
09/19 13:08, 2F
→
09/19 13:09, , 3F
09/19 13:09, 3F
→
09/19 13:09, , 4F
09/19 13:09, 4F
→
09/19 13:10, , 5F
09/19 13:10, 5F
→
09/19 13:10, , 6F
09/19 13:10, 6F
→
09/19 13:11, , 7F
09/19 13:11, 7F
→
09/19 15:10, , 8F
09/19 15:10, 8F
討論串 (同標題文章)