黑马MySQL数据库从入门到精通-进阶篇 3 锁 概述 1 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、1/0)的争用以外，数据也是-种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访可性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
分类 1 2 3 4 MySQL中的锁，按照锁的粒度分，分为以下三类 1.全局锁:锁定数据库中的所有表。 2.表级锁:每次操作锁住整张表。 3.行级锁:每次操作锁住对应的行数据
全局锁 介绍 1 2 全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。 其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
1 2 3 如果备份时不加锁，会导致数据不一致 mysqldump是mysql提供的备份工具
演示 1 2 3 flush tables with read lock; unlock tables;
特点 1 2 3 4 5 6 数据库中加全局锁，是一个比较重的操作，存在以下问题: 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟(mysql的主从复制，读写分离在运维篇) 在innoDB引擎中，我们可以在备份时加上参数--single-transaction 参数来完成不加锁的一致性数据备份。(不使用全局锁，innodb通过快照读来实现)
表级锁 介绍 1 表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、lnnoDB、BDB等存储引警中
1 2 3 4 对于表级锁，主要分为以下三类: 表锁 元数据锁(meta data lock，MDL) 意向锁
表锁 1 2 3 对于表锁，分为两类: 1.表共享读锁(read lock) 2.表独占写锁(write lock)
1 2 3 语法: 1.加锁: lock tables 表名... read/write。 2.释放锁: unlock tables / 客户端断开连接
读锁
演示
特点 写锁 1 2 3 语法: 加锁: lock tables 表名... read/write 释放锁:unlock tables / 客户端断开连接
1 2 3 MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读/写锁(共享)，当对表结构进行变更操作的时候，加MDL(排他)。
1 2 查看元数据锁: select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks
1 执行alter操作，会申请排它锁，但此时已有共享锁，且事务还未提交，因此出现阻塞
意向锁 1 为了避免DML在执行时，加的行锁与表锁的冲突，在innoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
1 上图为不加意向锁，需要逐行检查行锁情况，有则阻塞
1 会检查当前意向锁和想加的表锁是否兼容，不兼容则阻塞，阻塞到事务提交，不需要逐行检查
1 2 意向共享锁(IS):由语句select ... lock in share mode添加。 意向排他锁 (IX): 由insert、update、delete、select ...for update 添加。
1 2 意向共享锁 (IS):与表锁共享锁 (read)兼容，与表锁排它锁 (write) 互斥 意向排他锁 (IX):与表锁共享锁 (read) 及排它锁 (write) 都互。意向锁之间不会互斥
1 select obiect_schema,obiect_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks
意向共享锁
1 上面这句话的意思是，会为对应行加上行锁的共享锁，同时为表加上意向共享锁
1 上面表共享读锁能成功加上，是因为与表意向共享锁兼容
1 当左边事务提交，对应的行锁和意向共享锁没了，右边阻塞消失
意向排他锁
1 上边左边命令提交后，会为对应行加上排它行锁，对应表加上意向排它锁
1 此时右边相加表排它写锁会阻塞，因为与意向排它锁不兼容，等左边事务提交后，右边阻塞消失
行级锁 1 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
1 2 3 4 innoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类: 行锁 (Record Lock) : 锁定单个行记录的锁。防止其他事各对此行进行update和delete。在RC，RR隔离级别下都支持 间隙锁 (Gap Lock)：锁定索引记录间隙 (不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持 临键锁(Next-Key Lock): 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
行锁 1 2 共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。 排他锁(X):允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
1 2 3 4 默认情况下，innoDB在REPEATABLE READ事务隔离级别运行，innoDB使用next-key 锁进行搜索和索引扫描，以防止幻读针。 对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁 InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么lnnoDB将对表中的所有记录加锁，此时就会升级为表锁
1 name字段没有建索引，此时左边操作后自动加表锁，此时右边阻塞
1 对name字段建立索引，此时右边不会阻塞，没有建立表锁
间隙锁和临键锁 间隙锁 1 2 3 4 默认情况下，lnnoDB在REPEATABLE READ事务隔离级别运行，lnnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
1 注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
临键锁 1 这里以18为例，普通锁，18，16-18也会加锁（因为不是唯一索引所以之前有可能插入数据），18-29会加锁（理由和前面一样），29邻键锁退化为间隙锁（符合第二条）
1 2 3 这里加lock in share mode是为了看见行级锁。3，3前后都会加锁。符合第二条。3，3表示3那行。7，7表示7那行 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
1 2 3 符合第三条。19，25,19-25，25->正无穷都会加锁 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
InnoDB引擎 逻辑存储结构
1 表空间(ibd文件)，一个mysql实例可以对应多个表空间文件，用于存储记录、索引等数据
1 段，分为数据段(Leaf node segment)、索引段 (Non-leaf node segment)、回滚段(Rollback segment)，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。
1 区，表空间的单元结构，每个区的大小为1M。默认情况下，innoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
1 页，是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，innoDB存储引肇每次从磁盘申请4-5个区。
1 2 3 4 行，innoDB存储引擎数据是按行进行存放的。 Trx_id: 每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。 Roll_pointer: 每次对某条引记录进行改动时，都会把旧的版本写入undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息
架构 1 MVSQL5.5版本开始，默认使用1nnoDB存储引擎，它擅长事务处理，具有溃恢复特性，在日常开发中使用非常广泛。下面是1nnoDB架构图，左侧为内存结构，右侧为磁盘结构。
内存架构 buffer pool 1 Buffer Pool: 缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
1 2 3 4 缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型 free page:空闲page，未被使用。 clean page:被使用page，数据没有被修改过。 dirty page: 脏页，被使用page，数据被修改过，页中数据与磁盘的数据产生了不一致。
change buffer 1 2 5.几版本叫insert buffer 8.0之后叫change buffer
1 Change Buffer: 更改缓冲区(针对于非唯一，二级索引页)，在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
1 2 Change Buffer的意义是什么? 与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了Change Buffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。
adaptive hash index 1 2 3 4 Adaptive Hash index:自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。 自适应哈希索引，无需人工干预，是系统根据情况自动完成。 参数:adaptive_hash_index
log buffer 1 2 3 4 Log Buffer:日志缓冲区，用来保存要写入到磁盘中的log日志数据(redo log、undo log)，默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。 参数: innodb_log_buffer_size: 缓冲区大小 innodb_flush_log_at_trx_commit:日志刷新到磁盘时机
1 如何一台专门的服务器来运行mysql，一般80%的内存都用于缓冲区
磁盘架构 system tablespace 1 2 System Tablespace: 系统表空间是更改缓冲区的存储区域(8.几)。如果表是在系统表空间而不是每个表空间或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等) 参数:innodb_data_file_path
per-file-tables tablespaces 1 2 3 File-Per-Table Tablespaces: 每个表的文件表空问包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。 参数:innodb_file_per_table
general tablespaces 1 2 3 4 5 General Tablespaces: 通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，可以指定该表空间。 CREATE TABLESPACS XXXX ADD DATAFILE 'file_name' ENGINE=engine_name; CREATE TABLE XXX ... TABLESPACE ts_name;
undo tablespaces
1 Undo Tablespaces: 撒销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间(初始大小16M)，用于存储undo log日志。
temporary tablespaces 1 Temporary Tablespaces: InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据
doublewrite buffer files
1 Doublewrite Buffer Files: 双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
redo log
1 Redo Log: 重做日志，是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer，在log buffer里)以及重做日志文件(redo log),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复使用。
后台线程
Master Thread 1 核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。
IO Thread 1 在InnoDB存储引擎中大量使用了AIO(异步非阻塞IO)来处理IO请求,这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。
Purge Thread 1 主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收
Page Cleaner Thread 1 协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。
事务原理
redo log
1 2 3 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。 该日志文件由两部分组成: 重做日志缓冲(redo log buffer)以及重做志文件(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。
1 客户端提交一组DML操作到服务端，首先检查buffer pool中是否有相应的数据，无则加载数据，数据加载后进行数据更新，生成脏页。事务提交后，缓冲池中的脏页数据在刷新到磁盘中时发生错误，此时数据持久性得不到保障
1 2 3 4 5 在引入redo log后 在对缓存区的数据修改后，会将修改记录到redo buffer中，在事务提交后会将数据页的变化写到磁盘中。在某一时刻进行脏页刷新时发生错误，此时可以根据redo log数据恢复。脏页成功刷新后，每隔一段时间会对redo log日志进行清理 这里涉及到顺序磁盘IO和随机磁盘IO问题
undo log
1 2 3 4 5 回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和MVCC(多版本并发控制) undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行roll back时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。 Undo log销毁: undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC. Undo log存储: undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment
MVCC多版本并发控制(面试部分很多) 1 在高并发的数据库操作环境中，确保事务的一致性与隔离性
基本概念 当前读 1 2 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如: select...lock in share mode(元数据共享读锁)，select ... for update、update、insert、delete(元数据共享写锁)都是一种当前读。
1 右边事务未提交时，左边查不到更新(事务隔离级别重复读可以解决脏读)。右边提交后，左边还是查不到更新(事务隔离级别重复读解决了不可重复读)。
快照读 1 2 3 4 5 简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。 Read Committed:每次select，都生成一个快照读。 Repeatable Read:开启事务后第一个select语句才是快照读的地方(后续的普通select是查询的前面的快照，如上面的左边图) Serializable:快照读会退化为当前读。
MVCC 1 2 3 全称Multi-version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MYSQL实现MVCC提供了一个非阻塞读功能。 MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
记录中的隐藏字段
1 2 3 4 隐藏字段 含义 DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。 DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。 DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。(不一定有，当表没有主键时，mysql才会自动创建该字段)
undo log 1 2 3 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
undo log版本链
1 第2个事务执行更新数据操作后，现在undo log中记录原来的版本数据，然后修改记录，记录事务id和回滚指针
1 不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
read view 1 ReadView(读视图)是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的) id。
四个核心字段 1 2 3 4 5 字段 含义 mids 当前活跃的事务ID集合 min_trx_id 最小活跃事务ID max_trx_id 预分配事务ID，当前最大事务ID+1(因为事务ID是自增的) creator_trx_id ReadView创建者的事务ID
版本链数据访问规则 1 2 3 4 5 6 ~~~ 
trx_id是undo log版本链和当前记录的最近修改事务id(DB_TRX_ID)
1 2 3 
不同的隔离级别，生成ReadView的时机不同
RC隔离级别下，在事务中每一次执行快照读时生成ReadView
1 2 3 4 5  
分析事务5两次快照读时readview的情况
第一次
第二次
RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
1 2 3 4 5  
隐藏字段+undo log+read view这三部分实现了MVCC原理
Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下:(版本不同可能不一样，这是8.几)
1 2 3 4 5  #### mysql
包含mysql服务器运行所需要的信息
主从复制，慢日志，时区操作，用户相关表等
1 2 3 4 5 6 7 8 9 10 11 #### information_schema ~~~~ mysql服务器里数据库的一些元数据信息 都支持什么存储引擎，当前innodb引擎各项指标，创建了哪些innnodb表，表空间等 存储过程，存储函数相关信息，当前数据库有哪些表，数据库的说明，触发器，视图 ~~~~ #### performance_schema
mysql服务器的运行状态会记录在里面
_.datalocks 意向锁，行级锁
二进制日志，错误日志，事件信息，事务相关信息等
1 2 3 4 5 6 7 ### 常用工具 #### mysql
这里指的不是mysql服务，而是mysql客户端工具
语法：
-h -P没有的话表示连接当前主机上的
-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。
1 2 3 4 5 6 7   #### mysqladmin
mysqladmin是个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等
mysqladmin –help
创建删除数据库，debug查看详细日志，刷新操作，进程信息，关闭服务，查看变量，开启关闭主从复制
主要运用在脚本中查看和操作mysql服务器
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理具。
1 2 3 4 5 6 7   #### mysqlshow
mysqlshow客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
1 2 3 4 5 6 7 8 9 10 11 12 13      #### mysqldump
可以将整个数据库或单个表格的数据导出到一个文件中。
mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句
1 2 3 4 5  
内容报错创建表，删除表，插入语句
1 2 3 
不包含表创建语句
1 2 3 
不包含插入语句
1 2 3 
并没有创建两个文件，是因为这个目录不是mysql指定的目录
1 2 3 4 5  
此时sql文件中记录的是创建，删除表的语句，没有insert语句
1 2 3 4 5 6 7  #### mysqlimport/source ##### mysqlimport
mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件。
1 2 3 
删除score这张表里的所有数据，再恢复
1 2 3 4 5 6 7  ##### source 
删除db01下的所有表，再通过source恢复。source命令需要再mysql客户端下使用
1 2 3 4 5  
表恢复了，数据同时也恢复了
