网站友情链接要加什么,网站广告布局,品牌形象宣传策划方案,ps软件下载官网免费基础篇
InnoDB、MyISAM 和 MEMORY 存储引擎的区别#xff1f;
主要区别#xff1a; 为什么MySQL选择 InnoDB 作为默认存储引擎#xff1f;
1.innodb支持事务#xff0c;myisam、memory不支持。
2.innodb支持行级锁#xff0c;可以使多个事务同时访问不同的行#xf…基础篇
InnoDB、MyISAM 和 MEMORY 存储引擎的区别
主要区别 为什么MySQL选择 InnoDB 作为默认存储引擎
1.innodb支持事务myisam、memory不支持。
2.innodb支持行级锁可以使多个事务同时访问不同的行提高并发性能myisam、memory仅支持表级锁。
3.innodb支持外键约束能保证数据的完整性myisam、memory不支持。
比如student表中含有学号、姓名课程号course表中含有课程号任课教师将课程号设置成foreign key当course表中课程号发生修改对应student表也发生修改。
4.innodb提供崩溃恢复机制myisam、memory不支持。
5.innodb、myisam将数据存储在磁盘上而memory将所有数据存储在内存中虽然访问速度非常快但数据并不持久服务器重启后数据会丢失。
执行一条select语句时期间发生了什么 第一步连接器
如果在Linux操作系统下使用MySQL第一步首先是要连接MySQL服务先确保你的Linux系统下MySQL服务已经启动。
安装MySQL sudo apt-get install mysql-server 启动MySQL sudo service mysql start 连接MySQL mysql -u root -p 连接的过程要经历TCP三次握手完成TCP连接后连接器就要开始验证你的用户名和密码如果用户名或密码不正确就会收到一个Access denied for user的错误然后客户端程序结束执行。
如果用户名、密码都没有问题连接器就会获取该用户的权限然后保存起来后续该用户在此连接里的任何操作都会基于连接开始时读到的权限进行权限逻辑的判断。
所以如果一个用户已经建立了连接即使管理员中途修改了该用户的权限也不会影响已经存在连接的权限。修改完成后只有再新建的连接才会使用新的权限设置。
总结 与客户端进行三次握手建立连接校验客户端的用户名和密码如果用户名或密码不对则报错如果用户名和密码没有问题会读取该用户的权限后面权限逻辑的判断都会基于此时读取到的权限 如何查看 MySQL 服务被多少个客户端连接了 show processlist 空闲连接会一直占用着吗
不会MySQL定义了空闲连接的最大空闲时长默认8小时28880秒一旦超过这个时间连接会自动断开。
查看最大空闲连接时长 show variables like wait_timeout; MySQL 的连接数有限制吗
有限制MySQL 5.7 版本默认最大连接数为151个。
查看最大连接数 show variables like max_connections; MySQL和HTTP一样有长连接和短连接之分使用长连接会导致占用内存增多因为MySQL在执行查询的过程中临时使用内存来管理连接对象在断开连接的时候才会释放。
如何解决长连接占用内存问题 定期断开长连接客户端主动重置连接使用mysql_reset_connection()函数 第二步查询缓存 如果SQL是查询语句MySQL就会先去查询缓存里查找数据看看之前有没有执行过这条命令这个查询缓存是以key-value形式保存的key为SQL查询语句value为SQL语句查询的结果。如果查询的语句命中缓存那么直接返回value给客户端如果查询到的语句没有命中缓存那么就要继续向下执行等执行完后查询到的结果会被存入查询缓存中。 注在MySQL 8.0版本中已经将查询缓存删掉了这里说的查询缓存指的是server层的并不是Innodb存储引擎中的buffer pool
第三步解析SQL
这时候解析器会做两件事
1.词法分析
解析器会根据输入的SQL语句识别出关键字来 2.语法分析
根据词法分析的结果语法解析器会根据语法规则判断输入的SQL语句是否满足MySQL语法如果没有问题就构建出SQL语法树方便后面模块获取SQL类型、表名。字段名、where条件等。 注意这个阶段只会对SQL语句的语法进行检查表或字段不存在并不是在解析器里做的。
第四步执行SQL
经过解析器后接着就进入SQL查询语句的流程了每条select查询语句流程主要分为以下这三个阶段预处理阶段、优化阶段、执行阶段。
预处理阶段 检查SQL查询语句中的表或者字段是否存在将select * 中的 * 符号扩展为表上的所有列 优化阶段 经过预处理阶段优化器负责为SQL语句制定一个执行计划比如在表里有多个索引时优化器会基于查询成本的考虑来决定使用哪个索引。 如何查看select语句使用了个哪索引
在select语句前面加一个explain即可。
执行阶段
经历了优化阶段后进入执行阶段此时执行器就会和存储引擎交互交互是以记录为单位的。
总结执行一条 SQL 查询语句期间发生了什么? 连接器建立连接、管理连接、校验用户身份查询缓存查询语句如果命中查询缓存则直接返回否则继续往下执行(MySQL 8.0 已删除);解析SQL通过解析器对 SQL查询语句进行词法分析、语法分析然后构建语法树方便后续模块读取表名、字段、语句类型执行 SQL执行 SQL 共有三个阶段 预处理阶段检查表或字段是否存在将select*中的*符号扩展为表上的所有列优化阶段基于查询成本的考虑选择查询成本最小的执行计划执行阶段根据执行计划执行 SQL查询语句从存储引擎读取记录返回给客户端 面试题MySQL一行记录是怎么存储的
mysql数据存放在哪个文件 我们每创建一个 database数据库 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名 的目录然后保存表结构和表数据的文件都会存放在这个目录里。 比如创建一个名为 my_test 的 database该 database 里有一张名为 t_order 数据库表。 ls /var/lib/mysql/my_test 进入该目录会有三个文件
1.db.opt用来存储当前数据库的默认字符集和字符校验规则。
2.t_order.frm存储表结构每建立一张表就会生成一个 .frm文件。
3.t_order.ibd存储表数据自 MySQL 5.6.6 版本开始MySQL 中每张表的数据都存放在一个独立的.idb文件也称为独占表空间文件。
表空间文件的结构是什么样的 数据库表中记录是按行存放的但是数据库的读取不是以行为单位而是按页进行读写一页默认16KB。
InnoDB 的行格式有哪些
InnoDB 提供了 4 种行格式分别是 Redundant、Compact、Dynamic和 Compressed 行格式。
Redundant 是很古老的行格式了 MySQL 5.0 版本之前用的行格式现在基本没人用了。 由于 它不是一种紧凑的行格式所以 MySQL 5.0 之后引入了 Compact 行记录存储方式。Compact 是一种紧凑的行格式设计的初衷就是为了让一个数据页中可以存放更多的行记录从 MySQL 5.1 版本之后行格式默认设置成 Compact。 Dynamic 和 Compressed 两个都是紧凑的行格式它们的行格式都和 Compact 差不多因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后默认使用 Dynamic 行格式。
Compact行格式长什么样 记录的额外信息包含 3 个部分变长字段长度列表、NULL 值列表、记录头信息。 1.变长字段长度列表保存变长字段的真实数据占用的字节数。变长字段字节数列表不是必须的。 当数据表没有变长字段的时候比如全部都是 int 类型的字段这时候表里的行格式就不会有变 长字段长度列表。 2.NULL 值列表如果存在允许 NULL 值的列则每个列对应一个二进制位bit二进制位按照列的顺序逆序排列。 二进制位的值为 1 时代表该列的值为NULL。 二进制位的值为 0 时代表该列的值不为NULL。 另外NULL 值列表必须用整数个字节的位表示1字节8位如果使用的二进制位个数不足整数 个字节则在字节的高位补 0 。 NULL 值列表也不是必须的。 当数据表的字段都定义成 NOT NULL 的时候这时候表里的行格式就不会有 NULL 值列表了。 所以在设计数据库表的时候通常都是建议将字段设置为 NOT NULL这样可以至少节省 1 字节的空间NULL 值列表至少占用 1 字节空间。 3.记录头信息包含当前记录是否被删除的标识、下一条记录的位置、当前记录的类型等。 记录真实的数据 1.row_id 如果我们建表的时候指定了主键或者唯一约束列那么就没有 row_id 隐藏字段了。如果既没有指定主键又没有唯一约束那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的 占用 6 个字节。 2.trx_id 事务id表示这个数据是由哪个事务生成的。 trx_id是必需的占用 6 个字节。 3.roll_pointer记录上一个版本的指针。roll_pointer 是必需的占用 7 个字节。 面试题MySQL 的 NULL 值是怎么存放的 MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列NULL 值并不会存储在行格式中的真实数据部分。 NULL值列表会占用 1 字节空间当表中所有字段都定义成 NOT NULL行格式中就不会有 NULL 值列表这样可节省 1 字节的空间。 面试题MySQL 怎么知道 varchar(n) 实际占用数据的大小 MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。 面试题varchar(n) 中 n 最大取值为多少 一行记录最大能存储 65535 字节的数据但是这个是包含「变长字段字节数列表所占用的字节 数」和「NULL值列表所占用的字节数」。所以 我们在算 varchar(n) 中 n 最大值时需要减去这 两个列表所占用的字节数。 举例如果一张表只有一个 varchar(n) 字段且允许为 NULL字符集为 ascii。varchar(n) 中 n 最大取值为 65532。 计算公式65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 65535 - 2 - 1 65532。 如果有多个字段的话要保证所有字段的长度 变长字段字节数列表所占用的字节数 NULL值列 表所占用的字节数 65535。 面试题行溢出后MySQL 是怎么处理的 如果一个数据页存不了一条记录InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。 Compact 行格式针对行溢出的处理是这样的当发生行溢出时在记录的真实数据处只会保存该 列的一部分数据而把剩余的数据放在「溢出页」中然后在真实数据处用 20 字节存储指向溢出页的地址从而可以找到剩余数据所在的页。 Compressed 和 Dynamic 这两种格式采用完全的行溢出方式记录的真实数据处不会存储该列的 一部分数据只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。 索引篇
预览 什么是索引
索引的定义就是帮助存储引擎快速获取数据的一种数据结构类比于书的目录索引相当于是数据的目录。
索引的分类 按「数据结构」分类:Btree索引、Hash索引、Full-text索引 按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引) 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引 按「字段个数」分类:单列索引、联合索引 按数据结构分类
从数据结构角度看MySQL分为Btree索引、Hash索引、Full-text索引。
不同存储引擎支持的索引类型不一定相同以下是MySQL常见存储引擎分别支持的存储类型 InnoDB是在MySQL 5.5之后默认的MySQL存储引擎BTree索引类型也是MySQL索引类型采用的最多的索引类型。
BTree 是一种多叉树叶子节点才存放数据非叶子节点只存放索引而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中因此在叶子节点中包括了所有的索引值信息并且每一个叶子节点都有两个指针分别指向下一个叶子节点和上一个叶子节点形成一个双向链表。 上图是一张商品表表中的行数据存储在BTree索引时是什么样的呢
主键索引的BTree
注这里的叶子节点之间实际上是双向链表。
二级索引的BTree
主键索引的 BTree和二级索引的 BTree的区别 主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree的叶子节点里二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据 如果采用二级索引product_no查询商品首先会检索二级索引BTree的索引值product_no找到相应叶子节点获取主键值然后再根据主键索引的BTree查询对应的叶子节点然后获取整行数据。这个过程叫做回表也就是说要查询两个BTree才能查到数据。
注意当查询的数据能在二级索引的叶子节点中查到时就不需要再查询主键索引的BTree了。
这种在二级索引的BTree就能查到结果的过程叫做覆盖索引也就是只需要查一个BTree就能得到数据。
BTree 相比于 B树、二叉树或 Hash 索引结构的优势有哪些 1、BTree vs B Tree BTree 只在叶子节点存储数据而 B树 的非叶子节点也要存储数据所以 BTree 的单个节点的数据量更小在相同的磁盘 I/O次数下就能查询更多的节点。BTree 叶子节点采用的是双链表连接适合 MySQL 中常见的基于范围的顺序查找而B树无法做到这一点。 2、BTree vs 二叉树 对于有 N 个叶子节点的 BTree其搜索复杂度为 O(logdN)其中 d表示节点允许的最大子节点个数为d个。在实际的应用当中d值是大于100的这样就保证了即使数据达到千万级别时BTree 的高度依然维持在 3~4 层左右也就是说一次数据査询操作只需要做 3~4 次的磁盘 I/O 操作就能査询到目标数据。而二叉树的每个父节点的儿子节点个数只能是2个意味着其搜索复杂度为O(logN)这已经比 BTree高出不少因此二叉树检索到目标数据所经历的磁盘 I/O次数要更多。 3、BTree vs Hash 虽然 Hash 在做等值查询时效率非常快搜索复杂度为 O(1)但是 Hash 表不适合做范围査询它更适合做等值的査询这也是 BTree 索引要比 Hash 表索引有着更广泛的适用场景的原因。 按物理存储分类
从物理存储的角度来看索引分为聚簇索引(主键索引)、二级索引(辅助索引)。 主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree的叶子节点里二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。 按字段特性分类
从字段特性的角度来看索引分为主键索引、唯一索引、普通索引、前缀索引。
主键索引
主键索引就是建立在主键字段的索引通常在创建表的时候一起创建一张表最多只有一个主键索引索引列的值不允许为空。
在创建表时创建主键索引的方式如下 CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column_name) ); 唯一索引
唯一索引就是建立在UNIQUE字段上的索引一张表可以有多个唯一索引索引列的值必须唯一但允许有空值。
在创建表时创建唯一索引的方式如下 CREATE TABLE table_name ( column1 datatype, column2 datatype, ... UNIQUE KEY(column_name1,column_name1,...) ); 在已经存在的表上添加唯一索引方式如下 CREATE UNIQUE INDEX index_name ON table_name(column_name1,column_name2,...); 普通索引
在创建表时创建普通索引的方式如下 CREATE TABLE table_name ( column1 datatype, column2 datatype, ... INDEX(column_name1,column_name1,...) ); 在已经存在的表上添加普通索引方式如下 CREATE INDEX index_name ON table_name(column_name1,column_name2,...); 前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引而不是在整个字段上建立的索引前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间提升查询效率。
在创建表时创建前缀索引的方式如下 CREATE TABLE table_name ( column1 datatype, column2 datatype, ... INDEX(column_name(prefix_length)) ); 在已经存在的表上添加前缀索引方式如下 CREATE INDEX index_name ON table_name(column_name(prefix_length)); 使用 show index 查看表中所有索引 show index from table_name; 使用 alter table 删除表中索引 alter table table_name drop index index_name; 按字段个数分类
从字段个数的角度来看索引分为单列索引、联合索引复合索引。 单列索引建立在单列上的索引比如主键索引联合索引建立在多列上的索引 联合索引的最左匹配原则在遇到范围查询(如、)的时候就会停止匹配也就是范围查询的字段可以用到联合索引但是在范围查询字段的后面的字段无法用到联合索引。注意对于、、BETWEEN、like 前缀匹配的范围查询并不会停止匹配。
索引优缺点 优点 提高查询速度 缺点 需要占用物理空间数量越大占用空间越大创建索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率因为每次增删改索引B 树为了维护索引有序性都需要进行动态维护。 索引使用场景
什么时候适用索引? 字段有唯一性限制的比如商品编码;经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们已经知道了建立索引之后在 BTree 中的记录都是排序好的。 例如select * from order where status 1 order by create_time asc; 我们可以给status和create_time建立一个联合索引这样根据status筛选后的数据就是按照create_time排好序的。
什么时候不适用索引? WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引比如性别字段只有男女如果数据库表中男女的记录分布均匀那么无论搜索哪个值都可能得到一半的数据。在这些情况下还不如不要索引因为MySQL 还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候它一般会忽略索引进行全表扫描。表数据太少的时候不需要创建索引;经常更新的字段不用创建索引比如不要对电商项目的用户余额建立索引因为索引字段频繁修改由于要维护 BTree的有序性那么就需要频繁的重建索引这个过程是会影响数据库性能的。 常见优化索引方法
前缀索引优化 前缀索引可以减小索引字段大小进而增加一个索引页中存储的索引值有效提高索引的查询速度。在一些大字符串的字段作为索引时使用前缀索引可以帮助我们减小索引项的大小 不过前缀索引有一定的局限性例如 order by 无法使用前缀索引无法把前缀索引用作覆盖索引 覆盖索引优化 覆盖索引可以从二级索引中查询得到记录而不需要通过聚簇索引查询得到可以避免回表操作。 主键索引最好是自增的 如果使用自增主键那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有的数据当页面写满就会自动开辟一个新页面。因为每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。如果我们使用非自增主键由于每次插入主键的索引值都是随机的因此每次插入新的数据时就可能会插入到现有数据页中间的某个位置这将不得不移动其它数据来满足新数据的插入甚至需要从一个页面复制数据到另外一个页面我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。 另外主键字段的长度不要太大因为主键字段长度越小意味着二级索引的叶子节点越小(二级索引的叶子节点存放的数据是主键值)这样二级索引占用的空间也就越小。
索引最好设置为NOT NULL
为了更好的利用索引索引列要设置为 NOT NULL 约束。有两个原因 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化因为可为NULL的列会使索引、索引统计和值比较都更复杂比如进行索引统计时count 会省略值为 NULL 的行。NULL值是一个没意义的值但是它会占用物理空间所以会带来的存储空间的问题因为 InnoDB 存储记录的时候如果表中存在允许为 NULL 的字段那么行格式中至少会用1字节空间存储 NULL 值列表。 防止索引失效
发生索引失效场景 当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效当我们在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。 特殊情况1.如果数据库中的字段只有主键二级索引即使使用了左模糊匹配也不会走全表扫描typeall而是走全扫描二级索引树typeindex。2.如果数据库表中的字段都是索引的情况下即使查询过程中没有遵循最左匹配原则索引也不会失效会走全扫描二级索引树。
哪种count性能最好
什么是count()?
count()是一个聚合函数函数的参数不仅可以是字段名也可以是其他任意表达式该函数的作用是统计符合查询条件的记录中函数指定参数不为NULL的记录个数。
按照性能排序count(*) count(1) count(主键字段) count(字段)
执行流程
在通过count函数统计记录个数时server层会维护一个名叫count的变量server层会循环向innodb读取一条记录如果count函数指定的参数不为NULL那么count值加1直到符合条件的记录全部读取完就退出循环。最后将count的值发给用户。
count(*)、count(1)、count(主键字段)执行时如果表中存在二级索引优化器优先选择二级索引进行扫描。如果存在多个二级索引优先选择key_len最小的二级索引进行扫描。对于count(*)、count(1)innodb以相同方式进行处理没有性能差异。
count(字段)采用全表扫描的方式来计数执行效率最差。
如何优化count(*)?
1.如果对于统计个数不需要很准确可以使用 show table status 或 explain 进行估算。执行explain命令不会真正去查询效率很高。例explain select count(*) from table_name;
2.如果想要精确获得表中记录总数可以使用额外表保存计数值。将计数值保存在单独的一张表中当在表中插入或删除一条记录时计数表中计数字段加1或减1。
事务篇
预览
基本语法参见我之前的文章CSDN
事务作为单个逻辑单元执行的一系列sql语句的集合操作要么都执行要么都不执行。
事务有哪些特性 原子性事务是最小的工作单元不可再分要么都执行要么都不执行。 一致性数据库的完整性约束不能被破坏。 隔离性并发执行的多个事务彼此不影响。重点掌握持久性事务数据提交以后持久的保存在数据库中。 事务是由Mysql的引擎实现的那么InnoDB引擎通过什么技术来保证事务的这四个特性的呢? 持久性通过 redo log(重做日志)来保证 原子性通过 undo log(回滚日志)来保证 隔离性通过 MVCC (多版本并发控制)或锁机制来保证 一致性通过持久性原子性隔离性来保证 并发事务会引发什么问题
MySQL服务端是允许多个客户端连接的即MySQL会出现同时处理多个事务的情况那么在同时处理多个事务的时候就可能出现脏读、不可重复读、幻读的问题。
脏读 如果一个事务读到了另一个未提交事务修改过的数据就意味着发生了脏读现象。 不可重复读 在一个事务内多次读取同一个数据如果前后两次读到的数据不一样就意味着发生了不可重复读现象。 幻读 在一个事务内多次查询某个符合查询条件的记录数量如果前后两次查询到的记录数量不一样就意味着发生了幻读现象。 这三种现象会对事务的一致性产生不同程度影响按严重性排序如下 脏读 不可重复读 幻读 事务的隔离级别有哪些
SQL标准提出了四种隔离级别来规避这些现象隔离级别越高性能效率就越低这四个隔离级别如下 读未提交指一个事务还没提交时它做的变更就能被其他事务看到读提交指一个事务提交之后它做的变更才能被其他事务看到可重复读指一个事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的MySQL InnoDB 引擎的默认隔离级别串行化会对记录加上读写锁在多个事务对这条记录进行读写操作时如果发生了读写冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行 按隔离水平高低排序如下 串行化 可重复读 读提交 读未提交 针对不同的隔离级别并发事务时可能发生的现象也不同 在「读未提交」隔离级别下可能发生脏读、不可重复读和幻读现象在「读提交」隔离级别下可能发生不可重复读和幻读现象不可能发生脏读现象在「可重复读」隔离级别下可能发生幻读现象不可能发生脏读和不可重复读现象在「串行化」隔离级别下脏读、不可重复读和幻读现象都不可能发生 总结要解决脏读现象就要升级到「读提交」以上的隔离级别要解决不可重复读现象就要升级到「可重复读」的隔离级别要解决幻读现象不建议将隔离级别升级到「串行化」。 MySQL 在「可重复读」隔离级别下可以很大程度上避免幻读现象的发生所以 MySQL并不会使用「串行化」隔离级别来避免幻读现象的发生因为使用「串行化」隔离级别会影响性能。 前面提到隔离级别越高性能效率就越低 注「可重复读」隔离级别下可以很大程度上避免幻读现象的发生但不是完全避免。 如何解决「可重复读」中可能存在的幻读现象 解决方案有两种 针对快照读(普通 select 语句)是通过 MVCC方式解决了幻读因为可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的即使中途有其他事务插入了一条数据是查询不出来这条数据的所以就很好了避免幻读问题。针对当前读(select...for update 等语句)是通过 next-key lock(记录锁间隙锁)方式解决了幻读因为当执行 select... for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。 快照读是如何避免幻读的 「可重复读」隔离级是由 MVCC(多版本并发控制)实现的实现的方式是开始事务后(执行 begin 语句后)在执行第一个查询语句后会创建一个Read View后续的查询语句利用这个 Read View通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据所以事务过程中每次查询的数据都是一样的即使中途有其他事务插入了新记录是查询不出来这条数据的所以就很好了避免幻读问题。 当前读是如何避免幻读的 事务A执行了上面这条锁定读语句后就在对表中的记录加上 id 2 的 next-key lock 然后事务B在执行插入语句的时候判断到插入的位置被事务A加了 next-key lock于是事物B会生成一个插入意向锁同时进入等待状态直到事务A提交了事务。这就避免了由于事务B插入新记录而导致事务A发生幻读现象。 MySQL 里除了普通查询是快照读其他都是当前读比如 update、insert、delete这些语句执行前都会查询最新版本的数据然后再做进一步的操作。这很好理解假设你要 update 一个记录另一个事务已经 delete 这条记录并且提交事务了这样不是会产生冲突吗所以 update 的时候肯定要知道最新的数据。另外select ... for update 这种查询语句是当前读每次执行的时候都是读取最新的数据。 注即使给出了这两种解决方案仍然没有完全解决幻读现象。
为什么说幻读没有被完全解决举个例子 场景一 事务A执行查询 id 5 的记录此时表中没有该记录所以查询不出来。然后事务B插入一条 id 5 的记录并且提交了事务。此时事务A更新 id 5 的这条记录再次查询事务A就能看到 id5的这条记录了。 场景二 T1 时刻事务A先执行「快照读语句」select * from t_test where id 100 得到了3条记录T2 时刻事务B插入一个 id 200 的记录并提交T3 时刻事务A再执行「当前读语句」 select * from t_test where id 100 for update 就会得到4条记录此时也发生了幻读现象。 要避免这类特殊场景下发生幻读现象尽量在开启事务之后马上执行 select ... for update 这类当前读的语句因为它会对记录加 next-key lock从而避免其他事务插入一条新记录。 Read View 在MVCC是如何工作的
了解 Read View 在MVCC里是如何工作的需要了解两个知识 Read View 中四个字段的作用聚簇索引记录中两个跟事务有关的隐藏列 Read View四个字段的作用
对于使用 InnoDB 存储引擎的数据库表它的聚簇索引记录中都包含下面两个隐藏列 trx_id当一个事务对某条聚簇索引记录进行改动时就会把该事务的事务 id 记录在 trx_id 隐藏列里roll_pointer每次对某条聚族索引记录进行改动时都会把旧版本的记录写入到 undo 日志中然后这个隐藏列是个指针指向每一个旧版本记录于是就可以通过它找到修改前的记录。 在创建Read View后我们可以将记录中的trx_id划分为这三种情况 一个事务去访问记录的时候除了自己的更新记录总是可见之外还有这几种情况 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值表示这个版本的记录是在创建 Read View前已经提交的事务生成的所以该版本的记录对当前事务可见。如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值表示这个版本的记录是在创建 Read View 后才启动的事务生成的所以该版本的记录对当前事务不可见。如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间需要判断 trx_id 是否在m_ids 列表中 如果记录的 trx_id 在 m_ids列表中表示生成该版本记录的活跃事务依然活跃着(还没提交事务)所以该版本的记录对当前事务不可见。如果记录的 trx_id 不在m_ids列表中表示生成该版本记录的活跃事务已经被提交所以该版本的记录对当前事务可见。 这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC(多版本并发控制)
版本链每当某个事务对一行数据进行更新时InnoDB 存储引擎不会直接修改原始数据而是会创建该数据行的一个新版本。新版本会被插入到表中同时保留原始版本以便其他事务可以根据它们的隔离级别和事务开始时间戳来访问历史数据。版本链由行记录中的 trx_id标识该行数据是由哪个事务修改的和 roll_pointer回滚指针指向该行数据的前一个版本如果没有前一个版本这个指针为 NULL进行管理每个行版本都有一个指向前一个版本的指针从而形成了一个“版本链”。
介绍了这么多总结一下什么是MVCC
对于读提交和可重复读隔离级别通过事务的ReadView 里的字段 记录中的两个隐藏列的对比如果不满足可见性就会顺着 undo_log 版本链里找到满足其可见性的记录从而控制并发事务访问同一个记录。
具体流程MVCC 通过版本链来控制并发事务访问同一个记录当一个事务执行读操作时会根据当前的读视图来判断读取哪个版本的数据。如果数据行创建版本号小于当前读视图的 min_trx_id说明该数据行是在当前事务开始之前就已经提交的那么当前事务可以直接读取到该数据行如果数据行创建的版本号大于当前读视图的 min_trx_id 且小于当前读视图的 max_trx_id并且创建的版本号不在当前的读视图的 m_ids 列表中说明该数据行是在当前事务开始之后创建的但创建该数据行的事务已经提交那么当前事务也可以读取到该数据行否则当前事务不能读取到该数据行。
当一个事务执行写操作如插入、更新、删除时不会直接修改原数据而是会创建该数据行的一个新版本。新版本会被插入到表中同时保留旧版本由 roll_pointer 指向旧版本这样其他事务在读取该行数据时会根据版本链和读视图来确定读取哪个版本的数据。
MVCC 的优缺点
优点1.提高并发性能允许多个事务同时读取同一行数据的不同版本而不会互相阻塞从而提高数据库并发处理能力减少了锁的竞争和使用提高系统吞吐量。2.保证事务隔离级别通过 MVCC 机制实现不同的事务隔离级别如读已提交、可重复读确保事务的隔离性。
缺点1.存储开销每次更新都会创建新的版本数据行这会占用额外的存储空间尤其是对于频繁更新的表。2.清理过期版本需要有机制来清理过期的、不可见的数据版本这通常由后台线程负责完成否则可能导致数据库存储的膨胀。
可重复读是如何工作的 「可重复读」隔离级别是启动事务时生成一个Read View然后整个事务期间都在用这个Read View因此「可重复读」隔离级别下在整个事务期间读到的记录都是事务启动前的记录。 读提交是如何工作的 「读提交」隔离级别是在每次读取数据时都会生成一个新的 Read View因此事务期间多次读取同一条数据前后两次读的数据可能不一致因为在这期间可能出现另外一个事务修改了该记录并提交了事务。 锁篇
预览 在MySQL中根据加锁的范围可以分为全局锁、表级锁和行级锁三类。
全局锁
使用
使用全局锁 flush tables with read lock 执行后整个数据库就处于只读状态了这时其他线程执行以下操作都会被阻塞 对数据的增删改操作比如insert、delete、update等语句 对表结构的更改操作比如alter table、drop table等语句。 释放全局锁 unlock tables 如果会话断开了全局锁会自动释放。
应用场景 全局锁主要应用于做全库逻辑备份这样在备份数据库期间不会因为数据或表结构的更新而出现备份文件的数据与预期的不一样。 举例
如果在全库逻辑备份期间有用户购买了一件商品一般购买商品的业务逻辑是会涉及到多张数据库表的更新比如在用户表更新该用户的余额然后在商品表更新被购买的商品的库存。
如果在备份用户表和商品表之间有用户购买了商品这种情况下备份的结果是用户表中该用户的余额并没有扣除反而商品表中该商品的库存被减少了如果后面用这个备份文件恢复数据库数据的话用户钱没少而库存少了等于用户白嫖一件商品。
所以在全库逻辑备份期间加上全局锁就不会出现上面这种情况了。
缺点 加上全局锁意味着整个数据库都是只读状态。如果数据库里有很多数据备份就会花费很多的时间关键是备份期间业务只能读数据而不能更新数据这样会造成业务停滞。 既然备份数据库数据的时候使用全局锁会影响业务那有什么其他方式可以避免?
如果数据库的引擎支持的事务支持可重复读的隔离级别那么在备份数据库之前先开启事务会先创建 Read View然后整个事务执行期间都在用这个 Read View而且由于 MVCC 的支持备份期间业务依然可以对数据进行更新操作。
解释因为在可重复读的隔离级别下即使其他事务更新了表的数据也不会影响备份数据库时的 Read View这就是事务四大特性中的隔离性这样备份期间备份的数据一直是在开启事务时的数据。
备份数据库的工具是 mysqldump在使用 mysqldump 时加上 -single-transaction 参数的时候就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。
InnoDB 存储引擎默认的事务隔离级别是可重复读因此可以采用这种方式来备份数据库。但是对于 MyISAM 这种不支持事务的引擎在备份数据库时就要使用全局锁的方法。
表级锁
MySQL表级锁有哪些 表锁元数据锁MDL意向锁AUTO-INC锁 表锁
如果我们想对学生表t_student加表锁可以使用以下命令
表级别的共享锁读锁 lock tables t_student read; 注 如果本线程对学生表加了共享表锁那么接下来任何线程对该表的写操作都是阻塞的包括本线程直到锁被释放。
表级别的独占锁写锁 lock tables t_student write; 要释放表锁可以使用下面这条命令会释放当前会话的所有表锁 unlock tables 另外当会话退出后也会释放所有表锁。
注尽量避免在使用 InnoDB 引擎的表使用表锁因为表锁的颗粒度太大会影响并发性能。
元数据锁MDL
我们不需要显示的使用 MDL因为当我们对数据库表进行操作时会自动给这个表加上 MDL 对一张表进行 CRUD create、read、update、delete操作时加的是 MDL 读锁对一张表做结构变更操作的时候加的是 MDL写锁 MDL是为了保证当用户对表执行 CRUD 操作时防止其他线程对这个表结构做了变更。
当有线程在执行 select 语句(加 MDL 读锁)的期间如果有其他线程要更改该表的结构 (申请 MDL写锁)那么将会被阻塞直到执行完 select 语句(释放 MDL 读锁)。 同样当有线程对表结构进行变更(加 MDL 写锁)的期间如果有其他线程执行了 CRUD 操作(申请MDL 读锁)那么就会被阻塞直到表结构变更完成(释放 MDL 写锁)。
MDL不需要显示调用那它是在什么时候释放的? MDL是在事务提交后才会释放这意味着事务执行期间MDL是一直持有的。 如果数据库有一个长事务(所谓的长事务就是开启了事务但是一直还没提交)那在对表结构做变更操作的时候可能会发生意想不到的事情比如下面这个顺序的场景 1.首先线程A先启用了事务(但是一直不提交)然后执行一条 select 语句此时就先对该表加上MDL 读锁 2.然后线程B也执行了同样的 select 语句此时并不会阻塞因为「读读」并不冲突 3.接着线程C修改了表字段此时由于线程A的事务并没有提交也就是 MDL 读锁还在占用着这时线程C就无法申请到 MDL 写锁就会被阻塞 那么在线程C阻塞后后续有对该表的 select 语句就都会被阻塞如果此时有大量该表的 select 语句的请求到来就会有大量的线程被阻塞住这时数据库的线程很快就会爆满了。 为什么线程C因为申请不到 MDL 写锁而导致后续申请读锁的查询操作也会被阻塞? 因为申请 MDL 锁的操作会形成一个队列队列中写锁获取优先级高于读锁一旦出现 MDL写锁等待会阻塞后续该表的所有 CRUD 操作。 所以为了能安全的对表结构进行变更在对表结构变更前先要看看数据库中的长事务是否有事务已经对表加上了 MDL 读锁如果有的话可以考虑结束这个长事务然后再做表结构的变更。
意向锁
意向锁是 InnoDB 存储引擎自动管理的不需要用户显式地加锁。
意向锁的目的是为了快速判断表里是否有记录被加锁。 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前需要先在表级别加上一个「意向共享锁」在使用 InnoDB 引擎的表里对某些记录加上「独占锁」之前需要先在表级别加上一个「意向独占锁」也就是当执行插入、更新、删除操作时需要先对表加上「意向独占锁」然后对该记录加独占锁。 如果没有「意向锁」那么加「独占锁」时就需要遍历表里所有记录查看是否有记录存在独占锁这样效率会很慢。
那么有了「意向锁」由于在对记录加独占锁前先会加上表级别的意向独占锁那么在加「独占表锁」时直接查该表是否有意向独占锁如果有就意味着表里已经有记录被加了独占锁这样就不用去遍历表里的记录。
AUTO-INC锁
通过对主键字段声明 AUTO-INCREMENT 属性实现主键自增之后在插入数据时可以不指定主键的值数据库会自动给主键赋递增的值这主要是通过 AUTO-INC 锁实现的。
AUTO-INC 锁是特殊的表锁机制锁不是在一个事务提交后才释放而是执行完插入语句后就会立即释放。 在插入数据时会加一个表级别的 AUTO-INC 锁然后为被 AUTO_INCREMENT 修饰的字段赋递增的值等插入语句执行完成后才会把 AUTO-INC 锁释放掉。那么一个事务在持有 AUTO-INC 锁的过程中其他事务如果要向该表插入数据都会被阻塞从而保证插入数据时被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是 AUTO-INC 锁在对大量数据进行插入的时候会影响插入性能因为另一个事务中的插入会被阻塞。 因此在 MySQL 5.1.22 版本开始InnoDB 存储引擎提供了一种轻量级的锁来实现自增。 同样在插入数据时会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁然后给该字段赋一个自增的值就把这个轻量级锁释放了而不需要等待整个插入语句执行完后才释放锁。 InnoDB 存储引擎提供了 innodb_autoinc_lock_mode 的系统变量是用来控制选择用 AUTO-INC锁还是轻量级的锁。 当 innodb_autoinc_lock_mode0就采用 AUTO-INC 锁语句执行结束后才释放锁。当 innodb_autoinc_lock_mode2就采用轻量级锁申请自增主键后就释放锁并不需要等语句执行后才释放。当 innodb_autoinc_lock_mode1 普通 insert 语句自增锁在申请之后就马上释放类似 insert ... select 这样的批量插入数据的语句自增锁还是要等语句结束后才被释放 行级锁
行级锁的类型主要有三类 Record Lock记录锁也就是仅仅把一条记录锁上Gap Lock间隙锁锁定一个范围但是不包含记录本身Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。 Record Lock
Record Lock 称为记录锁锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的 当一个事务对一条记录加了 S 型记录锁后其他事务也可以继续对该记录加 S 型记录锁但是不可以对该记录加 X 型记录锁当一个事务对一条记录加了 X 型记录锁后其他事务既不可以对该记录加 S 型记录锁也不可以对该记录加 X 型记录锁。 Gap Lock Gap Lock 称为间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。 假设表中有一个范围 id 为 (3,5) 间隙锁那么其他事务就无法插入 id 4 这条记录了这样就有效的防止幻读现象的发生。 间隙锁虽然存在 X 型间隙锁和 S 型间隙锁但是并没有什么区别间隙锁之间是兼容的即两个事务可以同时持有包含共同间隙范围的间隙锁并不存在互斥关系因为间隙锁的目的是防止插入幻影记录而提出的。
Next-Key Lock Next-Key Lock 称为临键锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。 假设表中有一个范围 id 为 (35] 的 next-key lock那么其他事务即不能插入 id 4 记录也不能修改 id 5 这条记录。 所以next-key lock 即能保护该记录又能阻止其他事务将新记录插入到被保护记录前面的间隙中 next-key lock 是包含间隙锁记录锁的如果一个事务获取了 X 型的 next-key lock那么另外一个事务在获取相同范围的 X 型的 next-key lock 时是会被阻塞的。 注虽然相同范围的间隙锁是多个事务相互兼容的但对于记录锁我们是要考虑 X 型与 S 型关系X 型的记录锁与 X 型的记录锁是冲突的。
插入意向锁
一个事务在插入一条记录的时候需要判断插入位置是否已被其他事务加了间隙锁(next-key lock 也包含间隙锁)。如果有的话插入操作就会发生阻塞直到拥有间隙锁的那个事务提交为止(释放间隙锁的时刻)在此期间会生成一个插入意向锁表明有事务想在某个区间插入新记录但是现在处于等待状态。
举个例子假设事务 A 已经对表加了一个范围 id 为 (35) 间隙锁。当事务 A 还没提交的时候事务 B 向该表插入一条 id 4 的新记录这时会判断到插入的位置已经被事务 A 加了间隙锁于是事务 B 会生成一个插入意向锁然后将锁的状态设置为等待状态(注MySQL 加锁时是先生成锁结构然后设置锁的状态如果锁状态是等待状态并不是意味着事务成功获取到了锁只有当锁状态为正常状态时才代表事务成功获取到了锁)此时事务B就会发生阻塞直到事务 A 提交了事务。 插入意向锁名字虽然有意向锁但是它并不是意向锁它是一种特殊的间隙锁属于行级别锁。如果说间隙锁锁住的是一个区间那么「插入意向锁」锁住的就是一个点。因而从这个角度来说插入意向锁确实是一种特殊的间隙锁。插入意向锁与间隙锁的另一个非常重要的差别是尽管「插入意向锁」也属于间隙锁但两个事务却不能在同一时间内一个拥有间隙锁另一个拥有该间隙区间内的插入意向锁(当然插入意向锁如果不在间隙锁区间内则是可以的)。 MySQL是如何加锁的
什么 SQL 语句会加行级锁? InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁所以后面的内容都是基于 InnoDB 引擎的。所以在说 MySQL是怎么加行级锁的时候其实是在说 InnoDB 引擎是怎么加行级锁的。 普通的 select 语句是不会对记录加锁的(除了串行化隔离级别)因为它属于快照读是通过 MVCC(多版本并发控制)实现的。
如果要在查询时对记录加行级锁可以使用下面这两个方式这两种查询会加锁的语句称为锁定读 //对读取的记录加共享锁(S型锁) select ... lock in share mode; //对读取的记录加独占锁(X型锁) select ... for update; 注意:上面这两条语句必须在事务中使用即在使用这两条语句的时候要加上 begin 或 start transaction 开启事务的语句。否则锁会在查询结束后立即释放,不会等到整个事务结束.
除了上面这两条锁定读语句会加行级锁之外update 和 delete 操作都会加行级锁且锁的类型都是独占锁(X型锁)。 //对操作的记录加独占锁(x型锁) update table .... where id 1; //对操作的记录加独占锁(x型锁) delete from table where id 1; 共享锁S锁满足读读共享读写互斥。独占锁X锁满足写写互斥读写互斥。 行级锁有哪些种类?
不同隔离级别下行级锁的种类是不同的。 在读已提交隔离级别下行级锁的种类只有记录锁也就是仅仅把一条记录锁上。 在可重复读隔离级别下行级锁的种类除了有记录锁还有间隙锁(目的是为了避免幻读)所以行级锁的种类主要有三类 Record Lock记录锁也就是仅仅把一条记录锁上Gap Lock间隙锁锁定一个范围但是不包含记录本身Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。 MySQL是怎么加行级锁的
行级锁加锁规则比较复杂不同的场景加锁的形式是不同的。
加锁的对象是索引加锁的基本单位是 next-key lock它是由记录锁和间隙锁组合而成的next-key lock 是前开后闭区间而间隙锁是前开后开区间。但是next-key lock 在一些场景下会退化成记录锁或间隙锁。那到底是什么场景呢?总结一句在能使用记录锁或者间隙锁就能避免幻读现象的场景下next-key lock 就会退化成记录锁或间隙锁。
唯一索引等值查询
当我们用唯一索引进行等值查询的时候查询的记录存不存在加锁的规则也会不同 当查询的记录是「存在」的在索引树上定位到这一条记录后将该记录的索引中的 next-key lock 会退化成「记录锁」。 当查询的记录是「不存在」的在索引树找到第一条大于该查询记录的记录后将该记录的索引中的next-key lock 会退化成「间隙锁」 有什么命令可以分析加了什么锁 使用 select * from performance_schema.data_locks\G; 这条语句查看事务执行SQL过程中加了什么锁。 1.记录存在的情况
为什么唯一索引等值查询并且查询记录存在的场景下该记录的索引中的 next-key lock 会退化成记录锁? 原因就是在唯一索引等值查询并且查询记录存在的场景下仅靠记录锁也能避免幻读的问题。 幻读的定义就是当一个事务前后两次查询的结果集不相同时就认为发生幻读。所以要避免幻读就是避免结果集某一条记录被其他事务删除或者有其他事务插入了一条新记录这样前后两次查询的结果集就不会出现不相同的情况。 由于主键具有唯一性所以其他事务插入 id1 的时候会因为主键冲突导致无法插入 id1 的新记录。这样事务 A 在多次査询 id1 的记录的时候不会出现前后两次查询的结果集不同也就避免了幻读的问题。由于对 id1 加了记录锁其他事务无法删除该记录这样事务 A 在多次査询 id 1 的记录的时候不会出现前后两次查询的结果集不同也就避免了幻读的问题。
2.记录不存在的情况
为什么唯一索引等值查询并且查询记录「不存在」的场景下在索引树找到第一条大于该查询记录的记录后要将该记录的索引中的 next-key lock 会退化成「间隙锁」? 原因就是在唯一索引等值查询并且查询记录不存在的场景下仅靠间隙锁就能避免幻读的问题。 比如执行 select * from user where id 2 for update; 这条语句此时事务在 id5 记录的主键索引上加的是间隙锁锁住的范围是1,5。
为什么 id5 记录上的主键索引的锁不可以是 next-key lock? 如果是 next-key lock就意味着其他事务无法删除 id5 这条记录但是这次的案例是査询 id2 的记录只要保证前后两次查询 id2 的结果集相同就能避免幻读的问题了所以即使 id5 被删除也不会有什么影响那就没必须加 next-key lock因此只需要在 id5 加间隙锁避免其他事务插入 id2 的新记录就行了。为什么不可以针对不存在的记录加记录锁? 锁是加在索引上的而这个场景下查询的记录是不存在的自然就没办法锁住这条不存在的记录。
如何确定间隙锁的范围 执行 select * from performance_schema.data_locks\G; 这条语句时如果 LOCK_MODE 是 next-key 锁或者间隙锁那么 LOCK_DATA 就表示锁的范围「右边界」然后锁范围的「左边界」是该记录的上一条记录的主键值。 唯一索引范围查询
当唯一索引进行范围查询时会对每一个扫描到的索引加 next-key 锁然后如果遇到下面这些情况会退化成记录锁或者间隙锁 情况一针对「大于等于」的范围査询因为存在等值査询的条件那么如果等值査询的记录是存在于表中那么该记录的索引中的 next-key 锁会退化成记录锁。 情况二针对「小于或者小于等于」的范围查询要看条件值的记录是否存在于表中 当条件值的记录不在表中那么不管是「小于」还是「小于等于」条件的范围查询扫描到终止范围查询的记录时该记录的索引的 next-key 锁会退化成间隙锁其他扫描到的记录都是在这些记录的索引上加 next-key 锁。当条件值的记录在表中如果是「小于」条件的范围查询扫描到终止范围查询的记录时该记录的索引的 next-key 锁会退化成间隙锁其他扫描到的记录都是在这些记录的索引上加 next-key锁如果「小于等于」条件的范围查询扫描到终止范围查询的记录时该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录都是在这些记录的索引上加 next-key 锁。 非唯一索引等值查询 当查询的记录「存在」时由于不是唯一索引所以肯定存在索引值相同的记录于是非唯一索引等值查询的过程是一个扫描的过程直到扫描到第一个不符合条件的二级索引记录就停止扫描然后在扫描的过程中对扫描到的二级索引记录加的是 next-key 锁而对于第一个不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。同时在符合查询条件的记录的主键索引上加记录锁。当查询的记录「不存在」时扫描到第一条不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录所以不会对主键索引加锁。 非唯一索引范围查询 非唯一索引范围查询索引的 next-key lock 不会退化为间隙锁和记录锁。 update没加索引会锁全表?
原因 事务的 update 语句中 where 条件没有索引列时,会触发全表扫描,就会给全表记录加上 next-key锁,那么锁就会持续很长一段时间,直到事务结束,而这期间除了 select...from 语句,其他语句都会被锁住不能执行,业务也会因此停滞. update 语句的 where 加上索引就能避免全表记录加锁了吗? 并不是,关键还得看这条语句在执行过程中,优化器最终选择的是索引扫描还是全表扫描,如果走的是全表扫描,就会对全表的记录加锁了. 避免方法
我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1开启安全更新模式
当 sql_safe_updates 设置为1时
update 语句必须满足如下条件之一才能执行成功: 使用 where并且 where 条件中必须有索引列;使用 limit;同时使用 where 和 limit此时 where 条件中可以没有索引列 delete 语句必须满足以下条件能执行成功: 同时使用 where 和 limit此时 where 条件中可以没有索引列; 如果 where 条件带上索引列但是优化器最终扫描选择的是全表而不是索引的话我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引以此避免有几率锁全表带来的隐患。
MySQL 记录锁间隙锁可以防止删除操作而导致的幻读吗 答案是可以的。在 MySQL 的可重复读隔离级别下针对当前读的语句会对索引加记录锁间隙锁这样可以避免其他事务执行增、删、改时导致幻读的问题。 有一点要注意的是在执行 update、delete、select .. for update 等具有加锁性质的语句一定要检査语句是否走了索引如果是全表扫描的话会对每一个索引加 next-key 锁相当于把整个表锁住了这是挺严重的问题。 如何避免死锁? 死锁的四个必要条件请求与保持、不可剥夺、互斥、环路等待。只要系统发生死锁这些条件必然成立但是只要破坏任意一个条件就死锁就不会成立 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后就对这个事务进行回滚于是锁就释放了另一个事务就可以继续执行了。在 InnoDB 中参数 innodb_lock_wait_timeout 是用来设置超时时间的默认值时 50 秒。
当发生超时后就出现下面这个提示: ERROR 1205(HY000):Lock wait timeout exceeded; try restarting transaction 开启主动死锁检测。主动死锁检测在发现死锁后主动回滚死锁链条中的某一个事务让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on表示开启这个逻辑默认就开启。
当检测到死锁后就会出现下面这个提示: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 上面这个两种策略是「当有死锁发生时」的避免方式。
我们可以回归业务的角度来预防死锁对订单做幂等性校验的目的是为了保证不会出现重复的订单那我们可以直接将 order _no 字段设置为唯一索引列利用它的唯一性来保证订单表不会出现重复的订单不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。
面试题
可重复读级别下这个场景会发生什么 产生死锁。原因事务A和事务B在执行完update语句后都持有范围为20,30的间隙锁而接下来的插入操作为了获取到插入意向锁都在等待对方事务的间隙锁释放于是就造成了循环等待满足了死锁的四个条件因此发生了死锁。 日志篇
预览
undo log(回滚日志)
是 Innodb 存储引擎层生成的日志实现了事务中的原子性主要用于事务回滚和 MVCC
一条记录每一次更新操作产生的 undo log 格式都有一个 roll pointer 指针和一个 trx_id 事务id通过 trx_id 可以知道该记录是被哪个事务修改的通过 roll_pointer 指针可以将这些 undo_log 串成一个链表这个链表就被称为版本链。
作用 实现事务回滚保障事务的原子性。事务处理过程中如果出现了错误或者用户执行了 ROLLBACK语句MySQL可以利用undo log中的历史数据将数据恢复到事务开始之前的状态 实现 MVCC(多版本并发控制)关键因素之一。MVCC 是通过 ReadView undo log 实现的。undo log 为每条记录保存多份历史数据MySQL在执行快照读(普通 select 语句)的时候会根据事务的 Read View 里的信息顺着 undo log 的版本链找到满足其可见性的记录。 redo log(重做日志)
是 Innodb 存储引擎层生成的日志实现了事务中的持久性主要用于掉电等故障恢复
undo log 与 redo log 的区别 redo log 记录了此次事务「修改后」的数据状态记录的是更新之后的值主要用于事务崩溃恢复保证事务的持久性。 undo log 记录了此次事务「修改前」的数据状态记录的是更新之前的值主要用于事务回滚保证事务的原子性。 redo log 什么时候刷盘
执行事务产生的 redo log 并不是直接写入磁盘而是先缓存在内存中的 redo log buffer默认16MB那么什么时候刷盘
1.MySQL正常关闭时
2.当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半
3.Innodb后台线程每隔一秒将 redo log buffer 持久化到磁盘
4.每次事务提交时都将缓存在 redo log buffer 中的 redo log 持久化到磁盘
redo log 文件写满了怎么办
默认情况下Innodb 存储引擎有一个重做日志文件组由两个redo log文件组成重做日志文件组以循环写的方式工作第一个redo log文件写满了写第二个第二个满了写第一个依次类推。我们知道redo log是为了防止 buffer pool 中脏页丢失而设计的如果脏页刷新到了磁盘对应的redo log中的记录就可以擦除了。如果redo log文件都满了此时MySQL就不能执行更新操作了即MySQL会阻塞这时会停下来将脏页刷新到磁盘将redo log中对应记录擦除然后就能正常执行更新操作了。
binlog (归档日志)
是 Server 层生成的日志主要用于数据备份和主从复制
redo log 和 binlog 有什么区别
1.适用对象不同binlog 是 MySQL 的 server 层实现的日志所有存储引擎都可以使用redo log 是 innodb 存储引擎层实现的日志。
2.文件格式不同binlog 有三种格式STATEMENT默认格式、ROW、MIXED三种格式的区别是ROW 每行数据变化的结果都会被记录STATEMENT只会记录执行的语句比如update语句ROW 更新多少行数据就会产生多少条记录会使 binlog 文件过大。STATEMENT 存在动态函数问题比如使用uuid、now函数这种随时在变的函数可能会导致主库执行结果和从库执行结果不一致的问题。ROW 不存在这种问题。MIXED 包含了上述上述两种格式根据不同情况自动选择使用。redo log 是物理日志记录某个数据页做了什么修改。
3.写入方式不同binlog 是追加写写满一个文件就创建新的文件继续写不会覆盖以前的日志而 redo log 是循环写日志空间大小是固定的全部写满就从头开始。如果不小心将整个数据库的数据都删除了使用 binlog 可以恢复而使用 redo log 不能恢复。
4.用途不同binlog 用于备份恢复主从复制redo log 用于掉电等故障恢复。
主从复制是怎么实现的
1.写入 binlog MySQL主库收到客户端提交事务请求后先写入binlog再提交事务更新本地存储数据。
2.同步 binlog 主库中的 log dump 线程负责发送binlog日志从库创建专门的I/O线程负责接收并将binlog写入从库relay log中继日志返回主库复制成功响应。
3.回访 binlog 从库会创建一个用于回放 binlog 的线程去读 relay log 中继日志然后回放 binlog 更新存储引擎中的数据最终实现主从数据一致性。
binlog 什么时候刷盘
事务执行过程中先把日志写到 binlog cache server 层的 cache事务提交的时候再把binlog cache 写到 binlog 文件。
如何保证MySQL主从一致性
redo log 影响主库的数据binlog 影响从库的数据如果二者不一致就会导致主从不一致。
当客户端执行 commit 语句或者在自动提交的情况下MySQL 内部开启一个 XA 事务分两阶段完成 XA 事务的提交
prepare阶段将 XID内部XA事务的ID写入redo log同时将redo log对应的事务状态设置成prepare然后将redo log持久化到磁盘。
commit 阶段将 XID 写入 binlog然后将 binlog 持久化到磁盘接着调用提交事务接口将redo log设置成 commit此时该状态不需要持久化到磁盘只需要写到文件系统的Page Cache中就可以因为只要binlog写成功即使redo log 还是prepare状态一样认为事务已经执行成功。
对于prepare阶段的redo log既可以提交事务也可以回滚事务这取决于能否在binlog中查找到与redo log相同的 XID如果找到了就提交事务如果没找到就回滚事务以此保证两个日志的一致性。所以两阶段提交是以binlog写成功作为事务提交成功的标识因为binlog写成功就意味着能在binlog中找到与redo log一样的XID。
总结 具体更新一条记录 UPDATE t_user SET name Lili WHERE id 1; 的流程如下 1.执行器负责具体执行会调用存储引擎的接口通过主键索引树搜索获取 id 1这一行记录 如果 id 1 这一行所在的数据页本来就在 buffer pool 中就直接返回给执行器更新如果记录不在 buffer pool将数据页从磁盘读入到 buffer pool返回记录给执行器。 2.执行器得到聚簇索引记录后会看一下更新前的记录和更新后的记录是否一样 如果一样的话就不进行后续更新流程如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层让 InnoDB 真正的执行更新记录的操作。 3.开启事务 lnnoDB 层更新记录前首先要记录相应的 undo log因为这是更新操作需要把被更新的列的旧值记下来也就是要生成一条 undo logundo log 会写入 Buffer Pool 中的 Undo 页面不过在内存修改该 Undo 页面后需要记录对应的 redo log。 4.InnoDB 层开始更新记录会先更新内存(同时标记为脏页)然后将记录写到 redo log 里面这个时候更新就算完成了。为了减少磁盘I/O不会立即将脏页写入磁盘后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术MySQL的写操作并不是立刻写到磁盘上而是先写 redo 日志然后在合适的时间再将修改的行数据写到磁盘上。 5.至此一条记录更新完了。 6.在一条更新语句执行完成后然后开始记录该语句对应的 binlog此时记录的 binlog 会被保存到binlog cache并没有刷新到硬盘上的 binlog 文件在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。 7.事务提交 prepare 阶段将 redo log 对应的事务状态设置为 prepare然后将 redo log 刷新到硬盘 commit 阶段将 binlog 刷新到磁盘接着调用引擎的提交事务接口将 redo log 状态设置为commit(将事务设置为 commit 状态后刷入到磁盘 redo log 文件) 8.至此一条更新语句执行完成。 内存篇
为什么要有 Buffer Pool
提高数据库的读写性能。
Buffer Pool 有多大
Buffer Pool 是在 MySQL 启动的时候向操作系统申请的一片连续的内存空间默认配置下 Buffer Pool 只有 128MB 。
可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小一般建议设置成可用物理内存的 60%~80%。
Buffer Pool 缓存什么
InnoDB 会把存储的数据划分为若干个「页」以页作为磁盘和内存交互的基本单位一个页的默认大小为 16KB。因此Buffer Pool同样需要按「页」来划分。
在 MySQL 启动的时候InnoDB 会为 Buffer Pool 申请一片连续的内存空间然后按照默认的 16KB 的大小划分出一个个的页 Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的之后随着程序的运行才会有磁盘上的页被缓存到 Buffer Pool 中。
所以MySQL 刚启动的时候你会观察到使用的虚拟内存空间很大而使用到的物理内存空间却很小这是因为只有这些虚拟内存被访问后操作系统才会触发缺页中断接着将虚拟地址和物理地址建立映射关系。
Buffer Pool 除了缓存「索引页」和「数据页」还包括了 undo 页插入缓存、自适应哈希索引、锁信息等等。 如何管理 Buffer Pool
Innodb 通过三种链表来管理缓存页
Free List 空闲页链表管理空闲页Flush List脏页链表管理脏页LRU List管理脏页干净页将最近且经常査询的数据缓存在其中而不常查询的数据就淘汰出去。
如何管理空闲页
Buffer Pool 是一片连续的内存空间当 MySQL 运行一段时间后这片连续的内存空间中的缓存页既有空闲的也有被使用的。
如果从磁盘读取数据时通过遍历这一片连续的内存空间来找到空闲的缓存页这样效率太低了。所以为了能够快速找到空闲的缓存页可以使用链表结构将空闲缓存页的控制块作为链表的节点这个链表叫做 Free 链表空闲链表。 Free 链表上除了有控制块还有一个头节点该头节点包含链表的头节点地址尾节点地址以及当前链表中节点的数量等信息。 Free 链表节点是一个一个的控制块而每个控制块包含着对应缓存页的地址所以相当于 Free 链表节点都对应一个空闲的缓存页。 有了Free 链表后每当需要从磁盘中加载一个页到 Buffer Pool 中时就从 Free 链表中取一个空闲的缓存页并且把该缓存页对应的控制块的信息填上然后把该缓存页对应的控制块从 Free 链表中移除。
如何管理脏页
设计 Buffer Pool 除了能提高读性能还能提高写性能也就是更新数据的时候不需要每次都要写入磁盘而是将 Buffer Pool 对应的缓存页标记为脏页然后再由后台线程将脏页写入到磁盘。
为了能快速知道哪些缓存页是脏的于是就设计出 Flush 链表它跟 Free 链表类似的链表的节点也是控制块区别在于 Flush 链表的元素都是脏页。 有了 Flush 链表后后台线程就可以遍历 Flush 链表将脏页写入到磁盘。
如何提高缓存命中率
使用 LRU(Least recently used)算法。
该算法的思路是链表头部的节点是最近使用的而链表未尾的节点是最久没被使用的。那么当空间不够了就淘汰最久没被使用的节点从而腾出空间。
简单的 LRU 算法的实现思路是这样的
当访问的页在 Buffer Pool 里就直接把该页对应的 LRU 链表节点移动到链表的头部当访问的页不在 Buffer Pool 里除了要把页放入到 LRU 链表的头部还要淘汰 LRU 链表末尾的节点
简单的 LRU 算法并没有被 MySQL 使用因为简单的 LRU 算法无法避免下面这两个问题
预读失效Buffer Pool 污染
那到底怎么才能避免呢?
MySQL 是这样做的它改进了 LRU 算法将 LRU 划分了 2 个区域old 区域 和 young 区域。young 区域在 LRU 链表的前半部分old 区域则是在后半部分如下图 old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pct 参数来设置默认是 37代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37.
划分这两个区域后预读的页就只需要加入到 old 区域的头部当页被真正访问的时候才将页插入 young 区域的头部。如果预读的页一直没有被访问就会从 old 区域移除这样就不会影响 young 区域中的热点数据。
虽然通过划分 old 区域 和 young 区域避免了预读失效带来的影响但是还有个问题无法解决那就是 Buffer Pool 污染的问题。
怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题
MySQL 是这样做的进入到 young 区域条件增加了一个停留在 old 区域的时间判断。
具体是这样做的在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间
如果后续的访问时间与第一次访问的时间在某个时间间隔内那么该缓存页就不会被从 old 区域移动到 young 区域的头部如果后续的访问时间与第一次访问的时间不在某个时间间隔内那么该缓存页移动到 young 区域的头部
这个间隔时间是由 innodb_old_blocks_time 控制的默认是 1000 ms。
也就说只有同时满足「被访问」与「在 old 区域停留时间超过1秒」两个条件才会被插入到 young区域头部这样就解决了 Buffer Pool 污染的问题。
另外MySQL 针对 young 区域其实做了一个优化为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部只有后面的 3/4 被访问了才会。
脏页什么时候会被刷入磁盘
引入了 Buffer Pool 后当修改数据时首先是修改 Bufer Pool 中数据所在的页然后将其页设置为脏页但是磁盘中还是原数据。
因此脏页需要被刷入磁盘保证缓存和磁盘数据一致但是若每次修改数据都刷入磁盘则性能会很差因此一般都会在一定时机进行批量刷盘。
可能大家担心如果在脏页还没有来得及刷入到磁盘时MySQL宕机了不就丢失数据了吗?
这个不用担心InnoDB 的更新操作采用的是 Write Ahead Log 策略即先写日志再写入磁盘通过redo log 日志让 MySQL 拥有了崩溃恢复能力。
下面几种情况会触发脏页的刷新
当 redo log 日志满了的情况下会主动触发脏页刷新到磁盘Buffer Pool空间不足时需要将一部分数据页淘汰掉如果淘汰的是脏页需要先将脏页同步到磁盘MySQL 认为空闲时后台线程会定期将适量的脏页刷入到磁盘MySQL正常关闭之前会把所有的脏页刷入到磁盘
参考https://xiaolincoding.com/mysql/
