Skip to content

Oracle查询优化实践

一次数据库查询的计算复杂度来源于：I/O代价 + CPU代价 + 内存访问开销 + 通信代价

基本的编写查询的查询优化理论一般从：

  • 减小中间过程数据量
  • 避免重复运算
  • 使用更高效的算法

三个维度来考虑。

常见的思路、准则：

  • 快速缩小处理数据量的选择运算先行
  • 投影操作与选择运算/双目运算一起做，避免重复扫描
  • 利用等价运算，如笛卡尔积交换律/笛卡尔积结合律/投影串接定律/选择串接定律，减少运算步数

算法复杂度

join复杂度计算

据说没有索引engine会选择hash join或者merge join进行优化，我的引擎默认是使用hash join 参考：join的类型和Nested-Loop & Block Nested-Loop的复杂度

hash join

选择被哈希的表，通常是小一点的表。 小表T1所有的记录都被遍历。如果记录符合查询条件，这条记录就会进去哈希表，以id为key，以查询值为value。 大表T2所有的记录被遍历。如果记录符合查询条件，使用这条记录的id去搜索哈希表，所有命中的记录的查询值的值，都被返回，这样就可以把两者join起来了。 时间复杂度O(n+m)，实现hash表是O(n)，hash表查找是O(m)，直接将其相加。

merge join

  1. 复制T1(id, name),根据id排序。
  2. 复制T2(id, date)，根据id排序。
  3. 两个指针指向两个表的最小值。 >1 2< 2 3 2 4 3 5
  4. 在循环中比较指针，如果match，就返回记录。如果不match，指向较小值的指针指向下一个记录。

    >1  2<  - 不match, 左指针小，左指针++
    2  3
    2  4
    3  5

    1  2<  - match, 返回记录，两个指针都++
    >2  3
    2  4
    3  5

    1  2  - match, 返回记录，两个指针都++
    2  3< 
    2  4
    >3  5

    1  2 - 左指针越界，查询结束。
    2  3
    2  4<
    3  5
    >

时间复杂度O(nlog(n)+mlog(m)+（m和n中较大值）)。merge-sort排序算法的复杂度分别是O(nlog(n))和O(mlog(m))，直接将两者相加。

在这种情况下，使查询更加复杂或许可以加快速度

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

1、SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

UNION 操作符会扫描全表去重，结果集中的列名等于 UNION 中第一个 SELECT 语句中的列名。

2、SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

UNION 的实现需要在合并结果集后，执行去重操作。

首先，它会执行每个子查询，并将结果放入一个临时表中。 然后，它会对这个临时表进行排序或使用哈希算法，找到并删除重复的行，返回去重后的结果集，因此这一步是O(n*m)

UNION ALL 的实现则相对简单得多。

它仅仅是执行每个子查询，将结果直接合并到一起，而不做去重处理。 因此，执行过程中无需额外的排序或哈希计算步骤。

GROUPBY

group by的实现方式是先按需要分组的列排序。常见的快排，时间复杂度为 O(nlogn),空间复杂度O(1)。即使数据量大，group by基本也能hold住。

DISTINCT

distinct将需要去重的列中的所有内容都加载到哈希表中。最后计算hash中有多少key就是最终的结果。计算复杂度O(n)

由于需要将所有不同的值都存起来，内存消耗可想而知,如果数据量特别大可能会内存不够。

案例

针对销售流水数据增删改查处理的接口，具体sql需要查销售订单表SALE_ORDER_HEADER和退货订单表RETURN_ORDER_HEADER，并关联门店信息视图ORGVIEW获取门店名称等额外信息，纯流水数据SALE_ORDER_HEADER这张表原本是按年分表的（累计数据量过亿），现在半年有两千万数据；RETURN_ORDER_HEADER这张表有两万条数据，ORGVIEW这张视图有一万条数据。

基本操作——限定查询范围，建立索引，优化关联顺序

优化基操，了解到一般是门店使用该服务，检查该门店在一段时间内的流水。所以SALE_ORDER_HEADER表建立了业务时间POSTING_DATE、门店编码STORE_ID的组合索引，DOC_ID的单列索引，基于组合索引的最左查询原则写了查询语句； 发现查询的限制条件两张表都有，所以先两个表分开查缩小数据量后再UNION，不加条件限制直接查需要2分43秒； 然后发现由于两张表和门店视图都不小，测试了关联的若干排列顺序组合发现一般来讲先关联SOH和RTOH查询出子表，再关联门店视图。

这样操作以后，针对门店的查询已经很让人满意了，只要0.3秒，但由于POSTING_DATE、STORE_ID是组合索引，根据实际写的顺序，如果只限定查询的时间范围无法命中这个组合索引，花费时间41秒，显然如此（错误）使用这个服务的客户是一定会跳起来骂程序员的（）

4.png

基于业务改善——SQL UNION 操作符的优化

上述问题可以靠新建单列索引/分表进一步解决，先再看看SQL有没有优化空间，查询计划如图：

1.png

Oracle查询计划的解读可以看：https://use-the-index-luke.com/sql/explain-plan/oracle/operations

发现严重拖后腿的是sort（unique）去重的过程发生了全表扫描，这两张表由于主键唯一，事实上数据本身就是去重的，就可以去掉这一步全表扫描，一举再把查询时间打到0.8秒（😎）

2.png 3.png