巧用复合索引，有效降低系统IO
|
《巧用复合索引，有效降低系统IO》要点： 我们知道索引至关重要,合理的索引使用能够在很大程度上改善数据库的性能.然而很多人都会走入这样一个误区：走索引的SQL语句的性能一定比全表扫描好.真的是这样吗?今天我们将围绕B*Tree索引的使用,解读如何合理地使用索引,以及如何通过正确的索引来提高性能. 影响数据库性能的因素主要有以下几个：
在以上几个因素中,我认为I/O的问题是最重要的,也是很多数据库最普遍的性能问题.因此SQL优化的核心就是用最少的I/O处理想要的数据,提高核心SQL的处理速度,会带来整个系统性能的提升.而跟I/O最相关的因素就是索引. 接下来我们通过真实案例来分析索引的使用. 首先创建测试表：
生成测试数据：
对上述的Tip进行说明：
接下来我们进行测试： 不使用索引的情况
说明：
我们来看执行计划：
我们看到此时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的组合索引. 经验证,此时性能达到最佳. 文章来自微信公众号：数据和云 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
