資料庫索引問題

 

遇到的問題前提如下：

一張包含四個欄位的表，表名為test，表中有100萬條記錄。

        第一列為id，主鍵，自增。

        第二列為col1，隨機為Mike，Bob，Jack，Alice，Cathy，Ann，Betty，Cindy，Mary，Jane中的一個

        第三列為col2，隨機為一個5位字母，字母限制在a-e

        第三列為col3，隨機為一個1-20之間的整數

 

(1)select count(*) from test group by col1 order by count(*);記錄執行時間

(2)找出所有第二列以ab開頭的記錄，記錄執行時間

 

對於第一題建立col1上的索引以後查詢時間縮短了一半。而第二題建立在col2上的索引發現時間由原來的0.6s增加到7s。

我的環境是java語言和mysql資料庫，使用jdbc串連。

下面是第二題添加索引代碼和查詢的代碼

public static void dropCol1Index() {<br />try {<br />Statement st = conn.createStatement();<br />String sql = "drop index test_col1 on test;";<br />st.execute(sql);<br />st.close();<br />System.out.println("col1Index has been deleted");<br />System.out.println();<br />} catch (Exception e) {<br />e.printStackTrace();<br />}<br />}<br />public static void query2() {<br />try {<br />System.out<br />.println("start query2: 'select * from test where col2 > /'ab/' and col2 </'ac/';'");<br />Long beginTime = System.currentTimeMillis();<br />Statement st = conn.createStatement();<br />String sql = "select * from test where col2 > /'ab/' and col2 </'ac/';";<br />ResultSet rs = st.executeQuery(sql);<br />Long endTime = System.currentTimeMillis();<br />// System.out.println("result:");<br />// while (rs.next()) {<br />// System.out.print(rs.getInt(1)+" ");<br />// System.out.print(rs.getString(2)+" ");<br />// System.out.print(rs.getString(3)+" ");<br />// System.out.println(rs.getInt(4));<br />// }<br />System.out.println("query2 time: " + (double) (endTime - beginTime)<br />/ 1000 + " s");<br />System.out.println();<br />st.close();<br />} catch (Exception e) {<br />e.printStackTrace();<br />}<br />}

 

得到高人指點，將該疑惑解決了。

 

我嘗試著在插入col2的資料時，有規律的插入資料，因為總行數為100萬行，資料範圍從aaaaa到eeeee一共有3125種，可以按照順序將每種資料插入320行，這樣整體col2的資料就是根據字典序進行排序的。同樣建立索引以後查詢，用時在0.2s左右。終於恍然大悟。

 

從網上看到相關資料，瞭解到當col2是隨機值時，在col2上建立的索引屬於非叢集索引（非叢集索引就好像按照偏旁部首的索引方式尋找漢語字典中的字），而當col2是按照一定的順序存放時，在col2上建立的索引屬於叢集索引（叢集索引就好像按照漢語拼音的索引方式尋找漢語字典中的字）。在實際應用中，叢集索引可以高效的提高查詢速度，而非叢集索引則不具有這個特性。

 

具體的原因可能是這樣的，對於聚集資料，當使用者查詢時，通過索引尋找到資料在磁碟中的位置，因為其的聚集特性，可以從這個位置開始順序的讀取大量的符合要求的資訊。就好像你以漢語拼音為索引尋找一個讀音的頁碼以後，可以找到大量的這個讀音的字。但是非聚集則不然，非叢集索引查詢到的結果分布在磁碟的各個地方，需要頻繁的移動磁頭才能夠擷取這些資訊，就好像你以偏旁部首為索引尋找一個偏旁部首的字時，你要頻繁的翻動書頁才能找到所有的符合要求的字。