MySQL-进阶二

五、锁

5.1概述

• 介绍
  锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
• 分类
  MySQL中的锁，按照锁的粒度分，分为以下三类
  1.全局锁：锁定数据库中的所有表
  2.表级锁：每次操作锁住整张表
  3.行级锁：每次操作锁住对应得行数据

5.2全局锁

• 介绍
  全局锁就是锁住整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被堵塞
  其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
  
  特点
  数据库中加全局锁，是一个比较重的操作，存在以下问题：
  1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
  2.如果从库上备份，那么在备份期间不能执行主库同步过来的二进制日志，会导致主从延迟

在InnoDB引擎中，我们可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot -p 123456 itcast>itcast.sql

5.3表级锁

元数据锁
MDK加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上，MDL主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加DML读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。

查看元数据锁：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

• 意向锁
  1.意向共享锁（IS）：由语句select…lock in share mode添加。
  2.意向排它锁（IX）：由insert、updatee、select…for update添加。
  
  1.意向共享锁（IS）：与表锁共享锁(read）兼容，与表锁排他锁(write）互斥。
  2.意向排它锁（IX）：与表锁共享锁(read）及排它锁(write）都互斥。意向锁之间不会互斥。
  可以通过以下SQL,查看意向锁及行锁的加锁情况：

select object_scheme,object_name,index_name,lock_type,lock_mode,lock_data from performance_schma.data_locks;

5.4行级锁

• 介绍
  行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
  InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
  1.行锁：锁定单个记录的锁，防止其他事务对此进行updatee和delete.在RC、RR隔离级别下都支持
  2.间隙锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他业务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
  3.临键锁：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap.在RR隔离级别下支持。
  

• 间隙锁/临键锁

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
1.索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
2.索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
3.索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

六、InnoDB引擎

6.1逻辑存储结构

6.2架构

内存架构




磁盘结构



后台线程

6.3事务原理

• 事务
  事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败
• 特性
  原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  一致性：事务完成时，必须使所有的数据都保持一致状态
  隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  持久性：事务一旦提交成功，它对数据库中的数据的改变就是永久的
  
• redo log
  重做日志，记录的是事务提交时数据页的物理修改，使用来实现事务的持久性
  该日志文件由两部分组成：重做日志缓冲以及重做日志文件，前者是在内存中，后者是在磁盘中，当事务提交之后会把所有修改信息都存到该日志中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用
  
• undo logo
  回滚
  

6.4MVCC

6.4.1基本概念

• 当前读
  读取的是记录的最新版本，读取时还要保证其它并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们的日常操作，如：select…lock in share mode(共享锁),select …for update、update、insert、delete（排它锁）都是一种当前读。
• 快照读
  简单的select（不加锁）就是快照读，快照读，读取的就记录数据的可见版本，有可能是历史数据，不加锁，是非堵塞读。
  - Read Committed：每次select,都生成一个快照读。
  - Repeatable Read：开启事务后第一个select语句才是快照读的地方。
  - Serializable：快照读会退化为当前读。
• MVCC
  全称Multi-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快找读为MySQL实现MVCC提供了一个非堵塞读功能。MVCC的具题实现，还需要依赖于数据库中记录中的三个隐式字段、undo log日志、readView。

6.4.2MVCC-实现原理

• 记录中的隐藏字段
  
• undo log
  回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志
  当insert的时候，产生的undo log日志只在回滚的时需要，事务提交后，可被立即删除。
  而update、delete的时候，产生的undo log日志不仅在回滚的时候需要，在快照读时也需要，不会被立即删除。
• undo log版本链
  
  不同事务或者相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部时最新的旧记录，链表的尾部是最早的旧记录。
• readview
  ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
  ReadView中包含了四个核心字段：
  
  
  不同的隔离级别，生成ReadView的时机不同：
• READ COMMITTED：在事务中每一次执行快照读时生成ReadView
  
  
  
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView.
  
  

七、MySQL管理

7.1系统数据库

MySQL数据库安装完成后，自带了以下四个数据库，具体作用如下：

7.2常用工具

• mysql
  该mysql不是指MySQL服务，而是mysql的客户端工具
  
  -e选项可以在MySQL客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便
  
• mysqladmin
  mysqladmin是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
  

mysqladmin -uroot-p1234 create test1;
mysql -uroot -p1234 -e 'show databases'

• mysqlbinlog
  由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具：
  
• mysqlshow
  mysqlshow客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
  

mysqlshow -uroot -p1234 --count

mysqlshow -uroot -p1234 zwj --count

主要查看状态

mysqlshow -uroot -p1234 zwj course -i

• mysqldump
  mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句
  
  备份文件的话，需要备份到mysql信赖的文件目录中/var/lib/mysql-files

mysqldump -uroot -p1234 -T /var/lib/mysql-files/ zwj course

• mysqllimport/source
  mysqllimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件
  
  如果需要导入sql文件，可以使用mysql中的souce指令：

source /root/xxxxx.sql