数据库索引问题

遇到的问题前提如下：

一张包含四个字段的表，表名为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上建立的索引属于聚集索引（聚集索引就好像按照汉语拼音的索引方式查找汉语字典中的字）。在实际应用中，聚集索引可以高效的提高查询速度，而非聚集索引则不具有这个特性。

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