SQL性能优化常见措施(Lock wait timeout exceeded)

时间:2021-12-04 03:02:14

SQL性能优化常见措施

目 录

1、mysql中explain命令使用

2、mysql中mysqldumpslow的使用

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

4、mysql中如何加索引

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

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

一、mysql中explain命令使用

  使用explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。MySQL的EXPLAIN语法常运行在SELECT语句上。

EXPLAIN SELECT * FROM assets_check_outer_order_res WHERE id = ''

该语句为sql生成一个执行计划Query Execution Plan(QEP)。explain用于解释sql的执行计划,后边的sql不执行。在查询得到的结果中,possible_keys表示应用在这张表中的索引;

EXPLAIN SELECT * FROM assets_check_outer_order_res GROUP BY id

当前没有加任何索引,如果数据量大的话,查询时间明显会很长

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

使用上述语句,添加了索引之后,查询速度明显变快很多。同时可以通过rows显示的行数,可以看到查询得到了很大成都上的优化。数据库具体是如何使用索引来执行的,有待进一步研究。

二、mysql中mysqldumpslow的使用

  如果不知道是哪个sql慢,就开启mysql的慢查询日志。对记录的日志文件用mysql安装目录下的bin目录下的 mysqldumpslow查看。具体命令是 mysqldumpslow -s c -t 10 /path/to/slow.log。 可以提取出top10慢的sql语句模式。这样就找到了哪些语句慢。

  • -s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
  • -t, 是top n的意思,即为返回前面多少条的数据;
  • -g, 后边可以写一个正则匹配模式,大小写不敏感的;

  使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句,对MySQL查询语句的监控、分析、优化是MySQL优化的第一步,也是非常重要的一步。

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

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

  log-slow-queries = F:\MySQL\log\mysqlslowquery.log为慢查询日志存放的位置,一般这个目录要有MySQL的运行帐号的可写权限,一般都将这个目录设置为MySQL的数据存放目录;long_query_time=2中的2表示查询超过两秒才记录.

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

四、mysql中如何加索引

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

五、需求分析中考虑程序性能及配置事务

  @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 = '' WHERE id = ''
开启了了一个JDBC事务

附:参考博客

1. http://my.oschina.net/quanzhong/blog/222091 详细解释了innodb_trx innodb_locks innodb_lock_waits 三个表各个字段的含义。

2.