mysql之调优概论的案例分析

共计 3009 个字符，预计需要花费 8 分钟才能阅读完成。

自动写代码机器人，免费开通

丸趣 TV 小编给大家分享一下 mysql 之调优概论的案例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

一   简介

咱们先不说 cpu 的频率，内存的大小（这个和索引一样重要，但不是本文讨论的内容），硬盘的寻道时间。想起 mysql 的调优，最起码的必须知道 explain 执行计划，慢 sql 日志，老旧的 profile 命令，新的 performance_schema 性能视图和 information_schema 中当前事务和内存占用信息的相关表，还有 show engine innodb status 的诊断信息，以及某些 metrix 中的 tps，qps,iops 的指标。（相关推荐：《MySQL 教程》）

以上是为调优准备的一些工具，而数据库都会为高可用提供很多大大小小的功能，大的有：复制，组复制，分区，文件链接：即 log 日志与数据文件等可分别放置不同硬盘。小的有：计算列，为列计算 hash，索引合并，索引下推，MRR，BKA,Loose Index 等算法，以及填充因子等。

当然，没有视图索引和分布式分区视图，以及 join 仅仅只支持 nested 这是 mysql 的不足，而 sql server join 的算法支持三种，loop while hash，极大的改善 join 的速度。mysql 自带提升性能的功能并不多，其他的就是经验之谈，比如静态表，不要在子查询中使用函数，尽量将子查询变为 join 查询，非字符串和 blob 列永远比其他的数字或者时间列要慢，join |order by|group 一定不要让其在硬盘生成临时表，当然这个和内存有关，窄表和宽表设计等，当然最后还是取决你的业务类型。

优化入手有两种方法，一种是运行时的，即在运行的服务器上优化，一种是开发过程中。而无论哪种，performance_schema 都会需要。

二 performance_schema 讲解

性能视图是每个数据库中都会有的，sql server 是 dm_* 开头的一系列内存表。而 mysql 就是 performance_schema 库中的各种表，先看入口的几个表：

SELECT * FROM setup_timers; --  计时定义表
select * from setup_actors; --  那些用户需要收集信息
select * from Setup_objects; --  那些对象需要收集信息，比如 mysql 表，select * from setup_consumers; --  那些仪器的分类需要收集
select * from setup_instruments; --  收集仪器，每一个功能点都会有仪器的事件，开始和结束，然后开启那个仪器，就会收集那个仪器的数据 

首先我们看开启 performance_schema 的开关：

show variables like  performance_schema  --  这是一个 read only 变量 

如果为 OFF，则需要在配置文件中开启。

那么下面就一个一个介绍这几个入口表。

1，setup_actors 表

全部用户都可收集。

2，Setup_objects

那些对象可以收集，是 table 还是 trigger 等。至于关闭两个列控制，enabled 和 timed 字段设置为 No，这几个表都是如此。

3  setup_consumers

事件的分类，stages 是步骤，一个语句在服务器执行的过程步骤，结果和 profile 一样，profile 方式不推荐，因为后面会去掉。transaction 是事务的事件收集等。

4 setup_instruments

这个就是主要的事件监控仪器，如下：

5 最后就是 setup_timers，配合 performance_timers 定义那些仪器分类是的时间类型，如下：

CYCLE:cpu 时钟，TIMER_FREQUENCY 是一秒有多少，TIMER_RESOLUTION 是每次增加多少，最后是多久获取一次这个时间。

三 利用 performance_schema 获取 priofile 数据

开启相关的 instrument:

我们看上面 instrument 分类表 setup_consumers 中的信息，关于 stage 的行都是 NO，那么我们需要改为 YES，同时一会需要拿 statements 监控表中的信息，所以也需要开启 statements：

UPDATE setup_consumers SET ENABLED =  YES 
 WHERE NAME LIKE  %stage% 
UPDATE setup_consumers SET ENABLED =  YES 
WHERE NAME LIKE  %statements%

然后把 stage 的 instrument 开启

UPDATE performance_schema.setup_instruments SET ENABLED =  YES , TIMED =  YES 
 WHERE NAME LIKE  %stage/%  --  开启所有执行步骤的监控
UPDATE performance_schema.setup_instruments SET ENABLED =  YES , TIMED =  YES 
WHERE NAME LIKE  %statement/%

执行依据 sql

 select * from quartz.TestOne

查询这条语句的 queryid：

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
 FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like  %quartz%

那么 id 就是 509

然后执行性能监控表：

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
 FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=509

内容和老版本的 profile 结果一样。

主要看下 stage/sql/Sending data 这一行，这一行是主要 io 相关的事件，一般情况下，sql 慢了，而这一行数值比较大，那肯定硬盘读数据慢了或者有锁冲突。

那么就是用 error log，有死锁，mysql 会将死锁信息打入 error 日志，show engine innodb status 只是全局的一些信息，如果要想看详细的再去监控对应的 instrument。

而且目前 mysql8 多支持 NOWAIT 和 skiplocked 两个语句，用法还是 select.. from 表明 for update/for nowait 等，非常灵活的解决了死锁的处理方式，当然你也可以让其事务隔离级别为脏读级别，但是并不能解决更多的业务类型，设置死锁超时也是一个可行的办法。

以上是“mysql 之调优概论的案例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

丸趣 TV 网 – 提供最优质的资源集合！