我们从复合索引(一)的内容中知道了复合索引的好处,但是复合索引的多个索引列会增加索引列的长度,导致索引层次过高。所以,在创建复合索引时,需要考虑记录量非常大时,索引树的高度会由于复合索引的多个列变高,从而导致索引性能下降的情况。故在某些特定的情况下,还需要考虑是否创建为单一索引,避免复合索引层数过高带来的性能影响。下面,我们通过某业务系统的实例来理解如何选择单一索引和复合索引。
表结构如下,包含近百个字段,这里其他字段省略。该表有超过6亿条的记录:
create table epr_patient_record(
patient_key char(8) not null ,
sub_category1 varchar(100),
entity_id integer,
object_rdn char(8),
... );
表在优化前包含如下复合索引:
create index idx_record on epr_patient_record(patient_key,sub_category1,entity_id);
在业务系统中有如下SQL语句,每天执行上万次:
select * from epr_patient_record where patient_key=? and sub_category1=? and entity_id=?;
该SQL语句可以正确地使用到在上面创建的复合索引(patient_key、sub_category1、entity_id),由于表的记录数非常大,所以该SQL需要运行1.10秒。
优化分析如下:
(1)索引的层次及占用空间。通过查询GBase8s系统表发现索引idx_record占用24GB的空间,索引层次为6层。
(2)索引字段区分度。通过查询,字段patient_key的不同值为4225352,而sub_category1为10832,entity_id仅为1029。
(3)分析如下两个查询语句的返回结果,第一个SQL返回160条记录,第二个SQL返回150条记录:
select count(*) from epr_patient_record where patient_keyn = 'ACD2012';
select count(*) from epr_patient_record where patient_keyn = 'ACD2012' and sub_category1 = '12kuyk' and entity_id = 1231;
优化措施如下:
通过上述分析可知复合索引idx_record的效率并不好,索引层次过高且占用过大的存储空间。
SQL语句中最重要的一个过滤条件patient_key基本决定了查询速度,而该案例创建的复合索引后面的两个字段只起到了很小的作用,从上面两个SQL语句运行的结果可以看出,后面的两个条件只是让结果集从160条减少到150条。而利用如上复合索引idx_record进行查询时,首先需要从磁盘读取索引的数据,然后进行高层次索引的搜索,再读取记录。在此过程中,索引处理过程消耗了过多的时间。
创建一个单索引取代复合索引:
create index idx_record_s on epr_patient_record(patient_key);
该索引的占用空间仅为10GB,索引层次也从6层减少为5层。
在此测试如下查询语句的效率,只需消耗0.8秒:
select * from epr_patient_record where patient_key=? and sub_category1=? and entity_id=?;
使用单一索引idx_record_s时,数据库首先通过索引查找到满足条件的160条记录,然后再通过后面两个条件过滤为150条,在查询过程中,索引可以快速地从6亿记录中找到160条记录。
因此,在使用复合索引时,需要考虑索引占用空间、索引层次,特别是字段的区分度。否则会出现复合索引性能比单一索引性能低的情况。