[toc] # 六、数据库 ## 1、数据库基础 ### 1.1 数据库三范式 范式是具有最小冗余的表结构。3 范式具体如下 #### 1.1.1 第一范式(列都是不可再分) 第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF） <img src="https://i.loli.net/2021/01/04/AlVPIu4FEiTYkDp.png" alt="image-20210104142755373" style="zoom:60%;" /> #### 1.1.2 第二范式(每个表只描述一件事) 首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。 **第二范式要求每个表只描述一件事情**。 <img src="https://i.loli.net/2021/01/04/ef13FwnNPvyrLsp.png" alt="image-20210104142910198" style="zoom: 67%;" /> #### 1.1.3 第三范式(不存在对非主键列的传递依赖) 第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传递依赖。除了主键订单编号外，顾客姓名依赖于非主键顾客编号。 <img src="https://i.loli.net/2021/01/04/abLRQxXyfgrnzUO.png" alt="image-20210104143039648" style="zoom:50%;" /> ### 1.2 数据库事务 事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作， 这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 。 #### 1.2.1 数据库事务特性 (ACID) ##### 1.2.1.1 原子性（Atomicity） 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。 ##### 1.2.1.2 一致性（Consistency） 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。 ``` 拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。 ``` ##### 1.2.1.3 隔离性（Isolation） 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 ``` 即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。 ``` ##### 1.2.1.4 持久性（Durability） 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。 #### 1.2.2 脏读、不可重复读、幻读 ##### 脏读（Dirty Read） A事务执行过程中，B事务读取了A事务的修改。但是由于某些原因，A事务可能没有完成提交，发生RollBack了操作，则B事务所读取的数据就会是不正确的。这个未提交数据就是脏读（Dirty Read）。脏读产生的流程如下： <img src="https://i.loli.net/2021/01/04/5rsdMkSzKWj4cp3.jpg" alt="image-20200111153612588" style="zoom: 50%;" /> ##### 不可重复读（Nonrepeatable Read） B事务读取了两次数据，在这两次的读取过程中A事务修改了数据，B事务的这两次读取出来的 数据不一样 。B事务这种读取的结果，即为不可重复读（Nonrepeatable Read）。不可重复读的产生的流程如下： <img src="https://i.loli.net/2020/04/20/5YbVK8NgF1lnBxP.jpg" alt="5YbVK8NgF1lnBxP" style="zoom:99%;" /> 不可重复读有一种特殊情况，两个事务更新同一条数据资源，后完成的事务会造成先完成的事务更新丢失。这种情况就是大名鼎鼎的**第二类丢失更新**。主流的数据库已经默认屏蔽了第一类丢失更新问题（即：后做的事务撤销，发生回滚造成已完成事务的更新丢失），但我们编程的时候仍需要特别注意第二类丢失更新。它产生的流程如下： > ##### 第一类丢失更新（Lost Update） > > 在完全未隔离事务的情况下，两个事务更新同一条数据资源，某一事务完成，另一事务异常终止，回滚造成第一个完成的更新也同时丢失 。这个问题现代关系型数据库已经不会发生。 **第二类丢失更新** <img src="https://i.loli.net/2021/01/04/RIqT7WYvbsxFpj3.jpg" alt="image-20200111153920684" style="zoom:50%;" /> 可以明显看出事务A的更新被事务B所覆盖，更新丢失。 ##### 幻读（Phantom Read） B事务读取了两次数据，在这两次的读取过程中A事务添加了数据，B事务的这两次读取出来的集合不一样。幻读产生的流程如下： <img src="https://i.loli.net/2021/01/04/hqxg3Oi9WeoLVcy.jpg" alt="image-20200111154008090" style="zoom: 50%;" /> #### 1.2.3 实际情况演示 在下面我会使用 2 个命令行mysql ，模拟多线程（多事务）对同一份数据的脏读问题。 MySQL 命令行的默认配置中事务都是自动提交的，即执行SQL语句后就会马上执行 COMMIT 操作。如果要显式地开启一个事务需要使用命令：`START TARNSACTION`。 我们可以通过下面的命令来设置隔离级别。 ```sql SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE] ``` 我们再来看一下我们在下面实际操作中使用到的一些并发控制语句: - `START TARNSACTION` |`BEGIN`：显式地开启一个事务。 - `COMMIT`：提交事务，使得对数据库做的所有修改成为永久性。 - `ROLLBACK`：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。 ##### 脏读(读未提交) 实例.jpg) ##### 避免脏读(读已提交)  ##### 不可重复读 还是刚才上面的读已提交的图，虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了 不可重复读问题。  ##### 可重复读  ##### 防止幻读(可重复读) .jpg) 一个事务对数据库进行操作，这种操作的范围是数据库的全部行，然后第二个事务也在对这个数据库操作，这种操作可以是插入一行记录或删除一行记录，那么第一个是事务就会觉得自己出现了幻觉，怎么还有没有处理的记录呢? 或者 怎么多处理了一行记录呢? 幻读和不可重复读有些相似之处 ，但是不可重复读的重点是修改，幻读的重点在于新增或者删除。 ### 1.3 数据库隔离级别 为了解决上面提及的并发问题，主流关系型数据库都会提供四种事务隔离级别。 #### 1.3.1 读未提交（Read Uncommitted） >读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。 ``` 事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。 ``` ``` 分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。 ``` **那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。** #### 1.3.2 读已提交（Read Committed）[处理脏读] 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。 > 读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。 ``` 事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的… ``` ``` 分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。 ``` #### 1.3.3 可重复读（Repeatable Read）[处理脏读、不可重复读、] 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。这种隔离级别可以防止除幻读外的其他问题。 > 重复读，就是在开始读取数据（事务开启）时，不再允许修改操作 ``` 事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。 ``` ``` 分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。 ``` #### 1.3.4 可串行化（Serializable）[处理脏读、不可重复读、幻读] > Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读、第二类更新丢失问题。在这个级别，可以解决上面提到的所有并发问题，但可能导致大量的超时现象和锁竞争，通常数据库不会用这个隔离级别，我们需要其他的机制来解决这些问题:乐观锁和悲观锁。 ``` 事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。 ```  ### 1.4 数据库锁 #### 1.4.1 封锁 封锁是实现并发控制的一个非常重要的技术。所谓封锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其他事务不能更新此数据对象。例如，事务T1要修改A，若在读出A之前先锁住A，其他事务就不能再读取和修改A了，直到T1修改并写回A解除了对A的封锁为止。这样，就不会丢失T1的修改。 确切的控制由封锁的类型决定。基本的封锁类型有两种:**排他锁**（ exclusive locks，简称X锁）和**共享锁**（ share locks，简称S锁） ##### 1.4.1.1 X锁（排他写锁） **X锁（排他写锁）**:若事务T1对数据对象A加上X锁，则只允许T读取和修改A，其他任何事物都不能再对A加任何类型的锁，直到T释放A上的锁为止。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A ##### 1.4.1.2 s锁（共享读锁） **s锁（共享读锁）**:阻塞写锁若事务T对数据A加上S锁，则事务T可以读A但是不能修改A，其他事务只能对A加S锁而不能加X锁直到T释放A上的S锁为止。这就保证了其他食物可以读A，但在T释放A上的S锁之前不能对A进行任何修改。 #### 1.4.2 封锁协议（解决脏读不可重复读） ##### 1.4.2.1 一级封锁协议 **一级封锁协议**:事务T在对数据对象A进行修改之前，必须对其加X锁，直至事务结束才释放。事务结束包括正常结束（ COMMIT）和非正常结束（ ROLLBACK）在一级加锁协议中，如果仅仅是对数据进行读操作而不进行修改， 是不需要进行加锁的。所以**只能避免修改丢失而不能避免不可重复读和脏读** ##### 1.4.2.2 二级封锁协议 **二级封锁协议**:在一级加锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。二级加锁协议除防止了丢失修改，还可进一步防止读脏数据。例如事务T1正在对数据对象R进行修改，此前已经对R加上了X锁，此时事务T2想读取R，就必须对R加上S锁，但是T2发现R已经被T1加上了X锁，于是T2只能等待T1释放了在R上加的锁之后才能对R加S锁并读取。这能防止T2读取到T1未提交的数据，从而**避免了脏读**。 但是在二级封锁协议中，由于读完数据后即可释放S锁，所以它**不能保证可重复读**。 ##### 1.4.2.3 三级封锁协议 **三级封锁协议**:三级封锁协议是指，在一级封锁协议的基础上增加事务T在读取数据R之前对其加S锁直至事务结束才释放三级封锁协议除了防止丢失修改和读“脏”数据之外，还进一步**防止了不可重复读** #### 1.4.3 死锁活锁 - **活锁**:如果事务T1封锁了数据R，事务T2又请求封锁R，于是T2等待。T3也请求封锁R，当T1释放了R上的锁之后系统首先批准了T3的请求，T2继续等待;然后T4又请求封锁R，T3在释放R上的锁之后系统又批准了T4的请求，T2有可能永远等待，这就是活锁的情形 **避免活锁的简单方法就是采用先来先服务的策略。**当多个事务请求封锁同一数据对象时，封锁子系统按请求锁的先后次序对事务进行排队，数据对象上的锁一旦释放就批准批准申请队列中第一个事务获得锁 - **死锁**:事务T1封锁了数据R1，事务T2封锁了数据R2;同时，事务T1请求封锁R2，因为T2已经封锁了R2，所以T1只能等待。T2也请求封锁R1，由于R1被T1封锁了，R2也只能等待。由于它们互相等待，T1和T2两个事务永远也不能结束，于是就形成了死锁。 --------------- **解决死锁的方法** ------------------- - **死锁的预防** 在数据库中，产生死锁的原因是两个或多个事务都已经封锁了一些数据对象，然后又都请求对已被事务封锁的对象加锁，从而出现死锁。防止死琐的发生其实就是要破坏产生死锁的条件。预防死锁发生通常有以下两种方法。 **一次封锁法**:一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行下去。一次封锁法虽然可以有效防止死锁的发生，但是增加了锁的粒度，从而降低了系统的并发性。并且数据库是不断变化的，所以事先很难精确地确定每个事务所需进行加锁的对象，为此只能扩大封锁范围，将事务在执行过程中可能需要封锁的数据对象全部加锁，这就进一步降低了并发度 **顺序封锁法**:顺序封锁法是预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实施封锁。例如在B树结构的索引中，可规定封锁的顺序必须是从根节点开始，然后是下一级的子节点，逐级封锁。顺序封锁法可以有效地避免死锁，但是要实现顺序封锁法十分的困难，因为很难事先确定每一个事务要封锁哪些对象，因此也就很难按规定的顺序去实施加锁。 由此可见数据库中不适合预防死锁，只适合进行死锁的诊断与解除 - **死锁的诊断与解除** 数据库系统中诊断死锁的方法与操作系统类似，一般使用**超时法**或**事务等待图法** **超时法**:如果一个事务的等待时间超过了规定的时限，那么就认为其发生了死锁。超时法实现简单，但其不足也十分明显，一是有可能误判了死锁，如事务因为其他原因而使等待时间超过时限，系统就会误认为发生了死锁;二是若时限设置得太长，则不能及时发现死锁。 **事务等待图法**:事务等待图是一个有向图G=（T，U），T为结点的集合，每个结点表示正在运行的事务:U为边的集合，每条边表示事务等待的情况。若T1等待T2，则在T1，T2之间画一条有向边，从T1指向T2。事务等待图动态地反应了所有事务的等待情况。并发控制子系统周期性（比如每隔数秒）生成事务等待图，并进行检测。如果发现图中存在回路，则表示系统中出现了死锁。 数据库管理系统的并发控制系统一旦检测到系统中存在死锁，就要设法解除。通常采用的方法是选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他事务得以继续运行下去。当然，对撤销的事务所进行的数据修改必须加以恢复。 #### 1.4.4 两段锁协议 申请不释放释放不申请 - 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁; - 在释放一个锁之后，事务不再申请和获得任何其他封锁 所谓两段锁的含义是，事务分为两个阶段:第一个阶段是获得封锁，也称为拓展阶段，在这个阶段，事务可以申请获得任何数据项上的任何类型的锁，但是不能释放锁;第二个阶段是释放封锁，也称为收缩阶段，在这个阶段，事务可以释放任何数据项上的任何类型的锁，但是不能再申请任何锁 #### 1.4.5 乐观锁 乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间戳就是不加锁，通过时间戳来控制并发出现的问题。 #### 1.4.6 悲观锁 悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。 **时间戳** 时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”， 每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加 1，提交之前 ，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量，以上悲观锁所说的加“锁”，其实分为几种锁，分别是： 排它锁（写锁）和共享锁（读锁） 。 #### 1.4.7 行级锁 行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时， Oracle 会自动应用行级锁： 1. INSERT、 UPDATE、 DELETE、 SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT]; 2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新 3. 使用 COMMIT 或 ROLLBACK 语句释放锁。 ##### InnoDB的行锁模式及加锁方法 InnoDB实现了以下两种类型的行锁。 - 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 - 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。 另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。 - 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。 - 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。 上述锁模式的兼容情况具体如表20-6所示。  **如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。**意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。 ¡ 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 ¡ 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。 用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。 <img src="https://i.loli.net/2021/01/04/rD5A9EK2CJRhdYq.png" alt="image-20210104161236719" style="zoom: 80%;" /> ##### InnoDB行锁实现方式 <font color="orange">***InnoDB行锁是通过给索引上的索引项加锁来实现的***</font>，这一点MySQL与Oracle不同，后者是<font color="orange"> ***通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！***</font> **在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。**下面通过一些实际例子来加以说明。 <font color="darkred">**（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。**</font> 在如表20-9所示的例子中，开始tab_no_index表没有索引： ```sql mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.15 sec) mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 ```  在如表20 -9所示的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如表20-10所示。 创建tab_with_index表，id字段有普通索引： ```sql mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.15 sec) mysql> alter table tab_with_index add index id(id); Query OK, 4 rows affected (0.24 sec) Records: 4 Duplicates: 0 Warnings: 0 ```  <font color='darkred'>（2） ***由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。***</font>应用设计的时候要注意这一点。 在如表20-11所示的例子中，表tab_with_index的id字段有索引，name字段没有索引： ```sql mysql> alter table tab_with_index drop index name; Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into tab_with_index values(1,'4'); Query OK, 1 row affected (0.00 sec) mysql> select * from tab_with_index where id = 1; +------+------+ | id | name | +------+------+ | 1 | 1 | | 1 | 4 | +------+------+ 2 rows in set (0.00 sec) ```  （3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 在如表20-12所示的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引： ```sql mysql> alter table tab_with_index add index name(name); Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 Warnings: 0 ```  (4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。 ***因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。***关于MySQL在什么情况下不使用索引的详细讨论，参见本章“索引问题”一节的介绍。 在下面的例子中，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行 **计划，我们可以清楚地看到了这一点。** **例子中tab_with_index表的name字段有索引，但是name字段是varchar类型的，如果where条件中不是和varchar类型进行比较，则会对name进行类型转换，而执行的全表扫描。** ```sql mysql> alter table tab_no_index add index name(name); Query OK, 4 rows affected (8.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain select * from tab_with_index where name = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab_with_index type: ALL possible_keys: name key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tab_with_index where name = '1' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab_with_index type: ref possible_keys: name key: name key_len: 23 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) ``` #### 1.4.8 表级锁 表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。 ##### 什么时候使用表锁 对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。 **¡ 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。** **¡ 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。** 当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。 在InnoDB下，使用表锁要注意以下两点。 **（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。** **（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。**正确的方式见如下语句： 例如，如果需要写表t1并从表t读，可以按如下做： ```sql SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES; ``` #### 1.4.9 页级锁 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。 BDB 支持页级锁 #### 1.4.10 间隙锁（Next-Key锁） 当我们用**范围条件**而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。 举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL： Select * from emp where empid > 100 for update; 是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。 InnoDB使用间隙锁的目的，**一方面是为了防止幻读，以满足相关隔离级别的要求**，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；**另外一方面，是为了满足其恢复和复制的需要**。 很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。 还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！ 在如表20-13所示的例子中，假如emp表中只有101条记录，其empid的值分别是1,2,......,100,101。  ## 2、索引 ### 2.1 什么是索引？ 数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105111604792.png" alt="image-20210105111604792" style="zoom:67%;" /> ### 2.2 索引存储模型推演 #### 2.2.1 二分查找 其实这个就是二分查找的一种思想，也叫**折半查找**，每一次，我们都把候选数据缩小了一半。如果**数据已经排过序的话**，这种方式效率比较高。所以第一个，我们可以考虑用**有序数组**作为索引的数据结构。`有序数组`的**等值查询**和**比较查询**效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据（改变 index），所以只适合存储静态的数据。为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。 #### 2.2.2 二叉查找树诞 **有没有可以使用二分查找的链表呢？** 为了解决这个问题，BST（Binary Search Tree）也就是我们所说的二叉查找树诞生了。 二叉查找树的特点是什么？左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。 二叉查找树既能够实现快速查找，又能够实现快速插入。但是二叉查找树有一个问题：就是它的**查找耗时是和这棵树的深度相关的**，在最坏的情况下时间复杂度会退化成O(n)。还是刚才的这一批数字，如果我们插入的数据刚好是有序的，2、6、11、13、17、22。个时候我们的二叉查找树变成了什么样了呢？它会变成链表（我们把这种树叫做“斜树”），这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。 <img src="https://i.loli.net/2021/01/05/7PojBMZYEQvRUNG.png" alt="image-20210105121015808" style="zoom:67%;" /> 造成它倾斜的原因是什么呢？因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它**不够平衡**。 所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢？这个就是**平衡二叉树**，叫做 Balanced binary search trees，或者 **AVL 树**（AVL 是发明这个数据结构的人的名字）。 #### 2.2.3 平衡二叉树（AVL Tree） AVL Trees (Balanced binary search trees)**平衡二叉树**的定义：左右子树深度差绝对值**不能超过 1**。 是什么意思呢？比如左子树的深度是 2，右子树的深度只能是 1 或者 3。这个时候我们再按顺序插入 1、2、3、4、5、6，一定是这样，不会变成一棵“斜树” <img src="https://i.loli.net/2021/01/05/agFi9Qd6GZ5TKMU.png" alt="image-20210105121436828" style="zoom:50%;" /> 那它的平衡是怎么做到的呢？怎么保证左右子树的深度差不能超过 1 呢？插入 1、2、3。我们注意看：当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。 那应该怎么办呢？因为它是右节点下面接一个右节点，**右-右型**，所以这个时候我们**要把 2 提上去**，这个操作叫做**左旋**。 <img src="https://i.loli.net/2021/01/05/Zd51UGreDmBhuFP.png" alt="image-20210105121622332" style="zoom:50%;" /> 同样的，如果我们插入 7、6、5，这个时候会变成**左左型**，就会发生**右旋**操作，把 6提上去。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105121644748.png" alt="image-20210105121644748" style="zoom:50%;" /> 所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作。平衡的问题我们解决了，那么平衡二叉树作为索引怎么查询数据？在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？ 它应该存储三块的内容： - 第一个是**索引的键值**。比如我们在 id 上面创建了一个索引，我在用 where id =1 的条件查询的时候就会找到索引里面的 id 的这个键值。 - 第二个是**数据的磁盘地址**，因为索引的作用就是去查找数据的存放的地址。 - 第三个，因为是**二叉树**，它必须还要有**左子节点**和**右子节点**的**引用**，这样我们才能找到下一个节点。比如大于 26 的时候，走右边，到下一个树的节点，继续判断。 <img src="https://i.loli.net/2021/01/05/GsZFONAeBX97gbo.png" alt="image-20210105121820008" style="zoom: 40%;" /> 在分析用 AVL 树存储索引数据之前，我们先来学习一下 InnoDB 的逻辑存储结构。 #### 2.2.4 InnoDB逻辑存储结构 MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。 <img src="https://i.loli.net/2021/01/05/QsWeqVylYPCNrv5.png" alt="image-20210105122151788" style="zoom:67%;" /> ##### 2.2.4.1 表空间 Table Space `表空间`可以看做是 InnoDB 存储引擎逻辑结构的最高层，**所有的数据**都存放在**表空间中**。分为：**系统表空间**、**独占表空间**、**通用表空间**、**临时表空间**、**Undo 表空间** ##### 2.2.4.2 段 Segment 表空间是由各个段组成的，常见的`段`有**数据段**、**索引段**、**回滚段**等的概念。**一个 ibd 文件（独立表空间文件）里面会由很多个段组成**。创建一个索引会创建两个段，**索引段**：leaf node segment，**数据段**：non-leaf node segment。索引段管理**非叶子节点**的数据。数据段管理**叶子节点**的数据。也就是说，一个表的段数，就是索引的个数乘以 2。 ##### 2.2.4.3 簇 Extent 一个段（Segment）又由很多的簇（也可以叫区）组成，每个区的大小是 **1MB**（64个连续的页）。 每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是**扩展的最小单位就是簇**。 ##### 2.2.4.4 页 Page **为了高效管理物理空间，对簇进一步细分，就得到了页。**簇是由连续的页（Page）组成的空间，一个簇中有 **64 个连续的页**。 （1MB／16KB=64）。这些页面在**物理上**和**逻辑上**都是连续的。跟大多数数据库一样，InnoDB 也有页的概念（也可以称为块），每个页默认 16KB。**页**是 InnoDB 存储引擎磁盘管理的**最小单位**，通过 innodb_page_size 设置。 >一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。 注意，文件系统中，也有页的概念。操作系统和内存打交道，最小的单位是页 Page。文件系统的内存页通常是 4K。 <img src="https://i.loli.net/2021/01/05/UkGvXOhFaHn9qRy.png" alt="image-20210105123136525" style="zoom: 50%;" /> ```sql SHOW VARIABLES LIKE 'innodb_page_size'; ``` 假设一行数据大小是 1K，那么一个数据页可以放 16 行这样的数据。举例：一个页放 3 行数据。 <img src="https://i.loli.net/2021/01/05/fCAODWJbtSj6MFz.png" alt="image-20210105123231225" style="zoom:67%;" /> 往表中插入数据时，如果一个页面已经写完，产生一个新的叶页面。如果一个簇的所有的页面都被用完，会从当前页面所在段新分配一个簇。**如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂：** <img src="https://i.loli.net/2021/01/05/D6iIAo71aZuxPWB.png" alt="image-20210105123603118" style="zoom:50%;" /> ##### 2.2.4.5 行 Row InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。DYNAMIC Row Format（5.7 默认） <img src="https://i.loli.net/2021/01/05/EjFJWcyBTg4zIVb.png" alt="image-20210105135856450" style="zoom:67%;" /> innodb_file_format 在配置文件中指定；row_format 则在创建数据表时指定。 ```sql show variables like "%innodb_file_format%"; SET GLOBAL innodb_file_format=Barracuda; ``` 在创建表的时候可以指定行格式。 ```sql CREATE TABLE tf1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; --查看行格式： SHOW TABLE STATUS LIKE 'student' \G; ``` <img src="https://i.loli.net/2021/01/05/AZd1zXHsRu8xN3U.png" alt="image-20210105140129869" style="zoom:50%;" /> 下面我们继续看一下，用 AVL 树存储索引数据，会有什么样的问题。 #### 2.2.5 AVL树用于存储索引数据 首先，**索引的数据**，是放在**硬盘上**的。查看数据和索引的大小： ```sql select CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len, CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len from information_schema.TABLES where table_schema='gupao' and table_name='user_innodb'; ``` 当我们用树的结构来存储索引的时候，**访问一个节点**就要跟磁盘之间**发生一次 IO**。InnoDB 操作磁盘的最小的单位是一**页**（或者叫一个**磁盘块**），**大小是 16K**(16384 字节)。那么，一个树的节点就是 16K 的大小。*如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候，浪费了大量的空间。*所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着**跟磁盘交互次数就会过多**。如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数越多，消耗的时间就越多。 <img src="https://i.loli.net/2021/01/05/QWtX8dcIlug92CP.png" alt="image-20210105140604113" style="zoom:50%;" /> 比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=37 的时候，要查询两个子节点，就需要跟磁盘交互 3 次，如果我们有几百万的数据呢？这个时间更加难以估计。所以我们的解决方案是什么呢？第一个就是让每个节点存储更多的数据。第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有 更多的**分叉（我们把它叫做“路数”）**。因为分叉数越多，树的深度就会减少（根节点是 0）。这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子？这个时候，我们的树就不再是**二叉了，而是多叉，或者叫做多路。** #### 2.2.6 多路平衡查找树（B Tree） Balanced Tree这个就是我们的多路平衡查找树，叫做 B Tree（B 代表平衡）跟 AVL 树一样，B 树在枝节点和叶子节点**存储键值**、**数据地址**、**节点引用**。它有一个特点：分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。 <img src="https://i.loli.net/2021/01/05/l7qmUQ3X9vogLHO.png" alt="image-20210105140930571" style="zoom:67%;" /> 那 B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟 AVL 树有什么区别？比如 Max Degree（路数）是 3 的时候，我们**插入数据** 1、2、3，在插入 3 的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有 4 个指针，子节点会变成 4 路，所以这个时候必须进行**分裂**。把中间的数据 2 提上去，把 1 和 3 变成 2 的子节点。如果**删除节点**，会有相反的**合并**的操作。注意这里是分裂和合并，跟 AVL 树的左旋和右旋是不一样的。我们继续插入 4 和 5，B Tree 又会出现分裂和合并的操作。 <img src="https://i.loli.net/2021/01/05/8ATGqFmCRprUNaH.png" alt="image-20210105141104177" style="zoom:50%;" /> 在更新索引的时候会有大量的索引的结构的调整，所以 解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。**节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。** #### 2.2.7 B+树（加强版多路平衡查找树） B Tree 的效率已经很高了，为什么 MySQL 还要对 B Tree 进行改良，最终使用了B+Tree 呢？总体上来说，这个 B 树的改良版本解决的问题比 B Tree 更全面。我们来看一下 InnoDB 里面的 B+树的存储结构：  MySQL 中的 B+Tree 有几个特点： 1、它的关键字的数量是跟路数相等的； 2、B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。 3、B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。 4、它是根据左闭右开的区间 [ )来检索数据。 >举个例子：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶子节点可以存储多少个指针？假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384/14=1170 个这样的单元（键值+指针），代表有 1170 个指针。树 深 度 为 2 的 时 候 ， 有 1170^2 个 叶 子 节 点 ， 可 以 存 储 的 数 据 为1170*1170*16=21902400。  在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。 所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。 **总结一下，InnoDB 中的 B+Tree 的特点：** 1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。**B Tree 解决的两大问题是什么**？（每个节点存储更多关键字；路数更多） 2)扫库、扫表能力更强（如果我们要对表进行**全表扫描**，只需要**遍历叶子节点就可以了**，不需要遍历整棵 B+Tree 拿到所有的数据） 3) B+Tree 的**磁盘读写能力**相对于 B Tree 来说**更强**（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多） 4)**排序能力更强**（因为**叶子节点**上有下一个数据区的指针，**数据形成了链表**） 5)**效率更加稳定**（B+Tree 永远是在叶子节点拿到数据，所以 **IO 次数是稳定的**） #### 2.2.8 索引方式：真的是用的 B+Tree吗？ 在 Navicat 的工具中，创建索引，索引方式有两种，**Hash** 和 **B Tree**。 **HASH**：以 KV 的形式检索数据，也就是说，它会根据索引字段生成**哈希码**和指针，指针指向数据。 <img src="https://i.loli.net/2021/01/05/2EQriNMupZbhYFj.png" alt="image-20210105142120458" style="zoom:67%;" /> **哈希索引有什么特点呢？** - 时间复杂度是 O(1)，查询速度比较快。因为哈希索引里面的数据不是按顺序存储的，所以不能用于排序。 - 查询数据时根据键值计算哈希码，只支持等值查询（= IN），不支持范围查询（> < >= <= between and）。 - 如果字段重复值很多的时候，会出现大量的哈希冲突（采用拉链法解决），效率会降低。 InnoDB 内部使用哈希索引来实现自适应哈希索引特性。 这句话的意思是 InnoDB 只支持显式创建 B+Tree 索引，对于一些热点数据页， InnoDB 会自动建立自适应 Hash 索引，也就是在 B+Tree 索引基础上建立 Hash 索引， 这个过程对于客户端是不可控制的，隐式的。 ### 2.3 MySql有哪些索引？ ■数据结构角度 1. BTREE 2. HASH 3. FULLTEXT 4. R-Tree ■物理存储角度 1. 聚集索引（ clustered index） 2. 非聚集索引（non- clustered index ■从逻辑角度 1. **普通索引**:仅加速查询 2. **唯一索引**:加速査询+列值唯一（可以有null) 3. **主键索引**:加速查询+列值唯一（不可以有null）+表中只有一个 4. **组合索引**:多列值组成一个索引，专门用于组合搜索，其效率大于索引合并 5. **全文索引**:对文本的内容进行分词，进行搜索 在 InnoDB 里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引。 - 普通索引（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。 - 唯一索引（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。 - 全文索引（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。 #### 2.3.1 常见索引类型 ##### 2.3.1.1 主键索引(Primary Key) 数据表的主键列使用的就是主键索引。 一张数据表有只能有一个主键，并且主键不能为null，不能重复。 在mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。 ##### 2.3.1.2 二级索引(辅助索引) **二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。** 唯一索引，普通索引，前缀索引等索引属于二级索引。  InnoDB 中，主键索引和辅助索引是有一个主次之分的。辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。 **PS:不懂的同学可以暂存疑，慢慢往下看，后面会有答案的，也可以自行搜索。** 1. **唯一索引(Unique Key)** ：唯一索引也是一种约束。**唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。** 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。 2. **普通索引(Index)** ：**普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。** 3. **前缀索引(Prefix)** ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。 4. **全文索引(Full Text)** ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。 ##### 2.3.1.3 聚集索引与非聚集索引 ###### 2.3.1.3.1 聚集索引 **聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。**索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的 在 Mysql 中，InnoDB引擎的表的 `.ibd`文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。 ###### 2.3.1.3.2 聚集索引的优点 聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。 ###### 2.3.1.3.3 聚集索引的缺点 1. **依赖于有序的数据** ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。 2. **更新代价大** ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。 ##### 2.3.1.4 非聚集索引 **非聚集索引即索引结构和数据分开存放的索引。** **二级索引属于非聚集索引。** >MYISAM引擎的表的.MYI文件包含了表的索引， >该表的索引(B+树)的每个叶子非叶子节点存储索引， >叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。 > >**非聚集索引的叶子节点并不一定存放数据的指针， >因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。** ###### 2.3.1.4.1 非聚集索引的优点 **更新代价比聚集索引要小** 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的 ###### 2.3.1.4.2 非聚集索引的缺点 1. 跟聚集索引一样，非聚集索引也依赖于有序的数据 2. **可能会二次查询(回表)** :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。 这是Mysql的表的文件截图:  聚集索引和非聚集索引:  ##### 2.3.1.5 非聚集索引一定回表查询吗(覆盖索引)? **非聚集索引不一定回表查询。** >试想一种情况，用户准备使用SQL查询用户名，而用户名字段正好建立了索引。 ````text SELECT name FROM table WHERE username='guang19'; ```` >那么这个索引的key本身就是name，查到对应的name直接返回就行了，无需回表查询。 **即使是MYISAM也是这样，虽然MYISAM的主键索引确实需要回表， 因为它的主键索引的叶子节点存放的是指针。但是如果SQL查的就是主键呢?** ```text SELECT id FROM table WHERE id=1; ``` 主键索引本身的key就是主键，查到返回就行了。这种情况就称之为覆盖索引了。 ##### 2.3.1.6覆盖索引 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！ **覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。** >如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键。 > >再如普通索引，如果一条SQL需要查询name，name字段正好有索引， >那么直接根据这个索引就可以查到数据，也无需回表。 覆盖索引:  #### 2.3.2 主键和唯一索引区别？ 本质区别，主键是一种约束，唯一索引是一种索引。 - 主键不能有空值（非空+唯一），唯一索引可以为空 - 主键可以是其他表的外键，唯一索引不可以。 - 一个表只能有一个主键，唯一索引可以多个。 - 都可以建立联合主键或联合唯一索引 - 主键-》聚簇索引，唯一索引->非聚簇索引。 #### 2.3.3 索引不生效的情况？ - 使用不等于查询 - NULL值 - 列参与了数学运算或者函数 - 在字符串like时左边是通配符比如％xxx - 当mysq分析全表扫描比使用索引快的时候不使用索引 - 当使用联合索引前面一个条件为范围查询后面的即使符合最左前缀原则，也无法使用索引 #### 2.3.4 Mysql索引使用两种数据结构 MySQL索引使用的数据结构主要有**BTree索引** 和 **哈希索引** 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。 ##### Hash索引和 B+树索引优劣分析 **Hash索引定位快** Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。 **Hash冲突问题** 知道HashMap或HashTable的同学，相信都知道它们最大的缺点就是Hash冲突了。不过对于数据库来说这还不算最大的缺点。 **Hash索引不支持顺序和范围查询(Hash索引不支持顺序和范围查询是它最大的缺点。** 试想一种情况: ````text SELECT * FROM tb1 WHERE id < 500; ```` B+树是有序的，在这种范围查询中，优势非常大，直接遍历比500小的叶子节点就够了。而Hash索引是根据hash算法来定位的，难不成还要把 1 - 499的数据，每个都进行一次hash计算来定位吗?这就是Hash最大的缺点了。 MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。 - **MyISAM:** B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。 - **InnoDB:** 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。**在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。** **因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。** PS：整理自《Java工程师修炼之道》 #### 2.3.5 MyISAM和InnoDB实现BTree索引方式的区别 ##### 2.3.5.1.B树和B+树 ###### 1.B树 > B树是一种多路搜索树。 > > 1. 定义任意非叶子结点最多只有M个儿子，且M>2。 > 2. 根结点的儿子数为[2, M]。 > 3. 除根结点以外的非叶子结点的儿子数为[M/2, M]。（结点丰满程度至少为一半） > 4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）。 > 5. 非叶子结点的关键字个数=指向儿子的指针个数-1。 > 6. 非叶子结点的关键字：K[1], K[2], …, K[M-1]，且K[i] <= K[i+1]。 > 7. 非叶子结点的指针：P[1], P[2], …,P[M]（其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树）。 > 8. 所有叶子结点位于同一层。 下图是一个M=4阶的B树。  B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的是叶子结点。 查找文件29的过程： 1. 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。（磁盘IO操作1次） 2. 此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。 3. 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。（磁盘IO操作2次） 4. 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。 5. 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。（磁盘IO操作3次） 6. 此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地址。 ```java B树的特性： 1.关键字分布在整颗树的所有节点。 2.任何一个关键字出现且只出现在一个结点中。 3.搜索有可能在非叶子结点结束。 4.其搜索性能等价于在关键字全集内做一次二分查找。 ``` ###### 2.B+树  下图是一个M=3阶的B+树。  一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。  B+树与B树的差异 ```JAVA B+树是B树的一种变形树，总结起来，数据库索引的B+树与B树的差异在于： 1.非叶子结点的子树指针与关键字个数相同。 2.非叶子结点的子树指针P[i]，指向关键字值属于[K[i],K[i+1])的子树（注意，区间是前闭后开）。 3.为所有叶子结点增加一个链指针。 4.所有关键字都在叶子结点出现。 ``` B+树的特性： ```JAVA 1. 所有关键字都出现在叶子结点的链表中，且链表中的关键字是有序的。 2. 搜索只在叶子结点命中。 3. 非叶子结点相当于是叶子结点的索引，叶子结点是存储关键字数据的数据层。 ``` ###### 3.B树/B+树做索引的原因分析 一般来说，磁盘I/O次数可以用于评价索引结构的优劣。在B-Tree中查找，可知检索一次最多需要访问h个节点（上文举例查找文件29的过程）。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。 为了达到这个目的，在实际实现中，B树还使用如下技巧： 1. 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个节点只需一次I/O。 2. B树中一次检索最多需要h-1次I/O（根节点常驻内存）。一般实际应用中，出度d（树的分叉数）是非常大的数字，通常超过100；h非常小，通常不超过3。 综上所述，用B树作为索引结构效率是非常高的。 红黑树或者平衡二叉树的其他树结构， 1. h明显要深的多，执行效率低。 2. 逻辑上很近的节点（父子）物理上可能很远，无法利用局部性， 3. 每个节点存储的数据量太小了，对磁盘空间造成浪费，带来频繁的IO操作。 所以其他树结构的效率明显比B树差很多。 ##### 2.3.5.2.Innodb索引结构基础知识 ###### 1 各种树形结构 **<font color="darkred">1. 搜索二叉树</font>**：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结构。 **<font color="darkred">2. B树</font>：**一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足： $$(\frac{m}{2}-1)\leq j \leq (m-1)$$ ；<u>一个节点的子节点数量会比关键字个数多1</u>，这样关键字就变成了子节点的分割标志。由于数据同时存在于叶子节点和非叶子结点中，无法简单完成按顺序遍历B树中的关键字，必须用中序遍历的方法。 **<font color="darkred">3. B+树</font>：**一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：$$(\frac{m}{2}-1)\leq j \leq (m)$$；<u>子树的个数最多可以与关键字一样多。</u>非叶节点存储的是子树里最小的关键字。同时数据节点只存在于叶子节点中，且叶子节点间增加了横向的指针，这样顺序遍历所有数据将变得非常容易。 **<font color="darkred">4. B*树</font>：**一棵m阶B树是一棵平衡的m路搜索树。最重要的两个性质是:①每个非根节点所包含的关键字个数 j 满足$$(\frac{2m}{3}-1)\leq j \leq (m)$$ 。②非叶节点间添加了横向指  先看B树的分裂，下图的红色值即为每次新插入的节点。每当一个节点满后，就需要发生分裂，并且有可能引起分裂想根节点方向的传递，（甚至传递到root节点，导致root节点也分裂，从而引起树的高度增加）  **<font color="darkred">B+树的分裂</font>**：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据复制到新结点，最后在父结点中增加新结点的指针；B+树的分裂只影响原结点和父结点，而不会影响兄弟结点，所以它不需要指向兄弟节点的指针。  <font color="darkred"> **B\*树的分裂:**</font>当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分数据移到兄弟结点中，再在原结点插入关键字，最后修改父结点中兄弟结点的关键字（因为兄弟结点的关键字范围改变了）。如果兄弟也满了，则在原结点与兄弟结点之间增加新结点，并各复制1/3的数据到新结点，最后在父结点增加新结点的指针。可以看到B*树的分裂非常巧妙，因为B*树要保证分裂后的节点还要2/3满，如果采用B+树的方法，只是简单的将已满的节点一分为二，会导致每个节点只有1/2满，这不满足B*树的要求了。所以B*树采取的策略是在本节点满后，继续插入兄弟节点（这也是为什么B*树需要在非叶子节点加一个兄弟间的链表），直到把兄弟节点也塞满，然后拉上兄弟节点一起凑份子，自己和兄弟节点各出资1/3成立新节点，这样的结果是3个节点刚好是2/3满，达到B*树的要求，皆大欢喜。  B+树适合作为数据库的基础结构，完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问（随机访问即给出任意一个地址，要求返回这个地址存储的数据）但是容量较小。而硬盘的随机访问要经过机械动作（1磁头移动 2盘片转动），访问效率比内存低几个数量级，但是硬盘容量较大。典型的数据库容量大大超过可用内存大小，这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。如下图所示：通常向下读取一个节点的动作可能会是一次磁盘IO操作，不过非叶节点通常会在初始阶段载入内存以加快访问速度。同时为提高在节点间横向遍历速度，真实数据库中可能会将图中蓝色的CPU计算/内存读取优化成二叉搜索树（InnoDB中的page directory机制）。  真实数据库中的B+树应该是非常扁平的。对于一个22.1G容量的表，B+树的高度是3，如果要把非叶节点全部加载到内存也只需要少于18.8M的内存。 ###### 2 Mysql的存储引擎和索引 数据库必须有索引，没有索引则检索过程变成了顺序查找，O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引，只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时，一棵B+树就只能存储主键，如果检索的是非主键字段，则主键索引失去作用，又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。 这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题，**一种叫做聚簇索引（clustered index ），一种叫做非聚簇索引（secondary index）**。这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。对于聚簇索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。 ``` InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。 MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。 ``` 为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。 <img src="https://tva1.sinaimg.cn/large/006tNbRwgy1gazmrxzzp6j30wn0u07wh.jpg" alt="image-20200117161839227" style="zoom:67%;" /> 我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？ 1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。 2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。 ###### 3 Page结构 理解InnoDB的实现不得不提Page结构，Page是整个InnoDB存储的最基本构件，也是InnoDB磁盘管理的最小单位，与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型，常见的页类型有数据页（B-tree Node）Undo页（Undo Log Page）系统页（System Page） 事务数据页（Transaction System Page）等。单个Page的大小是16K（编译宏UNIV_PAGE_SIZE控制），每个Page使用一个32位的int值来唯一标识，这也正好对应InnoDB最大64TB的存储容量（16Kib * 2^32 = 64Tib）。一个Page的基本结构如下图所示：  每个Page都有通用的头和尾，但是中部的内容根据Page的类型不同而发生变化。Page的头部里有我们关心的一些数据，上图把Page的头部详细信息显示出来： 我们重点关注和数据组织结构相关的字段：Page的头部保存了两个指针，分别指向前一个Page和后一个Page，头部还有Page的类型信息和用来唯一标识Page的编号。根据这两个指针我们很容易想象出Page链接起来就是一个双向链表的结构。  再看看Page的主体内容，我们主要关注行数据和索引的存储，他们都位于Page的User Records部分，User Records占据Page的大部分空间，User Records由一条一条的Record组成，每条记录代表索引树上的一个节点（非叶子节点和叶子节点）。在一个Page内部，单链表的头尾由固定内容的两条记录来表示，字符串形式的"Infimum"代表开头，"Supremum"代表结尾。这两个用来代表开头结尾的Record存储在System Records的段里，这个System Records和User Records是两个平行的段。InnoDB存在4种不同的Record，它们分别是1主键索引树非叶节点 2主键索引树叶子节点 3辅助键索引树非叶节点 4辅助键索引树叶子节点。这4种节点的Record格式有一些差异，但是它们都存储着Next指针指向下一个Record。后续我们会详细介绍这4种节点，现在只需要把Record当成一个存储了数据同时含有Next指针的单链表节点即可。  User Record在Page内以单链表的形式存在，最初数据是按照插入的先后顺序排列的，但是随着新数据的插入和旧数据的删除，数据物理顺序会变得混乱，但他们依然保持着逻辑上的先后顺序 把User Record的组织形式和若干Page组合起来，就看到了稍微完整的形式。  现在看下如何定位一个Record： -　1 通过根节点开始遍历一个索引的B+树，通过各层非叶子节点最终到达一个Page，这个Page里存放的都是叶子节点。 -　2 在Page内从"Infimum"节点开始遍历单链表（这种遍历往往会被优化），如果找到该键则成功返回。如果记录到达了"supremum"，说明当前Page里没有合适的键，这时要借助Page的Next Page指针，跳转到下一个Page继续从"Infimum"开始逐个查找。  详细看下不同类型的Record里到底存储了什么数据，根据B+树节点的不同，User Record可以被分成四种格式，下图种按照颜色予以区分。 - **1 主索引树非叶节点（绿色）** +　1 子节点存储的主键里最小的值（Min Cluster Key on Child），这是B+树必须的，作用是在一个Page里定位到具体的记录的位置。 +　2 最小的值所在的Page的编号（Child Page Number），作用是定位Record。 - **2 主索引树叶子节点（黄色）** -　 1 主键（Cluster Key Fields），B+树必须的，也是数据行的一部分 -　 2 除去主键以外的所有列（Non-Key Fields），这是数据行的除去主键的其他所有列的集合。 　　这里的1和2两部分加起来就是一个完整的数据行。 - **3 辅助索引树非叶节点非（蓝色）** -　1 子节点里存储的辅助键值里的最小的值（Min Secondary-Key on Child），这是B+树必须的，作用是在一个Page里定位到具体的记录的位置。　 -　2 主键值（Cluster Key Fields），非叶子节点为什么要存储主键呢？因为辅助索引是可以不唯一的，但是B+树要求键的值必须唯一，所以这里把辅助键的值和主键的值合并起来作为在B+树中的真正键值，保证了唯一性。但是这也导致在辅助索引B+树中非叶节点反而比叶子节点多了4个字节。（即下图中蓝色节点反而比红色多了4字节） -　3 最小的值所在的Page的编号（Child Page Number），作用是定位Record。 - **4 辅助索引树叶子节点（红色）** - 1 辅助索引键值（Secondary Key Fields），这是B+树必须的。 - 2 主键值（Cluster Key Fields），用来在主索引树里再做一次B+树检索来找到整条记录。  下面是本篇最重要的部分了，结合B+树的结构和前面介绍的4种Record的内容，我们终于可以画出一幅全景图。由于辅助索引的B+树与主键索引有相似的结构，这里只画出了主键索引树的结构图，只包含了"主键非叶节点"和"主键叶子节点"两种节点，也就是上图的的绿色和黄色的部分。  把上图还原成下面这个更简洁的树形示意图，这就是B+树的一部分。注意Page和B+树节点之间并没有一一对应的关系，Page只是作为一个Record的保存容器，它存在的目的是便于对磁盘空间进行批量管理，上图中的编号为47的Page在树形结构上就被拆分成了两个独立节  ##### 2.3.5.3.MyISAM ###### 1. 磁盘存储 MyISAM在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。 - .frm：用于存储表的定义。 - .MYD：用于存放数据。 - .MYI：用于存放表索引。 ###### 2. 主键索引 MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。 <img src="https://i.loli.net/2021/01/05/tB8fmYNjlZXcykP.png" alt="image-20210105161444788" style="zoom:50%;" /> MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。 树中叶子保存的是对应行的物理位置。通过该值，存储引擎能顺利地进行回表查询，得到一行完整记录。同时，每个叶子页也保存了指向下一个叶子页的指针。从而方便叶子节点的范围遍历。 ###### 3. 辅助索引 在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。 <img src="https://i.loli.net/2021/01/05/aZsvymQXwDtGJkf.png" alt="image-20210105161456613" style="zoom:50%;" /> ##### 2.3.5.4.Innodb MySQL5.5开始支持InnoDB引擎，并将其作为默认数据库引擎。 ###### 1. 磁盘存储 Innodb有两种存储方式，共享表空间存储和多表空间存储。 Innodb只有表结构文件和数据文件。 表结构文件和MyISAM一样，以表名开头，扩展名是.frm。 数据文件与存储方式有关： - 如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。 - 如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。 ###### 2. 主键索引 Innodb主键索引中，既存储了主键值，又存储了行数据。（聚簇索引）  ###### 3. 辅助索引 对于辅助索引，InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表（上图）查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询，效率肯定是没有按照主键检索高的。  ##### 2.3.5.5.Innodb与MyISAM的区别 | | Innodb索引 | MyISAM索引 | | -------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | | 1.存储结构 | 存储方式不同结构不同 | 三个文件frm（表结构）、MYD（表数据）、MYI（表索引） | | 2.事务支持 | 支持事务 | 不支持事务（MyISAM强调性能） | | 3.外键和主键 | 支持外键（但Innodb必须有主键，若没指定主键，自动生成6字节主键） | 不支持外键 | | 4.锁 | 行级锁（但行级锁只有在where子句是对主键筛选才生效，非主键会锁全表）InnoDB 比较适合于插入和更新操作比较多的情况 | 表级锁(MyISAM 则适用于频繁的查询的情况) | | 5.索引 | Innodb也是用B+树作为索引结构，数据表本身就是按照b+树组织，叶节点key值为数据记录的主键，data域为完整的数据记录，辅助索引data域保存的是数据记录的主键。 | MyISAM使用B+树作为索引结构，叶节点保存的是存储数据的地址，主键索引key值唯一，辅助索引key可以重复，二者在结构上相同。 | | 6.全文检索 | 不支持全文检索 | 支持全文检索 | | 7.表的具体行数 | 不保存表的具体行数，也就是说，执行select count(*) from table 的时候，Innodb要扫描一遍整表来计算 | `select count(*) from table` ，MyISAM 只要简单的读出保存好的行数。因为 MyISAM 内置了一个计数器， count(*) 时它直接从计数器中读。 | #### 2.3.6 覆盖索引介绍 ##### 什么是覆盖索引 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！ ##### 覆盖索引使用实例 现在我创建了索引(username,age)，我们执行下面的 sql 语句 ```sql select username , age from user where username = 'Java' and age = 22 ``` 在查询数据的时候：要查询出的列在叶子节点都存在！所以，就不用回表。 #### 2.3.7 为什么索引能提高查询速度 > 地址： https://juejin.im/post/5b55b842f265da0f9e589e79 ##### 2.3.7.1 MySQL 的基本存储结构 MySQL的基本存储结构是页(记录都存在页里边)：   - **各个数据页可以组成一个双向链表** - **每个数据页中的记录又可以组成一个单向链表** - 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录 - 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。 所以说，如果我们写select * from user where indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做： 1. **定位到记录所在的页：需要遍历双向链表，找到所在的页** 2. **从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了** 很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。 ##### 2.3.7.2 使用索引之后 索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：  要找到id为8的记录简要步骤：  很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 **“目录”** 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)） 其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。 ### 2.4 索引设计原则？ #### 2.4.1 列的离散（sàn）度 第一个叫做**列的离散度**，我们先来看一下列的离散度的公式：count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105144317915.png" alt="image-20210105144317915" style="zoom: 67%;" /> 如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。了解了离散度的概念之后，我们再来思考一个问题，我们在 name 上面建立索引和在 gender 上面建立索引有什么区别。当我们用在 gender 上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在 gender 列上面创建一个索引，然后看一下执行计划。 ```sql ALTER TABLE user_innodb DROP INDEX idx_user_gender; ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久 EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0; ```  而 name 的离散度更高，比如“青山”的这名字，只需要扫描一行。 ```sql ALTER TABLE user_innodb DROP INDEX idx_user_name; ALTER TABLE user_innodb ADD INDEX idx_user_name (name); EXPLAIN SELECT * FROM `user_innodb` WHERE name = '青山'; ```  MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。 #### 2.4.2 联合索引最左匹配 前面我们说的都是针对**单列**创建的索引，但有的时候我们的**多条件查询**的时候，也会建立**联合索引**。单列索引可以看成是特殊的联合索引。比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。 ```sql ALTER TABLE user_innodb DROP INDEX comidx_name_phone; ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone); ``` <img src="https://i.loli.net/2021/01/05/rUTP13aBwJ6EVnz.png" alt="image-20210105144803466" style="zoom:67%;" /> **联合索引**在 B+Tree 中是**复合的数据结构**，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。name 是有序的，phone 是无序的。**当 name 相等的时候，phone 才是有序的。**这个时候我们使用 where name= '青山' and phone = '136xx '去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。 #### 2.4.3 什么时候用到联合索引 建立联合索引的时候，一定要把**最常用**的列放在**最左边**。 - 1）使用两个字段，可以用到联合索引： ```sql EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800'; ```  - 2）使用左边的 name 字段，可以用到联合索引： ```sql EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' ```  - 3）使用右边的 phone 字段，无法使用索引，全表扫描： ```sql EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800' ```  #### 2.4.4 如何创建联合索引 有一天我们的 DBA 找到我，说我们的项目里面有两个查询很慢。 ```sql SELECT * FROM user_innodb WHERE name= ? AND phone = ?; SELECT * FROM user_innodb WHERE name= ?; --按照我们的想法，一个查询创建一个索引，所以我们针对这两条 SQL 创建了两个索引，这种做法觉得正确吗？ CREATE INDEX idx_name on user_innodb(name); CREATE INDEX idx_name_phone on user_innodb(name,phone); ``` 当我们创建一个联合索引的时候，按照**最左匹配原则**，用左边的字段 name 去查询的时候，也能用到索引，所以**第一个索引完全没必要**。相当于建立了两个联合索引(name),(name,phone)。如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：index(a)、index(a,b)、index(a,b,c)用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引的。不能不用第一个字段，不能中断。 #### 2.4.5 覆盖索引 **回表** **非主键索引**，我们先通过索引找到**主键索引的键值**，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多**扫描了一棵索引树**，这个过程就叫回表。例如：select * from user_innodb where name = '青山';  在辅助索引里面，不管是单列索引还是联合索引，**如果 select 的数据列只用从索引中就能够取得**，不必从数据区中读取，这时候使用的索引就叫做**覆盖索引**，这样就**避免了回表**。 ### 2.5 索引的创建与使用 因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。 #### 2.5.1 索引的创建 - 在用于 **where 判断**,**order 排序**和 **join 的（on）**字段上创建索引 - 索引的**个数不要过多**。(浪费空间，更新变慢) - **区分度低的字段**，例如性别，不要建索引(离散度太低，导致扫描行数过多) - **频繁更新**的值，不要作为**主键**或者**索引**(**页分裂**) - 组合索引把**散列性高**（区分度高）的值放在**前面**。 - 创建复合索引，而不是修改单列索引(index(a,b,c)不需要index(a),index(a,b)) ## 3、一条查询SQL语句是如何执行的 <img src="https://i.loli.net/2021/01/04/bWMX1NwJ5Qks8eT.png" alt="image-20210104190448725" style="zoom:50%;" /> ### 3.1 通信协议 MySQL 必须要运行一个服务，监听默认的 3306 端口。 在我们开发系统跟第三方对接的时候，必须要弄清楚的有两件事。 - 第一个就是通信协议，比如我们是用 HTTP 还是 WebService 还是 TCP？ - 第二个是消息格式，比如我们用 XML 格式，还是 JSON 格式，还是定长格式？报文 头长度多少，包含什么内容，每个字段的详细含义。 #### 3.1.1 通信协议 MySQL 是支持多种通信协议的，可以使用**同步/异步**的方式，支持**长连接/短连接**。 这里我们拆分来看。第一个是通信类型。 ##### 通信类型：同步或者异步 **同步通信的特点：** 1. 同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库， 线程会阻塞，等待数据库的返回。 2. 一般只能做到一对一，很难做到一对多的通信 **异步跟同步相反：** 1. 异步可以避免应用阻塞等待，但是不能节省 SQL 执行的时间 2. 如果异步存在并发，每一个 SQL 的执行都要单独建立一个连接，避免数据混乱 异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。一般来说我们连接数据库都是同步连接。 ##### 连接方式：长连接或者短连接 MySQL 既支持短连接，也支持长连接。短连接就是操作完毕以后，马上 close 掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。 ```sql show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序 show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具 默认都是 28800 秒，8 小时。 ``` 查看 MySQL 当前有多少个连接？ <img src="https://i.loli.net/2021/01/04/eKVbvUFqPachC9I.png" alt="image-20210104191312381" style="zoom:50%;" /> - Threads_cached：缓存中的线程连接数。 - Threads_connected：当前打开的连接数 - Threads_created：为处理连接创建的线程数。 - Threads_running：非睡眠状态的连接数，通常指并发连 **MySQL 服务允许的最大连接数是多少呢？** ```sql show variables like 'max_connections'; 在 5.7 版本中默认是 151 个，最大可以设置成 16384（2^14） ``` ##### 通信协议 **第一种是 Unix Socket。** 比如我们在 Linux 服务器上，如果没有指定-h 参数，它就用 socket 方式登录（省略了-S /var/lib/mysql/mysql.sock） <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210104191721330.png" alt="image-20210104191721330" style="zoom:50%;" /> 它不用通过网络协议，也可以连接到 MySQL 的服务器，它需要用到服务器上的一个物理文件（/var/lib/mysql/mysql.sock）。 **第二种方式，TCP/IP 协议** 如果指定-h 参数，就会用第二种方式，TCP/IP 协议。 ```sql mysql -h192.168.8.211 -uroot -p123456 ``` 另外还有命名管道（Named Pipes）和内存共享（Share Memory）的方式，这两种通信方式只能在 Windows 上面使用，一般用得比较少。 #### 3.1.2 通信方式 <img src="https://i.loli.net/2021/01/04/blmYz8rL2XF4qIG.png" alt="image-20210104192008686" style="zoom:50%;" /> ##### 单工(遥控器) 在两台计算机通信的时候，数据的传输是单向的。生活中的类比：遥控器。 ##### 半双工(对讲机) 在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你要给我发的话，也必须等我发给你完了之后才能给我发。生活中的类比：对讲机。 ##### 全双工(打电话) 数据的传输是双向的，并且可以同时传输。生活中的类比：打电话。 **MySQL 使用了半双工的通信方式？** 要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。所以客户端发送 SQL 语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送 ### 3.2 查询缓存 MySQL 内部自带了一个缓存模块。MySQL 的缓存默认是关闭的。默认关闭的意思就是不推荐使用，为什么 MySQL 不推荐使用它自带的缓存呢？ 主要是因为 MySQL 自带的缓存的应用场景有限 - 第一个是它要求 SQL 语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的 SQL。 - 第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合 ### 3.3 语法解析和预处理 #### 3.3.1 词法解析 词法分析就是把一个完整的 SQL 语句打碎成一个个的单词 #### 3.3.2 语法解析 语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）。 <img src="https://i.loli.net/2021/01/04/DloxWu4zCN6yXmV.png" alt="image-20210104193052181" style="zoom: 50%;" /> #### 3.3.3 预处理器 解析 SQL 的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。 ### 3.4 查询优化与查询执行计划 #### 3.4.1 优化器 数据库最终执行的 SQL 是不是就是我们发送的 SQL？这个答案是否定的.查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。 **MySQL 的优化器能处理哪些优化类型呢？** 1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。 2、有多个索引可以使用的时候，选择哪个索引。 ### 3.5 存储引擎 #### 3.5.1 存储引擎的基本介绍 在关系型数据库里面，数据是放在什么结构里面的？（放在表 Table 里面的）我们可以把这个表理解成 Excel 电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。在 MySQL 里面，支持多种存储引擎，他们是可以替换的，所以叫做**插件式的存储引擎**。  每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎。默认情况下，每个数据库有一个自己文件夹 任何一个存储引擎都有一个 frm 文件，这个是表结构定义文件。  #### 3.5.2 存储引擎比较 MyISAM 和 InnoDB 是我们用得最多的两个存储引擎，在 MySQL 5.5 版本之前，默认的存储引擎是 MyISAM，它是 MySQL 自带的。MyISAM 的前身是 ISAM（Indexed Sequential Access Method：利用索引，顺序存取数据的方法）。5.5 版本之后默认的存储引擎改成了 InnoDB，InnoDB 支持事务，支持行级别的锁 ##### （1）MyISAM（3 个文件） 应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中，它通常用于只读或以读为主的工作。 **特点：** - 支持表级别的锁（插入和更新会锁表）。不支持事务。 - 拥有较高的插入（insert）和查询（select）速度。 - 存储了表的行数（count 速度更快）。（怎么快速向数据库插入 100 万条数据？我们有一种先用 MyISAM 插入数据，然后修改存储引擎为 InnoDB 的操作。） 适合：只读之类的数据分析的项目。 ##### （2）InnoDB（2 个文件） mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全（与 ACID 兼容）的 MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁（不升级为更粗粒度的锁）和 Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。 **特点：** - 支持事务，支持外键，因此数据的完整性、一致性更高。 - 支持行级别的锁和表级别的锁。 - 支持读写并发，写不阻塞读（MVCC）。 - 特殊的索引存放方式，可以减少 IO，提升查询效率。 适合：经常更新的表，存在并发读写或者有事务处理的业务系统。 ##### （3）Memory（1 个文件） 将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而 ndbcluster 为大型分布式数据集提供了快速的键值查找。 **特点：** 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。将表中的数据存储到内存中。 ##### （4）CSV（3 个文件） 带有逗号分隔值的文本文件。csv 表允许以 csv 格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为 csv 表没有索引，所以通常在正常操作期间将数据保存在 innodb 表中，并且只在导入或导出阶段使用 csv 表。 **特点：** 不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。 ##### （5）Archive（2 个文件) 这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。 **特点：** 不支持索引，不支持 update delete。 ### 3.6 执行引擎 谁使用执行计划去操作存储引擎呢？这就是我们的执行引擎，它利用存储引擎提供的相应的 API 来完成操作。 为什么我们修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存储引擎实现的 API 是相同的。最后把数据返回给客户端，即使没有结果也要返回。 ## 4、MySQL 体系结构总结 ### 4.1 模块详解  1、 **Connector**：用来支持各种语言和 SQL 的交互，比如 PHP，Python，Java 的JDBC； 2、 **Management Serveices & Utilities**：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等等； 3、 **Connection Pool**：连接池，管理需要缓冲的资源，包括用户密码权限线程等等； 4、 **SQL Interface**：用来接收用户的 SQL 命令，返回用户需要的查询结果 5、 **Parser**：用来解析 SQL 语句； 6、 **Optimizer**：查询优化器； 7、 **Cache and Buffer**：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等； 8、 **Pluggable Storage Engines**：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道。 ### 4.2 架构分层 总体上，我们可以把 MySQL 分成三层，跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层（参考 MyBatis：接口、核心、基础）。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210104195946368.png" alt="image-20210104195946368" style="zoom:50%;" /> #### 4.2.1 连接层 我们的客户端要连接到 MySQL 服务器 3306 端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。 #### 4.2.2 服务层 连接层会把 SQL 语句交给服务层，这里面又包含一系列的流程：比如查询缓存的判断、根据 SQL 调用相应的接口，对我们的 SQL 语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。 然后就是优化器，MySQL 底层会根据一定的规则对我们的 SQL 语句进行优化，最后再交给执行器去执行。 #### 4.2.3 存储引擎 存储引擎就是我们的数据真正存放的地方，在 MySQL 里面支持不同的存储引擎。再往下就是内存或者磁盘。 ## 5、一条更新 SQL 是如何执行的 新流程和查询流程有什么不同呢？基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。区别就在于拿到符合条件的数据之后的操作。 ### 5.1 缓冲池 Buffer Pool 首先，InnnoDB 的**数据都是放在磁盘上**的，InnoDB 操作数据有一个**最小的逻辑单位**，叫做**页**（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为**磁盘的速度太慢了**。InnoDB 使用了一种**缓冲池的技术**，也就是把磁盘**读到的页**放到一块内存区域里面。这个内存区域就叫 **Buffer Pool**。 <img src="https://i.loli.net/2021/01/05/rcVsTkJImHuQwi5.png" alt="image-20210105095150977" style="zoom:67%;" /> 下一次**读取**相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。 **修改数据**的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据**不一致**的时候，我们把它叫做**脏页**。InnoDB 里面有**专门的后台线程**把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做**刷脏**。 ### 5.2 InnoDB 内存结构 <img src="https://i.loli.net/2021/01/05/bPHs8936Cr7dlqz.png" alt="image-20210105095635490" style="zoom:67%;" /> Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer。 #### 5.2.1 Buffer Pool Buffer Pool 缓存的是**页面信息**，包括**数据页**、**索引页**。Buffer Pool 默认大小是 128M（134217728 字节），可以调整。***InnoDB 用 LRU算法来管理缓冲池（链表实现，不是传统的 LRU，分成了 young 和 old），经过淘汰的*** ***数据就是热点数据。***内存缓冲区对于提升读写性能有很大的作用。当需要更新一个数据页时，如果数据页在 Buffer Pool 中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，有没有优化的方式呢？ ```sql SHOW STATUS LIKE '%innodb_buffer_pool%'; --查看服务器状态，里面有很多跟 Buffer Pool 相关的信息： ``` <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105095934305.png" alt="image-20210105095934305" style="zoom:67%;" /> #### 5.2.2 Change Buffer 写缓冲 如果这个数据页**不是唯一索引**，**不存在数据重复**的情况，也就**不需要从磁盘加载**索引页判断数据是不是重复（**唯一性检查**）。这种情况下可以**先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）执行速度。**[这一块区域就是 Change Buffer。5.5 之前叫 Insert Buffer 插入缓冲，现在也能支持 delete 和 update。] 最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge？有几种情况： 1. 在访问这个数据页的时候 2. 或者通过后台线程、或者数据库 shut down、 3. redo log 写满时触发。 如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。写多读少的业务，调大这个值： ```sql SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'; --代表 Change Buffer 占 Buffer Pool 的比例，默认 25%。 ``` #### 5.2.3 Adaptive Hash Index 索引应该是放在磁盘的，为什么要专门把一种哈希的索引放到内存? #### 5.2.4 （redo）Log Buffer 如果 Buffer Pool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。为了避免这个问题，**InnoDB 把所有对页面的修改操作**专门写入一个**日志文件**，并且在数据库启动时从这个文件进行**恢复操作**（实现 **crash-safe**）——用它来实现**事务的持久性**。 <img src="https://i.loli.net/2021/01/05/CUFZa21oVHIerku.png" alt="image-20210105102441327" style="zoom:50%;" /> 这个文件就是磁盘的 **redo log**（叫做**重做日志**），对应于**/var/lib/mysql/目录下的ib_logfile0 和 ib_logfile1，每个 48M**。这 种 日 志 和 磁 盘 配 合 的 整 个 过 程 ， 其 实 就 是 MySQL 里 的 **WAL 技 术**（Write-Ahead Logging），它的关键点就是**先写日志，再写磁盘**。 同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？我们先来了解一下**随机 I/O** 和**顺序 I/O** 的概念。 - 磁盘的最小组成单元是**扇区**，通常是 512 个字节。 - 操作系统和内存打交道，最小的单位是**页 Page**。 - 操作系统和磁盘打交道，读写磁盘，最小的单位是**块 Bloc**k。 <img src="https://i.loli.net/2021/01/05/26pTWlG87iZ5tbo.png" alt="image-20210105102725157" style="zoom:67%;" /> > 如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是**随机 IO**，读取数据速度较慢。 > > 假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫**顺序 IO** **刷盘**是**随机 I/O**，而**记录日志**是**顺序 I/O**，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。当然 redo log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，默认 16M，它一样可以节省磁盘 IO。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105103436854.png" alt="image-20210105103436854" style="zoom:50%;" /> 需要注意：**redo log 的内容**主要是用于**崩溃恢复**。磁盘的数据文件，数据来自 bufferpool。redo log 写入磁盘，不是写入数据文件。在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓log buffer 写入磁盘的时机，由一个参数控制，默认是 1 ```sql SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; ``` <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105103647785.png" alt="image-20210105103647785" style="zoom:67%;" /> <img src="https://i.loli.net/2021/01/05/p4CRN1FyjALaMeX.png" alt="image-20210105103726011" style="zoom:67%;" /> 这是内存结构的第 4 块内容，redo log，它又分成**内存**和**磁盘**两部分。redo log 有什么特点？ 1. redo log 是 **InnoDB 存储引擎实现的**，并不是所有存储引擎都有。 2. 不是记录数据页更新之后的状态，而是记录这个页**做了什么改动**，属于**物理日志**。 3. redo log 的**大小是固定**的，**前面的内容会被覆盖**。 <img src="https://i.loli.net/2021/01/05/YQDboOqmvASt2UJ.png" alt="image-20210105103945181" style="zoom:50%;" /> check point 是当前要覆盖的位置。如果 write pos 跟 check point 重叠，说明 redolog 已经写满，这时候需要同步 redo log 到磁盘中。 ### 5.3 InnoDB 磁盘结构 表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB 的表空间分为 5 大类。 #### 5.3.1 系统表空间 system tablespace 在默认情况下 InnoDB 存储引擎有一个**共享表空间**（对应文件/var/lib/mysql/ibdata1），也叫**系统表空间**。InnoDB 系统表空间包含 InnoDB **数据字典**和**双写缓冲区**，(Change Buffer 和 Undo Logs），如果没有指定 file-per-table，也包含**用户创建的表和索引数据**。 1. undo 在后面介绍，因为有独立的表空间 2. 数据字典：由内部系统表组成，存储表和索引的元数据 3. 双写缓冲（InnoDB 的一大特性） `InnoDB 的页`和`操作系统的页`**大小不一致**，InnoDB 页大小一般为 **16K**，操作系统页大小为 **4K**，**InnoDB 的页写入到磁盘时，一个页需要分 4 次写**。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105104925870.png" alt="image-20210105104925870" style="zoom:67%;" /> 如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4K，就宕机了，这种情况叫做**部分写失效（partial page write）**，可能会导致数据丢失。我们不是有 redo log 吗？但是有个问题，**如果这个页本身已经损坏了**，用它来做崩溃恢复是没有意义的。所以在对于应用 redo log 之前，需要一个**页的副本**。如果出现了写入失效，就用页的副本来还原这个页，然后再应用 redo log。这个页的副本就是 **double write，InnoDB 的双写技术**。通过它实现了数据页的可靠性。 跟 redo log 一样，double write 由两部分组成，一部分是内存的 double write，一个部分是磁盘上的 double write。因为 double write 是顺序写入的，不会带来很大的开销。在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。 #### 5.3.2 独占表空间 file-per-table tablespaces 我们可以让**每张表独占一个表空间**。这个开关通过 innodb_file_per_table 设置，默认开启。 开启后，则每张表会开辟一个表空间，这个文件就是数据目录下的 ibd 文件（如/var/lib/mysql/gupao/user_innodb.ibd），存放表的索引和数据。但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。 #### 5.3.3 通用表空间 general tablespaces 通用表空间也是一种**共享的表空间**，跟 ibdata1 类似。可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。语法： ```sql create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb; ``` 在创建表的时候可以指定表空间，用 ALTER 修改表空间可以转移表空间。 ```sql create table t2673(id integer) tablespace ts2673; ``` 不同表空间的数据是可以移动的。 删除表空间需要先删除里面的所有表： ```sql drop table t2673; drop tablespace ts2673; ``` #### 5.3.4 临时表空间 temporary tablespaces 存储临时表的数据，包括**用户创建的临时表**，和**磁盘的内部临时表**。对应数据目录下的 ibtmp1 文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生。 #### 5.3.5 undo log tablespace undo log（**撤销日志或回滚日志**）记录了**事务发生之前**的**数据状态**（不包括 select）。如果修改数据时出现异常，可以用 undo log 来实现**回滚操作**（**保持原子性**）。执行 undo 的时候，仅仅是将数据从**逻辑上**恢复至事务之前的状态，而不是从物理页面上操作实现的，属于**逻辑格式**的日志。 **redo Log 和 undo Log 与事务密切相关，统称为事务日志。** undo Log 的数据默认在系统表空间 ibdata1 文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。`show global variables like '%undo%';` #### 5.3.6 更新总结 总结一下一个更新操作的流程，这是一个简化的过程。name 原值是 qingshan。 ```sql update user set name = 'penyuyan' where id=1; ``` 1、事务开始，从内存或磁盘取到这条数据，返回给 Server 的执行器； 2、执行器修改这一行数据的值为 penyuyan； 3、记录 name=qingshan 到 undo log； (用于回滚) 4、记录 name=penyuyan 到 redo log；（用于恢复） 5、调用存储引擎接口，在内存（Buffer Pool）中修改 name=penyuyan； 6、事务提交。 内存和磁盘之间，工作着很多后台线程。 ### 5.4 后台线程 后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread，IO thread，purge thread，page cleaner thread。 - **master thread** 负责刷新缓存数据到磁盘并协调调度其它后台进程。 - **IO thread** 分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer、重做日志、读写请求的 IO 回调。 - **purge thread** 用来回收 undo 页。 - **page cleaner thread** 用来刷新脏页。 ### 5.5 Bin Log 除了 InnoDB 架构中的日志文件，MySQL 的 Server 层也有一个日志文件，叫做binlog，它可以被所有的存储引擎使用。binlog 以**事件的形式**记录了所有的 **DDL** 和 **DML** 语句（因为它记录的是操作而不是数据值，属于**逻辑日志**），可以用来做**主从复制**和**数据恢复**。 跟 redo log 不一样，它的**文件内容是可以追加的，没有固定大小限制**。在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把**所有的操作重放一遍**，来实现**数据的恢复**。binlog 的另一个功能就是用来实**现主从复制**，它的原理就是从服务器读取主服务器的 binlog，然后执行一遍。 有了这两个日志之后，我们来看一下一条更新语句是怎么执行的：  > 例如一条语句：update teacher set name='盆鱼宴' where id=1; > 1、先查询到这条数据，如果有缓存，也会用到缓存。 > 2、把 name 改成盆鱼宴，然后调用引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。 > 3、执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log 为 commit状态。 > 4、更新完成。 这张图片的重点（没必要背下来）： - 先记录到内存，再写日志文件。 - 记录 redo log 分为两个阶段。 - 存储引擎和 Server 记录不同的日志 - 先记录 redo，再记录 binlog。