叁 、mysql中修改my.ini配置文件记录日志,慢SQL的种类表现

图片 1

SQL质量优化周边方法

方今,在运行部及DBA同事的相助和豪门的共同努力下,对项目中的慢SQL举办了优化和考订,效果仍旧很显眼的,在此给大家点3个大大的赞。为了让大家在SQL的处理上越来越客观,形成可举行、可借鉴、可参看优化的方案,作者在此处梳理一下慢SQL的缓解思路,供我们参考。

  目 录

慢SQL的系统表现

① 、mysql中explain命令使用

首先,我们什么样分辨系统中相见了SQL慢查询难点?个人认为慢SQL有如下三个特点:

2、mysql中mysqldumpslow的使用

1,数据库CPU负载高。貌似是查询语句中有为数不少乘除逻辑,导致数据库cpu负载。

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

2,IO负载高以致服务器卡住。其一貌似和全表查询没索引有关系。

④ 、mysql中哪些加索引

3,查询语句平时,索引符合规律可是照旧慢。如若外部上索引符合规律,不过查询慢,必要探视是或不是索引没有立竿见影。

五 、须求分析中考虑程序品质及配置事务

敞开SQL慢查询的日记

⑥ 、化解行思索的常用命令

若果您的序列现身了上述情状,并且你不是用的Ali云的智跑DS那样的产品,那么下一步就需要打开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, 后面可以写2个正则匹配格局,大小写不灵活的;

=和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) =
’二〇一五-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事务占用改行的锁,后3个事情也一向试图去占用该行的锁,后二个工作一贯去占用,等到好久依旧不曾得到这一个锁的话,就会冒出这么些尤其,现身了那种死锁的动静。,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.