当你接手一个系统时，通常是从检查系统负载，cpu、内存使用率开始，查看statspack top5等待事件，逻辑读、物理读排名靠前的sql语句等等，然后进行初步的优化。而随着业务的深入了解，你开始从系统的角度去考虑据库设计，考虑应用实现的合理性，是否有更好的改进方案等。假设通过statspack报表找到了很耗资源的sql，表分析过，执行计划也是走索引，这种情况下怎么去判断 sql是优化的呢？

    1.提取逻辑读排名靠前的sql

     6,813,699 336 20,278.9 10.1 66.72 80.45 3039661161

    module: java@admin1 (tns v1-v3)

     select b.biz_source, count(*) as counts from tb_hanzgs_de

    tail a, tb_business_info b where a.id = b.hanzgs_id

    and a.status = :1 and a.deal_id = :2 and a.create_date

    >= to_date(:3,\'yyyy-mm-dd hh24:mi:ss\') and a.create_date < to

    _date(:4,\'yyyy-mm-dd hh24:mi:ss\') group by b.biz_source

    2.查看执行计划

    sql> explain plan for

     2 select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = :1

     6 and a.deal_id = :2

     7 and a.create_date >= to_date(:3, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(:4, \'yyyy-mm-dd hh24:mi:ss\')

     9 group by b.biz_source;

    Explained.

    SQL> @?/rdbms/admin/utlxpls

    Plan hash value: 1387434542

    -----------------------------------------------------------------------

    id | operation | name | rows | bytes | cost |

    -----------------------------------------------------------------------

     0 | select statement | | 1 | 31 |215

     1 | sort group by | | 1 | 31 |215

     2 | filter | | | |

     3 | nested loops | | 1 | 31 |199

     4 | table access by index rowid| tb_hanzgs_detail | 1| 21 |198

     5 | index range scan | ind_tb_hanzgs_create | 231| |397

     6 | index range scan | ind_tb_business_info_biz | 1| 10 |1

    -----------------------------------------------------------------------

    索引定义

    create index ind_tb_hanzgs_create on tb_hanzgs_detail (create_date, deal_id,status, id)tablespace tbs_tb_ind online compute statistics;

    3.查看语句执行时间

    sql>select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = 1

     6 and a.deal_id = 0

     7 and a.create_date >= to_date(sysdate-10, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(sysdate-5, \'yyyy-mm-dd hh24:mi:ss\')

     9 group by b.biz_source;

    biz_source counts

    ---------- ----------

     102 712

     501 7881

     701 1465

    3 rows selected.

    elapsed: 00:00:17.03

    sql> /

    biz_source counts

    ---------- ----------

     102 713

     501 7882

     701 1465

    3 rows selected.

    elapsed: 00:00:05.32

    这个语句查询时间在5.3秒左右，对于查询频繁的oltp系统中，毫无疑问全表扫描的代价是最高的，按时间索引扫描数据效率也是很低的，毕竟一个时间段的数据也是不少的。考虑到上面sql正好使用时间列索引，如果status，rule_id列稀疏读很高的话，这些列建立索引性能应该会有很大的提高。

    4.查看表数据分布

    sql> select status, count(*) as counts

     2 from tb_hanzgs_detail

     3 where create_date >= to_date(sysdate - 50, \'yyyy-mm-dd hh24:mi:ss\')

     4 and create_date < to_date(sysdate - 49, \'yyyy-mm-dd hh24:mi:ss\') + 1

     5 group by status;

     status counts

    --------- ----------

     0 2

     1 286

     2 3567

     3 123477

    根据随机抽取几天数据分布结构，这个表中97%以上数据的status都等于3，如果status为3的查询应该会走全表扫描，消耗大量资源，查询频繁的话DBA是不允许开发部署到生产系统的，数据库压力太大。而status不等于3的数据量很少很少，以status列来建立索引，性能应该会有很大的提高。分析完就可以尝试的进行优化了。

    5.重新建立索引

    create index ind_tb_hanzgs_de_sta on tb_hanzgs_detail (status, deal_id, create_date,id)tablespace tbs_tb_ind online compute statistics;

    新执行计划

    sql> explain plan for

     2 select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = :1

     6 and a.deal_id = :2

     7 and a.create_date >= to_date(:3, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(:4, \'yyyy-mm-dd hh24:mi:ss\') + 1

     9 group by b.biz_source;

    explained.

    elapsed: 00:00:00.01

    14:11:48 sql> @?/rdbms/admin/utlxpls

    ------------------------------------------------------------------

    id | operation | name | rows | bytes | cost |

    ------------------------------------------------------------------

     0 | select statement | |1 |31 |19 |

     1 | sort group by | |1 |31 |19 |

     2 | filter | | | | |

     3 | nested loops | |1 |31 | 3 |

     4 | index range scan | ind_tb_hanzgs_de_sta |1 |21 | 4 |

     5 | index range scan | ind_tb_business_info_biz |1 |10 | 1 |

    ------------------------------------------------------------------

    6.重新执行该语句

    sql>select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = 1

     6 and a.deal_id = 0

     7 and a.create_date >= to_date(sysdate-10, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(sysdate-5, \'yyyy-mm-dd hh24:mi:ss\')

     9 group by b.biz_source;

    biz_source counts

    ---------- ----------

     102 713

     501 7881

     701 1465

    3 rows selected.

    elapsed: 00:00:00.24

    sql> /

    biz_source counts

    ---------- ----------

     102 713

     501 7882

     701 1465

    3 rows selected.

    elapsed: 00:00:00.23

    调整后的查询时间在0.2秒左右，速度提高了100倍左右，我只是简单的把索引列位置调换一下，性能就有了很大提高，statspack看不到这条语句了。总的来说，索引不是说create就可以了，通常需要考虑以下几点。

    1.结合实际的应用。

    2.考虑索引列的数据分布，如果distinct值很少且数据分布均匀的话，可能就不适合放在联合索引的最前面。

    3.考虑索引的大小，在字段长度32的列和长度为7的列上建立索引大小肯定是不一样的，索引越大扫描的代价就越高。

    4.考虑索引列冗余，可能你在索引中多冗余一个小字段，select就只走索引而不需要去扫描原表的数据。

    5.考虑索引对其他sql的影响，是否其他的sql也可以使用这个索引。

    6.考虑对是否可以对原有索引进行合并。

建议继续学习：

1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读：15130)
2. 浅谈MySQL索引背后的数据结构及算法    (阅读：9966)
3. 由浅入深理解索引的实现(2)    (阅读：6461)
4. HBase二级索引与Join    (阅读：5848)
5. InnODB和MyISAM索引统计集合    (阅读：5300)
6. Innodb 表和索引结构    (阅读：4863)
7. mysql查询中利用索引的机制    (阅读：4823)
8. MySQL索引背后的数据结构及算法原理    (阅读：4487)
9. 多维度分类排行榜应用：用位图索引    (阅读：4033)
10. mysql索引浅析    (阅读：4138)