大数据量下MySQL插入方法的性能比较_MySQL
不管是日常业务数据处理中，还是数据库的导入导出，都可能遇到需要处理大量数据的插入。插入的方式和数据库引擎都会对插入速度造成影响，这篇文章旨在从理论和实践上对各种方法进行分析和比较，方便以后应用中插入方法的选择。
插入分析
MySQL中插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：
- 连接：（3）
- 发送查询给服务器：（2）
- 分析查询：（2）
- 插入记录：（1x记录大小）
- 插入索引：（1x索引）
- 关闭：（1）
如果我们每插入一条都执行一个SQL语句，那么我们需要执行除了连接和关闭之外的所有步骤N次，这样是非常耗时的，优化的方式有一下几种：
- 在每个insert语句中写入多行，批量插入
- 将所有查询语句写入事务中
- 利用Load Data导入数据
每种方式执行的性能如下。
Innodb引擎
InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)以及外键约束(FOREIGN KEY constraints)。
InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
测试环境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
总数100W条数据
插入完后数据库大小38.6MB（无索引），46.8（有索引）
- 无索引单条插入 总耗时：229s 峰值内存：246KB
- 有索引单条插入 总耗时：242s 峰值内存：246KB
- 无索引批量插入 总耗时：10s 峰值内存：8643KB
- 有索引批量插入 总耗时：16s 峰值内存：8643KB
- 无索引事务插入 总耗时：78s 峰值内存：246KB
- 有索引事务插入 总耗时：82s 峰值内存：246KB
- 无索引Load Data插入 总耗时：12s 峰值内存：246KB
- 有索引Load Data插入 总耗时：11s 峰值内存：246KB
MyIASM引擎
MyISAM 是MySQL缺省存贮引擎。设计简单，支持全文搜索。
测试环境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
总数100W条数据
插入完后数据库大小19.1MB（无索引），38.6（有索引）
- 无索引单条插入 总耗时：82s 峰值内存：246KB
- 有索引单条插入 总耗时：86s 峰值内存：246KB
- 无索引批量插入 总耗时：3s 峰值内存：8643KB
- 有索引批量插入 总耗时：7s 峰值内存：8643KB
- 无索引Load Data插入 总耗时：6s 峰值内存：246KB
- 有索引Load Data插入 总耗时：8s 峰值内存：246KB
总结
我测试的数据量不是很大，不过可以大概了解这几种插入方式对于速度的影响，最快的必然是Load Data方式。这种方式相对比较麻烦，因为涉及到了写文件，但是可以兼顾内存和速度。
测试代码
<ol class="dp-j">
<li class="alt"><span><span><?php </span></span></span></li>
<li><span>$dsn = <span class="string">'mysql:host=localhost;dbname=test'</span><span>; </span></span></li>
<li class="alt"><span>$db = <span class="keyword">new</span><span> PDO($dsn,</span><span class="string">'root'</span><span>,</span><span class="string">''</span><span>,array(PDO::ATTR_PERSISTENT => </span><span class="keyword">true</span><span>)); </span></span></li>
<li><span><span class="comment">//删除上次的插入数据</span><span> </span></span></li>
<li class="alt"><span>$db->query(<span class="string">'delete from `test`'</span><span>); </span></span></li>
<li><span><span class="comment">//开始计时</span><span> </span></span></li>
<li class="alt"><span>$start_time = time(); </span></li>
<li><span>$sum = <span class="number">1000000</span><span>; </span></span></li>
<li class="alt"><span><span class="comment">// 测试选项</span><span> </span></span></li>
<li><span>$num = <span class="number">1</span><span>; </span></span></li>
<li class="alt"><span> </span></li>
<li><span><span class="keyword">if</span><span> ($num == </span><span class="number">1</span><span>){ </span></span></li>
<li class="alt"><span><span class="comment">// 单条插入</span><span> </span></span></li>
<li><span><span class="keyword">for</span><span>($i = </span><span class="number">0</span><span>; $i </span></span></li>
<li class="alt"><span>$db->query(<span class="string">"insert into `test` (`id`,`name`) values ($i,'tsetssdf')"</span><span>); </span></span></li>
<li><span>} </span></li>
<li class="alt"><span>} elseif ($num == <span class="number">2</span><span>) { </span></span></li>
<li><span><span class="comment">// 批量插入，为了不超过max_allowed_packet，选择每10万插入一次</span><span> </span></span></li>
<li class="alt"><span><span class="keyword">for</span><span> ($i = </span><span class="number">0</span><span>; $i </span></span></li>
<li><span><span class="keyword">if</span><span> ($i == $sum - </span><span class="number">1</span><span>) { </span><span class="comment">//最后一次</span><span> </span></span></li>
<li class="alt"><span><span class="keyword">if</span><span> ($i%</span><span class="number">100000</span><span> == </span><span class="number">0</span><span>){ </span></span></li>
<li><span>$values = <span class="string">"($i, 'testtest')"</span><span>; </span></span></li>
<li class="alt"><span>$db->query(<span class="string">"insert into `test` (`id`, `name`) values $values"</span><span>); </span></span></li>
<li><span>} <span class="keyword">else</span><span> { </span></span></li>
<li class="alt"><span>$values .= <span class="string">",($i, 'testtest')"</span><span>; </span></span></li>
<li><span>$db->query(<span class="string">"insert into `test` (`id`, `name`) values $values"</span><span>); </span></span></li>
<li class="alt"><span>} </span></li>
<li><span><span class="keyword">break</span><span>; </span></span></li>
<li class="alt"><span>} </span></li>
<li><span><span class="keyword">if</span><span> ($i%</span><span class="number">100000</span><span> == </span><span class="number">0</span><span>) { </span><span class="comment">//平常只有在这个情况下才插入</span><span> </span></span></li>
<li class="alt"><span><span class="keyword">if</span><span> ($i == </span><span class="number">0</span><span>){ </span></span></li>
<li><span>$values = <span class="string">"($i, 'testtest')"</span><span>; </span></span></li>
<li class="alt"><span>} <span class="keyword">else</span><span> { </span></span></li>
<li><span>$db->query(<span class="string">"insert into `test` (`id`, `name`) values $values"</span><span>); </span></span></li>
<li class="alt"><span>$values = <span class="string">"($i, 'testtest')"</span><span>; </span></span></li>
<li><span>} </span></li>
<li class="alt"><span>} <span class="keyword">else</span><span> { </span></span></li>
<li><span>$values .= <span class="string">",($i, 'testtest')"</span><span>; </span></span></li>
<li class="alt"><span>} </span></li>
<li><span>} </span></li>
<li class="alt"><span>} elseif ($num == <span class="number">3</span><span>) { </span></span></li>
<li><span><span class="comment">// 事务插入</span><span> </span></span></li>
<li class="alt"><span>$db->beginTransaction(); </span></li>
<li><span><span class="keyword">for</span><span>($i = </span><span class="number">0</span><span>; $i </span></span></li>
<li class="alt"><span>$db->query(<span class="string">"insert into `test` (`id`,`name`) values ($i,'tsetssdf')"</span><span>); </span></span></li>
<li><span>} </span></li>
<li class="alt"><span>$db->commit(); </span></li>
<li><span>} elseif ($num == <span class="number">4</span><span>) { </span></span></li>
<li class="alt"><span><span class="comment">// 文件load data</span><span> </span></span></li>
<li><span>$filename = dirname(__FILE__).<span class="string">'/test.sql'</span><span>; </span></span></li>
<li class="alt"><span>$fp = fopen($filename, <span class="string">'w'</span><span>); </span></span></li>
<li><span><span class="keyword">for</span><span>($i = </span><span class="number">0</span><span>; $i </span></span></li>
<li class="alt"><span>fputs($fp, <span class="string">"$i,'testtest'\r\n"</span><span>); </span></span></li>
<li><span>} </span></li>
<li class="alt"><span>$db->exec(<span class="string">"load data infile '$filename' into table test fields terminated by ','"</span><span>); </span></span></li>
<li><span>} </span></li>
<li class="alt"><span> </span></li>
<li><span>$end_time = time(); </span></li>
<li class="alt"><span>echo <span class="string">"总耗时"</span><span>, ($end_time - $start_time), </span><span class="string">"秒\n"</span><span>; </span></span></li>
<li><span>echo <span class="string">"峰值内存"</span><span>, round(memory_get_peak_usage()/</span><span class="number">1000</span><span>), </span><span class="string">"KB\n"</span><span>; </span></span></li>
<li class="alt"><span> </span></li>
<li><span>?> </span></li>
</ol>以上就是MySQL大量数据插入各种方法性能分析与比较，希望能帮到你。
博文出处：http://yansu.org/2014/04/16/insert-large-number-of-data-in-mysql.html
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!
인기 기사
뜨거운 도구
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
드림위버 CS6
시각적 웹 개발 도구
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
LARAVEL 소개 예
Apr 18, 2025 pm 12:45 PM
Laravel은 웹 응용 프로그램을 쉽게 구축하기위한 PHP 프레임 워크입니다. 설치 : Composer를 사용하여 전 세계적으로 Laravel CLI를 설치하고 프로젝트 디렉토리에서 응용 프로그램을 작성하는 등 다양한 기능을 제공합니다. 라우팅 : Routes/Web.php에서 URL과 핸들러 간의 관계를 정의하십시오. 보기 : 리소스/뷰에서보기를 작성하여 응용 프로그램의 인터페이스를 렌더링합니다. 데이터베이스 통합 : MySQL과 같은 데이터베이스와 상자 외 통합을 제공하고 마이그레이션을 사용하여 테이블을 작성하고 수정합니다. 모델 및 컨트롤러 : 모델은 데이터베이스 엔티티를 나타내고 컨트롤러는 HTTP 요청을 처리합니다.
MySQL 및 Phpmyadmin : 핵심 기능 및 기능
Apr 22, 2025 am 12:12 AM
MySQL 및 Phpmyadmin은 강력한 데이터베이스 관리 도구입니다. 1) MySQL은 데이터베이스 및 테이블을 작성하고 DML 및 SQL 쿼리를 실행하는 데 사용됩니다. 2) PHPMYADMIN은 데이터베이스 관리, 테이블 구조 관리, 데이터 운영 및 사용자 권한 관리에 직관적 인 인터페이스를 제공합니다.
MySQL 대 기타 프로그래밍 언어 : 비교
Apr 19, 2025 am 12:22 AM
다른 프로그래밍 언어와 비교할 때 MySQL은 주로 데이터를 저장하고 관리하는 데 사용되는 반면 Python, Java 및 C와 같은 다른 언어는 논리적 처리 및 응용 프로그램 개발에 사용됩니다. MySQL은 데이터 관리 요구에 적합한 고성능, 확장 성 및 크로스 플랫폼 지원으로 유명하며 다른 언어는 데이터 분석, 엔터프라이즈 애플리케이션 및 시스템 프로그래밍과 같은 해당 분야에서 이점이 있습니다.
데이터베이스 연결 문제 해결 : Minii/DB 라이브러리 사용 실질적인 사례
Apr 18, 2025 am 07:09 AM
작은 응용 프로그램을 개발할 때 까다로운 문제가 발생했습니다. 가벼운 데이터베이스 운영 라이브러리를 신속하게 통합해야합니다. 여러 라이브러리를 시도한 후에는 기능이 너무 많거나 호환되지 않는다는 것을 알았습니다. 결국, 나는 내 문제를 완벽하게 해결하는 YII2를 기반으로 단순화 된 버전 인 Minii/DB를 발견했습니다.
Laravel 프레임 워크 설치 방법
Apr 18, 2025 pm 12:54 PM
기사 요약 :이 기사는 Laravel 프레임 워크를 쉽게 설치하는 방법에 대한 독자들을 안내하기위한 자세한 단계별 지침을 제공합니다. Laravel은 웹 애플리케이션의 개발 프로세스를 가속화하는 강력한 PHP 프레임 워크입니다. 이 자습서는 시스템 요구 사항에서 데이터베이스 구성 및 라우팅 설정에 이르기까지 설치 프로세스를 다룹니다. 이러한 단계를 수행함으로써 독자들은 라벨 프로젝트를위한 탄탄한 토대를 빠르고 효율적으로 놓을 수 있습니다.
MySQL 모드 해결 문제 : theliamysqlmodeschecker 모듈 사용 경험
Apr 18, 2025 am 08:42 AM
Thelia를 사용하여 전자 상거래 웹 사이트를 개발할 때 까다로운 문제가 발생했습니다. MySQL 모드가 제대로 설정되지 않아 일부 기능이 제대로 작동하지 않습니다. 약간의 탐색 후, 나는 theliamysqlmodeschecker라는 모듈을 발견했습니다.이 모듈은 Thelia가 요구하는 MySQL 패턴을 자동으로 수정하여 내 문제를 완전히 해결할 수 있습니다.
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. 쿼리 최적화 및 색인 사용을 통해 쿼리 효율성을 향상시킵니다.
