云原生数据仓库AnalyticDB MySQL版中编写和优化SQL时,需要充分考虑其分布式特性,本文汇总了SQL编写和优化时的经验和技巧供您参考。

经验总结

编写和优化SQL的经验总结如下:

  • SQL编写原则为追求简单

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

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

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

  • 分布式计算,本地计算&并行计算

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

  • 高QPS,分区裁剪

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

SQL优化技巧

  • 去掉不必要的列

    云原生数据仓库AnalyticDB MySQL版是行列混存数据库,返回的列的数量直接影响性能,在编写SQL时一定要确认业务需要返回的列,不要直接使用星号(*)进行查询。

    SQL示例。
    • 错误SQL:
      select * from tab1 where c1>100 and c1<1000;
    • 正确SQL:
      select col1, col2 from table_name where c1>100 and c1<1000;
  • 索引和扫描

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

    云原生数据仓库AnalyticDB MySQL版内部采用行列混存方式,通过单列高效过滤后,可直接通过内部记录指针扫描其他列值,减少其他列的索引查询开销。

    云原生数据仓库AnalyticDB MySQL版支持在查询级别和集群级别针对特定字段关闭索引过滤,请参见过滤条件不下推

    示例
    说明 以下示例适用于3.14以下的内核版本。如果内核版本为3.14及以上,请使用filter_not_pushdown_columnsHint。
    • time条件通过内部扫描
      在SQL命令中通过条件c1=3可快速查询到少量记录(假设10000),单独使用time>'2010-01-01 00:00:00'时返回的记录数又非常大,SQL示例如下:
      select c1,c2 from tab1 where c1=3 and time >='2010-01-01 00:00:00';
      此时可只通过c1进行索引,time通过内部扫描方式执行,查询更快,返回更多有效记录数。SQL示例如下:
      /*+ no_index_columns=[tab1.time] */
      select c1,c2 from tab1
      where c1=3 and time>='2010-01-01 00:00:00';

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

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

    • 不等于条件通过内部扫描
      例如在查询中使用c2<>100,通过索引扫描时,c2<>100无法有效过滤掉无效记录。SQL示例如下:
      select c1,c2 from tab1 where c1=3 and c2<>100;
      增加no_index_columnsHint,使不等于条件通过内部扫描执行,SQL示例如下:
      /*+ no_index_columns=[tab1.c2] */
      select c1,c2 from tab1 where c1=3 and c2<>100;
    • like条件通过内部扫描

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

      增加no_index_columnsHint,使like条件通过内部扫描执行,更加快速地查询有效记录,SQL示例如下:
      /*+ no_index_columns=[tab1.c3] */
      select c1,c2 from tab1 where c1=3 and c3 like '%abc%';
  • 索引失效

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

    以下情形容易引起索引失效:
    • 函数转换(列)。
    • 类型转换。
    • like条件,例如:like '%abc%'
    SQL示例:
    • 以下SQL中的函数转换导致索引失效。time为timestamp类型,存储时间2017-12-10 10:00:23
      select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='2017-12-10';
    • 正确SQL示例:
      select c1,c2 from tab1 where time>='2017-12-10 00:00:00' and time<='2017-12-10 23:59:59';
  • 去掉不必要的is not null过滤条件
    SQL示例:
    • 错误SQL示例:
      Select c1, c2 from tab1 where c1>100 and c1<1000 and c1 is not null;
    • 删除上述SQL中的多余条件and c1 is not null,优化后SQL如下:
      Select c1,c2 from tab1 where c1>100 and c1<1000;
  • 多表关联

    不同多表关联场景下,SQL优化原则不同,其中:

    • 普通表JOIN普通表,尽量包含分区列条件。如果不包含,则尽量通过WHERE条件过滤掉多余的数据。
    • 维度表JOIN普通表,没有限制。
    多表关联查询WHERE条件中,需要明确写明每一个表的过滤条件。通常在传统数据库中,都是通过索引字段关联来快速检索数据。例如:
    Select count(*) 
    from t1 C join 
    t2 O on C.t1_id= O.t1_id
    where O.t2_time between'2018-07-20 10:00:11' 
    and '2018-09-30 10:00:11' 
    and O.t2_amount=100;

    在明确t2与t1表都有同样的time和type过滤条件情况下,建议修改为如下SQL:

    Select count(*)
    from t1 join t2 on t1.id=t2.id
    where t1.time between '2017-12-10 00:00:00' and '2017-12-10 23:59:59'
    and t1.type= 100
    and t2.time between '2017-12-10 00:00:00' and '2017-12-10 23:59:59'
    and t2.type=100