云原生数据仓库AnalyticDB MySQL版在编写和优化SQL时,需要充分考虑其分布式特性。

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

SQL编写原则为追求简单
一般情况下,数据库性能会随SQL复杂度而下降。例如,单表查询(冗余设计)优于表关联查询。
SQL优化核心方法是减少I/O
尽可能少的进行列扫描,返回最小数据量,减少I/O同时也减少内存开销。
分布式计算,本地计算&并行计算
大数据计算情况下,本地计算时充分利用分布式多计算资源的能力,避免数据跨节点。
高QPS,分区裁剪
业务系统要求高QPS、毫秒级RT时,表和SQL必须设计为分区裁剪模式。

常见SQL优化细节

去掉不必要的列

云原生数据仓库AnalyticDB MySQL版是行列混存数据库,返回的列的数量直接影响性能,在编写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版内部采用行列混存方式,通过单列高效过滤后,可直接通过内部记录指针扫描其他列值,减少其他列的索引查询开销。
示例
  • time条件通过内部扫描

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

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

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

    /* +no-index=[tab1.time] */
    select c1,c2 from tab1
    where c1=3 and time>='2010-01-01 00:00:00';
    /* +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无法有效过滤掉无效记录。例如:

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

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

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

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

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

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

索引失效

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

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

  • 函数转换(列);
  • 类型转换;
  • like条件,例如:like '%abc%'

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

select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='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';

is not null

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

示例
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;

多表关联

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

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

Select count(*) 
from customer_table C join 
order_table O on C.customer_id= O.customer_id
where O.order_time between'2018-07-20 10:00:11' 
and '2018-09-30 10:00:11' 
and O.order_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