mysql数据库优化2

配置优化

MySQL 参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系，

优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。 下面列出了对性能优化影响较大

的主要变量，主要分为连接请求的变量和缓冲区变量

1 ）连接请求的变量：

1. max_connections

MySQL 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当

然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL 会为每个连接提供连接缓冲区，就会开

销越多的内存，所以要适当调整该值，不能盲目提高设值。 数值过小会经常出现 ERROR 1040: Too many

connections 错误，可以通过 mysql> show status like 'connections'; 通配符查看当前状态的连接数量 ( 试

图连接到 MySQL( 不管是否连接成功 ) 的连接数 ) ，以定夺该值的大小。

mysql>show variables like ‘max_connections’ 最大连接数
mysql>show status like ‘max_used_connections’响应的连接数

 max_used_connections / max_connections * 100% （理想值≈ 85%）  如果max_used_connections跟 max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

如何设置 max_connections? 修改 /etc/my.cnf 文件，在 [mysqld] 下面添加如下内容，如设置最大连接数为 1024

max_connections = 1024

重启 mysql 服务

2. back_log MySQL 能暂存的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求，它就

会起作用。如果 MySQL 的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一

连接释放资源，该堆栈的数量即 back_log ，如果等待连接的数量超过 back_log ，将不被授予连接资源。

back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如

果期望在一个短时间内有很多连接，你需要增加它。 当观察你主机进程列表（ mysql> show full

processlist ），发现大量 xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login |

NULL 的待连接进程时，就要加大 back_log 的值了或加大 max_connections 的值。 通过

 如何设置back_log? 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数1024

 

3. wait_timeout 和 interactive_timeout wait_timeout -- 指的是 MySQL 在关闭一个非交互的连接之前所要等待 的秒数 interactive_time -- 指的是 mysql 在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上 进入mysql 管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了 interactive_time 设置的时

间就会自动断开。默认数值是 28800 ，可调优为 7200 。 对性能的影响： wait_timeout ： （ 1 ）如果设置 大小，那么连接关闭的很快，从而使一些持久的连接不起作用 （2 ）如果设置太大，容易造成连接打开 时间过长，在show processlist 时，能看到太多的 sleep 状态的连接，从而造成 too many connections 错误

（ 3 ）一般希望 wait_timeout 尽可能地低 interactive_timeout 的设置将要对你的 web application 没有多大 的影响 查看wait_timeout 和 interactive_timeout

 如何设置wait_timeout和interactive_timeout? 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

 2）缓冲区变量

全局缓冲：

4. key_buffer_size key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。

通过检查状态值 Key_read_requests 和 Key_reads ，可以知道 key_buffer_size 设置是否合理。比例 key_reads

/ key_read_requests 应该尽可能的低，至少是 1:100 ， 1:1000 更好（上述状态值可以使用 SHOW STATUS LIKE ‘key_read%’获得）。

一共有 6 个索引读取请求，有 3 个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%  key_buffer_size 只对 MyISAM 表起作用。 即使你不使用MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。可以使用检查状态值

created_tmp_disk_tables 得知详情。

如何调整 key_buffer_size 默认配置数值是 8388608(8M) ，主机有 4GB 内存，可以调优值为 268435456(256MB) 修 改/etc/my.cnf 文件，在 [mysqld] 下面添加如下内容

 

5. query_cache_size( 查询缓存简称 QC) 使用查询缓冲， MySQL 将查询结果存放在缓冲区中，今后对于同样

的 SELECT 语句（区分大小写），将直接从缓冲区中读取结果。 一个 SQL 查询如果以 select 开头，那么 MySQL服务器将尝试对其使用查询缓存。

注：两个 SQL 语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个 SQL 将使用不同的一个CACHE

通过检查状态值 ’Qcache%’ ，可以知道 query_cache_size 设置是否合理（上述状态值可以使用 SHOW STATUS LIKE ‘Qcache%’获得）。

Qcache_free_blocks ：缓存中相邻内存块的个数。如果该值显示较大，则说明 Query Cache 中的内存碎片较多 了，FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。  注：当一个表被更新之后，和 它相关的cache blocks 将被 free 。但是这个 block 依然可能存在队列中，除非是在队列的尾部。可以用 FLUSH QUERY CACHE语句来清空 free blocks Qcache_free_memory ： Query Cache 中目前剩余的内存大小。通

过这个参数我们可以较为准确的观察出当前系统中的 Query Cache 内存大小是否足够，是需要加还是过多 了。 Qcache_hits ：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大， 缓存效果越理想。 Qcache_inserts ：表示多少次未命中然后插入，意思是新来的 SQL 请求在缓存中未找到，不 得不执行查询处理，执行查询处理后把结果insert 到查询缓存中。这样的情况的次数越多，表示查询缓存应用到 的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。 Qcache_lowmem_prunes ：多 少条Query 因为内存不足而被清除出 Query Cache 。通过 “Qcache_lowmem_prunes” 和 “Qcache_free_memory” 相 互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内

存不足而有 Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重， 或者内存很少。（上面的free_blocks 和 free_memory 可以告诉您属于哪种情况）   Qcache_not_cached ：不适合 进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了 now() 之类的函数。 Qcache_queries_in_cache：当前 Query Cache 中 cache 的 Query 数量；   Qcache_total_blocks ：当前 Query Cache 中的 block 数量；。  我们再查询一下服务器关于

query_cache的配置：

 上图可以看出query_cache_type为off表示不缓存任何查询 各字段的解释： query_cache_limit：超过此大小的查 询将不缓存  query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄”双刃 剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪 费。 query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024 的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。) query_cache_type：缓存类型，决 定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

query_cache_type三个参数的含义:
query_cache_type=0（OFF）关闭
query_cache_type=1（ON）缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存
query_cache_type=2(DEMAND)，只缓存select语句中通过SQL_CACHE指定需要缓存的查询

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。 如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。 如果设置为2，则只缓存在select语句中通过
SQL_CACHE指定需要缓存的查询。 query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。  修改/etc/my.cnf,
配置完后的部分文件如下： query_cache_size=256M query_cache_type=1 保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%  如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的
话。  查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%  查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且
Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。 查询缓存命中率 =Qcache_hits/(Qcache_hits +Qcache_inserts) * 100%
Query Cache 的限制 a) 所有子查询中的外部查询SQL 不能被Cache； b) 在Procedure，Function 以及Trigger 中 的Query 不能被Cache； c) 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。 鉴于上面
的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query的查询结果被Cache。
如何设置query_cache_size？ 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

 

6. max_connect_errors 是一个 MySQL 中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止 暴力破解密码的情况, 当超过指定次数， MYSQL 服务器将禁止 host 的连接请求，直到 mysql 服务器重启或 通过flush hosts 命令清空此 host 的相关信息。 max_connect_errors 的值与性能并无太大关系。 修 改/etc/my.cnf 文件，在 [mysqld] 下面添加如下内容 max_connect_errors=20 重启 MySQL Server 进入后， 查看设置已经生效。

7. sort_buffer_size 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速 ORDER BY 或 GROUP BY操作。 Sort_Buffer_Size  是一个 connection 级参数，在每个 connection （ session ）第一次需要使用这个 buffer的时候，一次性分配设置的内存。 Sort_Buffer_Size  并不是越大越好，由于是 connection 级的参 数，过大的设置+ 高并发可能会耗尽系统内存资源。例如： 500 个连接将会消 耗 500*sort_buffer_size(2M)=1G 内存 例如设置 sort_buffer_size 修改 /etc/my.cnf 文件，在 [mysqld] 下面添 加如下内容 sort_buffer_size = 2M 重启 MySQL Server 进入后，查看设置已经生效。

8. max_allowed_packet = 32M MySQL 根据配置文件会限制 Server 接受的数据包大小。有时候大的插入和更 新会受 max_allowed_packet 参数限制，导致写入或者更新失败。最大值是 1GB ，必须设置 1024 的倍数。

9. join_buffer_size = 2M    用于表间关联缓存的大小，和 sort_buffer_size 一样，该参数对应的分配内存也是 每个连接独享

10. thread_cache_size = 300    服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量 , 当断开 连接时, 那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁 ( 前提是缓存数未达上限 ), 如果线 程重新被请求，那么请求将从缓存中读取, 如果缓存中是空的或者是新的请求，那么这个线程将被重新创 建, 如果有很多新的线程，增加这个值可以改善系统性能 . 通过比较  Connections  和  Threads_created  状态 的变量，可以看到这个变量的作用。设置规则如下：1GB  内存配置为 8 ， 2GB 配置为 16 ， 3GB 配置为 32 ， 4GB或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁 ( 前 提是缓存数未达上限)

试图连接到 MySQL( 不管是否连接成功 ) 的连接数

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。 Threads_connected : 代表当前已建立连接的 数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。 Threads_created : 代表从最近一 次服务启动，已创建线程的数量，如果发现Threads_created 值过大的话，表明 MySQL 服务器一直在创建线程， 这也是比较耗资源，可以适当增加配置文件中thread_cache_size 值。 Threads_running : 代表当前激活的（非睡 眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep 状态。

3 ）配置 InnoDB 的几个变量

11. innodb_buffer_pool_size 对于 InnoDB 表来说， innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对 于MyISAM 表的作用一样。 InnoDB 使用该参数指定大小的内存来缓冲数据和索引。对于单独的 MySQL 数 据库服务器，最大可以把该值设置成物理内存的80% 。根据 MySQL 手册，对于 2G 内存的机器，推荐值是 1G（ 50% ）。 如果你的数据量不大，并且不会暴增，那么无需把  innodb_buffer_pool_size  设置的太大 了。 mysql> show variables like 'innodb_buffer_pool_size'; 设置 innodb_buffer_pool_size 修 改 /etc/my.cnf 文件，在 [mysqld] 下面添加如下内容 innodb_buffer_pool_size = 2048M 重启 MySQL Server 进入后，查看设置已经生效

 

12. innodb_flush_log_at_trx_commit 主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时 间点，取值分别为0 、 1 、 2 三个。 0 ，表示当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中 的数据写入日志文件并flush 磁盘一次； 1 ，则在每秒钟或是每次事物的提交都会引起日志文件写入、 flush磁盘的操作，确保了事务的 ACID ；设置为 2 ，每次事务提交引起写入日志文件的动作，但每秒钟完 成一次flush 磁盘操作。 实际测试发现，该值对插入数据的速度影响非常大，设置为 2 时插入 10000 条记录 只需要2 秒，设置为 0 时只需要 1 秒，而设置为 1 时则需要 229 秒。因此， MySQL 手册也建议尽量将插入操 作合并成一个事务，这样可以大幅提高速度。 根据MySQL 手册，在允许丢失最近部分事务的危险的前提 下，可以把该值设为0 或 2 。

13. innodb_thread_concurrency = 0    此参数用来设置 innodb 线程的并发数量，默认值为 0 表示不限制，若 要设置则与服务器的CPU 核数相同或是 cpu 的核数的 2 倍，建议用默认设置，一般为 8.

 14. innodb_log_buffer_size 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能， 对于较大的事务，可以增大缓存大小。 innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M 此参数确定数据日志文件的大小，以M 为单位，更大的设置可以提高性能 .

16. innodb_log_files_in_group = 3 为提高性能，MySQL 可以以循环方式将日志文件写到多个文件。推荐设置为 3

17. read_buffer_size = 1M   MySql 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区， MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太 慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size 一样，该参数对应的分 配内存也是每个连接独享。

18. read_rnd_buffer_size = 16M    MySql 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时 ( 例如， 按照排序顺序) ，将分配一个随机读缓存区。进行排序查询时， MySql 会首先扫描一遍该缓冲，以避免磁 盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql 会为每个客户连接发放该 缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 注：顺序读是指根据索引的叶节点数据就 能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而 辅助索引和主键所在的数据段不同，因此访问方式是随机的。

19. bulk_insert_buffer_size = 64M    批量插入数据缓存大小，可以有效提高插入效率，默认为 8M

20. binary log log-bin=/usr/local/mysql/data/mysql-bin binlog_cache_size = 2M // 为每个 session 分配的内 存，在事务过程中用来存储二进制日志的缓存, 提高记录 bin-log 的效率。没有什么大事务， dml 也不是很 频繁的情况下可以设置小一点，如果事务大而且多，dml 操作也频繁，则可以适当的调大一点。前者建议 是--1M ，后者建议是：即 2--4M max_binlog_cache_size = 8M // 表示的是 binlog 能够使用的最大 cache 内存大小 max_binlog_size = 512M // 指定 binlog 日志文件的大小，如果当前的日志大小达到 max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于 1GB 或小于 4096 字节。默 认值是1GB 。在导入大容量的 sql 文件时，建议关闭 sql_log_bin ，否则硬盘扛不住，而且建议定期做删 除。 expire_logs_days = 7 // 定义了 mysql 清除过期日志的时间。 二进制日志自动删除的天数。默认值为 0,表示 “ 没有自动删除 ” 。 mysqladmin flush-logs 也可以重新开始新的 binary log

 21. log_queries_not_using_indexes 开启这个选项真实地记录了返回所有行的查询。

在优化之前执行mysqlslap工具进行测试

[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --
iterations=1 --create-schema='test1' --query='select * from test1.tb1' --
engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose
显示结果：
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 13.837 seconds
Minimum number of seconds to run all queries: 13.837 seconds
Maximum number of seconds to run all queries: 13.837 seconds
Number of clients running queries: 10
Average number of queries per client: 200
优化之后执行mysqlslap工具进行测试
[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --
iterations=1 --create-schema='test1' --query='select * from test1.tb1' --
engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose
显示结果：
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 4.199 seconds
Minimum number of seconds to run all queries: 4.199 seconds
Maximum number of seconds to run all queries: 4.199 seconds
Number of clients running queries: 10
Average number of queries per client: 200

相关优化参数总结：
[mysqld]
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow-query.log
long_query_time = 1
log-queries-not-using-indexes
max_connections = 1024
back_log = 128
wait_timeout = 60
interactive_timeout = 7200
key_buffer_size=256M
query_cache_size = 256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size = 2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size = 2048M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
log-error = /usr/local/mysql/data/mysqld.err