本文共 3281 字，大约阅读时间需要 10 分钟。
网上有很多评论myisam和innodb读写性能对比。 读myisam要比innodb要快，为啥快？ 我的论点是：myisam直接从磁盘里拿数据，而innodb要分两步，innodb要从内存里首先获取数据，如果没有再到磁盘里拿。而且一开始innodb要有个加热的过程，也就是说，内存里的数据不是一下子就缓存，而是一点一点的缓存那些热数据。如果你的内存小，数据库里的数据量要大于buffer_pool_size设置的值，并发较低，性能就下降。 这也就解释了在这个场景里，读myisam要比innodb要快。 innodb玩的是内存，内存越大，它的优势才能发挥出来。 myisam玩的是硬盘IO，转速越快，它的优势才能发挥出来。 数据库做RAID10较合适。 大并发测试 innodb_buffer_pool_size=11G sync_binlog=0 innodb_flush_log_at_trx_commit = 0 mysql 5.1.43 + innodb_plugin 1.0.6 [root@test ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --max-requests=10000 --num-threads=100 --mysql-host=192.168.1.11 --mysql-port=3306 --mysql-user=admin --mysql-password=admin123 --mysql-db=test --mysql-socket=/tmp/mysql.sock run sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Running the test with following options: Number of threads: 100 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Maximum number of requests for OLTP test is limited to 10000 Threads started! Done. OLTP test statistics: queries performed: read: 140014 write: 50005 other: 20002 total: 210021 transactions: 10001 (1549.15 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190019 (29433.80 per sec.) other operations: 20002 (3098.29 per sec.) Test execution summary: total time: 6.4558s total number of events: 10001 total time taken by event execution: 643.9687 per-request statistics: min: 9.24ms avg: 64.39ms max: 450.00ms approx. 95 percentile: 150.97ms Threads fairness: events (avg/stddev): 100.0100/6.69 execution time (avg/stddev): 6.4397/0.01 ======================================================================================================== [root@test ~]# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --max-requests=10000 --num-threads=100 --mysql-host=192.168.1.11 --mysql-port=3306 --mysql-user=admin --mysql-password=admin123 --mysql-db=test --mysql-socket=/tmp/mysql.sock run sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Running the test with following options: Number of threads: 100 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "LOCK TABLES WRITE" for starting transactions Using auto_inc on the id column Maximum number of requests for OLTP test is limited to 10000 Threads started! Done. OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (154.54 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (2936.22 per sec.) other operations: 20000 (309.08 per sec.) Test execution summary: total time: 64.7090s total number of events: 10000 total time taken by event execution: 6436.0518 per-request statistics: min: 9.72ms avg: 643.61ms max: 738.83ms approx. 95 percentile: 665.96ms Threads fairness: events (avg/stddev): 100.0000/0.00 execution time (avg/stddev): 64.3605/0.19 ====================================================================== 在大并发情况下，innodb的性能体现出来了。
本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/729829
转载地址：http://hlghx.baihongyu.com/