sqlserver 删除大数据-mysql チュートリアル-php.cn

ホームページ

データベース

mysql チュートリアル

sqlserver 删除大数据

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:39 PM

sqlserver消去データ

一、写在前面 - 想说爱你不容易为了升级数据库至SQL Server 2008 R2，拿了一台现有的PC做测试，数据库从正式库Restore(3个数据库大小夸张地达到100G)，而机器内存只有可怜的4G，不仅要承担DB Server角色，同时也要作为Web Server，可想而知这台机器的命运是

一、写在前面 - 想说爱你不容易

　　为了升级数据库至SQL Server 2008 R2，拿了一台现有的PC做测试，数据库从正式库Restore(3个数据库大小夸张地达到100G+)，而机器内存只有可怜的4G，不仅要承担DB Server角色，同时也要作为Web Server，可想而知这台机器的命运是及其惨烈的，只要MS SQL Server一启动，内存使用率立马飙升至99%。没办法，只能升内存，两根8G共16G的内存换上，结果还是一样，内存瞬间被秒杀（CPU利用率在0%徘徊）。由于是PC机，内存插槽共俩，目前市面上最大的单根内存为16G（价格1K+），就算买回来估计内存还是不够（卧槽，PC机伤不起啊），看样子别无它法 -- 删数据！！！

　　删除数据 - 说的容易，不就是DELETE吗？靠，如果真这么干，我XXX估计能“知道上海凌晨4点的样子”（KB，Sorry，谁让我是XXX的Programmer，哥在这方面绝对比你牛X），而且估计会暴库（磁盘空间不足，产生的日志文件太大了）。

二、沙场点兵 - 众里寻他千百度

　　为了更好地阐述我所遇到的困难和问题，有必要做一些必要的测试和说明，同时这也是对如何解决问题的一种探究。因为毕竟这个问题的根本是如何来更好更快的操作数据，说到底就是DELETE、UPDATE、INSERT、TRUNCATE、DROP等的优化操作组合，我们的目的就是找出最优最快最好的方法。为了便于测试，准备了一张测试表Employee

sqlserver 删除大数据

<span>--</span><span>Create table Employee</span>
<span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span> (
    <span>[</span><span>EmployeeNo</span><span>]</span> <span>INT</span> <span>PRIMARY</span> <span>KEY</span>,
    <span>[</span><span>EmployeeName</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span><strong>50</strong></span>) <span>NULL</span>,
    <span>[</span><span>CreateUser</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span><strong>50</strong></span>) <span>NULL</span>,
    <span>[</span><span>CreateDatetime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span>
);

sqlserver 删除大数据

1. 数据插入PK

1.1. 循环插入，执行时间为38026毫秒

sqlserver 删除大数据

<span>--</span><span>循环插入</span>
<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Index</span> <span>INT</span> <span>=</span> <span><strong>1</strong></span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>WHILE</span> <span>@Index</span> <span> <span><strong>100000</strong></span>
<span>BEGIN</span>
    <span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) <span>VALUES</span>(<span>@Index</span>, <span>'</span><span>Employee_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@Index</span> <span>AS</span> <span>CHAR</span>(<span><strong>6</strong></span>)), <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>());
    <span>SET</span> <span>@Index</span> <span>=</span> <span>@Index</span> <span>+</span> <span><strong>1</strong></span>;
<span>END</span>

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;</span>

sqlserver 删除大数据

1.2. 事务循环插入，执行时间为6640毫秒

sqlserver 删除大数据

<span>--</span><span>事务循环</span>
<span>BEGIN</span> <span>TRAN</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Index</span> <span>INT</span> <span>=</span> <span><strong>1</strong></span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>WHILE</span> <span>@Index</span> <span> <span><strong>100000</strong></span>
<span>BEGIN</span>
    <span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) <span>VALUES</span>(<span>@Index</span>, <span>'</span><span>Employee_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@Index</span> <span>AS</span> <span>CHAR</span>(<span><strong>6</strong></span>)), <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>());
    <span>SET</span> <span>@Index</span> <span>=</span> <span>@Index</span> <span>+</span> <span><strong>1</strong></span>;
<span>END</span>

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

<span>COMMIT</span>;</span>

sqlserver 删除大数据

1.3. 批量插入，执行时间为220毫秒

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>(EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
<span>SELECT</span> <span>TOP</span>(<span><strong>100000</strong></span>) EmployeeNo <span>=</span> ROW_NUMBER() <span>OVER</span> (<span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>), <span>'</span><span>Employee_</span><span>'</span>, <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>()
<span>FROM</span> SYS.COLUMNS <span>AS</span> C1 <span>CROSS</span> <span>JOIN</span> SYS.COLUMNS <span>AS</span> C2
<span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

sqlserver 删除大数据

1.4. CTE插入，执行时间也为220毫秒

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

;<span>WITH</span> CTE(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) <span>AS</span>(
    <span>SELECT</span> <span>TOP</span>(<span><strong>100000</strong></span>) EmployeeNo <span>=</span> ROW_NUMBER() <span>OVER</span> (<span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>), <span>'</span><span>Employee_</span><span>'</span>, <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>()
    <span>FROM</span> SYS.COLUMNS <span>AS</span> C1 <span>CROSS</span> <span>JOIN</span> SYS.COLUMNS <span>AS</span> C2
    <span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>
)
<span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span> <span>SELECT</span> EmployeeNo, EmployeeName, CreateUser, CreateDatetime <span>FROM</span> CTE;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

sqlserver 删除大数据

小结：

按执行时间，效率依次为：CTE和批量插入效率相当，速度最快，事务插入次之，单循环插入速度最慢；
单循环插入速度最慢是由于INSERT每次都有日志，事务插入大大减少了写入日志次数，批量插入只有一次日志，CTE的基础是CLR，善用速度是最快的。

2. 数据删除PK

2.1. 循环删除，执行时间为1240毫秒

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>DELETE</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

sqlserver 删除大数据

2.2. 批量删除，执行时间为106毫秒

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>SET</span> <span>ROWCOUNT</span> <span><strong>100000</strong></span>;

<span>WHILE</span> <span><strong>1</strong></span> <span>=</span> <span><strong>1</strong></span>
<span>BEGIN</span>
    <span>BEGIN</span> <span>TRAN</span>
    <span>DELETE</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>;
    <span>COMMIT</span>
    <span>IF</span> <span><strong>@@ROWCOUNT</strong></span> <span>=</span> <span><strong>0</strong></span>
        <span>BREAK</span>;
<span>END</span>

<span>SET</span> <span>ROWCOUNT</span> <span><strong>0</strong></span>;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

sqlserver 删除大数据

2.3. TRUNCATE删除，执行时间为0毫秒

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>TRUNCATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

sqlserver 删除大数据

小结：

TRUNCATE太快了，清除10W数据一点没压力，批量删除次之，最后的DELTE太慢了；
TRUNCATE快是因为它属于DDL语句，只会产生极少的日志，普通的DELETE不仅会产生日志，而且会锁记录。

三、磨刀霍霍 - 犹抱琵琶半遮面

　　由上面的第二点我们知道，插入最快和删除最快的方式分别是批量插入和TRUNCATE，所以为了达到删除大数据的目的，我们也将采用这两种方式的组合，其中心思想是先把需要保留的数据存放之新表中，然后TRUNCATE原表中的数据，最后再批量把数据插回去，当然实现方式也可以随便变通。

1. 保留需要的数据之新表中->TRUNCATE原表数据->还原之前保留的数据之原表中

　　脚本类似如下

<span>SELECT</span> <span>*</span> <span>INTO</span> #keep <span>FROM</span> Original <span>WHERE</span> CreateDate <span>></span> <span>'</span><span>2011-12-31</span><span>'</span>
<span>TRUNCATE</span> <span>TABLE</span> Original
<span>INSERT</span> Original <span>SELECT</span> <span>*</span> <span>FROM</span> #keep

　　第一条语句会把所有要保留的数据先存放至表#keep中（表#keep无需手工创建，由SELECT INTO生效），#keep会Copy原始表Original的表结构。PS：如果你只想创建表结构，但不拷贝数据，则对应的脚本如下

<span>SELECT</span> <span>*</span> <span>INTO</span> #keep <span>FROM</span> Original <span>WHERE</span> <span><strong>1</strong></span> <span>=</span> <span><strong>2</strong></span>

　　第二条语句用于清除整个表中数据，产生的日志文件基本可以忽略；第三条语句用于还原保留数据。

几点说明：

你可以不用SELECT INTO，自己通过写脚本（或拷贝现有表）来创建#keep，但是后者有一个弊端，即无法通过SQL脚本来获得对应的表生成Script（我的意思是和原有表完全一致的脚本，即基本列，属性，索引，约束等），而且当要操作的表比较多时，估计你肯定会抓狂；
既然第一点欠妥，那考虑新建一个同样的数据库怎么样？既可以使用现有脚本，而且生成的数据库基本一致，但是我告诉你最好别这么做，因为第一要跨库，第二，你得准备足够的磁盘空间。

2. 新建表结构->批量插入需要保留的数据->DROP原表->重命名新表为原表

　　CREATE TABLE #keep AS (xxx) xxx -- 使用上面提到的方法（使用既有表的创建脚本），但是不能够保证完全一致；

　　INSERT #keep SELECT * FROM Original where clause

　　DROP TBALE Original

　　EXEC SP_RENAME '#keep','Original'

　　这种方式比第一种方法略快点，因为省略了数据还原（即最后一步的数据恢复），但是稍微麻烦点，因为你需要创建一张和以前原有一模一样的表结构，包括基本列、属性、约束、索性等等。

三、数据收缩 - 秋风少落叶

　　数据删除后，发现数据库占用空间大小并没有发生变化，此时我们就用借助强悍的数据收缩功能了，脚本如下，运行时间不定，取决于你的数据库大小，多则几十分钟，少则瞬间秒杀

<span>DBCC</span> SHRINKDATABASE(<span>DB_NAME</span>)

声明

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

MySQL：初心者が習得するための必須スキルApr 18, 2025 am 12:24 AM

MySQLは、初心者がデータベーススキルを学ぶのに適しています。 1.MySQLサーバーとクライアントツールをインストールします。 2。selectなどの基本的なSQLクエリを理解します。 3。マスターデータ操作：テーブルを作成し、データを挿入、更新、削除します。 4.高度なスキルを学ぶ：サブクエリとウィンドウの関数。 5。デバッグと最適化：構文を確認し、インデックスを使用し、選択*を避け、制限を使用します。

MySQL：構造化データとリレーショナルデータベースApr 18, 2025 am 12:22 AM

MySQLは、テーブル構造とSQLクエリを介して構造化されたデータを効率的に管理し、外部キーを介してテーブル間関係を実装します。 1.テーブルを作成するときにデータ形式と入力を定義します。 2。外部キーを使用して、テーブル間の関係を確立します。 3。インデックス作成とクエリの最適化により、パフォーマンスを改善します。 4.データベースを定期的にバックアップおよび監視して、データのセキュリティとパフォーマンスの最適化を確保します。

MySQL：説明されている主要な機能と機能Apr 18, 2025 am 12:17 AM

MySQLは、Web開発で広く使用されているオープンソースリレーショナルデータベース管理システムです。その重要な機能には、次のものが含まれます。1。さまざまなシナリオに適したInnodbやMyisamなどの複数のストレージエンジンをサポートします。 2。ロードバランスとデータバックアップを容易にするために、マスタースレーブレプリケーション機能を提供します。 3.クエリの最適化とインデックスの使用により、クエリ効率を改善します。

SQLの目的：MySQLデータベースとの対話Apr 18, 2025 am 12:12 AM

SQLは、MySQLデータベースと対話して、データの追加、削除、変更、検査、データベース設計を実現するために使用されます。 1）SQLは、ステートメントの選択、挿入、更新、削除を介してデータ操作を実行します。 2）データベースの設計と管理に作成、変更、ドロップステートメントを使用します。 3）複雑なクエリとデータ分析は、ビジネス上の意思決定効率を改善するためにSQLを通じて実装されます。

初心者向けのMySQL：データベース管理を開始しますApr 18, 2025 am 12:10 AM

MySQLの基本操作には、データベース、テーブルの作成、およびSQLを使用してデータのCRUD操作を実行することが含まれます。 1.データベースの作成：createdatabasemy_first_db; 2。テーブルの作成：createTableBooks（idintauto_incrementprimarykey、titlevarchary（100）notnull、authorvarchar（100）notnull、published_yearint）; 3.データの挿入：InsertIntoBooks（タイトル、著者、公開_year）VA

MySQLの役割：WebアプリケーションのデータベースApr 17, 2025 am 12:23 AM

WebアプリケーションにおけるMySQLの主な役割は、データを保存および管理することです。 1.MYSQLは、ユーザー情報、製品カタログ、トランザクションレコード、その他のデータを効率的に処理します。 2。SQLクエリを介して、開発者はデータベースから情報を抽出して動的なコンテンツを生成できます。 3.MYSQLは、クライアントサーバーモデルに基づいて機能し、許容可能なクエリ速度を確保します。

MySQL：最初のデータベースを構築しますApr 17, 2025 am 12:22 AM

MySQLデータベースを構築する手順には次のものがあります。1。データベースとテーブルの作成、2。データの挿入、および3。クエリを実行します。まず、createdAtabaseおよびcreateTableステートメントを使用してデータベースとテーブルを作成し、InsertINTOステートメントを使用してデータを挿入し、最後にSelectステートメントを使用してデータを照会します。

MySQL：データストレージに対する初心者向けのアプローチApr 17, 2025 am 12:21 AM

MySQLは、使いやすく強力であるため、初心者に適しています。 1.MYSQLはリレーショナルデータベースであり、CRUD操作にSQLを使用します。 2。インストールは簡単で、ルートユーザーのパスワードを構成する必要があります。 3.挿入、更新、削除、および選択してデータ操作を実行します。 4. Orderby、Where and Joinは複雑なクエリに使用できます。 5.デバッグでは、構文をチェックし、説明を使用してクエリを分析する必要があります。 6.最適化の提案には、インデックスの使用、適切なデータ型の選択、優れたプログラミング習慣が含まれます。

See all articles

ホットAIツール

Undresser.AI Undress

リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover

写真から衣服を削除するオンライン AI ツール。

Undress AI Tool

脱衣画像を無料で

Clothoff.io

AI衣類リムーバー

AI Hentai Generator

AIヘンタイを無料で生成します。

ホットツール

VSCode Windows 64 ビットのダウンロード

Microsoft によって発売された無料で強力な IDE エディター

MantisBT

Mantis は、製品の欠陥追跡を支援するために設計された、導入が簡単な Web ベースの欠陥追跡ツールです。 PHP、MySQL、Web サーバーが必要です。デモおよびホスティングサービスをチェックしてください。

ZendStudio 13.5.1 Mac

強力な PHP 統合開発環境

Dreamweaver Mac版

ビジュアル Web 開発ツール

MinGW - Minimalist GNU for Windows

このプロジェクトは osdn.net/projects/mingw に移行中です。引き続きそこでフォローしていただけます。 MinGW: GNU Compiler Collection (GCC) のネイティブ Windows ポートであり、ネイティブ Windows アプリケーションを構築するための自由に配布可能なインポートライブラリとヘッダーファイルであり、C99 機能をサポートする MSVC ランタイムの拡張機能が含まれています。すべての MinGW ソフトウェアは 64 ビット Windows プラットフォームで実行できます。