查询执行引擎

查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开 启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有 相同的 SQL 语句执行则直接返回结果。

如果开启了查询缓存，先将查询结果做缓存操作
返回结果过多，采用增量模式返回

3.3 Mysql存储引擎层

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的， 服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有 很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

show engines; 查询支持的存储引擎。

5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。

各种引擎介绍

InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全。

MyISAM：不支持事务和外键，访问速度比较快。

Memory：利用内存创建表，访问的速度非常快，默认使用Hash索引，一旦关闭，数据就会丢失。

Archive：归档类型引擎，仅支持insert和select语句。

Csv：csv文件格式进行数据存储，由于文件限制，所有的列必须强制指定not null。另外csv引擎不支持索引和分区，适合做数据交换的中间表。

BlackHole：黑洞，只进不出。进来就消失，所有插入的数都不会保存，但是一些操作是会记录在binlog中的。可以用来做为主从复制的中介，节省空间。

Federated：可以访问远端Mysql数据库中的表。一个本地表，不保存数据，访问远程表内容。

MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge本身没有数据，对Merge操作可以对一组MyISAM表进行操作。

InnoDB和MyISAM

事务和外键

InnoDB支持事务和外键，强调数据安全和完整性，适合大量insert或update操作

MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作

锁机制

InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。

MyISAM支持表级锁，锁定整张表。影响到并发能力。

索引结构

InnoDB使用聚簇索引，索引和记录在一起存储，及缓存索引，也缓存记录。

MyISAM使用非聚簇索引，索引和记录分开。（主键索引也是和数据分开的）

并发处理能力

MyISAM 使用表级锁，会导致写操作并发率低，读之间并不会进行阻塞，读写阻塞。

InnoDB读写阻塞与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。

存储文件

InnoDB对应两个文件，一个.frm表结构文件（8版本没了），一个.ibd数据文件。一个表最大支持64TB

MyISAM表对应三个文件，一个.frm表结构文件（8版本 .sdi文件），一个MYD表数据文件，一个.MYI索引文件。

一个表最大支持256TB。

适用场景：

MyISAM：不需要支持事务、并发相对较低（锁机制，表级锁）、数据修改相对较少、以读为主、数据一致性要求不高。

InnoDB：需要事务支持、行级锁对高并发有很好的适应能力、数据更新较为频繁的场景、数据一致性要求高，硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

如何选择存储引擎？

• 是否需要事务？InnoDB
• 是否存在并发修改？InnoDB
• 是否追求快速查询，且数据修改少？MyISAM
• 在绝大多数情况下，推荐适用InnoDB

3.4 InnoDB存储结构

主要分为内存结构和磁盘结构两部分。

内存结构

内存结构主要包括括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件。

BufferPool

BufferPool主要由一个个page页组成。

缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数 据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁 盘IO操作，提升效率。

show variables like ‘%innodb_page_size%’;

Page管理机制

Page根据状态可以分为三种类型：
    free page ： 空闲page，未被使用
    clean page：被使用page，数据没有被修改过
    dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不 一致
针对上述三种page类型，InnoDB通过三种链表结构来维护和管理
    free list ：表示空闲缓冲区，管理free page
    flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间 排序。脏页即存在于flush        链表，也在LRU链表中，但是两种互不影响，LRU链表负 责管理page的可用性和释放，而flush链表负责管理        脏页的刷盘操作。lru某些page淘汰也会触发刷盘操作。
    lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以 midpoint为基点，前面链表称为        new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。
    查看 innodb状态信息：show engine innodb status \G;
    ![](assets/image-20200824133007184.png#alt=image-20200824133007184)
    有时dtabase pages+free buffers有时小于buffer pool size。
    因为 buffer pool里面还包含了自适应hash索引，以及 changed buffer pool

而且有几个#buffer_pool实例个数
innodb_buffer_pool_instances=4就有几个buffer_pool

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1098907648
Dictionary memory allocated 822720
Buffer pool size   65536 #这个为个数 65536*16kB=1048576KB=1G
Free buffers       60815
Database pages     4700
Old database pages 1810
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4525, created 175, written 6278
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4700, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   16384 
Free buffers       15168
Database pages     1210
Old database pages 466
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1196, created 14, written 5355
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1210, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   16384
Free buffers       15206
Database pages     1178
Old database pages 453
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1105, created 73, written 179
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1178, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   16384
Free buffers       15200
Database pages     1176
Old database pages 453
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1102, created 74, written 423
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1176, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   16384
Free buffers       15241
Database pages     1136
Old database pages 438
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1122, created 14, written 321
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1136, unzip_LRU len: 0

改进型LRU算法维护

    普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
    改进型LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间 midpoint位置插入，        如果数据很快被访问，那么page就会向new列表头部移动，如果 数据没有被访问，会逐步向old尾部移动，        等待淘汰。 每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足 够，如果        有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会 根据LRU算法淘汰LRU链表默认        的页，将内存空间释放分配给新的页。

Buffer Pool配置参数

show variables like '%innodb_page_size%'; //查看page页大小  select @@innodb_pagesize; 除以1024就是多    少KByte。默认每个page页16k。

show variables like '%innodb_old%'; //查看lrulist中old列表参数

show variables like '%innodb_buffer%'; //查看buffer pool参数
![](assets/image-20200824134532156.png#alt=image-20200824134532156)
    mysql 8.0 innodb_buffer_pool_size默认为(134217728字节)128M 8192个page。和5相比加了16倍。
    mysql 5 默认为 8M 有512个page。

建议：将innodb_buffer_pool_size设置为总内存（计算机总内存）大小的60%-80%， innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。

蓝色为整个bufferpool

白色为bufferpool分出的多个instance

绿色为instance分成多个块chunk

红色为chunk包含多个page页

我们可以对buffer pool分配大内存然后按照这种方式进行逐层配置拆分。

Change Buffer

可以参考：https://www.jianshu.com/p/92a674683f28

写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据， 并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数 据合并恢复到BP中。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来 进行调整。参数innodb_change_buffer_max_size;

show variables like ‘%innodb_change_buffer_max_size%’;

set global innodb_change_buffer_max_size=20;

当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如果该记录在BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进行磁盘读取，然后再从 ChangeBuffer中读取信息合并，最终载入BufferPool中。

写缓冲区，仅适用于非唯一普通索引页，为什么？

如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘， 做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在 ChangeBuffer操作。

Log Buffer

日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲 区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。 LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。 LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少 磁盘IO频率 。

innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1，建议设为2

show variables like “%innodb_flush%”;

Adaptive Hash Index ：自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监 控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以 称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

mysql> show variables like '%hash%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+

skip-innodb_adaptive_hash_index 可以关闭

innodb_adaptive_hash_index_parts HashTable分区，减小锁粒度，提高并发量。每个分区各自一把锁，管自己的。最大512

hash自适应索引会占用innodb buffer pool；
自适应hash索引只适合搜索等值的查询或者in，而对于其他查找类型，如范围查找，是不能使用的；
如: where index_col=‘xxx’；
无法进行排序；
MySQL自动管理，人为无法干预；

磁盘结构

InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log 和Undo Logs。

系统表空间信息（System Tablespace）（多个表共享）文件名为ibdata1：

包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区 域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空 间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数 innodb_data_file_path控制。

show variables like ‘%innodb_data_file_path%’;

autoextent：自动扩展。

• 数据字典（InnoDB Data Dictionary）
  InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数 据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上 与InnoDB表元数据文件（.frm文件）中存储的信息重叠。

• 双写缓冲区（Doublewrite Buffer）
  位于系统表空间，是一个存储区域。在BufferPage的dirty page页刷新到磁盘真正的位置之前，会先将数据存在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或 mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个备份。在大多数情况下，默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将 innodb_doublewrite设置为0。使用Doublewrite 缓冲区时建议将innodb_flush_method设 置为O_DIRECT。

  MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、
  刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。设置O_DIRECT表示
  数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb
  Buffer写到磁盘文件。
  默认的fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文
  件与redo log的缓存信息。

独立表空间（File-Per-Table）：

每创建一个表就会生成一个独立的.ibd文件。

默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于 系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则， innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件 默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩 （commpressed）行格式。

show variables like ‘%innodb_file_per_table%’;

5.7以后默认是开启的。

通用表空间（General Tablespace）:

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于 mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

用法：

create tablespace ts1 add datafile ‘ts1.ibd’ engine=innodb; //在data目录下直接创建

use dbname;

create table t1(id int primary key) tablespace ts1;

撤销表空间（undo tablespaces）：

撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的 是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。 InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参 数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、 undo_002等。设置为n就有那个undo_xxx

show variables like ‘%innodb_undo_tablespaces%’;

撤销日志（Undo Logs） 撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志 属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 | #最大1G
| innodb_undo_directory    | .\         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |# 2个
+--------------------------+------------+

临时表空间（Temporary Tablespace)：

分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户临时表的回滚段（rollback segments ）。mysql服务 器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

重做日志（Redo Log） 重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。 MySQL以循环方式写入重做日志文件，记录InnoDB中所有对Buffer Pool修改的日志。当出 现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数 据更新到数据文件。读写事务在执行的过程中，都会不断的产生redo log。默认情况下，重 做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。默认一个为48M，可以适当进行调整（256M，512M）。

新版本架构演变

MySQL 5.7 版本

将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用 户自行指定文件大小和数量。

增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。

Buffer Pool 大小可以动态修改，无需重启数据库实例。

MySQL 8.0 版本

将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要 ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。

temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建 索引，这样加快了处理的速度。

用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个 表空间可以给多个表使用，但一个表只能存储在一个表空间中。

将Doublewrite Buffer从共享表空间ibdata中也分离出来了。

扩展 缓存Qcache与Buffer pool区别

参考博文【https://blog.51cto.com/13111614/1946212】

这两个不是一个东西：

1、Qcacche缓存的是SQL语句及对应的结果集，缓存在内存，最简单的情况是SQL一直不重复，那Qcache的命令率肯定是0；

2、buffer pool中缓存的是整张表中的数据，缓存在内存，SQL再变只要数据都在内存，那么命中率就是100%。

3，Qcache是服务层的缓存。buffer pool是innodb存储引擎带的一个缓存池，查询数据的时候，它首先会从内存中查询，如果内存中存在的话，直接返回，从而提高查询响应时间。Buffer pool是设置的越大越好，一般设置为服务器物理内存的70%。

3.5 InnoDB线程模型

IO Thread

在InnoDB中使用了大量的AIO（Async IO）来做读写处理，这样可以极大提高数据库的性能。在 InnoDB1.0版本之前共有4个IO Thread，分别是write，read，insert buffer和log thread，后来 版本将read thread和write thread分别增大到了4个，一共有10个了。 read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个

show engine innodb status \G;查看引擎状态信息

read thread:负责读取操作，将数据从磁盘加载到缓存page页。4个

write thread:负责写操作，将缓存脏页刷新到磁盘。4个

log thread:将日志缓冲区内容刷新到磁盘，形成redo log。

insert buffer thread:负责将写缓冲内容刷新到磁盘。

Purge Thread

事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo 页。
show variables like '%innodb_purge_threads%';

Page Cleaner Thread

作用是将脏数据刷新到磁盘，脏数据刷盘后响应的redo log也就可以覆盖，即可以同步数据，又能达到redo log循环使用的目的。会调用write thread线程处理。
show variables like '%innodb_page_cleaners%';

Master Thread

Master Thread 是InnoDB的主线程，负责调度其他线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包含：脏页的刷新-page cleaner thread，undo页回收-purge thread，redo日志刷新-log thread，合并写缓冲等。内部有两个主处理，分别是每隔1s和10s处理。
每1s操作：
    刷新日志缓冲区，刷到磁盘 log_buufer->redolog
    合并写缓冲区数据，根据IO读写压力来决定是否操作
    刷新脏页数据到磁盘，根据脏页比列达到75%才操作。8.0是90%。这个百分比是占change_buffer的大小。
        show variables like '%innodb_max_dirty_pages_pct%';
        show variables like '%innodb_io_capacity%'; innodb_io_capacity 每次刷新多少页，默认200，可以调整            最大到2000；

每10s操作：
    脏页刷新到磁盘，无条件。
    合并写缓冲区数据，无条件。
    刷新日志缓冲区。log_bufer->redolog
    删除无用的undo 页。show variables like '%innodb_purge_batch_size%'; 默认为300每次处理300页。

3.6 Innodb数据文件

Innodb文件存储结构

分为一个ibd数据文件—>Segment（段）—>Extent（区）—>Page（页）—>Row（行）

Tablespace：表空间，用于存储多个ibd数据文件，用来存储表的记录和索引。一个文件包含多个段。

Segment：段，用于管理多个Extent，分为数据段（Leaf node segment），索引段（Non-leaf node segment），回滚段（Rollback segment）。一个表至少有两个segment，一个管理数据，一个管理索引。每创建一个索引，会多两个segment。

Extent：区，一个区里面包含64个连续的页，大小为1M.当表空间不足时，需要分配新的页资源，不会一页一页分，直接分配一个区。16*64=1024

Page：页，用于存储多个行记录，大小为16k。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的Blob对象页。

Row：行，包含了记录字段值，事务ID(trx id)，滚动指针（Rollpointer），字段指针（Field pointers）等信息。

Page是文件最基本的单位，无论何种类型的page，都是由page header，page trailer和page body组成。

数据文件存储格式

查看表信息。

use dbName;

show tables;

show table status;

一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果 row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。

通过 information_schema 查看指定表的文件格式

5.7

select * from information_schema.innodb_sys_tables \G;
select * from information_schema.innodb_sys_tables where name = 'lagou/r_resume' \G;

8

select * from information_schema.innodb_tables \G; 
select * from information_schema.innodb_tables where name = 'la_gou/r_resume' \G;

文件格式 （File Format）

在早期的InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新文件格式，用于 支持新的功能。

目前InnoDB只支持两种文件格式：Antelope 和 Barracuda。

Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和 REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。

Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC。

通过innodb_file_format 配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7版本 开始改为Barracuda。

行格式（Row Format）

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。

InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引 前缀。

每个表的数据分成若干页来存储，每个页中采用B树结构存储；

如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页， 该字段被称为页外列。

• REDUNDANT 行格式
  使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余 的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便 能够在页外存储。

• COMPACT 行格式
  与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了 某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式 可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。

• DYNAMIC 行格式
  使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只 包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。 DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数 控制。

• COMPRESSED 行格式
  COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引 数据压缩的支持。

相关博客【https://blog.csdn.net/peng_zhanxuan/article/details/106506022】

在创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与*.ibd匹配）。修改文件 格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用以下命令：

ALTER TABLE 表名 ROW_FORMAT=格式类型;

3.7 Undo Log

Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。
Undo Log：将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，    可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将    该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进 行回收处理。Undo Log属于逻    辑日志，记录一个变化过程。例如执行一个delete，undolog会记 录一个insert；执行一个update，undolog会    记录一个相反的update。
Undo Log存储：undo log采用段的方式管理和记录。**在innodb数据文件中包含一种rollback segment回滚段**，**内部包含1024个undo log segment**。可以通过下面一组参数来控制Undo log存储。

show variables like ‘%innodb_rollback_segments%’; //查看有多少个segment 默认为128每个回滚段包含1024个undo log segment

show variables like '%undo%';

innodb_undo_tablespaces=2说明undo日志有两个undo日志文件。可以设定个数。

undo_log和rollback segment有什么关系？
作用：
    实现事务的原子性：Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或        者用户执 行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
    实现多版本并发控制（MVCC）:Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未        提交之前，Undo Log 保存了未提交之前的版本数据，**Undo Log 中的数据可作为数据旧版本快照供其他并发        事务进行快照读。**
    ![](assets/image-20200825120119705.png#alt=image-20200825120119705)
事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。
事务B手动开启事务，    执行查询操作，会读取 Undo 日志数据返回，进行快照读

3.8 redo log 和 binlog

redo log

Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。

Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做 日志。

Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生 Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

Redo Log工作原理 Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表 的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

其实redo buffer就是log buffer。数据库所有更新的操作都会=》log buffer=>redo log。redo buffer和change buffer是不一样的。redo 是保存一系列的操作记录，change buffer缓冲的是修改的数据，然后下次查询的时候先从磁盘查询出来和change buffer的数据合并返回。

可能有疑问，先写到内存的log buffer可以理解（不需要与磁盘有io操作，提高效率），为啥还要有写到redo log(这个也不是每次提交都都写入到redolog文件，没1s,10s或者log buffer满时提交)这种设计呢。redo log是一种顺序写入，而如果将更新操作持久化到数据文件先要查询到对应行数据然后进行更新就是一种磁盘随机操作，性能低于顺序写入。

Redo log写入机制：

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开 头；

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

Redo Log相关配置参数：

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文 件，默认为ib_logfile0和ib_logfile1。可以通过下面一组参数控制Redo Log存储：

show variables like '%innodb_log%';

可以看到innodb_log_files_in_group=2表示一个日志文件组有两个日志文件，我们可以进行调整。

Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置： 建议设为2

0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数 据。由后台Master线程每隔 1秒执行一次操作。

1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安 全，性能最差的方式。

2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。

bin log日志

Binlog记录模式：

Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日    志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制 日志，不会记录SELECT和    SHOW这类操作。**Binlog日志是以事件形式记录，还包含语句所执行的消耗时间**。开启Binlog日志有以下两个最重要的使用场景。

• 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到 Binlog后实现数据恢复达到主从数据一致性。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。

• ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave端对相同的数据进行修改。
  优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。 比如一个sql语句可以更新10条记录，那么会产生10条日志记录。
  缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

• STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到 master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的 SQL再次执行。简称SQL语句复制。
  优点：日志量小，减少磁盘IO，提升存储和恢复速度
  缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

• MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存 binlog，MySQL会根据执行的SQL语句选择写入模式。
  binlog_format=MIXED

Binlog文件结构：

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的    修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog    文件的内容就是各种Log event的集合。
log event结构：

bin log写入机制

• 根据记录模式和操作触发event事件生成log event(事件触发执行机制)

• 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区
  log Event保存再一个binblog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache,用 于存放不支持事务的信息；另一个是trx_cache,用于存放支持事务的信息。

• 事务在提交阶段会将产生的log event写入到外部binlog文件中
  不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在 binlog文件中是 连续的，中间不会插入其他事务的log event。

binlog文件操作

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------------+
| Variable_name                   | Value                                            |
+---------------------------------+--------------------------------------------------+
| log_bin                         | ON  #开启                                        |
| log_bin_basename                | D:\develop\mysql-8.0.19-winx64\data\binlog       |
| log_bin_index                   | D:\develop\mysql-8.0.19-winx64\data\binlog.index |
| log_bin_trust_function_creators | OFF                                              |
| log_bin_use_v1_row_events       | OFF                                              |
| sql_log_bin                     | ON #恢复的logbin日志是否记录，通常应改关闭 不然就冗余了  |
+---------------------------------+--------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

如果是关闭的我们可以在my.cnf或者my.ini文件，[mysqld]下面增加

log-bin=logbin #logbin日志前缀
binlog-fomat=ROW #logbin记录模式 ROW STATMENT MIXED

查看binlog文件，内容

#查看binlog有哪些日志文件
show binary logs;
#正在对那个binlog文件进行写入
show master status;
show binlog events;
#查看binlog文件内容
show binlog events in 'binlog.000016';

使用mysqlbinlog

mysqlbinlog "文件名"
mysqlbinlog "文件名" > "test.sql"

使用binlog恢复数据

//按指定时间恢复
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="2020-04-26 00:00:00" binlog.000002 | mysql -uroot -p1234
//按事件位置号恢复
mysqlbinlog --start-position=234 --stop-position=841 binlog.000001| mysql -uroot -proot

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      762 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in "binlog.000001";
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000001 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.19, Binlog ver: 4    |
| binlog.000001 |  124 | Previous_gtids |         1 |         155 |                                      |
| binlog.000001 |  155 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 |  234 | Query          |         1 |         311 | BEGIN                                |
| binlog.000001 |  311 | Table_map      |         1 |         376 | table_id: 245 (la_gou.student)       |
| binlog.000001 |  376 | Write_rows     |         1 |         430 | table_id: 245 flags: STMT_END_F      |
| binlog.000001 |  430 | Xid            |         1 |         461 | COMMIT /* xid=2999 */                |
| binlog.000001 |  461 | Anonymous_Gtid |         1 |         540 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 |  540 | Query          |         1 |         617 | BEGIN                                |
| binlog.000001 |  617 | Table_map      |         1 |         682 | table_id: 245 (la_gou.student)       |
| binlog.000001 |  682 | Write_rows     |         1 |         731 | table_id: 245 flags: STMT_END_F      |
| binlog.000001 |  731 | Xid            |         1 |         762 | COMMIT /* xid=3002 */                |
| binlog.000001 |  762 | Anonymous_Gtid |         1 |         841 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 |  841 | Query          |         1 |         918 | BEGIN                                |
| binlog.000001 |  918 | Table_map      |         1 |         983 | table_id: 245 (la_gou.student)       |
| binlog.000001 |  983 | Delete_rows    |         1 |        1037 | table_id: 245 flags: STMT_END_F      |
| binlog.000001 | 1037 | Xid            |         1 |        1068 | COMMIT /* xid=3006 */                |
| binlog.000001 | 1068 | Anonymous_Gtid |         1 |        1147 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 1147 | Query          |         1 |        1224 | BEGIN                                |
| binlog.000001 | 1224 | Table_map      |         1 |        1289 | table_id: 245 (la_gou.student)       |
| binlog.000001 | 1289 | Delete_rows    |         1 |        1338 | table_id: 245 flags: STMT_END_F      |
| binlog.000001 | 1338 | Xid            |         1 |        1369 | COMMIT /* xid=3007 */                |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
22 rows in set (0.01 sec)

恢复删除的数据：mysqlbinlog —start-position=234 —stop-position=841 binlog.000001| mysql -uroot -proot

mysqldump:定期全部备份数据库数。mysqlbin可以做增量备份和恢复操作。

删除binlog文件

purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
reset master; //清除所有文件

设置expire_logs_days参数来启动自动清理。默认值为0表示未启用。

show variables like ‘%expire_logs_days%;

mysql 8关闭 log bin

[mysqld]配置下加 skip-log-bin

redo log和bin log的区别

redo log属于innodb引擎功能，binlog 属于mysq server自带功能。

redo log属于物理日志记录数据页更新状态内容，binlog是逻辑日志，记录更新过程。

redo log日志是循环写，日志空间大小固定，binlog是追加写入，写完一个写下一个，不会覆盖使用。

redo log作为服务器异常当即后事务数据自动恢复使用，binlog可以作为主从复制和数据恢复使用。binlog没有自动crash-safe的能力。

4 Mysql索引原理

索引会提升查询速度，会影响where查询，以及order by排序。索引类型如下：

从索引存储结构划分：BTree索引，Hash索引，FULLTEXT全文索引，RTree索引

从应用层次划分：普通索引，唯一索引，主键索引，复合索引

从索引键值类型划分：主键索引，辅助索引（二级索引）

从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）—数据和索引放在一起，非聚集索引（非聚簇索引）—数据和索引不在一起。

4.1 索引类型

普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

创建普通索引的方法如下：

CREATE INDEX <索引的名字> ON tablename (字段名); 
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名); 
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

查看索引：

show index from tablename;

删除索引：

drop index indexName on tablename;

（1）id为PK，聚集索引，叶子节点存储行记录；

（2）name为KEY，普通索引，叶子节点存储PK值，即id；

唯一索引

与”普通索引”类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一 约束，就会自动创建对应的唯一索引。

创建方法：

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主 键。

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索 引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合 索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超 过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有 效。但是如果一个表建了很多索引，就考虑使用宽索引了。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

注意：

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效 率有很大影响。

如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果 使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全 文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如:

match里面可以支持多个字段。

select * from user
where match(name) against('aaa');

show variables like ‘%ft%’;

ft_min_word_len：myisam最小单词数，

ft_max_word_len：myisam最大单词数 满足才会建立全文索引。

innodb_ft_min_token_size: 3

innodb_ft_max_token_size: 84 3~84个字符的时候才会创建全文索引。可以在配置文件进行修改重新启动。这里是token_size而不是整个word长度，通过切词后的长度也必须大于3才会建立全文索引。

ft_boolean_syntax:+ -><()~*:""&|
遇到+ -><()~*:""&|等字符才会进行切词

注意事项（了解就可以，限制太多）：

• 全文索引必须在字符串、文本字段上建立。

• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4- 84）

• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa

• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布 尔模式下搜索a*。

  select * from user
  where match(name) against('aaa' in boolean mode);

不过可以自己定义innodb_ft_server_stopword_table，停用词表

4.2 索引原理

数据库索引是一种数据结构，它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的 速度。通俗来说，索引类似于书的目录，根据其中记录的页码可以快速找到所需的内容。

MySQL 官方对索引（Index）的定义是存储引擎用于快速查找记录的一种数据结构。

索引是物理数据页，数据库页大小（Page Size 16K 16384个字节）决定了一个页可以存储多少个索引行，以及 需要多少页来存储指定大小的索引。

索引可以加快检索速度，但同时也降低索引列插入、删除、更新的速度，索引维护需要代价。

索引涉及的理论知识有二分查找法、哈希表及 B+Tree

二分查找

针对有序的数据。

优点：等值查询，范围查询性能优秀

缺点：增删改数据维护成本高。需要对原来的有序数据进行处理。

Hash 结构

Hash底层实现是由Hash表来实现的，是根据键值 存储数据的结构。非常适合根据key查找 value值，也就是单个key查询，或者说等值查询。其结构如下所示：

从Hash索引的结构可知非常适用于等值查询，范围查询就需要全表扫描了。

主要应用于Memory引擎原生的Hash索引，以及innodb的自适应hash索引。

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当 InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内 存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据（如果存在Hash碰撞可能就需要多次查询了，mysql用的是拉链法来处理hash碰撞，我们可以优先选择避免Hash冲突算法），等值查询效率要优于 B+Tree。

hash算法：FNV64 > CRC32 （大数据量下 Hash 冲突概率较大）> MD5 > SHA1

自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页 建立哈希索引来加速访问。

另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

show engine innodb status \G;
show variables like '%innodb_adaptive%';
#关闭自适应hash索引
set global innodb_adaptive_hash_index=0;
#开启自适应hash索引
set global innodb_adaptive_hash_index=1;

我们可以看到 使用AHI和不适用AHI查询速率，如果使用AHI查询速率反而低于不使用AHI查询速率，可以考虑关闭了。

mysql> show variables like '%innodb_adaptive%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| innodb_adaptive_flushing         | ON     |
| innodb_adaptive_flushing_lwm     | 10     |
| innodb_adaptive_hash_index       | ON     |
| innodb_adaptive_hash_index_parts | 8      |
| innodb_adaptive_max_sleep_delay  | 150000 |
+----------------------------------+--------+
5 rows in set, 1 warning (0.00 sec)

BTree

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

B-Tree结构

• 索引值和data数据分布在整棵树结构中
• 每个节点可以存放多个索引值及对应的data数据 树节点中的多个索引值

• 从左到右升序排列

  

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有 命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束

B+Tree结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
• 叶子节点包含了所有的索引值和data数据
• 叶子节点用指针连接（双向），提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

如下图，假如有一个2层的索引B+Tree,图中每个页面都已经被随机编号（编号可以认定为页 面号）

其中页面号为 20 的页面是 B+Tree 的根页面（根页面通常是存放在内存中的），根页面存 储了 ，pageno 是指向具体叶子节点的页面号。其他页面都是叶子节点，存放了具 体的数据 。

通过页面号为 20 的根节点可以快速得知 Key<10 的数据在 pageno 33 的页面，key在 [10,16) 范围的数据在 pageno 56 的页面。 叶子节点存放的 ，对于真正要存放哪些数据还得取决于该 B+Tree 是聚簇索引 （Clustered Index）还是辅助索引（Secondary Index）。

B+Tree 索引能够快速访问数据，就是因为存储引擎可以不再需要通过全表扫描来获取数据，而是从索引的根结点（通常在内存中）开始进行二分查找，根节点的槽中都存放了指向子节点的指针，存储引擎根据这些指针能够快速遍历数据。

4.3 聚簇索引和辅助索引

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

聚簇索引（聚集索引）

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说 的主键索引就是聚集索引。

InnoDB的表要求必须要有聚簇索引：

• 如果表定义了主键，则主键索引就是聚簇索引
• 如果表没有定义主键，则第一个非空unique列作为聚簇索引
• 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

辅助索引：

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中 只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是 为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

5 索引分析与优化

5.1 Explain

我们在执行语句前面加上 explian可以对语句进行分析:

id值越大越先被执行。

select_type:

simple: 表示查询语句不包含子查询或union，最常见的
primary: 表示此查询是最外层的查询
union: 表示此查询是union的第二个或后续的查询
dependent_union: union中的第二个或后续的查询语句，使用到了外面的查询结果
union_result: union查询结果
![](assets/image-20200826220010456.png#alt=image-20200826220010456)
subquery: select 子查询语句
![](assets/image-20200826220150694.png#alt=image-20200826220150694)
dependent subquery: select子查询语句依赖外层的查询结果
有时mysql对子查询的优化会导致dependent subquery，使用子查询的时候一定要注意，使用explain跑一遍看看。
![](assets/image-20200826221039033.png#alt=image-20200826221039033)

有这样一段话：

mysql 在处理子查询时，会改写子查询。
通常情况下，我们希望由内到外，先完成子查询的结果，然后再用子查询来驱动外查询的表，完成查询。
例如：
select * from test where tid in(select fk_tid from sub_test where gid=10)
通常我们会感性地认为该 sql 的执行顺序是：
sub_test 表中根据 gid 取得 fk_tid(2,3,4,5,6)记录，
然后再到 test 中，带入 tid=2,3,4,5,6，取得查询数据。
但是实际mysql的处理方式为：
select * from test where exists (
select * from sub_test where gid=10 and sub_test.fk_tid=test.tid
)
mysql 将会扫描 test 中所有数据，每条数据都将会传到子查询中与 sub_test 关联，子查询不会先被执行，所以如果 test 表很大的话，那么性能上将会出现问题。

type: 通过它可以判断出查询是全表扫描还是基于索引的部分扫描

• All：表示全表扫描，性能最差，大部分说明你的sql需要优化了。

• index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。先经过索引的全表扫描是有序的。

• range：表示使用索引范围查询，使用>,>=,<,<=,in,between等。

• ref：表示使用非唯一索引进行查询。 select * from u_user where real_name = '王豪' real_name为二级索引
  

• eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果，一对一关系。

• const：表示使用主键或唯一索引做等值查询。常量查询
  select * from u_user where id = 1
  

• NULL：表示不用访问表，速度最快。

possible_keys: 查询时能够使用的索引，不一定会使用，显示的是索引名称。

key: 表示查询时真正使用到的索引，显示的时索引名称。

key_len: 表示查询使用了索引的字节数，可以判断是否全部使用了组合索引，或只用到索引的最左部分字段。

计算规则：
    字符串：字符串长度跟字符集有关。latin=1,gbk=2,utf8=3,utf8mb4=4    char(n):n*字符集长度
    varchar(n):n*字符集长度+2字节
数值类型：
    tinyint: 1byte
    smallint: 2byte
    mediumint: 3byte
    int,float: 4byte
    bigint,double: 8byte
时间类型：
    date: 3byte
    timestamp: 4byte (2038问题,大于2038的时间不能存储)
    datetime: 8byte
字段属性：
    null 属性只用一个byte，如果一个字段设置了not null,则没有此项。

ref:

rows: mysql索引优化器会根据统计信息，估算sql查询到结果需要扫描多少行记录。原则上rows越少效率越高，可以直观的了解到sql效率高低。

filtered:

extra:

• using where
  1、查询条件没有用到索引，直接根据条件全表扫描。server 层对数据进行过滤。
  2、通常是查询条件有用到索引，但是其他的没有用索引的需要回表查询。需要在存储引擎层数据返回给server层对数据进行过滤。
  3、如下用id查询，查询的字段position没有索引，这个应该没有回表查询吧？

  select id,position from u_user where id in (1,2);

• using index
  表示查询需要通过索引，索引就可以满足所需数据。

• using filesort
  表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，有using filesort建议优化。

• using emprorary
  查询使用到了临时表，一般用于去重，分组操作。

• using index condition
  索引下沉的时候会出现。直接由存储引擎层根据条件进行索引过滤。
  如果用到了复合索引、或者like查询字段有索引左匹配时会出现。

5.2 回表查询

InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要 有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记 录，通常情况下，需要扫描两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

5.3 覆盖索引

只需要在一棵索引树上就能获取SQL所需的所 有列数据，无需回表，速度更快，这就叫做索引覆盖。

实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

5.4 最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列， 那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

5.5 Like查询

MySQL在使用like模糊查询时，索引能不能起作用？

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

select * from user where name like ‘%o%’; //不起作用

select * from user where name like ‘o%’; //起作用

select * from user where name like ‘%o’; //不起作用

5.6 Using index condition

索引下沉，5.6版本之后加入的新特性

含义就是存储引擎层根据索引尽可能的过滤数据,然后在返回给服务器层根据where其他条件进行过滤

show variables like ‘%optimizer_switch%’;

可以看到 index_condition_pushdown=on说明索引下沉功能是开启的

set optimizer_switch='index_condition_pushdown=off'; //关闭ICP

 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on

下面我们来看一个列子：

有如下表设计

CREATE TABLE `u_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `pwd` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
  `user_code` varchar(10) NOT NULL DEFAULT '' COMMENT '员工编号',
  `real_name` varchar(30) NOT NULL COMMENT '姓名',
  `tel_no` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '手机号',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '邮箱',
  `position` varchar(20) DEFAULT NULL COMMENT '职位',
  `head_img` varchar(150) DEFAULT NULL COMMENT '头像地址',
  `depart_id` int NOT NULL DEFAULT '-1' COMMENT '部门id',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态 1正常 0禁用',
  `can_pub_topic` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否可以发布主题 1是 0否',
  `can_pub_notice` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否可以发布公告 1是 0否',
  `deleted` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 1是 0否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_depart_id` (`depart_id`),
  KEY `idx_name` (`real_name`),
  KEY `idx_tel_no` (`tel_no`),
  KEY `idx_code` (`user_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我们有如下查询语句：

select * from u_user force index(idx_name) where   id > 23 and real_name ='张三';

这里我们强制使用name索引，不让其走主键索引。

假如使用了索引下沉：

首先在存储引擎根据real_name字段索引树查到满足real_name=’张三’的数据，因为辅助索引保存了主键值，所以我们拿到这个主键id再来判断id>23是否满足，然后拿到满足条件的id回表去查询数据。

假如没有使用所以你下沉：

首先在存储引擎根据real_name字段索引树查到满足real_name=’张三’的数据，因为辅助索引保存了主键值，

拿到满足real_name=’张三’数据行id回表去查询，将数据行返回给mysql server层，mysql server层再通过id>23条件进行过滤。

5.7 Null查询

如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

null列可以使用索引，不论是普通二级索引，还是复合所有都有效。

但是：

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他 值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时 不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍 五入到最接近的字节

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为 NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以 设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

5.8 索引与排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序 操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

filesort有两种排序算法：双路排序和单路排序。

双路排序：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二 次去读取其他字段数据。

单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。

解决方 案：少使用select *；增加sort_buffer_size容量(尽量不要修改)和max_length_for_sort_data容量

mysql> show variables like '%sort_buffer%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 | 1M
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |256k
+-------------------------+---------+
3 rows in set, 1 warning (0.00 sec)

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用 index排序方式，建议大家尽可能采用覆盖索引。

以下几种情况，会使用index方式的排序。

ORDER BY 子句索引列组合满足索引最左前列

explain select id from user order by id; //对应(id)、(id,name)索引有效

WHERE子句+ORDER BY子句索引列组合满足索引最左前列

explain select id from user where age=18 order by name; //对应 (age,name)复合索引

以下几种情况，会使用filesort方式的排序。

对索引列同时使用了ASC和DESC

explain select id from user order by age asc,name desc; //对应(age,name)复合索引

WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in 等）

explain select id from user where age>10 order by name; //对应(age,name)复合索引

ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

explain select id from user order by name; //对应(age,name)索引

使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

explain select id from user order by name,age; //对应(name)、(age)两个索引

WHERE子句与ORDER BY子句，使用了不同的索引

explain select id from user where name='tom' order by age; //对应(name)、(age)索引

WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs(age); //对应(age)索引

5.9 查询定位

查看慢查询日志功能状态：其实慢的增删改语句也会记录

mysql> show variables like '%slow_query_log%';
+---------------------+----------------------------------------------------------+
| Variable_name       | Value                                                    |
+---------------------+----------------------------------------------------------+
| slow_query_log      | ON                                                       |
| slow_query_log_file | D:\develop\mysql-8.0.19-winx64\data\buqingyishi-slow.log |
+---------------------+----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

设置参数：

#开启慢查询日志
SET global slow_query_log = ON;
#设置文件名
SET global slow_query_log_file = 'mysql-slow.log';
#记录没有使用索引的查询日志 必须开启slow_query_log
SET global log_queries_not_using_indexes = ON;
#指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中
SET long_query_time = 1;

文本方式查看 各属性含义：

time：日志记录的时间
User@Host：执行的用户及主机
Query_time：执行的时间
Lock_time：锁表时间
Rows_sent：发送给请求方的记录数，结果数量
Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点
select....：执行的具体的SQL语句

使用mysqldumpslow查看：

MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志 内容。

需要安装perl环境。

查看帮助信息：

perl mysqldumpslow.pl --help

perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\mysql-slow.log

也可以使用第三方分析工具，比如pt-query-digest【参考博客：https://blog.csdn.net/xiaoweite1/article/details/80299754】、 mysqlsla等。

5.10 慢查询优化

索引是为了优化查询效果，但是查询的快慢和有没有使用索引没有必然的关系。

如下：

select * from u_user where id > 0;
select * from u_user;

这两个其实都是会进行全表扫描的。

因此，我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果 扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过 滤性好，执行速度才会快。

有如下表结构设计

CREATE TABLE `u_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `pwd` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
  `user_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '员工编号',
  `real_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `tel_no` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '手机号',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '邮箱',
  `position` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '职位',
  `head_img` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '头像地址',
  `depart_id` int NOT NULL DEFAULT '-1' COMMENT '部门id',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态 1正常 0禁用',
  `can_pub_topic` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否可以发布主题 1是 0否',
  `can_pub_notice` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否可以发布公告 1是 0否',
  `deleted` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 1是 0否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_depart_id` (`depart_id`) USING BTREE,
  KEY `idx_name` (`real_name`) USING BTREE,
  KEY `idx_tel_no` (`tel_no`) USING BTREE,
  KEY `idx_code` (`user_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

有445 6448数据

select count() from u_user一个简单的数量统计都需要十几秒，我们肯定是不能忍受的，所以我们在查询的时候一定要加过滤条件。如果真的需要查询大量数据或者类似于这种count()，就需要缓存了。

当depart_id和real_name都是独立的索引的时候

explain select * from u_user where depart_id = 3 and real_name like '王%';

mysql> explain select * from u_user where depart_id = 3 and real_name like '王%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u_user
   partitions: NULL
         type: ref
possible_keys: idx_depart_id,idx_name
          key: idx_depart_id
      key_len: 4
          ref: const
         rows: 884452
     filtered: 11.19
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

需要扫描884452行数据扫描，没有索引下沉。

我们在 depart_id和real_name创建复合索引，数据量大，创建索引也很耗时

 create index idx_depart_id_name on u_user(depart_id,real_name);

mysql> explain select * from u_user where depart_id = 3 and real_name like '王%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u_user
   partitions: NULL
         type: range
possible_keys: idx_depart_id,idx_name,idx_depart_id_name
          key: idx_depart_id_name
      key_len: 126
          ref: NULL
         rows: 126590
     filtered: 100.00
        Extra: Using index condition; Using MRR
1 row in set, 1 warning (0.01 sec)

需要扫描126590行数据，使用了索引下沉。

而且 也很快了。

select count(id) from u_user where depart_id = 3 and real_name like '王%';

但其实我们的数据只有65536行数据，我们还可以进行优化。 100多ms。

加入虚拟列，比如我们可以针对姓单独搜索，但是不要像有些脑残产品非要几个条件合到一起那种，再怎么做都无法优化了。

//为u_user表添加first_name虚拟列，以及联合索引(first_name,depart_id) 然后针对first_name做等值查询

alter table u_user add first_name varchar(2) generated always as (left(real_name, 1)), add index(first_name, depart_id);

explain select * from u_user where depart_id = 3 and first_name = '王';

mysql> explain select * from u_user where depart_id = 3 and first_name = '王' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u_user
   partitions: NULL
         type: ref
possible_keys: idx_depart_id,idx_depart_id_name,first_name
          key: first_name
      key_len: 15
          ref: const,const
         rows: 125282
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

虽然扫描的行也是15282，但是我们的type从range变成ref了，速率更高了。

select count(id) from u_user where depart_id = 3 and first_name = ‘王’; 也变成了几十ms左右。

慢查询原因：

1. 全表扫描：explain分析type属性all
2. 全索引扫描：explain分析type属性index
3. 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
4. 频繁的回表查询开销：尽量少用select *，使用覆盖索引

5.11 分页查询优化

我们可以先开启 profiling。

show variables like 'profiling';
set profiling=1;#不要加global

一般性分页：

般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量，注意从0开始； 第二个参数指定返回记录行的最大数目； 如果只给定一个参数，它表示返回最大的记录行数目；

测试：

如果偏移量固定，返回记录量对执行时间有什么影响？

select * from u_user limit 10000,1;
select * from U_user limit 10000,10;
select * from u_user limit 10000,100;
select * from u_user limit 10000,1000;
select * from u_user limit 10000,10000;

然后 show profiles;

mysql> show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00365075 | show variables like 'profiling'        |
|        2 | 0.05038300 | select * from u_user limit 10000,1     |
|        3 | 0.01735725 | select * from U_user limit 10000,10    |
|        4 | 0.01718300 | select * from u_user limit 10000,100   |
|        5 | 0.01968875 | select * from u_user limit 10000,1000  |
|        6 | 0.07443350 | select * from u_user limit 10000,10000 |
+----------+------------+----------------------------------------+
6 rows in set, 1 warning (0.00 sec)

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

select * from u_user limit 1,100;
select * from u_user limit 10,100;
select * from u_user limit 100,100;
select * from u_user limit 1000,100;
select * from u_user limit 10000,100;

show profiles

mysql> show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
`````````8 | 0.00083000 | select * from user limit 1,100         |
|        9 | 0.00128050 | select * from u_user limit 1,100       |
|       10 | 0.00095900 | select * from u_user limit 10,100      |
|       11 | 0.00111350 | select * from u_user limit 100,100     |
|       12 | 0.00291275 | select * from u_user limit 1000,100    |
|       13 | 0.01799550 | select * from u_user limit 10000,100

可以看到随着偏移量的增大，查询数据会变长。当偏移量大于100的时候，查询时间急剧增加。

（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而 且查询的数据越多，也会拖慢总查询速度。）

分页优化方案：

1. 利用覆盖索引优化：

id和real_name都是索引

select * from u_user limit 10000,100;
select id from u_user limit 10000,100;
select id,real_name from u_user limit 1000,100;

2.使用子查询优化

select * from u_user limit 10000,100;
select * from u_user where id>(select id from u_user limit 10000,1) limit 100

          9 | 0.00128050 | select * from u_user limit 1,100                                                       |
|       10 | 0.00095900 | select * from u_user limit 10,100                                                      |
|       11 | 0.00111350 | select * from u_user limit 100,100                                                     |
|       12 | 0.00291275 | select * from u_user limit 1000,100                                                    |
|       13 | 0.01799550 | select * from u_user limit 10000,100                                                   |
|       14 | 0.00037450 | select name from u_user limit 10000,100                                                |
|       15 | 0.01592600 | select real_name from u_user limit 10000,100                                           |
|       16 | 0.01553750 | select * from u_user where id>=(select id from u_user limit 10000,1) limit 100         |
|       17 | 0.00408600 | select real_name from u_user where id>=(select id from u_user limit 10000,1) limit 100

可以看到查询性能提升了。

使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

5.12 问题：

select real_name from u_user where id>=(select id from u_user limit 10000,1) limit 100

select id,position from u_user where id = 2; #主键索引，type=const

select id,position from u_user where id in (1,2); #主键索引，type=range，extra=using where

Using where过滤元组和是否读取数据文件或索引文件没有关系

select id,position from u_user where id > 50 limit 100000,1000;#主键索引，type=range，extra=using where

select id from u_user limit 100000,100; #idx_depart_id，type=index，extra=using index

select id from u_user force index(primary) limit 100000,100; #primary，type=index，extra=using index

select id,depart_id from u_user where depart_id in (1,2,3,9,5) #idx_deaprt_id,type=range,extry=using where;using index

select id,depart_id,real_name from u_user where depart_id > 3;

6 ACID

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID： 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

6.1 原子性 Atomicity

原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

修改—-》Buffer Pool修改—-》刷盘。可能会有下面两种情况：

事务提交了，如果此时Buffer Pool的脏页没有刷盘，如何保证修改的数据生效？ redo
如果事务没提交，但是Buffer Pool的脏页刷盘了，如何保证不该存在的数据撤销？(会出现这种情况，刷盘是异步的，和提没提交有没有关系，commit之后应该会记录point)Undo

每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中。

如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢 失。

如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了。

6.2 持久性

持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

如下图所示，一个“提交”动作触发的操作有：binlog落地、发送binlog、存储引擎提交、flush_logs， check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。

MySQL的持久性也与WAL技术相关，redo log在系统Crash重启之类的情况时，可以修复数据，从而保 障事务的持久性。通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持 久性。

6.3 隔离性

隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。

InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。锁和多版本控制（MVCC）技术就是用于保障隔离性的（后面课程详解）。

6.4 一致性

一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内容，分别是约束一致性和数据一致性。

约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持 Check 。

数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是 单单依赖于某一种技术。

一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个 特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属 于业务逻辑范畴。

ACID 及它们之间的关系如下图所示，4个特性中有3个与 WAL 有关系，都需要通过 Redo、Undo 日志 来保证等。

WAL的全称为Write-Ahead Logging，先写日志，再写磁盘。

7事务控制的演进