本文介绍AnalyticDB for MySQL中常用的SQL优化原则以及方法。

SQL优化原则

  • SQL尽量简单

    大部分情况下SQL性能随SQL复杂度下降,例如,单表查询性能 (冗余设计)优于多表关联查询。

  • 减少IO

    使用索引扫描,尽可能少的进行列扫描,返回最小数据量,同时减少IO和内存开销。

  • 本地计算

    在大数据计算中,进行本地计算时应避免数据跨节点,充分利用分布式多计算资源的能力。

常用优化方法

  • 去掉不必要的列

    AnalyticDB for MySQL是行列混存数据库,返回列的数量直接影响SQL性能,建议在编写SQL时只写业务确认需要返回的列,不直接使用 *。

    典型的错误写法:

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

    正确写法中明确要返回的列:

    select col1, col2,... from tab1 where c1>100 and c1<1000;
  • 尽早过滤数据

    尽可能早的过滤数据,减少数据规模,有助于加速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;

    上述SQL中,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
  • 选择性使用索引

    AnalyticDB for MySQL建表时默认创建全列索引,系统会尽量使用索引完成扫描工作。但在某些情况下,不使用索引效率更高,主要取决于数据选择率和计算能力。

    以下场景中,您可以不使用索引,SQL性能将更好。
    • 低筛选列
      当SQL中包含多个查询条件时,可优先选择高筛选条件的索引进行扫描,其他条件可以通过表扫描方式。例如以下SQL通过c1字段和time字段过滤数据,通过c1=3可以快速查询到少量记录(假设10000条),而单独通过time>'2010-01-01 00:00:00'时返回的记录数非常大。由此可见,通过c1的筛选条件选择率较高,通过time的筛选条件选择率较低。
      select c1,c2 from tab1 where c1=3 and time >='2010-01-01 00:00:00';

      以下SQL只使用c1列的索引,time条件通过内部扫描,例如time >='2010-01-01 00:00:00'通过表扫描。计算引擎首先检索c1列的索引,得出满足条件c1=3的行集合,然后读取每行所对应的time列数据,如果满足time >='2010-01-01 00:00:00',则将该行数据加入返回结果。

      /*+ no_index_columns=[tab1.time]*/ select c1,c2 from 
      tab1 where c1=3 and time >='2010-01-01 00:00:00';
    • 不等值条件比较
      例如select c1,c2 from tab1 where c1=3 and c2<>100;中存在不等值条件c2<>100,无法有效过滤记录。此时可以不通过索引扫描,通过内部扫描效率更高。
      /*+ no_index_columns=[tab1.c2]*/ select c1,c2 from tab1 where c1=3 and c2<>100;
    • 非前缀索引

      中缀或后缀查询中,建议不通过索引扫描,通过内部扫描。

      例如SQL为 select c1,c2 from tab1 where c1=3 and c3 like '%abc'%;,可以不通过索引扫描,通过增加no-index hint进行内部扫描,对应的SQL为/*+ no_index_columns=[tab1.c3]*/ select c1,c2 from tab1 where c1=3 and c3 like '%abc%';

  • 索引与NULL

    AnalyticDB for MySQL不同于其他类型的数据库,AnalyticDB for MySQL中可以保存NULL值。因此,如果WHERE条件中使用了is nullis not null,则可以使用索引。

  • 分区裁剪

    AnalyticDB for MySQL是分布式数据库,数据分布在多个分片中,支持在分片的基础上进行二级分区。在表设计阶段,确定好分片和分区规则后,后期编写SQL语句时,应尽量增加对分片字段和分区字段的使用,快速过滤数据,缩小搜索范围。

    CREATE TABLE t_fact_mail_status (
    mail_id varchar COMMENT '',
    scan_timestamp timestamp COMMENT '',
    biz_date bigint COMMENT '',
    org_code varchar COMMENT '',
    org_name varchar COMMENT '',
    dlv_person_name varchar COMMENT '',
    receiver_name varchar COMMENT '',
    receiver_phone varchar COMMENT '',
    receiver_addr varchar COMMENT '',
    product_no varchar COMMENT '',
    mag_no varchar COMMENT '',
    op_1_timestamp bigint COMMENT '',
    op_2_timestamp bigint COMMENT '',
    op_3_timestamp bigint COMMENT '',
    op_4_timestamp bigint COMMENT '',
    op_5_timestamp bigint COMMENT '',
    PRIMARY KEY (mail_id,org_code,biz_date)
    )
    DISTRIBUTED BY (org_code) 
    PARTITIONED BY VALUE(biz_date)
    COMMENT '';

    针对以上表,可通过以下SQL实现分区裁剪。

    select count(*) as cn from t_fact_mail_status t
    where
    t.org_code = '21111101'and t.biz_date = 20171128
  • 数据重分布

    多个普通表进行关联时,若访问的关联数据不在同一分片或分区,即涉及跨分片或分区的数据访问时,需要做数据重分布。

    数据重分布操作非常消耗资源,因此应尽量保证数据在同一分片或分区内完成关联操作。因此普通表关联时, ON条件尽量包含分区列且是等值查询,以减少因分区没有对齐而造成的数据重分布。

常见问题

哪些问题会导致索引失效?

  • 函数(列)

    SQL中的函数转换可能造成索引失效,例如select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='2017-10-12';

  • 模糊匹配

    SQL中含有模糊匹配条件可能导致索引失效。

    select c1,c2 
    from tab1
    where time like '2017-10-12%';

    可将上述SQL改为以下形式,避免索引失效。

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