浅谈MySQL数据库性能优化 - 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也有一个致命的缺陷，就是当表中数据有变化时，所有引用到该表的 QC 缓存全部失效。所以，当数据变化非常频繁的情况下，使用QC 反而得不偿失。

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

用于在打开了二进制日志（binlog）记录功能的环境中，是 MySQL 用来提高 binlog 的记录效率而设计的一个在短时间内缓存binlog 数据的内存缓存。

如果数据库中没有大事务，写入不是特别频繁，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_additional_mem_pool_size

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

innodb_buffer_pool_size 参数设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果有足够的内存，尽可能加大该参数的值，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中。

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

(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%