MySQL InnoDB 的性能问题讨论

MySQL最为人垢病的缺点就是缺乏事务的支持，MyISAM 性能虽然出众，不是没有代价的，InnoDB 又如何呢？InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！

InnoDB的表空间分成三种，一种是裸设备，一种是若干个 ibdata 文件（缺省方式），再一种是 Per-Table 文件，第一种用得少，第二种显然比第三种效率更差，本文的讨论基于 Per-Table，也即 innodb_file_per_table 配置参数。

现象重现：导出一个几百万行数据、带若干索引、有过频繁更新的表出来再导入，如果能以真实环境下的表来做测试就更理想，到 data 目录下观察对应的数据文件的 size 增长情况，会发现前 1G 速度相当令人满意，可是越往后效率越低，到后面基本就是蜗牛般的速度了。

不是只有导入才会让你慢得受不了，alter column/index 都会这样。。。

InnoDB 跟磁盘相关的文件存储，可以分成两个部分，一个是日志文件，另一个是数据文件。当有频繁的 INSERT/UPDATE 操作的时候，InnoDB 需要分别写入这两个文件，日志文件是顺序操作，数据文件包括了表数据和索引数据两个部分（和 MyISAM 直接拆开成表文件和索引文件不同，InnoDB 的表和索引是在同一个文件当中的）。

InnoDB 的索引用的是 BTREE 格式，如果当前更新的记录影响到索引的变化，逻辑上就存在三个操作，从原来的 BTREE 找到并摘除原来这行的记录并做调整、插入行数据、根据新数据查找 BTREE 相应的位置并重新插入新索引信息，假设索引数为 N，相应的逻辑操作数就为 1 + 2*N，显然这些信息不能保证在同一个磁盘连续空间上，因此需要 1 + 2*N 次的磁头移动，行数越大、文件尺寸越大，磁头的移动幅度也就可能越大，带来的后果显然是极差的磁盘 IO 效率。

MySQL 对于 MyISAM 的的磁盘 IO 优化是如何建议的呢？使用符号链接将表文件和索引文件分别指向不同的不同的目录，分散到不同的磁盘上以增加系统的访问速度。这种优化方式，在 InnoDB 上完全没有可能性！

如果有 tablespace 支持，磁盘效率问题就好解决了，一如商业数据库的做法，将日志、表文件、索引文件分别分布到不同的表空间也就是物理磁盘上，可是 MySQL 一直到 5.1 都没有提供 tablespace 功能，仅在 NDB/NDBCLUSTER 中才提供，但是 -- "CREATE TABLESPACE was added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster."。

不知道 Yahoo 等大网站是怎么解决这个难题的。。。头痛。。。考虑切换到 PostgreSQL 中。。。

日志文件还是比较好优化的，一则可以去掉默认写入的bin-log，不让他写update的log，二则在my.cnf里面开大log buffer，减少log flush次数：

innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 0

其实Oracle数据库也是每个tablespace一个数据库表文件的，而且数据和索引也是写在一起的，不知道oracle是怎么存储的。

MySQL的表我尚未在生产环境中使用到百万级别，现在JavaEye也就10万级别的记录，速度很快，所以我还不知道到百万级别会碰到你说的问题。

oracle的索引，也是可以单独指定表空间的。

既然mysql暂时没有tablespace，那就优化一下磁盘性能好了。换更高转速更大缓存的硬盘设备，效果能好一些？

我们公司的一个mysql数据库是用的isam，现在已经有十个左右百万级的大表，性能还可以。不过商业应用采用mysql真是天生的失败，没有databaselink，需要和其它数据库交互的时候非常的麻烦。

参考 PostgreSQL 的语法：

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]


CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

在创建表、索引的时候，分别创建到不同的表空间，并将表空间放在不同的磁盘上，Oracle 也可以是同样的处理。

InnoDB 的日志文件是好优化，除了 robbin 说的，还可以指定日志文件的路径，和数据文件分布在不同的磁盘上，可是数据文件无法优化磁盘性能。

http://forum.mysqlperformanceblog.com/s/t/17/
http://software.newsforge.com/article.pl?sid=04/12/27/1243207
看看，有些建议。

http://dev.mysql.com/doc/refman/5.0/en/estimating-performance.html
按照这篇说的一个500,000的表，在没有cache的情况下，命中一条记录需要磁盘操作数:
log(500,000)/log(1024/3×2/(3+4)) + 1 = 4 seeks.
写入需要:4seeks查找，两次seeks进行更新索引和写入行。

所以对于大表:1.有足够多的内存做index的cache。2.避免全表检索。

题外话！有一个地方不太明白，MySQL提供的MaxDB从介绍上看来，性能，稳定性等各方面都有无可比拟的优势
但是在好几个论坛上数据库板块很少有人讨论，不知为何maxdb的人气这么差

我觉得磁盘IO问题可以有很多方法解决吧，不一定局限于tablespace。比如透明的使用RAID，不就提高了磁盘性能了么？

而且，PostgreSql就性能来说，在网上一般的比较资料中，都是比MySQL低的。所以不能单纯比较一项功能是否支持，就得出性能是否好的结论吧。

postgresql如果做了适当的索引和优化，一般情况下性能也够用了。但是postgresql的功能点和成熟度上比mysql强太多了，基本上和oracle有一拼.
关键的一点，postgresql是BSD类许可证的，商业上应用没有心理负担。

资料太少，去网上翻一圈，资料少的可怜。我九月底在amazon订了一本书，MaxDB for enterprise，几次延期，现在还没拿到。

抓下来代码看一看，光搭个build environment就要用到perl，python。这两门语言我都不会，就没有深究下去。现在别的事忙得很，以后再有时间研究吧。

我对MaxDB的兴趣是很大的，SAP做的东西，用来跑SAP系统的，并发事务处理的效率肯定不低。

现在InnoDB给Oracle买去了，MySQL应该会在MaxDB上下更大功夫的。

兄弟也对MaxDB感兴趣？