ifnull 函数的正确使用方法和限制是什么

🪐 TiDB 技术问题
, , ,
1

【TiDB 使用环境】生产环境
【TiDB 版本】
【操作系统】

在生产库中， ifnull 函数经常被用的where 后面连接条件中，造成全表扫的情况，效率很低。

select *   
FROM
   a
  LEFT JOIN  b ON b.bussinessno = 'MERGE|COINS20251223175117'
  AND groupno = '2'
  AND a.certino = b.certino
  AND IFNULL(a.payno, 1) = b.payno

执行计划


| id                               | estRows      | estCost         | actRows   | task      | access object                                            | execution info                                                         
| Insert_1                         | 0.00         | 0.00            | 0         | root      |                                                          | time:5m44.7s, loops:1, prepare: 5m44.7s, insert:33.7ms, lock_keys: {tim
| └─Projection_11                  | 1002.55      | 112603832836.80 | 353       | root      |                                                          | time:5m44.7s, loops:6, Concurrency:OFF                                 
|   └─HashJoin_29                  | 1002.55      | 112603758596.21 | 353       | root      |                                                          | time:5m44.7s, loops:6, build_hash_table:{total:7.82ms, fetch:7.48ms, bu
|     ├─IndexLookUp_36(Build)      | 341.62       | 1895408.50      | 354       | root      |                                                          | time:7.44ms, loops:2, index_task: {total_time: 1.69ms, fetch_handle: 1.
|     │ ├─IndexRangeScan_33(Build) | 956.06       | 251839.23       | 354       | cop[tikv] | table:b, index:INX_CPT_BUSSINESSNO(bussinessno, groupno) | time:1.64ms, loops:3, cop_task: {num: 2, max: 795µs, min: 760.3µs, avg:
|     │ └─Selection_35(Probe)      | 341.62       | 485323.59       | 354       | cop[tikv] |                                                          | time:4.09ms, loops:2, cop_task: {num: 1, max: 3.79ms, proc_keys: 354, t
|     │   └─TableRowIDScan_34      | 956.06       | 389909.04       | 354       | cop[tikv] | table:b                                                  | tikv_task:{time:0s, loops:4}                                           
|     └─Projection_30(Probe)       | 261976638.00 | 102143674297.61 | 261871926 | root      |                                                          | time:5m36.1s, loops:258622, Concurrency:OFF                            
|       └─TableReader_32           | 261976638.00 | 97672833388.83  | 261871926 | root      |                                                          | time:5m15.5s, loops:258622, cop_task: {num: 22683, max: 0s, min: 0s, av
|         └─TableFullScan_31       | 261976638.00 | 110925364685.95 | 261871926 | cop[tikv] | table:a                                                  | tikv_task:{proc max:490ms, min:0s, avg: 74.1ms, p80:130ms, p95:310ms, i

针对这个情况也只能进行改成。所以这个ifnull 函数是应该适合用select 中做null的判断？

1 个赞
2

避免在查询字段上直接使用函数（包括IFNULL），优先将条件转换为 “字段本身直接参与判断” 的形式，让索引能够正常生效

3

主键、索引字段使用函数后，执行计划会全表扫描，和IFNULL没关系。

4

索引列加函数处理都不会再走索引了

5

certino 列上能加个索引吗，区分度够大的话

6

问题就是a表 ，为啥不选择 a.certino 列，这个字段上是有索引的。如果是这么改下执行就没问题了。

  AND a.certino = b.certino
  AND STRCMP(IFNULL(a.payno, 1) , b.payno ) = 0
1 个赞
7

IFNULL 用在 WHERE/ON 条件会导致索引失效触发全表扫

8

我也是这么理解的， 有官方文档的介绍说明吗

1 个赞
9

或者这样写试试：
(a.payno = b.payno OR (a.payno IS NULL AND b.payno = 1))

再尝试给 a.certino、b.payno 加索引优化。

10

搞不清楚手工加个hint或者绑定执行计划把

11

嗯，是可以这样搞

12

开发的SQL规范有时候很重要

13

– 示例：查询用户信息，age为NULL则返回0，nickname为NULL则返回’未设置’
SELECT
id,
IFNULL(nickname, ‘未设置’) AS user_nickname,
IFNULL(age, 0) AS user_age,
IFNULL(create_time, ‘1970-01-01 00:00:00’) AS create_time
FROM user;

14

如果用 (( a.payno = b.payno) or (a.payno is null and b.payno=‘1’) ), 会不会走索引呢 ? 反正 NULL=NULL 是false的.

15

我印象中所有的条件前件如果是函数的话,肯定走不了索引的.这个在传统关系型数据库中也是这样的

16

IFNULL函数更适合用在 SELECT子句中，为显示或后续计算提供一个非 NULL 的默认值，而不应用于决定数据关联关系的连接条件中。

17

使用任何函数运算都无法使用上索引

18

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。