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

文章由LinuxBoy分享于2019-03-30 03:03:16热评（490）

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性能测试对比，不清楚的博友可以参考一下上两篇文章：和，在这一篇文章中我们主要测试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安装包。
解决方法，
1 [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.

到这里我们编译配置就完成了，下面我们编译并安装。
1 [root@node6 mysql-5.1.73]# make && make install

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

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