答疑微信号：uhz2008

Mysql 性能优化
Mysql 的性能参数可以分为以下几个大类，这里仅整理一些常用的参数配置

连接参数

max_connections

mysql 服务器的最大连接数
show VARIABLES like '%connections%'
查看 mysql 服务器的最大连接数
Max_user_connections
单个用户最大连接数限制
set GLOBAL max_connections
设置 mysql 服务器的最大连接数
max_connect_errors
每个 IP 的连接请求异常中断的最大次数

Connections
试图连接到 mysql 服务器的连接总数
show STATUS like '%Connections%'
查看试图连接的总数
Max_used_connections
已经建立连接的总数

Aborted_connects
尝试重连的次数，结合 host_cache 分析，可能的原因有
1.连接到 MySQL 服务器的失败总数
2.客户端没有权限但是尝试访问 MySQL
3.客户端输入的密码不对
4.超过 connect_timeout 限制（mysql 默认是 10s，除非网络环境极端不好，一般不会超时）

Aborted_clients

客户端没有正常关闭连接而强行中止的连接数
有客户端成功建立连接，但是由于某些原因断开连接，一般是由于网络环境不稳定。主要的可能性有

1.客户端退出之前未调用 mysql_close（）正确关闭 MySQL 连接
2.sleep 时间超过了变量 wait_timeout 和 interactive_timeout 的值，导致连接被 MySQL 进程终止
3.客户端在数据传输过程中突然结束

select USER,COUNT(USER) AS CNT from information_schema.PROCESSLIST GROUP BY USER ORDER BY CNT DESC;
查询执行 sql 的连接总数

注：VARIABLES 是参数变量，可以优化；status 是状态位，只能查看不能修改

缓冲空间

sort_buffer_size
查询排序缓存，为每个 connection 一次性分配设置的内存。500 个连接将会消耗 500*sort_buffer_size(8M)=4G 内存

join buffer
表关联缓存空间。增加 join buffer 的值来优化 join 查询，因为增加的 join buffer 可以容纳下更多的外表 join 字段记录

query_cache_limit
MySQL 将 SELECT 语句和查询结果存放在缓冲区，后期对于同样的 SELECT 语句，将直接从缓冲区读取结果
使用show status like "%Qcache%"查看缓冲区。如果Qcache_lowmem_prunes的值非常大，表明缓冲区不够，如果Qcache_hits也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲

query_cache_size
MySQL 查询缓存保存查询返回的完整结果。查询命中该缓存，则立刻返回结果，跳过解析，优化和执行阶段。
如果表发生变更，要把Query_cache和该表相关的语句全部置为失效，然后再写入更新。如果Query_cache非常大，该表的查询结构又比较多，那么查询语句失效也会很慢，更新或是 Insert 就会更慢
数据库写入量或是更新量也比较大的系统，该参数不宜分配过大

query_cache_min_res_unit
指定分配缓冲区空间的最小单位，缺省为 4K。检查Qcache_free_blocks。如果该值非常大，则表明缓冲区中碎片很多，查询结果都比较小，此时需要减小尺寸。小数据查询，此值过大容易造成内存碎片和浪费

query_cache_type
设置为 0，OFF,缓存禁用
设置为 1，ON,缓存开启

innodb_buffer_pool_size
内存中开辟的缓存空间用于缓存索引和数据,。数据读写在内存中非常快, 它减少对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中

innodb_buffer_pool_instances
缓冲池总数
注意：mysql 的缓冲区空间尺寸只能在 my.cnf 里面修正

back_log
如果 MySql 的连接数达到 max_connections 时,新来的请求将会被存在堆栈中,以等待某一连接释放资源
这个值不能超过 TCP 的 net.ipv4.tcp_max_syn_backlog

socket 参数

Bytes_received
从客户端接收到的字节数，作为数据库网卡吞吐量的评测指标

Bytes_sent
发送给客户端的字节数，作为数据库网卡吞吐量的评测指标

max_allowed_packet
网络传输中单次接收数据包尺寸的上限值

线程参数

innodb_thread_concurrency
thread_stack
Threads_cached
缓存线程总数。空闲的连接线程放在连接池中,而不是立即销毁.当有一个新的请求的时候,mysql 不会立即去创建连接线程,而是先去 Thread_Cache 中去查找空闲的连接线程
Threads_connected【当前打开的连接数】
Threads_created【已创建的线程数】
Threads_running【已经激活的线程数】
thread_cache_size
当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程

文件参数

Created_tmp_files【创建的临时文件总数】
Open_files
Open_tables

日志参数

general_log = OFF 关闭通用查询日志
max_binlog_size = 50M
log_bin=ON
expire_logs_days = 10 清除过期日志

慢 sql

show variables like '%slow%';查询慢 sql 状态
show variables like "long%";慢查询时间
show global status like '%slow%'; 慢 sql 条数
set long_query_time=0.0001;【定义慢查询时间】
set global slow_query_log=ON;【开启慢查询日志】

Durid 连接池

spring.datasource.druid.initial-size=1 初始化连接
spring.datasource.druid.min-idle=1 最小空闲连接
spring.datasource.druid.max-active=30 最大活动连接
spring.datasource.druid.max-wait=60000 从连接池获取连接超时