数据最重要的特性是一致性ACID中的C是目的AID是手段和完整性其次还有安全性。。完整性实体主码完整性参照外码完整性用户自定义的完整性一致性先删除从表才能删除主表事务安全性用户身份鉴别--用户权限控制---视图技术、加密存储一、视图数据的安全性视图有什么用呢1保证数据的安全性角度一视图对应数据库模式中的外模式对于不同的应用程序数据库提供不同的外模式这些应用程序只能访问外模式提供给他的部分数据而无法查看数据库中的完整数据保证了数据的安全性。角度二比如全公司的员工信息都存储在同一张表中但是人事部只能查看人事部的视图财务部只能查看财务部的视图都不能查看公司的整个员工信息表保证了数据的安全性。此外视图另一个重要的作用是2提供更加丰富的数据组织形式二、事务先别看下面的理论因为看了也不会用。先从实际开发切入。两个问题① java每次操作数据库都需要用事务的方式吗如果不是的话操作什么样的数据的情况下才用到事务 ② java实现事务的方式都有哪些① 不是所有情况下都要用到事务。只有当一组操作必须要么都执行要么都不执行的时候。两个场景“当实际的业务中需要将一条数据同时存放到两张表中并且要求两张表中的数据同步时。如果其中一张表的操作失败那么就需要回滚事务”“金融行业的软件开发严格重视事务处理。例如常见的转账操作一方的账户金额减少对应的是另一方的账户金额增加这个过程需要使用到事务机制以保证转账操作的正确性”② 两种实现方式一种是jdbc实现事务另一种是spring框架实现事务。最初是用jdbc这个包里的一些API去实现事务的叫commit()还记得不但是jdbc太复杂后来我们有了mybatis以后就不用jdbc了改用spring提供的API配合mybatis去使用。2.1 事务什么是事务一个数据库操作的序列小到两行sql语句大到几个sql程序。事务的定义方式显示定义START TRANSACTION ...... COMMIT/ROLLBACK隐式定义我们的DMBSmysql和SqlServer会默认划分并开启事务2.2 ACID原则AID是手段、数据的一致性C才是目的。1.原子性事务中包含的一系列数据库操作要么都做要么都不做2.一致性事务执行前后数据保持一致。A给B转100块B得到100块A减少100块事务执行前后金钱的总额不变。一致性状态数据库只包含成功事务提交的结果。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态不一致状态发生故障但已经写进数据库一部分了3.持久性记住两点提交。出现故障时事物一旦提交持久化到数据库事务没有提交恢复到原状不可逆。一旦持久化到数据库就不可逆了。4.隔离性其实就是并发 A给B转钱的同时C也给B转钱这两个事务互不影响如何实现ACID原则的原子性undolog历史版本。事务一旦出错就回滚持久性redolog。重做这里还有待填坑隔离性锁或者MVCC一致性原子性持久性隔离性共同保证了数据的一致性3种事务并发带来的破坏事务隔离性的现象1. 丢失修改2. 不可重复读和幻读哪来的哪去了怎么变了3. 读脏数据解决方法和java锁一样也是分为悲观锁和乐观锁两类悲观锁共享锁排它锁商用的DBMS基本上都默认用悲观锁但是MySQL的默认隔离级别是用MVCC实现的乐观锁时间戳MVCC乐观控制法这里可能有个疑问为什么java级别的版本控制就维护一个版本字段就可以了MySQL级别的版本控制这么复杂根本原因在于MySQL涉及到“提交和未提交的问题”这也是为什么MySQL叫做“多版本控制”而java级别的乐观锁只需要控制一个版本就可以了。1.排它锁写锁和共享锁读锁注意事务T加了写锁以后事务T自己可以写当然也可以读但是其他事务不能读。另外根据根据锁粒度的不同又被分为表级锁(table-level locking)和行级锁(row-level locking)。InnoDB 不光支持表级锁还支持行级锁默认为行级锁。行级锁的粒度更小仅对相关的记录上锁即可对一行或者多行记录加锁那么以上的解决方法都是把并行变串行是隔离的最高等级——可串行化。2. 其他等级低的隔离是用多版本控制MVCC乐观锁实现的中高级面试常问的MVCC实现原理是什么 被大佬讲明白了_哔哩哔哩_bilibiliMVCC多版本并发控制对一个数据存储多个版本也就是时间快照。保证事务T只能读开启本事务前提交的数据和当前事务修改的数据。实现MVCC的三大要素记录中/元祖中的三个隐藏字段DB_TRX_ID 上一次对这个记录进行修改的事务的idDB_ROLL_ID 回滚指针。记录上一条历史记录在undolog中的地址DB_ROW_ID 隐藏主键就是版本号修改一次版本号1。undolog数据的历史版本READ_VIEW事务启动时生成的一个快照trx_list: READ_VIEW生成时刻正在活跃的事务id列表up_limit_id : trx_list中最小的idlow_limit_id :READ_VIEW生成时刻尚未分配的下一个事务ID这么多东西怎么组合起来用呢 比如事务1现在要读student表里的一行记录事务1先去查这条记录的DB_TRX_ID字段看一下上一次修改过这个数据的事务是几比如说是事务3那么事务1现在就要判断事务3是已经提交了还是怎样怎样这样才能确定自己到底能不能读这条数据。如何判断呢去和READ_VIEW的3个字段对比。如果这个版本的数据不可见那么再尝试上一个版本的数据这不就用上undolog了吗举个例子看一下undolog生成的记录链2. 假设又有一个id为2的事务将上面的记录中的name字段由“zhangsan”改为“lisi”age字段不变图错了3.又有一个事务3将age改为32现在还有一个READ_VIEW我们没有涉及到。通过MVCC的处理流程我们综合介绍一下。注意事务的id按照事务开启的顺序依次递增便于事务开启时间前后的判断。当事务2进行快照读的时候要为其生成一个READ_VIEW此时事务4已经提交了所以处于活跃状态的事务是1,2,3其中最小的事务id是1如果有下一个事务那么将为其分配id5。所以READ_VIEW3个字段trx_list:[1,2,3]up_limit_id :1low_limit_id :5那么事务2能读到事务4提交的数据吗要拿从记录隐藏字段里读到的上一次修改数据的事务id和READ_VIEW中的记录作比较规则如下首先比较上一次修改数据的事务DB_TRX_ID 事务2进行快照读的时候活跃的最小IDup_limit_id 如果DB_TRX_ID up_limit_id就说明修改这个数据的事务在事务2进行快照读的时候已经不活跃了意思是已经提交了那么事务2可以放心读/改这个数据over。如果DB_TRX_ID up_limit_id我们则无法判断修改这个数据的事务是否已经提交进入下一轮判断DB_TRX_ID low_limit_id? 代表修改这个数据的事务在事务2生成READ_VIEW之后才出现的那么肯定不能读/改over。如果DB_TRX_ID low_limit_id进入下一轮判断判断DB_TRX_ID 是否在trx_list活跃列表里如果在说明事务2进行快照读的时候修改这个数据的事务还没有提交呢那么无法去读/改over。如果不在再结合前两轮的判断这个事务不在[0, up_limit_id ]的区间里也不在[ up_limit_id, ]这个活跃列表里注意这个列表不是连续区间哦也不在[low_limit_id , ∞] 里那么它只能是一个[up_limit_idlow_limit_id ]之间的一个在事务2生成READ_VIEW的时候已经提交的事务可以读/改。继续回到上面的例子DB_TRX_ID4不在活跃事务中说明事务4在READ_VIEW生成前就已经提交了所以事务2能够看到事务4修改的结果。上面我们讲的是“读已提交”这个隔离级别。我们知道MVCC可以实现“读已提交”和“可重复读”两个隔离级别对于“读已提交”来说事务2能够看到事务4修改的结果但是对于“可重复读”来说事务2不应该看到。以上说的都是事务2能够读/改事务4动过的数据那么判断能改以后MCVV怎么控制改呢插入呢删除呢3. 四个隔离等级实际上隔离有4个等级等级越高安全性越强但是效率越低。mysql4种隔离级别都支持但是mysql默认使用可重复读级别也就是第二高的隔离级别相当于在效率和安全上取了折中。隔离级别脏读不可重复读UPDATE幻读DELETE/INSERT丢失修改实现方式读未提交read uncommitted√√√所有的读操作都不加锁读到的数据都是最新的数据性能最好。所有的写操作加排它锁写完释放读已提交read committed√√MVCC可重复读repeatable read√MVCC可串行化serializable排它锁共享锁。但是不是写完就释放排它锁而是事务结束才释放不然会有脏读问题。读锁也是不是读完就释放而是事务结束再释放不然会有不可重复读幻读的问题。其实把并发的事务完全变成串行执行。PS: 之前我们把不可重复读等价为幻读哪来的哪去了怎么变了但是这里不可重复读狭义地理解为“怎么变了”也就是单指Update命令幻读理解为“哪来的哪去了”单指“DELETE和INSERT命令”。四种隔离等级都能解决“丢失修改”的问题四个隔离级别的共同点都是“事务T1写的时候事务T2不能写了”这是最低要求也是为什么四个隔离级别都能解决“丢失修改”问题。1可串行化排它锁、共享锁都用上该隔离等级实现的是“表锁定”而不是“行锁定”。以为要想避免幻读中“增加一行”“删除一行”的现象单单锁定行是不够的要锁定整个表貌似行锁锁定一个范围也能实现比如线程1锁的是where id 300这个范围线程2就无法插入id299了如果插入的是id301也不影响线程1呀2可重复读MVCC乐观控制MySql的默认隔离级别指一个事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的。在读提交的基础上用MVCC的快照机制进行行锁定不允许T1读的同时T2去UPDATE但是允许insert和delete因为MVCC机制无法阻止INSERT和DELETE所以可以避免不可重复读但避免不了幻读。两种隔离级别的不同之处仅在于生成READ_VIEW的时机不同解释可重复读RR更好理解。在RR隔离级别下事务在第一次快照读的时候创建一个READ_VIEW以后进行快照读的时候使用的依然是这个READ_VIEW这也就保证了事务第一次快照读能够读到的数据后面读到的是相同的数据保证了数据的一致性。而RC读到的永远是最新的数据3读已提交MVCC乐观控制读已提交的内容肯定不会出现脏读。但是事务T1读的同时居然T2可以UPDATE、INSETT、DELETE因为MVCC允许事务每一次读的时候都重新生成快照当然会出现不可重复读和幻读了4读未提交事务T1还未提交的时候T2居然可以读当然什么问题都会出现4. 活锁和死锁避免先来先服务操作系统有4种避免死锁的方法并不适用于数据库数据库直接不去避免死锁而是出现了死锁后诊断、解除。自古以来解除死锁注意区别于死锁避免的“先释放再分配”方式只有一个办法双方必须有一方妥协让出自己的资源。2.3 创建事务例子模板-- mysql默认开启事务自动提交现在我们把它关掉,变成手动处理事务 SET autocommit 0; -- 事务开启 START TRANSACTION SQL语句 SQL语句 ...... -- 提交持久化到数据库中成功 COMMIT -- 回滚恢复到事务开始前的状态失败 ROLLBACK -- 事务结束别忘了开启mysql的自动提交 SET autocommit 1模拟一个转账的例子这一段代码如何运行建议听一下视频的最后一分钟29、测试事务实现转账_哔哩哔哩_bilibili-- 转账的例子 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci USE shop CREATE TABLE account( id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, money DECIMAL(9,2) NOT NULL )ENGINEINNODB DEFAULT CHARSETutf8 INSERT INTO account(name,money) VALUES(A,2000.00),(B,10000.00) -- 模拟A给B转账事务 SET autocommit 0; START TRANSACTION; UPDATE account SET moneymoney-500 WHERE nameA; -- A减500 UPDATE account SET moneymoney500 WHERE nameB; -- B加500 COMMIT ROLLBACK SET autocommit 1三、索引INDEXmysql官方定义索引是帮助mysql高效获取数据的数据结构。所以索引是一种数据结构一种树形的数据结构也正是借助这种数据结构索引才能如此之快。如果不用索引那就是从头到尾遍历时间复杂度为O(n)用了B树作为数据结构虽然B数的时间复杂度是O(logn)但这是我们找索引的过程相当于查字典查到记录在第一页后直接翻到那一页查记录的时间复杂度是O(1)。索引作用极大加快查询速度。用于用于大数据量的查找百万条、千万条数据小数据量意义不大分类精确匹配主键索引值唯一UNIQUE 索引 (值唯一常规索引值不要求唯一模糊匹配全文索引3.1 创建索引的3种方式-- 方式一创建表的时候就指定主码、unique、普通索引index(key) CREATE TABLE tb_test( .... .... PRIMARY KEY(id), UNIQUE(name) ) -- 方式二创建完表了又想加索引 ALTER TABLE tb_test ADD UNIQUE(name) -- 方式三创建完表了又想加索引普通索引为例这种方法如何加UNIQUE索引还没研究过哈 -- CREATE INDEX 索引名 ON 表名字段名 CREATE INDEX index_tb_test_name ON tb_user(name)3.2 百万数据索引测试向app_user表中插入100万条用户数据。不用索引的情况下检索一个用户用时1秒左右如果一个网站并发检索10个用户就要10秒这可太慢了用EXPLAIN查看检索了991749条数据后才检索到看了两篇博客都说是全表从上到下扫描建立索引后从100万条用户数据中检索一个用户只需要0.001秒而且只检索了一条数据就检索到了是不是和查字典一样3.3 建立索引的原则正向超过300万条数据就加索引小数据量的表不要加索引作用不大主键索引unique唯一索引这是INNODB默认会加的对经常查询的字段加索引经常用于WHERE条件查询的字段加索引经常出现在Group by, order by ,distinct后面的字段索引列长度不应过大原因索引列太长B树每个非叶子节点能包含的索引数就变少了会导致树变高IO次数变多。应该加在哪一句话加在①大表 ②经常查询 ③且不怎么变动的字段上。索引不是越多越好存储空间“之前就有人反馈说他们公司的某个表实际内容的大小才 10G, 而索引大小却有 30G这是多么巨大的成本所以千万不要觉得索引建得越多越好”耗时不要对经常变动的数据加索引 因为要频繁维护索引也耗时3.4 索引的数据结构一共有4种1B树索引2B树索引3哈希索引4全文索引myisam支持innodb安装查件才能支持但工程上默认只有哈希索引和B树索引两种所以才会有面试题问你哈希索引和B树索引的区别而完全不提另外两个索引类型。1. 被淘汰的哈希索引这里建议再去看看数据结构里对哈希表的讲解。哈希表就是一个数组这个数组里存储的是地址。注意MySQL表一行行的数据在磁盘当中依然是连续存储的只不过他们的地址被“零零散散”地填充到了哈希表中。首先主键--哈希值---数组下标然后把这行MySQL记录的地址填到哈希表里时间复杂度也是O1。为什么被淘汰了呢缺点1哈希碰撞/哈希冲突。如果冲突了3次那就已经访问了3次I/O了而B树最多也就4层需要访问4次I/O而已缺点2因为不能进行范围查询这是最主要的缺点会进行全表扫描。比如有如下SQL语句SELECT * FROM student_table WHERE id300B树可以通过指针实现连续读磁盘但是哈希索引数据存储是不连续的根本不支持范围查询会进行全表扫描。2. B索引MySQL中所有的数据都被存储在page里由page进行管理而B树的一个节点也是一个page。一个page的大小是16KB如果一行数据的大小是1K那么一个page里最多可以存储16行数据这就是B树的存储方式把数据也存到节点里去但是只存储键值很小哇一个page可以存储1170条索引比存32条数据划算多了吧这就是B树比B数还要“矮胖”的原因。假如我们存了这样一张大表CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;每一行记录可以抽象成这样这是以叶子结点为例的一个page而page和page之间通过B树的双向链表相连。 好处有二① B树的范围查询只需要对链表进行遍历即可 ②局部性原理访问一个地址后有可能访问周围的B树非叶子结点存储索引键值和指向下一层的指针叶子结点存储键值和每一行记录。当然把数据也存在叶子结点上这是聚簇索引的做法。3. 自适应hash索引结构自适应哈希索引是Innodb引擎的一个特殊功能当它注意到某些索引值被使用的非常频繁时会在内存中基于B-Tree所有之上再创建一个哈希索引这就让B-Tree索引也具有哈希索引的一些优点比如快速哈希查找。这是一个完全自动的内部行为用户无法控制或配置3.5 面试题innodb的聚簇索引非聚簇索引和myIsam的非聚簇索引的区别聚簇索引把数据和索引放在一起索引的叶子结点是mysql数据库的每一行记录就是聚簇索引。innodb就是这么干的把数据和索引都放在一个文件里。聚簇索引只是一个概念默认是主键索引如果没有主键默认是第一个UNIQUE索引如果没有UNIQUEinnodb会自己生成一个隐式的聚簇索引GEN_CLUST_INDEX特点数据访问更快 因为索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快。聚簇索引对于主键的排序查找和范围查找速度非常快。按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库可以从更少的数据块中提取数据节省了大量的IO操作一个表只能有一个聚簇索引。很好理解啊因为聚簇索引把数据也一起存了你搞两个聚簇索引是想把数据存两遍吗缺点插入速度严重依赖于插入顺序 按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键。更新主键的代价很高 因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。非聚簇索引把数据和索引分开存储索引的叶子结点是数据的地址先查主键索引树在叶子结点获得数据的地址然后再拿着地址找数据回表所以效率慢innodb是聚簇索引和非聚簇索引配合着使用的。因为前面说了聚簇索引只能有一个那么我在这个SQL表上针对其他字段建的索引只能是非聚簇索引了。而数据都存在聚簇索引的叶子节点上呀所以当我们触发非聚簇索引时最终还是得触发聚簇索引。举个例子假如Student这个表有主键stuId那么innodb根据前面说的选择原则就会选主键索引作为聚簇索引当我们需要频繁查学生的专业字段也给专业字段create了一个索引这个索引就是非聚簇索引。当我们“select ... from student where 专业计算机科学与技术 ”的时候就先触发了“专业”这个非聚簇索引非聚簇索引的叶子节点存储着主键找到主键再根据主键回表触发聚簇索引找到叶子结点上存的学生数据。3.6 索引失效的情况模糊匹配“%你好”或者“_你好”的匹配字符出现在最前面。“你好%”这种情况下依然可以走索引在字段上做了SUMAVG等函数运算联合索引最左前缀匹配失效!NOT LIKE , NOT IN使用OR时OR两边的字段必须都有索引才行。这也是为什么要少用OR的原因四、用户管理为什么要给用户设权呢防止员工删库跑路。让某一部分活的员工只能查看某一部分表而且只有SELECT的权限没有DELETE的权限。对用户的管理无非是创建用户、给用户授予什么样的权限、删除用户。本质上其实是在对mysql自带的mysql数据库下的user表进行增删改查。可以看出root用户默认都是Y也就是上面都能干。1. 手动2. sql语句数据库中用户的权限一般都手动管理如上图但是如果服务器上没有sqlyog这样的可视化就得敲命令了。-- 创建用户,并设置密码 CREATE USER huanhuan IDENTIFIED BY 123456 -- 修改密码修改当前用户的密码 SET PASSWORD PASSWORD(123456) -- 修改密码修改指定用户的密码 SET PASSWORD FOR huanhuan PASSWORD(123456) -- 重命名 RENAME USER huanhuan TO zhenhuan -- 授权 ON 库.表 GRANT ALL PRIVILEGES -- 除了GRANT以外的所有权限 ON *.* -- 所有库的所有表 TO zhenhuan -- 撤销权限 REVOKE ALL PRIVILEGES ON *.* FROM zhenhuan -- 查询权限 SHOW GRANTS FOR zhenhuan -- 删除用户 DROP USER zhenhuanPSGRANT ALL PRIVILEGES语句看似是赋予所有权限但是有一个权限没有就是GRANT权限五、数据库备份导出的三种方式1. 方式一直接拷贝磁盘上的物理文件/data目录2. 方式二sqlyog可视化导出再导入可以导出整个数据库也可以导出某张表都一样导出来的都是sql文件。拿导出数据库为例我们导出名为shop的数据库打开这个文件导出以后其实就是sql文件然后把sql文件发给别人别人执行这个sql文件都是sql语句直接把sql文件拖入sqlyog就可以这个表就到别人数据库上了倘若导出的是表操作稍有不同3. 方式三mysqldump导出 source导入导出的时候直接在cmd里就行// 导出整个数据库mysqldump -h主机名 -u用户名 -p密码 数据库名 路径/文件自己命名 C:\Users\DELLmysqldump -hlocalhost -uroot -p123456 shop F:/1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. // 导出数据库中的一张表mysqldump -h主机名 -u用户名 -p密码 数据库名 表名 路径/文件自己命名 C:\Users\DELLmysqldump -hlocalhost -uroot -p123456 shop account F:/1.sql // 导出数据库中的一张表mysqldump -h主机名 -u用户名 -p密码 数据库名 表名1 表名2 表名3 路径/文件自己命名 C:\Users\DELLmysqldump -hlocalhost -uroot -p123456 shop account1 account2 account3 F:/1.sql导入的时候要先登录如果我们要导入的是一张表就要切换到数据库然后执行mysql source F:/1.sql5.2 完整备份差异备份日志备份完整备份因为很耗时一般一周执行一次那么这一周内的数据怎么备份呢差异备份。差异备份存的是上一次完整备份到下一次完整备份之间数据库的变动。再讲一下日志备份注意是日志备份而不是日志。日志本身就是一个数据的备份但是日志也需要被备份。六、三大范式2NF就是在1NF的基础上消除【部分依赖】3NF就是在2NF的基础上消除【传递依赖】1NF一个关系模式的所有属性不可再分表的每一列属性都不可再分违反了怎么办① 再拆呗② 把可以再分的属性作为实体拆分后的属性作为其属性2NF非主属性完全依赖于主属性这里特别指的是联合主键如果主码是单个属性那么一定满足二范式或者直接说消除非主属性的部分依赖前提满足1NF如何消除非主属性的部分依赖拆成两个表让产生部分依赖的非主属性带着被依赖的主属性滚蛋3NF非主属性不传递依赖于主属性前提满足1NF和2NF如何消除传递依赖也是拆让产生传递依赖的非主属性带着被依赖的非主属性滚出去如果数据库的设计完全满足三大范式那就可能会拆拆拆成十几张表这十几张表都是通过某某id外键关联起来的查一个数据要经过几十张表效率极低所以真实开发中我们必须违背三大范式阿里就有一个原则关联的表不能超过3张
【MySQL】学习笔记（四）—— 视图、事务、索引、用户管理、备份、三大范式
发布时间：2026/6/2 4:59:04
数据最重要的特性是一致性ACID中的C是目的AID是手段和完整性其次还有安全性。。完整性实体主码完整性参照外码完整性用户自定义的完整性一致性先删除从表才能删除主表事务安全性用户身份鉴别--用户权限控制---视图技术、加密存储一、视图数据的安全性视图有什么用呢1保证数据的安全性角度一视图对应数据库模式中的外模式对于不同的应用程序数据库提供不同的外模式这些应用程序只能访问外模式提供给他的部分数据而无法查看数据库中的完整数据保证了数据的安全性。角度二比如全公司的员工信息都存储在同一张表中但是人事部只能查看人事部的视图财务部只能查看财务部的视图都不能查看公司的整个员工信息表保证了数据的安全性。此外视图另一个重要的作用是2提供更加丰富的数据组织形式二、事务先别看下面的理论因为看了也不会用。先从实际开发切入。两个问题① java每次操作数据库都需要用事务的方式吗如果不是的话操作什么样的数据的情况下才用到事务 ② java实现事务的方式都有哪些① 不是所有情况下都要用到事务。只有当一组操作必须要么都执行要么都不执行的时候。两个场景“当实际的业务中需要将一条数据同时存放到两张表中并且要求两张表中的数据同步时。如果其中一张表的操作失败那么就需要回滚事务”“金融行业的软件开发严格重视事务处理。例如常见的转账操作一方的账户金额减少对应的是另一方的账户金额增加这个过程需要使用到事务机制以保证转账操作的正确性”② 两种实现方式一种是jdbc实现事务另一种是spring框架实现事务。最初是用jdbc这个包里的一些API去实现事务的叫commit()还记得不但是jdbc太复杂后来我们有了mybatis以后就不用jdbc了改用spring提供的API配合mybatis去使用。2.1 事务什么是事务一个数据库操作的序列小到两行sql语句大到几个sql程序。事务的定义方式显示定义START TRANSACTION ...... COMMIT/ROLLBACK隐式定义我们的DMBSmysql和SqlServer会默认划分并开启事务2.2 ACID原则AID是手段、数据的一致性C才是目的。1.原子性事务中包含的一系列数据库操作要么都做要么都不做2.一致性事务执行前后数据保持一致。A给B转100块B得到100块A减少100块事务执行前后金钱的总额不变。一致性状态数据库只包含成功事务提交的结果。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态不一致状态发生故障但已经写进数据库一部分了3.持久性记住两点提交。出现故障时事物一旦提交持久化到数据库事务没有提交恢复到原状不可逆。一旦持久化到数据库就不可逆了。4.隔离性其实就是并发 A给B转钱的同时C也给B转钱这两个事务互不影响如何实现ACID原则的原子性undolog历史版本。事务一旦出错就回滚持久性redolog。重做这里还有待填坑隔离性锁或者MVCC一致性原子性持久性隔离性共同保证了数据的一致性3种事务并发带来的破坏事务隔离性的现象1. 丢失修改2. 不可重复读和幻读哪来的哪去了怎么变了3. 读脏数据解决方法和java锁一样也是分为悲观锁和乐观锁两类悲观锁共享锁排它锁商用的DBMS基本上都默认用悲观锁但是MySQL的默认隔离级别是用MVCC实现的乐观锁时间戳MVCC乐观控制法这里可能有个疑问为什么java级别的版本控制就维护一个版本字段就可以了MySQL级别的版本控制这么复杂根本原因在于MySQL涉及到“提交和未提交的问题”这也是为什么MySQL叫做“多版本控制”而java级别的乐观锁只需要控制一个版本就可以了。1.排它锁写锁和共享锁读锁注意事务T加了写锁以后事务T自己可以写当然也可以读但是其他事务不能读。另外根据根据锁粒度的不同又被分为表级锁(table-level locking)和行级锁(row-level locking)。InnoDB 不光支持表级锁还支持行级锁默认为行级锁。行级锁的粒度更小仅对相关的记录上锁即可对一行或者多行记录加锁那么以上的解决方法都是把并行变串行是隔离的最高等级——可串行化。2. 其他等级低的隔离是用多版本控制MVCC乐观锁实现的中高级面试常问的MVCC实现原理是什么 被大佬讲明白了_哔哩哔哩_bilibiliMVCC多版本并发控制对一个数据存储多个版本也就是时间快照。保证事务T只能读开启本事务前提交的数据和当前事务修改的数据。实现MVCC的三大要素记录中/元祖中的三个隐藏字段DB_TRX_ID 上一次对这个记录进行修改的事务的idDB_ROLL_ID 回滚指针。记录上一条历史记录在undolog中的地址DB_ROW_ID 隐藏主键就是版本号修改一次版本号1。undolog数据的历史版本READ_VIEW事务启动时生成的一个快照trx_list: READ_VIEW生成时刻正在活跃的事务id列表up_limit_id : trx_list中最小的idlow_limit_id :READ_VIEW生成时刻尚未分配的下一个事务ID这么多东西怎么组合起来用呢 比如事务1现在要读student表里的一行记录事务1先去查这条记录的DB_TRX_ID字段看一下上一次修改过这个数据的事务是几比如说是事务3那么事务1现在就要判断事务3是已经提交了还是怎样怎样这样才能确定自己到底能不能读这条数据。如何判断呢去和READ_VIEW的3个字段对比。如果这个版本的数据不可见那么再尝试上一个版本的数据这不就用上undolog了吗举个例子看一下undolog生成的记录链2. 假设又有一个id为2的事务将上面的记录中的name字段由“zhangsan”改为“lisi”age字段不变图错了3.又有一个事务3将age改为32现在还有一个READ_VIEW我们没有涉及到。通过MVCC的处理流程我们综合介绍一下。注意事务的id按照事务开启的顺序依次递增便于事务开启时间前后的判断。当事务2进行快照读的时候要为其生成一个READ_VIEW此时事务4已经提交了所以处于活跃状态的事务是1,2,3其中最小的事务id是1如果有下一个事务那么将为其分配id5。所以READ_VIEW3个字段trx_list:[1,2,3]up_limit_id :1low_limit_id :5那么事务2能读到事务4提交的数据吗要拿从记录隐藏字段里读到的上一次修改数据的事务id和READ_VIEW中的记录作比较规则如下首先比较上一次修改数据的事务DB_TRX_ID 事务2进行快照读的时候活跃的最小IDup_limit_id 如果DB_TRX_ID up_limit_id就说明修改这个数据的事务在事务2进行快照读的时候已经不活跃了意思是已经提交了那么事务2可以放心读/改这个数据over。如果DB_TRX_ID up_limit_id我们则无法判断修改这个数据的事务是否已经提交进入下一轮判断DB_TRX_ID low_limit_id? 代表修改这个数据的事务在事务2生成READ_VIEW之后才出现的那么肯定不能读/改over。如果DB_TRX_ID low_limit_id进入下一轮判断判断DB_TRX_ID 是否在trx_list活跃列表里如果在说明事务2进行快照读的时候修改这个数据的事务还没有提交呢那么无法去读/改over。如果不在再结合前两轮的判断这个事务不在[0, up_limit_id ]的区间里也不在[ up_limit_id, ]这个活跃列表里注意这个列表不是连续区间哦也不在[low_limit_id , ∞] 里那么它只能是一个[up_limit_idlow_limit_id ]之间的一个在事务2生成READ_VIEW的时候已经提交的事务可以读/改。继续回到上面的例子DB_TRX_ID4不在活跃事务中说明事务4在READ_VIEW生成前就已经提交了所以事务2能够看到事务4修改的结果。上面我们讲的是“读已提交”这个隔离级别。我们知道MVCC可以实现“读已提交”和“可重复读”两个隔离级别对于“读已提交”来说事务2能够看到事务4修改的结果但是对于“可重复读”来说事务2不应该看到。以上说的都是事务2能够读/改事务4动过的数据那么判断能改以后MCVV怎么控制改呢插入呢删除呢3. 四个隔离等级实际上隔离有4个等级等级越高安全性越强但是效率越低。mysql4种隔离级别都支持但是mysql默认使用可重复读级别也就是第二高的隔离级别相当于在效率和安全上取了折中。隔离级别脏读不可重复读UPDATE幻读DELETE/INSERT丢失修改实现方式读未提交read uncommitted√√√所有的读操作都不加锁读到的数据都是最新的数据性能最好。所有的写操作加排它锁写完释放读已提交read committed√√MVCC可重复读repeatable read√MVCC可串行化serializable排它锁共享锁。但是不是写完就释放排它锁而是事务结束才释放不然会有脏读问题。读锁也是不是读完就释放而是事务结束再释放不然会有不可重复读幻读的问题。其实把并发的事务完全变成串行执行。PS: 之前我们把不可重复读等价为幻读哪来的哪去了怎么变了但是这里不可重复读狭义地理解为“怎么变了”也就是单指Update命令幻读理解为“哪来的哪去了”单指“DELETE和INSERT命令”。四种隔离等级都能解决“丢失修改”的问题四个隔离级别的共同点都是“事务T1写的时候事务T2不能写了”这是最低要求也是为什么四个隔离级别都能解决“丢失修改”问题。1可串行化排它锁、共享锁都用上该隔离等级实现的是“表锁定”而不是“行锁定”。以为要想避免幻读中“增加一行”“删除一行”的现象单单锁定行是不够的要锁定整个表貌似行锁锁定一个范围也能实现比如线程1锁的是where id 300这个范围线程2就无法插入id299了如果插入的是id301也不影响线程1呀2可重复读MVCC乐观控制MySql的默认隔离级别指一个事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的。在读提交的基础上用MVCC的快照机制进行行锁定不允许T1读的同时T2去UPDATE但是允许insert和delete因为MVCC机制无法阻止INSERT和DELETE所以可以避免不可重复读但避免不了幻读。两种隔离级别的不同之处仅在于生成READ_VIEW的时机不同解释可重复读RR更好理解。在RR隔离级别下事务在第一次快照读的时候创建一个READ_VIEW以后进行快照读的时候使用的依然是这个READ_VIEW这也就保证了事务第一次快照读能够读到的数据后面读到的是相同的数据保证了数据的一致性。而RC读到的永远是最新的数据3读已提交MVCC乐观控制读已提交的内容肯定不会出现脏读。但是事务T1读的同时居然T2可以UPDATE、INSETT、DELETE因为MVCC允许事务每一次读的时候都重新生成快照当然会出现不可重复读和幻读了4读未提交事务T1还未提交的时候T2居然可以读当然什么问题都会出现4. 活锁和死锁避免先来先服务操作系统有4种避免死锁的方法并不适用于数据库数据库直接不去避免死锁而是出现了死锁后诊断、解除。自古以来解除死锁注意区别于死锁避免的“先释放再分配”方式只有一个办法双方必须有一方妥协让出自己的资源。2.3 创建事务例子模板-- mysql默认开启事务自动提交现在我们把它关掉,变成手动处理事务 SET autocommit 0; -- 事务开启 START TRANSACTION SQL语句 SQL语句 ...... -- 提交持久化到数据库中成功 COMMIT -- 回滚恢复到事务开始前的状态失败 ROLLBACK -- 事务结束别忘了开启mysql的自动提交 SET autocommit 1模拟一个转账的例子这一段代码如何运行建议听一下视频的最后一分钟29、测试事务实现转账_哔哩哔哩_bilibili-- 转账的例子 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci USE shop CREATE TABLE account( id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, money DECIMAL(9,2) NOT NULL )ENGINEINNODB DEFAULT CHARSETutf8 INSERT INTO account(name,money) VALUES(A,2000.00),(B,10000.00) -- 模拟A给B转账事务 SET autocommit 0; START TRANSACTION; UPDATE account SET moneymoney-500 WHERE nameA; -- A减500 UPDATE account SET moneymoney500 WHERE nameB; -- B加500 COMMIT ROLLBACK SET autocommit 1三、索引INDEXmysql官方定义索引是帮助mysql高效获取数据的数据结构。所以索引是一种数据结构一种树形的数据结构也正是借助这种数据结构索引才能如此之快。如果不用索引那就是从头到尾遍历时间复杂度为O(n)用了B树作为数据结构虽然B数的时间复杂度是O(logn)但这是我们找索引的过程相当于查字典查到记录在第一页后直接翻到那一页查记录的时间复杂度是O(1)。索引作用极大加快查询速度。用于用于大数据量的查找百万条、千万条数据小数据量意义不大分类精确匹配主键索引值唯一UNIQUE 索引 (值唯一常规索引值不要求唯一模糊匹配全文索引3.1 创建索引的3种方式-- 方式一创建表的时候就指定主码、unique、普通索引index(key) CREATE TABLE tb_test( .... .... PRIMARY KEY(id), UNIQUE(name) ) -- 方式二创建完表了又想加索引 ALTER TABLE tb_test ADD UNIQUE(name) -- 方式三创建完表了又想加索引普通索引为例这种方法如何加UNIQUE索引还没研究过哈 -- CREATE INDEX 索引名 ON 表名字段名 CREATE INDEX index_tb_test_name ON tb_user(name)3.2 百万数据索引测试向app_user表中插入100万条用户数据。不用索引的情况下检索一个用户用时1秒左右如果一个网站并发检索10个用户就要10秒这可太慢了用EXPLAIN查看检索了991749条数据后才检索到看了两篇博客都说是全表从上到下扫描建立索引后从100万条用户数据中检索一个用户只需要0.001秒而且只检索了一条数据就检索到了是不是和查字典一样3.3 建立索引的原则正向超过300万条数据就加索引小数据量的表不要加索引作用不大主键索引unique唯一索引这是INNODB默认会加的对经常查询的字段加索引经常用于WHERE条件查询的字段加索引经常出现在Group by, order by ,distinct后面的字段索引列长度不应过大原因索引列太长B树每个非叶子节点能包含的索引数就变少了会导致树变高IO次数变多。应该加在哪一句话加在①大表 ②经常查询 ③且不怎么变动的字段上。索引不是越多越好存储空间“之前就有人反馈说他们公司的某个表实际内容的大小才 10G, 而索引大小却有 30G这是多么巨大的成本所以千万不要觉得索引建得越多越好”耗时不要对经常变动的数据加索引 因为要频繁维护索引也耗时3.4 索引的数据结构一共有4种1B树索引2B树索引3哈希索引4全文索引myisam支持innodb安装查件才能支持但工程上默认只有哈希索引和B树索引两种所以才会有面试题问你哈希索引和B树索引的区别而完全不提另外两个索引类型。1. 被淘汰的哈希索引这里建议再去看看数据结构里对哈希表的讲解。哈希表就是一个数组这个数组里存储的是地址。注意MySQL表一行行的数据在磁盘当中依然是连续存储的只不过他们的地址被“零零散散”地填充到了哈希表中。首先主键--哈希值---数组下标然后把这行MySQL记录的地址填到哈希表里时间复杂度也是O1。为什么被淘汰了呢缺点1哈希碰撞/哈希冲突。如果冲突了3次那就已经访问了3次I/O了而B树最多也就4层需要访问4次I/O而已缺点2因为不能进行范围查询这是最主要的缺点会进行全表扫描。比如有如下SQL语句SELECT * FROM student_table WHERE id300B树可以通过指针实现连续读磁盘但是哈希索引数据存储是不连续的根本不支持范围查询会进行全表扫描。2. B索引MySQL中所有的数据都被存储在page里由page进行管理而B树的一个节点也是一个page。一个page的大小是16KB如果一行数据的大小是1K那么一个page里最多可以存储16行数据这就是B树的存储方式把数据也存到节点里去但是只存储键值很小哇一个page可以存储1170条索引比存32条数据划算多了吧这就是B树比B数还要“矮胖”的原因。假如我们存了这样一张大表CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;每一行记录可以抽象成这样这是以叶子结点为例的一个page而page和page之间通过B树的双向链表相连。 好处有二① B树的范围查询只需要对链表进行遍历即可 ②局部性原理访问一个地址后有可能访问周围的B树非叶子结点存储索引键值和指向下一层的指针叶子结点存储键值和每一行记录。当然把数据也存在叶子结点上这是聚簇索引的做法。3. 自适应hash索引结构自适应哈希索引是Innodb引擎的一个特殊功能当它注意到某些索引值被使用的非常频繁时会在内存中基于B-Tree所有之上再创建一个哈希索引这就让B-Tree索引也具有哈希索引的一些优点比如快速哈希查找。这是一个完全自动的内部行为用户无法控制或配置3.5 面试题innodb的聚簇索引非聚簇索引和myIsam的非聚簇索引的区别聚簇索引把数据和索引放在一起索引的叶子结点是mysql数据库的每一行记录就是聚簇索引。innodb就是这么干的把数据和索引都放在一个文件里。聚簇索引只是一个概念默认是主键索引如果没有主键默认是第一个UNIQUE索引如果没有UNIQUEinnodb会自己生成一个隐式的聚簇索引GEN_CLUST_INDEX特点数据访问更快 因为索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快。聚簇索引对于主键的排序查找和范围查找速度非常快。按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库可以从更少的数据块中提取数据节省了大量的IO操作一个表只能有一个聚簇索引。很好理解啊因为聚簇索引把数据也一起存了你搞两个聚簇索引是想把数据存两遍吗缺点插入速度严重依赖于插入顺序 按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键。更新主键的代价很高 因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。非聚簇索引把数据和索引分开存储索引的叶子结点是数据的地址先查主键索引树在叶子结点获得数据的地址然后再拿着地址找数据回表所以效率慢innodb是聚簇索引和非聚簇索引配合着使用的。因为前面说了聚簇索引只能有一个那么我在这个SQL表上针对其他字段建的索引只能是非聚簇索引了。而数据都存在聚簇索引的叶子节点上呀所以当我们触发非聚簇索引时最终还是得触发聚簇索引。举个例子假如Student这个表有主键stuId那么innodb根据前面说的选择原则就会选主键索引作为聚簇索引当我们需要频繁查学生的专业字段也给专业字段create了一个索引这个索引就是非聚簇索引。当我们“select ... from student where 专业计算机科学与技术 ”的时候就先触发了“专业”这个非聚簇索引非聚簇索引的叶子节点存储着主键找到主键再根据主键回表触发聚簇索引找到叶子结点上存的学生数据。3.6 索引失效的情况模糊匹配“%你好”或者“_你好”的匹配字符出现在最前面。“你好%”这种情况下依然可以走索引在字段上做了SUMAVG等函数运算联合索引最左前缀匹配失效!NOT LIKE , NOT IN使用OR时OR两边的字段必须都有索引才行。这也是为什么要少用OR的原因四、用户管理为什么要给用户设权呢防止员工删库跑路。让某一部分活的员工只能查看某一部分表而且只有SELECT的权限没有DELETE的权限。对用户的管理无非是创建用户、给用户授予什么样的权限、删除用户。本质上其实是在对mysql自带的mysql数据库下的user表进行增删改查。可以看出root用户默认都是Y也就是上面都能干。1. 手动2. sql语句数据库中用户的权限一般都手动管理如上图但是如果服务器上没有sqlyog这样的可视化就得敲命令了。-- 创建用户,并设置密码 CREATE USER huanhuan IDENTIFIED BY 123456 -- 修改密码修改当前用户的密码 SET PASSWORD PASSWORD(123456) -- 修改密码修改指定用户的密码 SET PASSWORD FOR huanhuan PASSWORD(123456) -- 重命名 RENAME USER huanhuan TO zhenhuan -- 授权 ON 库.表 GRANT ALL PRIVILEGES -- 除了GRANT以外的所有权限 ON *.* -- 所有库的所有表 TO zhenhuan -- 撤销权限 REVOKE ALL PRIVILEGES ON *.* FROM zhenhuan -- 查询权限 SHOW GRANTS FOR zhenhuan -- 删除用户 DROP USER zhenhuanPSGRANT ALL PRIVILEGES语句看似是赋予所有权限但是有一个权限没有就是GRANT权限五、数据库备份导出的三种方式1. 方式一直接拷贝磁盘上的物理文件/data目录2. 方式二sqlyog可视化导出再导入可以导出整个数据库也可以导出某张表都一样导出来的都是sql文件。拿导出数据库为例我们导出名为shop的数据库打开这个文件导出以后其实就是sql文件然后把sql文件发给别人别人执行这个sql文件都是sql语句直接把sql文件拖入sqlyog就可以这个表就到别人数据库上了倘若导出的是表操作稍有不同3. 方式三mysqldump导出 source导入导出的时候直接在cmd里就行// 导出整个数据库mysqldump -h主机名 -u用户名 -p密码 数据库名 路径/文件自己命名 C:\Users\DELLmysqldump -hlocalhost -uroot -p123456 shop F:/1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. // 导出数据库中的一张表mysqldump -h主机名 -u用户名 -p密码 数据库名 表名 路径/文件自己命名 C:\Users\DELLmysqldump -hlocalhost -uroot -p123456 shop account F:/1.sql // 导出数据库中的一张表mysqldump -h主机名 -u用户名 -p密码 数据库名 表名1 表名2 表名3 路径/文件自己命名 C:\Users\DELLmysqldump -hlocalhost -uroot -p123456 shop account1 account2 account3 F:/1.sql导入的时候要先登录如果我们要导入的是一张表就要切换到数据库然后执行mysql source F:/1.sql5.2 完整备份差异备份日志备份完整备份因为很耗时一般一周执行一次那么这一周内的数据怎么备份呢差异备份。差异备份存的是上一次完整备份到下一次完整备份之间数据库的变动。再讲一下日志备份注意是日志备份而不是日志。日志本身就是一个数据的备份但是日志也需要被备份。六、三大范式2NF就是在1NF的基础上消除【部分依赖】3NF就是在2NF的基础上消除【传递依赖】1NF一个关系模式的所有属性不可再分表的每一列属性都不可再分违反了怎么办① 再拆呗② 把可以再分的属性作为实体拆分后的属性作为其属性2NF非主属性完全依赖于主属性这里特别指的是联合主键如果主码是单个属性那么一定满足二范式或者直接说消除非主属性的部分依赖前提满足1NF如何消除非主属性的部分依赖拆成两个表让产生部分依赖的非主属性带着被依赖的主属性滚蛋3NF非主属性不传递依赖于主属性前提满足1NF和2NF如何消除传递依赖也是拆让产生传递依赖的非主属性带着被依赖的非主属性滚出去如果数据库的设计完全满足三大范式那就可能会拆拆拆成十几张表这十几张表都是通过某某id外键关联起来的查一个数据要经过几十张表效率极低所以真实开发中我们必须违背三大范式阿里就有一个原则关联的表不能超过3张
