实际中有时候虽然只查询一行或者几行记录,查询的速度也很慢
原因:
主要有以下三个原因
- 其他线程在执行操作,表被锁住了
- 其他事务占据了该行的写锁
- 执行的sql语句为一致性读,需要回滚较长的undo log日志(或者查询没走索引树)
处理方式:
1) 对于表锁,找到堵住的线程,将其kill;
可以通过指令show processlist,查看所有当前正在执行的线程
进一步的可以用指令select blocking_pid from sys.schema_table_lock_waits直接找到造成阻塞的线程id,再将其kill掉。
2) 对于行锁也是找到对应的占用写锁的线程,将其kill;
可以用指令sys.innodb_lock_waits查询当前占用写锁的线程
3) 对于sql语句本身来说;
SQL语句处理索引有一套步骤流程。此外,对于多事务并发情况下,事务中执行一致性读(快照保证事务中每次读到的数据是一致的)和当前读(实时读取表中当前的最小数据,相当于valitile)的查询速度不同,如下两句sql语句逻辑一样,查询时间可能差很多
mysql> select * from t where id=1;
mysql> select * from t where id=1 lock in share mode;
前者是一致性读,如果之前本事务已经开启了且生成了快照,那么该sql语句必须查询这个快照上的数据,如果期间其他事务对行数据进行了大量更新,那么就需要进行大量的undo log回滚,直到恢复到快照上的版本。
第二句sql语句加了in share mode表示为当前读,事务实时从表中读取数据,所以不需要回滚undo log。
(可以结合以下两张图进行理解)
由此可见,可重复读进行了大量的日志回滚操作,耗时很大。
参考文章:
https://time.geekbang.org/column/article/74687