一、前言
二、概述
三、100 万数据性能测试
四、200 万数据性能测试
五、500 万数据性能测试
六、1000 万数据性能测试
七、总结
八、MySQL 5.1与MySQL 5.5 MyISAM与InnoDB引擎对比
注，测试环境 CentOS 6.4 x86_64，软件版本 MySQL 5.5.35 (MySQL 5.5最新版)，下载地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。
一、前言
在上一篇博客中我们测试了MySQL 5.5 的TPS与rw request /s性能指标与MySQL5.1相比性能有了较大的提升，不清楚的博友可以查看一下这篇博客http://freeloda.blog.51cto.com/2033581/1348390。在后面的博客中我们又测试一下MySQL 5.1插入数据与查询数据的性能，从测试结果上看MyISAM引擎的性能要优于InnoDB引擎，不清楚的博友可以查看一下这篇博客http://freeloda.blog.51cto.com/2033581/1348507。在这篇博客中我们主要来测试一下MySQL 5.5的MyISAM引擎与InnoDB引擎的插入数据与查询数据的性能并与MySQL 5.1进行对比，看看MySQL 5.5 对MyISAM与InnoDB引擎改进多少。
二、概述
1.环境准备
(1).安装yum源
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@node7 src]
# wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm
--2014-01-05 17:37:53-- http:
//mirrors
.hustunique.com
/epel/6/x86_64/epel-release-6-8
.noarch.rpm
正在解析主机 mirrors.hustunique.com... 115.156.219.152
正在连接 mirrors.hustunique.com|115.156.219.152|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：14540 (14K) [application
/x-redhat-package-manager
]
正在保存至: “epel-release-6-8.noarch.rpm”
100%[===============================================================================>] 14,540 --.-K
/s
in
0.09s
2014-01-05 17:37:54 (165 KB
/s
) - 已保存 “epel-release-6-8.noarch.rpm” [14540
/14540
])
[root@node7 src]
# rpm -ivh epel-release-6-8.noarch.rpm
warning: epel-release-6-8.noarch.rpm: Header V3 RSA
/SHA256
Signature, key ID 0608b895: NOKEY
Preparing...
########################################### [100%]
1:epel-release
########################################### [100%]
|
(2).同步时间
|
1
2
3
|
[root@node7 src]
# yum install -y ntp vim man
[root@node7 src]
# ntpdate 202.120.2.101
[root@node7 src]
# hwclock -w
|
3.安装cmake
注，mysql 5.5 以后编译安装都用cmake。在make与make install的时候可以看到进度百分比，感觉这一点要比configure方式要好。
|
1
|
[root@node7 src]
# yum install -y cmake
|
4.准备mysql数据目录
|
1
2
3
4
5
6
7
8
|
[root@node7 data]
# mkdir -pv /data/mysql
mkdir
: 已创建目录
"/data/mysql"
[root@node7 data]
# useradd mysql
[root@node7 data]
# chown mysql.mysql /data/mysql/
[root@node7 data]
# ll /data/
总用量 20
drwx------. 2 root root 16384 8月 17 18:42 lost+found
drwxr-xr-x. 2 mysql mysql 4096 1月 5 09:47 mysql
|
5.安装相关依赖包
|
1
|
[root@node7 ~]
# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*
|
6.安装mysql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@node7 src]
# tar xf mysql-5.5.35.tar.gz
[root@node7 src]
# cd mysql-5.5.35
[root@node7 mysql-5.5.35]
# ls
BUILD cmd-line-utils Docs libmysql mysys scripts strings vio
BUILD-CMAKE config.h.cmake extra libmysqld packaging sql support-files win
client configure.cmake include libservices plugin sql-bench tests zlib
cmake COPYING INSTALL-SOURCE
man
README sql-common unittest
CMakeLists.txt dbug INSTALL-WIN-SOURCE mysql-
test
regex storage VERSION
[root@node7 mysql-5.5.35]
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/data/mysql -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1
上面内容省略……
-- Configuring
done
-- Generating
done
-- Build files have been written to:
/root/src/mysql-5
.5.35
|
注，看到这几行就说明我们的cmake配置成功了，下面我们进行编译安装。
|
1
|
[root@node7 mysql-5.5.35]
# make && make install
|
7.为mysql提供配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@node7 mysql-5.5.35]
# cp support-files/my-huge.cnf /etc/my.cnf
cp
：是否覆盖
"/etc/my.cnf"
？ y
[root@node7 mysql-5.5.35]
# vim /etc/my.cnf
#增加下面几行配置文件
[client]
default-character-
set
= utf8
[mysqld]
character-
set
-server = utf8
collation-server = utf8_unicode_ci
datadir =
/data/mysql
innodb_buffer_pool_size = 384M
innodb_file_per_table=1
|
8.为mysql提供启动脚本
|
1
2
3
4
|
[root@node7 mysql-5.5.35]
# cp support-files/mysql.server /etc/init.d/mysqld
[root@node7 mysql-5.5.35]
# chmod +x /etc/init.d/mysqld
[root@node7 mysql-5.5.35]
# chkconfig mysqld --add
[root@node7 mysql-5.5.35]
# chkconfig mysqld on
|
9.初始化mysql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@node7 mysql-5.5.35]
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot
time
you have to copy
support-files
/mysql
.server to the right place
for
your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To
do
so, start the server,
then
issue the following commands:
/usr/local/mysql//bin/mysqladmin
-u root password
'new-password'
/usr/local/mysql//bin/mysqladmin
-u root -h node7.
test
.com password
'new-password'
Alternatively you can run:
/usr/local/mysql//bin/mysql_secure_installation
which
will also give you the option of removing the
test
databases and anonymous user created by default. This is
strongly recommended
for
production servers.
See the manual
for
more
instructions.
You can start the MySQL daemon with:
cd
/usr/local/mysql/
;
/usr/local/mysql//bin/mysqld_safe
&
You can
test
the MySQL daemon with mysql-
test
-run.pl
cd
/usr/local/mysql//mysql-test
; perl mysql-
test
-run.pl
Please report any problems with the
/usr/local/mysql//scripts/mysqlbug
script!
|
10.启动mysql
|
1
2
|
[root@node7 mysql-5.5.35]
# service mysqld start
Starting MySQL... SUCCESS!
|
11.尝试登录一下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[root@node7 mysql-5.5.35]
# /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.5.35-log Source distribution
Copyright (c) 2000, 2013, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
test
|
+--------------------+
4 rows
in
set
(0.00 sec)
mysql>
|
12.输出mysql的man手册至man命令的查找路径
|
1
2
|
[root@node7 ~]
# vim /etc/man.config
MANPATH
/usr/local/mysql/man
|
13.输出mysql的头文件至系统头文件路径/usr/include
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@node7 ~]
# ln -sv /usr/local/mysql/include /usr/include/mysql
"/usr/include/mysql"
->
"/usr/local/mysql/include"
[root@node7 ~]
# cd /usr/include/mysql
[root@node7 mysql]
# ls
decimal.h my_dbug.h mysqld_ername.h plugin_ftparser.h
errmsg.h my_dir.h mysqld_error.h plugin.h
keycache.h my_getopt.h mysql_embed.h sql_common.h
m_ctype.h my_global.h mysql.h sql_state.h
m_string.h my_list.h mysql_time.h sslopt-
case
.h
my_alloc.h my_net.h mysql_version.h sslopt-longopts.h
my_attribute.h my_pthread.h my_sys.h sslopt-vars.h
my_compiler.h mysql my_xml.h typelib.h
my_config.h mysql_com.h plugin_audit.h
|
14.输出mysql的库文件给系统库查找路径
|
1
2
|
[root@node7 ~]
# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@node7 ~]
# ldconfig
|
15.修改PATH环境变量，让系统可以直接使用mysql的相关命令
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@node7 ~]
# vim /etc/profile.d/mysql.sh
export
PATH=$PATH:
/usr/local/mysql/bin/
[root@node7 ~]
# source /etc/profile
[root@node7 ~]
# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 2
Server version: 5.5.35-log Source distribution
Copyright (c) 2000, 2013, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
好了，到这里我们的mysql就全部安装完成了，下面我们来准备一下测试环境。
16.新建mydb测试库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
|
test
|
+--------------------+
5 rows
in
set
(0.00 sec)
mysql> show create database mydb;
+----------+---------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------+
| mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+---------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
17.新建两测试表t1与t2，t1为MyISAM引擎、t2为InnoDB引擎
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> use mydb;
Database changed
mysql> show tables;
Empty
set
(0.00 sec)
mysql> CREATE TABLE `t1` (
id
int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `t2` (
id
int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows
in
set
(0.00 sec)
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`
id
` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`
id
` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
18.批量插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> insert into t1 value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into t2 value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into t1
select
* from t1;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into t2
select
* from t2;
Query OK, 10 rows affected (0.10 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t1;
+----------+
| count(*) |
+----------+
| 20 |
+----------+
1 row
in
set
(0.00 sec)
mysql>
select
count(*) from t2;
+----------+
| count(*) |
+----------+
| 20 |
+----------+
1 row
in
set
(0.00 sec)
|
三、100 万数据性能测试
1.t1表插入并查询数据（MyISAM引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t1
select
* from t1;
Query OK, 1310720 rows affected (0.68 sec)
Records: 1310720 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t1;
+----------+
| count(*) |
+----------+
| 2621440 |
+----------+
1 row
in
set
(0.00 sec)
|
2.t2表插入并查询数据（InnoDB引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t2
select
* from t2;
Query OK, 1310720 rows affected (12.26 sec)
Records: 1310720 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t2;
+----------+
| count(*) |
+----------+
| 2621440 |
+----------+
1 row
in
set
(0.79 sec)
|
3.总结
(1).MyISAM 引擎
-
插入100多万行数据花费的时间为 0.68 秒。
-
用select count(*) from t1 命令查询的时间为 0.00 秒。
(2).InnoDB 引擎
-
插入100多万行数据花费的时间为 12.26 秒。
-
用select count(*) from t1 命令查询的时间为 0.79 秒。
四、200 万数据性能测试
1.t1表插入并查询数据（MyISAM引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t1
select
* from t1;
Query OK, 2621440 rows affected (1.18 sec)
Records: 2621440 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t1;
+----------+
| count(*) |
+----------+
| 5242880 |
+----------+
1 row
in
set
(0.00 sec)
|
2.t2表插入并查询数据（InnoDB引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t2
select
* from t2;
Query OK, 2621440 rows affected (26.13 sec)
Records: 2621440 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t2;
+----------+
| count(*) |
+----------+
| 5242880 |
+----------+
1 row
in
set
(1.59 sec)
|
3.总结
(1).MyISAM 引擎
-
插入200多万行数据花费的时间为 1.18 秒。
-
用select count(*) from t1 命令查询的时间为 0.00 秒。
(2).InnoDB 引擎
-
插入200多万行数据花费的时间为 26.13 秒。
-
用select count(*) from t1 命令查询的时间为 1.59 秒。
五、500 万数据性能测试
1.t1表插入并查询数据（MyISAM引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t1
select
* from t1;
Query OK, 5242880 rows affected (2.27 sec)
Records: 5242880 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t1;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row
in
set
(0.00 sec)
|
2.t2表插入并查询数据（InnoDB引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t2
select
* from t2;
Query OK, 5242880 rows affected (55.33 sec)
Records: 5242880 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t2;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row
in
set
(4.04 sec)
|
(1).MyISAM 引擎
-
插入500多万行数据花费的时间为 2.27 秒。
-
用select count(*) from t1 命令查询的时间为 0.00 秒。
(2).InnoDB 引擎
-
插入500多万行数据花费的时间为 55.33 秒。
-
用select count(*) from t1 命令查询的时间为 4.04 秒。
六、1000 万数据性能测试
1.t1表插入并查询数据（MyISAM引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t1
select
* from t1;
Query OK, 10485760 rows affected (6.57 sec)
Records: 10485760 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t1;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row
in
set
(0.09 sec)
|
2.t2表插入并查询数据（InnoDB引擎）
|
1
2
3
4
5
6
7
8
9
10
|
mysql> insert into t2
select
* from t2;
Query OK, 10485760 rows affected (1 min 50.43 sec)
Records: 10485760 Duplicates: 0 Warnings: 0
mysql>
select
count(*) from t2;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row
in
set
(11.36 sec)
|
(1).MyISAM 引擎
-
插入1000多万行数据花费的时间为 6.57 秒。
-
用select count(*) from t1 命令查询的时间为 0.09 秒。
(2).InnoDB 引擎
-
插入1000多万行数据花费的时间为 1 min 50.43 秒。
-
用select count(*) from t1 命令查询的时间为 11.36 秒。
七、总结
MyISAM 引擎
| 数据（单位/万） |
插入数据时间（单位/秒） |
查询数据时间（单位/秒） |
| 100 | 0.68 | 0.00 |
| 200 | 1.18 | 0.00 |
| 500 | 2.27 | 0.00 |
| 1000 | 6.57 | 0.09 |
InnoDB 引擎
| 数据（单位/万） |
插入数据时间（单位/秒） |
查询数据时间（单位/秒） |
| 100 | 12.26 | 0.79 |
| 200 | 26.13 | 1.59 |
| 500 | 55.33 | 4.04 |
| 1000 | 1 min 50.43 | 11.36 |
八、MySQL 5.1与MySQL 5.5 MyISAM与InnoDB引擎对比
1.MySQL 5.1
MyISAM 引擎
| 数据（单位/万） | 插入数据时间（单位/秒） | 查看数据时间（单位/秒） |
| 100 | 0.64 | 0.00 |
| 200 | 1.23 | 0.00 |
| 500 | 2.35 | 0.00 |
| 10000 | 8.47 | 0.07 |
InnoDB 引擎
| 数据（单位/万） | 插入数据时间（单位/秒） | 查看数据时间（单位/秒） |
| 100 | 13.03 | 1.20 |
| 200 | 26.32 | 2.08 |
| 500 | 52.81 | 6.63 |
| 1000 | 53.02 | 18.50 |
2.总结
经过上面的表格结比相信大家对，MySQL 5.1与MySQL 5.5 MyISAM引擎与InnoDB引擎的读写性能有所了解，MySQL 5.5 的InnoDB引擎在1000万行以内的性能有明显提升。总体来说不管是引擎的读写性能还是TPS与rw request /s性能，MySQL 5.5的性能都有很大的提升，还是那句话找个稳定的5.5版本升了吧。最后，希望大家有所收获吧^_^……
本文转自陈明乾51CTO博客，原文链接：http://blog.51cto.com/freeloda/1348580，如需转载请自行联系原作者
