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引擎。好了，這次的部落格就到這裡了。最後，希望大家有所收穫^_^……