MySQL——基础知识

目录1. 事务的四个特性 ACID1A atomicity原子性2Cconsistency一致性3I isolation隔离性4Ddurability持久性AUTOCOMMIT2. 隔离性可能存在的问题3. 数据库提供的四种隔离级别Read Uncommitted(读未提交)Read Committed(读已提交)Repeatable Read(可重复读)【MySQL默认隔离级别】Serializable(串行)4. 数据表类型/MySQL的数据库引擎有什么5. MyIASM和innoDB的区别应用在什么场景6. 锁的类型1行级锁和表级锁2读写锁 / 共享锁和排它锁3意向锁是一种表级锁4 封锁协议5MySQL的隐式与显示锁定6InnoDB有三种行锁的算法7. MVCC版本号基本原理增删改查8. 关系数据库设计理论9. ER图实体的三种联系表示出现多次的关系联系的多向性表示子类10 什么是存储过程用什么来调用11 什么是触发器触发器的作用12 存储过程与触发器的区别1. 事务的四个特性 ACID1A atomicity原子性一个事务必须视为一个不可分割的最小工作单元。整个事务中的所有操作要么全部提交成功要么全部失败回滚。回滚可以用回滚日志来实现回滚日志记录着事务所执行的修改操作在回滚时反向执行这些修改操作即可。2Cconsistency一致性在一致性状态下所有事务对一个数据的读取结果都是相同的。数据库总数从一个一致性的状态转换到另一个一致性的状态。3I isolation隔离性一个事务所做的修改在最终提交以前对其他事务是不可见的。并发访问数据库事务之间的隔离很重要。4Ddurability持久性一旦事务提交则其所做的修改就会永久保存到数据库中。此时即使系统崩溃修改的数据也不会丢失。ACID的关系参考CyC2018只有满足一致性事务的执行结果才是正确的。在无并发的情况下事务串行执行隔离性一定能够满足。此时只要能满足原子性就一定能满足一致性。在并发的情况下多个事务并行执行事务不仅要满足原子性还需要满足隔离性才能满足一致性。事务满足持久化是为了能应对数据库崩溃的情况。AUTOCOMMITMySQL 默认采用自动提交模式。也就是说如果不显式使用START TRANSACTION语句来开始一个事务那么每个查询都会被当做一个事务自动提交。使用START TRANSACTION自动提交将保持禁用状态直到你使用COMMIT或ROLLBACK结束事务。之后自动提交还是会开启。set session autocommit0;可以修改会话系统变量或全局系统变量只对当前实例有效如果MySQL服务重启的话这些设置就会丢失如果要永久生效就必须在配置文件中修改系统变量。2. 隔离性可能存在的问题当多个线程都开启事务来操作数据库中的数据时数据库系统要进行隔离操作以保证各个线程获取数据的准确性。不考虑事务的隔离性会产生几种问题脏读、不可重复读、幻读01脏读是指一个事务处理过程里读取了另一个未提交的事务中的数据然后使用了这个数据例事务A开始事务B开始事务B修改数据X未提交事务A读取数据X事务B回滚。事务A读取到的数据X属于脏读。02不可重复读不可重复读是指在一个事务内多次读取同一个数据在这个事务还没有结束 另一个事务修改了该数据并提交那么第一个事务两次读取的数据可能不一样因此称为不可重复读即同一个事务中原始数据读取不可重复。注不可重复读和脏读的区别脏读是某一个事务读取另一个事务未提交的脏数据; 不可重复读则是读取前一事务提交后的数据例事务A开始事务A读取数据X事务B修改数据X并提交事务A再次读取数据X此时X与上次读取的不同。03幻读当事务不是独立执行时发生的一种现象例如第一个事务对一个表中的数据进行修改这种数据涉及到表中的全部数据行同时第二个事务也对这个表数据进行修改这个修改是对表中新增/删除一条数据那么操作第一个事务的用户发现表中的数据还没有修改的数据行就好像发生了幻觉一样这就是发生了幻读。注幻读和不可重复读都读取另一条已经提交的事务所不同的是不可重复读查询的都是同一数据项而幻读针对的是增加或减少了数据。例事务A开始事务A读取count(*) n事务B插入m条数据并提交事务A再次读取count(*) nm 与上次读取的不同。3. 数据库提供的四种隔离级别Read Uncommitted(读未提交)一个事务可以读取另一个未提交事务的数据最低级别什么情况都可能会发生。包括脏读、不可重复度、幻读Read Committed(读已提交)一个事务要等另一个事务提交后才能读取数据。可避免脏读的发生但还存在不可重复读和幻读。Repeatable Read(可重复读)【MySQL默认隔离级别】就是在开始读取数据事务开启时不再允许修改操作可避免脏读、不可重复读的发生但还存在幻读Serializable(串行)是最高的事务隔离级别在该级别下事务串行化顺序执行可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下比较耗数据库性能一般不使用。隔离级别脏读不可重复读幻读读未提交√√√读已提交×√√可重复读××√串行×××注意级别越高执行效率就越低; 隔离级别的设置只对当前链接有效对JDBC操作数据库来说一个Connection对象相当于一个链接只对该Connection对象设置的隔离级别只对该connection对象有效与其它链接connection对象无关。oracle数据库中只支持seralizable(串行化)级别和Read committed();默认的是Read committed级别4. 数据表类型/MySQL的数据库引擎有什么MyIASM、InnoDB、HEAP、ISAM、MERGE、DBD以及Gemeni(一般只知道前两者即可)5. MyIASM和innoDB的区别应用在什么场景InooDB支持事务而MyISAM不支持事务InnoDB支持行级锁而MyISAM支持表级锁InnoDB支持MVCC而MyISAM不支持InnoDB支持外键而MyISAM不支持InnoDB不支持全文索引而MyISAM支持InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器 myisam 支持InnoDB表支持多种行格式 myisam 不支持InnoDB是索引组织表 myisam 是堆表MyISAM适合(1)做很多count 的计算(2)插入不频繁查询非常频繁(3)没有事务。InnoDB适合(1)可靠性要求比较高或者要求事务(2)表更新和查询都相当的频繁并且行锁定的机会比较大的情况。为什么MyISAM会比Innodb 的查询速度快。INNODB在做SELECT的时候要维护的东西比MYISAM引擎多很多1数据块INNODB要缓存MYISAM只缓存索引块 这中间还有换进换出的减少2innodb寻址要映射到块再到行MYISAM 记录的直接是文件的OFFSET定位比INNODB要快3INNODB还需要维护MVCC一致虽然你的场景没有但他还是需要去检查和维护6. 锁的类型1行级锁和表级锁在选择封锁粒度时需要在锁开销和并发程度之间做一个权衡。并发程度锁粒度越小发生锁争用的可能越小系统并发程度越高。适合写操作或修改操作较多的场景系统开销锁粒度越小开销约大。锁的各种操作包括获取锁、释放锁、以及检查锁状态都会增加系统开销。2读写锁 / 共享锁和排它锁排它锁Exclusive简写为 X 锁又称写锁。 可以对对象A进行读取和更新加锁期间其他事务不能访问对象A共享锁Shared简写为 S 锁又称读锁。可以对对象A进行读取但不能更新期间其他事务可以对A加读锁。3意向锁是一种表级锁Intention Locks 解决这种情况在存在行级锁和表级锁的情况下事务 T 想要对表 A 加 写 锁就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁那么就需要对表 A 的每一行都检测一次这是非常耗时的。引入意向锁之后 如果事务T想对表A 加表级锁需要先检查意向锁再检查行级锁。一个事务在获得某个数据行对象的读锁之前必须先获得表的 意向读锁 或者更强的锁一个事务在获得某个数据行对象的写锁之前必须先获得表的 意向写锁 。-X 写锁IX 意向写锁S 读锁IS 意向读锁X写锁××××IX意向写锁×√×√S读锁××√√IS意向读锁×√√√任意 IS/IX 锁之间都是兼容的因为它们只是表示想要对表加锁而不是真正加锁S 锁只与 S 锁和 IS 锁兼容也就是说事务 T 想要对数据行加 S 锁其它事务可以已经获得对表或者表中的行的 S 锁。4 封锁协议一级封锁协议事务 T 要修改数据 A 时必须加 X 锁直到 T 结束才释放锁。可以解决丢失修改问题因为不能同时有两个事务对同一个数据进行修改那么事务的修改就不会被覆盖。二级封锁协议在一级的基础上要求读取数据 A 时必须加 S 锁读取完马上释放 S 锁。读取完不一定事务已经结束可以解决脏读问题因为如果一个事务在对数据 A 进行修改根据 1 级封锁协议会加 X 锁那么就不能再加 S 锁了也就是不会读入数据。三级封锁协议在二级的基础上要求读取数据 A 时必须加 S 锁直到事务结束了才能释放 S 锁。可以解决不可重复读的问题因为读 A 时其它事务不能对 A 加 X 锁从而避免了在读的期间数据发生改变。5MySQL的隐式与显示锁定innoDB默认隐式锁定会根据隔离级别在需要的时候自动加锁并且所有的锁都是在同一时刻被释放也可以使用命令SELECT ... LOCK In SHARE MODE; SELECT ... FOR UPDATE;6InnoDB有三种行锁的算法Record Locks锁定一个记录上的索引而不是记录本身。如果表没有设置索引InnoDB 会自动在主键上创建隐藏的聚簇索引因此 Record Locks 依然可以使用。Gap Locks锁定索引之间的间隙但是不包含索引本身。例如当一个事务执行以下语句其它事务就不能在 t.c 中插入 15。SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;Next-Key Locks它是 Record Locks 和 Gap Locks 的结合不仅锁定一个记录上的索引也锁定索引之间的间隙。例如一个索引包含以下值10, 11, 13, and 20那么就需要锁定以下区间(-∞, 10] (10, 11] (11, 13] (13, 20] (20, ∞)7. MVCC多版本并发控制 Multi-Version Concurrency Control 是乐观锁的一种实现方式通过版本号控制MVCC 是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式用于实现读已提交和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行无需使用 MVCC。原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。可串行化隔离级别需要对所有读取的行都加锁单纯使用 MVCC 无法实现。版本号系统版本号是一个递增的数字每开始一个新的事务系统版本号就会自动递增。事务版本号事务开始时的系统版本号。MVCC 在每行记录后面都保存着两个隐藏的列用来存储两个版本号创建版本号指示创建一个数据行的快照时的系统版本号创建时的系统版本号即创建时的事务版本号删除版本号如果该快照的删除版本号上一次操作的事务版本号大于当前事务版本号表示该快照有效否则表示该快照已经被删除了。基本原理MVCC的实现通过保存数据在某个时间点的快照快照编号来实现的。这意味着一个事务无论运行多长时间在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同在同一个时刻不同事务看到的相同表里的数据可能是不同的。一个事务无法看到版本号比自己的事务版本号高的数据每行数据都存在一个版本每次数据更新时都更新该版本。修改时Copy出当前版本随意修改各个事务之间无干扰。保存时比较版本号如果成功commit则覆盖原记录失败则放弃copyrollback增删改查1. SELECT多个事务必须读取到同一个数据行的快照并且这个快照是距离现在最近的一个有效快照。但是也有例外如果有一个事务正在修改该数据行那么它可以读取事务本身所做的修改而不用和其它事务的读取结果一致。事务T执行select操作所要读取的数据行快照的创建版本号必须小于等于 T 的版本号因为如果大于 T 的版本号那么表示该数据行快照是其它事务的最新修改因此不能去读取它。所要读取的数据行快照的删除版本号必须是未定义或者大于 T 的版本号因为如果小于等于 T 的版本号那么表示该数据行快照是已经被删除的不应该去读取它。如果大于T的版本号说明这条记录在T开始之后有另一个事务删除了它此时T是可以读取的。2. INSERT将当前事务版本号作为数据行快照的创建版本号。3. DELETE将当前事务版本号作为数据行快照的删除版本号。4. UPDATE将当前事务版本号作为更新前的数据行快照的删除版本号并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。例testmvcc(id,name)insert into testmvcc values(1,test); 记录的创建版本号即当前事务版本号update table set name new_value where id1; 先标记旧的那行记录为已删除并且删除版本号是事务版本号; 然后插入一行新的记录, 新记录创建版本号是当前事务版本号delete from table where id1;把事务版本号作为删除版本号8. 关系数据库设计理论三个范式1属性不可分数据库表中的字段都是单一属性的不可再分(保持数据的原子性)2非主属性完全依赖于主属性主键每行数据能够被主键唯一区分3不包含其他表中的非主键信息即不存在冗余信息。确保数据表中的每一列数据都和主键直接相关在满足第二范式的基础上在实体中不存在其他实体中的非主键属性传递函数依赖于主键属性确保数据表中的每一列数据都和主键直接相关而不能间接相关(表中字段[非主键]不存在对主键的传递依赖)9. ER图Entity-Relationship有三个组成部分实体、属性、联系。用来进行关系型数据库系统的概念设计。实体的三种联系包含一对一一对多多对多三种。如果 A 到 B 是一对多关系那么画个带箭头的线段指向 B如果是一对一画两个带箭头的线段如果是多对多画两个不带箭头的线段。下图的 Course 和 Student 是一对多的关系。表示出现多次的关系一个实体在联系出现几次就要用几条线连接。下图表示一个课程的先修关系先修关系出现两个 Course 实体第一个是先修课程后一个是后修课程因此需要用两条线来表示这种关系。联系的多向性虽然老师可以开设多门课并且可以教授多名学生但是对于特定的学生和课程只有一个老师教授这就构成了一个三元联系。表示子类用一个三角形和两条线来连接类和子类与子类有关的属性和联系都连到子类上而与父类和子类都有关的连到父类上。10 什么是存储过程用什么来调用答存储过程是一个预编译的SQL 语句。优点是允许模块化的设计就是说只需创建一次以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL 使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象exec来调用存储过程。11 什么是触发器触发器的作用答触发器是一中特殊的存储过程主要是通过事件来触发而被执行的。它可以强化约束来维护数据的完整性和一致性可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如某表上的触发器上包含对另一个表的数据操作而该操作又会导致该表触发器被触发。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时SQLSERVER就会自动执行触发器所定义的SQL语句从而确保对数据的处理必须符合这些SQL语句所定义的规则。12 存储过程与触发器的区别答触发器与存储过程非常相似触发器也是SQL语句集两者唯一的区别是触发器不能用EXECUTE语句调用而是在用户执行Transact-SQL语句时自动触发激活执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器所以可以用它来强制实施复杂的业务规则以确保数据的完整性。触发器不同于存储过程触发器主要是通过事件执行触发而被执行的而存储过程可以通过存储过程名称名字而直接调用。