MyISAM和InnoDB引擎优化分析

数据库  /  管理员 发布于 5年前   199

这几天喻名堂在学习mysql数据库的优化并在自己的服务器上进行设置，喻名堂主要学习了MyISAM和InnoDB两种引擎的优化方法，它们各有优缺点，一般在实际应用中将两种引擎结合起来使用效果会更好。喻名堂测试的硬件配置以及软件环境如下：

服务器型号：IBM S226
CPU：至强四核
内存：4G
硬盘：两个80G做RAID1
系统：windows server 2003 SP1 32位企业版
Mysql版本：5.5
根据自己服务器的实际情况，优化过和参数如下：

一、公共选项
skip-external-locking //避免MySQL的外部锁定，减少出错几率增强稳定性。
skip-name-resolve //禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程亿恩科技主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
max_connections = 1024 //指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提示，则需要增大该参数值。
query_cache_size = 16M //默认为0，就是不启用。指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
# 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；
如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
sort_buffer_size = 6M //每个线程的排序缓存大小，该选项对排序order by、group by起作用。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
record_buffer=16M //每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为2M以上
table_cache = 512 //为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。Mysql对每个唯一打开的表需要2个文件描述符。

二、MyISAM选项
key_buffer_size = 256M //key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。
注意：该参数值设置的过大反而会是服务器整体效率降低！
read_buffer_size = 4M //读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！
myisam_sort_buffer_size = 64M //默认为16M。设置、恢复、修改表的时候使用的缓冲大小，值不要设的太大。
join_buffer_size = 8M //联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！

三、InnoDB选项
innodb_buffer_pool_size＝1G //一个Innodb最重要的参数，这个参数和MyISAM的key_buffer_size有相似之处，但也是有差别的。这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲，设置得越大，存取表里面数据时所需要的磁盘I/O越少，一般是内存的一半，不超过2G，否则系统会崩溃。为Innodb加速优化首要参数。该参数分配内存的原则：这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用ＤＢ服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小＋１０％左右做为这个参数的值。

例如：数据大小为50Ｍ,那么给这个值分配innodb_buffer_pool_size＝64Ｍ
innodb_additional_mem_pool_size=16M //用来存放Innodb的内部目录这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。

innodb_log_file_size=256M //在日志组中每个日志文件的大小，一般是innodb_buffer_pool_size的25%，官方推荐是innodb_buffer_pool_size的40-50%。一般控制在几个ＬＯＧ文件相加大小在２Ｇ以内为佳。具体情况还需要看你的事务大小，数据大小为依据。说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。

innodb_log_files_in_group=2 //指定你有几个日志组。分配原则：一般我们可以用２－３个日值组。默认为两个。

innodb_log_buffer_size＝3M //事务在内存中的缓冲。分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在oracle等数据库了解这个，一般最大指定为３Ｍ比较合适。

innodb_flush_logs_at_trx_commit＝0 //控制事务的提交方式分配原则：这个参数只有３个值，０，１，２请确认一下自已能接受的级别。默认为１，主库请不要更改了。性能更高的可以设置为０或是２，但会丢失一秒钟的事务。说明：这个参数的设置对Ｉｎｎｏｄｂ的性能有很大的影响，所以在这里给多说明一下。当这个值为1时：innodb 的事务LOG在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。

从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。