座谈SQL慢查询的解决思路。mysql开启慢查询(EXPLAIN SQL语句以介绍)

betway体育 1

今日,数据库的操作更为成为整个应用之性瓶颈了,这点对Web应用越来越明确。关于数据库的特性,这并无只是是DBA才用担心之转业,而这再是我们程序员需要去关注之事情。当我们错过规划数据库表结构,对操作数据库时(尤其是查表时之SQL语句),我们还亟需小心数据操作的习性。

近期,在运维部及DBA同事的援助与豪门之共同努力下,对项目中之慢SQL进行了优化及更正,效果或坏引人注目的,在这个于大家点一个大妈的夸赞。为了给咱们在SQL的拍卖达成进一步客观,形成可实施、可借鉴、可参考优化的方案,我在这里梳理一下慢SQL的化解思路,供大家参考。

1、开启慢查询

慢SQL的系统表现

1> 查看慢查询是否打开

首先,我们怎样辨别系统中碰到了SQL慢查询问题?个人觉得慢SQL有如下三单特色:

show variables like "%quer%";
slow_query_log = ON #已开启

1,数据库CPU负载高。貌似是查询语句被发出那么些测算逻辑,导致数据库cpu负载。

2> 开启方法:my.cnf目录配置

2,IO负载高以致服务器卡住。本条貌似与全表查询没索引发生涉嫌。

slow_query_log=on #是否开启
slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log #慢查询文件位置
long_query_time=2 #查询超过多少秒才记录

3,查询语句正常,索引正常不过还是舒缓。要外部上索引正常,但是查询慢,需要看是不是索引没有奏效。

2、EXPLAIN慢查询日志里冒出的SELECT查询

启SQL慢查询的日记

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user NULL ref user user 768 const 1 100.00 NULL

如果你的系出现了上述情况,并且你切莫是用的阿里云的RDS这样的出品,那么下一致步就是用开辟Mysql的款查询日志来一发定位问题。MySQL
提供了款查询日志,这个日志会记录有执行时间跨
long_query_time(默认是10s)的 SQL 及有关的音讯。

explain列的解释

如打开日志,需要在 MySQL 的配置文件 my.cnf 的 [mysqld]
项下安排慢查询日志被,如下所示:

  • table:显示这等同实施的数目是有关哪张表的

  • type:这是最主要之排列,显示连续使用了何种类型。从最好到绝差之连接路为const、eq_reg、ref、range、index、all

  • possible_keys:显示可能采用在就张表中的目录。如果为空,没有可能的目录。可以啊有关的地域于where语词被甄选一个宜的说话

  • key:
    实际应用的目。如果为null,则无行使索引。很少之景下,mysql会选优化不足之目。这种情景下,可以在select语句被应用use
    index(indexname)来强制行使一个目录或者用ignore
    index(indexname)来强制mysql忽略索引

  • key_len:使用的目的长短。在无损失精确性的图景下,长度逾短越好

  • ref:显示索引的啊一样排列于采用了,如果可能的话,是一个时时反复

  • rows:mysql认为必须检查的故来回到请求数据的行数

  • extra:关于mysql如何剖析查询的附加信息。例子:using temporary和using
    filesort,意思mysql根本未能够应用索引,结果是寻找会格外缓慢

[mysqld]slow_query_log=1

key_len的计算

slow_query_log_file=/var/log/mysql/log-slow-queries.log

  1. 持有的索引字段,如果没有设置not null,则需要加以一个字节。

  2. 定长字段,int占四独字节、date占三个字节、char(n)占n个字符。

  3. 对变成字段varchar(n),则有n个字符+两个字节。

  4. 不等之字符集,一个字符占用的字节数不同。latin1编码的,一个字符占用一个字节,gbk编码的,一个字符占用少独字节,utf8编码的,一个字符占用三个字节。

long_query_time=2

3、建索引的几百般标准

于实质上项目面临,由于变化的悠悠查询的日记可能会见专程好,分析起来不是坏

  • 最荒唐前方缀匹配原则,非常重大的准绳,mysql会一直于右侧匹配直到遇到范围查询(>、<、between、like)就歇匹配,比如a
    = 1 and b = 2 and c > 3 and d = 4
    如果建立(a,b,c,d)顺序的目录,d是用无交目的,如果建立(a,b,d,c)的目则还足以为此到,a,b,d的逐条可以随便调整。

  • =和in可以乱序,比如a = 1 and b = 2 and c = 3
    建立(a,b,c)索引可以肆意顺序,mysql的询问优化器会帮您优化成索引好辨别的样式。

  • 尽心尽力选区分度高之列作为索引,区分度的公式是count(distinct
    column)/count(*),表示字段非重复的比例,比例更是怪我们扫描的记录数更是少,唯一键的区分度是1,而有态、性别字段可能以那个数量面前区分度就是0,那可能有人会问,这个比重起啊更值为?使用状况不同,这个价值吗生不便确定,一般要join的字段我们且求是0.1上述,即平均1长扫描10漫长记下。

  • 索引列不可知与计算和函数的行使,保持列干净。

  • 尽可能的扩充索引,不要新建索引。比如表中已经有a的目,现在一经加以(a,b)的目,那么单纯待修改原来的目录即可。

利,所以Mysql官方也供了mysqldumpslow以此家伙,方便我们解析慢查询日志,感兴趣之同学可以活动到Mysql官方进行查看。

汝可能感兴趣的篇章:

  • MySQL查询优化之explain的深切剖析
  • mysql中explain用法详解
  • mysql总结之explain
  • MySQL性能分析及explain的应用说明
  • MySQL
    开启慢查询日志的办法
  • Mysql慢查询操作梳理总结
  • 详解MySqlbetway体育的款款查询分析和开慢查询日志
  • 详解mysql数据库如何打开慢查询日志
  • MySQL慢查询的pt-query-digest分析慢查询日志
  • MySQL慢查询的起启慢查询

SQL调优

多少SQL虽然出现于减缓查询日志被,但不至于是那个自的性质问题,可能是以锁等待,服务器压力高等等。需要分析SQL语句实在的实行计划,而不是圈又履行同一不折不扣SQL时,花费了不怎么日子,由自带的放缓查询日志或者开源之慢查询系统稳定到现实的有问题之SQL,然后使用Explain工具来逐渐调优,了解
MySQL
在实践这漫长数时之有的细节,比如是否进行了优化、是否以了目录等等。基于
Explain 的回来结果我们尽管可以因 MySQL
的实践细节越分析是否该优化搜索、怎样优化索引。

有关索引的开创与优化原则,个人特别推荐美团点评技术团队的几碰总结,讲得特别好,特地引用一下:

最荒唐前缀匹配原则,非常重大的口径,mysql会一直向右侧匹配直到遇到范围查询(>、<、between、like)就停下匹配,比如a
= 1 and b = 2 and c > 3 and d = 4
如果起(a,b,c,d)顺序的目录,d是用非交目的,如果起(a,b,d,c)的目则都得以为此到,a,b,d的相继可以任意调整;

=和in可以乱序,比如a = 1 and b = 2 and c = 3
建立(a,b,c)索引可以擅自顺序,mysql的查询优化器会帮您优化成索引好辨别的形式;

尽心尽力选区分度高之列作为索引,区分度的公式是count(distinct
col)/count(*),表示字段非重复的百分比,比例更是怪我们扫描的记录数更是少,唯一键的区分度是1,而部分态、性别字段可能以特别数量面前区分度就是0,那可能有人会问,这个比重起啊更值为?使用状况不同,这个价吗生不便确定,一般要join的字段我们且求是0.1以上,即平均1长长的扫描10长长的记下;

索引列不能够与计算,保持列“干净”,比如from_unixtime(create_time) =
’2014-05-29’就不能够采用及目,原因颇简短,b+树中存的且是数据表中的许段值,但进展查找时,需要拿所有因素还采用函数才会比,显然成本不过好。所以谈应该写成create_time
= unix_timestamp(’2014-05-29’);

尽心尽力的扩大索引,不要新建索引。比如表中已经有a的目录,现在只要加(a,b)的目录,那么单纯待改原来的目录即可。

一些总

冲本文的思绪,关于SQL慢查询的解决得依照以下的手续执行:

1.
开辟徐日志查询,确定是否有SQL语句占用了了多资源,如果是,在非改变工作原意的前提下,对insert、group
by、order by、join等报告句进行优化。

  1. 考虑调整MySQL的体系参数:
    innodb_buffer_pool_size、innodb_log_file_size、table_cache等。

  2. 确定是否是坐高并发引起行锁的超时问题。

4.
假设数据量过特别,需要考虑进一步的分库分表,可以参见之前的文章1和文章2。

举目四望二维码或手动搜索微信公众号【架构栈】: ForestNotes

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website