一、不合理的索引设计 例:表record有620000行,试看在不同的索引下,下面几个 SQL的运行情况: 1.在date上建有一非个群集索引 0select count(*) from record where date > ′19991201′ and date < ′19991214′and amount > 2000 (25秒) 0select date,sum(amount) from record group by date (55秒) 0select count(*) from record where date > ′19990901′ and place in (′BJ′,′SH′) (27秒)
2.在date上的一个群集索引 0select count(*) from record where date > ′19991201′ and date < ′19991214′ and amount > 2000 (14秒) 0select date,sum(amount) from record group by date (28秒) 0select count(*) from record where date > ′19990901′ and place in (′BJ′,′SH′)(14秒)
3.在place,date,amount上的组合索引 0select count(*) from record where date > ′19991201′ and date < ′19991214′ and amount > 2000 (26秒) 0select date,sum(amount) from record group by date (27秒) 0select count(*) from record where date > ′19990901′ and place in (′BJ, ′SH′)(< 1秒)
0select count(*) from record where date > ′19991201′ and date < ′19991214′ and amount > 2000(< 1秒) 0select date,sum(amount) from record group by date (11秒) 0select count(*) from record where date > ′19990901′ and place in (′BJ′,′SH′)(< 1秒)
①.有大量重复值、且经常有范围查询(between, >,< ,>=,< =)和order by 、group by发生的列,可考虑建立群集索引;
②.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
③.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。
二、不充份的连接条件: 例:表card有7896行,在card_no上有一个非聚集索引,表account有191122行,在 account_no上有一个非聚集索引,试看在不同的表连接条件下,两个SQL的执行情况: 0select sum(a.amount) from account a, card b where a.card_no = b.card_no(20秒)
将SQL改为: 0select sum(a.amount) from account a, card b where a.card_no = b.card_no and a. account_no=b.account_no(< 1秒)