Mysql性能优化

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 从连接池获取连接超时