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은 웹 개발에 널리 사용되는 오픈 소스 관계형 데이터베이스 관리 시스템입니다. 주요 기능에는 다음이 포함됩니다. 1. 다른 시나리오에 적합한 InnoDB 및 MyISAM과 같은 여러 스토리지 엔진을 지원합니다. 2.로드 밸런싱 및 데이터 백업을 용이하게하기 위해 마스터 슬레이브 복제 기능을 제공합니다. 3. 쿼리 최적화 및 색인 사용을 통해 쿼리 효율성을 향상시킵니다.

SQL의 목적 : MySQL 데이터베이스와 상호 작용합니다Apr 18, 2025 am 12:12 AM

SQL은 MySQL 데이터베이스와 상호 작용하여 데이터 첨가, 삭제, 수정, 검사 및 데이터베이스 설계를 실현하는 데 사용됩니다. 1) SQL은 Select, Insert, Update, Delete 문을 통해 데이터 작업을 수행합니다. 2) 데이터베이스 설계 및 관리에 대한 생성, 변경, 삭제 문을 사용하십시오. 3) 복잡한 쿼리 및 데이터 분석은 SQL을 통해 구현되어 비즈니스 의사 결정 효율성을 향상시킵니다.

초보자를위한 MySQL : 데이터베이스 관리를 시작합니다Apr 18, 2025 am 12:10 AM

MySQL의 기본 작업에는 데이터베이스, 테이블 작성 및 SQL을 사용하여 데이터에서 CRUD 작업을 수행하는 것이 포함됩니다. 1. 데이터베이스 생성 : createAbasemy_first_db; 2. 테이블 만들기 : CreateTableBooks (idintauto_incrementprimarykey, titlevarchar (100) notnull, authorvarchar (100) notnull, published_yearint); 3. 데이터 삽입 : InsertIntobooks (Title, Author, Published_year) VA

MySQL의 역할 : 웹 응용 프로그램의 데이터베이스Apr 17, 2025 am 12:23 AM

웹 응용 프로그램에서 MySQL의 주요 역할은 데이터를 저장하고 관리하는 것입니다. 1. MySQL은 사용자 정보, 제품 카탈로그, 트랜잭션 레코드 및 기타 데이터를 효율적으로 처리합니다. 2. SQL 쿼리를 통해 개발자는 데이터베이스에서 정보를 추출하여 동적 컨텐츠를 생성 할 수 있습니다. 3.mysql은 클라이언트-서버 모델을 기반으로 작동하여 허용 가능한 쿼리 속도를 보장합니다.

MySQL : 첫 번째 데이터베이스 구축Apr 17, 2025 am 12:22 AM

MySQL 데이터베이스를 구축하는 단계에는 다음이 포함됩니다. 1. 데이터베이스 및 테이블 작성, 2. 데이터 삽입 및 3. 쿼리를 수행하십시오. 먼저 CreateAbase 및 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 Hentai를 무료로 생성하십시오.

뜨거운 도구

VSCode Windows 64비트 다운로드

Microsoft에서 출시한 강력한 무료 IDE 편집기

맨티스BT

Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.

ZendStudio 13.5.1 맥

강력한 PHP 통합 개발 환경

Dreamweaver Mac版

시각적 웹 개발 도구

MinGW - Windows용 미니멀리스트 GNU

이 프로젝트는 osdn.net/projects/mingw로 마이그레이션되는 중입니다. 계속해서 그곳에서 우리를 팔로우할 수 있습니다. MinGW: GCC(GNU Compiler Collection)의 기본 Windows 포트로, 기본 Windows 애플리케이션을 구축하기 위한 무료 배포 가능 가져오기 라이브러리 및 헤더 파일로 C99 기능을 지원하는 MSVC 런타임에 대한 확장이 포함되어 있습니다. 모든 MinGW 소프트웨어는 64비트 Windows 플랫폼에서 실행될 수 있습니다.