巧用复合索引，有效降低系统IO

《巧用复合索引，有效降低系统IO》要点：
本文介绍了巧用复合索引，有效降低系统IO，希望对您有用。如果有疑问，可以联系我们。

我们知道索引至关重要,合理的索引使用能够在很大程度上改善数据库的性能.然而很多人都会走入这样一个误区：走索引的SQL语句的性能一定比全表扫描好.真的是这样吗?今天我们将围绕B*Tree索引的使用,解读如何合理地使用索引,以及如何通过正确的索引来提高性能.

影响数据库性能的因素主要有以下几个：

• DB call
• Hard Parse+Soft Parse
• Wait Event
• I/O
• 不合理的设计与开发

在以上几个因素中,我认为I/O的问题是最重要的,也是很多数据库最普遍的性能问题.因此SQL优化的核心就是用最少的I/O处理想要的数据,提高核心SQL的处理速度,会带来整个系统性能的提升.而跟I/O最相关的因素就是索引.

接下来我们通过真实案例来分析索引的使用.

首先创建测试表：

生成测试数据：

对上述的Tip进行说明：

Tip1：生成1年的日期数据, 格式为 YYYYMMDD

Tip2：销售类型别生成数据,2个B2C,1个B2B

Tip3：使用笛卡尔积生成大量数据

接下来我们进行测试：

不使用索引的情况

说明：

Tip.4 清除BUFFER与SHARED POOL里的内容(禁止在生产库执行)

Tip.5 为抓取实际执行计划

Tip.6 查看实际执行计划内容

我们来看执行计划：

我们看到此时SQL走全表扫描,物理读为36111.

然后创建索引,再次执行以上SQL.

此时查看执行计划：

我们看到,此时走索引范围扫描,物理读为1322.

比之前提升了30倍左右.

接下来我们继续测试：

以下是单列索引,对之前的查询条件做了修改：

查看执行计划：

此时物理读为3994.

创建复合索引,并再次执行相同操作：

再次查看执行计划：

相同的操作逻辑读降为原来的十分之一.说明复合索引的效率在合理的场景下效率更高.

但是索引真的是万能的吗?我们继续测试

在没有索引的情况下修改查询条件执行以下语句：

查看执行计划：

SQL走全表扫,物理读36111.

创建索引,并执行相同语句：

查看执行计划：

WTH!

物理读竟然达到了40921?!比全表扫还多?!

这是什么原因呢?我们看上面的查询条件就能知道,当要访问的数据量占所有数据的比例较高的时候,此时全表扫描可以通过多块读加快速度,而索引则需要一条一条地进行检索,因此性能反而变差.

所以,并不是所有使用索引的SQL性能都比全表扫描好.

前面分析到,在某些场景下,如何使用适当的复合索引,能够很大程度提高性能.那么接下来我们将通过真实案例来说明,如何创建高性能的复合索引.

假如对于测试表,经常要进行操作的SQL语句包括以下几个：

收集表使用的所有SQL,制作成表格用于分析：

如果为每一条SQL语句创建最佳索引,则列举如下：

接下来我们使用排除法,来选择最佳索引.

1、SQL-4可以被 X_2代替使用, 这时X_4去掉.或者,反过来X_4 代替 X_2使用也可以.但是,SQL-2 为点与线段的条件组合,如使用 X_4 效率不高.

2、对于剩下的三组,对比发现,索引2和3相似,只是3包含更多的列.因此考虑索引多的话会对DML操作有负担,所以最终合并为2个索引.

但是,其中 SQL-2 的 SQL写法变换为以下写法.

这样处理后,创建两个索引,一个是以SALE_YMD的单列索引,一个是SHOP_ID,SALE_TP,SALE_YHD的组合索引.

经验证,此时性能达到最佳.

文章来自微信公众号：数据和云

转载请注明本页网址：
http://www.vephp.com/jiaocheng/3751.html