3、mysql中修改my.ini配置文件记录日志,慢SQL的连串表现

图片 1

SQL性能优化周边方法

最近,在运维部及DBA同事的接济和豪门的共同努力下,对品种中的慢SQL进行了优化和更正,效果仍旧很明显的,在此给大家点一个大大的赞。为了让大家在SQL的拍卖上越发合理,形成可实施、可借鉴、可参考优化的方案,我在此地梳理一下慢SQL的化解思路,供大家参考。

  目 录

慢SQL的系统表现

1、mysql中explain命令使用

第一,我们怎样鉴别系统中蒙受了SQL慢查询问题?个人认为慢SQL有如下多少个特性:

2、mysql中mysqldumpslow的使用

1,数据库CPU负载高。诚如是查询语句中有众多划算逻辑,导致数据库cpu负载。

3、mysql中修改my.ini配置文件记录日志

2,IO负载高导致服务器卡住。以此一般和全表查询没索引有涉嫌。

4、mysql中怎么样加索引

3,查询语句正常,索引正常不过照旧慢。倘使外部上索引正常,不过查询慢,要求探视是否索引没有立见成效。

5、须要分析中考虑程序性能及配置事务

敞开SQL慢查询的日志

6、解决行思索的常用命令

万一你的系统出现了上述情状,并且你不是用的阿里云的RDS这样的制品,那么下一步就要求开辟Mysql的慢查询日志来一发定位问题。MySQL
提供了慢查询日志,那个日志会记录所有执行时间当先long_query_time(默许是10s)的 SQL 及相关的消息。

一、mysql中explain命令使用

要开启日志,须求在 MySQL 的安顿文件 my.cnf 的 [mysqld]
项下安插慢查询日志开启,如下所示:

  使用explain呈现的新闻可以协助接纳更好的目录和写出更优化的查询语句。MySQL的EXPLAIN语法常运行在SELECT语句上。

[mysqld]slow_query_log=1

EXPLAIN SELECT * FROM assets_check_outer_order_res WHERE id = '1468289'

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

该语句为sql生成一个实施安排Query
Execution Plan(QEP)。explain用于解释sql的施行安插,后面的sql不举行。在查询得到的结果中,possible_keys表示应用在这张表中的目录;

long_query_time=2

EXPLAIN SELECT * FROM assets_check_outer_order_res GROUP BY id

在其实项目中,由于变化的慢查询的日记可能会特意大,分析起来不是很

此时此刻尚无加任何索引,假设数据量大的话,查询时间肯定会很长

惠及,所以Mysql官方也提供了mysqldumpslow那一个工具,方便我们解析慢查询日志,感兴趣的同窗可以自动到Mysql官方举办查看。

EXPLAIN SELECT * FROM assets_check_outer_order_res USE INDEX (id) GROUP BY id

SQL调优

动用上述讲话,添加了目录之后,查询速度显著变快很多。同时可以通过rows突显的行数,可以看出查询得到了很大圣路易斯上的优化。数据库具体是什么使用索引来执行的,有待进一步研商。

些微SQL尽管出现在慢查询日志中,但未必是其本人的性能问题,可能是因为锁等待,服务器压力高等等。必要分析SQL语句实在的推行安排,而不是器重新履行一回SQL时,费用了多少时间,由自带的慢查询日志或者开源的慢查询系统定点到实际的出题目标SQL,然后使用Explain工具来逐步调优,精通MySQL
在推行这条数据时的片段细节,比如是不是举行了优化、是还是不是利用了目录等等。基于
Explain 的回来结果我们就可以根据 MySQL
的执行细节尤其分析是或不是应该优化搜索、怎么着优化索引。

二、mysql中mysqldumpslow的使用

至于索引的创制及优化原则,个人更加推荐美团点评技术公司的几点总计,讲得专程好,特地引用一下:

  若是不领会是哪位sql慢,就开启mysql的慢查询日志。对记录的日志文件用mysql安装目录下的bin目录下的
mysqldumpslow查看。具体命令是 mysqldumpslow -s c -t 10
/path/to/slow.log。
可以提取出top10慢的sql语句情势。那样就找到了怎么样语句慢。

最左前缀匹配原则,极度首要的标准,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的依次可以擅自调整;

  • -s,
    是表示根据何种格局排序,c、t、l、r分别是依据记录次数、时间、查询时间、再次来到的记录数来排序,ac、at、al、ar,表示相应的倒叙;
  • -t, 是top n的意味,即为再次来到前边多少条的数码;
  • -g, 前边可以写一个正则匹配情势,大小写不灵敏的;

=和in可以乱序,比如a = 1 and b = 2 and c = 3
建立(a,b,c)索引可以随心所欲顺序,mysql的询问优化器会帮你优化成索引能够辨认的款型;

  使用mysqldumpslow命令可以非凡显眼的获取各类大家必要的查询语句,对MySQL查询语句的监察、分析、优化是MySQL优化的第一步,也是不行主要的一步。

尽可能挑选区分度高的列作为索引,区分度的公式是count(distinct
col)/count(*),表示字段不另行的百分比,比例越大大家扫描的记录数越少,唯一键的区分度是1,而有些情景、性别字段可能在大数额面前区分度就是0,那也许有人会问,这一个比例有哪些经验值吗?使用处境不一致,这么些值也很难确定,一般必要join的字段我们都务求是0.1上述,即平均1条扫描10条记下;

三、mysql中修改my.ini配置文件记录日志

索引列不可能插足总结,保持列“干净”,比如from_unixtime(create_time) =
’2014-05-29’就不可以使用到目录,原因很简单,b+树中存的都是多少表中的字段值,但进展检索时,须要把富有因素都利用函数才能相比较,鲜明用度太大。所以语句应该写成create_time
= unix_timestamp(’2014-05-29’);

  Windows下打开MySQL慢查询,MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下边加上
log-slow-queries =
F:\MySQL\log\mysqlslowquery.log和long_query_time =
2。

尽量的恢宏索引,不要新建索引。比如表中已经有a的目录,现在要加(a,b)的目录,那么只须要修改原来的目录即可。

  log-slow-queries =
F:\MySQL\log\mysqlslowquery.log为慢查询日志存放的地点,一般那一个目录要有MySQL的运作帐号的可写权限,一般都将以此目录设置为MySQL的数目存放目录;long_query_time=2中的2象征查询超越两秒才记录.

一些统计

 

依照本文的思绪,关于SQL慢查询的化解能够听从以下的手续执行:

log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2
log-queries-not-using-indexes
添加log-queries-not-using-indexes参数,表示记录下没有使用索引的查询

1.
打开慢日志查询,确定是不是有SQL语句占用了过多资源,如果是,在不改动工作原意的前提下,对insert、group
by、order by、join等语句进行优化。

 

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

  2. 确定是或不是是因为高并发引起行锁的过期问题。

四、mysql中什么加索引

4.
比方数据量过大,要求考虑进一步的分库分表,可以瞻仰从前的文章1文章2

  通过SQL来添加索引,或者Navicat视图中添加索引。

围观二维码或手动搜索微信公众号【架构栈】: ForestNotes

五、需要分析中考虑程序性能及安排事务

  @Transcational(progration=Progration.supports
rollback=Exception.class)

六、解决行思索的常用命令

  假如在某一个工作逻辑中,要求创新五个表,涉及到数次与数据库中表的修改交互操作。那么,很可能在操作表中同一行数据的时候出现lock
wait timeout
exceeded格外,那一个这几个发生的原委是前一个JDBC事务占用改行的锁,后一个政工也直接打算去占用该行的锁,后一个作业一贯去占用,等到好久依旧尚未得到这些锁的话,就会见世这几个那些,现身了那种死锁的图景。,Mysql的
InnoDB存储引擎是支撑工作的,事务开启后并未被主动Commit。导致该资源被长时间占有,其余工作在抢占该资源时,因上一个作业的锁而招致抢占失利!由此出现锁等待超时。

  当在本机安装好mysql之后,会发现地面默许的有一个information_schema数据库,其中保存着有关MySQL服务器所有限支持的拥有其余数据库的新闻。如数据库名,数据库的表,表栏的数据类型与走访权限等。它们其实是视图,而不是基本表。

 

快捷键:选中当前行:shift+Home 或 Shift + End
执行当前行:ctrl + shift + R

SHOW PROCESSLIST
SHOW FULL PROCESSLIST
SELECT * FROM information_schema.`PROCESSLIST`
当死锁发生时,用于显示当前跟数据连接的所有线程

kill 7658932 
kill 线程ID:7658932,即可以杀死死锁的线程

SHOW CREATE TABLE assets_check_temp
显示assets_check_temp建表的SQL语句,同DDL效果

SHOW TABLE STATUS LIKE 'assets_check_%'
SHOW ENGINE INNODB STATUS 
显示了指定表的结构,创建时间、表的总列数

SELECT * FROM information_schema.INNODB_TRX 
SELECT * FROM information_schema.INNODB_LOCKS 
SELECT * FROM information_schema.INNODB_LOCK_WAITS
分别表示:当前运行的所有事务、当前出现的锁、锁等待的对应关系;trx是事务transaction的缩写。当有JDBC事务时,第一个表有数据。当产生锁等待的时候,第二个表中有数据。可以用于排查错误。

START TRANSACTION;UPDATE assets_check_temp SET id = '1' WHERE id = '1468300' 
开启了了一个JDBC事务

 

附:参考博客

1. http://my.oschina.net/quanzhong/blog/222091 详细表达了innodb_trx
innodb_locks innodb_lock_waits 多少个表逐项字段的含义。

2.

 

 

 

 

相关文章