面试深入之mysql

作者： 作者的头像   大菜狗 ,  2022-06-22 17:46:56 ,  所有人可见 ,  阅读 408

6


1

面试深入之mysql

mysql中的专有名词解释

  1. 脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
  2. 干净页：内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
  3. redo log:重做日志,确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做;redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
  4. undo log:回滚日志,保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读;undo log一般是逻辑日志，根据每行记录进行记录。
  5. binlog:归档日志,二进制日志,用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。
  6. 聚集(簇)索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。索引的叶子节点就是对应的数据节点
  7. 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。
  8. 慢查询：，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。

Mysql体系结构

组成

  1. 连接池组件
  2. 管理服务和工具组件
  3. SQL接口组件

  4. 查询分析器组件

  5. 优化器组件

  6. 缓冲(Cache)组件,
  7. 插件式存储引擎。
  8. 物理文件

MySQL存储引擎

背景

MySQL5对其结构体系做了较大的改造，并引入了一个新的概念：插件式存储引擎体系结构

存储引擎层和sql 层各自更为独立，耦合更小，甚至可以做到在线加载新的存储引擎，也就是完全可以将一个新的存
储引擎加载到一个正在运行的MySQL 中，而不影响MySQL 的正常运行。

插件式存储引擎的架构，为存储引擎的加载和移出更为灵活方便，也使自行开发存储引擎更为方便简单

MySQL 的插件式存储引擎主要包括MyISAM，Innodb，NDB Cluster，Maria，Falcon，Memory，Archive，Merge，Federated 等，其中最著名而且使用最为广泛的MyISAM 和Innodb两种存储引擎。

MyISAM

MyISAM的文件结构

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。

  1. 存放表结构定义信息的.frm 文件
  2. 存放了表的数据的.MYD文件
  3. 存放索引数据的.MYI文件

每个表都有且仅有这样三个文件做为MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在
同一个.MYI文件中。

MyISAM的索引类型

1.B-Tree索引

B-Tree 索引，顾名思义，就是所有的索引节点都按照balance tree 的数据结构来存储，所有的索引数据节点都在叶节点。

那就是参与一个索引的所有字段的长度之和不能超过1000 字节

2.R-Tree 索引

R-Tree 索引的存储方式和b-tree 索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的MySQL 版本来说，也仅支持geometry 类型的字段作索引。

3.Full-text 索引

Full-text 索引就是我们长说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like 查询的低效问题。

最经常使用的就是B-Tree 索引了，偶尔会使用到Fulltext，但是R-Tree 索引一般系统中都是很少用到的。另外MyISAM 的B-Tree 索引有一个较大的限制，那就是参与一个索引的所有字段的长度之和不能超过1000 字节。

MyISAM的索引存储格式

MyISAM 的数据存放格式是分为静态固定长度（FIXED）、动态可变长度（DYNAMIC）以及压缩（COMPRESSED）这三种格式

虽然每一个MyISAM 的表都是存放在一个相同后缀名的.MYD 文件中，但是每个文件的存放格式实际上可能并不是完全一样的

三种格式中是否压缩是完全可以任由我们自己选择的，可以在创建表的时候通过ROW_FORMAT 来指定{COMPRESSED | DEFAULT}，也可以通过myisampack 工具来进行压缩，默认是不压缩的

在非压缩的情况下，是静态还是动态，就和我们表中个字段的定义相关了。只要表中有可变长度类型的字段存在，那么该表就肯定是DYNAMIC 格式的，如果没有任何可变长度的字段，则为FIXED 格式

MyISAM引擎的缺点

MyISAM 存储引擎的某个表文件出错之后，仅影响到该表，而不会影响到其他表，更不会影响到其他的数据库

如果我们的出据苦正在运行过程中发现某个MyISAM 表出现问题了，则可以在线通过check table 命令来尝试校验他，并可以通过repairtable命令来尝试修复

Innodb 存储引擎

特点

1、支持事务安装

2、数据多版本读取

Innodb 在事务支持的同时，为了保证数据的一致性已经并发时候的性能，通过对undo信息，实现了数据的多版本读取。

3、锁定机制的改进

Innodb 改变了MyISAM 的锁机制，实现了行锁(MyISAM为表锁)。Innodb 的行锁机制的实现是通过索引来完成的

4、实现外键

innodb的文件结构

分为两大部分

数据文件(存放表数据和索引数据)

存放数据表中的数据和所有的索引数据，包括主键和其他普通索引

Innodb 的表空间分为两种形式

一种是共享表空间，也就是所有表和索引数据被存放在同一个表空间（一个或多个数据文件）中，通过innodb_data_file_path 来指定，增加数据文件需要停机重启。

另外一种是独享表空间，也就是每个表的数据和索引被存放在一个单独的.ibd 文件中。

注意事项

1.我们可以自行设定使用共享表空间还是独享表空间来存放我们的表

2.共享表空间都是必须存在的，因为Innodb 的undo 信息和其他一些元数据信息都是存放在共享表空间里面的

3.共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式的，自动扩展形式的文件可以设置文件的最大大小和每次扩展量

4.Innodb 不仅可以使用文件系统，还可以使用原始块设备，也就是我们常说的裸设备。

5.当我们的文件表空间快要用完的时候，我们必须要为其增加数据文件，当然，只有共享表空间有此操作。

6.Innodb 在创建新数据文件的时候是不会创建目录的，如果指定目录不存在，则会报错并无法启动。

7.Innodb 在给共享表空间增加数据文件之后，必须要重启数据库系统才能生效，如果是使用裸设备，还需要有两次重启。

日志文件

特点

1.Innodb 的日志文件和Oracle 的redo 日志比较类似，同样可以设置多个日志组（最少2个），同样采用轮循策略来顺序的写入，甚至在老版本中还有和Oracle 一样的日志归档特性。

2.由于Innodb 是事务安全的存储引擎，所以系统Crash 对他来说并不能造成非常严重的损失，

3.由于有redo 日志的存在，有checkpoint 机制的保护，Innodb 完全可以通过redo 日志将数据库Crash 时刻已经完成但还没有来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。

4.Innodb 的所有参数基本上都带有前缀“innodb_”，不论是innodb 数据和日志相关，还是其他一些性能，事务等等相关的参数都是一样。

Innodb体系架构详解

InnoDB有多个内存块，你可以认为这些内存块组成了一个大的内存池，负责如下工作：

  1. 维护所有进程/线程需要访问的多个内部数据结构。
  2. 缓存磁盘上的数据，方便快速地读取，并且在对磁盘文件的数据进行修改之前在这里缓存。
  3. 重做日志 (redo log）缓冲。

image-20220326104828327.png

后台线程

查看后台线程信息

show engine innodb status

内存

InnoDB存储引擎内存由以下几个部分组成：

  1. 缓冲池（buffer pool)、
  2. 重做日志缓冲池(redo log buffer）
  3. 额外的内存池（additional memory pool)，分别由配置文件中的参数innodb_buffer_pool_size和innodb_log_buffer_size的大小定。

缓冲池是占最大块内存的部分，用来存放各种数据的缓存。

InnoDB的存储引擎的工作方

因为InnoDB的存储引擎的工作方式总是将数据库文件按页（每页16K）读取到缓冲池，然后按最近最少使用（LRU）的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页（发生修改后，该页即为脏页），然后再按照一定的频率将缓冲池的脏页刷新(flush）到文件

innodb存储引擎内存结构.png

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据结构本身分配内存时，需要从额外的内存池中申请，当该区域的内存不够时，会从缓冲池中申请。InnoDB实例会申请缓冲池（innodb_buffer_pool）的空间，但是每个缓冲池中的帧缓冲（frame buffer）还有对应的缓冲控制对象（buffercontrol block)，而且这些对象记录了诸如LRU、锁、等待等方面的息，而这个对象的内存需要从额外内存池中申请。因此，当你申请了很大的InnoDB缓冲池时，这个值也应该相应增加。

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据结构本身分配内存时，需要从额外的内存池中申请，当该区域的内存不够时，会从缓冲池中申请。InnoDB实例会申请缓冲池（innodb_buffer_pool）的空间，但是每个缓冲池中的帧缓冲（frame buffer）还有对应的缓冲控制对象（buffercontrol block)，而且这些对象记录了诸如LRU、锁、等待等方面的信息，而这个对象的内存需要从额外内存池中申请。因此，当你申请了很大的InnoDB缓冲池时，额外内存池也应该相应增加。

主线程

InnoDB存储引擎的主要工作都是在一个单独的后台线程master thread中完成的

master thread的线程优先级别最高。其内部由几个循环（loop）组成：主循环（loop）后台循环 (background loop)、刷新循环(flush loop)、暂停循环(suspend loop)。masterthread会根据数据库运行的状态在loop、background loop、 flush loop和suspend loop中进行切换。

循环中每秒一次的操作包括：

日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）。

即使某个事务还没有提交，InnoDB存储引擎仍然会每秒将redo log缓冲中的内容刷新到redo log文件，这就是为什么再大的事务也会提交的很快

合并插人缓冲（可能)。

合并插人缓冲（insert buffer）并不是每秒都发生。InnoDB存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插人缓冲的操作。

至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）。

刷新100个脏页也不是每秒都在发生。InnoDB存储引擎通过判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中innodb.maxdirty_pages_pct这个参数（默认为90，代表90%），如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步操作，将100个脏页写人磁盘。

如果当前没有用户活动，切换到background loop(可能)。

若当前没有用户活动（数据库空闲时）或者数据库关闭时，就会切换到这个循环。这个循环会执行以下操作：
删除无用的Undo页 (总是)。
合并20个插人缓冲(总是)。
跳回到主循环 (总是)。
不断刷新100个页，直到符合条件（可能，跳转到flush loop中完成）。

两次写

如果说插人缓冲带给InnoDB存储引擎的是性能，那么两次写带给InnoDB存储引擎的是数据的可靠性。当数据库宕机时，可能发生数据库正在写一个页面，而这个页只写了一部分（比如16K的页，只写前4K的页）的情况，我们称之为部分写失效（partial page write）。在InnoDB存储引擎未使用double write技术前，曾出现过因为部分写失效而导致数据失的情况。

innodb的索引

b+树索引

b+树的结构及特点

1、b+树其实有点类似于线段树，非叶子节点中存放的是子树中某一段的头指针

2、b+树的子树间无高度差，所有叶子节点都在同一层，且用一个双向指针连接，且是严格按照主键从左到右排序；且每个叶子节点都是大小相同的页

b+树的插入操作

b+树的插入.png

这里总结一下：

1、首先，叶子节点的页大小是固定的，每页存放的数据的条数也是固定的

2、当要插入的那一页，页满的时候，先看看左边相邻的页有没有空间，有的话进行旋转，没有的话再考虑拆页

一个案例，插入

插入前：

b+树插入案例插入前.png

插入70(旋转)：

b+树插入时旋转.png

插入95(拆页)：

b+树插入时拆页.png

聚集索引

聚集索引.png

自适应哈希索引

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池
的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

MySQL安全管理

数据库系统安全的三大防线

1.网络

2.主机

3.数据库

4.代码

权限系统

相关权限信息主要存储在几个被称为granttables 的系统表中，即： mysql.User，mysql.db，mysql.Host，mysql.table_priv 和mysql.column_priv。

由于权限信息数据量比较小，而且访问又非常频繁，所以Mysql 在启动的时候，就会将所有的权限信息都Load 到内存中保存在几个特定的结构中

权限的5大级别

  1. Global Level：全局权限控制，所有权限信息都保存在mysql.user 表中。Global Level 的所有权限都是针对整个mysqld 的，对所有的数据库下的所有表及所有字段都有效。
  2. Database Level：Database Level 是在Global Level 之下，其他三个Level 之上的权限级别，其作用域
    即为所指定整个数据库中的所有对象
  3. Table Level：Table Level 的权限作用范围是授权语句中所指定数据库的指定表
  4. Column Level
  5. Routine Level

访问控制实现原理

用户想要访问数据库，必须提供：主机名或ip地址、用户名、密码。

如果要通过localhost 访问的话，必须要有一条专门针对localhost 的授权信息，即使不对任何主机做限制也不行

如果MySQL 正在运行之中的时候，我们对系统做了权限调整，那调整之后的权限什么时
候会生效呢？

Mysql数据备份

MyISAM 存储引擎的数据备份

1.MyISAM 存储引擎文件的物理文件比较集中，而且不支持事务没有redo和undo 日志，对数据一致性的要求也并不是特别的高，所以MyISAM 存储引擎表的物理备份也比较简单，只要将MyISAM 的物理文件copy 出来即可。

2.MyISAM存储引擎的同一个表的数据文件和索引文件之间是有一致性要求的。

3.当MyISAM 存储引擎发现某个表的数据文件和索引文件不一致的时候，会标记该表处于不可用状态，并要求你进行修复动作，

4.我们自己必须至少保证数据库在备份时候的数据是处于某一个时间点的，这样就要求我们必须做到在备份MyISAM 数据库的物理文件的时候让MyISAM 存储引擎停止写操作，仅仅提供读服务，其根本实质就是给数据库表加锁来阻止写操作。

5.MySQL 自己提供了一个使用程序mysqlhotcopy，这个程序就是专门用来备份MyISAM 存储引擎的

Innodb 存储引擎的数据备份

1.Innodb 存储引擎由于是事务性存储引擎，有redo 日志和相关的undo 信息，而且对数据的一致性和完整性的要求也比MyISAM 要严格很多，所以Innodb 的在线（热）物理备份要比MyISAM 复杂很多，一般很难简单的通过几个手工命令来完成，大都是通过专门的Innodb
在线物理备份软件来完成。

2.Innodb 存储引擎的开发者（Innobase 公司）开发了一款名为ibbackup 的商业备份软件，专门实现Innodb 存储引擎数据的在线物理备份功能。该软件可以在MySQL 在线运行的状态下，对数据库中使用Innodb 存储引擎的表进行备份，不过仅限于使用Innodb 存储引擎的表。

影响MySQL性能的相关因素

适合利用Cache提前缓存数据的场景

  1. 系统各种配置及规则数据；
  2. 活跃用户的基本信息数据；
  3. 活跃用户的个性化定制信息数据；
  4. 准实时的统计信息数据；
  5. 其他一些访问频繁但变更较少的数据；

合理的sql语句的应用

一个经典的对比案例

在我们的示例网站系统中，现在要实现每个用户查看各自相册列表（假设每个列表显示10 张相片）
的时候，能够在相片名称后面显示该相片的留言数量。这个需求大家认为应该如何实现呢？

方案一：

1、通过 

SELECT id,subject,url FROM photo WHERE user_id = ? limit 10

得到第一页的相片相关信息；

2、通过第1 步结果集中的10 个相片id 循环运行十次

SELECT COUNT(*) FROM photo_commentWHERE photh_id = ?

来得到每张相册的回复数量然后再瓶装展现对象。

方案二：

1、通过 

SELECT id,subject,url FROM photo WHERE user_id = ? limit 10

得到第一页的相片相关信息；

2、通过程序拼装上面得到的10 个photo 的id，再通过in 查询

SELECT photo_id,count(*) FROM photo_comment WHERE photo_id in (拼装后的id) GROUP BY photo_id

一次得到10 个photo 的所有回复数量，再组装两个结果集得到展现对象。

两种方案的复杂度对比
  1. 从MySQL 执行的SQL 数量来看，第一种解决方案为11（1+10=11）条SQL 语句，第二种解决方案
    为2 条SQL 语句（1+1）;
  2. 从应用程序与数据库交互来看，第一种为11 次，第二种为2 次；
  3. 从数据库的IO 操作来看，简单假设每次SQL 为1 个IO，第一种最少11 次IO，第二种小于等于11
    次IO，而且只有当数据非常之离散的情况下才会需要11 次；
  4. 从数据库处理的查询复杂度来看，第一种为两类很简单的查询，第二种有一条SQL 语句有GROUP
    BY 操作，比第一种解决方案增加了了排序分组操作；
  5. 从应用程序结果集处理来看，第一种11 次结果集的处理，第二中2 次结果集的处理，但是第二种
    解决方案中第二词结果处理数量是第一次的10 倍；
  6. 从应用程序数据处理来看，第二种比第一种多了一个拼装photo_id 的过程。
消耗性能上的对比
  1. 由于MySQL 对客户端每次提交的SQL 不管是相同还是不同，都需要进行完全解析，这个动作主要
    消耗的资源是数据库主机的CPU，那么这里第一种方案和第二种方案消耗CPU 的比例是11:2。SQL 语句的
    解析动作在整个SQL 语句执行过程中的整体消耗的CPU 比例是较多的;
  2. 应用程序与数据库交互所消耗的资源基本上都在网络方面，同样也是11：2；
  3. 数据库IO 操作资源消耗为小于或者等于1：1；
  4. 第二种解决方案需要比第一种多消耗内存资源进行排序分组操作，由于数据量不大，多出的消耗
    在语句整体消耗中占用比例会比较小，大概不会超过20%，大家可以针对性测试；
  5. 结果集处理次数也为11：2，但是第二中解决方案第二次处理数量较大，整体来说两次的性能消
    耗区别不大；
  6. 应用程序数据处理方面所多出的这个photo_id 的拼装所消耗的资源是非常小的，甚至比应用程
    序与MySQL 做一次简单的交互所消耗的资源还要少。
总结

综合上面的这6 点比较，我们可以很容易得出结论，从整体资源消耗来看，第二中方案会远远优于第一种解决方案。

而在实际开发过程中，我们的程序员却很少选用。主要原因其实有两个，

一个是第二种方案在程序代码实现方面可能会比第一种方案略为复杂，尤其是在当前编程环境中面向对象思想的普
及，开发工程师可能会更习惯于以对象为中心的思考方式来解决问题。

还有一个原因就是我们的程序员可能对SQL 语句的使用并不是特别的熟悉，并不一定能够想到第二条SQL 语句所实现的功能。对于

探究sql语句对性能的影响

当MySQL Server 的连接线程接收到Client 端发送过来的SQL 请求之后，会经过一系列的分解Parse，进行相应的分析。然后，MySQL 通过查询优化器模块（Optimizer）根据该SQL 所设涉及到的数据表的相关统计信息进行计算分析，然后再得出一个MySQL 认为最合理最优化的数据访问方式，也就是我们常说的“执行计划”，然后再根据所得到的执行计划通过调用存储引擎借口来获取相应数据。然后再将存储引擎返回的数据进行相关处理，并以Client 端所要求的格式作为结果集返回给Client 端的应用程序。

锁

两种锁的级别

InnoDB存储引擎实现了如下两种标准的行级锁：

  1. 共享锁 (S Lock)，允许事务读一行数据。
  2. 排他锁 (X Lock)，允许事务删除或者更新一行数据。

当一个事务已经获得了行r的共享锁，那么另外的事务可以立即获得行r的共享锁，因为读取并没有改变行r的数据，我们称这种情况为锁兼容。但如果有事务想获得行r的排他
锁，则它必须等待事务释放行r上的共享锁这种情况我们称为锁不兼容。

意向锁

InnoDB存储引擎支持多粒度锁定，这种锁定允许在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，我们称之
为意向锁。意向锁是表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。InnoDB存储引擎支持两种意向锁：

  1. 意向共享锁 (IS Lock)，事务想要获得一个表中某几行的共享锁。
  2. 意向排他锁 (IX Lock)，事务想要获得一个表中某几行的排他锁。

因为InnoDB存储引擎支持的是行级别的锁，所以意向锁其实不会阻塞除全表扫以外的任何请求。

查看锁的信息

版本的InnoDB Plugin中，在INFORMATION_SCHEMA架构下添加了INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，可以更简单地监控当前的
事务并分析可能存在的锁的问题。通过实例我们来分析这三张表，先看表INNODB_TRX，

INNODB_TRX由8个字段组成：

  1. trx_id：InnoDB存储引擎内部唯一的事务ID。
  2. trx_state：当前事务的状态。
  3. trx_started：事务的开始时间。
  4. trx_requested_lock_id：等待事务的锁ID。如trx_state的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state不是LOCK WAIT，则该值为NULL。
  5. trx_wait_started：事务等待开始的时间。
  6. trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小的进行回滚。
  7. trx_mysql_thread_id：MySQL中的线程ID，SHOW PROCESSLIST显示的结果。
  8. trx_query：事务运行的SQL语句。在实际使用中发现，该值有时会显示为NULL(不知道是不是Bug)。

INNODB_LOCK_WAITS由4个字段组成：

  1. requesting_trx_id：申请锁资源的事务ID.
  2. requesting_lock_id：申请的锁的ID。
  3. blocking_trxid：阻塞的事务ID。
  4. blocking_trx_id：阻塞的锁的ID。

一致性非锁定读

一致性的非锁定行读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正
在执行DELETE、UPDATE操作，这时读取操作不会因此而会等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据。

快照数据是指该行之前版本的数据，该实现是通过Undo段来实现。而Undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。

可以看到，非锁定读的机制大大提高了数据读取的并发性，在InnoDB存储引擎默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别
下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。同样，即使都是使用一致性读，但是对于快照数据的定义也不相同。

快照

快照数据其实就是当前行数据之前的历史版本，可能有多个版本。一个行可能有不止一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（MultiVersionConcurrencyControl，MVCC)。

不同事务隔离级别下的快照

在Read Committed和Repeatable Read（InnoDB存储引擎的默认事务隔离级别）下，InnoDB存储引擎使用非锁定的一致性读。

在ReadCommitted事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据,这其实不太符合事务的隔离性原则。

在Repeatable事务隔离级别下和Repeatable Read事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

自增长和锁

从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插人的性能。InnoDB
存储引擎提供了一个参数innodb_autoinc_lock_mode，默认值为1。

另外，InnoDB存储引擎下，自增长值的列必须是索引，并且是索引的第一个列，如果是第二个列则会报错；而MyISAM存储引擎则没有这个问题

innodb_autoinc_lock_mode=1:·。对于在插入前就能确定行数的语句(Simple inserts)，该值会用互斥量（mutex）去对内存中的计数器进行累加的操作。

对于插入前不能得到确定行数的语句(Bulk inserts),还是使用传统表锁的AUTO-INC Locking方式。这样做，如果不考虑回滚操作，对于自增值的增长还是连续的。而且在这种方式下Statement-Based方式的Replication还是能很好地工作。需要注意的是，如果已经使用表锁(AUTO-INC Locing)的方式产生自增长的值，而这时需要再进行“Simple inserts”的操作时，还是要等待AUTO-INC Locking的释放。

innodb_autoinc_lock_mode=2在这个模式下，对于所有“INSERT-like”自增长值的产生都是通过互斥量，而不是AUTO-INC Locking的方式。显然，这是最高性能的方式。然而，这带来一定的问题。因为并发插人的存在，所以每次插人时，自增长的值可能不是连续的。此外，最重要的是，基于Statement-Base Replication会出现问题。因此，使用这个模式，任何时候都应该使用Row-Base Replication。这样才能保证最大的并发性能和Replication数据的同步。

外键和锁

前面已经介绍了外键，外键主要用于引用完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，
因为这样可以避免表锁这比Oracle做得好，Oracle不会自动添加索引，用户必须自己手工添加，这也是导致很多死锁问题产生的原因。

对于外键值的插入或者更新，首先需要查询父表中的记录，即SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的
问题，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，主动对父表加一个S锁。如果这时父表上已经这样加X锁，那么子表上的操作会被阻塞，

锁的算法

InnoDB存储引擎有3中行锁的算法设计，分别是：

  1. Record Lock：单个行记录上的锁。
  2. Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
  3. Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

例子

对于：

select * from sys_user where user_id = 5 lock in share mode

如果主键5,前面的数字是1，间隙锁会锁住(1,5);不包括1,5，行锁会锁住1

Record Lock总是会去锁住索引记录。如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。对于不同SQL查询语句，可能设置共
享的（Share）Next-Key Lock和排他的（exlusive）Next-Key Lock。

锁的一些问题

丢失更新

丢失更新（lost update）是一个经典的数据库问题。实际上，所有多用户计算机系统
环境下有可能产生这个问题。简单说来，出现下面的情况时，就会发生丢失更新：
（1）事务T1查询一行数据，放人本地内存，并显示给一个终端用户User1。
（2）事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
（3）User1修改这行记录，更新数据库并提交。
(4）User2修改这行记录，更新数据库并提交。
显然，这个过程中用户User1的修改更新操作“丢失”了。即第一次更新的值被第二次更新覆盖了

想要解决，就得把更新的行加一个排他锁

脏读

理解脏读之前，需要理解脏数据的概念。脏数据和脏页有所不同。

脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘，即数据库实例内存中的页和磁盘的页中的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写人了重做日志文件中。

而所谓脏数据，是指在缓冲池中被修改的数据，并且还没有被提交(commit)。对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步同步造成的，这并不影响数据的一致性。并且因为是异步的，因此可以带来性能的提高。而脏数据却不同，脏数据是指未提交的数据。如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的据，则显然违反了数据库的隔离性。脏读指的就是在不同的事务下，可以读到另外事务未提交的数据，简单来说，就是可以读到脏数据。比如下面的例子所示：

不可重复读

不可重复读是指在一个事务内多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读。

不可重复读和脏读的区别是：脏读是读到未提交的数据；而不可重复读读到的确实是已经提交的数据，但是其违反了数据库事务一致性的要求。

0 评论