MySQL架构与锁

1. MySQL架构概览

        MySQL的层级大概可以分为3类：

• 第一层：连接层，只要负责MySQL的数据库连接，安全认证的功能；
• 第二层：MySQL核心层面，主要功能包括：MySQL的查询，缓存，执行计划，优化等；
• 第三层：引擎层，为MYSQL指定不同的引擎将达到不同的数据操作效果。

2. Query Cache

        MySQL的Query Cache是基于hash值计算进行匹配的缓存机制。在大数据量的情况下如果开启Query Cache会频繁的计算Hash，会增加性能的消耗，得不偿失，在生产环境建议关闭该选项。

可使用语句：show VARIABLES like '%query_cache%'查看Query Cache是否关闭。主要关注参数query_cache_type是否关闭(OFF关闭，ON开启)，不用过于关注缓存分配大小的query_cache_size参数。

3. 读锁

        MySQL中根据不同的引擎，主要出现三类锁的情况：表锁、读锁、写锁。

读锁：也是共享锁，即多用户状态下同一时间对资源的读取互不影响，但不能对数据进行修改等操作。

使用场景：一般情况下手动给一条或某个范围内(一般是用在存储过程)的数据加上读锁。

读锁语法示例：

select 字段 from 表名 [where 条件] lock in share mode;

4. 写锁

写锁：是"排他锁"，也称"独立锁"。

使用场景：一般是写入数据的情况，一个用户如果获得写锁，其他用户将不能获取写锁或读锁，直到该用户执行完操作并释放锁。

使用方式：在执行语句后加for update语句

写锁语法示例：

select 字段 from 表名 [where 条件] for update;

5. 锁粒度

锁粒度：对资源锁定范围的一个程度，使用不同的锁定策略达到并发性能较优的结果。

锁粒度使用策略情况分为：行锁、表锁、页锁。

1. 表锁

概念：对整张表进行加锁。

优缺点：

• 优点：性能开销较小，加锁的速度较快
• 缺点：锁粒度大，并发低

手动加表锁语法示例：

lock table 表名

释放锁：

unlock tables 表名

2. 行锁

        概念：对行进行锁定。

优缺点：

• 优点：能够最大支持并发量，故锁粒度最小
• 缺点：加锁速度慢，性能消耗大，会出现死锁

行锁的种类：

• 记录锁(主键或者唯一索引)
• 间隙锁(GAP)，一般用于查询条件是范围情况下，而非相等条件
• 记录锁和间隙锁的组合(next-key lock)

3. 页锁

通常情况下遇不到页锁，其开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间。

知识点：

• MyISAM和Memory引擎支持表锁，会自动给select、update、insert、delete自动加表锁
• InnoDB支持表锁和行锁，会自动给update、insert、delete语句的数据加排他锁，select语句不加锁

4. 乐观锁

乐观锁基于版本号实现。

        注意点：条件必须是主键，读取时将数据版本号读出，更新数据时，版本号加1，将查询的数据进行对比，如果版本号不一致就是过期数据。

查询示例：

select id,value,version from 表名 where id=#{id}

更新示例：

update 表名 set value=2,version=version+1 where id=#{id} and version=#{version}

5. 悲观锁

        表锁、行锁、读写锁都是悲观锁。

6. 引擎简介

        MySQL支持多种引擎，主流使用的引擎是InnoDB，其次是MyISAM，特殊情况下使用Memory。

1. InnoDB

使用最广泛的引擎，也是最重要的引擎。

存储性能：

• InnoDB是可重复读的事务隔离级别，但其实现了next key lock，防止 的幻读 出现
• 基于聚簇索引实现
• 主要组成结构为内存结构、线程、磁盘文件组

2. MyISAM

MyISAM在早期版本是MySQL的默认引擎，在MySQL5.1之后不再使用。

特点：不支持事务，不支持行锁，默认表锁，并发量低

3. Memory

存储内容都是存放在引擎当中。

特点：

• 优点：支持Hash和Btree索引，其数据读取快
• 缺点：服务器如果出现故障重启后就会造成数据丢失