一、前言

二、概述

三、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源


[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).同步时间



[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方式要好。



[root@node7 src]# yum install -y cmake



4.准备mysql数据目录 



[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.安装相关依赖包



[root@node7 ~]# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*



6.安装mysql 



[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配置成功了，下面我们进行编译安装。



[root@node7 mysql-5.5.35]# make && make install



7.为mysql提供配置文件



[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提供启动脚本 



[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



[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



[root@node7 mysql-5.5.35]# service mysqld start 
Starting MySQL... SUCCESS!



11.尝试登录一下



[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命令的查找路径



[root@node7 ~]# vim /etc/man.config
MANPATH /usr/local/mysql/man



13.输出mysql的头文件至系统头文件路径/usr/include



[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的库文件给系统库查找路径



[root@node7 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf  
[root@node7 ~]# ldconfig



15.修改PATH环境变量，让系统可以直接使用mysql的相关命令



[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测试库



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引擎



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.批量插入数据



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引擎）



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引擎）



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引擎）



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引擎）



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引擎）



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引擎）



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引擎）



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引擎）



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版本升了吧。最后，希望大家有所收获吧^_^……



转载于:https://blog.51cto.com/freeloda/1348580