MariaDBでPrepared Statementsを使ってみた。

１．はじめに

MariaDBでデータ削除でin句にSQL文を記述すると遅くなってしまった為、Prepared Statementsを使用し削除を実施してみた。

２．サンプル例

MariaDB [test]> select * from test_name;
+----------+--------+
| test_id  | name   |
+----------+--------+
| A0000001 | user01 |
| A0000002 | user02 |
| A0000003 | user03 |
| A0000004 | user04 |
| A0000005 | user05 |
| A0000006 | user06 |
| A0000007 | user07 |
| A0000008 | user08 |
| A0000009 | user09 |
| A0000010 | user10 |
| A0000011 | user11 |
| A0000012 | user12 |
| A0000013 | user13 |
| A0000014 | user14 |
| A0000015 | user15 |
+----------+--------+
15 rows in set (0.000 sec)

MariaDB [test]> select * from test_info;
+----------+-------------+
| test_id  | create_date |
+----------+-------------+
| A0000001 | 2020-12-01  |
| A0000002 | 2021-01-01  |
| A0000003 | 2021-09-01  |
+----------+-------------+
3 rows in set (0.000 sec)

3.　実行計画の確認

以下のような削除SQLの実行計画を確認するとtest_nameをフル検索していることが分る

MariaDB [test]> explain delete from test_name where test_id in
    -> (select ti.test_id from test_info ti where ti.create_date < '2021-08-01');
+------+--------------------+-----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type        | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+--------------------+-----------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY            | test_name | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where |
|    2 | DEPENDENT SUBQUERY | ti        | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+------+--------------------+-----------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.000 sec)

in句に直接値を入れるとrange検索に変化している。

MariaDB [test]> explain delete from test_name where test_id in ('A0000001', 'A0000002');
+------+-------------+-----------+-------+----------------------+----------------------+---------+------+------+-------------+
| id   | select_type | table     | type  | possible_keys        | key                  | key_len | ref  | rows | Extra       |
+------+-------------+-----------+-------+----------------------+----------------------+---------+------+------+-------------+
|    1 | SIMPLE      | test_name | range | newtable_test_id_IDX | newtable_test_id_IDX | 22      | NULL |    2 | Using where |
+------+-------------+-----------+-------+----------------------+----------------------+---------+------+------+-------------+
1 row in set (0.000 sec)

4. Prepared Statementsで記述

mariadbでPrepared Statementsがあるので試したみた。
set 構文で削除SQLを構築してprepareで実行する。

MariaDB [test]> set @testid = IFNULL((select group_concat('''', test_id, '''') from test_info where create_date < '2021-08-01'), '''dummy''');
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> select @testid;
+-----------------------+
| @testid               |
+-----------------------+
| 'A0000001','A0000002' |
+-----------------------+
1 row in set (0.000 sec)

MariaDB [test]> set @del_test_name = concat('delete from test_name where test_id in (', @testid, ');') ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @del_test_name;
+-----------------------------------------------------------------+
| @del_test_name                                                  |
+-----------------------------------------------------------------+
| delete from test_name where test_id in ('A0000001','A0000002'); |
+-----------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> prepare stmt from @del_test_name;
Query OK, 0 rows affected (0.000 sec)
Statement prepared

MariaDB [test]> execute stmt;
Query OK, 2 rows affected (0.001 sec)

set構文でSQLを構築しているが、in句の値を決定する時にデータが無い場合、test_nameに存在しない値を定義して実行している。
SQL内で値が存在しない時のスキップ実行はストアドプロシージャで記述しないとできないと思われる。

参考にしたURL
Prepared Statements