MySQL5.1 MyISAM与InnoDB 引擎读写性能对比

太阳不下山

　　一、前言
　　二、概述
　　三、100 万数据性能测试
　　四、200 万数据性能测试
　　五、500 万数据性能测试
　　六、1000 万数据性能测试
　　七、总结
　　注，测试环境 CentOS 6.4 x86_64，软件版本 MySQL 5.1.73 (MySQL 5.1最新版)，下载地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。
　　

　　一、前言
　　在上两篇博客中我们测试了，MySQL5.5对多核CPU支持、Mysql 5.1与MySQL5.5性能测试对比，不清楚的博友可以参考一下上两篇博客：http://freeloda.blog.51cto.com/2033581/1348385 和 http://freeloda.blog.51cto.com/2033581/1348385，在这一篇博客中我们主要测试MySQL 5.1 MyISAM与InnoDB 引擎读写性能对比。
　　

　　二、概述
　　1.环境准备
　　(1).安装yum源

[root@node6 src]# wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@node6 src]# rpm -ivh epel-release-6-8.noarch.rpm

　　(2).同步时间(系统与硬件)

[root@node6 src]# yum install -y ntp
[root@node6 src]# ntpdate 202.120.2.101
[root@node6 src]# hwclock -w

　　2.安装mysql 5.1

[root@node6 mysql-5.1.73]# tar xf mysql-5.1.73.tar.gz
[root@node6 mysql-5.1.73]# cd mysql-5.1.73
[root@node6 mysql-5.1.73]# ./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql --enable-assembler --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-pthread --enable-static --with-big-tables --without-ndb-debug --with-charset=utf8 --with-extra-charsets=all --without-debug --enable-thread-safe-client --enable-local-infile --with-plugins=max

　　出错1：checking for termcap functions library... configure: error: No curses/termcap library found。
　　原因：缺少ncurses安装包。
　　解决方法，

[root@node6 mysql-5.1.73]# yum -y install ncurses ncurses-devel

　　下面继续，

[root@node6 mysql-5.1.73]# ./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql --enable-assembler --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-pthread --enable-static --with-big-tables --without-ndb-debug --with-charset=utf8 --with-extra-charsets=all --without-debug --enable-thread-safe-client --enable-local-infile --with-plugins=max

　　上面配置内容省略……

This version of MySQL Cluster is no longer maintained.  
Please use the separate sources provided for MySQL Cluster instead.   
See http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html
for more details.
Thank you for choosing MySQL!
Remember to check the platform specific part of the reference manual  
for hints about installing MySQL on your platform.   
Also have a look at the files in the Docs directory.

　　到这里我们编译配置就完成了，下面我们编译并安装。

[root@node6 mysql-5.1.73]# make && make install

　　注，编译与安装时间比较长请大家耐心等待，当然会看各位博友机器的配置，相对来说配置越好，相对的编译与安装时间相对就少。
　　3.创建数据目录并授权

[root@node6 mysql-5.1.73]# mkdir -pv /data/mysql  
mkdir: 已创建目录 "/data/mysql"   
[root@node6 mysql-5.1.73]# useradd mysql   
[root@node6 mysql-5.1.73]# chown mysql.mysql /data/mysql/   
[root@node6 mysql-5.1.73]# ll /data/   
总用量 20   
drwx------. 2 root  root  16384 8月  17 18:42 lost+found   
drwxr-xr-x. 2 mysql mysql  4096 1月   4 16:10 mysql

　　4.为mysql提供配置文件
　　

[root@node6 mysql-5.1.73]# cp support-files/my-huge.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y

　　5.简单修改一下配置文件

[root@node6 mysql-5.1.73]# vim /etc/my.cnf
[client]
default-character-set = utf8
[mysqld]  
default-character-set = utf8   
datadir    = /data/mysql

　　6.提供启动脚本

[root@node6 mysql-5.1.73]# cp support-files/mysql.server /etc/init.d/mysqld   
[root@node6 mysql-5.1.73]# chmod +x /etc/init.d/mysqld
[root@node6 ~]# chkconfig mysqld --add   
[root@node6 ~]# chkconfig mysqld on

　　7.初始化mysql

[root@node6 mysql-5.1.73]# /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql  
Installing MySQL system tables...   
140104 16:18:43 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.   
140104 16:18:43 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.   
OK   
Filling help tables...   
140104 16:18:43 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.   
140104 16:18:43 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.   
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 node6.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!

　　注，从上面的内容中我们看到了几个警告，我们查看一下。
　　

140104 16:18:43 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. 
140104 16:18:43 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 
OK 
Filling help tables... 
140104 16:18:43 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. 
140104 16:18:43 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

　　

　　从上面的警告可以看到，--default-character-set、--skip-locking选项已经过时，建议使用--character-set-server、--skip-external-locking。
　　8.查看一下初始化目录

[root@node6 data]# ls /data/mysql/  
mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  test

　　9.启动一下mysql

[root@node6 ~]# service mysqld start  
Starting MySQL.. SUCCESS!

　　10.测试访问一下

[root@node6 ~]# /usr/local/mysql/bin/mysql  
Welcome to the MySQL monitor.  Commands end with ; or \g.   
Your MySQL connection id is 1   
Server version: 5.1.73-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        |   
| test         |   
+--------------------+   
3 rows in set (0.00 sec)
mysql>

　　好了，到这里我们的mysql基本就安装完成，但我们还提做上些优化工作。
　　11.输出mysql的man手册至man命令的查找路径

[root@node6 ~]# yum install -y man
[root@node6 ~]# vim /etc/man.config
MANPATH  /usr/local/mysql/man

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

[root@node6 mysql]# ln -sv /usr/local/mysql/include  /usr/include/mysql

　　13.输出mysql的库文件给系统库查找路径
　　

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

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

[root@node6 mysql]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@node6 mysql]# source /etc/profile  
[root@node6 mysql]# mysql   
Welcome to the MySQL monitor.  Commands end with ; or \g.   
Your MySQL connection id is 2   
Server version: 5.1.73-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就全部安装完成了，下面我们来准备一下测试环境。
　　15.新建测试库与测试表

mysql> CREATE DATABASE mydb;  
Query OK, 1 row affected (0.33 sec)
mysql> SHOW DATABASES;  
+--------------------+   
| Database       |   
+--------------------+   
| information_schema |   
| mydb         |   
| mysql        |   
| test         |   
+--------------------+   
4 rows in set (0.00 sec)
mysql> use mydb;  
Database changed
mysql> show create database mydb;  
+----------+---------------------------------------------------------------+   
| Database | Create Database                         |   
+----------+---------------------------------------------------------------+   
| mydb   | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |   
+----------+---------------------------------------------------------------+   
1 row in set (0.00 sec)
mysql> show tables;   
Empty set (0.01 sec)
mysql> CREATE TABLE `t1` (id int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;  
Query OK, 0 rows affected (0.03 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 tables;  
+----------------+   
| Tables_in_mydb |   
+----------------+   
| t1       |   
+----------------+   
1 row in set (0.00 sec)
mysql> desc t1;  
+-------+---------+------+-----+---------+-------+   
| Field | Type  | Null | Key | Default | Extra |   
+-------+---------+------+-----+---------+-------+   
| id  | int(11) | YES  |   | NULL  |     |   
+-------+---------+------+-----+---------+-------+   
1 row in set (0.33 sec)

　　16.准备1000万数据
　　注，现在我们要测试的表是空表，下面我们要用下面的方法，快速插入1000万数据。
　　(1).先简单插入10行数据，

mysql>  insert into t1  value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

　　(2).查看一下

mysql> select * from t1;  
+------+   
| id   |   
+------+   
|  1 |   
|  2 |   
|  3 |   
|  4 |   
|  5 |   
|  6 |   
|  7 |   
|  8 |   
|  9 |   
|   10 |   
+------+   
10 rows in set (0.00 sec)

　　(3).插入多行数据方法

mysql> insert into t1 select * from t1;  
Query OK, 10 rows affected (0.00 sec)   
Records: 10  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;  
+----------+   
| count(*) |   
+----------+   
|     20 |   
+----------+   
1 row in set (0.33 sec)

　　大家可以看到现在我们有20行数据，使用 insert into t1 select * from t1 这样的插入数据方法，数据不但可以成倍增长，而且增加的数据可控，方便我们测试100万数据、200万数据、500万数据，以及更多数据测试。下面我们就开始数据读写测试。
　　

　　三、100 万数据性能测试
　　1.查看一下创建的表

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)

　　注，我们先测试一下MyISAM引擎。
　　2.查看一下表结构

mysql> desc t1;  
+-------+---------+------+-----+---------+-------+   
| Field | Type  | Null | Key | Default | Extra |   
+-------+---------+------+-----+---------+-------+   
| id  | int(11) | YES  |   | NULL  |     |   
+-------+---------+------+-----+---------+-------+   
1 row in set (0.01 sec)

　　3.下面插入数据100万行
　　(1).先插入10行数据

mysql>  insert into t1  value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 mysql> select * from t1; 
+------+ 
| id   | 
+------+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  6 | 
|  7 | 
|  8 | 
|  9 | 
|   10 | 
+------+ 
10 rows in set (0.00 sec)

　　(2).下面我们正来继续插入数据

mysql> insert into t1 select * from t1;  
Query OK, 1310720 rows affected (0.64 sec)   
Records: 1310720  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;  
+----------+   
| count(*) |   
+----------+   
|  2621440 |   
+----------+   
1 row in set (0.00 sec)

　　注，大家可以看到插入100万行的数据只用了0.64秒，查询几乎不花时间。
　　4.下面我们来新建一下t2表，并设置默认引擎为InnoDB;

mysql> CREATE TABLE `t2` ( id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
Query OK, 0 rows affected (0.36 sec)
mysql> show tables;  
+----------------+   
| Tables_in_mydb |   
+----------------+   
| t1       |   
| t2       |   
+----------------+   
2 rows 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)
mysql> desc t2;  
+-------+---------+------+-----+---------+-------+   
| Field | Type  | Null | Key | Default | Extra |   
+-------+---------+------+-----+---------+-------+   
| id  | int(11) | YES  |   | NULL  |     |   
+-------+---------+------+-----+---------+-------+   
1 row in set (0.01 sec)

　　5.下面我们插入数据并进行查询测试

mysql> insert into t2 select * from t2;  
Query OK, 1310720 rows affected (13.03 sec)   
Records: 1310720  Duplicates: 0  Warnings: 0
mysql> select count(*) from t2;  
+----------+   
| count(*) |   
+----------+   
|  2621440 |   
+----------+   
1 row in set (1.20 sec)

　　6.总结
　　(1).MyISAM 引擎

• 　　插入100多万行数据花费的时间为 0.64 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 0.00 秒。
  

　　(2).InnoDB 引擎

• 　　插入100多万行数据花费的时间为 13.03 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 1.20 秒。
  

　　下面我们继续测试……
　　

　　四、200 万数据性能测试
　　1.t1表插入并查询数据

mysql> insert into t1 select * from t1;  
Query OK, 2621440 rows affected (1.23 sec)   
Records: 2621440  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;  
+----------+   
| count(*) |   
+----------+   
|  5242880 |   
+----------+   
1 row in set (0.00 sec)

　　2.t2表插入并查询数据

mysql> insert into t2 select * from t2;  
Query OK, 2621440 rows affected (26.32 sec)   
Records: 2621440  Duplicates: 0  Warnings: 0
mysql> select count(*) from t2;  
+----------+   
| count(*) |   
+----------+   
|  5242880 |   
+----------+   
1 row in set (2.08 sec)

　　3.总结
　　(1).MyISAM 引擎

• 　　插入200多万行数据花费的时间为 1.23 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 0.00 秒。
  

　　(2).InnoDB 引擎

• 　　插入200多万行数据花费的时间为 26.32 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 2.08 秒。
  

　　

　　五、500 万数据性能测试
　　1.t1表插入并查询数据

mysql> insert into t1 select * from t1;  
Query OK, 5242880 rows affected (2.35 sec)   
Records: 5242880  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;  
+----------+   
| count(*) |   
+----------+   
| 10485760 |   
+----------+   
1 row in set (0.00 sec)

　　2.t2表插入并查询数据

mysql> insert into t2 select * from t2;  
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

　　注，大家可以看到Innodb引擎插入500多万行数据直接报错。
　　原因：InnoDB表执行大批量数据的更新，插入，删除操作时会出现这个问题，需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题，并且重启mysql服务。
　　查看一下默认的innodb_buffer_pool_size默认为8M，

mysql> show variables like "innodb_buffer_pool_size";  
+-------------------------+---------+   
| Variable_name       | Value   |   
+-------------------------+---------+   
| innodb_buffer_pool_size | 8388608 |   
+-------------------------+---------+   
1 row in set (0.00 sec)

　　下面我们修改一下并重启mysql，

[root@node6 ~]# vim /etc/my.cnf
innodb_buffer_pool_size = 384M
[root@node6 ~]# service mysqld restart  
Shutting down MySQL..... SUCCESS!  
Starting MySQL. SUCCESS!
mysql> show variables like "innodb_buffer_pool_size";  
+-------------------------+-----------+   
| Variable_name       | Value   |   
+-------------------------+-----------+   
| innodb_buffer_pool_size | 402653184 |   
+-------------------------+-----------+   
1 row in set (0.00 sec)

　　好了，问题解决下面我们继续测试。

mysql> insert into t2 select * from t2;  
Query OK, 5242880 rows affected (52.81 sec)   
Records: 5242880  Duplicates: 0  Warnings: 0
mysql> select count(*) from t2;  
+----------+   
| count(*) |   
+----------+   
| 10485760 |   
+----------+   
1 row in set (6.63 sec)

　　3.总结
　　(1).MyISAM 引擎

• 　　插入500多万行数据花费的时间为2.35秒。
  
• 　　用select count(*) from t1 命令查询的时间为 0.00 秒。
  

　　(2).InnoDB 引擎

• 　　插入500多万行数据花费的时间为 52.81 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 6.63 秒。
  

　　

　　六、 1000 万数据性能测试
　　1.t1表插入并查询数据

mysql> insert into t1 select * from t1;  
Query OK, 10485760 rows affected (8.47 sec)   
Records: 10485760  Duplicates: 0  Warnings: 0
mysql> select count(*) from t1;  
+----------+   
| count(*) |   
+----------+   
| 20971520 |   
+----------+   
1 row in set (0.07 sec)

　　2.t2表插入并查询数据

mysql> insert into t2 select * from t2;  
Query OK, 10485760 rows affected (1 min 53.02 sec)   
Records: 10485760  Duplicates: 0  Warnings: 0
mysql> select count(*) from t2;  
+----------+   
| count(*) |   
+----------+   
| 20971520 |   
+----------+   
1 row in set (18.50 sec)

　　3.总结
　　(1).MyISAM 引擎

• 　　插入1000多万行数据花费的时间为 8.47 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 0.07 秒。
  

　　(2).InnoDB 引擎

• 　　插入1000多万行数据花费的时间为 53.02 秒。
  
• 　　用select count(*) from t1 命令查询的时间为 18.50 秒。
  

　　

　　七、总结
　　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

　　注，从插入数据时间和查看数据时间这两个数据来看，可以说MyISAM引擎是完胜InnoDB引擎。好了，这次的博客就到这里了。最后，希望大家有所收获^_^……