【IT168专稿】写在前面的话：在之前的文章中，我们已经评测了大部分主流的行业数据库软件，对其性能的优劣也做出了尽量客观的阐述，对此感兴趣的朋友可以搜索“数据库评测”来查看其他的评测文章。这里，我们将继续行式数据库的评测，在本篇文章中，将为大家评测MySQL 5.5，希望本篇评测文章能对MySQL DBA或是MySQL爱好者有所帮助。
近年来，MySQL凭借其性能高、成本低、可靠性好等等特点已成为最流行的开源数据库，被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。
MySQL支持Windows、Linux、Solaris、Mac OS X和FreeBSD等操作系统平台。版本包括免费开源的Community版(社区版)和收费的Enterprise版(企业版)。第一个比较成熟的版本是2001年推出的3.23版，以后依次是4.0(增加了union)、4.1(增加了子查询)、5.0(增加了游标、存储过程、视图)、5.1(增加了分区)，由于Oracle公司收购了事务存储引擎innodb公司，导致Sun收购MySQL后在2009年曾经推出6.0 alpha版和新的存储引擎Falcon，在Oracle收购Sun后，6.0版已停止开发，版本号6和7目前专属于MySQL Cluster高可用版本，当前主流(GA)的版本是5.1和2010年推出的5.5(默认存储引擎innodb)，本文就是基于MySQL 5.5版展开评测的。
MySQL的可插拔存储引擎架构允许第3方厂商利用自行开发的存储引擎和MySQL进行捆绑，从而简化了开发流程并把关注点着重于特定功能的开发，以前列式数据库系列介绍过的Infobright和Calpont都是这一类产品。
一、数据库安装
MySQL在其官方网站http://dev.mysql.com/上提供了各种版本的下载，这些版本的界面语言都是英语，但通过选择字符集，可以处理简体中文数据。MySQL 5.5社区版（进入下载）没有任何限制，提供了完整的源代码。
MySQL的产品安装中提供了英文联机帮助文档，同时在网站http://dev.mysql.com/doc/refman/5.5/en/提供在线浏览。http://dev.mysql.com/doc/也有部分其它语言的文档，但简体中文只有5.1版的。建议下载http://downloads.mysql.com/docs/refman-5.5-en.a4.pdf，制作精美，具备搜索功能。
本次测试基于Intel Xeon 7550*8的PC服务器上用VMWare VSphere 4.1管理的虚拟机，虚拟机的逻辑 CPU个数是8，内存100GB，存储为8个300GB SAS本地磁盘，采用一块512M缓存RAID卡，按RAID5方式组成磁盘阵列。操作系统采用和RHEL 5相同的核心级别的RedFlag Asian Linux Sever 3.0 x64。因此选用的安装文件是Red Hat & Oracle Enterprise Linux 5 (x86, 64-bit), RPM Package版本，与多数数据库软件不同，MySQL的安装文件是按照功能模块分别打包的，通常情况下，只需要下载安装Server和client二个rpm包就能够正常运行和访问MySQL数据库了，devel模块包括编译用到的库文件和include文件，如果需要编译自己的访问MySQL模块，则也需要安装。我们这里安装这3个文件，总大小大约75MB。
安装步骤如下：
1.安装前准备工作
文档中介绍安装MySQL前，首先要创建mysql用户和组，但作为一款广受欢迎的软件，很多Linux发行版中都默认安装了MySQL, RedFlag Linux也不例外，因此，建用户的步骤可以忽略。
但正因为系统中已经安装过mysql,直接安装MySQL5.5报冲突：
[root@redflag11012501 user1]# ll My*-rw-r--r-- 1 root root 18191603 06-02 17:46 MySQL-client-5.5.13-1.rhel5.x86_64.rpm-rw-r--r-- 1 root root 3817269 06-02 17:47 MySQL-devel-5.5.13-1.rhel5.x86_64.rpm-rw-r--r-- 1 root root 53626402 06-02 17:39 MySQL-server-5.5.13-1.rhel5.x86_64.rpm[root@redflag11012501 user1]# rpm -ivh My*error: Failed dependencies: MySQL-devel conflicts with mysql-devel-5.0.77-4.2.1.AXS3.x86_64 MySQL-devel conflicts with mysql-devel-5.0.77-4.2.1.AXS3.i386 MySQL conflicts with mysql-5.0.77-4.2.1.AXS3.x86_64 MySQL conflicts with mysql-5.0.77-4.2.1.AXS3.i386 MySQL-server conflicts with mysql-server-5.0.77-4.2.1.AXS3.x86_64原来系统自带的MySQL是5.0版本的，那么首先卸载这个版本的几个冲突文件，仍然有问题，这些文件还被其它的文件，主要是连接MySQL的一些模块所引用。
[root@redflag11012501 user1]# rpm -ev mysql-server-5.0.77-4.2.1.AXS3.x86_64 mysql-5.0.77-4.2.1.AXS3.x86_64 mysql-devel-5.0.77-4.2.1.AXS3.x86_64 mysql-5.0.77-4.2.1.AXS3.i386 mysql-devel-5.0.77-4.2.1.AXS3.i386error: Failed dependencies: libmysqlclient.so.15()(64bit) is needed by (installed) php-mysql-5.1.6-27.0.1.AXS3.x86_64 libmysqlclient.so.15()(64bit) is needed by (installed) ... libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by (installed) dovecot-1.0.7-7.1AXS3.x86_64... libmysqlclient_r.so.15()(64bit) is needed by (installed) mysql-connector-odbc-3.51.26r1127-1.AXS3.x86_64... mysql is needed by (installed) MySQL-python-1.2.1-1.x86_64 mysql is needed by (installed) libdbi-dbd-mysql-0.8.1a-1.2.2.x86_64 mysql is needed by (installed) postfix-2.3.3-2.9AXS3.x86_64 mysql = 5.0.77-4.2.1.AXS3 is needed by (installed) mysql-bench-5.0.77-4.2.1.AXS3.x86_64 mysql is needed by (installed) freeradius-mysql-1.1.3-1.6.AXS3.x86_64 libmysqlclient.so.15 is needed by (installed) cyrus-sasl-sql-2.1.22-5.AXS3.3.i386 libmysqlclient.so.15(libmysqlclient_15) is needed by (installed) cyrus-sasl-sql-2.1.22-5.AXS3.3.i386因为不知道这些引用MySQL的文件又被那些文件使用，为了简化操作，使用rpm命令的nodeps参数强制卸载。卸载完成后再用rpm -ivh命令就可以安装成功了。
[root@redflag11012501 user1]# rpm -ev --nodeps mysql-server-5.0.77-4.2.1.AXS3.x86_64 mysql-5.0.77-4.2.1.AXS3.x86_64 mysql-devel-5.0.77-4.2.1.AXS3.x86_64 mysql-5.0.77-4.2.1.AXS3.i386 mysql-devel-5.0.77-4.2.1.AXS3.i386[root@redflag11012501 user1]# rpm -ivh My*Preparing... ########################################### [100%] 1:MySQL-server ########################################### [ 33%]PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/usr/bin/mysqladmin -u root password 'new-password'/usr/bin/mysqladmin -u root -h redflag11012501 password 'new-password'Alternatively you can run:/usr/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.Please report any problems with the /usr/bin/mysqlbug script! 2:MySQL-client ########################################### [ 67%] 3:MySQL-devel ########################################### [100%]安装完server包后，系统给出提示信息要求修改MySQL的root用户口令来保证数据库安全。这里我们仅仅用于测试，就不设置root口令了。
安装程序给出的提示信息相当有限，实际上，它将软件的不同部分安装到了如下目录。
安装完MySQL软件后服务器默认没有启动，需要用命令启动。启动的命令有多种，其中一种是用service 服务名 start启动。启动服务器后,就可以用mysql命令行工具连接了。
[root@redflag11012501 user1]# service mysql start Starting MySQL..[确定][root@redflag11012501 user1]# mysql -urootmysql: Unknown OS character set 'GB18030'.mysql: Switching to the default character set 'latin1'.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.13 MySQL Community Server (GPL)Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>使用rpm方式安装的MySQL,是不包含my.cnf配置文件的，可能在数据库文件存储位置、内存使用等方面不符合需要，因此我们需要定制配置文件，方法是从/usr/share/mysql复制一份配置文件，针对有关的项目进行修改。
[root@redflag11012501 mysql]# ls /usr/share/mysql/*cnf/usr/share/mysql/my-huge.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-small.cnf/usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf[root@redflag11012501 mysql]#cp /usr/share/mysql/my-innodb-heavy-4G.cnf /etc/my.cnf由于Linux的/文件系统空间有限，因此将数据文件目录改到空闲空间较多的/user1/mysql_db。用vi编辑/etc/my.cnf文件。另一种Linux和Unix下可用的方式是在数据库关闭时使用软连接，将数据库目录指向另一个目录。
innodb_data_file_path = ibdata1:1000M:autoextendinnodb_data_home_dir = /user1/mysql_dbinnodb_log_file_size = 256M修改后保存/etc/my.cnf文件，用mysqld命令重新启动服务器，结果报错：
[root@redflag11012501 mysql]# mysqld --user=root110603 10:29:28 [Note] Plugin 'FEDERATED' is disabled.110603 10:29:28 InnoDB: The InnoDB memory heap is disabled110603 10:29:28 InnoDB: Mutexes and rw_locks use GCC atomic builtins110603 10:29:28 InnoDB: Compressed tables use zlib 1.2.3110603 10:29:28 InnoDB: Using Linux native AIO110603 10:29:28 InnoDB: Initializing buffer pool, size = 20.0G110603 10:29:30 InnoDB: Completed initialization of buffer poolInnoDB: The first specified data file /user1/mysql_db/ibdata1 did not exist:InnoDB: a new database to be created!110603 10:29:30 InnoDB: Setting file /user1/mysql_db/ibdata1 size to 1000 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000110603 10:29:36 InnoDB: Error: all log files must be created at the same time.110603 10:29:36 InnoDB: All log files must be created also in database creation.110603 10:29:36 InnoDB: If you want bigger or smaller log files, shut down the110603 10:29:36 InnoDB: database and make sure there were no errors in shutdown.110603 10:29:36 InnoDB: Then delete the existing log files. Edit the .cnf file110603 10:29:36 InnoDB: and start the database again.110603 10:29:36 [ERROR] Plugin 'InnoDB' init function returned error.110603 10:29:36 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.110603 10:29:36 [Note] Event Scheduler: Loaded 0 events110603 10:29:36 [Note] mysqld: ready for connections.Version: '5.5.13-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)110603 10:30:10 [Note] mysqld: Normal shutdown110603 10:30:10 [Note] Event Scheduler: Purging the queue. 0 events110603 10:30:10 [Note] mysqld: Shutdown complete从提示信息可知，原有日志文件和新的数据文件不匹配，需要删除原有的文件。
删除所有数据文件和日志文件，
[root@redflag11012501 mysql]# ll总计 787260-rw-rw---- 1 root root 268435456 06-03 10:26 ib_logfile0-rw-rw---- 1 root root 268435456 06-03 10:26 ib_logfile1-rw-rw---- 1 root root 268435456 06-03 10:26 ib_logfile2drwx--x--x 2 mysql mysql 4096 06-02 17:59 mysql-rw-rw---- 1 root root 126 06-03 10:14 mysql-bin.000001-rw-rw---- 1 root root 126 06-03 10:24 mysql-bin.000002-rw-rw---- 1 root root 126 06-03 10:26 mysql-bin.000003-rw-rw---- 1 root root 126 06-03 10:30 mysql-bin.000004-rw-rw---- 1 root root 76 06-03 10:29 mysql-bin.indexdrwx------ 2 mysql mysql 4096 06-02 17:59 performance_schema-rw-rw---- 1 mysql root 2079 06-03 09:44 redflag11012501.err-rw-rw---- 1 root root 692 06-03 10:29 redflag11012501-slow.log-rw-r--r-- 1 root root 121 06-02 17:59 RPM_UPGRADE_HISTORY-rw-r--r-- 1 mysql mysql 121 06-02 17:59 RPM_UPGRADE_MARKER-LASTdrwxr-xr-x 2 mysql mysql 4096 06-02 17:59 test[root@redflag11012501 mysql]# rm ib_logfile*rm：是否删除 一般文件 “ib_logfile0”? trm：是否删除 一般文件 “ib_logfile1”? trm：是否删除 一般文件 “ib_logfile2”? t[root@redflag11012501 mysql]# rm mysql-bin*rm：是否删除 一般文件 “mysql-bin.000001”? trm：是否删除 一般文件 “mysql-bin.000002”? trm：是否删除 一般文件 “mysql-bin.000003”? trm：是否删除 一般文件 “mysql-bin.000004”? trm：是否删除 一般文件 “mysql-bin.index”? t[root@redflag11012501 mysql]# rm /user1/mysql_db/*rm：是否删除 一般文件 “/user1/mysql_db/ibdata1”? y同时将日志文件目录也修改，重新启动就可以了。
# Total number of files in the log group. A value of 2-3 is usually good# enough.innodb_log_files_in_group = 2# Location of the InnoDB log files. Default is the MySQL datadir. You# may wish to point it to a dedicated hard drive or a RAID1 volume for# improved performance#innodb_log_group_home_dirinnodb_log_group_home_dir = /user1/mysql_db# Maximum allowed percentage of dirty pages in the InnoDB buffer pool."/etc/my.cnf" 479L, 19863C 已写入 [root@redflag11012501 mysql]# rm /user1/mysql_db/*rm：是否删除 一般文件 “/user1/mysql_db/ibdata1”? y[root@redflag11012501 mysql]# mysqld --user=root110603 10:42:38 [Note] Plugin 'FEDERATED' is disabled.110603 10:42:38 InnoDB: The InnoDB memory heap is disabled110603 10:42:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins110603 10:42:38 InnoDB: Compressed tables use zlib 1.2.3110603 10:42:38 InnoDB: Using Linux native AIO110603 10:42:38 InnoDB: Initializing buffer pool, size = 20.0G110603 10:42:40 InnoDB: Completed initialization of buffer poolInnoDB: The first specified data file /user1/mysql_db/ibdata1 did not exist:InnoDB: a new database to be created!110603 10:42:40 InnoDB: Setting file /user1/mysql_db/ibdata1 size to 1000 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000110603 10:42:45 InnoDB: Log file /user1/mysql_db/ib_logfile0 did not exist: new to be createdInnoDB: Setting log file /user1/mysql_db/ib_logfile0 size to 256 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200110603 10:42:46 InnoDB: Log file /user1/mysql_db/ib_logfile1 did not exist: new to be createdInnoDB: Setting log file /user1/mysql_db/ib_logfile1 size to 256 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200InnoDB: Doublewrite buffer not found: creating newInnoDB: Doublewrite buffer createdInnoDB: 127 rollback segment(s) active.InnoDB: Creating foreign key constraint system tablesInnoDB: Foreign key constraint system tables created110603 10:42:47 InnoDB: Waiting for the background threads to start110603 10:42:48 InnoDB: 1.1.7 started; log sequence number 0110603 10:42:48 [Note] Event Scheduler: Loaded 0 events110603 10:42:48 [Note] mysqld: ready for connections.Version: '5.5.13-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)但是我们改用service mysql start启动时出错。
[root@redflag11012501 mysql]# service mysql startStarting MySQL.The server quit without updating PID file (/var/lib/mysql/redflag11012501.pid).[失败]通过查看err文件的错误信息提示，这是相关目录的权限问题。修改相关目录和文件的属主后问题解决。
[root@redflag11012501 tmp]# chown -R mysql:mysql /var/lib/mysql [root@redflag11012501 tmp]# service mysql startStarting MySQL...The server quit without updating PID file (/var/lib/mysql/redflag11012501.pid).[失败][root@redflag11012501 tmp]# tail /var/lib/mysql/redflag11012501.err 110608 11:21:22 InnoDB: Using Linux native AIO110608 11:21:22 InnoDB: Initializing buffer pool, size = 20.0G110608 11:21:24 InnoDB: Completed initialization of buffer pool110608 11:21:24 InnoDB: Operating system error number 13 in a file operation.InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.InnoDB: File name /user1/mysql_db/ibdata1InnoDB: File operation call: 'open'.InnoDB: Cannot continue operation.110608 11:21:24 mysqld_safe mysqld from pid file /var/lib/mysql/redflag11012501.pid ended[root@redflag11012501 tmp]# chown -R mysql:mysql /user1/mysql_db/[root@redflag11012501 tmp]# service mysql startStarting MySQL........The server quit without updating PID file (/var/lib/mysql/redflag11012501.pid).[失败]使用--user=root参数mysqld_safe可以启动[root@redflag11012501 tmp]# mysqld_safe --user=root110608 13:59:54 mysqld_safe Logging to '/var/lib/mysql/redflag11012501.err'.110608 13:59:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql110608 14:00:28 mysqld_safe mysqld from pid file /var/lib/mysql/redflag11012501.pid ended[root@redflag11012501 tmp]# 不使用--user=root参数mysqld_safe启动失败[root@redflag11012501 tmp]# mysqld_safe &[1] 8415[root@redflag11012501 tmp]# 110608 14:01:06 mysqld_safe Logging to '/var/lib/mysql/redflag11012501.err'.110608 14:01:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql[root@redflag11012501 tmp]# [root@redflag11012501 tmp]# 110608 14:01:13 mysqld_safe mysqld from pid file /var/lib/mysql/redflag11012501.pid ended[1]+ Done mysqld_safe[root@redflag11012501 tmp]# tail /var/lib/mysql/redflag11012501.err 110608 14:01:11 [ERROR] Failed to open log (file './mysql-bin.000018', errno 13)110608 14:01:11 [ERROR] Could not open log file110608 14:01:11 [ERROR] Can't init tc log110608 14:01:11 [ERROR] Aborting110608 14:01:11 InnoDB: Starting shutdown...110608 14:01:13 InnoDB: Shutdown completed; log sequence number 58674268879110608 14:01:13 [Note] /usr/sbin/mysqld: Shutdown complete110608 14:01:13 mysqld_safe mysqld from pid file /var/lib/mysql/redflag11012501.pid ended[root@redflag11012501 tmp]# cd /var/lib/mysql[root@redflag11012501 mysql]# ll总计 104drwx--x--x 2 mysql mysql 4096 06-02 17:59 mysql-rw-rw---- 1 mysql mysql 150 06-04 19:42 mysql-bin.000015-rw-rw---- 1 mysql mysql 677 06-08 11:03 mysql-bin.000016-rw-rw---- 1 root root 126 06-08 11:22 mysql-bin.000017-rw-rw---- 1 root root 126 06-08 14:00 mysql-bin.000018-rw-rw---- 1 mysql mysql 76 06-08 13:59 mysql-bin.indexdrwx------ 2 mysql mysql 4096 06-02 17:59 performance_schema-rw-rw---- 1 mysql mysql 9885 06-08 14:01 redflag11012501.err-rw-rw---- 1 mysql mysql 45588 06-08 14:01 redflag11012501-slow.log-rw-r--r-- 1 mysql mysql 121 06-02 17:59 RPM_UPGRADE_HISTORY-rw-r--r-- 1 mysql mysql 121 06-02 17:59 RPM_UPGRADE_MARKER-LASTdrwxr-xr-x 2 mysql mysql 4096 06-02 17:59 testdrwx------ 2 mysql mysql 4096 06-04 20:32 tpch--修改二进制日志文件的属主[root@redflag11012501 mysql]# chown mysql:mysql mysql-bin.*[root@redflag11012501 mysql]# service mysql startStarting MySQL.......[确定]另外，值得注意的，MySQL 5.5的默认存储引擎已经改为InnoDB,但样本配置文件中有这么一行：default-storage-engine = MYISAM，需要把它注释掉，否则默认创建的表存储引擎还是MyISAM，此外，如果数据库不需要主从复制，也可以通过注释log-bin=mysql-bin一行，将二进制日志功能关闭。
至此，安装过程结束。
二、MySQL功能简介
由于MySQL是非常流行的免费数据库管理系统，介绍它的资料也非常多，这里不准备罗列MySQL的功能，仅介绍和我们测试有关的几项功能。
1.创建数据库
MySQL的数据库实际上对应磁盘上一个目录，该目录下的.frm文件保存所有表的定义，而数据存储则随存储引擎的不同而不同,myisam的数据文件保存在同一个目录下，一个非分区表一个.myd数据文件，索引则存储在.myi后缀的文件中，如果是每个表独立存储的innodb引擎，每个表的数据和索引保存在一个与表同名的.ibd文件中，如果是共享存储的innodb引擎，所有数据库的innodb表和索引保存在一个.ibd数据文件中。
2.数据引擎
MySQL一个区别于其他数据库的特色功能就是可插拔式存储引擎。前面介绍了2种主要的存储引擎，myisam和innodb,前一个是mysql自有的，它不支持事务，因此也没有提交和回滚操作，不太能满足数据库的ACID的要求，一般只用于对数据库完整性要求不太高的场合和mysql系统表。Innodb原本是第3方的Innodb公司开发的，一度因版权原因退出MySQL的发行包，转而以插件形式提供，在经历多次并购以后，现在卷土重来成为了MySQL的默认存储引擎，它支持事务和外键约束，更符合ACID的要求，可以满足大部分业务需求。还有其他数据引擎，比如memory和csv,它们的使用都不如上述2种广泛，具体使用方法参考文档。数据库引擎和表一一对应，同一个数据库允许不同的表采用不同的引擎，但这些表之间的互访问性有所不同，比如：假如2个表存在外键引用关系，则2表必须都是innodb引擎。为了得到较好的执行计划，尽量在一个查询中涉及的表采用同一种引擎。
三、TPCH测试
和前几次测试一样，主要测试数据加载和查询性能，也对数据压缩进行测试。
1.准备工作
mysql是MySQL提供的命令行工具，它包括单行命令方式和交互方式。
mysql常用的命令行参数选项如下：
mysql -u 用户名 -p 口令 数据库名
构造测试环境，首先建立一个名为tpch的数据库，并设置它为当前数据库。然后执行创建表的脚本。需要注意将脚本中的表名统一为小写字符，因为tpc-h生成的查询语句中的表名都是小写，并检查一下表的存储引擎是否如所预期的，若不是，需要制定默认存储引擎。
mysql> create database tpch;Query OK, 1 row affected (0.00 sec)mysql> use tpch;Database changedmysql> create table nation ( n_nationkey integer not null, -> n_name char(25) not null, -> n_regionkey integer not null, -> n_comment varchar(152));Query OK, 0 rows affected (0.00 sec)mysql> mysql> create table region ( r_regionkey integer not null, -> r_name char(25) not null, -> r_comment varchar(152));Query OK, 0 rows affected (0.00 sec)…mysql> create table t(a int);Query OK, 0 rows affected (0.00 sec)mysql> show create table t;+---------------------------------------------- CREATE TABLE `t` ( `a` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 +----------------------------------------------1 row in set (0.00 sec)mysql> set storage_engine=innodb -> ;Query OK, 0 rows affected (0.00 sec)2.数据加载
MySQL的大容量文本数据文件导入方法是在交互界面中用load data local infile语句导入，语法是：
load data local infile 文件名 into table 表名 fields terminated by 列分隔符 lines terminated by 行分隔符。Local关键字表示从客户端加载，由于本测试的服务器和客户端装在同一台机器，所以local也可以省略。
为了提高导入性能，需要用set autocommit=off命令关闭自动提交，同时为了减少innodb数据文件的扩展频率，设定每次自动扩展1GB。
mysql> use tpchDatabase changedmysql> set global innodb_autoextend_increment= 1024;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> set autocommit=off;Query OK, 0 rows affected (0.00 sec)mysql> load data local infile '/user1/app/oradata/tpch2/customer.tbl' into table customer fields terminated by '|' lines terminated by '|\r\n';Query OK, 1500000 rows affected (16.91 sec)Records: 1500000 Deleted: 0 Skipped: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.58 sec)mysql> load data local infile '/user1/app/oradata/tpch2/orders.tbl' into table orders fields terminated by '|' lines terminated by '|\r\n';Query OK, 15000000 rows affected (2 min 54.38 sec)Records: 15000000 Deleted: 0 Skipped: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (10.79 sec)mysql> load data local infile '/user1/app/oradata/tpch2/partsupp.tbl' into table partsupp fields terminated by '|' lines terminated by '|\r\n';Query OK, 8000000 rows affected (1 min 40.01 sec)Records: 8000000 Deleted: 0 Skipped: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (7.74 sec)mysql> load data local infile '/user1/app/oradata/tpch2/region.tbl' into table region fields terminated by '|' lines terminated by '|\r\n';Query OK, 5 rows affected (0.00 sec)Records: 5 Deleted: 0 Skipped: 0 Warnings: 0mysql> load data local infile '/user1/app/oradata/tpch2/supplier.tbl' into table supplier fields terminated by '|' lines terminated by '|\r\n';Query OK, 100000 rows affected (1.04 sec)Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0mysql> load data local infile '/user1/app/oradata/tpch2/lineitem.tbl' into table lineitem fields terminated by '|' lines terminated by '|\r\n';Query OK, 59986052 rows affected (14 min 48.84 sec)Records: 59986052 Deleted: 0 Skipped: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (36.71 sec)我们看到，MySQL插入10GB的数据大约需要20分钟。越大的表，提交的时间也越长。
另一种插入方法是通过表之间复制数据，我们利用tpch数据库中的数据复制一份到每个表独立innodb表空间的tpch2数据库，对比从外部文件导入，二者差别不大。
前面提到，INNODB数据引擎存储表有2种方式，第一种：所有表和索引存储在一个共享的表空间中，第2种，每个表及其上的所有索引存储在一个独立的数据文件中。
默认情况下，MySQL采用第一种方式，如果要使用第二种方式，需要设置innodb_file_per_table参数，有2种设置办法，可以动态在MySQL命令行设置，设置后立即生效，也可以在配置文件中设置，服务器重新启动后自动生效。用户可根据自己使用的需要决定用哪种方式。由于设置innodb_file_per_table参数基本上没有不良影响，这里我们在my.cnf中设置。设置以后重新启动服务器，以前在共享表空间中的innodb表依然可以访问，但新建的表都是独立表空间了。由于独立数据文件的位置默认在/var/lib/mysql下的数据库名目录下，我们创建数据库后关闭MySQL服务器，将tpch2目录移动到空间较大的/user1/mysql_db目录下，然后再在原来的位置建立一个软连接。
mysql> show variables like '%per_table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+1 row in set (0.00 sec)mysql> create database tpch2;Query OK, 1 row affected (0.00 sec)[root@redflag11012501 tpch]# mysqladmin -u root shutdownmysqladmin: Unknown OS character set 'GB18030'.mysqladmin: Switching to the default character set 'latin1'.[root@redflag11012501 tpch]# pwd/var/lib/mysql/tpch[root@redflag11012501 tpch]# su - mysql-bash-3.2$ mv /var/lib/mysql/tpch2 /user1/mysql_db-bash-3.2$ ln -s /user1/mysql_db/tpch2 tpch2-bash-3.2$ ll总计 120drwx--x--x 2 mysql mysql 4096 06-02 17:59 mysql-rw-rw---- 1 mysql mysql 150 06-04 19:42 mysql-bin.000015-rw-rw---- 1 mysql mysql 677 06-08 11:03 mysql-bin.000016-rw-rw---- 1 mysql mysql 126 06-08 11:22 mysql-bin.000017-rw-rw---- 1 mysql mysql 126 06-08 14:00 mysql-bin.000018-rw-rw---- 1 mysql mysql 218 06-09 12:58 mysql-bin.000019-rw-rw---- 1 mysql mysql 95 06-08 21:19 mysql-bin.indexdrwx------ 2 mysql mysql 4096 06-02 17:59 performance_schema-rw-rw---- 1 mysql mysql 14288 06-10 15:16 redflag11012501.err-rw-rw---- 1 mysql mysql 50512 06-10 12:33 redflag11012501-slow.log-rw-r--r-- 1 mysql mysql 121 06-02 17:59 RPM_UPGRADE_HISTORY-rw-r--r-- 1 mysql mysql 121 06-02 17:59 RPM_UPGRADE_MARKER-LASTdrwxr-xr-x 2 mysql mysql 4096 06-02 17:59 testdrwx------ 2 mysql mysql 4096 06-10 12:33 tpchlrwxrwxrwx 1 mysql mysql 21 06-10 15:19 tpch2 -> /user1/mysql_db/tpch2-bash-3.2$ exitlogout[root@redflag11012501 tpch]# service mysql startStarting MySQL......[确定]mysql> use tpchDatabase changedmysql> create table tpch2.partsupp as select * from partsupp;Query OK, 8000000 rows affected (1 min 37.88 sec)Records: 8000000 Duplicates: 0 Warnings: 0mysql> create table tpch2.part as select * from part;Query OK, 2000000 rows affected (20.01 sec)Records: 2000000 Duplicates: 0 Warnings: 0mysql> create table tpch2.orders as select * from orders;Query OK, 15000000 rows affected (2 min 55.07 sec)Records: 15000000 Duplicates: 0 Warnings: 0mysql> create table tpch2.customer as select * from customer;Query OK, 1500000 rows affected (24.22 sec)Records: 1500000 Duplicates: 0 Warnings: 0mysql> create table tpch2.supplier as select * from supplier;Query OK, 100000 rows affected (2.58 sec)Records: 100000 Duplicates: 0 Warnings: 0mysql> create table tpch2.nation as select * from nation;Query OK, 25 rows affected (0.05 sec)Records: 25 Duplicates: 0 Warnings: 0mysql> create table tpch2.region as select * from region;Query OK, 5 rows affected (0.04 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> create table tpch2.lineitem as select * from lineitem;Query OK, 59986052 rows affected (13 min 29.94 sec)Records: 59986052 Duplicates: 0 Warnings: 0每个表独立表空间的好处之一是可以从文件大小直接看出表大小。例如：
[root@redflag11012501 tpch2]# ll总计 13145964-rw-rw---- 1 mysql mysql 8850 06-10 16:16 customer.frm-rw-rw---- 1 mysql mysql 301989888 06-10 16:16 customer.ibd-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt-rw-rw---- 1 mysql mysql 9226 06-10 15:20 lineitem.frm-rw-rw---- 1 mysql mysql 9353297920 06-10 15:34 lineitem.ibd-rw-rw---- 1 mysql mysql 8692 06-10 16:11 nation.frm-rw-rw---- 1 mysql mysql 98304 06-10 16:11 nation.ibd-rw-rw---- 1 mysql mysql 8928 06-10 16:23 orders.frm-rw-rw---- 1 mysql mysql 2000683008 06-10 16:27 orders.ibd-rw-rw---- 1 mysql mysql 8874 06-10 16:11 part.frm-rw-rw---- 1 mysql mysql 335544320 06-10 16:12 part.ibd-rw-rw---- 1 mysql mysql 8748 06-10 16:17 partsupp.frm-rw-rw---- 1 mysql mysql 1430257664 06-10 16:20 partsupp.ibd-rw-rw---- 1 mysql mysql 8648 06-10 16:11 region.frm-rw-rw---- 1 mysql mysql 98304 06-10 16:11 region.ibd-rw-rw---- 1 mysql mysql 8804 06-10 16:17 supplier.frm-rw-rw---- 1 mysql mysql 26214400 06-10 16:17 supplier.ibd3.测试数据和查询语句的产生步骤
主要步骤参照本系列第一篇文章《Oracle 11g R2企业版评测》。然后需要针对SQL Server的特性作修改。
Tpc-h的qgen产生的查询语句基本上满足了MySQL的语法要求，只需要将limit 前面一行的分号去掉即可。为了标识查询的编号，我们在每个查询前面插入prompt 序号。
将修改完成后的22个查询语句保存为mysql_tpch.sql文件。
MySQL的查询计时是默认打开的，因此不需要设置。
4.数据压缩测试
表压缩是INNODB的一个有用的功能，可以显著减少数据在磁盘上占用的空间。
为了启用表压缩功能，必须满足2个先决条件。
1.设置前文提到的innodb_file_per_table参数，启用单个表独立innodb数据文件；
2.设置innodb_file_format=barracuda，指定innodb文件格式为Barracuda。
然后在创建表时就可以指定ROW_FORMAT=COMPRESSED选项来创建压缩表，默认是不压缩的。下面比较一下创建非压缩表和压缩表的时间和空间占用情况。
mysql> create table part1 as select * from part;Query OK, 2000000 rows affected (19.58 sec)Records: 2000000 Duplicates: 0 Warnings: 0mysql> set global innodb_file_format=barracuda;Query OK, 0 rows affected (0.00 sec)mysql> create table part2 ROW_FORMAT=COMPRESSED as select * from part;Query OK, 2000000 rows affected (46.60 sec)Records: 2000000 Duplicates: 0 Warnings: 0[root@redflag11012501 tpch]# pwd/var/lib/mysql/tpch[root@redflag11012501 tpch]# ll part?.*-rw-rw---- 1 mysql mysql 8874 06-10 09:51 part1.frm-rw-rw---- 1 mysql mysql 348127232 06-10 09:52 part1.ibd-rw-rw---- 1 mysql mysql 8874 06-10 10:08 part2.frm-rw-rw---- 1 mysql mysql 176160768 06-10 10:09 part2.ibd可以看出，创建压缩表的时间大约是非压缩表的2倍，占用的磁盘空间大约是非压缩表的1/2。
下面再比较一下查询的性能。
mysql> select count(*),p_type from part group by p_type;+----------+---------------------------+| count(*) | p_type |+----------+---------------------------+| 13399 | ECONOMY ANODIZED BRASS || 13678 | ECONOMY ANODIZED COPPER || 13423 | ECONOMY ANODIZED NICKEL || 13452 | ECONOMY ANODIZED STEEL |...| 13115 | STANDARD POLISHED TIN |+----------+---------------------------+150 rows in set (14.06 sec)mysql> select count(*),p_type from part1 group by p_type;150 rows in set (3.74 sec)mysql> select count(*),p_type from part2 group by p_type;150 rows in set (8.60 sec)可以看出单个表一个innodb数据文件的查询性能要好于多个表存放在同一个数据文件，而不压缩(ROW_FORMAT=COMPACT)的表比压缩后的表查询又快1倍。
这里只是一个表的情况，下面，我们将对8个表添加主外键后再比较它们的大小。
另外，压缩的表进行DML操作的效率较低，因此不适用于频繁数据更改的表。
5.数据查询
由于原始tpch脚本dss.ri中创建外键的语法不符合MySQL的格式要求，需要把引用表的列名也列举出来，同时，MySQL默认对表名大小写敏感，也要做相应的修改。为了便于在执行计划中辨认主键，对主键约束也采取命名方式。
-- 添加主键alter table region add constraint region_pk primary key (r_regionkey);alter table nation add constraint nation_pk primary key (n_nationkey);alter table part add constraint part_pk primary key (p_partkey);alter table partsupp add constraint partsupp_pk primary key (ps_partkey,ps_suppkey);alter table customer add constraint customer_pk primary key (c_custkey);alter table orders add constraint orders_pk primary key (o_orderkey);alter table lineitem add constraint lineitem_pk primary key (l_orderkey,l_linenumber);alter table supplier add constraint supplier_pk primary key (s_suppkey);-- 添加外键alter table nation add constraint nation_fk1 foreign key (n_regionkey) references region(r_regionkey);alter table supplier add constraint supplier_fk1 foreign key (s_nationkey) references nation(n_nationkey);alter table customer add constraint customer_fk1 foreign key (c_nationkey) references nation(n_nationkey);alter table partsupp add constraint partsupp_fk1 foreign key (ps_suppkey) references supplier(s_suppkey);alter table partsupp add constraint partsupp_fk2 foreign key (ps_partkey) references part (p_partkey);alter table orders add constraint orders_fk1 foreign key (o_custkey) references customer (c_custkey);alter table lineitem add constraint lineitem_fk1 foreign key (l_orderkey) references orders (o_orderkey);alter table lineitem add constraint lineitem_fk2 foreign key (l_partkey,l_suppkey) references partsupp(ps_partkey,ps_suppkey);由于原始没有添加主键和外键的表执行查询的速度相当慢，比如第2个查询，基本不可能在可接受的时间内获得结果，因此，不进行原始表的测试，只测试添加主键和外键后的结果，同时测试所有表共享存储在单个表空间和每个表单独表空间的情况。鉴于MySQL在添加外键时同时创建了相应列上的索引，对查询影响较大，故分别测试只添加主键和添加主键和外键二种情况。
Innodb创建的主键是聚集主键，增加的空间非常少，甚至占用的空间比增加主键前的原始表还略有减少，这与它的索引存储和数据在一起有关。创建外键的同时，MySQL检查外键所在列是否有索引，如果没有，则自动创建索引，这与其他数据库的外键约束有所不同，因此创建外键后，表占用空间增加了。
创建主键后的表占用空间。
[root@redflag11012501 tpch2]# ll总计 12760564-rw-rw---- 1 mysql mysql 8850 06-10 16:16 customer.frm-rw-rw---- 1 mysql mysql 301989888 06-10 16:16 customer.ibd-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt-rw-rw---- 1 mysql mysql 9226 06-10 16:28 lineitem.frm-rw-rw---- 1 mysql mysql 8959033344 06-10 16:47 lineitem.ibd-rw-rw---- 1 mysql mysql 8692 06-10 16:11 nation.frm-rw-rw---- 1 mysql mysql 98304 06-10 16:11 nation.ibd-rw-rw---- 1 mysql mysql 8928 06-10 16:23 orders.frm-rw-rw---- 1 mysql mysql 2000683008 06-10 16:27 orders.ibd-rw-rw---- 1 mysql mysql 8874 06-10 16:11 part.frm-rw-rw---- 1 mysql mysql 335544320 06-10 16:12 part.ibd-rw-rw---- 1 mysql mysql 8748 06-10 16:17 partsupp.frm-rw-rw---- 1 mysql mysql 1430257664 06-10 16:20 partsupp.ibd-rw-rw---- 1 mysql mysql 8648 06-10 16:11 region.frm-rw-rw---- 1 mysql mysql 98304 06-10 16:11 region.ibd-rw-rw---- 1 mysql mysql 8804 06-10 16:17 supplier.frm-rw-rw---- 1 mysql mysql 26214400 06-10 16:17 supplier.ibd创建外键后的表占用空间。
mysql> system ls -l /user1/mysql_db/tpch2总计 15625452-rw-rw---- 1 mysql mysql 8850 06-13 11:03 customer.frm-rw-rw---- 1 mysql mysql 327155712 06-13 11:04 customer.ibd-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt-rw-rw---- 1 mysql mysql 9226 06-13 11:20 lineitem.frm-rw-rw---- 1 mysql mysql 11341398016 06-13 11:51 lineitem.ibd-rw-rw---- 1 mysql mysql 8692 06-13 11:03 nation.frm-rw-rw---- 1 mysql mysql 114688 06-13 11:03 nation.ibd-rw-rw---- 1 mysql mysql 8928 06-11 13:04 orders.frm-rw-rw---- 1 mysql mysql 2315255808 06-11 13:09 orders.ibd-rw-rw---- 1 mysql mysql 8874 06-10 16:11 part.frm-rw-rw---- 1 mysql mysql 335544320 06-10 16:12 part.ibd-rw-rw---- 1 mysql mysql 8748 06-13 11:06 partsupp.frm-rw-rw---- 1 mysql mysql 1635778560 06-13 11:08 partsupp.ibd-rw-rw---- 1 mysql mysql 8648 06-10 16:11 region.frm-rw-rw---- 1 mysql mysql 98304 06-10 16:11 region.ibd-rw-rw---- 1 mysql mysql 8804 06-13 11:03 supplier.frm-rw-rw---- 1 mysql mysql 29360128 06-13 11:04 supplier.ibd查看order表的索引，可见外键列上自动创建了索引。
mysql> show index from orders;+--------+------------+------------+--------------+-------------+-----------+-------------+------------+-| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | +--------+------------+------------+--------------+-------------+-----------+-------------+------------+-| orders | 0 | PRIMARY | 1 | o_orderkey | A | 14928217 | BTREE | | orders | 1 | orders_fk1 | 1 | o_custkey | A | 2132602 | BTREE | +--------+------------+------------+--------------+-------------+-----------+-------------+------------+-2 rows in set (0.01 sec)执行下述命令将表转为压缩格式。可见压缩耗费的时间比较长，比外部文件导入的时间还长得多。每个表的压缩率基本上都是50%，节约空间还是比较明显的。
mysql> set global innodb_file_format=barracuda;Query OK, 0 rows affected (0.00 sec)mysql> alter table nation row_format=compressed;Query OK, 25 rows affected (0.57 sec)mysql> alter table region row_format=compressed;Query OK, 5 rows affected (0.49 sec)mysql> alter table customer row_format=compressed;Query OK, 1500000 rows affected (1 min 4.93 sec)mysql> alter table part row_format=compressed;Query OK, 2000000 rows affected (45.82 sec)mysql> alter table supplier row_format=compressed;Query OK, 100000 rows affected (5.31 sec)mysql> alter table partsupp row_format=compressed;Query OK, 8000000 rows affected (5 min 48.06 sec)mysql> alter table orders row_format=compressed;Query OK, 15000000 rows affected (23 min 52.25 sec)mysql> alter table lineitem row_format=compressed;Query OK, 59986052 rows affected (1 hour 29 min 45.27 sec)...mysql> system ls -l /user1/mysql_db/tpch2总计 7841520-rw-rw---- 1 mysql mysql 8850 06-13 21:18 customer.frm-rw-rw---- 1 mysql mysql 163577856 06-13 21:19 customer.ibd-rw-rw---- 1 mysql mysql 65 06-10 15:15 db.opt-rw-rw---- 1 mysql mysql 9226 06-13 21:50 lineitem.frm-rw-rw---- 1 mysql mysql 5691670528 06-13 23:21 lineitem.ibd-rw-rw---- 1 mysql mysql 8692 06-13 21:18 nation.frm-rw-rw---- 1 mysql mysql 65536 06-13 21:18 nation.ibd-rw-rw---- 1 mysql mysql 8928 06-13 21:28 orders.frm-rw-rw---- 1 mysql mysql 1161822208 06-13 21:53 orders.ibd-rw-rw---- 1 mysql mysql 8874 06-13 21:19 part.frm-rw-rw---- 1 mysql mysql 167772160 06-13 21:20 part.ibd-rw-rw---- 1 mysql mysql 8748 06-13 21:21 partsupp.frm-rw-rw---- 1 mysql mysql 822083584 06-13 21:27 partsupp.ibd-rw-rw---- 1 mysql mysql 8648 06-13 21:18 region.frm-rw-rw---- 1 mysql mysql 65536 06-13 21:18 region.ibd-rw-rw---- 1 mysql mysql 8804 06-13 21:21 supplier.frm-rw-rw---- 1 mysql mysql 14680064 06-13 21:21 supplier.ibd为了比较不同条件下的查询结果，我们进行了4种组合的查询。分别是：共享表空间不压缩，独立表空间不压缩(不创建外键)，独立表空间不压缩(创建外键)，独立表空间压缩，为了避免查询缓存的影响，用set session query_cache_type = OFF关闭查询缓存。每种测试都在更新统计信息(analyze table)和清空内存(flush tables)后执行2遍，取第2遍的结果。对于个别查询时间过长的查询，如第17、18和20个查询，用人工改写后的写法代替原始的，以获得一个测试时间。
利用前面第一步产生的查询脚本，执行测试的命令行如下,查询结果输出到不同的log文件，以便于比较：注意在更改表存储和索引类型后，MySQL自动更新了统计信息。
mysql> ANALYZE TABLE nation;+--------------+---------+----------+----------+| Table | Op | Msg_type | Msg_text |+--------------+---------+----------+----------+| tpch2.nation | analyze | status | OK |+--------------+---------+----------+----------+1 row in set (0.01 sec)…mysql> show variables like 'query_cache_type';+------------------+-------+| Variable_name | Value |+------------------+-------+| query_cache_type | ON |+------------------+-------+1 row in set (0.00 sec)mysql> set query_cache_type = OFF;Query OK, 0 rows affected (0.00 sec)mysql> source /root/qall_myf.sql将输出保存到一个文件，然后用find命令来得到各次运行的时间：
C:\downloads>find "sec)" my_com---------- MY_COM4 rows in set (6 min 35.52 sec)10 rows in set (8.36 sec)10 rows in set (2 min 19.04 sec)5 rows in set (15.43 sec)5 rows in set (25.21 sec)1 row in set (34.08 sec)4 rows in set (28.48 sec)2 rows in set (43.85 sec)10 rows in set (28 min 13.00 sec)20 rows in set (3 min 40.64 sec)Empty set (16.66 sec)2 rows in set (20 min 50.48 sec)10 rows in set (1 min 11.77 sec)1 row in set (49 min 11.58 sec)1 row in set (1 min 24.77 sec)10 rows in set (1 min 58.55 sec)1 row in set (4 min 22.08 sec)10 rows in set (37.06 sec)1 row in set (2.26 sec)10 rows in set (7 min 59.96 sec)10 rows in set (19 min 30.41 sec)5 rows in set (18 min 30.74 sec)下面是各组查询测试结果，取实际运行时间。
▲表1 TPC-H scale=10各种压缩和索引数据的测试对比，单位：秒
注意：带*号的项目使用修改后的等价查询。独立表空间不压缩(不加外键)，第13个查询没法在合理的时间完成，后续的查询也没有再继续执行。
从表1可知，如果没有外键约束，MySQL的前12个查询仍能在合理的时间内运行完成，其中第10、11个查询的时间比添加了外键以后还短，这说明查询优化器对这些查询的优化能力不错，而加上外键约束后，在表不压缩的情况下，大约35分钟就能将22个查询执行完毕，但有些查询的时间变长了，这说明，MySQL采用基于规则的优化，虽然访问表比访问索引更好，还是采用了访问索引的执行计划。
MySQL压缩基本上发挥不了提高查询速度的作用，除了个别查询(如查询2)比非压缩表速度快外，其他时间短的查询和未压缩表相当，而时间长的有未压缩表的几倍到几百倍。按理说减少了I/O物理读对整体查询应该有帮助的，但事实并非如此，可能跟MySQL的压缩算法压缩比不高，但解压开销较大有关。可以这么说，MySQL的压缩功能基本不适合于查询。
6.性能调整和优化
性能调整和优化，涵盖表结构设计、存储方式设计、查询设计、参数调整等方面，添加主键、外键是优化查询性能的一种办法，如果是实际的查询，而不是基准测试，我们就需要充分利用MySQL数据库的功能，针对每个查询单独优化，必要时用等价的语句改写。
1.执行计划的查看
要利用改写查询语句提高执行效率，首先要了解现有的执行计划。
执行计划的查看，使用explain 后接需要查看计划的SQL语句。
先看一个简单的例子，复制一份customer表。然后查询符合c_name条件的记录数。表的总行数是150万，符合条件的999行，大约占总记录数的1500分之一。创建索引后，执行计划显示使用了Index。比不使用Index的执行时间减少了96%。(1-0.02/0.56)*100%
mysql> create table c2 as select * from customer;Query OK, 1500000 rows affected (16.37 sec)Records: 1500000 Duplicates: 0 Warnings: 0mysql> select count(*) from c2 where c_name like 'Customer#000000%';+----------+| count(*) |+----------+| 999 |+----------+1 row in set (0.89 sec)mysql> explain select count(*) from c2 where c_name like 'Customer#000000%';+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| 1 | SIMPLE | c2 | ALL | NULL | NULL | NULL | NULL | 1489455 | Using where |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec)mysql> select count(*) from customer where c_name like 'Customer#000000%';+----------+| count(*) |+----------+| 999 |+----------+1 row in set (0.56 sec)mysql> explain select count(*) from customer where c_name like 'Customer#000000%';+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+| 1 | SIMPLE | customer | ALL | NULL | NULL | NULL | NULL | 1495259 | Using where |+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec)mysql> create index c2_name on c2(c_name);Query OK, 0 rows affected (6.35 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select count(*) from c2 where c_name like 'Customer#000000%';+----------+| count(*) |+----------+| 999 |+----------+1 row in set (0.02 sec)mysql> explain select count(*) from c2 where c_name like 'Customer#000000%';+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+| 1 | SIMPLE | c2 | range | c2_name | c2_name | 27 | NULL | 998 | Using where; Using index +----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+1 row in set (0.00 sec)mysql> explain select sum(c_custkey) from c2 where c_name like 'Customer#000000%';+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | c2 | range | c2_name | c2_name | 27 | NULL | 998 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> select sum(c_custkey) from c2 where c_name like 'Customer#000000%';+----------------+| sum(c_custkey) |+----------------+| 499500 |+----------------+1 row in set (0.01 sec)mysql> select sum(c_custkey) from customer where c_name like 'Customer#000000%';+----------------+| sum(c_custkey) |+----------------+| 499500 |+----------------+1 row in set (0.62 sec)一个有趣的现象，如果一个复杂查询的执行计划显示很快，那么基本上这是一个不良的执行计划，执行时间会比较长，反之，如果一个执行计划用了数十秒到1分钟多才显示出来，那么实际查询的时间也差不多就是这个时间了，好像优化器是在一边扫描表一边评估执行计划。比如下面第20个查询，在非压缩的情况下，第一个需要8分钟多，第2个只要差不多1分58秒。当然，这个经验也不绝对，对于一些查询，可能很长时间也求不出执行计划，但查询依然很慢。比如，tpc-h原始的第17个查询,就是既查不到执行计划，也无法在合理的时间完成，只能根据业务含义人工改写了。
下面对tpc-h的第21个查询查看原始写法和改写的等价写法的执行计划。
mysql> explain -- 21 -> select -> s_name, -> count(*) as numwait -> from -> supplier, -> lineitem l1, -> orders, -> nation -> where -> s_suppkey = l1.l_suppkey -> and o_orderkey = l1.l_orderkey -> and o_orderstatus = 'F' -> and l1.l_receiptdate > l1.l_commitdate -> and exists ( -> select -> * -> from -> lineitem l2 -> where -> l2.l_orderkey = l1.l_orderkey -> and l2.l_suppkey <> l1.l_suppkey -> ) -> and not exists ( -> select -> * -> from -> lineitem l3 -> where -> l3.l_orderkey = l1.l_orderkey -> and l3.l_suppkey <> l1.l_suppkey -> and l3.l_receiptdate > l3.l_commitdate -> ) -> and s_nationkey = n_nationkey -> and n_name = 'GERMANY' -> group by -> s_name -> order by -> numwait desc, -> s_name limit 10;
6 rows in set (0.00 sec)mysql> explain -- 21 fix -> select s_name ,count(*) as numwait from -> (select -> s_name,l_suppkey ,l_orderkey -> -- count(*) as numwait -> from -> supplier, -> lineitem l1, -> orders, -> nation -> where -> s_suppkey = l1.l_suppkey -> and o_orderkey = l1.l_orderkey -> and o_orderstatus = 'F' -> and l1.l_receiptdate > l1.l_commitdate -> and s_nationkey = n_nationkey -> and n_name = 'GERMANY' -> ) l1 -> where exists ( -> select -> * -> from -> lineitem l2 -> where -> l2.l_orderkey = l1.l_orderkey -> and l2.l_suppkey <> l1.l_suppkey -> ) -> and not exists ( -> select -> * -> from -> lineitem l3 -> where -> l3.l_orderkey = l1.l_orderkey -> and l3.l_suppkey <> l1.l_suppkey -> and l3.l_receiptdate > l3.l_commitdate -> ) -> group by -> s_name -> order by -> numwait desc, -> s_name -> limit 10;
7 rows in set (1 min 44.56 sec)我们从执行计划可以看出表之间的主要访问路径，是通过索引还是表扫描，但并不包含成本的估算值。我们直接从执行计划查看它的优劣还是有难度的。需要对MySQL有相当的使用经验才能编写出高效执行的SQL语句。从测试的情况看，用表的关联来替代In子查询 操作能提高效率。
2.统计信息收集和管理
正确的统计信息对查询优化器得出较好的执行计划有十分重要的影响，在大量插入或更新数据以后，需要重新收集统计信息。收集指定表的统计信息的语法如下：
ANALYZE TABLE 表名还有其他的优化手段，比如添加必要的索引，这其实已经从添加外键约束的过程中体现了，因为对MySQL来说，外键必须有索引，添加了索引后，第13个查询的时间大大缩短了，效果显著。
四、小结
经过这次测试，我们对MySQL数据库已经有了初步的印象，虽然和商业数据库比较，安装包体积小了很多，安装步骤也很简单，但功能还是很强大，从其文档3000多页的篇幅可以看出，能够配置和优化的部分也很多，对SQL语句的兼容性也比较好，总体性能也比较好，比如数据文件导入，在基本上相同的硬件配置下，和商业数据库相差并不多。其次，对于TPC-H这种分析型查询，在利用主外键约束后，大部分语句都能产生较好的执行计划。另外，测试过程中没有出现数据库意外崩溃的现象。此外支持的操作系统平台比较广泛，从Linux、Unix到MS Windows 32位和64位都有相应的版本，便于跨平台移植。MySQL官方还提供了图形界面的管理工具MySQL Workbench，可以提高数据库管理和开发的效率。
存在的问题，首先，也是文章前面未曾提及的，单个查询无法使用并行，在现在大部分处理器都是多核的情况下，单进程对于批处理的分析查询显然是不太实用的，虽然并发执行多个查询可以分别使用不同的CPU,但这样又会带来I/O的竞争,限制了硬件资源的利用。其次，查询优化器虽然用到了统计信息，但基本上还是基于规则的，从添加外键后部分查询速度退化可以印证。对于一些类型的查询，比如IN 子查询类语句，执行计划不佳。需要人工修改，对开发人员的要求较高。explain显示的执行计划信息不够多，缺少成本信息，不便于分辨执行计划的优劣。对于一些复杂查询，显示执行计划的时间较长。最后，对新的SQL特性支持不够，不支持分析函数，也不支持with子查询。
最后提一下文档和支持，MySQL的文档还是非常详细的，但本地化工作落后于多数商业数据库，好在网络上有大量关于MySQL的中文技术文章和入门教材，市场上也有不少中文出版物。这要归功于它的免费造就了大量的高水平用户，这些用户的使用经验都是很好的资源。
总的来说，MySQL是一个功能全面，性能优良，运行稳定的主流免费数据库。虽然在分析型批处理上还有待提高，但功能和性能足以满足大部分OLTP的应用。
