MySQL性能调优

MySQL性能调优

调优思路总结

手段	具体操作
升级硬件	CPU、内存、硬盘
加大网络带宽	付费加大带宽
调整mysql服务运行参数	并发连接数、连接超时时间、重复使用的线程数…
调整与查询相关的参数	查询缓存、索引缓存…
启用慢查询日志	show-query-log
网络架构不合	调整网络架构

1. 升级硬件（CPU 内存 存储）运维部
2. 优化数据库服务运行参数

mysql> show variables;
mysql> show variables like "%timeout%";
mysql> show status;
mysql> show status like "%connnect%";

（1）并发及连接控制

• 连接数、连接超时

选项	含义
max_connections	允许的最大并发连接数
connect_timeout	等待连接超时，默认10s,仅登录时有效
wait_timeout	等待关闭连接的不活动超时秒数，默认28800秒（8小时）

设置并发连接数的公式

最大并发连/最大并发连接数 = 0.85 * 100% = 85%

查看当前已使用的连接数

mysql> show global status like "max_used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 5     |
+----------------------+-------+

查看默认的最大连接数

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+

（2）缓存参数控制

• 缓冲区、线程数量、开表数量

选项	含义
key_buffer_size	用于MyISAM引擎的关键索引缓存大小
sort_buffer_size	为每个要排序的线程分配此大小的缓存空间
read_buffer_size	为顺序读取表记录保留的缓存大小
thread_cache_size	允许保存在缓存中被重用的线程数量
table_open_cache	为所有的线程缓存的打开的表的数量

• key_buffer_size=8M
  当Key_reads/Key_read_quests 较低时，可适当的加大此缓存值
• sort_buffer_size=256k
  增大此值可以提高order和group的速度
• 查看表记录读取缓存
  此缓存值影响SQL查询的响应速度

mysql> show variables like "read_%_size";

• 查看可重用的线程数

mysql> show variables like "thread_%_size";

• 查看当前的线程重用状态

mysql> show global status like "thread_%";

• 查看已打开、打开过的表

mysql> show global status like "open%tables";

• 查看可缓存多少个打开的表

mysql> show variables like "table_open_cache";

已打开的表/可缓存的表个数 <= 95% //理想比率

（3）SQL查询优化
MySQL日志类型

• 常用的日志种类及选项

类型	用途	配置
错误日志	记录启动/运行/停止过程中的错误消息	log-error[=name]
查询日志	记录客户端连接和查询操作	general-log general-log-file=
慢查询日志	记录耗时较长或不使用索引的查询操作	slow-query-log slow-query-log-file= long-query-time=

优化SQL查询

• 记录慢查询

选项	含义
slow-query-log	启用慢查询
slow-query-log-file	指定慢查询日志文件
long-query-time	超时时间（默认10秒）
log-queries-not-using-indexes	记录未使用索引的查询

• 调整服务配置

[root@51 ~]# vim /etc/my.cnf
[mysqld]
.. ...
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=5
log_queries_not_using_indexes=1

[root@51 ~]# service mysql restart

• 查看慢查询日志
  使用mysqldumpslow工具

[root@51 ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log

关于查询的缓存

• 查看缓存的大小

mysql> show variables like "query_cache%";

查看当前的查询缓存统计

mysql> show global status like "qcache%";

• 让程序员去优化访问数据库的sql命令
  查看慢查询日志记录，让程序员去优化

• 网络带宽

• 优化服务架构（网络架构中是否有数据传输瓶颈）