事务型表具有以下特点：

Ø        Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

Ø        You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).

Ø        You can execute ROLLBACK to ignore your changes (if autocommit is disabled).

Ø        If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)

Ø        Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

非事务型表具有以下优点：

Ø        Much faster

Ø        Lower disk space requirements

Ø        Less memory required to perform updates

4．MyISAM存储引擎

下面MyISAM的参数是MySQL手册推荐的参数，据说适应于大部分情况。对于如何监视参数设置是否合理，仍然没有头绪。

max_connections=200

read_buffer_size=1M

read_rnd_buffer_size＝8M

sort_buffer_size=1M

Read_buffer_size

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increse this value.

Read_rnd_buffer_size

When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

Bulk_insert_buffer_size

该参数于4.0.3中引入。MyISAM使用一个树型的缓冲区来加速大量的插入，如INSERT…SELECT，INSERT…VALUES(…),VALUES(…),…,LOAD DATA INFILE等。该参数指定了缓冲区的大小。缺省值为8M，设置为0则表示不使用该优化。

如果不使用MyISAM表，则可以将其设置为0。

5．InnoDB存储引擎

参考了很多资料，都没有明确地表明如何优化InnoDB参数，以及如何监视这些参数设置是否合理，只有根据MySQL用户手册上面的介绍来进行设置。

innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。

innodb_flush_log_at_trx_commit

该值指定InnoDB记录日志的方式。如果设置为1，则每个事务提交的时候，MySQL都会将事务日志写入磁盘。如果设置为0或者2，则大概每秒中将日志写入磁盘一次。（还不清楚0和2的区别）

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。

根据MySQL手册，在存在丢失最近部分事务的危险的前提下，可以把该值设为0。

innodb_log_file_size

上一页  [1] [2] [3] [4]  下一页