MySQL高级（下）

MVCC

---

MVCC（多版本并发控制 Multi-Version Concurrent Control）

为了提升mysql 读-写,写-读两个操作同时进行, 写-写mysql支持行级锁的,如果操作同一行数据,那么肯定是不可以的.

每次对表中的记录操作时,会保存一个日志(undolog) 里面会记录事务的id号.

如果有多个事务操作时,他们就会根据事务id,找到自己操作的版本记录.

不同的隔离级别在读数据时, 会根据版本链生成一个ReadView(临时读视图) 版本链快照

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

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

---

MVCC（多版本并发控制 Multi-Version Concurrent Control），是 MySQL 提高性能的一种方式，配合 Undo log 和版本链,替代锁,让不同事务的 读-写、写-读操作可以并发执行，从而提升系统性能。一般在使用 读已提交 （READ COMMITTED）和 可重复读（REPEATABLE READ）隔离级别的事务 中实现。

锁机制

概述
首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有 一个事务对数据进行写操作，InnoDB 通过锁机制来保证这一点。
锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的 锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的， 其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁,间隙锁,表锁

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的间隙锁。表锁 在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并 发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要 消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL 中不同的存储引擎支持的锁是不一样的，例如 MyIsam 只支持表锁，而 InnoDB 同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。
InnoDB 为例

行锁

行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行 加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也 最大。行级锁分为共享锁 和 排他锁。
特点：
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

间隙锁

锁的是一个区间,当我们用范围条件而不是相等条件检索数据，InnoDB 会给 符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记 录，叫做“间隙",InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间 隙锁（Next-Key 锁）。

表锁

表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加 锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享锁与表排他锁。
特点：
开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
共享锁（S）： 又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据 集的排他锁。若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改 A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这 保证了其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。
排他锁（Ｘ）： 又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得 相同的数据集共享读锁和排他写锁。若事务 T 对数据对象 A 加上 X 锁，事务 T 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。 update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不 会加任何锁类型，如果加排他锁可以使用 select …for update 语句，加共享锁 可以使用 select … lock in share mode 语句。
还可以分为乐观锁,悲观锁：

1. 乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的.
2. 悲观锁：上面的行锁,z 间隙锁,表锁等都是悲观锁

SQL优化

为什么要对 SQL 进行优化？

项目上线初期，由于业务数据量相对较少，一些 SQL 的执行效率对程序运 行效率的影响不太明显，而开发和运维人员也无法判断 SQL 对程序的运行效率 有多大，故很少针对 SQL 进行专门的优化，而随着时间的积累，业务数据量的 增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对 SQL 的优化 就很有必要。

SQL 优化的一些方法

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免索引失效
   2.1 在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索 引而进行全表扫描，如：
   select id from t where num is null
   可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：
   select id from t where num=0
   2.2 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用 索引而进行全表扫描。
   2.3 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃 使用索引而进行全表扫描，如：
   select id from t where num=10 or num=20
   2.4 in 和 not in 也要慎用，否则会导致全表扫描，如：
   select id from t where num in(1,2,3)
   2.5 下面的查询也将导致全表扫描：
   select id from t where name like ‘%abc%’
   2.6 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃 使用索引而进行全表扫描。如：select id from t where num/2=100
   应改为：
   select id from t where num=100*2
   2.7 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使 用索引而进行全表扫描。如：
   select id from t where substring(name,1,3)=‘abc’ – name 以 abc 开头的 id
3. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降 低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑， 视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的 索引是否有必要。
4. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降 低查询和连接的性能，并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字 型而言只需要比较一次就够了。
5. 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节 省存储空间， 其次对于查询来说，在一个相对较小的字段内搜索效率显然要 高些。
6. 任何地方都不要使用 select * from t，用具体的字段列表代替“*”，不要返 回用不到的任何字段。
7. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
8. 尽量避免 NULL,平时开发过程中，MySQL 字段的一般都会设置为 NOT NULL，原因如下：
   空值(‘ ’)是不占用空间的 MySQL 中的 NULL 其实是占用空间的. 如果查询中包含可为 NULL 的列，不走索引 count()统计某列的记录数的时候，如果采用的 NULL 值，会被系统自动忽略 掉，但是空值是会进行统计到其中的。