MVCC、sql锁机制、sql优化

MVCC、sql锁机制、sql优化

目录

一、MVCC

1.概述2.基本原理

版本链基本特征 二、 mysql锁机制

myql中支持行锁，间隙锁，表锁

行锁：表锁：间隙锁：共享锁（S）:又称读锁排他锁（X）：又称写锁 三、sql优化

一、MVCC 1.概述

MVCC（多版本并发控制 Multi-Version Concurrent Control），为了提升mysql读-写，写-读； 两个 *** 作同时进行，写-写mysql支持行级锁的，如果 *** 作同一行数据，那么肯定是不可以的。一般在使用 读已提交（READ COMMITTED）和 可重复读（REPEATABLE READ）隔离级别的事务中实现。 2.基本原理 版本链

对于使用InnoDB存储引擎的表来说，聚簇索引记录中包含两个隐藏列。
trx_id：每次对某条聚簇索引记录进行改动时，会把对应的事务id赋值给trx_id隐藏列。
roll_pointer：

每次对表中的记录 *** 作时，会保存一个日志（undolog）里面记录事务id号，如果有多个事务 *** 作时，他们就会根据事务id，找到自己 *** 作的版本号。 基本特征

每行数据都存在一个版本号，每次数据更新时都更新该版本。

修改时Copy 出当前版本随意修改，各个事务之间无干扰。

保存时，比较版本号，如果成功（commit），失败则（rollback）。
eg：
插入记录的事务id为80；

之后两个事务id为100和200的事务进行update *** 作，

每次对记录进行改动，都会记录一条 Undo log

不同隔离级别在读取数据时，会根据版本链生成一个ReadView(临时读视图）版本链快照。

READ COMMITTED：每次读取数据前都生成一个 ReadView，产生不可重复读。其中数据发生改变，版本链中会发生修改，每次读的时候ReadView中的数据就发生改变，所有不可重复读。

REPEATABLE READ：在第一次读取数据时生成一个 ReadView，之后数据发生改变，版本链发生变化，没有关系。第一次读的时候已经拍过照了。

二、 mysql锁机制

mysql中的锁，主要是用于对写写 *** 作。锁定数据较多情况下使用表锁可以节省大量资源出于性能考虑，绝大多数情况下使用的都是行锁。 myql中支持行锁，间隙锁，表锁 行锁：

某个事务对某行记录进行写 *** 作时，会把当前行锁住，其他事务不会对当前行 *** 作。粒度最小，并发最高的，频繁加锁释放锁。分为共享锁和排它锁。

表锁：

当某个事务对某行记录 *** 作时，可以将整个表锁住，innodb用得少，myisam只支持表锁。分为共享锁和排它锁。 间隙锁：

在条件范围 *** 作时，会给满足条件的区间数据加锁。 共享锁（S）:又称读锁

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这保证了其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。

排他锁（X）：又称写锁

允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务 T 对数据对象 A 加上 X 锁，事务 T可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用 select …for update 语句，加共享锁可以使用 select … lock in share mode 语句。

在查询时，必要情况下，也可以为读 *** 作加排它锁 select … * from 表 for update 语句

1.乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的。没有加锁，可以通过版本号来区分。

2.悲观锁：上面的行锁，间隙锁，表锁都是悲观锁。

三、sql优化

1、正确使用索引，查询条件列，排序列添加索引，项目中是如何使用的，查询条件
2、应避免索引失效 name like “%name%”；改为全文索引
在 where 子句中避免 num is null
避免在 where子句中使用！= 或<> *** 作符
避免在where子句中使用or来连接条件
避免在where num/2=100 使用运算符
避免在where中使用函数substring（）
3、mysql建议使用主键自增，合理利用索引结构
4、索引不宜建立太多，一般一张表6个左右，可以考虑组合索引最左前缀原则
5、状态，类型… 一般建议使用数字类型 int
varchar（变长）最大存储6个字符 ，代替char（定长 4 只有两个字的时候，也占4个字符）。
6、不建议使用 select * from t ，应该写出具体查询得列
7、一次性不要查询数据过多，分页查询，降低每次查询数据量，不要过多
8、避免字段值为null ，null是占空间的，可以给默认值' '