MySql篇

本次主要针对使用InnoDB存储引擎的MySQL数据库做分析。

1、关系型数据库和非关系型数据库

1.1、关系型数据库（SQL）

表和表、表和字段、数据和数据存在着关系。

  • 优点:

    1. 数据之间有关系，进行数据的增删改查的时候是非常方便的。
    2. 关系型数据库是有事务操作的，保证数据的完整性和一致性。

  • 缺点：

    1. 因为数据和数据是有关系的，底层是运行了大量的算法，大量算法会降低系统的效率，会降低性能。
    2. 面对海量数据的增删改查的时候会显的无能为力。
    3. 海量数据对数据进行维护变得非常的无力。

1.2、非关系型数据库（NoSQL）

为了处理海量数据，非关系数据库设计之初就是为了替代关系型数据库的关系

  • 优点：
    1. 海量数据的增删改查是可以的。
    2. 海量数据的维护和处理非常轻松。
  • 缺点：
    1. 数据和数据没有关系，他们之间就是单独存在的。
    2. 非关系数据库没有关系，没有强大的事务关系，没有保证数据的完整性和安全性。

2、MySQL的结构

MySQL逻辑架构图：

img

MySQL 可以分为 Server 层和存储引擎层两部分。

2.1、server层

  1. 连接器：负责跟客户端建立连接、获取权限、维持和管理连接。

    • 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
    • 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

  2. 查询缓存：之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中（当表中有更新的时候缓存会被清空）。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。8.0版本开始删除了这个功能。

  3. 分析器：分析SQL字符的含义和SQL语法是否正常。

    1. 词法分析：你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

      例如：mysql> select * from T where ID=10；它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

    2. 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

  4. 优化器：在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

  5. 执行器：开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

2.2、存储引擎层

常见的两种引擎：

  • MyISAM
  • InnoDB

3、数据库表设计

3.1、三范式

  1. 第一范式（1NF）：列不可再分
    • 每一列属性都是不可再分的属性值，确保每一列的原子性。
    • 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
  2. 第二范式（2NF）：属性完全依赖于主键
    • 满足第一范式（1NF）的基础上，要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主键。
  3. 第三范式（3NF）：属性不依赖其他属性，只能直接依赖主键
    • 满足第二范式（2NF）的基础上，要求数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

4、数据库引擎

数据库引擎就是驱动各种数据库的程序，它负责处理数据库相关工作的整个核心部份。同样的，数据库应用项目的操作指令，均会通过数据库引擎的处理作用到数据库上。

存储引擎是针对于表的。

4.1、MyISAM

在MySQL 5.5.8版本以前，MyIASM是MySQL默认的存储引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。它的缓冲只缓冲索引文件，而不缓冲数据文件。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

img

适合场景：

  • 做很多count 的计算；
  • 插入不频繁，查询非常频繁；
  • 没有事务。

4.2、InnoDB

MySQL 5.5.8开始，默认的存储引擎为InnoDB。Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，支持一致性的非锁定读，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

Innodb引擎的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+ Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

主键索引：

img

辅助索引（非主键索引）：

img

联合索引：

它的辅助索引(Secondary Index，也就是非主键索引)也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

适合场景：

  • 可靠性要求比较高，或者要求事务；
  • 表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
  • 大尺寸的数据集趋向于选择InnoDB引擎，因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。

4.3、区别

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

  • InnoDB不支持FULLTEXT类型的索引；
  • InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的；
  • 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
  • DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除；
  • LOAD TABLE FROMMASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
  • 主索引的区别，InnoDB的数据文件本身就是索引文件，而MyISAM的索引和数据是分开的。
  • 辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

细则区分：

立项 MyISAM InnoDB 注意
MySQL默认引擎 5.5.8之前，为默认引擎 5.5.8及之后，为默认引擎 创建每个表格的代码是相同的，除了最后的 TYPE参数，这一参数用来指定数据引擎。
事务 不支持（可以通过表锁来伪实现事务的行为） 支持 事务提供了commit、rollback、崩溃修复的能力，能很好的处理系统异常崩溃的情况，并保护文件免损伤；
事务非常耗费性能，通常建议对一致性要求较高的业务使用复杂事务。
外键 不支持 支持 在高并发的情况下，建议有应用程序保证完整性，而不使用外键
索引 非聚集索引（索引文件与数据文件分离） 聚集索引 MyISAM内存空间使用率比InnoDB低
索引结构 B+Tree B+Tree
文件存储格式 .frm是表结构文件
.MYD（my data）是数据文件
.MYI（my index）是索引文件		 .frm是表结构文件
.ibd是数据文件
(innoDB的索引文件和数据文件是绑定在一起的)
索引文件存储特点 MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引 InnoDB（索引组织表）使用的聚簇索引、索引就是数据，顺序存储，因此能缓存索引，也能缓存数据
磁盘文件存储特点 MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦 InnoDB，基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小
索引文件描述 索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。
但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
因此，主键不应该过大，因为主键太大，其他索引也都会很大。
索引B+树的区别 B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。 B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；
索引B+树的区别图 img img
表的总行数 用一个变量保存了整个表的行数 不保存 当count(*)语句包含 where条件时，两种表都需要逐行扫描。
fulltext全文索引 支持（查询效率上MyISAM要高） 5.7及以后的支持 在数据量并发大的情况下，使用全文索引会导致小量请求占用大量数据库资源，所以在高并发的业务下，全文索引并不索引。
对表压缩后，查询 可以 不可以
锁 支持表级锁 支持表、页、行(默认)级锁
锁说明 对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；
对MyISAM表进行写操作时，它会阻塞其他用户对同一表的读和写操作。		 InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！

行级锁每次获取锁和释放锁的操作需要消耗比表锁更多的资源。

在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。

当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。
行锁是实现在索引上的，而不是锁在物理行记录上（潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。）
主键 可以没有 必须有（用户没有指定的话MySQL会自己给表维护一行主键列）
auto_increment类型字段 可以和其他字段一起建立联合索引 必须包含只有该字段的索引
清空表 会重建表 一行一行的删除（效率非常慢）

5、事务管理

事务是一组SQL语句，一个最小的不可再分的工作单元，要么全部执行成功，要么全部执行失败。

5.1、事务原则（ACID原则）

  • 原子性（A）：事务是最小单位，不可再分。事务中的操作要么都成功，要么都失败。
  • 一致性（C）：事务前后数据的完整性必须保持一致。
  • 隔离性（I）：一个事务不能被另一个事务干扰，多个事物之间相互隔离。
  • 持久性（D）：事务所对数据库所作的更改永久保存在数据库之中，不会被回滚。事务终结的标志（内存的数据持久到硬盘文件中）。

5.2、四种隔离级别

  1. Read uncommitted（读未提交）：如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读。脏读就是说事务B读取到了事务A未提交的数据。解决了更新丢失，但还是可能会出现脏读。
  2. Read committed（读提交）：如果是一个读事务（线程），则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。不可重复读就是事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。解决了更新丢失和脏读问题。
  3. Repeatable read（可重复读）：可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务（包括了读写），这样避免了不可重复读和脏读，但是有时可能会出现幻读。幻读就是B事务在A事务中读取数据时修改了表中的数据，A事务并不能读到修改内容，在此之间A事务读到的数据都是虚幻的。（读取数据的事务）可以通过“共享读锁”和“排他写锁”实现。解决了更新丢失、脏读、不可重复读、但是还会出现幻读。
  4. Serializable（串行化）：提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用。在该级别下，事务顺序执行，还可以避免脏读、不可重复读、幻读。

img

5.3、MVCC模型

全称Multiversion concurrency control，多版本并发控制，提供并发访问数据库时，对事务内读取的到的内存做处理，用来避免写操作堵塞读操作的并发问题。

使用了双版本号来解决数据的隔离问题。（create一个版本号，delete一个版本号，修改操作拆分为delete和create）每个事务在开始对每张表增删改查操作时都会生成一个版本号，每个事务只能查到create小于本版本号和delete大于本版本号的数据。这样，增删查操作就完全可以并发进行了，只有修改操作是一定要排队的。这样，就算没有共享锁也解决了不可重复读问题，因为其他事务修改后，数据的版本号比我大，我不会读到。

  • 当前读

    读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。（select lock in share mode (共享锁)，select for update; update; insert; delete; (排他锁)这些操作都是一种当前读）

  • 快照读

    前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC，可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

说白了 MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读，而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

5.3.1、当前读、快照读和MVCC关系

MVCC：多版本并发控制是维持一个数据的多个版本，使得读写操作没有冲突的概念，只是一个抽象概念，并非实现。

当前读：当前读就是悲观锁的具体功能实现。

快照读：快照读是MySQL实现MVCC理想模型的其中一个非阻塞读功能，快照读本身也是一个抽象概念，再深入研究。MVCC 模型在 MySQL 中的具体实现则是由 3 个隐式字段，undo 日志，Read View 等去完成的。

5.3.2、数据库三种并发场景

  • 读-读：不存在任何问题，也不需要并发控制。
  • 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读。
  • 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失。

不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了 MVCC，所以我们可以形成两个组合：

  • MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突。
  • MVCC + 乐观锁：MVCC 解决读写冲突，乐观锁解决写写冲突。

5.3.3、MVCC实现原理三核心

  1. 隐藏字段：DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID，而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本。

    在这里插入图片描述

  2. undo日志：

    • insert undo log：代表事务在 insert 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
    • update undo log：事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除。

  3. Read View：是事务进行快照读操作的时候生产的读视图（Read View），在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID （当每个事务开启时，都会被分配一个 ID，这个 ID 是递增的，所以最新的事务，ID 值越大）。

6、数据页与数据区

6.1、数据页

InnoDB从磁盘中读取数据的最小单位是数据页。

两个数据页之间会有指针指向上一个和下一个数据页，形成一个双向链表，在数据页中存储的就是一行行的数据了，每个数据行之间会有单向指针连接，组成一个单向链表。

当一个数据页中的数据行太多放不下的下，就会生成一个新的数据页来存储，同时使用双向链表来相连。

img

6.1.1、数据页分裂

当我们使用索引的时候，有一个最基础的条件就是后面数据页中的数据行的主键值需要大于前一个数据页中数据行的主键值，至于为什么要这么做，其实索引简单来说，其实就是通过二分法不断减少要筛选的数据，而主键值就是筛选的标准，以尽快定位到我们需要的数据。页分裂的目的就是保证后一个数据页中的所有行主键值比前一个数据页中主键值大，其实就是为了保证索引的顺序性。

页分裂前：img

页分裂后：img

6.1.2、数据页合并

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

6.2、数据区

在MySQL的设定中，同一个表空间内的一组连续的数据页为一个extent（区），默认区的大小为1MB，页的大小为16KB。16*64=1024，也就是说一个区里面会有64个连续的数据页。连续的256个数据区为一组数据区。

img

6.3、缓存区

6.3.1、change buffer更新缓存区

当需要更新一个数据页时（更新数据页，更新索引页），如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将数据页加载到内存更新，而更新数据的索引的更新操作将缓存在 change buffer 中减少磁盘的IO。在下次查询需要访问这个数据页的时候，将数据页读入内存，在将change buffer中的操作merge到原有的数据页。过程称为 merge，系统有后台线程会定期 merge、在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。简单来说就是把写的操作记录存在在缓存区中，下次查询时合并，延时写的操作就，减少了更新数据的磁盘IO操作。

6.3.1.1、使用场景

写多读少的业务场景（账单类、日志类的系统）change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

6.3.2、Double writer Buffer

用于数据页中的脏数据刷入磁盘中，为什么设计这块物理缓冲区，因为操作系统最小单位为4kb，我们数据页为16kb会写入4次。如果写入2次后操作系统崩了，恢复数据过程为表空间数据+redolog日志恢复最新数据在通过Double writer Buffer重新写入表空间，当某个数据页全写入double writer buffer后会清楚redolog日志空间。

7、索引

7.1、索引类型

  • 主键索引（PRIMARY KEY）：不允许重复，不允许空值。
  • 唯一索引（UNIQUE）：不允许重复，允许空值。
  • 普通索引（INDEX）：基本的索引，没有任何限制。
  • 全文索引（FULLTEXT INDEX）：用大文本对象的列构建的索引。
  • 组合索引（INDEX）：用多个列组合构建的索引，这多个列中的值不允许有空值。

7.1.1、主键索引和唯一索引

  • 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
  • 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
  • 唯一性索引列允许空值，而主键列不允许为空值。
  • 主键可以被其他表引用为外键，而唯一索引不能。
  • 一个表最多只能创建一个主键，但可以创建多个唯一索引。

7.1.2、主键索引和普通索引

img

回表：主键索引中存储的是整行数据，而普通索引只存储了主键Id，所以普通索引走了自身的B+索引树后还会走一次主键的B+索引树，这个过程就是回表。

7.1.3、普通索引和唯一索引

7.1.3.1、读操作
  • 普通索引：查找到主键索引后回表找到满足条件的第一个记录 后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
  • 唯一索引：由于索引定义了唯一性，查找到主键索引后回表查找到第一个满足条件的记录后，就会停止继续检索。
7.1.3.2、写操作
  • 写记录的目标页（数据页）在内存中
    • 唯一索引：找到要变更行位置，判断唯一约束，插入这个值，语句执行结束。
    • 普通索引：找到要变更行位置，插入这个值，语句执行结束。
  • 写记录的目标页（数据页）不在内存中
    • 对于唯一索引来说，需要将数据页读入内存，判断唯一约束，插入这个值，语句执行结束。
    • 对于普通索引来说，则是将更新记录在 change buffer（更新缓冲区），语句执行结束了。

7.2、索引模型

7.2.1、hash（散列）结构

哈希表这种结构适用于只有等值查询的场景，不适用于范围查找。

img

7.2.2、数组结构

有序数组索引只适用于静态存储引擎。因为修改数据需要更新索引，更新慢查找快也是数组结构的特点，所以修改数据导致索引的维护代价非常大。

img

7.2.3、二叉搜索树

树越高，与磁盘的I/O操作就越多，代价就越大，并且当插入数据的方式本身就是有序时会导致二叉树退化为一个链表。

img

7.2.4、B树（B-Tree）

因为每个节点存放的不止索引，还有数据，这就导致了占用过多的占用了空间。

在这里插入图片描述

7.2.5、B+树（B+Tree）

现目前索引的数据结构就是B+Tree，B+Tree相对于B-Tree，有两个区别

  • 非节点只存放索引key，不存放数据，数据只存在于叶子节点。
  • 叶子节点页之间使用链表连接。

img

7.3、MySQL中B+Tree数据结构详解

特点：

  • 非叶子节点不存储数据，只存储索引（冗余），可以放更多的索引。
  • 叶子节点包含所有索引字段。
  • 叶子节点用指针连接，提高区间访问的性能。

image-20220216114957932

InnoDB中一颗B+树可以存放约2千万行的数据。

7.4、索引查询过程

7.4.1、InnoDB引擎中索引的查找过程

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

  1. 在MySQL初始化之后会将索引B+树上的非叶子节点加载到内存中。
  2. 根据折半法（二分法）比对大小从根节点查找这个索引值。
  3. 如果没找到到将节点的数据区间指向的下一个节点去找。
  4. 依次定位到我们要找的元素。
  5. 最终获取到B+树叶子节点上的数据。

7.5、索引优化

  • 覆盖索引：简单来说就是索引搜索树中已经满足了查询所需要的数据，不需要在回表查询减少了IO次数。
  • 联合索引：最左匹配原则。

7.5.1、联合索引

包含两列或更多列以上的索引，称为联合索引；同时又被称为复合索引。

优点：

  • 减少开销：建一个联合索引，实际相当于建了多个单独的索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
  • 覆盖索引：如果有如下的sql：select Gid, Cid, SId from student where Gid = 1 and Cid = 2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。
  • 效率高：索引列越多，通过索引筛选出的数据越少。

缺点：修改数据需要更新索引，同样是需要花时间的，并且索引所占的磁盘空间也不小。

建议：单表尽可能不要超过一个联合索引，单个联合索引不超过3个字段。

7.5.2、最左前缀原则

而最左原则的原理就是，因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

联合索引中以最左边的为起点任何连续的索引都能匹配上，否则会导致查询语句中的索引失效。

where子句的顺序问题不会造成索引失效，如索引是 (name, cid)，条件语句是 cid=1 AND name=’小红’。

注意：

  • 范围查询：mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
  • like语句：如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀。在like “value%”可以使用索引，但是like “%value%”不会使用索引，走的是全表扫描。

7.5.3、Explain分析语句

列名 描述
id 查询语句中没出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值，某些子查询会被转化为join查询，那么出现的id会一样
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的查询方式（全表扫描、索引）
possible_keys 可能会用到的索引
key 实际上用到的索引
key_len 实际使用到的索引长度
ref 当时用索引的等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外信息，如排序等

7.6、索引维护

image-20220217135113385

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

7.7、索引失效

  • 走索引比全局扫描更加耗时mysq会放弃走索引，列如通过索引范围查询时回表次数过多导致索引失效，毕竟索引只是加快查询效率的一种手段而已，如果查询比全表扫描慢那么为什么要走索引。
  • 破坏了索引的有序性导致导致无法走索引：
    • Mysql函数：索引字段使用了Mysql的函数破坏了索引的有序性导致Mysql优化器放弃了走树的搜索功能。
    • 隐式类型转换：sql条件等号2表是字符和数字，会字符转数字，其实也是用了CAST函数与上一致，列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

7.8、三星索引

  • 一个查询相关的索引行是相邻的或者至少相距足够靠近的则获得一星。
  • 如果索引中的数据顺序和查找中的排序顺序一致则获得二星。
  • 如果索引中的列包含了查询中需要的全部列则获得三星。

三星索引在实际的业务中如果无法同时达到，一般认为第三颗星最重要，第一和第二星重要性差不多，根据业务情况调整这两颗星的优先度。

7.9、主键索引三问

  1. 为什么非主键索引结构叶子节点存储的是主键值？
    • 保证一致性，更新数据的时候只需要更新主键索引树。
    • 节省存储空间。
  2. 为什么推荐InnoDB表必须有主键？
    • 保证会有主键索引树的存在（因为数据存放在主键索引树上面），如果没有mysql会自己生成一个rowid作为自增的主键主键索引，相比于mysql去维护这列主键列带来的开销不如由我们自己来维护以减少额外开销。
  3. 为什么推荐使用整型的自增主键？
    • 方便查找比较。
    • 新增数据的时候只需要在最后加入，不会大规模调整树结构，如果是UUID的话，大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢。

8、日志

  • 重做日志（redo log）
  • 回滚日志（undo log）
  • 二进制日志（binlog）
  • 错误日志（errorlog）
  • 慢查询日志（slow query log）
  • 一般查询日志（general log）
  • 中继日志（relay log）

8.1、三种日志详解（redo log、undo log、binlog）

8.1.1、redo log（重做日志）

InnoDB引擎的特有日志。属于物理日志。

  • 作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
  • 内容：物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。
  • 什么时候产生：事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。
  • 什么时候释放：当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
  • 有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。

8.1.2、undo log（回滚日志）

InnoDB引擎的特有日志。属于逻辑日志。

  • 作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
  • 内容：逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。
  • 什么时候产生：事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性
  • 什么时候释放：当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

8.1.3、binlog（归档日志）

MySQL Server层的日志。属于逻辑日志。

  • 作用：
    • 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
    • 用于数据库的基于时间点的还原。
  • 内容：逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。在使用mysqlbinlog解析binlog之后一些都会真相大白。因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。
  • 什么时候产生：事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。
  • 什么时候释放：binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

8.2、区别

  • 作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。
  • 内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句。
  • 另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。
  • 恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog。
  • 关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

img

9、两阶段提交

9.1、两阶段提交说明

428cac47256e821f4eb2633f6f17af65.png

从流程图中可以看出，在 InnoDB 存储引擎下，一条 update 语句在 MySQL 内部执行大概会经历下面五个步骤：

  1. 执行器先找引擎取 id=2 这一行数据，如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交(commit)状态，更新完成。

在这五步中，注意加粗标出来的部分，redo 日志被分割成 prepare 和 commit 两个阶段提交，这个过程称为两阶段提交。

9.2、不将 redo 日志拆分成两步提交行不行？

我们可以用反推法来证明，假设不使用两阶段提交，那么就有两种情况，一种是先提交 redo 日志再提交 binlog 日志，另一种是先提交 binlog 日志再提交 redo 日志，一起来看看这两种提交方式有什么问题？

先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。在这个过程中更新发生了异常，redo 日志是可以在数据库发生异常是保证数据的持久性，启动后经过 redo 日志数据恢复后 c 的值是 1，但是 binlog 并没有写完，所以在 binlog 日志文件中并没有记录这条更新语句，如果用这个 binlog 日志文件来恢复临时库的话，恢复出来 id =2 的这行数据的 c 的值为 0，与原库的值就不一致了。

先写 binlog 后写 redo log。如果在 binlog 写完，redo 日志还没写，系统崩溃，系统重启后，id=2 的这行数据的 c 的值还是为 0，但是在 binlog 日志文件中却记录了这次更新，如果需要用 binlog 日志文件来恢复临时库的话，那么 id=2 的这行数据 c 的值就为 1，这样与原库的值就不一致了。

从这两个假设中，我们可以看出无论先提交那个日志文件都有可能出现数据不一致的现象，日志文件两阶段提交技术就解决了redo 日志和 binlog 日志文件记录数据不一致的问题，从而保证了在数据恢复时数据的一致性。

10、锁

MySQL锁进行划分：

  • 乐观锁（version）、悲观锁（排它锁）

  • 按照锁定粒度区分：表锁（意向锁、自增锁）、行锁（间隙锁、临建锁、记录锁）

  • 按照锁的方式区分: 排他锁（写锁）、共享锁（读锁）

  • IX：意向锁 X：间隙锁

10.1、三种锁类型

10.1.1、全局锁

对整个数据库实例加锁。

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

Flush tables with read lock与set global readonly=true的区别：

  • 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大。
  • 在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

10.1.2、表级锁

MySQL里面表级别的锁有两种：

  • 表锁：线程A中执行lock tables t1 read,t2 wirte;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许。
  • 元数据锁（meta data lock，MDL）：不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行。

10.1.3、行级锁

是在引擎层由各个引擎自己实现的。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

10.1.3.1、两阶段提交协议

img

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

10.2、死锁和死锁检测

10.2.1、死锁

在并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

img

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

  • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置
  • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

10.2.2、检测死锁

如果所有事务都要更新同一行的场景，每个新来的被堵住的线程都要判断会不会由于自己的加入导致死锁。解决由这种热点行更新导致的性能问题方法：

  • 如果确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

  • 控制并发度。

  • 将一行改成逻辑上的多行来减少锁冲突。以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成员原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。

11、集群

11.1、Mysql主从架构

主从之间是通过mysql的replication来保证数据的一致性。相对mysql cluster的数据同步方式来讲是异步的。
Replication：主节点要开启binlog，设置一个唯一的服务器id（局域网内唯一）；从节点设置服务器id，binlog记录了master上的所有操作，会被复制到从节点的relaylog并在从节点上回放。

img

主从复制类型：

  1. 基于语句的复制（statement）
    在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
  2. 基于行的复制（row）
    把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。
  3. 混合类型的复制（mixed）
    默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

12、扩展

12.1、count()最优

首先count(*)、count(id) 和 count(1) 都表示返回满足条件的结果集的总行数；

至于分析性能差别的时候，你可以记住这么几个原则：

  1. server 层要什么就给什么。
  2. InnoDB 只给必要的值。
  3. 现在的优化器只优化了count(*)的语义为取行数。
  • count(id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
  • count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字1进去，判断是不可能为空的，按行累加。
  • count(*)： 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
  • count(col)：则表示返回满足条件的数据行里面，参数col不为 NULL 的总个数。

性能比较：count(col) < count(id) < count(1) ≈ count(*)，但是MySQL目前只对count(*)进行了优化，因此建议使用count(*)。

12.2、给字符串添加索引

  • 直接创建完整索引

    alter table Customer add index index1(email);

    缺点：受字符串的长度影响，可能比较占用空间。

  • 创建前缀索引

    alter table Customer add index index1(email(6));

    优点：只取前n个字符作为索引，与创建完整索引相比，节约空间。

    缺点：增加查询扫描次数，并且不能使用覆盖索引，每次等值查询都会回表操作。

  • 倒序存储，再创建前缀索引

    缺点：在读写时要进行reverse函数（倒过来）。不支持范围扫描。

  • 通过mysql的哈希函数创建hash字段索引

    优点：查询性能稳定，有额外的存储和计算消耗。

    缺点：不支持范围扫描。

posted @ 2022-02-17 21:15  是老胡啊  阅读(25)  评论(0)    收藏  举报