本文介绍在 OceanBase 数据库避免隐式类型转换。

避免隐式类型转换

在 OceanBase 中，当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容，从而发生转换隐式，即数据库会根据需要自动将数字转换为字符串，将字符串转换数字。

在导致线下慢查询的 SQL 中，这一类 SQL 的出现是很不应该的，业务逻辑本身没有问题，仅是在书写 SQL 的时候没有关注到字段本身的类型。主要有以下两种类型：

• 建表时字段是数值类型，在查询的时候传入的参数值为字符。此时对执行计划没有影响，都会正常走索引。

  示例：建表语句和创建索引语句如下。

  obclient> CREATE TABLE `test1`(`id` INT,`name` VARCHAR(10));
  obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;

  • 传入的参数值为数值类型

    此时执行计划的 NAME 列能看到走到了正确的索引。

    obclient> EXPLAIN SELECT * FROM test1 WHERE id=100;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | =================================================
    |ID|OPERATOR  |NAME              |EST. ROWS|COST|
    -------------------------------------------------
    |0 |TABLE SCAN|test1(idx_test_id)|1        |92  |
    =================================================
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter(nil),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set

  • 传入的参数值为正确的字符类型

    此时执行计划的 NAME 列能看到走到了正确的索引。

    obclient> EXPLAIN SELECT * FROM test1 WHERE id='100';
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | =================================================
    |ID|OPERATOR  |NAME              |EST. ROWS|COST|
    -------------------------------------------------
    |0 |TABLE SCAN|test1(idx_test_id)|1        |92  |
    =================================================
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter(nil),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set

• 建表时字段是字符类型，在查询的时候传入的参数值为数字。此时会导致无法利用到索引而扫全表。

  示例：建表语句和创建索引语句如下。

  obclient> CREATE TABLE `test1` (`id` VARCHAR(10), `name` VARCHAR(10));
  obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;

  • 传入的参数值为数值类型

    查看当前的执行计划，我们可以看到 name 列只有表 test1 没有索引 idx_test_id(id) ，当前的查询并没有用到已创建的索引。此时 COST 值为 408。

    obclient> EXPLAIN SELECT * FROM test1 WHERE id=100;
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                          |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | ====================================
    |ID|OPERATOR  |NAME |EST. ROWS|COST|
    ------------------------------------
    |0 |TABLE SCAN|test1|5        |408 |
    ====================================
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter([cast(test1.id, DECIMAL(-1, -1)) = ?]),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

  • 传入的参数值为正确的字符类型

    查看当前的执行计划，我们可以看到 name 列不仅有表 test1 还有索引 idx_test_id(id) ，当前的查询很好的用到已创建的索引。此时 COST 值为 92，明显降低了很多。

    obclient> EXPLAIN SELECT * FROM test1 WHERE id='100';
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | =================================================
    |ID|OPERATOR  |NAME              |EST. ROWS|COST|
    -------------------------------------------------
    |0 |TABLE SCAN|test1(idx_test_id)|1        |92  |
    =================================================
    Outputs & filters:
    -------------------------------------
      0 - output([test1.id], [test1.name]), filter(nil),
          access([test1.id], [test1.name]), partitions(p0)
     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set

造成这两个示例差异的原因是，在进行数值和字符做比较时，数据库会将字符转换为数值再进行对比。