※ 引述《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)