本記事では、OceanBaseデータベースにおける暗黙型変換を回避する方法について説明します。
暗黙型変換の回避
OceanBaseでは、演算子が異なる型のオペランドと一緒に使用される場合、オペランドを互換性のあるものにするために型変換が発生します。これは暗黙的な変換であり、データベースは必要に応じて自動的に数値を文字列に、文字列を数値に変換します。
このような暗黙的な型変換は、オフライン時の遅いクエリを引き起こす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つの例の違いは、数値と文字列を比較する際に、データベースが文字列を数値に変換してから比較を行うために生じます。