全部产品
阿里云办公

8.8.2 AnalyticDB SQL优化实践

更新时间:2018-11-28 13:52:35

1.总则

AnalyticDB是一个分布式、列存数据库,在编写和优化SQL时,需要充分考虑其分布式特性。

在AnalyticDB中,编写和优化SQL的要求和经验总结如下:

  • SQL编写原则:追求简单

    一般情况下,数据库性能会随SQL复杂度而下降。例如,单表查询(冗余设计)优于表关联查询。

  • SQL优化核心方法:减少I/O

    尽可能少的进行列扫描,返回最小数据量,减少I/O同时也减少内存开销。

  • 分布式计算:本地计算&并行计算 大数据计算情况下,本地计算时充分利用分布式多计算资源的能力,避免数据跨节点。

  • 高QPS:分区裁剪 业务系统要求高QPS、毫秒级RT时,表和SQL必须设计为分区裁剪模式。

2.常见SQL优化细节

2.1 去掉不必要的列

AnalyticDB是列存数据库,返回的列的数量直接影响性能,在编写SQL时一定要确认业务需要返回的列,不要直接使用*。

典型错误SQL写法

  1. select * from tab1 where c1>100 and c1<1000;

正确SQL写法

  1. select col1, col2 from table_name where c1>100 and c1<1000;

2.2 索引&扫描

当SQL包含多个查询条件时,优先选择高筛选条件,其他条件可以通过扫描实现。

原理

AnalyticDB内部采用列存方式,通过单列高效过滤后,可直接通过内部记录指针扫描其他列值,减少其他列的索引查询开销。

示例

  • time条件通过内部扫描

    以下SQL通过条件c1=3可快速查询到少量记录(假设10000),单独使用time>'2010-01-01 00:00:00'时返回的记录数又非常大。

    1. select c1,c2 from tab1 where c1=3 and time >='2010-01-01 00:00:00';

    此时可只通过c1进行索引,time通过内部扫描方式执行,查询更快,返回更多有效记录数。SQL示例如下:

    1. /* +no-index=[tab1.time] */
    2. select c1,c2 from tab1
    3. where c1=3 and time>='2010-01-01 00:00:00';
    4. /* +no-index=[tab1.time] */

    Hint表示强制time>='2010-01-01 00:00:00'条件走扫描。

    在上述SQL中,计算引擎首先检索列c1的索引,得出满足条件c1=3的行集合,然后读取每行所对应的time列数据。如果满足time>='2010-01-01 00:00:00',则将该行数据加入返回结果。

  • 不等于条件通过内部扫描

    不等于条件查询,例如:c2<>100,不通过索引扫描时,c2<>100无法有效过滤掉无效记录。例如:

    1. select c1,c2 from tab1 where c1=3 and c2<>100;

    增加no-indexHint,使不等于条件通过内部扫描执行,SQL示例如下:

    1. /* +no-index=[tab1.c2] */
    2. select c1,c2 from tab1 where c1=3 and c2<>100;
  • like条件通过内部扫描

    中缀或后缀查询,例如:like '%abc'like '%abc%'

    增加no-indexHint,使like条件通过内部扫描执行,更加快速地查询有效记录,SQL示例如下:

    1. /* +no-index=[tab1.c3] */
    2. select c1,c2 from tab1 where c1=3 and c3 like '%abc%';

2.3 索引失效

索引失效时,SQL语句直接以扫描的方式进行查询,如果表记录数非常大,会导致查询缓慢。

以下情形容易引起索引失效:

• 函数转换(列);

• 类型转换;

• like条件,例如:like '%abc%'

以下SQL中的函数转换导致索引失效。time为timestamp类型,存储时间2017-12-10 10:00:23。

  1. select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='2017-
  2. 10-12';

正确SQL:

  1. select c1,c2 from tab1 where time>='2017-10-12 00:00:00' and time<='2017-10-12 23:59:59';

2.4 is not null

去掉不必要的is not null过滤条件。

示例

  1. Select c1, c2 from tab1 where c1>100 and c1<1000 and c1 is not null;

示例SQL中的and c1 is not null为多余条件,优化后SQL如下:

  1. Select c1,c2 from tab1 where c1>100 and c1<1000;

2.5 子查询修改为表关联

处理带有子查询的Select时,AnalyticDB首先执行子查询,并将子查询的结果保存在内存中,然后将该子查询作为一个逻辑表,执行条件筛选。

由于子查询没有索引,所有条件筛选都要进行扫描。因此如果子查询结果较大时,性能比较差。反之当子查询结果集较小时,扫描性能会超过索引查询。

对于join查询,由于AnalyticDB默认采用hash join算法,如果其中一张表结果集(条件筛选后)较大时,扫描性能会必索引差很多,因此尽量不要采子查询。例如以下SQL:

  1. Select O.customer_id from order_table O join
  2. (select customer_table.customer_id
  3. from customer_table where customer_table.city_name = '杭州') C
  4. on C.customer_id= O.customer_id where O.order_type='快餐';

当满足city_name = ‘杭州’和order_type=’快餐’的数据较多时,应改成:

  1. Select O.customer_id from order_table O join
  2. customer_table C on O.customer_id= C.customer_id
  3. where O.order_type='快餐' and C.city_name = '杭州';

2.6 多表关联

  • 普通表join普通表:尽量包含分区列join条件,如果不包含则,尽量通过where条件过滤掉多余的数据。

  • 维度表join普通表,没有限制。

多表关联查询where条件中,需要明确写明每一个表的过滤条件。通常我们在传统数据库中,都是通过索引字段关联来快速检索数据。如下SQL:

查询customer_table、order_table表中customer_id相同且order_time在2018-07-20 10:00:11和2018-09-30 10:00:11之间的记录。

  1. Select count(*)
  2. from customer_table C join
  3. order_table O on C.customer_id= O.customer_id
  4. where O.order_time between'2018-07-20 10:00:11'
  5. and '2018-09-30 10:00:11'
  6. and O.order_amount=100;

在明确customer_table与order_table表都有同样的order_time和order_amount过滤条件情况下,建议修改为如下SQL:

  1. Select count(*)
  2. from customer_table C join
  3. order_table O on C.customer_id= O.customer_id
  4. where O.order_time between'2018-07-20 10:00:11'
  5. and '2018-09-30 10:00:11' and O.order_amount=100
  6. and C.order_time between'2018-07-20 10:00:11'
  7. and '2018-09-30 10:00:11' and C.order_amount=100;