更新时间:2022-03-24 GMT+08:00
分享

WHERE

  • 表查询时,WHERE条件中应包含所有分布键字段等值查询条件,否则将在多个节点上进行查询,影响系统并发度和性能。
  • 禁止在WHERE条件相同表字段进行相互比较。

    例如如下语句应考虑合理性:

    SELECT * FROM t1 WHERE col1 = col1;

    应考虑修改为:

    SELECT * FROM t1 WHERE col1 IS NOT NULL;

  • 禁止WHERE条件涉及隐式数据类型转换。

    数据库中进行隐式转换后可能导致无法使用所创建的索引,导致潜在的性能问题。

    强烈建议在开发过程中开启GUC参数check_implicit_conversions,并关闭enable_fast_query_shipping,以便检查询语句中是否存在可能带来不良性能影响的隐式数据类型。

    SET enable_fast_query_shipping = off;

    SET check_implicit_conversions = true;

    由于隐式数据类型转换检测存在额外的开销,一旦查询语句开发完成后,请关闭check_implicit_conversions参数,并重置enable_fast_query_shipping。

    示例

    当不带如下代码不符合规范:

    t_tablename表的phonenumber字段为VARCHAR类型(而不是数值类型),以下语句利用phonenumber进行条件过滤时,优化器会将phonenumber隐式转化为bigint类型。

    SELECT column1

    INTO i_l_variable1

    FROM t_tablename

    WHERE phonenumber = 13512345678;

    导致两个后果:

    1. 不能进行DN裁剪,计划下发到所有的DN上执行。
    2. 计划中不能使用index scan方式扫描数据。

    建议修改t_tablename表的phonenumber字段为VARCHAR类型(而不是数值类型)

    SELECT column1

    INTO i_l_variable1

    FROM t_tablename

    WHERE phonenumber = '13512345678';

  • 禁止WHERE 条件字段使用表达式或是函数。

    对条件字段使用表达式或函数时,索引会失效,同时会对每一行数据进行计算,产生不必要的性能消耗。主要因为非常量的表达式在预处理阶段不能转化为Const值,因此不能用来剪枝,导致查询语句扫描所有的数据。

    示例:

    如下代码不符合规范:

    SELECT income FROM table WHERE abs(income) > ?;

    SELECT income FROM table WHERE income * 10 > ?;

    SELECT create_time

    FROM table

    WHERE date_format(create_time, '%Y­%m­%d %H:%i:%s') = '2009­01­01 00:00:0';

    应修改为:

    SELECT income FROM table WHERE income > ? OR income < (-1) * ?;

    SELECT income FROM table WHERE income > ?/10;

    SELECT create_time

    FROM table

    WHERE create_time = str_to_date('2009­01­01 00:00:0', '%Y­%m­%d %H:%i:%s');

  • 查询条件中与NULL做比较时,禁止使用“!=”比较符,应使用IS NULL或IS NOT NULL。

    不能写expression=NULL或expression != NULL,因为NULL代表一个未知的值,不能通过表达式判断两个未知值是否相等。

  • 查询条件中禁止对索引字段使用“!= ”比较符,避免索引失效。
  • 在where子句中,应当对过滤条件进行排序,把选择读较小(筛选出的记录数较少)的条件排在前面。
  • where子句中的过滤条件,尽量符合单边规则。即把字段名放在比较条件的一边,优化器在某些场景下会自动进行剪枝优化。形如col op expression,其中col为表的一个列, op为‘ =’、‘ >’的等比较操作符, expression为不含列名的表达式。

    示例:

    如下代码不推荐使用,根据time列进行筛选

    SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHEREcurrent_timestamp(6) - time < '1 days'::interval;

    建议修改为:

    SELECT id, from_image_id, from_person_id, from_video_id FROM face_data where time >current_timestamp(6) - '1 days'::interval;

  • 查询条件的索引字段上禁止避免与NULL(IS NULL和IS NOT NULL)进行比较。
  • 查询条件的索引字段上避免使用NOT。
  • 查询条件的索引字段上避免使用NOT IN。
  • 模糊查询LIKE语句,非必要情况下,%不应放在首字符位置。

    如果%放在首字符位置,将无法使用索引,会导致全表扫描。

  • WHERE条件中IN的候选子集不易过大,建议不超过500。

    查询时,会对IN中每一条数据进行等值比较,开销较大。

    如果包含的值为较为固定的值,应考虑创建REPLICATION表,并将候选数据写入表中,然后通过INNER JOIN来实现包含查询。

  • WHERE条件中IN的候选子集不为常量,而是表中的列时,建议改写为子查询。

    在这种情况下,实际上是一个不等值的JOIN,会通过nestloop计划执行。在表过大时执行效率低下,建议修改为等值JOIN的子查询。

    示例

    如下代码不推荐使用:

    SELECT col1, COALESCE(max(col2 - 1), 0)

    FROM t1, t2

    WHERE t1.col1 = ANY(VALUES(id1), (id2))

    GROUP BY col1;

    建议修改为:

    SELECT col1, COALESCE(max(tmp), 0) FROM

    (

    (

    SELECT col1, (col2-1) AS tmp

    FROM t1, t2

    WHERE t1.col1 = t2.id1 AND t1.col1 != t2.id2

    ) UNION ALL (

    SELECT col1, (col2-1) AS tmp

    FROM t1, t2

    WHERE t1.col1 = t2.id2

    )

    ) GROUP BY col1;

  • 多使用等值操作,少使用非等值操作。

    WHERE条件中的非等值条件(IN、BETWEEN、<、<=、>、>=)会导致后面的条件使用不了索引,因为不能同时用到两个范围条件。

分享:

    相关文档

    相关产品

close