全部产品

SQL开发规范

更新时间:2019-04-29 15:51:33

总则

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

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

  • SQL编写原则:追求简单

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

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

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

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

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

常见SQL优化细节

去掉不必要的列

分析型数据库MySQL版是列存数据库,返回的列的数量直接影响性能,在编写SQL时一定要确认业务需要返回的列,不要直接使用*。

典型错误SQL写法

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

正确SQL写法

  1. 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'时返回的记录数又非常大。

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

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

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

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

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

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

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

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

    1. /* +no-index=[tab1.c3] */
    2. 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。

  1. select c1,c2 from tab1 where substr(cast(time as varchar),1,10)='2017-
  2. 10-12';

正确SQL:

  1. 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过滤条件。

示例

  1. Select c1, c2 from tab1 where c1>100 and c1<1000 and c1 is not null;

示例SQL中的and c1 is not null为多余条件,优化后SQL如下:

  1. Select c1,c2 from tab1 where c1>100 and c1<1000;

多表关联

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

  • 维度表join普通表,没有限制。

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

  1. Select count(*)
  2. from customer_table C join
  3. order_table O on C.customer_id= O.customer_id
  4. where O.order_time between'2018-07-20 10:00:11'
  5. and '2018-09-30 10:00:11'
  6. and O.order_amount=100;

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

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