MySQL中my.cnf参数设定解析

3．存储引擎
在MYSQL 3.23.0版本中，引入了MyISAM存储引擎。它是一个非事务型的存储引擎，成为了MYSQL的缺省存储引擎。但是，如果使用设置向导来设置参数，则它会把InnoDB作为缺省的存储引擎。InnoDB是一个事务型的存储引擎。

创建表的时候，可以为表指定存储引擎，语法如下：

CREATE TABLE t (i INT) ENGINE = MyISAM

CREATE TABLE t (i INT) TYPE = MyISAM

如果没有指定，则使用缺省的存储引擎。也可以使用ALTER TABLE来更换表引擎，语法如下：

ALTER TABLE t ENGINE = MyISAM

同一数据库中可以包含不同存储引擎的表。

事务型表具有以下特点：

?        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。