# [Know Issue]关于 TiDB 中使用 plan cache SQL 执行带有 regexp 很慢

## Key Words

plancache, 执行计划缓存, regexp

## Description

1. 如下 SQL 使用 ignore_plan_cache 前后 SQL 执行速度差异巨大

    ```
    set @c1 = "0528|1513";
    set @c2 = "1";
    set @c3 = "1";
    set @c4 = "PL011";

    prepare ps from "SELECT /*+ ignore_plan_cache() */
    t1.id,
    t1.price_no,
    t1.price_name,
    t1.product_line_code,
    t1.effect_date,
    t1.disable_date,
    t1.customer_code,
    t1.effect_status,
    t1.price_type,
    t1.business_status,
    t1.channel_code_L1,
    t1.object_version_number,
    t1.creation_date,
    t1.created_by,
    t1.last_updated_by,
    t1.last_update_date,
    t1.created_account,
    t1.last_updated_account,
    t1.approval_time,
    t1.process_instance_id,
    GROUP_CONCAT(cpcust.company_code) AS company_code
    FROM
    (
        SELECT
        cpcust.price_no,
        cpcust.cust_code,
        cpcust.cust_name,
        cpcust.company_code
        FROM
        commodity_price_customer cpcust
        WHERE
        1 = 1
        AND cpcust.company_code REGEXP ?
        GROUP BY
        concat(cpcust.price_no, cpcust.company_code)
    ) AS cpcust
    LEFT JOIN commodity_price t1 ON cpcust.price_no = t1.price_no
    LEFT JOIN commodity_price_org t4 ON t4.price_no = t1.price_no
    AND t1.price_type = ?
    LEFT JOIN commodity_price_channel cpc ON t1.price_no = cpc.price_no
    WHERE
    1 = 1
    AND t1.price_type = ?
    AND t1.product_line_code IN (?)
    GROUP BY
    t1.id
    ORDER BY
    id DESC
    LIMIT
    10";
    execute ps using @c1,@c2,@c3,@c4;
    ```

2. 对比结果如下, 使用 plan cache 执行 7.725268475s, 不使用 plan cache 执行 0.784211976s

```
MySQL [tof_commodity]> select     * from     information_schema.cluster_slow_query where     Txn_start_ts = 442440871536230493     and time >='2023-06-26 19:00:00'     and time < '2023-06-26 19:20:00' \G
*************************** 1. row ***************************
                     INSTANCE:
                         Time: 2023-06-26 19:13:10.609736
                 Txn_start_ts: 442440871536230493
                         User: root
                         Host: 10.74.167.59
                      Conn_ID: 42681
             Exec_retry_count: 0
              Exec_retry_time: 0
                   Query_time: 7.725268475
                   ......


MySQL [tof_commodity]> select      *  from      information_schema.cluster_slow_query  where      Txn_start_ts = 442440858311327765      and time >='2023-06-26 19:00:00'     and time < '2023-06-26 19:20:00'\G
*************************** 1. row ***************************
                     INSTANCE:
                         Time: 2023-06-26 19:12:13.233855
                 Txn_start_ts: 442440858311327765
                         User: root
                         Host: 10.74.167.59
                      Conn_ID: 42681
             Exec_retry_count: 0
              Exec_retry_time: 0
                   Query_time: 0.784211976
                   ......
```

## Root Cause

如果 TiDB 用了 plan cache，正则表达式的编译结果没法缓存，使得处理每一行数据的时候，都需要对正则表达式进行一次编译，消耗大量时间, 如下图 profile 所示:

![know-issue-tidb-regexp-performance-issue](../images/TiDB/know-issue-tidb-regexp-performance-issue.jpg)

## Diagnostic Steps

1. 抓取 profile 观察是否 regexp.Compile 函数占用了大量 CPU 时间
2. 判断当前 TiDB 版本是否低于 Affected Versions

## Resolution

1. 升级至 v6.5 及之后, TiDB 通过该 [PR](https://github.com/pingcap/tidb/pull/37893) 支持 regexp 下推到 TiKV 解决该问题, 详情参见 Affected Versions
2. 临时使用 hint 绕过, 即: 如果发现 SQL 有带 regexp，就加上 ```/*+ ignore_plan_cache()*/```；

## Affected Versions

TiDB Version < v6.4.0, v6.3.0  
TiDB Version <= v6.2.0, v6.1.6
TiDB Version <= v5.x.x