且将新火试新茶 - MySQL Benchmark

公司内部最流行的数据库就是MySQL，而关于MySQL性能，我听过种种传说和流言。而对于数据库的性能优化和测试，我一直有强烈的兴趣，曾经见过一篇Oracle的性能优化文章，Linux Journal的Bert Scalzo所著的《Linux Maximus, Part 1: Gladiator-like Oracle Performance》，国人的翻译是《角斗士般的Oracle性能》[注]，那兄弟为了压榨Oracle的性能，从数据库的参数到操作系统的参数都进行了调整，最后得到的优化性能比开始提高了10多倍。为什么我用了压榨这个词，你可以想像将文件的最后修改时间调整为不可改变都可以提升数据库的性能。调整几个参数就可以使系统的能力如此提升，何乐而不为呢？

对于MySQL的性能，基本分成两种截然不同的声音：MySQL自己声称的性能数据是如此的优秀，超过Oracle等数据库若干倍等等。其实大多数公司进行的这样的对比测试都是不可信的，自己产品用专有接口，别人产品用出了名的慢接口ODBC，自己不用事务，别人用事务操作，这样的结果数据差距当然十万八千里。另外一种声音基本来之公司内部，主要质疑的是大数量下的性能，普遍的态度是认为在200000条记录以上，MySQL的性能将急剧下降。我个人是后一种观念的受害者，经常为了活动的开发还分表。最近有机会利用公司的设备资源，自己比较测试了若干种环境下的MySQL的表现，很多测试数据的结果颠覆了自己原来对MySQL的认识。

本文名为benchmark,但是实际是没有一个真正的基准，大部分测试是不同状况下的性能比较测试。如果非要找一个基准，那就是MySQL本身。

根据自己的疑惑，主要把测试的重点放在了MySQL不同版本间的性能比较，MySQL的查询缓存的性能，MySQL的MyISAM和InnoDB数据库引擎间性能比较，同时模拟了压力情况，真实环境测试性能，同时对于MySQL的新特性API接口STMT也进行了测试。在文档的最后，我给出了MySQL相关的优化参数说明以及MySQL的相关状态监控方法。

1         测试的相关环境

1.1        测试的机器

既然打着Benchmark的旗号，就必须介绍一下用于测试的机器。由于懒和想测试的用例是逐步的加入的，到测试结束，我耗费了将近6个月的时间。中间被我占用来测试的机器居然有4台，2种，HP380G3和HP380G4， 380G4的机器配置上要好一些（虽然我感觉上表现和G4的基本相同），所以可能影响测试结果数据。而且在后期整理数据阶段，发现了很多测试数据偏差过大。我不得不怀疑机器有人同时使用造成了影响。

所幸我最后基本上把所有的测试用例在一台绝对空闲380G3上全部重做了一次。如果没有特别指明之处，大家就把测试机器看作内核是2.6.8.1（这个内核好像有些不完善.）的380G3吧。

其中模拟多表访问的测试是在另有一台2.4.21的内核的380G3测试了。为了保证对比的公平性，相关的比较都是相同机器上的测试数据。

                                                                                                                                                          表1 HP380G4和HP380G3

1.2        测试的表

测试的表采用单键索引，主键为INT类型，记录类型覆盖了我们常用的记录类型，记录的长度为120多个字节，和我们平常的记录表类似。

CREATE TABLE IF NOT EXISTS benchmark.test1

(

F1      INT   NOT NULL ,

F2      INT   NOT NULL DEFAULT 0,

F3      INT   NOT NULL DEFAULT 0,

F4      INT   NOT NULL DEFAULT 0,

F5      INT   NOT NULL DEFAULT 0,

F6      INT   NOT NULL DEFAULT 0,

F7      INT   NOT NULL DEFAULT 0,

F8      INT   NOT NULL DEFAULT 0,

F9      INT   NOT NULL DEFAULT 0,

F10     INT   NOT NULL DEFAULT 0,

F11     FLOAT(10,4) NOT NULL DEFAULT 0,

F12     DOUBLE(10,4) NOT NULL DEFAULT 0,

F13     VARCHAR(64) NOT NULL DEFAULT “,    

F14     DATETIME   NOT NULL DEFAULT ‘0’,        

PRIMARY KEY (F1)

);

2         MySQL不同版本间的性能比较

不知道大家初次见到MySQL是何种感觉，反正我见到MySQL3.23时很不以为然，没有视图，没有事务，没有触发器，没有子查询，没有存储过程，没有……，懒得提了，但是要说是今天MySQL5.0的能力已经得到了很大的提升，至少上面说明的这些问题，MySQL已经解决了大部分了。

2.1        比较的版本

选择比较的版本是3.23版本,4.0版本, 4.1版本，5.0 Beta版本。其中3.23版本为MySQL的最早的可用版本，XXX等产品也一直在使用，4.0为MySQL支持查询缓冲的版本，也有很多产品使用。MySQL 4.1和5.0版本为较新的版本，支持一些新的特性。

MySQL的安装可以使用源代码自己编译，也可以使用MySQL官方提供的编译版本，相对而言官方提供的静态编译版本比较容易安装(动态编译的版本要依赖glibc库版本)。

另外MySQL提供从4.1以后的提供了Intel C++ Compiler编译的版本，据称Intel C++ Compiler的版本比GCC编译的版本速度要快20%以上，所以也找来看看。

从下面这个表，我们也可以看出MySQL发展史和各个版本的基本特点，可以看出MySQL从一个过家家类型的数据库开始越来越像一个成熟的商用数据库了。

                                                                                                                                                                   表2 测试的版本

2.2        测试的方法和结果

分别，插入，查询，修改，删除，1000000，5000000，10000000，条记录。尝试MySQL在不同的记录数量级别下的表现。表不进行压缩存储。

测试环境使用使用MyISAM数据库引擎，配置中比较关键的参数为，对于4.0后的查询版本使用查询Cache。同时为了模拟真实环境，记录二进制日志。

使用的关键参数为：

set-variable   = key_buffer_size=384M

set-variable   = query_cache_size=384M

set-variable    = sort_buffer_size =1M

set-variable    = read_buffer_size=1M

set-variable    = table_cache=256

1.       测试采用脚本分组进行，每次测试前删除掉原来的数据，同时删除掉二进制日志（可能flush-log一下更好）。每次操作前sleep 20秒。

2.       插入记录为插入0-N条记录，每次单条插入。

3.       查询为每次查询一条的N次查询。遍历所有的记录。查询使用主键作为查询条件。查询操作完成转储结果集的操作。

4.       修改所有的数据，每个记录修改部分信息（4个字段），每次修改1条。

5.       删除所有的数据，为每次单独删除。删除使用主键作为查询条件。

                                                                                                                                                             表3 数据文件的大小

测试的性能数据为：

                                                                                                                                                    表4 不同版本的表现的耗时

                                                                                                                                                    表5 不同版本处理性能比较

比较图表如下：

多个版本间的性能比较

从上面两张表可以看出，MySQL的简单查询，修改，插入，删除性能还是非常可圈可点的。在1000000的记录级别，效率也可以达到3000-6000条左右。

总体来看，除了查询，大家的性能其实都差不多。从4.0开始的版本，查询速度都有一个明显的下降，个人估计是由于4.0后MySQL支持查询Cache造成的，我的查询方式几乎不会利用到Cache数据，所以Cache不但没有帮助提高查询效率，反而降低了查询的效率。基于这个问题，我们后面单独对MySQL的Cache进行了单独的测试。

我在测试后惊讶的发现，最好的数据居然是mysql3.23.55的版本的性能。我只能猜测无数的功能堆加后，性能多少有下降，5.0Beta版本也的确排名最后。而对于现有使用的版本3.23.51和3.23.55版本的性能很接近。但是看来使用安装包版本比自己编译版本要好。

icc编译的版本没有想传言那样体现优势。我个人估计我的测试用例主要测试的是MySQL的I/O性能，而不是运算。icc的强项体现不出来。

即使到了10000000的数据量级别，查询和更新速度仍然可以接受。不想传说中那么恐怖。

插入速度的确快过查询速度。B树的重建索引速度大于查询的。？而且即使对于MySQL3.23的版本，查询的性能还是要落后于其他操作，个人估计是由于查询结果要返回结果集合造成的。

从这组数据我们可以发现，在数据量增加后，插入数据的性能变化很小，查询数据的性能成非常轻微的下降，这很好理解，这是由于查询的索引增大造成的。

个人觉得这些性能数据接近这些操作在MySQL在380G3上的极限性能。因为这些基本操作的瓶颈在都在I/O上，并发不可能提高这些性能[注]。倒是调整操作系统的参数和内核还可能有一些优化的余地。

2.3        MySQL的版本

从上面的测试结果来，使用MyISAM引擎，MySQL的数据库的基本功能性能没有太多变化。MySQL后面的几个版本介绍说明有性能提高的地方也都不包括基本功能这块。

而对于4.0后提供的查询Cache这个功能，其实也是差强人意，（后面会着重讨论）。所以指望升级数据库大规模改善数据表的基础功能性能看来不现实。但考虑到4.0和4.1版本提供了一些新的功能，而且更加稳定。使用4.0以后的版本还是正确的选择[注]。

3         MySQL的查询Cache测试

我曾经对MySQL的Cache作了非常大的期望。因为提高数据库的性能的最好方法就是使用大规模的Cache，但是通过查询相关资料我了解到MySQL的查询Cache有很多的限制。

3.1        Without Cache测试

可以看到,MySQL从4.0后查询速度有所下降,所以我们也测试了一下不使用Cache的数据。我测试了4.0gcc编译版本和4.1gcc编译版本在在使用Cache和不使用Cache的情况下的速度。

关闭查询Cache的方法为调整参数 query_cache_size为0 或者设置 query_cache_type为0 [注]。

                                                                                                                                   表6 是否使用cache的环境下的测试对比

                                                                                                                                   表7 是否使用cache的环境下的测试对比

是否使用Cache的性能比较

这个测试可以证明，使用Cache时，同样是有成本的，特别是对查询语句，成本还比较高,单条查询SQL的使用Cache成本大约是比不是使用Cache的成本高40%，这个成本应该来自MySQL要消耗将结果保存到Cache和淘汰出Cache的时间。[注]

3.2        查询时使用Cache

如何让查询时能使用上Cache，我考虑这样进行测试，查询1条记录后，再查询4次。同时不进行任何操作，保证后面的查询命中Cache。测试的数量为1000000条。

测试的结果如下：

                                                                                                                                     表8 查询1000000条记录5次的消耗时间

是否使用cache对查询效率的影响

可以看到MySQL的Cache在这个理想的测试环境下确实提高了。但是这些提高也是要付出代价的。

3.3        查询Cache的代价

正当我对上面的结果欢心鼓舞是，而查询的资料发现给自己浇了些冷水，上面的测试非常理想，MySQL的Cache使用有很多限制。

首先我们从Without Cache组测试数据可以看到，使用Cache后进行查询是有成本的。

在Cache中的MySQL查询结果是和查询SQL对应的，如果你想命中Cache，两次使用的查询语句必须完全一样。所以你最好使用同1个人编写的API查询数据库。

另外，MySQL的查询Cache淘汰策略近乎弱智，对于MyISAM引擎，对数据表的任何一次都会淘汰所有相关此表在Cache中的数据[注]，而InnoDB的引擎会在任何一次提交后淘汰所有的和此次交易相关表在Cache中的数据。所以对于一个频繁进行操作的数据库，MySQL的查询Cache的命中率肯定不会太高，而且查询同时要增加和Cache相关的成本，要消耗有入Cache和淘汰出Cache的时间。

所以你在使用MySQL Cache时最好仔细考虑一下。如果数据库的数据表基本是静态数据。可以使用这个Cache。如果是动态数据，而且改写操作频繁，是否值得用这个Cache很值得怀疑。[注]

4         InnoDB的性能

第一次认识InnoDB时，就是听说InnoDB能提供MySQL的支持事务和外键等支持，但是其性能大大不如MyISAM引擎。但是后来发现公司内部的游戏产品都在使用InnoDB数据库引擎，翻阅文档也发现InnoDB已经是MySQL自己从4.0开始内建的引擎。所以拿来比较一下。

4.1        MySQL数据库引擎

下面先介绍MySQL现有的数据库引擎，其中的InnoDB引擎已经不是一个过家家的数据库引擎，其为MySQL提供了许多标准数据库的基本支持。

MySQL支持的引擎种类之多有点吓人，但多少给人一些有点滥的感觉。

                                                                                                                                                              表9 MySQL的DB引擎

4.2        InnoDB的性能

InnoDB引擎的性能可以用冰火两重天形容，网上对它的评价也可谓大相径庭。其实核心问题只有一个。你如何使用事务。其实坦白说所有的数据库，包括Oracle，DB2的频繁的提交事务都将大大影响数据库的性能。

重要的参数如下：

数据库为4.0.22的数据库,使InnoDB作为引擎，

数据文件每个2G，8个

日志文件3个，每个150M

Cache为打开状态

set-variable    = key_buffer_size=384M

set-variable    = query_cache_size=64M

set-variable = innodb_mirrored_log_groups=1

set-variable = innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit= 1 [这个参数很重要]

set-variable = innodb_buffer_pool_size=1G

set-variable = innodb_additional_mem_pool_size=20M

4.2.1           AUTOCOMMIT自动提交

MySQL对于事务的处理有两种方式：

自动提交方式，SET AUTOCOMMIT=1就是每条SQL MySQL都自动帮你提交。这样就相当于每条SQL都是一个事务。

手动提交方式，SET AUTOCOMMIT=0，必须自己写事物的开始（START TRANSACTION）,结束（COMMIT）语句或者回滚（ROLLBACK）语句。

自动提交的方式将使系统的事务成千倍的增加，所以性能自然会下降很多。而有趣的是MySQL的默认事务使用方式居然是自动提交方式，而且好像没有方法进行配置修改，SET AUTOCOMMIT是针对对每个SESSION处理。（后面会提到这个问题）

对于InnoDB的测试我们分为自动提交和手动提交两种，手动提交在连接数据库后，发送SET AUTOCOMMIT=0设置，然后通知开始交易START TRANSACTION，然后再进行后面的操作，在所有的操作完成后，在进行提交COMMIT，（注意只有一次交易事务）。 而自动提交和前面的方式一样。

对比测试数据为100000条。(不是我偷懒不测试大数据量，自动提交太慢)

测试结果数据：

                                                                                                                                        表10 AUTOCOMMIT测试100000条记录

结果数据差别就是这么大，自动提交的性能真是惨不忍睹，所有的修改操作和非自动提交都有200多倍的性能差距。

从上面的结果我们看出，自动提交是一种不可接受的方案，但是如果使用非自动提交有两个限制，现有代码要更改，对于CGI这样的应用，每次就是一个语句进行修改，这样仍然是1个会话1个事务，对于大规模的这样使用效率仍然会造成低下。

正当我对此无比疑惑的时候，zengyu老大告诉我他们修改一个默认参数innodb_flush_log_at_trx_commit提高性能，查询了这个参数的解释如下：

                                                                                                                   表11 innodb_flush_log_at_trx_commit的取值说明

如果你对安全没有苛刻要求，可以忍受极少量错误，而且你的业务是大量小规模的交易，你可以使用innodb_flush_log_at_trx_commit =0来加快处理效率。

既然知道还有窍门，我们将innodb_flush_log_at_trx_commit=0后，再进行一组测试。

                                                                                  表12 innodb_flush_log_at_trx_commit=0下的AUTOCOMMIT测试100000条记录

我们可以看出这样，操作的效率可以大大提高，改写操作的速度都提高了很多。可以看作对于innodb_flush_log_at_trx_commit=0的设置下，InnoDB引擎自己控制提交的时机。

看来如果使用InnoDB，而且你的应用又是大数量级小事务操作（我们公司的业务基本上都是），还是使用innodb_flush_log_at_trx_commit=0比较好，对于一次有大规模的操作最好还是自己控制事务[注]。

4.2.2           InnoDB和MyISAM的性能比较

本是同根生，相煎何太急。J

仍然采用大规模的数据的方式进行测试。测试环境为4.0,数据仍然保留Cache和二进制日志。

                                                                                                                                           表13 MyISAM和InnoDB的性能比较

从测试数据可以看出，MyISAM引擎和InnoDB在性能上基本没有太大的区别，半斤八两。AUTOCOMMIT=0的方式有较好的性能，但其对WEB的应用不是太实用（事务一多性能还是要急剧下降），我们重点比较InnoDB在innodb_flush_log_at_trx_commit=0下和MyISAM引擎的性能。

由于MyISAM的特性而且测试用例是顺序插入, MyISAM引擎占了些便宜。在MyISAM数据文件没有空洞时(删除记录会产生一个没有使用的记录区)，插入记录操作没有进行锁表操作（应该说是写锁），（因为所有的记录都是插入文件末尾）。由于我的测试正好属于这种情况，在测试中MyISAM引擎在INSERT语句上表现优秀，但是对于实际的环境，这个优势估计不会存在。

修改部分MyISAM引擎较好这不能反映真实情况，InnoDB的纪录锁的优势无法体现。[注]

比较图表如下：

                                                                                                                                              图5 InnoDB和MyISAM的性能比较

必须要指明的是，这个测试其实是比较利于MyISAM 引擎的，由于MyISAM的引擎的设计是1个表1个文件的，所以在真实环境，数据文件内部必然有记录碎片，（定期使用交互命令可以优化这个问题），而这些碎片会MyISAM的降低处理性能，而且MyISAM采用文件锁处理模式，不利于真实环境下的并发操作，后面的一些模拟测试也证明了这个问题。

4.3        总结

InnoDB和MyIASM引擎谁优谁劣其实是一个很难衡量的问题，我把我知道的两者的情况都拿出来让你自己对比一下。

                                                                                                                                     表14 MyISAM引擎和InnoDB引擎的对比

个人感觉在大访问量的情况下，InnoDB引擎还是一个更好的选择。

InnoDB现在已经被Oracle收购，作为了Oracle的一个开源项目，一方面我期待有新的强大技术背景的公司大的支持下，InnoDB的性能能得到更大的提高的同时，另一方面，我倒要为它的未来捏把汗。（《Oracle收购InnoDB对MySQL的影响》一文对此有一些讨论）

5               模拟真实环境下的性能

再次强调的是，前面的测试数据都是性能测试，不能作为实际环境的性能数据，这些数据表现的应该是理想环境下的极限性能，其只能作为参考。

既然测试用例不完整，我们就考虑模拟一下真实的环境。

5.1        压力测试

先准备检测一下MySQL在压力情况下的性能变化情况，测试的版本为MysQL4.0 gcc静态版本，使用的配置参数和原来相同。

我先在数据库中插入10000000条记录，然后我在另外一台机器机器上启动1组压力进程对数据库进行查询或者修改操作，测试的进程组包括10，50，100个读的进程，10，50，100个读写进程（读写各一半），这些进程的操作间隔时间为10000微秒（0.01秒执行一次）或者1000微妙（0.001秒执行一次）。其中0.01秒执行一次间隔的压力相对较轻，0.001秒执行一次间隔压力就比较重了，一般用mysqladmin processlist检查，大部分进程的状态都是执行状态。

同时在这台机机器（本机上）测试查询1000000条记录（总记录数的1/10）所耗费的时间。同时为了比较，使用vmstat记录相关的CPU占用率的情况。分别记录压力进程运行后，测试进程运行前后的CPU空闲率。

MyISAM 引擎的测试结果如下：

                                                                                                                                                 表15 MyISAM引擎的压力测试

对于MyISAM引擎，最后一组测试我没能作完，实在是太慢了，在6个小时后我检查发现查询才进行到12477，我几乎崩溃，估计要让他跑完要5-6天，只好作罢。

比较的图表：

                                                                                                                                                    图6 MyISAM引擎的压力测试

通过比较，我们可以看出，在只有读取操作的压力下，MyISAM引擎表现还不错，在压力增加的情况下性能没有出现陡降。而对于读写的混合压力测试情况下，MyISAM引擎的表现急转直下，特别是最后的100个读写0.001s间隔压力进程测试情况下，速度已经不可接受。这是检查MySQL的状态计数变量Table_locks_waited数值非常大，而且远远大于Table_locks_immediate。这表示系统操作中，等待锁的操作非常多。

由于MyISAM引擎采用表锁，所以在读写压力测试下，由于写操作锁会阻塞所有的读取锁，而且优先基本大于读取锁，所以此时读取操作必须等待。而且由于最后的压力测试写操作频度很高，所以造成了几乎1秒1条的读取速度。而且值得注意的是此时CPU性能压力并不大，系统的能力并没有得以发挥。

为了横向比较MyISAM引擎和InnoDB引擎在不同压力的表现，我也测试了InnoDB在压力情况下的表现。

                                                                                                                                                 表16 InnoDB引擎的压力测试

比较的图表：

                                                                                                                                                    图7 InnoDB引擎的压力测试

可以看出InnoDB引擎在压力测试面前表现得不错，基本上是随着压力的增加，速度呈现线性的下降（注意第2步测试的进程是50个为第一步的5倍）。

为了方便比较，我也给出InnoDB和MySQL的两个比较图表，必须指出的是，这样的比较意义有一定的局限性。因为并不能保证每个连接对于两种引擎的压力都一致。

MyISAM和InnoDB的读取压力测试比较

                                                                                                                                  图9 MyISAM和InnoDB的读写压力测试比较

从对比图表可以看出[注]，InnoDB引擎可以更好的承担查询压力。

MyISAM 为了避免在大压力的环境下性能下降，可能必须将表分割的更细。

5.2        多表访问环境模拟

前面测试的方式都是使用单张表进行测试，在多张表的情况下，MySQL的表现如何？我打算这样模拟，采用10张表，每张表存放5000000条记录，在另外一台机器上，每个进程都查询访问一个表，查询频率为0.005秒一次，查询记录为随机数选取，同时在本机上进行插入，查询，更改，删除1000000条记录的操作。

对于MyISAM引擎，同时由于增加了表的数量，调整了一些相关的参数，

set-variable    = key_buffer_size=512M

set-variable    = query_cache_size=128M

对于InnoDB引擎，仍然使用原来的配置，同时由于测试机器发生了更改[注]，为了对比的公正性，我重新测试了没有压力（没有程序同时访问）情况下的对比数据。

                                                                                                                                                    表17 多表访问的环境模拟

对比图表为

                                                                                                                                                   图10 模拟多表访问的测试

在非模拟情况下，MyISAM 引擎有少量优势，但是在模拟环境下查询记录情况,MyISAM引擎的表现远慢于InnoDB的表现，对于这个我感觉有点奇怪，因为此时不应该有锁排队的情况发生。？

在压力情况下，MyISAM引擎的插入语句性能看似不错，但是这是由于在我的测试用例中，MyISAM引擎不用加入写锁锁定表。（见前InnoDB和MyISAM引擎的对比测试章节）

由于所有的操作都是顺序操作，而且没有加入写操作，感觉测试比较倾向于MyISAM引擎[注]。

5.3        网络访问速度

前面的测试基本都是基于本机操作访问数据库的操作，但是更多的应用是基于网络访问。所以也业考虑测试一下网络环境下MySQL访问速度。

基于网络的访问分为2种，一种是利用长链接，一种是短链接，就是每次访问都重新进行一次链接。

测试环境，数据库使用MyISAM引擎，使用Cache。网络带宽为100Mbps。

由于对于1个服务器，可以循环使用的SOCKET端口是有限，所以我只执行了20000次操作进行比较，由于测试的数据较少，对于这些数据比较差距就可以了。

比较图表如下：

                                                                                                                                                 图11 网络环境性能下的测试

对于网络的访问，网络长链接的访问速度是本地执行速度的3-4倍（降低了60%），网络短链接的访问速度是本地执行速度的7-8倍（降低了85%）。[注]

注意这个速度只是单链接下的极限速度，所以这并不代表MySQL在网络环境下的处理速度（但个人认为极限速度不可能超过2倍），但是我们还是看出，MySQL在网络环境下速度有相当的下降[注]。而使用网络短链接要付出连接和断开连接的成本。

STMT的操作方式（参看STMT的章节）由于不用重新传递分析SQL，在网络环境也表现了出了优于其他方式的性能。

基于MySQL在网络环境中的表现差强人意，如果硬件条件允许，在作DB服务器设计时，将DB服务器和MySQL放在同一个机器下可能是一个更好的选择。

5.4        小结

如何模拟真实的环境一直是我比较头大的问题，不管我如何模拟，好像感觉仍然只能专注到某个方面从而有相对性，而且模拟现实和测试极限性能本身可能就相互矛盾。这些测试数据更多的应该是一种参考，使你了解系统地性能极限和性能瓶颈[注]。如果你能有更好的方法模拟真实环境，请通知我。

6               STMT的测试

MySQL的API为什么在网络环境下表现较差？一方面由于MySQL的API全部直接使用SQL语句执行，SQL的分析成本是比较大的，同时每次都要传递SQL也占用带宽，一方面由于MySQL的结果是采用字符串的方式传递的，也是一个比较耗费资源的事情。为了解决这些不足，MySQL推出了一组新的API STMT。(我到现在没有搞明白是什么的缩写？)

STMT是MySQL 4.1版本的一个重要的噱头，其提供一种和以往的API不同的形式访问数据库。其类似Oracle等提供的绑定变量的API方式。

这样的方式基本方法是使用一个绑定参数的SQL，这个SQL在使用前先要进行分析准备（mysql_stmt_prepare），在执行前还必须，为输入参数绑定变量（mysql_stmt_bind_param）比如查询参数，插入语句参数，为输出参数绑定结果（mysql_stmt_bind_result），比如查询语句。下次在进行插入和更新操作时就可以直接修改相关的参数，由于SQL语句已经经过分析，再次处理的速度可以得到大大的提高。

STMT的API基本是由自己独立的一套，如mysql_stmt_execute，mysql_stmt_errno

STMT的重要优势是避免了语法分析SQL的成本，同时由于STMT语句在次执行的时候无需传输大量的SQL语句数据，可以大大减少网络流量，在网络环境下也更有优势。

为了进行横向的比较，我将测试分成两种方法，一种是使用每次都使用Prepare语句的测试，一种是只在第一次使用Prepare语句，后面的操作只更新邦定变量。查询的操作都完成转储结果集。

                                                                                                                                                          表18 STMT的性能测试

相关数据的比较图表如下。

STMT API性能测试

从上面看出，如果每个语句都使用Prepare，居然还慢了好多。看来这种STMT模式并不适合SQL语句要反复重新更换的环境。否则Prepare的成本反而更加突出。（只能执行一次CGI的环境就不要用这个东西了。）

而只在第一次执行前使用Prepare的测试结果令人惊喜，普遍的速度提高在30%左右，这个速度的提升是非常鼓舞人心的。所以看来STMT的API非常适合数据库服务器SERVER类的开发。

另外要补充说明的是STMT的语句不使用查询Cache。

7               MySQL的优化

熟悉Oracle优化的人都知道Oracle的重要优化对象就是SGA区,其实MySQL也是一样的.MySQL的参数配置在my.cnf文件中。[注]。

7.1        配置参数

MySQL有两种途径途径了解其的配置参数，一个是MySQL交互模式下的命令SHOW  VARIABLES，一个使用mysqladmin variables 查询。

MySQL的配置参数分为2种，全局的和局部的。局部的配置变量可以在每次会话中自己更改。

从MySQL 4.0以后开始，在SHOW VARIABLES中显示的参数，大部分可以动态使用SET命令进行更改。

基本参数配置：

查询的Cache的是从MySQL4.0版本开始提供的功能。相关的参数为：

MyISAM的索引参数：key_buffer_size为MyISAM引擎的最关键的优化参数之一。

InnoDB使用的参数：InnoDB的参数较少，笼统而不细致，内存的管理多由InnoDB引擎自己负责，主要的缓冲就是innodb_buffer_pool_size参数分配的缓冲。这样配置倒是简单了，但没有了细致优化乐趣。

Log的参数：MySQL的日志有6种，查询日志，慢查询日志，变更日志，二进制变更日志，告警日志，错误日志。my.cnf中可以配置日志的前缀和日志参数。日志是监控数据库系统的重要途径。

打开文件参数：

关于连接通信的参数：

每个会话使用的buffer设置，默认使用my.cnf的配置，也可以使用每个会话设置。不要设置的过大。

对于磁盘缓式写入的一些选项，delay_key_write，flush，flush_time参数可能可以进一步提高MyISAM引擎的性能，但是在服务器Crash的时候，可能会丢失数据，造成表损坏。

MySQL对于插入语句支持一个选项INSERT DELAYED，如果有这个选项，MySQL将这些插入语句放入一个队列，并不马上读入磁盘。delay_insert_XXX的选项都是配置这个功能，

MySQL创建表的时候也有一个选项，DELAY_KEY_WRITE，有这个选项描述的表的键发生改动后，改动可以缓冲在key_buffer中，不立即回写磁盘。

关闭某些选项：关闭某些选项可以加快MySQL的运行速度，这些选项在MySQL SHOW VARIABLES 中显示为have_XXX 的变量。

其他参数：

7.2        运行状态监控

MySQL有两种途径途径了解其的运行状态，一个是MySQL交互模式下的命令SHOW STATUS，一个使用mysqladmin extended-status  。两种方法异曲同工，通过观察其运行状态可以了解我们的参数设置是否合理，是否有要优化的表和数据。

SHOW STATUS显示了MySQL从运行开始到现在为止状态，大部分为一些计数器，使用FLUSH STATUS可以重新对各种状态变量进行计数。

                                                                                                                                                      表19 MySQL的状态计数器

InnoDB的状态监控的要在交互模式下使用show innodb status命令。相对的可以利用InnoDB状态参数也过少。

7.3        参数调整

了解了参数的含义，剩下的事情就是如何设置一个合理的MySQL参数了。下面我们结合几个关键参数讲解以下如何通过根据状态了解参数是否设置合理。

比如MySQL的重要参数table_cache,如果设置过小，SHOW STATUS显示的变量Opened_tables 会迅速增加，而正常状态下应该是保持稳定或者缓慢增加。

又比如想要了解查询Cache的状态，可以查询SHOW STATUS显示的Qcache_XXX 变量。变量Com_select表示查询语句的数量（实际放入Cache的语句数量是Qcache_inserts），Qcache_hits表示查询在Cache中命中的数量，除一下就可以得到你的查询Cache的命中率。是否值得使用查询Cache，就很明确了。

Com_XXX相关的状态计数是反映数据库处理的SQL语句的。通过这写计数器你可以了解你的数据库什么操作更多，从而更好的优化。

key_buffer_size对于MyISAM引擎是最重要的参数之一，其的使用情况可以通过key_XXX参数了解，key_blocks_used 状态计数器可以让你了解Key_buffer的使用情况，Block的尺寸默认是1024字节，MySQL4.1.1版本后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率。

最大连接数max_connections 是否设置合理可以查询SHOW STATUS的变量Connections，Connections变量为尝试连接的数量，监控其的增长速率就也可以知道客户对连接数的需求程度。

检查Table_locks_waited的数量以及和Table_locks_immediate的比率，可以了解查询操作是否有过多的锁等待，如果等待数量很大，建议你考虑更换引擎或者继续分割你的表。

Thread_XXX相关的状态参数可以告诉你MySQL的线程处理情况，Thead_running和Thread_connected的比率可以让你知道大部分连接的状态如何，是在SLEEP还是RUNNIG。

如果状态中的Created_tmp_disk_tables和Created_tmp_tables的比率过大，表示大量的临时表是在磁盘上创建的。这样效率当然是很低的，所以建议调整tmp_table_size的大小。

Slow_queries也是一个关键的计数器，MySQL将查询时间超过long_query_time的查询时为一个慢查询，出现一个慢查询就会将Slow_queries+1 。而long_query_time的默认设置是10秒，这个时间是比较长的，你可以调整的更加短一些，同时MySQL提供了日志可以记录慢查询的查询语句。my.cnf中可以配置log-queries-not-using-indexes参数记录没有使用索引查询的日志,log-slow-queries参数记录慢速的查询日志，在数据库设计初期阶段，通过这两个日志可以了解数据表的设计是否合理。

7.4        飞得更高

当然，调整MySQL的配置参数只是优化的一种方式。飞得更高的方法还很多。

kenix问过一个奇怪的问题，如果想最小的成本进行优化,如何进行？对于这个问题我和sunbirdcui的答案一致，把硬盘改为RAID10（0+1）的。对于提高性能，升级硬件可能是最简单最省力的方式，而且对于我们大部分的DB，在磁盘空间不够前，机器的性能可能已经远远跟不上服务增长而要分布了。所以建议DB服务器不要采用RAID 5而采用RAID 10，这样可以轻松的提高20-30%的性能。网上能找到的相关比较的文章是《常见RAID模式性能比拼》，不过可惜是其比较的不是SCSI硬盘。

而对于内存的使用方面，MySQL好像没有过高要求，主要的除了每个连接使用的read_buffer_size 和sort_buffer_size尺寸的内存外，全局有一个Qcache使用query_cache_size尺寸的cache。MyISAM引擎主要使用的是key_buffer_size尺寸的内存,InnoDB主要使用内存就是 innodb_buffer_pool_size 尺寸内存。而且InnoDB可能还有一个2G内存使用限制（是否真没有这个问题有待验证）。所以依靠大规模增加内存提高MySQL性能未必有效。MySQL自己在内存管理可以提高的地方还很多。

其他的方面也还有很多，比如Linux的内核优化，2.6的内核的更加倾向于I/O的应用。大家也许记得我前面有台测试机器使用的是380G3 2.4.21的内核，一台是380G3 2.6.8.1的内核我们把它们相同测试的数据列出来。但请大家注意的是，由于不是同一台机器（型号倒是一致），参考意义有待进一步证实。

                                                                                                                                                 表20 不同内核下的性能比较

另外的一个非常重要的决定性能的因素就是你对数据表的设计了。这部内容分包括数据表结构的设计，范式和索引，以及MySQL本身的查询优化.但是它们不在本文的讨论范畴之内了。

8         后记

终于，终于在2005年把手上的测试数据整理为文档了。其实手上最早的测试记录已经是今年6月的了。毕业后工作之外几乎就没有写过5页以上的文档，这个文档几乎又是半途而废。激励自己完成这个稿子的事情很偶然，一次培训，liddlechen介绍说MySQL Insert的语句快于Select，我当即反驳，但是回来翻看自己的测试记录，却的确如此。一方面惭愧，一方面感觉到测试的数据如果不文档化几乎没有任何意义。

8.1        如何看待测试数据

如何看待这些测试数据？正如我一直强调的任何Benchmark测试所产生的数据都只是参考，对于真实环境都是一种模拟，比如对于查询，如果是多个表的真实环境，数据表的查询效率可能要低很多，因为相关的索引必须在内存中换进换出，效率肯定成倍降低。

本文的作用是给出比较，对比参考，让你了解什么什么状态下可能具有更好的性能，比如MySQL的本地环境下的性能是网络环境下的性能的3倍多。STMT比原有的API可以提高处理30%的性能，这才是Benchmark参考的意义。

8.2        鸣谢

感谢 zengyu提供InnoDB 测试的指导，感谢wang,chris对文档提供意见。同时也向对提供机器给我测试的echoqin,penghao表示谢意。

记得和wang聊天的一句话，“搞技术的就是在自己领域YY的人”。把自己的YY作品献给5年（9年）的技术生活。

9         附录：

9.1        测试程序

下面是我的测试程序，IP和密码部分已经注释了，请参考时注意，另外，所有的测试程序基于我自己的一套程序库zenlib，如果有需要，可以请向我索要，您也可以直接使用MySQL API替换之。而且对于Benchmark，我建议你自己写自己的测试用例，这样才能有最好的效果。

9.2        参考书目

参考书目：

9.3        作者说明

曾星（Sail）  工作于 腾讯科技（深圳）有限公司  互动娱乐事业部

Tencent Technology (Shenzhen) Company Limited  IED

EMail：sailzeng@tencent.com

本着自由的精神，阅读者可以无须授权就可以自由的转载这个文档，我只保留作者的署名权利，也就是说，你转载只需保留这段说明和文档的完整性。