浅谈MySQL数据库性能优化(一)

MySQL数据库是 IO 密集型的程序，和其他数据库一样，主要功能就是数据的持久化以及数据的管理工作。本文侧重通过优化MySQL 数据库缓存参数如查询缓存，表缓存，日志缓存，索引缓存，innodb缓存，插入缓存，以及连接参数等方式来对MySQL数据库进行优化。

缓存参数

这里先引用一句话，从内存中读取一个数据的时间消耗是微秒级别，而从普通硬盘上读取是毫秒级别，二者相差3个数量级。可见，想对MySQL数据库进行优化，合理调配缓存参数显得更为直接

表缓存

相关参数： table_open_cache

指定表缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值，如果发现open_tables等于table_cache，并且opened_tables在不断增长，那么就需要增加table_open_cache的值了。注意，不能盲目地把这个参数设置得很大，如果设置太大，会引起文件描述符不足，造成性能不稳定或者数据库连接失败。建议为512

查询缓存

相关参数： query_cache_size / query_cache_type

QC（注：查询缓存简称） 主要用来缓存 MySQL 中的 结果集，也就是一条SQL语句执行的结果集，所以仅仅只能针对select 语句。如果启用了QC 功能，MySQL在接到select 请求后，如果该语句满足QC的要求，MySQL 会直接根据HASH算法将接收到的select 语句以字符串方式进行hash，然后到QC中直接查找，如果已经在缓存中，该select 请求就会直接将数据返回，从而省略了后面所有的步骤（如 SQL语法解析，优化器优化以及存储引擎请求数据等），极大的提高性能。

QC 的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置缓存记录集的内存大小，后者设置在何场景下使用QC 。

在以往的经验来看，中等规模的网站，query_cache_size 设置 256MB 足够了。当然，还可以通过计算QC的命中率来进行调整。

Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%

query_cache_type有三种选择：0（OFF，不使用QC），1（ON，默认使用QC ），2（DEMAND，默认不使用QC）。

为什么加上“默认”？MySQL还支持动态使用缓存的SQL语法，如下：

# 强制使用缓存
SELECT SQL_CACHE id FROM table

# 强制不使用缓存
SELECT SQL_NO_CACHE id FROM table

日志缓存

相关参数：binlog_cache_size

如果数据库中没有大事务，写入不是特别频繁，2MB～4MB是一个合适的选择。但是如果数据库大事务较多，写入比较频繁，可适当加大。使用的时候，还可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件来缓存了。

索引缓存

相关参数：key_buffer_size

这个是对MyISAM表性能影响最大的一个参数，用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果有足够的内存，这个缓存区域大小可以设为所有的 MyISAM表的索引大小的总和，即 data 目录下所有*.MYI文件大小的总和。

注意，由于 MyISAM 引擎只会缓存索引块到内存中，而不会缓存表数据库块。所以，查询SQL语句一定要尽可能让过滤条件都在索引中，以便使用到索引缓存来提高查询效率。

计算索引缓存未命中的概率：

Key_reads / Key_read_requests * 100%

插入缓存

相关参数：bulk_insert_buffer_size

用于使用 MyISAM引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件，默认8M，建议不要超过32M

insert … select …
insert … values (…),(…),(…),…
load data infile… into… /* 非空表 */

InnoDB缓存

innodb_buffer_pool_size参数是影响InnoDB存储引擎性能的最为关键的一个参数，设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数，当然，可能更像是 Oracle 的 db_cache_size。简单来说，当操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会涉及到这个内存区域。

当然，可以通过计算缓存命中率，并根据命中率来调整这个参数的大小：

(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

innodb_additional_mem_pool_size 参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。随着数据库对象越来越多，需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数的大小是相对稳定的，没有必要预留非常大的值。如果InnoDB引擎用光了这个池内的内存，InnoDB引擎就开始从操作系统申请内存，并往MySQL错误日志写警告信息。默认值是1MB，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小。

innodb_log_buffer_size 参数是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

innodb_flush_log_trx_commit 参数对 InnoDB引擎日志的写入性能有非常关