SQL truncate 高速データ削除 deleteやdropとの違いとDBMS間の差異
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQL truncate 高速データ削除 deleteやdropとの違いとDBMS間の差異

    SQLのtruncate tableについてまとめました。

    deleteやdropとの比較、制限、主要DBMS間の差異について解説します。

    truncateの構文

    領域の割り当てを解除し一気にデータを削除します。

    トランザクションログを作らずに削除するため、高速で大量データを削除できます。ただし、全件削除の機能のみで、where句での条件指定はできません。インデックスも削除されます。

    なお、実行にはdrop table権限が必要です。delete権限では実行できません。

    また、truncate実行時には、Deleteトリガーが設定されていても起動しません。

    delete文との違い

    delete文は、where句での条件指定ができ、limit句で削除件数の指定も可能です。

    また、トランザクションログに「レコードを削除した」というログが残ります。実行後にロールバックが可能です。

    大量削除をする場合は、トランザクションログ書き込みが起こるため速度が低下する点と、外部キー制約設定テーブルに対しても、制約に違反しない限り削除可能な点がtruncate文との違いです。

    また、delete文の場合は削除件数が返り値として取得できますが、truncate文では返り値が空になります。

    dropとの違い

    drop文はテーブル構造ごと削除します。トランザクションログの書き出しがなく、実行後のロールバックができません。

    テーブル自体が削除され、インデックスも削除されます。

    テーブル構造を復活させるには、create tableを実行する必要があります。

    大量データの入ったテーブルへのdrop tableの実行速度は、delete文よりは速く、truncate文よりは遅いため、データの削除だけが目的なら、drop文を使うメリットはありません。

    truncate文とdelete、dropの実行速度 ダントツ速いのはtruncate

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    30万件のデータが入ったemployeesテーブルで削除にかかる時間を比較してみます。

    •  300025 行削除しました。 (Query took 0.5920 seconds.)

    • 返り値が空でした (行数 0)。 (Query took 0.1011 seconds.)

    実行時間は、約1/6でした。

    件数が増えるに従って、実行時間はどのように変化するか見るために280万件のデータが入ったsalariesテーブルの削除にかかる時間を比較してみます。

    •  2844047 行削除しました。 (Query took 11.1441 seconds.)

    削除後にロールバックをおこなった場合は以下の通り。

    •  2844047 行削除しました。 (Query took 15.0803 seconds.) [ROLLBACK occurred.]

    • 返り値が空でした (行数 0)。 (Query took 0.2341 seconds.)

    •  返り値が空でした (行数 0)。 (Query took 1.5249 seconds.)

    truncateにかかった時間は、deleteにかかった時間の約1/47でした。

    truncateにかかった時間は、deleteにかかった時間の約1/7でした。

    件数が増えれば増えるほど、truncateとdeleteの実行時間差は大きくなると言えるでしょう。

    truncate tableの制限

    truncate tableで複数のテーブルを削除することはできない

    truncate table文は、対象の1テーブルのデータを削除します。複数テーブルの指定はできません。

    複数テーブルの削除を行いたい場合は、以下のようにtruncate table文を列挙したファイルを作成しておき、実行するなどの工夫が必要です。

    外部キー制約が設定されていると、truncate tableが実行できない

    外部キー制約が設定されているテーブルにtrancate tableを実行することはできません。

    例えば、MySQLだと下記のエラーが出力されます。

    以下のように、一時的に外部キー制約を無効化(MySQLの場合)してからtruncate tableする方法があります。

    もしくは、delete文で削除を行いましょう。

    テーブルロックされていると、truncate tableが実行できない

    対象テーブルでトランザクションが実行中の場合や、テーブルがロックされている場合は、truncate tableが実行できません。

    主要DBMSのtruncate table仕様の差異

    Oracle

    • 実行するためには、drop tableできる権限が必要
    • ロールバック不可
    • Oracleでは単純なselect文でテーブルロックがかからないため、truncateとselectを同時に実行すると意図しない結果になるケースあり
    • MATERIALIZED VIEW LOG指定で、マテリアライズド・ビュー・ログの保存・削除が選べる
    • STORAGE句で、開放領域のドロップや再利用の指定が可能
    • CASCADE指定で、ON DELETE CASCADE参照制約が設定されている子表を同時に削除可能

    TRUNCATE TABLE(Oracle SQL 言語リファレンス)

    MySQL

    • 実行するためには、drop tableできる権限が必要(5.1.16以前ではdelete権限が必要)
    • ロールバック不可
    • AUTO_INCREMENT設定のIDカウンタが初期化される
    • データまたはインデックスファイルが破損した場合でも、truncate tableでからテーブルとして再生成可能

    MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.33 TRUNCATE TABLE 構文

    SQL Server

    • 実行に必要な権限は、対象テーブルに対するALTER権限
    • truncate tableをロールバック可能
    • WITH (PARTITIONS… 指定により、切り捨てるパーティションを指定可能

    TRUNCATE TABLE (Transact-SQL) – SQL Server | Microsoft Docs

    truncate tableのまとめ

    ポテパンダの一言メモ
    • 大量データ削除時には、deleteよりもtruncate tableの方が処理速度が速い
    • 複数テーブル削除は、truncate tableを複数実行する
    • 外部テーブル制約のあるテーブルに対しては、制約オフ後に実行するか、delete文を使用
    • 主要DBMSで、主にオプション指定でtruncate tableの仕様に差異あり

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件