登录  /  注册

mysql之调优概论

藏色散人
发布: 2019-05-16 09:17:41
转载
1901人浏览过

 一  简介

咱们先不说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表

全部用户都可收集。

2Setup_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之调优概论的详细内容,更多请关注php中文网其它相关文章!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:cnblogs网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号