MySQL逻辑架构

image.png
image.png
image.png

Connectors

MySQL服务器之外的客户端程序。MySQL首先是一个网络程序，在TCP之上定义了自己的应用层协议。所以要使用MySQL，我们需要建立TCP连接，之后按照其定义好的协议进行交互。

第一层：连接层

系统(客户端)访问MySQL服务器前，做的第一件事就是建立TCP连接。
经过三次握手建立连接成功后，MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行。
用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。
为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接。
TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池(connection pool)去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。
注：Management Services & Utilities : 基础服务组件

第二层：服务层

image.png
SQL Interface

SQL接口，接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQLInterface。MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口。

Parser

解析器，在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树 ，并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。

Optimizer

查询优化器，SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。
这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤，调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
它使用 “选取-投影-连接” 策略进行查询。例如：

  1. SELECT id,name FROM student WHERE gender = '女';

这个SELECT查询先根据WHERE语句进行选取 ，而不是将表全部查询出来以后再进行过滤。然后根据id 和 name进行属性投影(类似于取出)，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。

Caches & Buffers

查询缓存组件，MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。这个查询缓存可以在 不同客户端之间共享 。
从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。

关于为什么缓存被删除了

MySQL的缓存是：之前执行过的语句和结果以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。
需要说明的是，只有相同的查询操作才会命中查询缓存 。两个查询请求在任何字符上的不同（例如：空格、注释、大小写）都会导致缓存不会命中。因此 MySQL 的查询缓存命中率不高。
同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 等数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数 NOW，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！
此外，既然是缓存，那就有它缓存失效的时候 。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT 、UPDATE 、DELETE 、TRUNCATE TABLE 、 ALTER TABLE 、DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。

第三层：引擎层

插件式存储引擎层（Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以文件的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

SQL执行流程

image.png

连接器

管理链接(TCP连接池，线程池都在这里进行获取)，账号认证，获取权限信息。

查询缓存

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端。如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。(一般在静态表里用这个功能)

解析器

在解析器中对 SQL 语句进行词法分析、语法分析。
分析器先做 “词法分析”。MySQL 需要识别出SQL里面的字符串分别是什么，代表什么。MySQL从输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串 “T” 识别成 “表名 T”，把字符串 “ID” 识别成 “列 ID”。
接着，要做 “语法分析” 。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否 满足 MySQL 语法 。
如果语句不正确，会收到”You have an error in your SQL syntax”的错误提醒。如果SQL语句正确，则会生成一个这样的语法树：
image.png
image.png

优化器

在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索等。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
在查询优化器中，可以分为 逻辑查询 优化阶段和 物理查询 优化阶段。
image.png
image.png

执行器

优化器将执行计划交给执行器准备执行SQL语句，在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎定义，调用存储引擎API对表进行读写。
存储引擎API只是抽象接口，下面还有一个存储引擎层，具体实现还是要看表选择的存储引擎。然后返回结果。在MySQL 8.0以下的版本，如果设置了查询缓存，这时会将查询缓存结果进行缓存。
image.png

MySQL缓存池

基本简介

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。磁盘 I/O 需要消耗的时间很多，而在内存中进行操
作，效率则会高很多。
为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池 ，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。
这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。

缓冲池

重要性

首先要明确一点：缓冲池和查询的缓存(已经被删除那个)不是一个东西。在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存。
image.png
缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟。所以InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。在进行完读写访问后把该页对应的内存空间缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。

缓存原则

“位置 * 频次” 这个原则，可以帮我们对 I/O 访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据，先缓存位置靠前的数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载 。

预读机制

只要不存在或减少磁盘 I/O，执行速度自然就会变快。预读新特性是 Innodb 通过在缓冲池中提前读取多个数据页来优化 I/O 的一种方式。每次至少读入一页的数据，如果下次读取的数据就在页中，就不用再去磁盘上读取了，从而减少了磁盘 I/O。
利用局部性原理：我们使用了一些数据，大概率还会使用它周围的一些数据。

  1. show variables like 'innodb_buffer_pool_size'; # 查看对应页的大小

如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中(注意不是查询结果)，如果存在就直接读取。如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。缓存在数据库中的结构和作用如下图所示：
image.png

查看/设置缓冲池大小

  1. #SQL语句
  2. show variables like 'innodb_buffer_pool_size'; # 134217278/1024/1024 = 128MB
  3. set global innodb_buffer_pool_size = 268435456;
  5. #配置文件
  6. [server]
  7. innodb_buffer_pool_size = 268435456

image.png

多个缓冲池

image.png
注意 innodb_buffer_pool_size 是总共的大小。

  1. [server]
  2. innodb_buffer_pool_instances = 2 #这样就表明我们要创建2个 Buffer Pool实例。
  4. show variables like 'innodb_buffer_pool_instances'; #查看缓冲池的个数

总共的大小除以实例的个数，结果就是每个Buffer Pool实例占用的大小。

刷盘与落盘

基本概念

落盘：将数据写入磁盘（即存储介质）
刷盘：将数据先写入缓冲区，再将缓冲区写入到磁盘的过程，称为刷盘

MySQL刷盘机制

刷盘的情况

脏页：内存中被修改过，跟磁盘中的数据页不一致的数据页称为脏页。有四种情况会触发脏页的刷盘：

  1. redo log 可写空间满了。
  2. 内存满了，需要删除的数据页恰好是脏页，需要先把脏页刷到磁盘上(更新数据)。
  3. 系统不繁忙的时候。
  4. 关闭数据库的时候。

其中，第三种情况不会为系统带来过多影响的，第四中情况不在乎给系统带来的影响。所以我们只需要关注第一和第二种情况：
对于第一种情况，由于 mysql 的更新需要先写日志，所以当日志满了的情况下，所有的更新都会停止，一直到刷完盘日志腾出了空间为止。
而对于第二种情况，当一次查询导致需要淘汰的脏页过多的时候，就需要先等待较长的刷盘时间，然后才能获取响应。
为了避免上述两种情况，必须要控制脏页在内存中的比例。

刷脏页的控制策略

首先，我们必须要知道主机磁盘的写入能力有多强，这样 innodb 才可以知道它刷脏页的速度最快应该是多快。
我们可以通过设置 innodb_io_capacity 这个参数来告诉 innodb 磁盘的写入速度。这个参数的值不宜过小，因为这会导致 innodb 错误的估计刷盘速度，最后导致刷脏页的速度跟不上脏页生成的速度。
innodb_io_capacity 规定了刷脏页速度的极限，但是实际上磁盘不可能只服务这么一个功能，所以还需要参考 redo log 的刷盘速度 和 允许的内存中的脏页比例。
参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。innodb 会根据 innodb_max_dirty_pages_pct 算出一个范围在 0 到 100 之间的数字，这个公式是 F1(M) 。
而每次写入 redo log 的写入点都会有一个序号，innodb 会根据这个序号和上一次清理日志的界限 checkpoint 之间的差值，计算得到一个范围在 0 到 100 之间的数字（假设为N），这个公式是 F2(N)
根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity * R的速度刷脏页。
这一整个流程对应的图片是这样的：
image.png 注意速度的计算公式为 innodb_io_capacity * Max(F1( innodb_max_dirty_pages_pct ) , F2(redo log的写入点序号 - redo log的cp)) ，图上有问题。
所以，我们需要关注内存中的脏页比例，让它尽量不要到75%，并且合理的设置 innodb_io_capacity 参数。
其中，针对脏页的比例，我们可以通过 Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total去设置。
另外，由于 mysql 存在这样一个机制：如果要刷盘的脏页相邻的数据页恰好也是脏页，就一起写入磁盘，如果邻居的邻居也是如此。在机械硬盘时代这个策略可以减少随机IO，但是如果使用固态硬盘的话随机IO的性能往往比较高，所以使用这个策略反而拖累了查询性能。因此可以通过 innodb_flush_neighbors关闭这个“连坐”的策略。

MySQL的事务日志

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。
注意日志多了会占用大量的磁盘空间，还会降低MySQL数据库的性能。

redo log

InnoDB引擎的事务采用了WAL技术(Write-Ahead Logging)，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功。这里的日志就是redo log。

作用

确保事务的持久性，以及保证checkpoint机制的实施(保证数据的最终落盘)。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候（crash），根据redo log进行重做，从而达到事务的持久性这一特性。
redo日志降低了刷盘频率，同时它占用的空间非常小。

内容

物理格式的日志，记录的是物理数据页面的修改的信息（比如”在某个数据页上做了什么修改”）。在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志。这些日志是按照产生的顺序写入redo log file的物理文件中去的。也就是使用顺序IO，比随机IO快。

什么时候产生

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放

当对应事务的脏页(与原先的页有差异的页)写入到磁盘之后，redo log的使命也就完成了，redo log占用的空间就可以重用（被覆盖）。

组成

重做日志的缓冲(redo log buffer)

保存在内存中。在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间。这片内存空间被划分成若千个连续的redo log block。一个redo log block占用512字节大小。
image.png

  1. mysql> show variables like '%innodb_log_buffer_size%'; #redo log buffer的大小
  2. +------------------------+----------+
  3. | Variable_name     | Value  |
  4. +------------------------+----------+
  5. | innodb_log_buffer_size | 16777216 |
  6. +------------------------+----------+

一个redo log block是由日志头、日志体、日志尾组成。日志头占用12字节，日志尾占用8字节，所以一个block真正能存储的数据就是512-12-8=492字节。
image.png

重做日志文件(redo log file)

保存在硬盘中，是持久的。

对应的物理文件

默认情况下，对应的物理文件位于数据库的data目录下(也就是在磁盘中)的 ib_logfile1&ib_logfile2
innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2。
关于文件的大小和数量，由一下两个参数配置：
innodb_log_file_size 重做日志文件的大小。
innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

整体流程

image.png
第1步：先将原始数据从磁盘中读到内存中，根据事务内容修改数据。
第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值。
第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式。
第4步：定期将内存中修改的数据刷新到磁盘中。

注：
image.png
第三步redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，而是刷入到文件系统缓存(page cache)中去(这是现代操作系统为了提高文件写入效率做的一个优化)，真正的写入会交给系统自己来决定(比如page cache足够大了)。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了(虽然整个系统宕机的概率还是比较小的)。
针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit参数，该参数控制 commit 提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略 ——
设置为0：
表示每次事务提交时不进行刷盘操作。
image.png
设置为1：
表示每次事务提交时都将进行同步，刷盘操作（默认值）
image.png
设置为2：
表示每次事务提交时都只把 redo log buffer 内容写入 page cache（操作系统的缓存），不进行同步。由os自己决定什么时候同步到磁盘文件。
image.png

日志写入log buffer的过程

mtr(Mini-Transaction)：一个事务可以包含若干条语句，每一条语句是由若干个 mtr 组成，每一个 mtr 又可以包含若干条redo日志。一个mtr代表一组不可分割的redo日志。
image.png
向log buffer中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写。我们第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以InnoDB的设计者特意提供了一个称之为buf_free的全局变量，该变量指明后续写入的redo日志应该写入到log buffer 中的哪个位置。
image.png
假设有事务T1，T2。对应的有T1产生的 mtr_t1_1和mtr_t1_2。T2产生的 mtr_t2_1和mtr_t2_2，不同的事务可能是并发执行的，所以 T1、 T2 之间的mtr可能是 交替执行的(但是一个事务里的mtr一定是按顺序的)，则有：
image.png

循环写的分析

redo日志文件实际上是放在一个日志文件组中的(注意不是mtr，mtr只是一些不可分割的redo日志，而日志文件组则类似一个收纳箱)，在将redo日志写入日志文件组时，是从ib_logfile0开始写，如果ib_logfile0写满了，就接着ib_logfile1写，依此类推。如果最后一个文件组也满了，那就重新转到ib_logfile0继续写：
image.png

CheckPoint

在整个日志文件组中还有两个重要的属性，分别是write pos和checkpoint：write pos是当前记录的位置，一边写一边后移。checkpoint是当前要擦除的位置，也是往后推移(都是顺时针)。
每次刷盘redo log记录到日志文件组中，write pos位置就会后移更新。每次MySQL加载日志文件组恢复数据时，会清空加载过的redo log记录，并把checkpoint后移更新。write pos和checkpoint之间的还空着的部分可以用来写入新的redo log记录。
如果write pos追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MysQL得停下来，清空一些记录，把 checkpoint推进一下。
image.png

undo log

作用

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作是要先写入一个undo log。保存了事务发生之前的数据的一个版本。
作用1：回滚数据。 undo并不是把数据库还原成多久之前的状态。undo是逻辑日志，因此撤回undo log对应事务的操作，其他事务的修改不会发生变化。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务，数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。
作用2：MVCC。undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

理解

事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：1. 事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。2. 程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前事务的执行。
以上情况出现，我们需要把数据改回原先的样子，这个过程称之为回滚。每当我们要对一条记录做改动时，都需要”留一手”—―把回滚时所需的东西记下来。MySQL把这些为了回滚而记录的内容称之为回滚日志(undo log)，注意select不会产生相应的undo日志。
此外，undo log会产生 redo log，因为undo log也需要持久性的保护。

内容

逻辑格式的日志，记录的是语句的原始逻辑，比如”给 id=2 这一行的 a字段 加1”。在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态(insert与delete互换，update执行一个相反的update)，而不是从物理页面上操作实现的，这一点是不同于redo log的。
InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了1024 个 undo log segment，而在每个undo log segment段中进行 undo页 的申请。

什么时候释放

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否有其他事务在使用undo long中上一个事务之前的版本信息，依此决定是否可以清理undo log的日志空间。

重用

当我们开启一个事务需要写undo log的时候，就得先去undo log segment中去找到一个空闲的位置，当有空位的时候，就去申请undo页，在这个申请到的undo页中进行undo log的写入。但是为每一个事务分配一个页是很浪费的。
于是undo页就被设计的可以重用了，当事务提交时，并不会立刻删除undo页。因为重用，所以这个undo页可能混杂着其他事务的undo log。undo log在commit后，会被放到一个链表中，然后判断undo页的使用空间是否小于3/4，如果小于3/4的话，则表示当前的undo页可以被重用，那么它就不会被回收，其他事务的undo log可以记录在当前undo页的后面。由于undo log是离散的，所以清理对应的磁盘空间时，效率不高。

回滚段与事务

image.png
image.png
purge：一个回收线程，用于判断是否可以回收。

回滚段数据类型

image.png

类型

insert undo log：在insert操作中产生的undo log。只对事务本身可见，而对其他事务不可见（事务隔离性的要求），故该undo log可以在事务提交后直接删除。
update undo log：记录delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

生命周期

image.png
对于InnoDB来说，每个行有三个隐藏属性，undo log主要是用到了 DB_ROLL_PTR：
image.png
image.png

  1. begin:
  2. INSERT INTO user(name) values("tom");

插入的数据会生成一条insert undo log，并且数据的回滚指针会指向它。undo log会记录undo log的序号、插入主键的列和值…，那么在进行rollback的时候，通过主键直接把对应的数据删除即可。
image.png
这是不更新主键的update：正常的更新链表，编号设置为1，由于不是新生成的行，故记录修改的列信息

  1. UPDATE user SET name="Sun" WHERE id=1;

image.png
这是修改主键的update：

  1. UPDATE user SET id=2 WHERE id=1;

image.png
对于更新主键的操作，会先把原来的数据delete mark标识打开(标记为删除)，这时并没有真正的删除数据，真正的删除会交给清理线程去判断，然后在后面插入一条新的数据，新的数据也会产生undo log，并且undo log的序号会递增，由于是新生成的记录，因此记录主键列信息。
因此对于上面三条语句，对应的rollback为：
image.png
image.png
Delete_Bit标识就是deletemark为1。

对应的物理文件

MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。
MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数
如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。
关于MySQL5.7之后的独立undo 表空间配置参数如下
innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录
innodb_undo_logs = 128 –回滚段为128KB
innodb_undo_tablespaces = 4 –指定有4个undo log文件
如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

bin log

作用

  1. 用于数据复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
  2. 用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了嘟些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

image.png

内容

binlog是逻辑格式的日志(全称为 binary log，二进制日志)，可以简单认为就是执行过的事务中的sql语句，但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。
但是binlog不包含没有修改任何数据的语句(如select，show等)。
在使用mysqlbinlog解析binlog之后一些都会真相大白，因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

什么时候产生

事务开始后，先把内容写到 binlog cache，事务提交的时候，再把binlog cache写到page cache中，最后刷盘到binlog文件中。
这里与redo log很明显的差异就是redo log是在事务开始之后就开始逐步写入磁盘。因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin log的情况下，对于较大事务的提交，可能会变得比较慢一些，这是因为bin log是在事务提交的时候一次性把binlog cache里的内容写入 page cache的造成的。
image.png
write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。
为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。
为了安全起见，可以设置为 1，表示每次提交事务都会执行fsync。
最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync

什么时候释放

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

三种模式

STATEMENT模式：每一条会修改数据的sql语句都会记录到binlog中。这是默认的binlog格式。
ROW模式：5.1.5版本的MySQL才开始支持，不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。
MIXED模式：从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement和Row的结合。 在Mixed模式下，一般的语句修改使用Statement格式保存binlog。而对于一些函数, 使用statement模式无法完成主从复制的操作(因为是一些计算，不是sql语句)，则采用Row格式保存binlog。

  1. binlog_format=STATEMENT
  2. binlog_format=ROW
  3. binlog_format=MIXED

对应的物理文件

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。对于每个binlog日志文件，通过一个统一的index文件来组织。

各种操作

  1. #查看默认情况
  2. mysql> show variables like '%log_bin%';
  4. #日志参数设置(永久)
  5. [mysqld]
  6. #启用二进制日志
  7. binlog_expire_logs_seconds=600  #控制二进制日志文件保留时长，单位是秒
  8. max_binlog_size=100M  #单个二进制日志大小
  9. log-bin="/var/lib/mysql/binlog/atguigu-bin"  #日志文件目录
  11. #临时设置参数
  12. # global 级别(mysql8中只有 session级别 的设置，没有了global级别的设置)
  13. mysql> set global sql_log_bin=0;
  15. # session级别
  16. mysql> SET sql_log_bin=0;
  18. #查看日志
  19. mysql> SHOW BINARY LOGS;
  21. #恢复数据
  22. mysqlbinlog [option] filename|mysql –uuser -ppass;
  24. #删除二进制日志
  25. PURGE {MASTER | BINARY} LOGS TO '指定日志文件名'
  26. PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'

image.png

crash safe机制

redo log以及bin log的区别

https://blog.csdn.net/liuzhe910422/article/details/112007897

  1. 记录内容不同

以一个仓库管理为例，redo log与binlog可以理解为该仓库的货物进出记录，只不过redo log和binlog记录的内容不一样。redo log记录物理日志的变化，比如在这个仓库中A货物从100件变成了102件。而binlog主要记录逻辑日志，比如A货物增加了2件。

  1. 记录方式不一样

redo log采用循环写的方式，也就是这个redo log写到末尾之后，再从头开始写（前面的会被覆盖）。而binlog会一直向后写，可以理解为没有空间限制。

  1. 用途不一样

当仓库管理员刚刚开始新的一天上班时（可以理解为MySQL刚刚崩溃重启），通过redo log就可以获取到该仓库里面货物的存放详细数据。当基于某个时间节点进行盘库时（可以理解为MySQL在某个时间点上进行恢复），假设这个时间点已经做了盘库（在MySQL里可以理解为备份）那么可以通过binlog日志盘查到从这个时间点到任意时间点的货物数量详情。

  1. 写入磁盘的时间不一样

redo log是在事务开启时就逐步写入磁盘，而bin log则是在事务结束后一次性写入。

两阶段提交机制

在这里以执行一个update语句为例说明redo log以及binlog是如何相互结合。
当执行update语句时，数据修改完，先写redo log 并设置redo log为准备阶段(prepared)，再写binlog，写完binlog设置为redo log为提交阶段(commit)。两个中间有一个写入差错，那么该操作都是失败，即redo log发现只有准备阶段没有结束，那么会将该条事务进行回滚。
假如不采用这种机制(也就是两阶段提交机制)的话那么会有什么影响呢？
(1) 先写redo log：当写完redo log设备断电，binlog没有记录，那么数据库刚刚启动之后通过redo log可以恢复到断电前的状态，但是由于binlog没有写入，当从某个节点进行恢复时，那么binlog没用这条记录，恢复出来的数据库是有错误的。
(2) 先写binlog：当写完binlog设备断电，redo log没有记录，那么数据库刚刚启动之后通过redo log恢复并没有这条数据，而当从某个时间点进行恢复时，binlog有相关数据，所以会导致数据不统一。
image.png

为什么redo log具有 crash-safe能力，而 bin log没有？

redolog 是循环记录某一页上的某个数据做了什么样的修改。是物理日志。只会记录未刷盘(还没有写入磁盘)的日志，已经刷盘的会从日志里删除。 而binlog是追加写，记录的是逻辑日志，保存的是全部日志。
当数据库crash后，想恢复未刷盘但是记录在 redolog 和 binlog 的数据到内存时，binlog是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。
但 redolog 不一样，只要刷入磁盘的数据都会从 redolog中抹除，数据库重启后，直接把 redolog中的数据都恢复至内存即可。

慢查询日志

在MySQL优化里讲解。

通用查询日志

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。
比如因为网络问题导致重复支付等，就可以通过查询这个日志发现问题在哪。

  1. #查看当前状态
  2. mysql> SHOW VARIABLES LIKE '%general%';
  4. #永久性启动日志
  5. [mysqld]
  6. general_log=ON
  7. general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名
  8. #如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。
  10. #临时性启动日志
  11. SET GLOBAL general_log=on;  # 开启通用查询日志
  12. SET GLOBAL general_log_file= 'path/filename'; # 设置日志文件保存位置
  14. #查看日志
  15. SHOW VARIABLES LIKE 'general_log%';
  16. #通用查询日志是以 文本文件 的形式存储在文件系统中的，可以使用 文本编辑器 直接打开日志文件。
  18. #永久关闭日志
  19. [mysqld]
  20. general_log=OFF
  22. # 临时关闭通用查询日志
  23. SET GLOBAL general_log=off; 
  25. #删除日志：手动删除

错误日志

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。
通过错误日志可以查看系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

  1. #MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。
  3. #查询存储路径
  4. mysql> SHOW VARIABLES LIKE 'log_err%';
  6. #启动日志
  7. [mysqld]
  8. log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名
  10. #MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除

中继日志

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的中继日志中。然后，从服务器中读取中继日志，并根据内容对从服务器的数据进行更新，完成主从服务器的数据同步。
搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。
文件名的格式是：从服务器名 -relay-bin.序号。中继日志还有一个索引文件:从服务器名 -relay-bin.index，用来定位当前正在使用的中继日志。
image.png

MySQL索引详解

索引介绍

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。 索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。**

优点与缺点

优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本 ，这也是创建索引最主要的原因。
（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性 。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点

（1）创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
（2）索引需要占磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
image.png

索引的代价

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表 。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些 记录移位，页面分裂，页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。q’e’w’n’g’diu

没有索引的查找

在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：
以主键为搜索条件 —— 数据页会为主键值生成 Page Directory（页目录），可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
以其他列为搜索条件 —— 因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找(可以建立索引)

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能 从第一个页 沿着 双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。

索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？
搜索树平均复杂度是O(logN)，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。也因此我们引入了B+树。

设计索引的原理

索引基础

  1. mysql> CREATE TABLE index_demo(
  2.    c1 INT,
  3.    c2 INT,
  4.    c3 CHAR(1),
  5.   PRIMARY KEY(c1)
  6. ) ROW_FORMAT = Compact;
  8.  #这个新建的index_demo表中有2个INT类型的列，1个CHAR(1)类型的列
  9.  #而且我们规定了c1列为主键，这个表使用 Compact 行格式来实际存储记录

Compact行格式：
image.png
把多条记录放在一个页中，示意图如下：
image.png(行要竖着看)
下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值：举一个很简单的例子，假设一个页中只能放三条记录(实际上能放很多)，如下图页10所示，主键值分别为 1，3，5。
image.png
此时插入一个主键为4的记录，由于页的空间已经不够，我们只能用一个新的页保存记录：image.png
注：新分配的页的编号可能并不是连续的(一个是10，一个是28)。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。
另外，页10中用户记录最大的主键值是5，而页28中有一条记录的主键值是4，因为5 > 4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的示意图如下：
image.png
这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立：即下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们称为页分裂。
然后给所有的页建立一个目录项：
image.png
以 页28 为例，它对应 目录项2 ，这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主键值 5。我们只需要把几个目录项(索引)在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：
先从目录项中根据二分法快速确定出主键值为 20 的记录在目录项3 中（因为 12 < 20 < 209 ），它对应的页是页9 。
再根据前边说的在一个页查找的方式去页9中定位具体的记录。至此，针对数据页做的简易目录就搞定了。这个目录就是索引。

InnoDB的加工：第一次加工

当目录项比较多时，需要非常大的连续存储空间才能把所有的目录项放下，这是不现实的。同时，我们对记录进行增删时，如果把某个目录项对应的记录全部删除了，这时就需要该目录项后面的目录项都向前移动一下（进行覆盖），这样操作效率很差。
因此我们可以把各目录项都放在数据页里：
image.png
目录项数据页与记录的数据页不同点：

  1. 目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
  2. 目录项记录只有 主键值和页的编号 两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列 ，另外还有InnoDB自己添加的隐藏列。
  3. 记录头信息里还有一个叫min_rec_mask 的属性，只有在存储最小主键值的目录项的min_rec_mask值为1，其他别的记录的 min_rec_mask值都是 0。

相同点：
两者用的是一样的数据页，都会为主键值生成 Page Directory（页目录），从而在按照键值进行查找时可以使用 二分法 来加快查询速度。
现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步： 先到存储目录项记录的页，也就是页30中通过 二分法 快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。
再到存储用户记录的页9中根据 二分法 快速定位到主键值为 20 的用户记录。

InnoDB的加工：第二次加工

我们可能需要多个存放目录项的数据页：比如当我们插入了主键值为320的数据，那么就需要新开辟一个数据页去存储它对应的目录。(假设目录项的数据页只能存四条数据)
image.png
举例：查找主键值为20的记录 —— 1. 确定目录项记录页（是页30还是页32）。这需要两次磁盘IO，因为需要把两张表都加载一下。根据主键值范围确定主键值为20的数据在页30对应的目录项中。2. 通过目录项记录页确定用户记录真实所在的页，这需要一次磁盘IO。3. 在真实记录的页中定位到具体的记录。

InnoDB的加工：第三次加工

当我们的目录项数据页很多时，我们需要一个数据页来记录某个目录项对应哪个页（也就是一个更高级的目录，大目录嵌套小目录）：
image.png
最高级页的列也只存储两条记录：某个目录项数据页里的最小主键值以及对应的页数。
这种数据结构就是B+树。

索引的数据结构

Hash方法

基本介绍

image.png
Hash算法是通过某种确定性的算法（比如MD5、SHA1、SHA2、SHA3)将输入转变为输出。相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。
image.png
采用Hash进行检索效率非常高，基本上一次检索就可以找到数据，而B+树需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次I/O操作，从效率来说Hash 比 B+树更快。
Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中。
当出现哈希碰撞的时候，在数据库中一般采用链接法来解决，也就是将散列到同一槽位的元素放在一个链表中（类似Java的HashMap）。
image.png

为什么不使用Hash算法

1. Hash索引仅仅能满足”=”，”IN”和”<=>”等 等值查询，不能使用范围查询，例如WHERE price >100。因为Hash索引比较的是进行Hash运算之后的Hash值，经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
2. 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，因此Hash索引无法用来避免数据的排序操作。
3. Hash索引不能利用组合索引。对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
4.Hash索引在任何时候都不能避免表扫描。由于不同索引键存在相同Hash值，所以即使找到了满足条件的Hash值，还是要继续找下去，因为有可能还有另外一个Hash值相同的记录。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

Hash的适用性

image.png
InnoDB本身不支持 Hash索引，但是提供自适应Hash索引(Adaptive Hash Index)。如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。
image.png

  1. #可以通过innodb_adaptive_hash_index变量来查看是否开启了自适应 Hash
  2. show variables like '%adaptive_hash_index'; #默认是开启的

二叉搜索树与二叉平衡树

确实加快了搜索速度，但是这样的树高度太大了，会导致磁盘IO次数过多。

B树

基本介绍

每次查找数据时把磁盘IO次数控制在一个很小的数量级，并且是常数数量级。B树在插入和删除节点的时候如果导致树不平衡，就会通过自动调节节点的位置来保持树的自平衡。
image.png
MySQL深度解析 - 图62
如上图，是一颗B树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。在B树中，关键字分布在整棵树中，BTree的一个node可以存储多个关键字，node的大小取决于计算机的文件系统。

查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+树

基本介绍

image.png
image.png
B+树的叶子节点存放着用户记录的数据页，而非叶子节点(或内节点)则存放着目录项记录的数据页(以及指示目录项记录的数据页)。
B+树存储量的计算：假设存放用户记录的页最多存放100条记录，存放目录项记录的页最多存放1000条记录，那么 ——
如果B+树只有一层：也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
如果B+树有2层，最多能存放 1000×100=10,0000 条记录（一个条目录项就可以指示100条用户记录）
如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。
4层已经可以存放大量的数据了，故一般情况下，B+树都不会超过4层。

B+树与B树的区别

image.png

数据只存叶子节点的好处

image.png

B+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小，而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.索引的最左匹配特性（即从左往右匹配）：当b+树的数据项是复合的数据结构，如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
3. 创建B+树时，首先创建根节点：每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，B+树索引对应的根节点中既没有用户记录，也没有目录项记录。随后向表中插入用户记录时，先把用户记录存储到这个根节点中。当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，如果该节点需要插入到新分配的页中，那么就会触发页分裂。而根节点便升级为存储目录项记录的页。这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。
image.png

R树

image.png
R-Tree在MySQL很少使用，仅支持 geometry数据类型 ，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
举个R树在现实领域中能够解决的例子：查找20英里以内所有的餐厅。如果没有R树你会怎么解决？一般情况下我们会把餐厅的坐标(x,y)分为两个字段存放在数据库中，一个字段记录经度，另一个字段记录纬度。这样的话我们就需要遍历所有的餐厅获取其位置信息，然后计算是否满足要求。如果一个地区有100家餐厅的话，我们就要进行100次位置计算操作了，如果应用到谷歌、百度地图这种超大数据库中，这种方法便必定不可行了。R树就很好的解决了这种高维空间搜索问题。它把B树的思想很好的扩展到了多维空间，采用了B树分割空间的思想，并在添加、删除操作时采用合并、分解结点的方法，保证树的平衡性。因此，R树就是一棵用来存储高维数据的平衡树。相对于B-Tree，R-Tree的优势在于范围查找。

索引的分类

逻辑分类

按功能分类

  • 主键索引 (primary key)：一张表只能有一个主键索引，不允许重复、不允许为 NULL。
  • 唯一索引 (unique key)：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以有多个唯一索引。
  • 普通索引 (key)：一张表可以创建多个普通索引，可以创建在任何数据类型中。一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入。
  • 全文索引 (fulltext key)：它查找的是文本中的关键词，主要用于全文检索，能利用分词技术等多种算法分析出文本文字中关键词的频率和重要性，然后筛选出我们想要的搜索结果。非常适合大型数据集（如搜索引擎）。
  • 空间索引 (spatial key) ：空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。了解就好，几乎不用。

注：key具有两层含义 —— 1. 约束 2. 索引。比如 primary key一方面表示主键约束，另一方面还代表为这个key建立主键索引。 foreign key一方面表明这是一个外键，另一方面为这个key建立普通索引 (因为foreign没有对应的特殊索引，默认为普通索引)。
image.png

按作用字段个数划分

  • 单列索引：一个索引只包含一个列，一个表可以有多个单例索引。
  • 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀” 原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效（不必全部填对应，但是一定要按顺序）。

物理分类

聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

聚簇索引(主键索引或一级索引)

image.png
聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。
特点：

  1. 页内的记录是按照主键的大小顺序排成一个单向链表。
  2. 各个页之间也是根据页里最小主键的顺序排成一个双向链表。
  3. 存放目录项的页分为不同层次，同样也是根据主键的值构成双向链表。

    非聚簇索引(辅助索引或二级索引)

    image.png
    非聚簇索引也是根据索引的值来进行排序的(参考上图的蓝色块)，只不过这个索引不再是主键了。链表结构和聚簇索引一致，只不过存储的内容不同。
    非聚簇索引的数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。具体放的什么需要根据存储引擎来决定，比如上图就是InnoDB类型的非聚簇索引，存储的是索引值和对应的主键值。
    注：非聚簇索引的非叶子节点有时会由三部分组成：1. 索引列的值。2. 主键值。3. 页号。就是要保证内节点目录项唯一性的时候：
    假设一个二级索引的B+树是这样的：
    image.png
    如果我们想新插入一行记录，其中c1、c2、c3的值分别是：9、1、 ‘c’。那么由于页3中存储的目录项记录是由c2列＋页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，而我们新插入的这条记录的c2列的值也是1，那我们这条新插入的记录到底应该放到页4中，还是应该放到页5中？很明显无法判断。
    因此我们需要加入主键：
    image.png 这样c2列相同的时候，就可以通过主键值来判断要插入到哪里了。

    聚簇索引与非聚簇索引

    虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

    聚簇索引优点

  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
  • 聚簇索引对于主键的排序查找和范围查找速度非常快

  • 按照聚簇索引排列顺序，查询一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。

    聚簇索引缺点

  • 插入速度严重依赖于插入顺序，按照主键的顺序(从小到大)插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）

  • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

image.png

两者区别

  • 聚簇索引的叶子节点存放的是数据页，支持覆盖索引；而非聚簇索引的叶子节点存放的是主键值或指向数据行的指针。
  • 由于叶子节点(数据页)只能按照一棵B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引。

  • 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

    为什么非聚簇索引需要回表

    如果把完整的用户记录放到叶子节点确实可以不用回表。但是非聚簇索引使用的是非主键来创建的B+树，也就是说，我们可以创建许多个非聚簇索引B+树，那么把全部数据放进去就会导致极大的内存浪费。

    联合索引

    我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照c2列进行排序。

  • 在记录的c2列相同的情况下，采用c3列进行排序。

image.png
目录项记录由c2，c3，页号组成，用户记录(叶子节点)由c1(主键)，c2，c3，页号组成。
注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

  1. 建立 联合索引 只会建立如上图一样的1棵B+树。

  2. 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

    InnoDB索引实现

    InnoDB使用B+TREE存储数据，除了主键索引为聚簇索引，其它索引均为非聚簇索引。一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。InnoDB表的索引和数据是存储在一起的，.idb表是数据和索引的文件。

    InnoDB聚簇索引示意图

    image.png
    可以看到叶子节点包含了完整的数据记录（即一个页，里面存了多个行），这就是聚簇索引。因为InnoDB的数据文件（.idb）按主键聚集，所以InnoDB必须有主键（MyISAM可以没有），如果没有显示指定主键，则选取首个为唯一且非空的列作为主键索引，如果还没具备，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
    结构分析：1. B+树单个叶子节点内的行数据按主键顺序排列，物理空间是连续的（即里面的所有行数据在存储空间里是连续排列的） 2. 叶子节点之间是通过指针连接，相邻叶子节点的数据在逻辑上是连续的（根据主键值排序），实际存储时的数据页（叶子节点）可能相距甚远。

    InnoDB非聚簇索引示意图

    MySQL深度解析 - 图77在聚簇索引之外创建的索引（不是根据主键创建的）称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录，而是辅助索引对应的值(比如name)和对应的主键值(比如id)。首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行，这同时也被称为回表查询。

    MyISAM索引实现

    MyISAM也使用B+Tree作为索引结构，但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。

    MyISAM索引的存储位置

    索引和数据分开存储：
    将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件，这个文件并不划分为若干个数据页。并且插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法查找。
    使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。

    MyISAM主键索引示意图

    image.png
    可以看到叶子节点的存放的是数据记录的地址。也就是说索引和行数据记录是没有保存在一起的，所以MyISAM的主键索引是非聚簇索引。
    在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。 MyISAM辅助索引也是非聚簇索引。

    InnoDB和MyISAM的检索过程

    对于InnoDB和MyISAM而言，主键索引是根据主键来构建的B+树存储结构，辅助索引则是根据辅助键(除了主键以外的键)来构造的B+树存储结构，彼此的索引树都是相互独立的。
    InnoDB辅助索引的访问需要两次索引查找，第一次从辅助索引树找到主键值，第二次根据主键值到主键索引树中找到对应的行数据。而主键索引则只需要一次（因为主键和数据放在一起）。
    MyISM使用的是非聚簇索引，表数据存储在独立的地方，这两棵（主键和辅助键）B+树的叶子节点都使用一个地址指向真正的表数据。因此辅助键和主键都是访问一次即可（访问一次得到地址，去存储区域拿到内容），辅助键拿到的是地址而不是对应的主键。
    假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
    image.png

    InnoDB和MyISAM的差异

  3. 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次回表操作。

  4. InnoDB的数据文件与索引文件存放在一起。而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
  5. InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是地址。
  6. MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的。而InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
  7. InnoDB要求表必须有主键（具体可查看上面），而MyISAM可以没有。

image.png

索引的操作

https://www.cnblogs.com/python-wen/p/9678043.html
https://achang.blog.csdn.net/article/details/122629207

MySQL8.0索引新特性

支持降序索引

  1. CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

支持隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。
注：主键不能设置为隐藏索引。

  1. #创建表时，设置隐藏索引
  2. CREATE TABLE tablename(
  3.      propname1 type1[CONSTRAINT1],
  4.      propname2 type2[CONSTRAINT2],
  5.      ……
  6.      propnamen typen,
  7.       INDEX [indexname](propname1 [(length)]) INVISIBLE #使用 INVISIBLE关键字
  8. );
  10. #在已经存在的表上创建
  11. CREATE INDEX indexname
  12. ON tablename(propname[(length)]) INVISIBLE;
  14. ALTER TABLE tablename
  15. ADD INDEX indexname (propname [(length)]) INVISIBLE;
  17. #切换索引隐藏状态
  18. ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
  19. ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

索引的使用场景

频繁作为 WHERE 查询条件的字段

MySQL深度解析 - 图81 id是索引。不管是SELECT, DELETE, INSERT，只要是频繁使用where的字段，都要加上索引。

ORDER BY和GROUP BY

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。
但是如果我们对该字段建立索引，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）
group by也是同理。在SELECT查询的时候，先进行 GROUP BY操作，再对数据进行 ORDER BY操作。

  1. SELECT student_id, create_time FROM student_info group by student_id;
  2. #由于先group by，因此联合索引 (student_id,create_time) 比 (create_time,student_id) 快

唯一字段

image.pngDISTINCT

有时候我们需要对某个字段进行去重，使用 DISTINCT。那么对这个字段创建索引，也会提升查询效率。

  1. SELECT DISTINCT(student_id) FROM `student_info`; #0.683s
  2. #对建立 student_id建立索引
  3. SELECT DISTINCT(student_id) FROM `student_info`; #0.010s
  4. #同时显示出来的 student_id 还是按照 递增的顺序 进行展示的

多表JOIN连接操作

对 join 语句匹配关系（on）涉及的字段建立索引能够提高效率。一些注意事项：
首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，先对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

  1. #只对course_id创建索引
  2. SELECT course_id, name, student_info.student_id, course_name
  3. FROM student_info JOIN course
  4. ON student_info.course_id = course.course_id
  5. WHERE name = '462eed7ac6e791292a79'; #0.189s
  7. #对name创建索引后，可缩短至 0.002s

最后， 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

散列性高的列适合作为索引

列的基数：指的是某一列中不重复数据的个数，比方说某个列包含值2,5,8,2,5,8,2,5,8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散。列的基数越小，该列中的值越集中。

  1. select count(distinct a)/count(*) from t1 #计算散列度，一般超过33%就是比较高效的索引了

不适合使用索引的场景

  1. 在where、group by、order by里用不到的字段不需要创建索引。
  2. 数据量小的表最好不要创建索引。因为对查询效率的影响不大。
  3. image.png
  4. 避免对经常更新的表创建过多的索引。
  5. 不建议用无序的值作为索引，因此会导致多次页分裂。比如身份证，MD5，HASH等。
  6. 不要定义冗余或重复的索引。 ```sql CREATE TABLE person_info( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR(11) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), KEY idx_name (name(10)) );

    我们可以用联合索引来对name进行快速搜索，所以 idx_name就是一个冗余索引

CREATE TABLE repeat_index_demo ( col1 INT PRIMARY KEY, col2 INT, UNIQUE uk_idx_c1 (col1), INDEX idx_c1 (col1) );

col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引

可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的。

  2. <a name="nAHd0"></a>
  3. ## 前缀索引
  4. **如果我们把很长的字符串当作索引，会出现一些问题：**<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/23175776/1647228544362-a1b57b53-6366-4ffb-83cb-6eeefe5a49ba.png#clientId=u6dcfb4e4-7503-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=106&id=ud73212b4&margin=%5Bobject%20Object%5D&name=image.png&originHeight=211&originWidth=1739&originalType=binary&ratio=1&rotation=0&showTitle=false&size=203599&status=done&style=none&taskId=u125c4266-7dee-481b-adb3-5e64e10d012&title=&width=869.5)<br />**前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。**
  5. ```sql
  6. ALTER TABLE table_name ADD KEY(column_name(prefix_length)); --建立前缀索引的语法
  8. ALTER TABLE shop add index(address(12));

使用场景：
1. 字符串列(varchar,char,text等)，需要进行前匹配。比如 like ‘xxx%’。
2. 字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。

  1. #整个字段在全部数据中的选择度
  2. select count (distinct address) / count(*) from shop;
  4. #字段前缀在全部数据中的选择度
  5. select count(distinct left(address,索引长度)) / count(*) from shop;
  7. select count(distinct left(address,10)) / count(*) as sub10 from shop 
  8. -- 截取前10个字符的选择度

3. 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们继续加大前缀字符长度的优势已经不明显，没有太大的建前缀索引的必要了。
注：使用前缀索引的话，无法支持使用索引排序，只能使用文件排序。因为无法对前缀字符相同，后面字符不同的记录进行排序。

  1. #比如前缀长度为3 
  2. # aaaaa aaabb aaacc就无法区分

索引覆盖

基本简介

简单说就是，索引列+主键 包含了 SELECT 到 FROM之间查询的所有列。
索引覆盖主要是用于解决回表的问题。当 explain的输出结果Extra字段为Using index时，能够触发索引覆盖，下面给出一个具体的例子：

  1. create table user (
  2.     id int primary key, --id是主键索引
  3.     name varchar(20),
  4.     sex varchar(5),
  5.     index(name)  --name是普通索引
  6. )engine=innodb;
  1. select id,name from user where name='bailong';

对于这句SQL，由于id和name都是索引，通过查找普通索引name，可以在叶子节点中直接得到 name和对应的id (参考InnoDB非聚簇索引示意图)，就不需要再进行回表查询了。

  1. select id,name,sex from user where name='shenjian';

对于这句SQL，虽然 name和id都可以直接查询辅助B+树得到，但是 sex 还必须通过 id值，查询主键树来得到全部的页信息，然后才能得到sex的值（也就是回表）。

  1. create table user (
  2.     id int primary key,
  3.     name varchar(20),
  4.     sex varchar(5),
  5.     index(name, sex) --单列索引升级为联合索引
  6. )engine=innodb;

但是如果表变成这样，把单列索引升级为联合索引，这样就可以得到sex了（但是如果希望单独得到sex还得进行回表，这样只能在同时查询name和sex时才会激活，参考最左匹配原则）。
同时，覆盖索引还可以改变一些索引失效的情况：

  1. SELECT * FROM student WHERE age <> 20; #未使用索引
  2. SELECT age,NAME FROM student WHERE age <> 20; #优化器发现可以使用覆盖索引后，就使用索引了
  4. SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc' #同理

使用场景

  1. 全表count查询优化。

    1. select count(age) from user;  -- 创建age字段索引

  2. 列查询回表优化。

    1. select id,age,name from user where age = 10; -- 建组合索引 (age,name) 即可

  3. 分页查询。

    1. select id,age,name from user order by age limit  -- 建立组合索引 (age,name)

    利弊

    image.png
    （如果没有覆盖索引的话，回表是需要随机IO的）

    索引的注意事项

  4. 索引字段要独立出现。

    1. select * from user where id = 20-1;  -- 会使用索引
    2. select * from user where id+1 = 20;  -- 不会使用索引,因为id没有独立出现

  5. 使用 like 时，不能以通配符开头。

如果使用通配符开头，只能作全表扫描，效率非常低。

  1. select * from article where title like '%mysql%'; -- 以 % 开头
  2. select * from article where title like 'mysql%'; -- 这个可以使用索引，前提是title是索引
  1. 最左匹配原则 ```sql alter table person add index(first_name,last_name); —这是一个复合索引

select from person where first_name = ?; —这个是可以利用索引的, 因为匹配了最左侧的一个 select from person where last_name = ?; —这个不可以利用索引, 因为最左侧的还没有被匹配 select * from person first_name = ? and last_name = ?; —这个可以利用 —注意上面这条, 只要有一个属性没有对应的索引, 就会导致全盘扫描

  1. **复合索引是：先找出符合A的结果，再在这些结果中找出符合B的结果。 如果单独写的话，是：找出符合A的结果，再找出符合B的结果，两者取交集。在多个字段都要创建索引的情况下，联合索引优于单值索引。**<br />**我们一般把使用最频繁的列放到联合索引的左侧。**
  3. 4. **状态值不常用索引**
  5. **如性别、支付状态等状态值字段往往只有极少的几种取值可能，这种字段即使建立索引，也往往利用不上。这是因为，一个状态值可能匹配大量的记录，这种情况MySQL会认为利用索引比全表扫描的效率低，从而弃用索引。**
  6. <a name="PdoU1"></a>
  7. ## 索引下推优化
  8. <a name="PP2pI"></a>
  9. ### 基本简介
  10. **索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。**<br />**在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。**<br />**在使用ICP的情况下，如果存在某些被索引化的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件（这样就不用返回给数据库服务器了），只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。**<br />**索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。**
  11. ```sql
  12. #开启索引下推
  13. SET optimizer_switch = 'index_condition_pushdown=off'
  15. #关闭索引下推
  16. SET optimizer_switch = 'index_condition_pushdown=on'
  18. #默认是启用的，当使用索引下推时，EXPLAIN语句输出结果中Extra列显示内容为 Using index condition

经典例子

准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引(name，age)，SQL语句如下：

  1. SELECT * from user where  name like '陈%' and age=20

Mysql5.6之前的版本

image.png
它会忽略age这个字段，直接通过name进行查询（最左匹配原则），在(name,age)这棵树上查找到了两个结果，id分别为2,1(InnoDB非聚簇树叶子节点存储主键的值)，然后拿着取到的id值进行回表查询，在聚簇索引树上再判断age是否符合要求。因此总共需要两次回表查询。

Mysql5.6及之后版本

MySQL深度解析 - 图86
InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。
索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

使用条件

image.png

索引失效

  1. 对字段进行函数与计算可能会破坏索引。因为 B+树是按照给的字段顺序进行排序的，一旦改变了，B+树就不知道怎么走了，就会走全表扫描。 ```sql

    可以使用索引

    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE ‘abc%’;

使用了LEFT函数，不能使用索引

type为 “ALL”，表示没有使用到索引，查询时间为 3.62秒，查询效率较之前低很多。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = ‘abc’;

CREATE INDEX idx_sno ON student(stuno);

不能使用索引

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

可以使用索引

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

  2. 2. **如果有类型转换也会导致索引失效。**
  3. ```sql
  4. # 未使用到索引
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123; #name是 varchar
  6. # name=123发生类型转换，索引失效。(隐式的类型转换)
  8. # 使用到索引
  9. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
  1. 破坏了最左匹配原则。使用索引必须按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
  2. 范围条件右边的列索引失效。 ```sql CREATE INDEX idx_age_classId_name ON student(age,classId,NAME); SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = ‘abc’ ;

    student.classId > 20的右侧的student.name = ‘abc’的索引就会失效

    因为 student.classId对应的是一个范围条件

直接交换sql语句的位置是没有用的，需要改变联合索引的位置

CREATE INDEX idx_age_classId_name ON student(age,NAME,classId); EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = ‘abc’ AND student.classId > 20;

把范围查询放最后，同时满足最左匹配原则，这样就能使用索引了

  2. 5. **不等于(!= 或者 <>)索引失效**
  3. ```sql
  4. CREATE INDEX idx_name ON student(NAME)
  5. SELECT * FROM WHERE student.name <> 'abc'; #索引失效
  7. #因此where中最好使用等于

  1. is null可以使用索引，is not null无法使用索引

    1. SELECT * FROM student WHERE age IS NOT NULL;  #无法使用索引

    image.png

  2. like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为 “%” ，索引就不会起作用。只有 “%” 不在第一个位置才有有效。
Alibaba Java开发手册：【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

  1. SELECT * FROM student WHERE name LIKE '%ab%'; #索引失效
  1. OR 前后存在非索引的列，索引失效

让OR的前后条件都具备索引，如果缺少一个就会出现索引失效。因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有一个列没有索引，就会进行全表扫描，因此索引的条件列也会失效。

  1. CREATE INDEX idx_name ON student(age)
  2. CREATE INDEX idx_name ON student(NAME)
  3. # 未使用到索引
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
  6. #使用到索引
  7. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR NAME = 'Abel';

总结：
image.png
image.png

页分裂

对于一个使用InnoDB存储引擎的表来说，数据页和记录是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插入。
而如果我们插入的主键值忽大忽小的话，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：
image.png
可这个数据页已经满了，再插进来咋办呢？
我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。
所以建议：让主键具有AUTO_INCREMENT(填入自增的主键值)，让存储引擎自己为表生成主键。

逻辑主键和业务主键

逻辑主键（surrogate key）：无意义的字段，即自增长字段，如id。
业务主键（natrual key）：有意义的字段，比如身份证 ID。

逻辑主键优缺点

优点：自增长字段往往用bigint(Long)类型，最多占8个字节。索引与外键 所占用的空间连带减少，增删改查 效率高。业务变化，不影响，不需要更新主键。 缺点：无法转移数据库，比如把表中的一批数据 转移 或 附带到 另一个表中，那么由于是自增长字段，那么会导致无法转移，因为另外一个表可能已经存在部分数据，会造成主键冲突。同时业务数据的完整性无法保证。 **

业务主键优缺点

优点：可以转移数据库，最大化节省了空间，因为并没有多增加一个非业务字段做主键。可以保证业务逻辑的完整性。避免产生垃圾数据，银行就是用业务字段做主键的，虽然效率低，但是安全。 缺点：如果业务发生改变，有可能需要修改主键，举例：国家A表用身份证号做主键，然后其他很多表中的身份证号这列都是来自身份证表A中的主键（即外键），那么如果身份证号升级，比如从1代升级到2代，那么连带的表的外键 的索引 通通都得发生变化，效率极低。可见用业务字段做主键的话，要保证主键不经常变化。**