現場での実践SQL(6) | IT SKILL MAP

現場での実践SQL(6)

 今回は、INSERT、UPDATE、DELETEの応用的なテクニックについてまとめます。

目次
1.INSERT
1- 1.BULK INSERT
1- 3.REPLACE INTO
2.UPDATE
3.DELETE
3- 1.TRUNCATE

1.INSERT

1-1.BULK INSERT

 テーブルにレコードをインサートする場合、以下のようにINSERT文を書きます。

insert into A values('1','100');
insert into A values('2','200');
insert into A values('3','300');

 これに対してBULK INSERTは、以下のようにINSERT文の一文にインサートしたいデータをまとめて書き、一度にインサートを行います。

insert into A values('1','100'),('2','200'),('3','200');

 サーバーではINSERT、UPDATE、DELETE等、データの更新を行う際、処理中の予期せぬ障害に備え、障害前のデータに戻す(ロールバック)ことができるように、トランザクションというDBMS特有の処理を、処理ごとバックグラウンドで実行しています。このトランザクション、数回であれば、それこそトランザクションの存在を意識することもないスピードで処理されますが、大量にインサートを行う際には、このトランザクションの時間がボトルネックとなります。これを解消するため、BULK INSERTを利用してトランザクションの回数を減らすことで、インサートにかかる時間を短縮します。
 上記の例の場合、通常インサートでは3回トランザクションが発生しますが、BULK INSERTの場合は1回のトランザクションで済みます。


1-2.独自トランザクション内でINSERT

strat transaction; -- トランザクション開始

insert into A values('1','100');
insert into A values('2','200');
insert into A values('3','300');

commit; -- コミット:トランザクションを確定

-- rollback; 中止する場合


 BULK INERTではトランザクションの回数を減らすことでインサート時間の短縮を図りましたが、仕組みは同じです。1回のトランザクションで全てのインサートを行います。


1-3.REPLACE INTO

replace into A values('1','100');

 REPLACE INTOは、INSERT INTOとは違い、仮に同じプライマリーキーのレコードが存在する場合はUPDATE、ない場合はINSERTを行ってくれる構文です。INSERT INTOでは同一プライマリキーのレコードが存在する場合にエラーになりますが、REPLACE INTOではエラーにならず上書きとなります。


2.UPDATE

2-1.SELECT FOR UPDATEによるレコードロック

start transaction;

select * from A where KEY='1' for update;
update A set KEY=KEY+1 where KEY='1';

commit;

-- rollback; 中止する場合


 INSERT、DELETEの際はそれほど気にする必要はないのですが、UPDATEについては、同時利用人数が多くなれば多くなるほど不整合が発生するリスクが高まります。このときいわゆるDBMSの分離レベルを高く設定していれば問題はありませんが、レスポンス性能を優先して分離レベルを低く運用している場合は、UPDATE文を利用する側で配慮し、SELECT FOR UPDATEを利用して行ロックをかけた上でUPDATEを行います。尚、独自トランザクション内でないと行ロックは有効にならないので注意が必要です。

3.DELETE

3-1.TRUNCATE

truncate table A;

 TRUNCATE文はトランザクションを実行せず、テーブルの内容を全削除します。このためDELETEに比べて高速に削除できますが、DELETEと違いWHERE文の指定が出来ないため、レコードを全削除する場合しか利用できません。



おすすめの関連記事

現場での実践SQL(5)
現場での実践的なSQL知識をまとめています。(5)ではグループ化による集計についてまとめています。
現場での実践SQL(7)完
現場での実践的なSQL知識をまとめています。(7)ではSQLのファンクション、ストアドプロシージャについてまとめています。