本文出自:http://blog.csdn.net/hongchangfirst

 序言

        这篇文章研究了在海量数据需求下，MySQL数据库的承载能力。如果读者想尽快的获得帮助，而不想纠于细节，可以只看第一章；如果读者想具体了解结论是怎么得到的，是通过什么方式，可以继续看第二章；当然，所有的实验环境和部分SQL代码在第三章里指出，这一章读者可以快速浏览，以达到管中窥豹的效果。如有任何疑问可以和我联系。原文来自http://blog.csdn.net/hongchangfirst/article/details/6947129。

 

第一章  概述

1.1 摘要

        本文通过对MySQL数据库（Win32）进行各种方法下的插入操作，研究了MySQL数据库在各种情况下，对插入操作的吞吐量和性能瓶颈所在，并指出通过“缓存”加“批处理”的方式可以使MySQL数据库的吞吐量达到每分钟46MB，可以满足目前大部分应用。

1.2 结论

        根据目前的实验，在现行实验环境下（见第三章），MySQL至少能支持每分钟46MB的插入数据量，所以每天能插入的数据量至少为64GB。

        虽然实验结果是在Windows平台下得到的，但由于在Linux平台下和Windows平台下MySQL数据库的吞吐量并不会有数量级的差别，所以本文的结论也适用于Linux平台。

1.3 系统性能瓶颈结论

        1.  网络带宽并不是MySQL数据库插入操作的性能瓶颈。

        2.  每次插入两条数据的存储过程比着每次插入一条数据的存储过程，吞吐量大致得到两倍的提升。说明应用和数据库之间的JDBC开销正是MySQL数据库插入操作的性能瓶颈。

        3.  每次插入四十条数据的存储过程比每次插入一条数据的存储过程性能提高了40倍，说明此时JDBC仍然是系统的性能瓶颈，仍然可以通过提升JDBC效率（如增加一次通信插入的数据量）来提高系统的吞吐量，直到其不再是系统的性能瓶颈。

        4.  并发操作并不能增加系统的吞吐量，所以系统的瓶颈不在并发。

1.4 MySQL自身对插入的优化

        1. 使用insert into tablename values(),(),()...语法。

        2. 如果有索引，批处理插入前先把建索引disable了，插入完成后再从新建索引。

        3. 插入前对表进行加锁，插入完后，再解锁。

        4. 使用插入延迟，当客户使用插入延迟，服务器立刻返回，数据库自己对缓冲区做处理。

        5.使用 LOAD DATA INFILE，把文件中的数据直接映射成表。这个技术我认为实际上是利用了缓存加批处理，另外加上2、3、4的方式，所以速度超过。但是使用时要注意文件的编码格式。

第二章  实验

2.1 单机节点和insertToemployee的插入

        利用单机节点（应用和MySQL服务器运行于同一台计算机上）和存储过程insertToemployee（详见3.5）进行数据的插入，实验结果如表2. 1所示。从中我们可以看出，插入的数据量基本与时间成线性关系，每分钟插入的数据量平均在1.3MB左右。

表2.1单机节点和存储过程insertToemployee插入表

时间（分钟）	记录数	数据量(MB）
1	2645	1.29
2	5332	2.60
5	13657	6.67
10	28122	13.73

2.2 网络节点和insertToemployee的插入

        利用网络节点（应用和MySQL服务器运行于两台计算机上）和存储过程insertToemployee进行数据的插入，实验结果如表2. 2所示。从中我们可以看出，插入数据量基本与时间成线性关系，每分钟插入的数据量平均在1.3MB左右；同时，与2.1的实验相比，网络节点间的插入和单机节点间的插入性能差不多（甚至利用网络更好一点，可能是单机节点情况下，要进行应用进程和MySQL服务器进程之间的调度所花费的开销），证明网络带宽并不是性能的瓶颈。

表2.2网络节点和存储过程insertToemployee插入表

时间（分钟）	记录数（行）	数据量(MB）
1	2788	1.36
2	5446	2.66
5	14002	6.84
10	26461	12.92

2.3 单机节点和insertToemployee2的插入

        利用单机节点和存储过程insertToemployee2（详见3.5）进行数据的插入，实验结果如表2. 3所示。

表2.3单机节点和存储过程insertToemployee2插入表

时间（分钟）	记录数	数据量(MB）
1	5300	2.58
2	11156	5.44
5	27634	13.50
10	54100	26.42

2.4 网络节点和insertToemployee2的插入

        利用网络节点和存储过程insertToemployee2进行数据的插入，实验结果如表2. 4所示。

表2.4网络节点和存储过程insertToemployee2插入表

时间（分钟）	记录数	数据量(MB）
1	5516	2.70
2	11136	5.44
5	27158	13.26
10	54158	26.44

        根据实验2.3和2.4，我们可以看出，应用一次向MYSQL数据库发送一条插入操作和两条插入操作，吞吐量基本上得到了两倍的提升，所以这说明应用和MySQL数据库之间的连接开销（JDBC）是MySQL数据库插入操作的性能瓶颈。所以我们可以通过继续提升JDBC的效率来提高MySQL数据库的吞吐量。

2.5 网络节点和insertToemployee20的插入

        由于JDBC是系统性能的瓶颈，所以现在研究一下，每次插入20条数据系统的吞吐量。从表2. 5中可以看出，MySQL能满足每分钟插入22MB数据的要求。吞吐量与每次插入的条数基本上成线性关系，说明每次插入20条数据时（数据量为10KB），JDBC仍然是系统性能的瓶颈所在。

        所以，仍然可以继续通过提升JDBC的效率（如增加每次插入记录的条数），来增加系统的性能，直到JDBC不再是系统的性能瓶颈。

表2.5网络节点和存储过程insertToemployee20插入表

时间（分钟）	记录数	数据量(MB）
1	45520	22.23
2	101840	49.72
5	228260	111.45
10	475060	231.96

2.6 网络节点和insertToemployee40的插入

        所以，继续增加一次通信插入的数据量，这次使每次插入40条记录，试验结果如表2. 6所示。

表2.6网络节点和存储过程insertToemployee40插入表

时间（分钟）	记录数	数据量(MB）
1	96000	46.88
2	197920	96.64
5	368960	180.16
10	942360	460.14

        从表2. 6中可以看出，每次插入40条记录，系统能满足每分钟插入46MB数据的要求。性能仍然得到了线性的增长，说明此时JDBC仍然是系统性能的瓶颈。所以，仍然可以继续增加每次插入记录的条数，用以增加系统的性能，直到JDBC不再是系统的性能瓶颈，由于篇幅所限，继续提升JDBC效率的实验暂做到这里。

2.7 网络节点和insertToemployee的并发（多线程）插入

        利用网络节点和存储过程insertToemployee进行数据的并发插入，用两个线程分别插入数据，得到实验结果如表2. 7所示。

表2.7网络节点和存储过程insertToemployee的多线程插入表

        可以看出，多线程并不增加系统的吞吐量，所以多线程并不是系统性能瓶颈所在。

2.8 网络节点和insertToemployee的并发（多进程）插入

表2. 8网络节点和存储过程insertToemployee的多进程插入表

        可以看出，多进程并不增加系统的吞吐量，所以多进程并不是系统性能瓶颈的所在。究其原因有可能是因为插入的是同一个表，数据库对其进行了加锁，所以多个连接只能顺序的插入，是否如此详见2.9实验。

2.9 向不同的表同时插入数据

        利用网络节点和存储过程insertToemployee和存储过程insertTostudent（详见3.5）进行数据的并发（多进程）插入，process1向student表插入数据，process2向employee表插入数据（student表和employee表除了表名不一样外，其他都一样），得到的实验结果如表2. 7所示。

表2.9不同的表同时插入数据实验

        可以看出，系统的吞吐量并没有得到提升，所以数据库并没有对表进行加锁，是MySQL自身的吞吐量限制了吞吐量的提升。

2.10 各阶段开销

        我们首先研究下不同阶段所花费的时间为多少，进而为我们优化数据库应用铺平道路。一条数据从应用开始到最终被插入在数据库中，我们粗浅的把它分为三个阶段：数据准备时间、连接时间、插入时间。

表2.10两分钟各阶段执行次数

存储过程	准备数据	准备数据+连接	准备数据+连接+执行插入操作
insertToemployee	21131919	158064	5380
insertToemployee2	21429164	298310	8360

        从表2. 10，我们可以得到下表。表2. 11表示每次所花费的时间。

表2.11各阶段每次迭代执行时间（ms）

	准备数据	准备数据+连接	准备数据+连接+执行插入操作
insertToemployee	0.00567	0.75758	22.02643
insertToemployee2	0.01120	0.80453	28.70813

        从表2. 11，我们可以得到每个阶段所花费的时间，如下表。

表2. 12各阶段每次执行时间（ms）

	准备数据	连接	执行插入操作
insertToemployee	0.00567	0.75191	21.26885
insertToemployee2	0.01120	0.79333	27.90360

去除准备数据花费的时间后，对连接和执行插入操作进行归一化处理。

表2. 12各阶段所占开销百分比

操作	时间百分比
	insertToemployee	insertToemployee2
连接	3.41%	2.76%
插入操作	96.59%	97.24%

        可以看到，随着每次插入条数的增多，连接所占花费的百分比就下降了。因为虽然每次传输两条记录比每次传输一条记录（0.79333>0.75191）所用的时间更长，但是与插入操作（27.90360>21.26885）相比，连接所花费的时间增长相对较慢，这样就充分的利用了JDBC。

 

 

第三章 实验环境

3.1 节点配置

3.2 数据库环境

3.3 局域网

        传输速率为100Mbps的以太网。根据局域网一般的效率而言，大概能维持在600MB/Min。

3.4 数据库表

CREATE TABLE employee

 (

id int(8),

id2 decimal(20,4),

name char(40),

name2 varchar(200),

name3 char(200)

) ;

employee表中每条记录大约为500字节的数据量。

 

CREATE TABLE student

 (

id int(8),

id2 decimal(20,4),

name char(40),

name2 varchar(200),

name3 char(200)

) ;

student表中每条记录大约为500字节的数据量。

3.5 存储过程

#insertToemployee存储过程每次插入一条数据

delimiter //

create procedure insertToemployee

(in id int(8),in id2 decimal(20,4),in name char(40),in name2 char(200),in name3 char(200))

begin

insert into employee values(id,id2,name,name2,name3);

end

//

 

#insertToemployee2存储过程每次插入两条数据

delimiter //

create procedure insertToemployee2

(

in id int(8),in id2 decimal(20,4),in name char(40),in name2 char(200),in name3 char(200),

in ids int(8),in id2s decimal(20,4),in names char(40),in name2s char(200),in name3s char(200)

)

begin

insert into employee values(id,id2,name,name2,name3),(ids,id2s,names,name2s,name3s);

end

//

存储过程insertToemployee20和存储过程insertToemployee40与insertToemployee2类似，这里为了减少篇幅，不再赘述。

 

版权所有，转载请注明出处http://blog.csdn.net/hongchangfirst