无锡网站制作优化推广,简单的公司网站系统,教育类的网站案例,网站二次备案数据库学习#xff08;进阶#xff09; Mysql结构:连接层#xff1a;服务层#xff08;核心层#xff09;#xff1a;存储引擎层#xff1a;系统文件层#xff1a; 存储引擎#xff08;概述#xff09;:存储引擎特点#xff1a;InnoDB存储引擎#xff1a;(为并发条… 数据库学习进阶 Mysql结构:连接层服务层核心层存储引擎层系统文件层 存储引擎概述:存储引擎特点InnoDB存储引擎(为并发条件下必选)InnoDB存储引擎特点 索引索引结构Btree索引机构哈希索引Memory支持 索引分类 分为入门基础、操作、进阶底层实现原理学习、优化进行总结。 Mysql结构:
Mysql体系结构:
连接层、服务层、存储引擎层、系统文件层。
连接层
主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时服务器对其进行认证。可以通过用户名与密码认证也可以通过SSL证书进行认证。登录认证后服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是MySQL所特有的技术。
服务层核心层
根据体系结构图可以可以看到服务器主要包含
连接池由于每次建立建立需要消耗很多时间连接池的作用就是将这些连接缓存下来下次可以直接用已经建立好的连接提升服务器性能
管理工具和服务系统管理和控制工具例如备份恢复、Mysql复制、集群等
SQL接口接受用户的SQL命令并且返回用户需要查询的结果。
解析器: SQL命令传递到解析器的时候会被解析器验证和解析。作用将SQL语句分解成数据结构
优化器查询优化器SQL语句在查询之前会使用查询优化器对查询进行优化。
缓存器 查询缓存如果查询缓存有命中的查询结果查询语句就可以直接去查询缓存中取数据。
第二层服务层是MySQL的核心MySQL的核心服务层都在这一层。
SQL语句在服务层内部执行顺序查询缓存解析查询之前服务器会检查查询缓存如果能找到对应的查询直接返回缓存中的结果集——解析器解析 MySQL会解析查询并创建了一个内部数据结构——查询优化器多数情况下一条查询可以有很多种执行方式最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划——查询引擎查询执行引擎根据执行计划给出的指令调用存储引擎的接口得出结果。。以及跨存储引擎的功能都在这一层实现存储过程触发器视图等。通过下图来观察服务层的内部结构 存储引擎层
负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信通过接口屏蔽了不同存储引擎之间的差异这也就说明在第二层服务层时暂时不需要考虑引擎的差异
系统文件层
该层主要是将数据库的数据存储在文件系统之上并完成与存储引擎的交互。
Mysql内存结构:本章节不深入主要在InnoDB底层那一章中了解InnoDB的内存结构
全局内存缓冲池里面有数据缓存、索引缓存、锁信息、插入缓存等等。此外还有重做日志缓存、额外的内存池。 线程内存Master Thread、IO Thread、Purage Thread、Page Cleaner Thread。
存储引擎概述:
存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式每个表都有设置自己的存储引擎;
存储引擎是属于表的一种类型。
//存储引擎设置
CREATE TABLE 表名
(字段1 字段1类型[COMMENT 字段1注释],字段2 字段2类型[COMMENT 字段2注释],字段2 字段2类型[COMMENT 字段2注释] ) ENGINE 引擎类型 [COMMENT 表注释]
//默认引擎是INNODBSHOW ENGINES;//数据库查看存储引擎特点
存储结构特点详细见本文InnoDB引擎底层重点这一章
tablespace----segment----extent----page----row;
表空间----段----区----页----行
InnoDB存储引擎(为并发条件下必选)
INNODB是兼顾高可靠性、高性能的通用存储引擎为Mysql默认存储引擎
InnoDB存储引擎特点
1.DML操作遵循事务的ACID属性原子性、一致性、隔离性、持久性。
2.行级锁提高并发访问
3.支持外键约束保证数据完整性。
MyISAM与innodb的区别
具体的对比在此次进阶篇学习完成后作者会深入总结回归另外进行学习笔记
索引
索引index是帮助MYSQL高效获取数据的数据结构有序
优点 提高数据检索效率降低数据库成本 通过索引列对数据进行排序和分组可以降低数据排序成本降低CPU消耗。通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。 缺点 索引列需要占用额外的物理空间 并且创建索引和维护索引需要花费一定的时间 索引大大提高了查询效率但更新表时索引需要重建这降低更新表的速度。 没有索引时Mysql使用全表查询一般当索引列覆盖的数据大于全表数据的30%时会触发mysql使用全表检索 索引结构
索引是在数据库结构的第三层存储引擎层实现的
主要的索引结构主要有以下几种平时默认就是Btree索引
索引结构描述BTree索引最常见的索引类型Hash索引底层数据结构通过哈希表来实现只有精确匹配索引查询才有效不支持范围查询R-tree索引空间索引空间索引是MySAM引擎的一个特殊索引类型适用于地理空间数据类型Full-text全文索引一种通过建立倒排索引快速匹配文档的方式
索引引擎支持的索引结构
索引结构InnoDBMyISAMMemoryBTree索引支持支持支持Hash索引不支持不支持支持R-tree索引空间索引不支持支持不支持Full-text全文索引5.6版本之后支持支持不支持
Btree索引机构
//来说说树一些类型的树自己后面再整理一下放这里先埋个坑
数据库使用B-tree与Btree的原因
相对与一般二叉搜索树B树层级更少搜索效率更高
二叉搜索树BST缺点在插入数据的过程中树的节点不能保证平衡性在一些极端情况下比如连续插入有序的序列容易产生近似链表的结构搜索复杂度近似为线性o(N)
红黑树大数据量情况下层次会很深检索速度下降。
了解一下B-tree和Btree;详细可以看看这篇博客http://t.csdn.cn/3UZcZ
B-tree多路平衡查找树:
类似于普通的二查搜索树
特点
1.,所有键值分布在整个树中 2,任何关键字出现且只出现在一个节点中 3,搜索有可能在非叶子节点结束 4,在关键字全集内做一次查找性能逼近二分查找算法logn
Btree:
B树是B树的变体也是一种多路平衡查找树
与B-tree的区别在于1.所有的数据都会出现在叶子节点2.叶子节点形成一个单向链表。
而在mysql中在原有的Btree的基础上增加一个指向相邻叶子节点的链表指针形成了带有顺序指针的Btree提高区间访问性能。
Btree相对于B-tree的优点 1.Btree数据存储在叶子节点上使得内存页可以存储更多的key相同数据下Btree的深度会比B-tree小这使得Btree的搜索效率上升。 2.Btree叶子节点是相连的通过增加一个指针可以方便数据在内存连续存储便于底层的区间查找提高缓存命中性。
哈希索引Memory支持
采用一定的哈希算法将键值换算成新的hash值映射到对应的槽位上然后存在hash表中
当发生哈希冲突时可以通过链表来解决。
哈希索引特点
优点效率高于Btree索引
缺点hash索引不支持范围查询只能通过等值来查询其存储是无序的无法利用索引来完成排序操作。
索引分类
通过约束关键字控制
索引分类 含义 特点 关键字约束 主键索引 针对表中主键创建的索引 唯一默认自动创建 PRIMARPY 唯一索引 避免一个表中的某一数据列有重复值 可以多个 UNIQUE 常规索引 可以快速定位 可以多个 全文索引 全文索引查找文本中的关键词 可以多个 FULLTEXT 数据库主键与索引区别和联系
主键一定是唯一索引在创建主键时数据库管理系统会自动给主键生成一个唯一索引所以主键索引就是一个特殊的唯一索引。
唯一索引是标识索引的值唯一但在表中可以有多个唯一索引主键却只能有一个。
主键列不能为空但唯一索引可以为空
个人理解索引就像是书的目录而主键就相当于一本书的页码。
innoDB中
分类 含义 特点 聚集索引(clustered index) 数据与索引一起存储索引结构的叶子节点保存了行数据 必须有并且只能有一个 二级索引(secondary index) 将数据与索引分开存储索引结构的叶子节点关联的是对应的主键 可以存在多个 聚集聚簇索引选取规则 如果存在主键主键索引就是聚集索引。
如果不存在主键将使用唯一UNIQUE索引作为聚集索引
如果表没有主键、以及唯一索引则innoDB会自动生成一个rowid作为隐藏的聚集索引。
索引语法 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tablename(index_col_name); //UNIQUE:唯一索引 //联合索引创建CREATE INDEX index_name ON tablename(index_col_name1,index_col_name2,……);
//主键索引创建 ALTER TABLE table_name ADD PRIMARY KEY ( column ); //在建表时建立主键索引例子 CREATE TABLE testIndex( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(16) NOT NULL, PRIMARY KEY(i_testID) ); 1 2 3 4 5 6 7 8 9 10 11 12 查看索引
SHOW INDEX FROM tablename; 1 删除索引
DROP INDEX index_name ON tablename; 1 索引使用原则与失效问题重点 1.最左前缀法则
如果索引了多列联合索引就是指查询从索引的最左列开始并且不跳过索引中的列如果不从最左边列开始查询则就不会使用索引查询索引失效如果从最左列开始查询但跳过其中一列那么索引将部分失效后面字段索引失效。
例如p1、p2、p3设置了联合索引p1为最左 select * from table1 where p1‘xx’ and p2‘yy’;//按索引查询 select * from table1 where p2‘yy’ and p3‘zz’;/不满足最左前缀法则不按索引查询 select * from table1 where p1‘xx’ and p3‘zz’;//p1按照索引查询p3不按照索引查询 1 2 3 4 2.范围查询
联合索引中出现范围查询,这会使得右侧的列索引失效。这篇可以帮助理解原因http://t.csdn.cn/ydo6N 总结就是范围查询无法保证范围获取的数据在后面条件中是有序的导致索引失效
3.索引列运算
索引列运算操作将会导致索引失效(-*/!)。
4.字符串不加引号会导致索引失效原因是类型不一致导致索引失效
5.模糊查询模糊查询链接http://t.csdn.cn/QkCu8
头部模糊匹配会导致索引失效。
6.使用or导致索引失效or两侧都有索引才能生效否则都不生效。
7.函数导致索引失效
8.数据分布导致索引失效
9 !也会导致索引失效
10.当MYSQL判断使用索引比全表更慢则不使用索引。
比如在选择一个字段数据为null在一般情况绝多数情况下下mysql会判断全表会更快索引失效如果判断全表慢索引也不会失效。因此尽量避免mysql where语句中出现null的判断
比如当数据重复率过大的时候。
在适合索引查询时尽量避免索引失效。
下列几种情况是不适合创建和使用索引的
频繁更新的字段不适合建立索引 where条件中用不到的字段不适合建立索引 数据比较少的表不需要建索引 数据重复且分布比较均匀的的字段不适合建索引例如性别、真假值 参与列计算的列不适合建索引 SQL 提示 SQL 提示是优化数据库的一个重要手段简单来说就是在SQL 语句中加入一些 人为的提示来达到优化操作的目的。
USE INDEX在查询语句中表名的后面添加USE INDEX 来提供希望MySQL 去参考的索引列表
IGNORE INDEX让MySQL 忽略一个或者多个索引。
FORCE INDEX为强制MySQL 使用一个特定的索引。
select * from table1 use index(idx_p1) where p1‘xx’; select * from table1 ignore use index(idx_p1) where p1‘xx’; select * from table1 force use index(idx_p1) where p1‘xx’; 1 2 3 覆盖索引与回表查询 如果索引包含所有满足查询需要的数据的索引就称为覆盖索引(Covering Index)也就是不需要回表查询操作。
比如一个表table2里面有a,b,c,d字段a为主键b设置单列索引c,d设置联合索引
select * from table2 where b.val1;//此时需要select全部字段而b二级索引不能覆盖全部字段需要回表查询大大降低了效率 select a,c,d from table2 where c.val1;//此时虽然cd联合索引是二级索引但由于所选字段a就是索引叶子节点存储的值bc都包括在该索引中就不需要回表查询了。 1 2 回表查询
通俗的讲就是如果索引的列在 select 所需获得的列中因为在 mysql 中索引是根据索引列的值进行排序的所以索引节点中存在该列中的部分值或者根据一次索引查询就能获得记录就不需要回表如果 select 所需获得列中有大量的非索引列索引就需要到表中找到相应的列的信息这就叫回表。
因此在查询时尽量减少回表查询的情况。
前缀索引 当字段类型为字符串时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量磁盘IO影响查询的效率。此时可以将字符串的一部分前缀作为索引节约索引空间从而提高索引效率。
create index idx_xxx on tablename(字段名(n));//将前n个字符作为索引, 1 前缀长度的选择
通过索引的选择性不重复的索引值与数据总数的比值选择性越大查询效率越高
比如唯一索引其没有重复值选择性为1查询效率最大。
前缀索引的查询流程
select * from tablename where 字段名‘snsjsdas’(全部);//语法上与正常的查询语句一样 1 流程提取该字段已经设置了前缀索引的前缀部分在第二索引/辅助索引中寻找到对应的值判断是否需要回表需要就到聚集索引中查找。
单列索引与联合索引 单列索引即一个索引只包含单个列
联合索引一个索引包含多个列
补充尽量使用联合索引来保证覆盖索引可以减少回表查询提高查询效率
重复索引与冗余索引 重复索引在相同的列上按照相同的顺序创建的相同类型的索引应该避免这样创建重复索引。
冗余索引两个索引所覆盖的列有重叠
当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引
索引设计原则 1对查询频繁的字段建议创建索引。
2索引不是越多越好一个表如果有大量索引不仅占用磁盘空间而且会影响INSERTDELETEUPDATE等语句的性能。
3避免对经常更新的表进行过多的索引因为当表中数据更改的同时索引也会进行调整和更新十分消耗系统资源。
4数据量小的表建议不要创建索引数据量小时索引不仅起不到明显的优化效果对于索引结构的维护反而消耗系统资源。
5不要在重复率高的字段建立索引。比如性别字段只有 “男” 和 “女” 建索引完全起不到优化效果。
6对于批量数据获取尽量使用联合索引保证覆盖索引减少回表查询次数提高效率
6当唯一性是某字段本身的特征时尽量建立唯一索引能提高查询速度。
7对频繁进行查询where、分组 group by 或排序 order by操作的列上建立索引。
SQL优化 SQL性能分析 SQL性能分析可以获取数据库查询、插入、删除、更新次数为性能优化提供参考。
SQL执行频率
SHOW GLOBAL STATUS LIKE ‘Com_______’;//查询数据库执行频率 1
慢查询日志
记录了所有执行时间超过指定参数默认是10s的所有sql语句的日志
慢查询日志默认关闭需要手动开启开启后注意要重启数据库
show profile(了解)
show profiles;//查看一系列语句资源消耗信息 show profile from query 语句编号;//查询具体语句消耗信息 1 2 show profile 命令用于跟踪执行过的sql语句的资源消耗信息可以帮助查看sql语句的执行情况可以在做性能分析或者问题诊断的时候作为参考。
explain命令 通过explain 命令我们可以学习到该条 SQL 是如何执行的
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 //直接在SELECT之前EXPLAIN 1 2
获取字段
id:
值越大越先执行同等值就从上往下执行
select_type:
表示SELECT的类型SIMPLE简单表不使用表连接、子查询PRIMARY主查询UNION、SUBQUERYSELECT以及WHERE包含的子查询
type:表示连接类型ALL表示全表查询
possible_key:显示可能用到的索引
Key:实际所用索引NULL就是没有加索引
Key_len:索引字节数
rows:执行查询所需行数innodb中认为是一个估计值
filtered:返回结果行数占所需读取行数的比列该值越大越好
Extra:额外信息
插入数据sql优化 每次使用插入语句时都要与数据库建立连接对于大量数据进行单个插入效率太低
优化方法
insert优化 1.1批量插入
可以进行插入但插入语句要在1000行以下对于上万以上的插入采用分段批量插入。
1.2手动提交事务
1.3主键顺序插入
使用load指令进行大批量数据插入 主键优化 在innoDB中表数据都是根据主键顺序组织存放的这种存储方式的表就是索引组织表IOT
在主键乱序插入的情况下就会出现页分裂的现象。
页分裂
当页中删除记录达到MERGE——THRESHOLD默认为页的50%innoDB回寻找该页最靠近的前后的页能否进行页合并操作以此来优化空间。
页合并当删除一行记录时实际上记录并没有被物理删除只是被记录标记为删除使得它的空间可以被其他记录申明使用。
主键设计原则 1.在满足业务需求下尽量降低主键长度。
2.插入数据时尽量选择顺序插入选择使用AUTO——INCREMENT自增主键
3.尽量不要用无序的作为主键比如身份证号太长且无序UUID无序使用AUTO_INCREAMENT可以保证自动顺序添加
4.业务操作时避免对主键的修改。
ordered by 优化 在使用排序条件时尽量使用索引不是通过索引直接返回排序结果的排序叫做FileSort排序
group by 优化 采用索引多字段分组并满足最前缀法则。
limit 优化 当遇到大批量数据分页操作时操作时间长比如对于一个200万的数据在查询时只需要第100w之后的10个数据但直接用limit进行查询会浪费大量时间为了保证效率可以采用
通过创建覆盖索引减少回表查询然后加上子查询的方式进行优化就是对覆盖索引查询后的结果进行分页查询。
count优化 count()为一个聚合函数计数时如果对象不为NULL则计数1
count的基本作用是有两个
统计某个列的数据的数量 统计结果集的行数。 select count() from table_cols;//count 计数 //count()、count(字段)………… 1 2 MyISAM 引擎回把一个表总行记录在磁盘里使用count(*)时会直接返回效率高
InnoDB 引擎只能读取然后累积计数
优化方法 1.count(主键)InnoDB引擎遍历整个表把每个主键id取出并返回服务层服务层直接进行累加不需要进行null的判断
2.count(字段)
对于有not null约束的InnoDB引擎遍历整个表把每行字段取出并返回服务层服务层直接进行累加不需要进行null的判断
对于没有not null 约束的InnoDB引擎遍历整个表把每行字段取出并返回服务层然后服务层存在一个判断null的过程不为null计数加1
3.count(*)
InnoDB引擎做了优化不需要取值,服务器直接进行累加。
4.count(1)表示的是直接查询符合条件的数据库表的行数会包含值为NULL的行数count(*)是标准语法MYSQL优化更多。
InnoDB会遍历整张表但不取值服务层对于返回的每一行就放一个数字‘1’直接进行累加
效率方面count(*)count(1)count(主键)count(字段);
updata优化 InnoDB的行锁是针对索引加的锁不是针对记录加的锁如果索引失效或者没有使用索引进行updata那么行锁就会变为表锁从而影响并发性能。
在执行update时需要根据索引来进行数据更新保证并发性能需求。
视图/存储过程/触发器暂时不做重点 视图 视图是由数据库中的一个表或多个表导出的虚拟表是一种虚拟存在的表方便用户对数据的操作。
http://t.csdn.cn/kjStA详细学习链接
视图是存储在数据库中的查询的SQL语句
它主要出于两种原因安全原因视图可以隐藏一些数据例如员工信息表可以用视图只显示姓名、工龄、地址而不显示社会保险号和工资数等另一个原因是可使复杂的查询易于理解和使用。
存储过程 大多数 SQL 语句都是针对一个或多个表的单条语句。并非所有的操作都那么简单。经常会有一个完整的操作需要多条语句才能完成。
存储过程简单来说就是为以后的使用而保存的一条或多条 MySQL 语句的集合可将其视为批处理文件。虽然他们的作用不仅限于批处理。
存储过程思想上来看就是数据库SQL语言层面代码的封装和重用。
特点
封装复用可以接受、返回数据减少网络交互提升效率。
详细见http://t.csdn.cn/bsXGl
触发器 触发器是与表有关的数据库对象是一种特殊类型的存储过程主要通过事件触发来执行而不是主动调用来执行
触发器使用往往是为某张表绑定一段代码当表中相关内容发生变化增、删、改的时候系统会自动触发这段代码并执行。
注意目前mysql只支持行级触发器不支持语句触发器。
触发器作用
触发器语法:
创建 delimiter 自定义结束符号 create trigger 触发器名字 触发时间 触发事件 on 表 for each row –目前mysql只支持行级触发器不支持语句触发器 begin – 触发器内容主体每行用分号结尾 end –自定义的结束符合
delimiter ;//恢复为默认的结束符号
–查看 SHOW TRIGGERS; 删除 DROP TRIGGER[数据库名字]触发器名字;//没有指定数据库名字就会默认选当前所在数据库 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 触发时间
当 SQL 指令发生时会令行中数据发生变化而每张表中对应的行有两种状态数据操作前和操作后
before表中数据发生改变前的状态 after表中数据发生改变后的状态 触发事件
触发器是针对数据发送改变才会被触发对应的操作只有这三种
INSERT DELETE UPDATE 每个表的每个事件每次只允许一个触发器因此每个表最多支持 6 个触发器before/after insert、before/after delete、before/after update
触发器应用详细见http://t.csdn.cn/EYmVD
–创建表 create table sp_table( id int comment ‘商品编号’, name varchar(10) comment ‘商品名字’, val FLOAT comment ‘商品价格’, ) comment ‘商品单’; —创建进货表 create table jinhuo_biao ( id int comment ‘商标编号’, nums int comment ‘数量’ ) comment ‘进货表’;
–创建触发器 delimiter ## create trigger insert_trigger after insert on sp_table for each row begin update jinhuo_biao set numsnums1 where idnew.id; end## delimiter ;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 mysql show TRIGGERS; ±---------------±-------±---------±---------------------------------------------------------------------±-------±-----------------------±------------------------------------------------------------------------------------------------------------------------------------------±---------------±---------------------±---------------------±------------------- | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | ±---------------±-------±---------±---------------------------------------------------------------------±-------±-----------------------±------------------------------------------------------------------------------------------------------------------------------------------±---------------±---------------------±---------------------±------------------- | insert_trigger | INSERT | sp_table | begin update jinhuo_biao set numsnums1 where idsp_table.id; end | AFTER | 2022-03-25 15:22:35.03 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | rootlocalhost | utf8 | utf8_general_ci | latin1_swedish_ci | ±---------------±-------±---------±---------------------------------------------------------------------±-------±-----------------------±------------------------------------------------------------------------------------------------------------------------------------------±---------------±---------------------±---------------------±------------------- 1 row in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 –示例 INSERT INTO jinhuo_biao (id,nums) VALUES(1,0); INSERT INTO jinhuo_biao (id,nums) VALUES(2,0); INSERT INTO jinhuo_biao (id,nums) VALUES(3,0); INSERT INTO sp_table (id,name,val) VALUES(1,‘coke’,7.8); INSERT INTO sp_table (id,name,val) VALUES(2,‘apple’,5); INSERT INTO sp_table (id,name,val) VALUES(2,‘apple’,5); INSERT INTO sp_table (id,name,val) VALUES(2,‘apple’,5); INSERT INTO sp_table (id,name,val) VALUES(3,‘milk’,3.3);
1 2 3 4 5 6 7 8 9 10 11 结果发现触发器生效插入使得jinhuo_biao的nums获得更新。
对于数据在操作前后都有一个状态old/new,操作前状态为old,操作后为new;
old与new不是所有触发器都有
触发器类型 new old INSERT 没有 有 UPDATE 没有 有 DELETE 没有 有 锁重点 锁是计算机协调多个进程或者线程并发访问某一资源的机制。
锁的主要作用:管理共享资源的并发访问用于实现事务的隔离性
分类
图片来源http://t.csdn.cn/zZEOv
MySQL中的锁按锁粒度分
全局锁、表级锁、行级锁。
全局锁 全局锁就是对整个数据库实例加锁。
典型使用场景做全局的逻辑备份对所有表进行锁定。
语法
flush tables with read lock;–让整个库处于只读状态的时候
解锁unlock tables;
表级锁 每次操作该锁都会锁住整张表锁粒度越大发生锁冲突概率越大并发度越低。
表级锁分类表锁元数据锁mata data lock,MDL意向锁.
表锁 表锁分类表共享读锁表独占写锁。
语法
lock tables 表名 read;//加了读锁后除了当前进程其它进程不能读. lock tables 表名 write;//加了写锁后除了当前进程其它进程不能写. 1 2 InnoDB只支持用行锁如果一定要加表锁就进行以下操作 –用 LOCK TABLES对InnoDB表加锁时要注意要将AUTOCOMMIT设为0否则MySQL不会给表加锁 SET AUTOCOMMIT0; LOCK TABLES t1 WRITE, t2 READ, …; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;//解锁
1 2 3 4 5 6 7 8 元数据锁 MySQL5.5之后版本加入了MDL
MDL加锁过程为系统自动控制其主要作用是维护元数据的数据一致性在表中有活动事务是不能对元数据进行写操作避免DML与DDL冲突保证读写正确性。
元数据其实就像是表示表的数据结构比如如果一个查询正在遍历一个表中的数据而执行期间另一个线程对这个表结构做了变更删了一列那么查询线程拿到的结果跟表结构对不上会报错这就需要MDL。
当对一个表做增删改查操作的时候加MDL读锁
当要对表做结构变更操作的时候加MDL写锁。
读锁之间不互斥共享因此可以有多个线程同时对一张表增删改查 读写锁之间、写锁之间是互斥的用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行 1 2 注意事务中的MDL锁在语句执行开始时申请但是语句结束后并不会马上释放而会等到整个事务提交后再释放这也符合事务的ACID中的原子性
意向锁 先看行锁再看这个
意向锁是一个表级锁。
意向共享锁IS与共享锁S兼容与排它锁X互斥。
意向排他锁IX与共享锁S以及排他锁X都互斥
意向锁不会互斥。
意向锁主要作用解决innoDB中行锁与表锁的冲突问题。
比如一个事务在需要获取资源锁定的时候如果遇到自己需要的资源已经被排他锁占用的时候该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁那么就在表上面添加一个意向共享锁
\ 共享锁S 排他锁X 意向共享锁IS 意向排他锁IX 共享锁S 兼容 冲突 兼容 冲突 排他锁X 冲突 冲突 冲突 冲突 意向共享锁IS 兼容 冲突 兼容 兼容 意向排他锁IX 冲突 冲突 兼容 冲突 一个事务请求的锁模式与当前的锁兼容InnoDB就将请求的锁授予该事务反之如果两者不兼容该事务就要等待锁释放
一个表的某行数据共享、意向共享锁可以有多个但是意向排他或排他锁只能有一个。
个人理解写只能有一个读可以有多个有排他锁的线程开启后不让别人读但如果设置的是意向排他锁就允许意向共享锁的来读。
行级锁 行级锁每次操作都会锁住对应的行数据
InnoDB的数据主要是基于索引组织的行锁是通过对索引上的索引项加锁来实现的不是对记录加的锁
主要分类
行锁、间隙锁、临建锁
行锁/记录锁Record Lock单个记录上的锁
间隙锁Gap Lock间隙锁锁定一个范围但不包含记录本身I就是只锁间隙
临建锁next-key lock 间隙锁 行锁对一个范围的数据以及间隙加锁
行锁Record Lock InnoDB 有两种类型的行锁
1.共享锁S共享锁又称为读锁简称S锁顾名思义共享锁就是多个事务对于同一数据可以共享一把锁都能访问到数据但是只能读不能修改。
2.排他锁X排他锁又称为写锁简称X锁顾名思义排他锁就是不能与其他所并存如一个事务获取了一个数据行的排他锁其他事务就不能再获取该行的其他锁包括共享锁和排他锁但是获取排他锁的事务是可以对数据就行读取和修改。
语法
共享锁S SELECT * FROM table_name WHERE … LOCK IN SHARE MODE 排他锁X SELECT * FROM table_name WHERE … FOR UPDATE 1 2 3 4 SQL 行锁类型 说明 INSERT 排他锁 自动加锁 UPDATE 排他锁 自动加锁 DELETE 排他锁 自动加锁 SELECT 不加 不加 SELECT……LOCK IN SHARE MODE 共享锁 需要手动区添加 SELECT……FOR UPDATE 排他锁 需要手动添加 注意
1.默认情况下在RR事务隔离级别下InnoDB使用next-key锁(临建锁)来进行搜索和索引扫描以防止幻读
2.对于普通SELECT语句InnoDB不会加任何锁
1.在针对唯一索引进行检索时对已存在的记录进行等值匹配将自动转化为行锁。
2.InnoDB行锁是针对索引加的锁如果不通过索引检索数据InnoDB将会对表中所有记录加锁此时行锁就会变成表锁。
间隙锁Gap Lock/临建锁next-key lock 对于键值在条件范围内但并不存在的记录叫做“间隙GAP)”InnoDB也会对这个“间隙”加锁这种锁机制就是所谓的间隙锁Gap Lock。
临建锁next-key lock 间隙锁 行锁对一个范围的数据以及间隙加锁
SELECT * FROM table_name WHERE 范围条件 FOR UPDATE 1 InnoDB使用间隙锁的目的
1.防止幻读以满足相关隔离级别的要求。对于某个范围比如a50要是不使用间隙锁如果其他事务插入了这个范围内数据的任何记录(比如插入一条a55的数据)那么这个事务如果再次执行上述语句就会发生幻读 2.为了满足其恢复和复制的需要。
间隙锁使用情况
1.当我们用范围条件而不是相等条件检索数据并请求共享或排他锁时InnoDB会给符合条件的已有数据记录的索引项加间隙锁
2.使用相等条件请求给一个不存在的记录加锁InnoDB也会使用间隙锁
3.间隙锁可以共存
间隙锁缺点
1.当锁定一个范围键值之后即使某些不存在的键值也会被无辜的锁定而造成在锁定的时候无法插入锁定键值范围内的任何数据,这将影响使用性能。
2.数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列但是也会被锁定因为间隙锁锁定的是一个范围而不是具体的索引键
3.使用索引定位数据的时候如果使用的索引键一样但访问的数据行不同的时候索引只是过滤条件的一部分一样会被锁定。
补充
间隙锁锁定原理
执行update/delete/select for update会产生间隙锁
innodb会根据更新或查询的索引条件寻找非唯一索引的间隙锁的上下区间
如果插入数据在区间之内则无法插入因为被间隙锁锁定如果插入数据在区间的边界值此时需要根据主键来判断锁定范围(因为除了锁二级索引也会锁聚集索引) 比如一个二级索引数据 a,a,c,d,d.对应主键为15,42,6。如果一个事务update范围为c,则加a-d的临建锁若此时另一个事务插入a只要插入的主键‘5’a的最大主键值则可以自由插入主键‘5’全部锁定无法插入若此时另一个事务插入d取d的最小主键值’2’如果插入主键‘2’,就可以插入其它都不能插入。
1 2 3 4 5 6 7 如何合理使用InnoDB的行级锁
1.在实际应用开发中尤其是并发插入比较多的应用要尽量优化业务逻辑尽量使用相等条件来访问更新数据避免使用范围条件
2.尽可能让所有的数据检索都通过索引来完成从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定
3.合理设计索引让InnoDB在索引键上面加锁的时候尽可能准确**尽可能的缩小锁定范围**避免造成不必要的锁定而影响其他事务的执行
4.尽量控制事务的大小减少锁定的资源量和锁定时间长度
5.在业务环境允许的情况下尽量使用较低级别的事务隔离以减少MySQL因为实现事务隔离级别所带来的附加成本。 1 2 3 4 5 6 7 8 9 表级锁与行级锁对比
表级锁开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高并发度最低 行级锁开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度也最高
从锁的角度来说
表级锁更适合于以查询为主只有少量按索引条件更新数据的应用如Web应用
而行级锁则更适合于有大量按索引条件并发更新少量不同数据同时又有并发查询的应用如一些在线事务处理。 1 2 3 4 5 6 7 8 死锁问题 InnoDB检测到死锁报错Deadlock found when trying to get lock;
死锁是指两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象。若无外力作用事务都将无法推进下去。
例如一个用户A 访问表A锁住了表A然后又访问表B另一个用户B 访问表B锁住了表B然后企图访问表A这时用户A由于用户B已经锁住表B它必须等待用户B释放表B才能继续同样用户B要等用户A释放表A才能继续这就死锁就产生了。
一般而言行级锁虽然在并发处理上有很大优势但容易发生死锁问题。
死锁产生的四个必要条件重点 1**.互斥条件**指进程对所分配到的资源进行排它性使用即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源则请求者只能等待直至占有资源的进程结束该事务才释放。
理解比如行锁中的排他锁
2.请求和保持条件指进程已经保持至少一个资源但又提出了新的资源请求而请求的资源已被其它进程占有此时请求进程阻塞但又对自己已获得的其它资源保持不放
理解比如两个并发事务互相申请资源A要1-3-5-7-4B要4-7-5-3-1A运行到比如要申请7的位置7被B锁B运行到申请到5的位置5被A锁。
3.不可抢占条件指进程已获得的资源在未使用完之前不能被剥夺只能在使用完时由自己释放
理解接上个例子A被B锁了要等B释放自己才能运行结束去释放B被A锁了要等A释放自己才能继续运行结束后才释放。
4.环路等待条件指在发生死锁时必然存在一个进程——资源的环形链即进程集合{P0P1P2···Pn}中的P0正在等待一个P1占用的资源P1正在等待P2占用的资源……Pn正在等待已被P0占用的资源。
理解上接例子此时A与B就形成了环路等待条件直接死锁了
死锁原因 该小节例子部分copy和参考的http://t.csdn.cn/uILFu部分感觉解释比较直接
死锁产生原因
进程推进顺序不当、不可抢占性资源竞争产生死锁竞争可消耗性资源引起死锁。
死锁一般是事务相互等待对方资源最后形成环路造成的。下面简单讲下造成相互等待最后形成环路的例子。
假设我们有一张消息表msg里面有3个字段。假设id是主键token是非唯一索引message没有索引。
1.不同表相同记录行锁冲突
这种情况很好理解事务A和事务B操作两张表但出现循环等待锁情况。
2相同表记录行锁冲突
这种情况比较常见之前遇到两个job在执行数据批量更新时jobA处理的的id列表为[1,2,3,4]而job处理的id列表为[8,9,10,4,2]这样就造成了死锁。
3不同索引锁冲突
这种情况比较隐晦事务A在执行时除了在二级索引加锁外还会在聚簇索引上加锁在聚簇索引上加锁的顺序是[1,4,2,3,5]而事务B执行时只在聚簇索引上加锁加锁顺序是[1,2,3,4,5]这样就造成了死锁的可能性。
4 gap锁冲突
innodb在RR级别下间隙锁会产生死锁比较隐晦。(这块原作者的例子看了好久是在没看出来哪里死锁了只能自己试了)
回顾间隙锁加锁原理
innodb会根据更新或查询的索引条件寻找非唯一索引的间隙锁的上下区间
如果插入数据在区间之内则无法插入因为被间隙锁锁定如果插入数据在区间的边界值此时需要根据主键来判断锁定范围(因为除了锁二级索引也会锁聚集索引) 比如一个二级索引数据 a,a,c,d,d.对应主键为15,42,6。如果一个事务update范围为c,则加a-d的临建锁若此时另一个事务插入a只要插入的主键‘5’a的最大主键值则可以自由插入主键‘5’全部锁定无法插入若此时另一个事务插入d取d的最小主键值’2’如果插入主键‘2’,就可以插入其它都不能插入。 1 2 3 4 例子与上面原理所讲例子一致
CREATE TABLE test_dath1( id int NOT NULL, name varchar(15) NOT NULL COMMENT’NAME’ ) ENGINEInnoDB DEFAULT CHARSETutf8; ALTER TABLE test_dath1 ADD PRIMARY KEY ( id ); CREATE INDEX index_name ON test_dath1(name); insert into test_dath1 values (1,‘a’),(2,‘d’),(4,‘c’),(5,‘a’),(6,‘d’);
–线程1执行事务A start transaction; –1执行顺序 update test_dath1 set name‘c’ where name‘c’;–锁死了a-d插a锁主键5.插b锁主键2 –3 insert into test_dath1 values(3,‘e’); --此时d之后的已经被B的更新确定的排他锁锁住 commit;
–线程2执行事务B start transaction; –2 update test_dath1 set name‘d’ where name‘d’;–锁死了d以及d之后无穷大 –4 insert into test_dath1 values (8,‘a’);–此时A的插入锁住了主键a能插入大于5的的部分 commit;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
根据结果可以看到具体原因看代码注释以及间隙锁原理发生了死锁问题
此外还有系统本身BUG、满足死锁必要条件的使用情况导致死锁本文不在探讨。
补充
间隙锁解决幻读问题例子
如下图所示事务A在第一次查询时得到1条记录在第二次执行相同查询时却得到两条记录。从事务A角度上看是见鬼了这就是幻读RC级别下尽管加了行锁但还是避免不了幻读。
innodb的RR隔离级别可以避免幻读发生怎么实现当然需要借助于锁了
为了解决幻读问题innodb引入了gap锁。
在事务A执行update msg set message‘订单’ where token‘asd’;
innodb首先会和RC级别一样给索引上的记录添加上X锁此外还在非唯一索引’asd’与相邻两个索引的区间加上锁。
这样当事务B在执行insert into msg values (null,‘asd’,’hello’); commit;时会首先检查这个区间是否被锁上如果被锁上则不能立即执行需要等待该gap锁被释放。这样就能避免幻读问题。
死锁问题解决办法 1.InnoDB中对于死锁问题
1.最直接的方法是通过超时即当两个事务互相等待时当一个等待时间超过设置的某一阈值时其中一个事务进行回滚解死锁是数据库对事务的保护机制,一旦发生死锁,MySQL会选择相对小的事务(undo较少的)进行回滚。另一个等待的事务就能继续进行。
2.除了超时机制当前数据库还都普遍采用wait-for graph等待图的方式来进行死锁检测。较之超时的解决方案这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。
wait-for graph要求数据库保存以下两种信息 锁的信息链表 事务等待链表 通过上述链表可以构造出一张图而在这个图中若存在回路就代表存在死锁因此资源间相互发生等待。这是一种较为主动的死锁检测机制在每个事务请求锁并发生等待时都会判断是否存在回路若存在则有死锁通常来说InnoDB存储引擎选择回滚undo量最小的事务。 1 2 3 4 5 6 7 8 2.人员分析死锁问题待填坑
3.尽量预防和减少死锁方法很多大致了解一下。
预防主要就是看打破死锁产生的必要条件其中互斥条件不能破坏不然会导致不可重复读的并发问题主要就是打破后三个条件。https://blog.csdn.net/weixin_47005624/article/details/105668043这篇介绍了部分关于三个条件打破预防死锁的方法例子。
此外还有
1.尽量避免并发的执行涉及到修改数据的语句。
2.要求每一个事务一次就将所有要使用到的数据全部加锁否则就不允许执行。
3.预先规定一个加锁顺序所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。
4.每个事务的执行时间不可太长对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入应该在事务之前得到输入然后快速执行事务。
5.使用尽可能低的隔离级别。
6.数据存储空间离散法。该方法是指采用各种手段将逻辑上在一个表中的数据分散的若干离散的空间上去以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表或者按照不同的用户群两种方法实现。
7.编写应用程序让进程持有锁的时间尽可能短这样其它进程就不必花太长的时间等待锁被释放
摘自原文部分https://blog.csdn.net/qq_34107571/article/details/78001309
避免死锁等待完善
使用银行家算法、安全性算法。
死锁的检测与解除 1.抢占资源将一个一个或多个进程中抢占足够数量的资源分配死锁进程以解除死锁状态。
2.终止进程终止系统中的一个或多个死锁进程直至打破循环环路使系统从死锁状态解脱出来
不同隔离级别下加锁情况 回顾一下隔离机制 以MYSQL数据库为例事务隔离级别
隔离级别 脏读 不可重复读 幻读 Read uncommotted(读未提交) n n n Read committed(读提交) y n n Repeatable read(可重复读取)mysql默认 y y n Serializable(可序化) y y y y:可以解决n不能解决
隔离级别查看
SELECT tx_isolation; 1 第一种隔离级别Read uncommitted(读未提交) 如果一个事务已经开始写数据则另外一个事务不允许同时进行写操作但允许其他事务读此行数据该隔离级别可以通过“排他写锁”但是不排斥读线程实现。
特点一写多读写的时候其他事务也能读
却可能出现脏读也就是说事务B读取到了事务A未提交的数据出现不可重复读问题B同时读取了A提交前后某一行数据数据不一致也避免不了幻读问题在此就不举例了。
第二种隔离级别Read committed(读提交) 如果是一个读事务(线程)则允许其他事务读写如果是写事务将会禁止其他事务访问该行数据该隔离级别避免了脏读但是可能出现不可重复读。事务A事先读取了数据事务B紧接着更新了数据并提交了事务而事务A再次读取该数据时数据已经发生了改变。
特点一个事务写这个数据时其它事务不可读只能读写后的。
只解决了脏读问题
第三种隔离级别Repeatable read(可重复读取) 可重复读取是指在一个事务内多次读同一个数据在这个事务还没结束时其他事务不能访问该数据(包括了读写)这样就可以在同一个事务内两次读到的数据是一样的因此称为是可重复读隔离级别读取数据的事务将会禁止写事务(但允许读事务)写事务则禁止任何其他事务(包括了读写)这样避免了不可重复读和脏读但是有时可能会出现幻读即一个事务在进行读某一行数据时另一个事务对这行数据所在表进行了数据条数的增减导致查询结果不一致。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。
特点一个事务执行结束前同一行数据只能在一个事务内读写其他事务不能操作。
解决了脏读、不可重复读但是还会出现幻读
第四种隔离级别Serializable(可序化/串行化) 提供严格的事务隔离它要求事务序列化执行事务只能一个接着一个地执行但不能并发执行如果仅仅通过“行级锁”是无法实现序列化的必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别同时代价也是最高的性能很低一般很少使用在该级别下事务顺序执行。
特点只能一个事务来读写这个表其他事务没有读写权限。
不仅可以避免脏读、不可重复读还避免了幻读
补充以上四种级别在实际效率方面由高到低也就是说安全级别高也会导致效率的下降。
一般我们常用的就是第三种RRMysql默认级别和第二种RC隔离级别
不同隔离级别下加锁情况 锁是加载索引上的。对于不同的隔离级别不同的列情况加锁情况都各不不同。
读未提交级别RU 读操作不会加锁写操作会加排他锁但该写操作与读操作兼容故会导致脏读。
MVCC不发生效果
无论是否使用索引、是否添加锁只对操作数据添加行锁record lock不会产生间隙锁
读已提交级别RC 读操作不会加锁写操作会添加排他锁
MVCC不发生效果
无论是否使用索引、是否添加锁只对操作数据添加行锁record lock不会产生间隙锁
可重复级别RR 读操作不会加锁写操作会添加排他锁有间隙锁
MVCC 会在事务开始第一次查询时生成 Read View
加锁规律
加锁默认为 next-key lock(临键锁)
非唯一索引会向排序方向一直匹配直到不满足条件为止。
可串行化级别 读操作加读锁写操作加写锁读写操作互斥但有间隙锁
1用到主键索引和唯一索引会对操作数据添加 Record Lock。
2普通索引会对操作数据以及间隙添加 Next-key Lock。
3未使用索引会对所有数据以及两边间隙添加 Next-key Lock
加锁机制 乐观并发控制(乐观锁)和悲观并发控制悲观锁是并发控制采用的主要技术手段
悲观锁Pessimistic Concurrency ControlPCC 悲观锁是一种并发控制方法对于数据被外界修改保持保守悲观态度在处理数据的过程中会将这个数据设置为锁定状态。
具体流程 在对任意记录进行修改前先尝试为该记录加上排他锁exclusive locking
如果加锁失败说明该记录正在被修改那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
如果成功加锁那么就可以对记录做修改事务完成后就会解锁了。 1 2 3 4 5 6 悲观锁的优点和不足
悲观锁实际上是采取了“先取锁再访问”的策略为数据的处理安全提供了保证但是在效率方面由于额外的加锁机制产生了额外的开销并且增加了死锁的机会。并且降低了并发性当一个事物所以一行数据的时候其他事物必须等待该事务提交之后才能操作这行数据。
乐观锁Optimistic Concurrency ControlOCC 相对悲观锁而言乐观锁假设认为数据一般情况下不会造成冲突所以在数据进行提交更新的时候才会正式对数据的冲突与否进行检测如果发现冲突了则让返回用户错误的信息让用户决定如何去做。
乐观锁并不会使用数据库提供的锁机制一般的实现乐观锁的方式就是记录数据版本。
为数据增加的一个版本标识。当读取数据时将版本标识的值一同读出数据每更新一次同时对版本标识进行更新。当我们提交更新的时候判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对如果数据库表当前版本号与第一次取出来的版本标识值相等则予以更新否则认为是过期数据。 1 2 3 乐观锁的优点与不足
乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的因此尽可能直接做下去直到提交的时候才去锁定所以不会产生任何锁和死锁。但如果直接简单这么做还是有可能会遇到不可预期的结果例如两个事务都读取了数据库的某一行经过修改以后写回数据库这时就遇到了问题。 1 InnoDB引擎底层重点 InnoDB索引结构与Myisam索引结构区别 InnoDB引擎采用BTree结构来作为索引结构
InnoDB索引组织表的索引类型分为聚簇索引和非聚簇索引
主键索引聚簇索引结构特点整张表的数据其实就是存储在聚簇索引中的故聚簇索引就是表。其叶子节点存储的是主键的值和整行数据
如果一个表没有主键索引MYSQL也会自动生成一个聚簇索引具体看本文聚簇索引生成规则
非聚簇索引结构特点叶子节点存储的是主键的值。
MyISAM引擎也采用BTree结构来作为索引结构
MyISAM堆组织表使用的是非聚簇索引、索引和文件分开随机存储只能缓存索引其叶子节点存储的是数据的地址
逻辑存储结构 InnoDB的存储文件有两个后缀名分别是 .frm和 .idb其中 .frm是表的定义文件 .idb是表的数据文件。
在存储引擎概述中已经稍微介绍了一下存储结构现在详细探讨一下。
表空间table space表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层所有的数据都存放在表空间中。分为系统表空间、独占表空间、通用表空间、 临时表空间、Undo 表空间
段表空间是由各个段组成的常见的段有数据段、索引段、回滚段等
InnoDB的数据段就是B树的叶子节点索引段就是B树的非叶子节点段就是用来管多个Extent区。
区一个段由很多个区组成每个区默认大小为1M64个连续的页为了保证区中页的连续性区扩展时InnoDB 存储引擎会一次性从磁盘申请4 ~ 5个区。
页为了高效管理物理空间对簇进一步细分就得到了页。簇是由连续的页Page组成的空间一个簇中有 64 个连续的页
InnoDB 也有页的概念也可以称为块每个页默认 16KB一个表空间最多拥有 2^32 个页即最大存储64TB数据
行InnoDB 存储引擎是面向行的row-oriented也就是说数据的存放按行进行存放。
InnoDB架构
内存架构 Buffer Pool(缓冲池简称BP) 为什么使用缓冲池
InnoDB存储引擎是基于磁盘存储的并将其中的记录按照页(page)的方式进行管理。但是磁盘IO读取速度与CPU速度差距过大目前磁盘io操作平均在100次/秒所以基于磁盘的数据库系统需要通过使用缓冲池位于内存来提高数据库的整体性能。
缓冲池中缓存的数据页类型有索引页、数据页、undo页、更改缓冲区(change buffer)、自适应HASH索引(adaptive hash index)、锁信息(lock info)、数据字典信息等。 Buffer Pool 主要有这三种page管理free page 空闲page未被使用clean page被使用page数据没有被修改过dirty page脏页被使用page数据被修改过页中数据和磁盘的数据产生了不一致针对上述三种page类型InnoDB通过三种链表结构来维护和管理
free list 表示空闲缓冲区管理free page
flush list表示需要刷新到磁盘的缓冲区管理dirty page内部page按修改时间 排序。脏页即存在于flush链表也在LRU链表中但是两种互不影响LRU链表负 责管理page的可用性和释放而flush链表负责管理脏页的刷盘操作。
lru list表示正在使用的缓冲区管理clean page和dirty page缓冲区以
midpoint为基点前面链表称为new列表区存放经常访问的数据占63%后 面的链表称为old列表区存放使用较少数据占37% 参考原文链接https://blog.csdn.net/qq_36194388/article/details/109325528
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 为了提高大容量读取操作的效率缓冲池以页(page默认大小16k)为单位。为了提高缓存管理的效率缓冲池被实现为链接的页链表(linked list of pages)使用LRU算法的变体很少使用的数据在缓存中老化(aged out)。
LRU算法
普通LRU末尾淘汰法新数据从链表头部加入释放空间时从末尾淘汰
力扣与牛客上有该类型算法题可以去练习一下这是LRU的基本原理做了就能理解
改进LRU链表分为new和old两个部分加入元素时并不是从表头插入而是从中间下面链接文章可以看看
http://t.csdn.cn/BORwK
Change Buffer 写缓冲区简称CB。在进行DML操作时如果BP没有其相应的Page数据 并不会立刻将磁盘页加载到缓冲池而是在CB记录缓冲变更等未来数据被读取时再将数 据合并恢复到BP中。
ChangeBuffer占用BufferPool空间默认占25%最大允许占50% 调整参数innodb_change_buffer_max_size; 1 2 写缓冲区仅适用于非唯一普通索引页
因为索引设置唯一性在进行修改时InnoDB必须要做唯一性校验因此必须查询磁盘做一次IO操作。会直接将记录查询到BufferPool中然后在缓冲池修改不会在ChangeBuffer操作
Log Buffer(日志缓存区) 用来保存写入到磁盘中的log日志数据定期刷新到磁盘上面默认大小为16MB。日志缓冲区满时会自动将其刷新到磁盘
控制日志刷新频率默认值1 0每1妙刷新磁盘和日志文件可能会丢失最多1秒的数据1事务提交立即写日志文件和刷盘数据不会丢失但io操作频繁2事务提交后立刻写日志文件每隔1秒进行刷盘。 innodb_flush_log_at_trx_commit 调整Log Buffer的大小 nnodb_log_buffer_size 1 2 3 4 增加日志缓存区大小可以节省磁盘io。
4.自适应哈希Adaptive Hash index:优化对Buffer Pool 数据的查询。
InnoDB存储引擎会监 控对表索引的查找如果观察到建立哈希索引可以带来速度的提升则建立哈希索引InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。所以这是自动开启不需要优化
磁盘架构 主要包含系统表空间、通用表空间、独立表空间、临时表空间、撤销undo表空间、双写缓冲区、重做日志redo log
系统表空间系统表空间是一个共享的表空间因为它是被多个表共享的。
1.InnoDB系统表空间包含InnoDB数据字典(元数据以及相关对象)、double write buffer(脏页落盘时使用的双写缓冲区)、change buffer(修改缓冲区)、undo logs的存储区域。
2.系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据
独占表空间每个表的文件表空间含单个InnoDB表的数据和索引 并存储在文件系统中自己的数据文件中。
通用表空间类似于系统表空间可以存储多个表的数据的一直共享表空间支持Antelope和Barracuda文件格式。
undo表空间undo表空间包含undo log撤销记录的集合其中包含通过主键索引事务撤销更改的最小信息
双写缓冲区innodb将数据页从BP刷新到磁盘前会先把数据页写到双写缓冲区里便于系统异常时恢复数据。
redo log重做日志保证事务持久性重做日志主要分为重做日志缓冲在内存里、重做日志文件在磁盘里
后台线程 后台线程分为Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。
后台线程的主要作用 从磁盘刷新内存池中的数据保证缓冲池中缓存的数据是最新的
将缓冲池中已修改的数据文件刷新到磁盘
保证数据库异常时InnoDB能恢复到正常运行状态
Master Thread:核心后台线程 核心后台线程负责将缓冲池中的数据异步刷新到磁盘保证数据的一致性包括脏页的刷新、合并插入缓冲Insert Buffer、undo页的回收等等。
IO Thread InnoDB 中使用了大量的AIOAsync IO来处理写IO请求以此提高数据库的性能。IO Thread负责这些IO请求的回调处理。
Purge Thread 用于回收已经分配且不再需要的undo页
Page Cleaner Thread 作用是把原本Master Thread中的脏页刷新操作独立到单独的线程中减轻Master Thread的工作以及对于用户查询线程的阻塞提高存储引擎的性能
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 事务原理 再回顾一下事务 关于事务基础篇作者整理了一部分概念现在重新回顾凝炼一下。
回顾一下事务的四大特性ACID原子性(Atomicity一致性(Consistent)隔离性Isolation持久性Durable。
**原子性**事务是一个原子操作单位。只有使事务中所有的数据库操作都执行成功才算整个事务成功。
事务中任何一个SQL语句执行失败已经执行成功的SQL必须撤销数据库状态应该退回到事务前的状态。
**一致性**指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后数据库的完整性约束没有被破坏。
即事务在开始前与结束后数据都要保持一致性的状态如果事务中某个动作失败了系统可以自动撤销事务 — 返回初始化的状态
**事务隔离性**要求每个读写事务的对象对其他事务的操作对象能相互分离即该事务提交前对其他事务都不可见通常使用锁来实现。
**持久性**事务一旦提交其结果就是永久性的。即使发生宕机等故障也能恢复。
事务的实现原理
事务的原子性是通过undo log日志系统归档日志来实现的
事务的持久性是通过redo log日志系统重做日志来实现的
事务的隔离性是通过读写锁MVCC来实现的
事务的一致性是通过原子性、持久性、隔离性来实现的
接下来学习三个特性如何保证实现的。
undo log回滚日志 回滚日志用于记录数据被修改前岁的信息。
作用提供回滚和MVCC多并发控制
undo log属于逻辑日志每当执行一个记录时其就会产生一个逻辑相反的记录比如delete某个记录其就会产生对应insert记录当执行rollback时就可以借助undo loh中的逻辑记录读取到相应内容并进行回滚
undo log销毁在事务执行时产生在事务结束后也不会立刻销毁因为在快照读中也需要 (即该 undo log 需要提供 MVCC 机制)所以不能随便删除只有当数据库所使用的快照中不涉及该日志记录对应的 undo log 才会被清理 (purge) 线程删除
undo log存储采用段的方式进行管理
redo log重做日志 记录事务提交时数据页的物理修改用来保证事务的持久性。
该日志由重做日志缓存在内存中以及重做日志文件在磁盘中
作用在事务提交之后把所有修改信息存到日志文件中在刷新脏页到磁盘发生故障时进行数据恢复使用保证事务的持久性。
MVCC重点 基本概念 MVCC(Multi-Version Concurrency Control)即多版本并发控制
基本概念MVCC是一种并发控制的方法一般在数据库管理系统中实现对数据库的并发访问在编程语言中实现事务内存
MVCC的作用为了提高数据库的并发性能即读不加锁读写不冲突在读多写少的系统中读写不冲突是非常重要的极大地提高了系统的并发性能
快照读 快照读简单的selsect不加锁就是快照读即不加锁的非阻塞读
快照读的前提是隔离级别不是未提交读和串行化级别因为未提交读总是读取最新的数据行而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁。
快照读读取的是记录数据的可见版本有可能是记录数据的可见版本也可能是历史数据
四种隔离级别下快照读的位置
RU:会退化为当前读
RC:每次select都会生成一个快照读
RR:开启后事务后的第一个select语句为快照读的地方。MVCCreadiew更新机制
S:会退化为当前读
当前读 当前读读取是记录的最新版本读取是要保证其它并发事务不能修改当前记录会对读取的记录加锁。
当前读 就是加了锁的读 select … lock in share mode select … for update 1 2 3 实现原理重要 记录中的隐藏字段Invisible ColumnMySQL 8.0.23 版本之后添加 InnoDB中每行数据记录都有隐藏列隐藏列中包含了本行数据的事务id、指向undo log的指针若没有主键则还有一个隐藏主键DB_ROW_ID行标识隐藏单增ID没有主键时主动生成(6字节)
6字节的事务IDDB_TRX_ID 表示最近修改数据的事务id
7字节的回滚指针DB_ROLL_PTR指向这条记录的上一个版本配合undo log
隐藏字段图示
图片来源黑马学习视频
undo log 版本链 每行数据的隐藏列中包含了指向undo log的指针而每条undo log也会指向更早版本的undo log从而形成一条版本链。
图片来源黑马学习视频
根据图片例子可以看到记录中的每条undo log也会指向其更早版本的undo log 4----3-------2---------1
ReadView读视图 是快照读SQL执行时MVCC提取数据的依据记录并维护系统当前活跃的事务为提交的id。
包含了四个核心字段
字段 含义 m_ids 当前活跃的事务ID集合 min_trx_id 最小活跃事务ID max_trx_id 预分配事务id是自增的是最大事务id1 creator_trx_id ReadVeiw创建的事务id 版本链数据访问规则
trx_id表示当前事务id
规则1trx_idcreator_trx_id:
可以访问该版本说明此时数据读取是当前事务更改的自己读自己改
规则2trx_idmin_trx_id:
可以访问该版本说明该数据已经提交是被其它已经commit的事务修改了
规则3trx_idmax_trx_id:
不可以访问该版本该事务是在readeiw生成后才开启的
规则4min_trx_idtrx_idmax_trx_id时
如果trx_id不在m_dis中则可以访问说明该事务已经提交了
例子比如事务123456按序列依次开启此时min_trx_id1max_trx_id7其中5先好了此时m_dis为(1,2,3,4,6);数据a最近被5修改了事务3来快照读这个数据时此时trx_id3根据要求会先读5的虽然其它三个条件都不满足
不同的隔离级别readiew生成也不同
RC在事务中每一次执行快照读的时候生成readview
RR只在事务第一次执行时执行快照读并生成readiew后续就复用该readview;
