AnalyticDBMySQL编写和优化SQL的经验和技巧_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

在云原生数据仓库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
```