本記事では、OceanBaseデータベースにおける暗黙型変換の回避方法について説明します。
暗黙型変換の回避
OceanBaseでは、演算子が異なる型のオペランドと共に使用される場合、オペランド間の互換性を持たせるために型変換が発生します。これは暗黙的な変換であり、データベースは必要に応じて自動的に数値を文字列に、文字列を数値に変換します。
オフラインでスロークエリを引き起こすSQLにおいて、このようなSQLの存在は望ましくありません。業務ロジック自体に問題はないものの、SQLを記述する際にフィールドの型に注意を払わなかったためです。主に以下の2つのタイプがあります：
テーブル作成時にフィールドは数値型であったが、クエリ時に渡されるパラメータ値が文字列である場合。この場合、実行計画に影響はなく、通常通りインデックスが使用されます。
例：テーブル作成文とインデックス作成文は以下の通りです。
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パラメータに渡される値が正しい文字列型の場合
現在の実行計画を確認すると、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
これら2つの例で差異が生じる原因は、数値と文字を比較する際、データベースが文字を数値に変換してから比較するためです。