通过 show status 命令了解各种 SQL 的执行频率
show [session|global] status
定位执行效率较低的 SQL 语句
修改配置,会在 data 目录下生成 log-slow.txt 慢查询日志
[mysqld]
#记录sql执行超过2秒的SQL语句
long_query_time = 2
#指定记录的文件名
log-slow-queries=log-slow.txt
#记录没有使用索引的SQL查询
log-queries-not-using-indexes
-
通过 EXPLAIN 分析低效 SQL 的执行计划
- 可以看到是否使用了索引
- 扫描了表的行数
MySQL锁
Mysql锁类型
- MySQL有三种锁的级别:页级、表级、行级。
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
死锁产生的情况
- 表级锁不会产生死锁,所以解决死锁主要还是针对于最常用的 InnoDB。
- 死锁的关键在于:两个(或以上)的 Session 加锁的顺序不一致。
查看死锁的语句
show engine innode status
mysql解除正在死锁的状态
- 运行命令 show processlist ;
- 查看 Command 为 Sleep 的 Id,执行 kill id 即可。
如何避免死锁
- 按同一顺序访问对象。
- 如两个并发事务,第一个事务要先获取A表的锁,再获得B表的锁,那么其他事务都保持一致。
- 避免事务中的用户交互。
- 保持事务简短并在一个批处理中。
- 使用低隔离级别。
- 使用绑定连接。
死锁举例
在 for 循环中执行 Select * from xxx where id=‘随机id’ for update,那么下面的情况就会发生死锁:
Session1
select * from t3 where id=9 for update;
Session2
select * from t3 where id<20 for update;
Session1
insert into t3 values(7,'ae','a',now());
Session2 在等待 Session1 的 id=9 的锁,session2 又持了 1 到 8 的锁(注意 9 到 19 的范围并没有被 session2 锁住),最后,session1 在插入新行时又得等待 session2,故死锁发生了。
MySQL事务
Mysql事务隔离级别
设置执行语句:
set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
Read Uncommitted(读取未提交内容)
- 所有事务都可以看到其他未提交事务的执行结果。
- 本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。
- 该级别引发的问题是——脏读(Dirty Read):读取到了未提交的数据。
- 事务 A 第一次执行查询,查到了数据 A,这时候 A 事务还没结束,B 修改了数据,但并未提交事务,这时候 A 再次查询发现数据被修改了。
Read Committed(读取提交内容)
- 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的)。
- 它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。
- 这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)
- 不可重复读意味着我们在同一个事务中执行完全相同的 select 语句时可能看到不一样的结果
- 导致这种情况的原因可能有
- 有一个交叉的事务有新的 commit,导致了数据的改变;
- 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的 commit
- 事务 A 第一次执行查询,查到了数据 A,这时候 A 事务还没结束,B 修改了数据,并已提交事务,这时候 A 再次查询发现数据被修改了,此时 B 再次修改数据,那么 A 再次查询,数据再次被修改。
Repeatable Read(可重读)
- 这是 MySQL 的默认事务隔离级别。
- 它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。
- 此级别可能出现的问题——幻读(Phantom Read):当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的 “幻影” 行。
- InnoDB 和 Falcon 存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。
Serializable(可串行化)
- 这是最高的隔离级别。
- 它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
- 在这个级别,可能导致大量的超时现象和锁竞争。