总则

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

在分析型数据库MySQL版中，编写和优化SQL的要求和经验总结如下：

• SQL编写原则：追求简单

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

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

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

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

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

常见SQL优化细节

去掉不必要的列

分析型数据库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包含多个查询条件时，优先选择高筛选条件，其他条件可以通过扫描实现。

原理

分析型数据库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