数据库索引问题

 

遇到的问题前提如下：

一张包含四个字段的表，表名为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() {try {Statement st = conn.createStatement();String sql = "drop index test_col1 on test;";st.execute(sql);st.close();System.out.println("col1Index has been deleted");System.out.println();} catch (Exception e) {e.printStackTrace();}}public static void query2() {try {System.out.println("start query2: 'select * from test where col2 > /'ab/' and col2 </'ac/';'");Long beginTime = System.currentTimeMillis();Statement st = conn.createStatement();String sql = "select * from test where col2 > /'ab/' and col2 </'ac/';";ResultSet rs = st.executeQuery(sql);Long endTime = System.currentTimeMillis();// System.out.println("result:");// while (rs.next()) {// System.out.print(rs.getInt(1)+" ");// System.out.print(rs.getString(2)+" ");// System.out.print(rs.getString(3)+" ");// System.out.println(rs.getInt(4));// }System.out.println("query2 time: " + (double) (endTime - beginTime)/ 1000 + " s");System.out.println();st.close();} catch (Exception e) {e.printStackTrace();}}

 

得到高人指点，将该疑惑解决了。

 

我尝试着在插入col2的数据时，有规律的插入数据，因为总行数为100万行，数据范围从aaaaa到eeeee一共有3125种，可以按照顺序将每种数据插入320行，这样整体col2的数据就是根据字典序进行排序的。同样建立索引以后查询，用时在0.2s左右。终于恍然大悟。

 

从网上看到相关资料，了解到当col2是随机值时，在col2上建立的索引属于非聚集索引（非聚集索引就好像按照偏旁部首的索引方式查找汉语字典中的字），而当col2是按照一定的顺序存放时，在col2上建立的索引属于聚集索引（聚集索引就好像按照汉语拼音的索引方式查找汉语字典中的字）。在实际应用中，聚集索引可以高效的提高查询速度，而非聚集索引则不具有这个特性。

 

具体的原因可能是这样的，对于聚集数据，当用户查询时，通过索引查找到数据在磁盘中的位置，因为其的聚集特性，可以从这个位置开始顺序的读取大量的符合要求的信息。就好像你以汉语拼音为索引查找一个读音的页码以后，可以找到大量的这个读音的字。但是非聚集则不然，非聚集索引查询到的结果分布在磁盘的各个地方，需要频繁的移动磁头才能够获取这些信息，就好像你以偏旁部首为索引查找一个偏旁部首的字时，你要频繁的翻动书页才能找到所有的符合要求的字。