MySQL进阶 2022-11-29 14:29 > https://www.bilibili.com/video/BV1Kr4y1i7ru?p=88 ## 存储引擎 ### mysql体系结构：  index索引是在存储引擎层实现的，也就意味着不同存储引擎的索引实现方式（数据结构）不一样。 **InnoDB是MySQL5.5之后默认的存储引擎**。 存储引擎制定了数据：如何存，如何取，如何组织。 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。 show engines; 展示当前数据库支持的存储引擎 ### InnoDB特点： **三大特性：事务，外键，行级锁。**  ### InnoDB逻辑存储结构：  一页的大小是固定的16k ### MyISAM存储引擎特点：  ### Memory存储引擎特点：  ### 三个存储引擎的区别：  ### 如何选择存储引擎：  MyISAM：业务系统的日子，电商系统的足迹、评论可以使用，但是使用的不多，被MongoDB替代 memory：被Redis替代 ## 索引 ### 有无索引对比  索引优缺点： 优点： 降低IO成本；降低排序成本。 缺点：（可忽略不计） 占磁盘空间（磁盘便宜）；降低了更新表的速度（正常业务系统都是查多更新少） ### 索引结构  ### 二叉树  ### BTree > https://www.bilibili.com/video/BV1Kr4y1i7ru?p=68&spm_id_from=pageDriver&vd_source=0c0289766193c30abf3c712637e7b104 n阶就是一个节点有n个指针，一个节点能储存n-1个key。 储存元素时，如果超过key的个数，则**中间元素向上裂变**：  插入1200，=》 继续插入：  插入1000， =》  继续插入：  插入2456，放在第二层最右端，但是放不下；中间元素向上分裂，1980向上分裂，第一层还是放不下；于是1200向上分裂：   最后一步分裂应该是先将【0123，0345】和【1567，1980】分裂，然后将1200的左指针指向【0123，0345】，1200右指针指向【1567，1980】。 B-Tree中的数据是存储在Key下的，即每一个key下都能挂一个数据。（1200这种是key） 动态演示网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html BTree演示地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html ### B+Tree B+Tree是BTree演变过来的，与B树不同的是： 1、所有元素都会出现在叶子节点，非叶子节点上的数据只是为了索引用。（叶子节点存放数据） 2、叶子节点形成了一个单向链表，每一个节点都通过一个指针指向下一个元素。 ### MySQL中的B+Tree  ### MySQL中的Hash索引  1、只支持等值比较=，不支持范围查询（between，> ，< ...） 2、无法排序 3、查询效率高，通常（不发生Hash碰撞情况下）只需要一次检索就可以了，效率通常高于B+Tree索引 在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。 ### 面试题 > https://www.bilibili.com/video/BV1Kr4y1i7ru?p=71&spm_id_from=pageDriver&vd_source=0c0289766193c30abf3c712637e7b104 为什么InnoDB存储引擎选择使用B+tree索引结构？（而不是使用二叉树、B树或者Hash索引？） A. 相对于二叉树，层级更少，搜索效率高； B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低； （一页的大小是固定的16k，非叶子节点放了数据，那可存放的指针就少了） C. 相对Hash索引，B+tree支持范围匹配及排序操作； ### 索引分类  当创建唯一约束UNIQUE时，会自动创建唯一索引。 根据**索引的储存形式**，分为： **聚集索引**：将**数据存储与索引放到了一块**，索引结构的叶子 节点保存了行数据。必须有，而且只有一个。 **二级索引**：将数据与索引分开存储，索引结构的**叶子节点关联的是对应的主键**。可以存在多个。  **常规索引都是二级索引**，**主键索引一定是聚集索引**。 常规索引可以是一个字段，也可以是多个字段。一个字段建立的索引叫**单列索引**，多个字段建立的常规索引叫**联合索引**。 **多个查询条件时为什么推荐建立联合索引而不推荐每个查询字段都分别建立索引？** 如果每个查询字段都建立索引，查询时，MySQL仍然只会选择一个单列索引去查，查到后还要回表查询。而建立联合索引时，使用联合索引查到后，联合索引中有需要的字段，就不需要再**回表查询**了，效率高一点。 create unique index idx_user_phone_name on tb_user (phone, name); explain select id, phone, name from tb_user use index (idx_user_phone_name) where phone = '17000000000' and name = '韩信'; 索引失效情况： 1、联合索引： - 最左前缀法则：查询时最左边的字段一定要出现，否则索引失效。最左边的字段出现，中间字段不出现，那么不出现的字段后面的字段索引都失效。 - 范围查询：联合索引中，范围查询（> , <）右边的字段索引失效。但是使用>= ， <=时，不会失效。 2、不要在索引列上进行函数运算，否则索引失效。（explain select * from tb_user where substring(phone,10,2) = '15';） 3、字符串不加引号会造成隐式类型转换索引会失效。 4、like模糊匹配前面加%，则索引失效。 5、or连接的条件，一侧有索引，一侧没有索引，则索引失效。 6、数据分布影响：MySQL评估全表扫描比走索引还快，那么不走索引。 #### 覆盖索引： 查询返回的列在索引结构中都包含了，**不需要回表查询**。 #### 回表查询： 在查询时，先根据二级索引查到该行记录的id，再根据id查询该行具体的记录。 #### 前缀索引： select count(distinct substring(email, 1, 5)) / count(*) from tb_user; 值越接近1越好。比如说该sql查出结果是0.9157，接近1，那么可以将email的前5个字符作为前缀索引，create index idx_user_email_5 on tb_user(email(5)); 索引设计原则： 1、哪些表建立索引？数据量大查询频次高。 2、哪些字段建立索引？where、 order by、 group by后面的字段建立索引。 3、建立什么样的索引？如果是唯一的，建立唯一索引；尽量建立联合索引；设计字符串较长或大文本字段则建立前缀索引。 ### 索引优化 innoDB索引是行级锁，根据id主键进行修改是只锁当前行。但是若根据非索引字段修改，则会锁表。所以Update的where条件一定要加索引。  with check option是需要看视图的条件，cascaded和local是判定【只看当前的】还是【当前也看，依赖的视图也看】 ## SQL优化？ 大多数情况下是围绕索引进行优化的 ### 插入优化 1、批量插入： ```sql Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); ``` 更大批量数据可以用load指令。 2、手动控制事务： ```sql start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit; ``` 3、主键顺序插入高于乱序 ```sql 主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89 ``` ### 查询优化 #### order by 优化： A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。 B. 尽量使用覆盖索引。 C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。 D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。 #### group by 优化： A. 在分组操作时，可以通过索引来提高效率。 B. 分组操作时，索引的使用也是满足最左前缀法则的。 #### limit优化： 覆盖索引+子查询 覆盖索引：select * from tb_sku limit 9000000, 10; ==> select id from tb_sku order by id limit 9000000, 10; 错误子查询：select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10); 原因：MySQL8不支持在in后使用limit 正确子查询：select t.* from tb_sku t , (select id from tb_sku order by id limit 9000000, 10) a where t.id = a.id; #### count优化： count(主键) InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。 服务层拿到主键后，直接按行进行累加(主键不可能为null) count(字段) 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出 来，返回给服务层，服务层判断是否为null，不为null，计数累加。 有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返 回给服务层，直接按行进行累加。 count(数字) InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。 count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接 按行进行累加 按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(\*)，所以**尽量使用 count(*)**。 #### update优化： InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁 升级为表锁 。 因此，update后的where条件，尽量是索引列，且索引不能失效。 ## 锁 在并发访问时，解决数据访问的一致性、有效性等问题。 ### 全局锁 整个数据库实例加锁，处于只读状态，其他修改语句全部阻塞。适合全库备份。 加全局锁：`flush tables with read lock;` 数据备份：`mysqldump -uroot -p1234 itcast > itcast.sql` 释放锁：`unlock tables;` 在InnoDB引擎中，可以加上参数 --single-transaction 完成不加锁的一致性数据备份。（通过快照读实现） `mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql` ### 表级锁 锁整张表，锁定粒度大，锁冲突概率高，并发度低。表级锁分为三类： - 表锁 - 元数据锁（meta data lock, MDL） - 意向锁 #### 表锁 表锁又可以分为两类： - 表共享读锁（read lock，简称读锁），加了读锁后所有客户端都能读，所有客户端都不能写。当前客户端写出错，其他客户端写阻塞（等待锁释放）。 - 表独占写锁（write lock，简称写锁），加了写锁后只有当前客户端可以读写，其他客户端都不能读写（阻塞，等待锁释放）。 加锁语法：lock tables tableName read（write） 释放锁语法：unlock tables / 客户端断开连接。 #### 元数据锁 元数据锁加锁过程是系统控制的，无需显式使用。在访问一张表时会自动加上，维护表结构的一致性，当表上有活动事务时，则不能修改表结构（alter语句阻塞）。 作用是避免DML（Manipulation操作）与DDL（Definition定义）冲突，保证读写正确性。Mysql5.5开始引入，当对表增删改查时，加MDL读锁（共享）；当对表结构进行变更操作时，加MDL写锁（排他）。 MDL读锁与MDL读锁之间是兼容的；MDL写锁与MDL读、写锁之间是互斥的。  查看元数据锁： ```sql select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks; ``` #### 意向锁 为了避免在DML执行时，**加的行锁和表锁冲突**，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。 意向锁也可分为两种： - 意向共享锁（IS）：select ... lock in share mode时自动添加（单纯的select不会加任何锁）。与表共享读锁（read）兼容，与表独占写锁（write）互斥。 - 意向排他锁（IX）：insert、update、delete、select ... for update 时自动添加。与表共享读锁（read）、表独占写锁（write）都互斥。 意向锁之间不互斥。事务提交后，意向锁自动释放。 以下sql查看意向锁及行锁的加锁情况： > 与上边的sql查询的表不同 ```sql select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks; ``` 有了意向锁后，客户端一执行DML语句时，会对设计的行加锁，同时也会对该表加上意向锁。而其他客户端在对这张表加表锁时，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判定行锁情况了。 ### 行级锁 行级锁，每次锁定当前行的数据，锁定粒度最小，发生锁冲突概率最低，并发度最高。应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，分为三类： - 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete，在RC、RR隔离级别下都支持。 - 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，从而产生幻读。在RR隔离级别下支持。 - 临键锁（Next-Key Lock）：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。(下述为临键锁示意图)  > B+树结构下，叶子节点的索引结构排列成一个有序的双向链表。因此有了间隙这个说法，因为有可能向两个叶子节点中间插入数据 默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用**next-key锁**进行搜索和索引扫描，**以防止幻读**。 注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务加的间隙锁不会阻止另一个事务在同一间隙上加间隙锁。 #### 行锁 InnoDB实现了以下两种类型的行锁： - 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。**共享锁和共享锁之间兼容，和排他锁互斥。** - 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁。**排他锁与其他共享锁和排他锁都互斥。**  单纯select不加任何锁；select ... lock in share mode加行共享锁；select ... for update加行排他锁；insert、update、delete加行排他锁。 **行锁升级为表锁：**InnoDB的行锁是针对于索引加的锁，如果**不通过索引条件检索数据**（而是通过非索引条件检索数据后进行删改），那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。演示： `update emp set name = '111' where salary = 10000;` salary字段没有索引。  #### 间隙锁&临键锁 间隙锁&临键锁的三种情况： - 在（唯一）索引上的等值查询，给不存在的记录加锁时，优化为间隙锁。    - 在（普通）索引上的等值查询，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。 因为普通索引不是唯一的，B+树叶子节点形成的双向链表中，给出一个值不一定只能找到一个叶子节点，前后可能有等值的。一直到最后一个不满足的叶子节点为止，每个间隙都加锁。   这里老师说的是两个间隙锁一个行锁，但是这样不就是临键锁了吗？这里按理说不应该退化为间隙锁吗？没明白。 - 在（唯一）索引上的范围查询时，也会加上临键锁。 select * from stu where id >= 19 lock in share mode;  ### 总结 全局锁是数据逻辑备份时使用； 表级锁： - 表锁：对一张表加锁解锁 - 元数据锁：避免DML和DDL的冲突，自动加的 - 意向锁：规避表锁和行锁在加锁时的冲突，避免逐行检查加锁情况，不用手动加。 行级锁： - 行锁：锁某一行，共享锁之间可以兼容，其他互斥。 - 间隙锁：锁的是两条记录间的间隙，避免多个事务并发操作时出现幻读。 - 临键锁：锁的是记录和间隙，简单理解为=行锁+间隙锁。 ## InnoDB架构 业务操作时，直接操作缓冲区；如果缓冲区没有数据，会将磁盘数据加载回来，然后再存储在缓冲区中，增删改查时都会操作缓冲区。缓冲区中的数据会以一定的频率（时机）通过一组后台线程刷新到磁盘中，在磁盘中进行持久化保存（数据，索引等）。 ### 逻辑存储结构 表空间（系统表空间、独占表空间、通用表空间），段，区（1M，包含64个连续的页），页（16k），行 InnoDB申请表空间时，一次性申请4到5个区，从而保证申请到的页是连续的。  ### 架构 MySQL从5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复的特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构：  #### 内存结构 针对一个专门的数据库服务器来说，会把80%左右的内存都分配给缓冲区。因为通过缓冲区，可以提高数据库的并发访问性能。   在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分。 1). Buffer Pool InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能 弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁 盘I/O。 在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及 InnoDB的锁信息等等。 缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增 删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频 率刷新到磁盘，从而减少磁盘IO，加快处理速度。 缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型： - free page：空闲page，未被使用。 - clean page：被使用page，数据没有被修改过。 - dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。 在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。参数设置： show variables like 'innodb_buffer_pool_size'; 2). Change Buffer Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page 没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。 Change Buffer的意义是什么呢? 先来看一幅图，这个是二级索引的结构图：  与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新 可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了 ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。 3). Adaptive Hash Index 自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持 hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在 进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需 要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。 InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度， 则建立hash索引，称之为自适应hash索引。 自适应哈希索引，无需人工干预，是系统根据情况自动完成。 参数： adaptive_hash_index 4). Log Buffer Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log）， 默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事 务，增加日志缓冲区的大小可以节省磁盘 I/O。 参数: - innodb_log_buffer_size：缓冲区大小 - innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个： 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。 0: 每秒将日志写入并刷新到磁盘一次。 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。 #### 磁盘结构 表空间，redo log，双写缓冲文件等  #### 后台线程  ### 事务的原理 如何保证事务的四大特性？  原子性、一致性、持久性 主要通过 redo log 和 undo log 日志保证 隔离性 主要通过锁机制和MVCC保证 #### 持久性（redo log） https://www.bilibili.com/video/BV1Kr4y1i7ru?p=139 4:22 通过redo log保证 redo log ，重做日志，记录的是事务提交时**数据页的物理修改**，是用来实现事务的持久性。 该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。 **为什么不直接将Buffer Pool的修改更新到idb磁盘文件中呢？**因为大多数修改操作都是随机的，这样直接更新到磁盘时，IO访问也是随机的，会很耗时。而先将Buffer Poll的修改存到Redolog Buffer，然后将Redolog Buffer追加写到磁盘中的redo log file，这个步骤虽然也是内存到磁盘，但是对日志文件的写是追加写，是顺序的，所以耗时较短。等到空闲时再把Buffer Pool的修改同步到idb中，如果同步成功了，那么redo log file就没用了，定期删除；若同步失败，则再从redo log file同步数据。这样就保证了数据的持久性（事务一旦提交【事务提交时Redolog Buffer已经同步到redo log file了】，则永久生效） 这个机制叫**WAL**（Write-Ahead Logging），先写日志。  #### 原子性（undo log） https://www.bilibili.com/video/BV1Kr4y1i7ru?p=140 通过undo log保证 回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC。 undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当delete 一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update 一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相反的内容，并进行回滚。 Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。 Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。 #### 隔离性（MVCC+锁） #### 一致性（redo log和undo log共同保证） ### MVCC 几个概念 #### 当前读 读取的记录是**当前最新版本**，读取时还要保证其他并发事务不能修改当前记录，会**对读取的记录进行加锁**。select ... lock in share mode（共享锁），select ... for update，update，insert，delete（排他锁）都是一种当前读。 #### 快照读 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的**可见版本**，有可能是历史数据，**不加锁**，是**非阻塞读**。 - Read Committed：每次select ，都生成一个快照读。 - Repeatable Read：开启事务后第一个select 语句才是快照读的地方。（select查询的有可能是历史数据） - Serializable：快照读会退化为当前读（每次读都加锁）。 select ... lock in share mode是当前读，而单纯的select不是：   **使用当前读时，若正在读的数据被另一个事务修改了但是另一个事务还未提交，则当前读会陷入阻塞（卡住）直到另一个事务提交。** #### MVCC 全称Multi-Version Concurrency Control，**多版本并发控制**。指**维护一个数据的多个版本，使得读写操作没有冲突**，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，依赖于 - 数据库记录中的三个（或两个）隐式字段 - undo log 日志 - readView **MVCC的作用：快照读的时候，通过MVCC查找到历史版本。** ##### 三个隐藏字段 - DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID - DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 - DB_ROW_ID：隐藏主键，如果表结构没有定义主键，将会生成该隐藏字段；如果表结构有主键，则不会生成该字段。 ##### undo log日志 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志 当Insert的时候，产生的undo log日志只在回滚时需要，因此在事务提交后，可被立即删除。 而update、delete时，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。 ###### undo log版本链 **不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，该链表头部是最新的记录，链表尾部是最早的记录（旧）。**  ##### readview ReadView（**读视图**）是快照读SQL执行时MVCC提取数据的依据，主要记录并维护系统当前活跃的事务id（活跃事务即未提交事务）。 ReadView中包含了四个核心字段： - m_ids：当前活跃的事务ID集合 - min_trx_id：最小活跃事务ID - max_trx_id：预分配事务ID，当前最大事务ID+1（事务ID自增） - creator_trx_id：ReadView创建者的事务ID 生成ReadView的时机： - Read Committed：在事务中每一次执行快照读时生成ReadView。 - Repeatable Read：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 ##### MVCC原理 - RC读已提交隔离级别下，读取数据的原理：  - RR可重复读隔离级别下，读取数据的原理：因为ReadView是复用的，所以当然可重复读了，规则和数据都一样，最终快照读到的东西就一样了  ### 总结  ## Mysql管理 ### 系统数据库 MySQL安装后，自带了四个数据库，具体作用如下：  ### MySQL常用工具 - mysql命令直接执行sql而不登录进去（用于一些脚本）： mysql -h192.168.200.201 -P3306 -uroot –p123456 db01 -e "select * from stu"; - mysqladmin - 查看mysql的bin log二进制文件：mysqlbinlog binlog.000011 - 统计MySQL中有哪些数据库，多少张表，多少条记录：mysqlshow -uroot -p1234 {database_name} {table_name} --count - 备份：mysqldump -uroot -p1234 database_name > db.sql （包含建表语句【先drop再create】，数据插入语句）  - 恢复/导入：mysqlimport -uroot -p1234 database_name /tmp/city.txt 或 source /root/xxx.sql （source需要在mysql命令行中执行） --END--
发表评论