巧用复合索引,有效降低系统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语句包括以下几个:

SQL1:每天执行10次

SQL2:每天执行1000次

SQL3:每天执行100次

SQL4:每天执行1000次

收集表使用的所有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的组合索引。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-06-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PHP技术

MyISAM InnoDB 区别

 MyISAM 和 InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的...

3345
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第八篇(r3笔记第24天)

生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话。会有很大的成就感,同时如果某个sql语句执行10秒...

2597
来自专栏数据和云

杀手SQL- 一条关于 'Not in' SQL 的优化案例

编辑手记:在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的。SQL 的世界无奇不有,今天我们一起见识一条让你绝对想吐血的杀手...

3296
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第二篇(r2第38天)

在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时,得到的sql monitor报告如下: Global Informat...

2617
来自专栏杨建荣的学习笔记

特殊的物化视图刷新 (r4笔记第77天)

现在有一个需求,某个环境中存在两个用户,一个用户中存在物化视图,另一个用户中存在源表,根据业务的需要,需要做一种特别的物化视图刷新。 ? 物化视图用户中的物化...

3307
来自专栏杨建荣的学习笔记

一条insert语句导致的性能问题分析(二)(r8笔记第43天)

今天对之前描述的问题一条insert语句导致的性能问题分析(一) 进行了进一步的补充。 有一条insert语句的主要性能瓶颈在于insert子句中的查询语句,查...

2885
来自专栏帘卷西风的专栏

创建角色随机名字(mysql抽取随机记录)和mysql游标的使用

1、现在创建游戏角色的时候,基本上都是支持角色名字随机的,以前此功能在客户端用代码实现,然后向服务器请求并验证,后来发现有时候连续几次都失败,所以改成在服务器...

542
来自专栏Hadoop实操

如何使用Phoenix在CDH的HBase中创建二级索引

1742
来自专栏更流畅、简洁的软件开发方式

为or、in平反——or、in到底能不能利用索引?

  先说一个笑话,作为开场白。俺也换换风格试一试,呵呵。   在以前,有三个书生赶考,在路上遇到了一个算命先生,于是就问算命先生:我们三个人赶考,结果如何呀?...

18710
来自专栏杨建荣的学习笔记

MYSQL索引条件下推的简单测试

自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。 ...

2695

扫描关注云+社区