Mysql的调优

Mysql体系结构

  • 管理工具:安装服务软件后,提供的命令(mysqldump mysqladmin)

  • 连接池:当接受到客户端的连接请求后,检查是否又资源响应客户端的连接请求

  • SQL接口:把SQL传递给mysqld线程处理

  • 分析器:检查执行的SQL命令是否有语法错误,和是否有访问权限

  • 优化器:优化执行的SQL命令,已最节省资源的方式执行SQL命令

  • 查询缓存:缓存曾经查找到的记录,缓存空间从物理内存划分出来的

  • 存储引擎:是表的处理器,不用的存储引擎有不同的功能和数据存储方式(Myisam Innodb)

  • 文件系统:存储表中记录的磁盘

Mysql服务处理查询请求过程

  • 数据库服务器接收到查询请求后,先从查询缓存里查找记录,若查询缓存里有查找记录,直接从缓存提取数据给客户端

  • 如果缓存里没有,则到表中去查询记录,把查到的记录先存放到查询缓存里再返回给客户端

Mysql调优

  • 如何优化mysql数据库服务器(那些原因会导致数据库服务器处理客户端的连接请求慢)

  • 1.硬件配置低:导致处理速度慢(cpu,内存,存储磁盘)

    接口 转数 15000/s
    top –> 0.0 wa                          #百分比越大,排队队列越长,读写速度越慢
    
  • 2.网络带宽:网络测速软件

  • 3.提供服务软件的版本太低,导致性能低下

mysql-5.7 社区开源版

很多配置都是默认配置,都不是峰值配置,所以性能不完全体现
查看服务运行时的参数配置
mysql> show variables [like “%xxx%”];

常用常数的配置(会影响性能的参数)

并发连接数量
连接超时时间
可以重复使用的线程数量
所有线程同时打开表的数量
与查询相关参数的设置

查看服务运行时的参数配置:

mysql> show variables;
mysql> show variables like “%innodb%”;

常用参数:

  1. 并发连接数量

如何计算并发访问的?

mysql> show global status like "%conn%";
Max_used_connections/Max_connection=0.85
set global max_connections= 数字;     #设置并发连接数
  1. 连接超时时间

查看时间

show variables like "%timeout%";
        connect_timeout                 #客户端与服务器建立连接时TCP三次握手超时的时间
        innodb_lock_wait_timeout        #防止死锁的超时的时间
        wait_timeout                #客户端与服务器建立连接后,等待执行sql命令的超时时间

  1. 可以重复使用的线程的数量

可以重复使用的线程的数量 thread

mysql> show variables like "%thread%";          #查有有关线程的所有项
 thread_cache_size          #当前缓存的线程数量,等待响应的线程数量
 #所有线程打开的表
        Mysql --> disk --> x.ibd --> memory -->disk

  1. 所有线程同时打开表的数量

查询方式

mysql> show variables like "%open%";
table_open_cache            #可以同时打开表的数量

  1. 与查询相关参数的设置

select * from 表名 where name=””
key_buffer-size 用于My

  1. 与查询缓存相关的参数

查询:

mysql> show variables like "query_cache%";
参数 作用
query_cache_limit 查询缓存的最大大小
query_cache_min_res_unit 查询结果的最小存储单元
query_cache_type 当前查询缓存的类形
query_cache_wlock_invalidate 当前查询缓存写锁是否有效

当与myisam存储的引擎的表执行查询时候,若检查到有对表做写的sql操作,不从查询缓存里面查询数据返回给客户端,而是等写操作完成后,重新允许查询结果返回客户端。

  1. 查询当前缓存的统计信息

查询:

mysql> show global status like "qcache%";

以下参数可以看出当前设置的查询缓存是否给得足够多

参数 作用
Qcache_lowmem_prunes 查询缓存的刷新次数,清理查询缓存的空间次数
Qcache_hits 查询缓存的命中率
Qcache_inserts 记录在查询缓存里面查找数据的次数,查一次+1

  1. 程序员编写的sql查询语句太过复杂,导致数据库服务器处理速度慢
  • 开启数据库的慢查询日志,记录超过指定的时间显示结果的sql命令

mysql数据库服务日志类型

日志的类型 作用
错误日志 记录Mysql在启动运行中所产生的问题
binlog日志 二进制日志(增删改查)
慢查询日志 记录sql执行时间的日志
查询日志 记录所有的Mysql的操作日志

开启慢日志查询

slow-query-log      #启动慢查询日志
long-query-time=2       #
log-queries-not-using-indexes       #

查询你的慢日志

cat /var/lib/mysql/主机名-slow.log

统计日志文件记录信息

mysqldumpslow /var/lib/mysql/xxx-slow.log >/保存的位置

发表评论