MySQL与Oracle主键Query性能测试结果

dickrong

MySQL与Oracle主键Query性能测试结果
　　From：DBA同事的测试结果
　　为了让大家更详细的了解Oracle和MySQL的差异和适用场景，为数据库选型提供帮助，我们对MySQL和Oracle的按主键Query做了一些测试。希望对大家在数据库选型和了解Oracle/MySQL异同时有所帮助。
　　测试结果总结如下：
　　1. 按主键读：

　　SQL形式：SELECT * FROM table WHERE>　　1.1. 主键为数字。如果所有ID均不存在，纯比较SQL解析能力。
　　MySQL解析SQL的速度约是Oracle的2倍。
　　原因在于MySQL优化器代码简单，动态规划的深度限制为64层，能较好的控制解析SQL的时间。
　　1.2. 主键为数字。如果所有ID均存在，且完全随机分布。
　　低并发（=32），落后Oracle一半左右。
　　1.3. 主键为数字。如果所有ID均存在，ID随机范围控制在一定范围。（ID 在 [minID, maxID] 范围内）
　　随着ID范围的缩小，实际访问的数据就越小，重复读到一条数据的概率增高，多次测试后，MySQL与Oracle的差距在减小，当控制的范围数据约等于InnoDB Buffer Pool的大小时，与Oracle的差距不再明显。
　　减少数据量的测试结果也基本相似，数据量减小到约等于MySQL Buffer Pool的大小时，与Oracle的差距较小。
　　由此可以判断，MySQL的缓存替换算法较Oracle存在一定的差距。InnoDB的预读准确率没有Oracle高，所以当数据量减少的时候，Oracle的预读准确率并不会有太大的提高，但是InnoDB随着数据量的减少，预读的准确性就会有较大的提升。
　　1.4. 主键为字符串。
　　Oracle的效率和MySQL差距缩小，低并发时MySQL还略高于Oracle。
　　同比MySQL主键为数字和字符串时，查询性能差距比较小，主键为字符串效率略低与数字主键。Oracle差距则很大，浮动最大达50%。
　　2. 随机插入：
　　SQL形式：INSERT INTO table (col) VALUES (?);
　　2.1. 主键为数字自增。
　　低并发（=32）时，MySQL的插入效率明显下降，Oracle采用序列，默认情况下插入速度高于MySQL，并发量提高这个差距越扩大。
　　原因在于，Oracle的序列有自己的锁，不与表的锁混用，MySQL默认情况下，自增的锁与表锁合一，每次获取自增值都回带来一次表锁，这会严重的限制并发量。同时MySQL开启了binlog，这会比Oracle带来更多的日志记录，除了undo log/redo log，还多了binlog。
　　但是MySQL InnoDB在5.1版本中提供了一个innodb_autoinc_lock_mode参数，如果设置为0，则出现上述情况，如果设置为1，采用 Auto_increment 独立的轻量锁，则效果改善很多，高并发时（>=32）与Oracle也基本本没有差距，在5%左右，到128依然没有明显的差距。
　　2.2. 主键为字符串时。
　　字符串无需的情况下，MySQL的插入效率明显下降，低并发时（=32）可以达到 50%。Oracle则变化不大。
　　原因在于，MySQL InnoDB的主键和数据是一起的，如果主键无序插入，每次插入都会带来主键索引的调整，这是一笔非常大的开销。即使字符串有序插入，由于字符串占用长度大于数字，存取主键的开销会略大，较数字主键依然会有15%左右的性能差据。
　　3. 随机更新：

　　SQL形式：UPDATE SET col=xxx WHERE>　　无论主键为数字或者字符串，MySQL的更新效率都要高于Oracle。
　　数字主键时，低并发时（=32）效率高10%左右。
　　字符串主键时，在数字主键上再提高5%左右，MySQL在处理字符串主键时比Oracle效率高。
　　原因在于MySQL的轻量，UPDATE只需要一个主键行锁，更新主键索引即可，没有任何主键位置的变动，也不需要回表访问数据块，所以效率较高。
　　总的来说，MySQL轻量、InnoDB的聚集索引，适合在简单的业务场景使用，性能并不逊于Oracle。存在复杂的SQL，高并发场景，MySQL单实例性能是逊于Oracle的，但是没有事务的情况下依靠分库，可以化解这些问题。但是复杂业务逻辑、高并发的事务处理，并不适合MySQL，Oracle更适合这种场景。
　　MySQL和Oracle都有自己适合的地方，合适的就是最好的。
　　__________ Information from ESET NOD32 Antivirus, version of virus signature database 5448 (20100913) __________
　　The message was checked by ESET NOD32 Antivirus.
　　http://www.eset.com
　　__________ Information from ESET NOD32 Antivirus, version of virus signature database 5448 (20100913) __________
　　The message was checked by ESET NOD32 Antivirus.
　　http://www.eset.com