【5_Mysql】

• 查询语句《MySQL 实战45讲》
• MySQL 5.6参考手册:https://dev.MySQL.com/doc/refman/5.6/en/

MySQL

MySQL 是一种关系型数据库，主要用于 持久化存储 我们的系统中的一些数据（比如用户信息）。

SQL（Structured Query Language：结构化查询语言）

常见的关系型数据库：MySQL、SQL Server、Oracle …

MySQL 的默认端口号是3306。

存储引擎

1、查看 MySQL 提供的所有存储引擎:

show engines;

MySQL 当前默认的存储引擎是 InnoDB，只有 InnoDB 支持事务。

2、查看 MySQL 当前默认的存储引擎：

show variables like '%storage_engine%';

3、查看表的存储引擎

show table status like "table_name" ;

MyISAM 和 InnoDB 的区别

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。

​ MyISAM 不支持 事务和 行级锁，而且最大的缺陷就是 崩溃后无法安全恢复。

MySQL 5.5 版本后默认的存储引擎为 InnoDB事务性数据库引擎）。

• 区别：

1、是否支持行级锁

MyISAM 只有 表级锁(table-level locking)，而 InnoDB 支持 行级锁(row-level locking)和 表级锁，默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 存储引擎的锁的算法有三种：

• Record lock：记录锁，单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

2、是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，具有 提交(commit)和 回滚(rollback)事务的能力。

3、是否支持外键

MyISAM 不支持，而 InnoDB 支持。

4、是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

InnoDB 支持数据库异常崩溃后的安全恢复，恢复的过程依赖于 redo log(重做日志) 。

5、是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

毕竟 MyISAM 连 行级锁都不支持。

MVCC 可以看作是 行级锁的一个升级，可以有效 减少加锁操作，提供性能。

锁

https://blog.csdn.net/qq_38151401/article/details/104144504

https://blog.csdn.net/qq_40378034/article/details/90904573

没整理完

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和 行锁。

一、全局锁

全局锁就是对整个数据库实例加锁。

-- MySQL提供了一个加全局读锁的方法
Flush tables with read lock

注：可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放

当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是：做全库逻辑备份。也就是把整库每个表都select出来存成文本。

但是让整个库都只读，可能出现以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟
在可重复读隔离级别下开启一个事务能够拿到一致性视图

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。single-transaction只适用于所有的表使用事务引擎的库

1.既然要全库只读，为什么不使用set global readonly=true的方式？

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

二、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

• 表锁

/* 锁表 */
表锁定只用于防止其它客户端进行不正当地读取和写入
MyISAM 支持表锁，InnoDB 支持行锁
-- 锁定
    lock tables tbl_name … read/write
-- 解锁
    unlock tables
-- 解锁,也可以在客户端断开的时候自动释放

lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

如果在某个线程A中执行lock tables t1 read,t2 wirte;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许。

• 元数据锁 MDL。

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行

在MySQL5.5版本引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁

读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，需要特别小心，以免对线上服务造成影响

session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁，因此可以正常执行。之后sesession C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

1.如果安全地给小表加字段？

首先要解决长事务，事务不提交，就会一直占着DML锁。在MySQL的information_schema库的innodb_trx表中，可以查到当前执行的事务。如果要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务

2.如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而又不得不加个字段，该怎么做？

在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程

三、行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。

行锁就是针对数据表中行记录的锁。比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

1、两阶段锁协议

事务A持有的两个记录的行锁都是在commit的时候才释放的，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行

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

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

假设要实现一个电影票在线交易业务，顾客A要在影院B购买电影票。业务需要涉及到以下操作：

1.从顾客A账户余额中扣除电影票价

2.给影院B的账户余额增加这张电影票价

3.记录一条交易日志

为了保证交易的原子性，要把这三个操作放在一个事务中。如何安排这三个语句在事务中的顺序呢？

如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度

2、死锁和死锁检测

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

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

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑
在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的

正常情况下还是要采用主动死锁检查策略，而且innodb_deadlock_detect的默认值本身就是on。主动死锁监测在发生死锁的时候，是能够快速发现并进行处理的，但是它有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁

如果所有事务都要更新同一行的场景，每个新来的被堵住的线程都要判断会不会由于自己的加入导致死锁，这是一个时间复杂度是O(n)的操作

怎么解决由这种热点行更新导致的性能问题？

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

2.控制并发度

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

查询缓存

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用

my.cnf 加入以下配置，重启 MySQL 开启查询缓存

query_cache_type=1
query_cache_size=600000

MySQL 执行以下命令也可以开启查询缓存

set global  query_cache_type=1;
set global  query_cache_size=600000;

开启查询缓存后 在同样的查询条件以及数据情况下，会直接在缓存中返回结果。

这里的 查询条件 包括：查询本身、当前要查询的数据库、客户端协议版本号 等一些可能影响结果的信息。

因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。

缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十 MB 比较合适。此外，还可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

数据库存储时间

3. date:      日期，只包含年月日，        yyyy-MM-dd
4. datetime:  日期，包含年月日时分秒	   yyyy-MM-dd HH:mm:ss
5. timestamp: 时间戳类型	包含年月日时分秒  yyyy-MM-dd HH:mm:ss	-- 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值

切记不要用字符串存储日期

1. 字符串占用的空间更大！
2. 字符串存储的日期效率比较低（逐个字符进行比对），无法用日期相关的 API 进行计算和比较。

Datetime 和 Timestamp 之间抉择

通常我们都会首选 Timestamp。

2.1 DateTime 类型没有时区信息

DateTime 类型是没有时区信息的（时区无关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。

当你的时区更换之后，比如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。

Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，

#建表
create table `time_zone_test`(
	`id` bigint(20) not null auto_increment,
    `date_time` datetime default null,
    `time_stamp` timestamp not null default current_timestamp on update current_timestamp,
    primary key(`id`)    
)engine=InnoDB default charset=utf8; 

#插入数据
insert into time_zone_test(date_time,time_stamp) values(now(),now());
#查看数据
select date_time,time_stamp from time_zone_test;

修改当前会话的时区:

set time_zone='+8:00';

再次查看数据：

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
+---------------------+---------------------+Copy to clipboardErrorCopied

扩展：一些关于 MySQL 时区设置的一个常用 sql 命令

# 查看当前会话时区
SELECT @@session.time_zone;
# 设置当前会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 数据库全局时区设置
SELECT @@global.time_zone;
# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';Copy to clipboardErrorCopied

2.2 DateTime 类型耗费空间更大

Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

• DateTime ：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp： 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Timestamp 在不同版本的 MySQL 中有细微差别。

3 再看 MySQL 日期类型存储空间

下图是 MySQL 5.6 版本中日期类型所占的存储空间：

可以看出 5.6.4 之后的 MySQL 多出了一个需要 0 ～ 3 字节的小数位。DateTime 和 Timestamp 会有几种不同的存储空间占用。

为了方便，本文我们还是默认 Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。

4.数值型时间戳是更好的选择吗？

很多时候，我们也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。

这种存储方式的具有 Timestamp 类型的所具有一些优点，并且使用它的进行日期排序以及对比等操作的效率会更高，跨系统也很方便，毕竟只是存放的数值。缺点也很明显，就是数据的可读性太差了，你无法直观的看到具体时间。

时间戳的定义如下：

时间戳的定义是从一个基准时间开始算起，这个基准时间是「1970-1-1 00:00:00 +0:00」，从这个时间开始，用整数表示，以秒计时，随着时间的流逝这个时间整数不断增加。这样一来，我只需要一个数值，就可以完美地表示时间了，而且这个数值是一个绝对数值，即无论的身处地球的任何角落，这个表示时间的时间戳，都是一样的，生成的数值都是一样的，并且没有时区的概念，所以在系统的中时间的传输中，都不需要进行额外的转换了，只有在显示给用户的时候，才转换为字符串格式的本地时间。

数据库中实际操作：

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
|                            1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32       |
+---------------------------+
1 row in set (0.01 sec)Copy to clipboardErrorCopied

5.总结

MySQL 中时间到底怎么存储才好？Datetime?Timestamp? 数值保存的时间戳？

好像并没有一个银弹，很多程序员会觉得数值型时间戳是真的好，效率又高还各种兼容，但是很多人又觉得它表现的不够直观。这里插一嘴，《高性能 MySQL 》这本神书的作者就是推荐 Timestamp，原因是数值表示时间不够直观。下面是原文：

每种方式都有各自的优势，根据实际场景才是王道。下面再对这三种方式做一个简单的对比，以供大家实际开发中选择正确的存放时间的数据类型：

索引

https://www.jianshu.com/p/8991cbca3854

https://blog.csdn.net/wangfeijiu/article/details/113409719?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control

https://blog.csdn.net/u013308490/article/details/83001060?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-3.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-3.control

索引是一种用于 快速查询和检索数据的数据结构。（常见的索引结构有: B 树， B+树和 Hash）

索引的作用就相当于目录的作用。

优点 ：

​ 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。

​ 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

​ 创建索引和维护索引 需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

​ 索引需要使用 物理文件存储，也会耗费一定空间。

• 哪些情况需要创建索引

  ​ 1、主键自动建立唯一索引

  ​ 2、频繁作为 查询条件的字段应该创建索引(where 后面的语句)

  ​ 3、查询中与其它表关联的字段，外键关系建立索引

  ​ 4、单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

  ​ 5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

  ​ 6、查询中统计或者分组字段

• 哪些情况不要创建索引

​ 1、表记录太少

​ 2、 经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

​ 3、Where条件里用不到的字段不创建索引

​ 4、 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

• 索引存储在文件系统中

索引是占据 物理空间的，在不同的存储引擎中，索引存在的文件也不同。存储引擎是基于 表的，以下分别使用MyISAM和InnoDB存储引擎建立两张表。

1、存储引擎为MyISAM：

​ .frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
​ .MYD：MyISAM DATA，用于存储MyISAM表的数据
​ .MYI：MyISAM INDEX，用于存储MyISAM表的索引相关信息

2、存储引擎为InnoDB：

`.frm`：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
`.ibd`：InnoDB DATA，表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

索引和实际的数据都是存储在磁盘中的，只不过在进行数据读取的时候会优先把索引加载到内存。

磁盘预读：内存和磁盘交互的时候有一个最小的逻辑单元，称为页(datapage)，一般是4k或8k，由操作系统决定。一般在进行数据读取的时候会读取页的整数倍，InnoDB存储引擎加载数据时，读取16kb的数据。（分块读取）

mysql假设一行数据大小为1k，则一颗层高为3的b+树可以存放多少条数据？

mysql页默认大小16k，如果数据行大小1k，叶子节点存放的完整数据，则叶子节点一页可以放16条数据；非叶子节点页面存放的是主键和指针，所以主要看主键是啥类型，假设是integer，则长度8字节，指针大小在innodb是6字节，一共14字节，所以非叶子节点每页可以存16384/14=1170个主键数据（1170个分叉），则三层b+树数据可以存1170117016=21902400条数据。（千万级别）

1. ·对于innodb的B+树索引来说，所有的行数据都放在叶子节点，非叶子节点不存储行数据，是为了存储更多的索引建，从而降低B+树的高度，减少IO的次数。所以一般来说高度为3的树就可以存储千万级别的数据。
2. 现在来回答问题：
3. ·假设现在有一个高度为3的B+树，当我们查找数据时，会先发生第一次IO加载高度为1的磁盘块1到内存，然后在内存中二分查找得到下次需要加载的高度为2的磁盘块2的地址，然后再发生第二次IO加载磁盘块2到内存中再比较得到高度为3的磁盘块3的地址，再发生第三次IO加载磁盘3到内存比较就可以得到数据了。
4. ·mysql加载索引是以磁盘块（页）为单位的，并不是一次性全部加载到内存中，每次只需要加载需要的那一块。然后一般高度为3的树就可以存储千万级别的数据，所以一般只需要3次IO就可以得到数据，速度就很快了

int不是4个字节吗？bigint才8个字节??

索引的底层数据结构

为什么不选择哈希表而选择B+树

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据。

hash = hashfunc(key)
index = hash % array_size

但是！哈希算法有 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。

通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

然而MySQL并没有使用哈希表作为索引的数据结构

1、Hash 冲突问题

2、Hash 索引 不支持 顺序和范围查询（范围查找的时候需要挨个遍历，效率低。）

试想一种情况:

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。

B 树& B+树

B 树也称 B-树，全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。[B 是 Balanced （平衡）]

目前大部分数据库系统及文件系统都采用 B-Tree 或其 变种 B+Tree 作为 索引结构。

B 树& B+树异同：

• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的；B+树的叶子节点有一条 引用链 指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做 二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是 从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

• MyISAM 引擎中，B+Tree 叶子节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
• InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

B+树的叶子节点链表是双向的，用于倒序

索引和实际的数据都是存储在磁盘中的，只不过在进行数据读取的时候会优先把索引加载到内存。

磁盘预读：内存和磁盘交互的时候有一个最小的逻辑单元，称为页(datapage)，一般是4k或8k，由操作系统决定。一般在进行数据读取的时候会读取页的整数倍，InnoDB存储引擎加载数据时，读取16kb的数据。（分块读取）

Mysql的InnoDB是以页为存储单位的，每个B+Tree的叶子节点都是一个页的大小的倍数，默认一页的大小是16K

也就是每一个页都包含两个页指针，一个是previous page指针，指向上一个页，一个是next page指针，指向下一个页。
头部还有Page的类型信息和用来唯一标识Page的编号。根据这个指针分布可以想象到Page链接起来就是一个双向链表。

由于一个B+Tree的叶子节点是一个页，所以每个叶子节点之间是一个双向链表的结构。

索引类型

InnoDB索引分类：

• 按功能划分：

  ​ 主键索引

  ​ 辅助索引（二级索引）

• 物理分类

  ​ 聚簇索引

  ​ 非簇集索引

主键索引 属于聚簇索引，二级索引 属于非聚簇索引。

innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据一般需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，

主键索引和 辅助索引

• 主键索引

数据表的 主键列 使用的就是 主键索引。

【一张数据表有只能有一个主键，并且主键不能为 null，不能重复】

MySQL 的 InnoDB 表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有 唯一索引 的字段，如果有，则选择该字段为默认的主键；否则 InnoDB 将会 自动创建 一个 6Byte 的 自增主键。

• 辅助索引（二级索引）

二级索引的 叶子节点 存储的数据是 主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引 等索引都属于 二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种 约束。唯一索引的 属性列 不能出现重复的数据，但是允许数据为 NULL，一张表允许创建 多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于 字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚簇索引与非聚簇索引

（1）聚簇索引

索引结构和数据 一起存放的索引。（找到索引也就找到了数据）

根据表的主键构造一棵B+树且B+树叶子节点的data域 存放的都是 表的行记录数据（主键值也是行内数据）(主键+数据)，非叶子节点存储 索引。

• 聚集索引的形成：

InnoDB要求表必须有主键（MyISAM可以没有），Innodb会按照如下规则进行处理：
　　1、如果一个主键被定义了，那么这个 主键 就是作为 聚集索引
　　2、如果没有主键被定义，那么该表的第一个 唯一非空索引 被作为聚集索引
　　3、如果没有主键也没有合适的唯一索引，那么Innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

优点：

​ 聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点：

1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

（2）非聚簇索引

索引结构和数据 分开存放的索引。B+树叶子节点的data域存放的是 ==主键值(InnoDB)==或 索引对应数据的指针(MyISAM)。

优点：

​ 更新代价比聚簇索引要小 。非聚集索引的叶子节点是不存放数据的。

缺点：

1. 跟聚簇索引一样，非聚簇索引也依赖于有序的数据
2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

（4）总结

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

（5）InnoDB和MyISAM 的存储索引对比

1、InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引。

2、InnoDB的 主键索引 是聚簇索引， 辅助索引是 非聚簇索引。

MyISAM的 主索引和辅助索引(Secondary key)都是 非聚簇索引，只是主索引要求 key 唯一，而辅助索引的 key 可以重复。

3、对于InnoDB，非聚簇索引的叶子节点存放的是 主键值，而MyISAM 非聚簇索引的叶子节点存放的是：索引对应数据的指针。

4、MYISAM 引擎表的 索引存在 .MYI 文件中， 索引对应数据的指针 存在.MYD 文件中。 InnoDB 引擎的表的索引和数据都存在 .ibd文件中。

5、因为InnoDB的数据文件.idb按主键聚集，所以InnoDB必须有主键（MyISAM可以没有）

回表查询和 覆盖索引

回表查询

非聚集索引和聚集索引都是采用 B+树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。所以索引不是建立越多越好的。

• 索引检索过程：

t(id PK, name KEY, sex, flag);
-- id是聚簇索引，name是 非聚簇索引

表中有四条记录：
+--------------------+
| 1, shenjian, m, A  |
+--------------------+
| 3, zhangsan, m, A  |
+--------------------+
| 5, lisi, m, A      |
+--------------------+
| 9, wangwu, f, B    |
+--------------------+

id是聚簇索引，叶子节点存的是，id索引下的 表对应的行记录数据。（叶子节点的key存的是主键，data域存的是 表对应的行记录数据）

name是 非聚簇索引，叶子节点存的是，name索引下的 主键值。（叶子节点的key存的是 name值，data域存的是 主键值）

name是 非聚簇索引。把名字值 当做索引

所以：

– 用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。
SELECT name FROM t WHERE name='lisi';
– 那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

两个B+树索引分别如上图：

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

（2）name为KEY，非聚簇索引，叶子节点存储主键值（id值）；

1、聚簇索引：

select * from table where id = 1256;

先根据 id B+树 定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。

这里不讲解平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。

2、非聚集索引：

先查到记录对应的主键值 ， 再使用主键的值通过 聚簇索引查找到需要的数据。

select * from t where name='lisi';

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值 id=5；

（2）在通过聚集索引定位到 行记录数据；

这就是所谓的回表查询，先定位主键值，再定位 行记录数据，它的性能较扫一遍索引树更低。

**3、补充：**非聚簇索引 不一定 回表查询——覆盖索引

 -- 用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。
 SELECT name FROM t WHERE name='lisi';
 -- 那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样（MyISAM的 主索引和辅助索引(Secondary key)都是 非聚簇索引）。

-- SQL 查的就是主键
SELECT id FROM table WHERE id=1;
-- 主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

覆盖索引

覆盖索引： 需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是 主键值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

如上补充

create table user (
	id int primary key,
	name varchar(20),
	sex varchar(5),
	index(name)
)engine=innodb;

-- 不需要回表查询
select name from user where name = 'shenjian'
select id, name from user where name = 'shenjian'

-- 能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。  Extra：Using index。

-- 需要二次的查询去获取原数据行的 sex：
select id,name, sex from user where name = 'shenjian'

-- 索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。  Extra：Using index condition

联合索引

把(name)单列索引升级为**联合索引(name, sex)**就不同了。

创建联合索引

create table user (
  id varchar(32) primary key, -- 主键索引
  name varchar(100),
  phone varchar(20),
  address varchar(200)
);
insert into user(id, name, phone, address) values ('250', '胡歌', '17635124678', '湖南省长沙市');
-- 创建联合索引
alter table user add index inx_merchant_name_phone_address(name, phone, address);
-- 创建非联合索引
create index inx_merchant_name_phone on user(name, phone, address);

或

create table user (
	id int primary key,
	name varchar(20),
	sex varchar(5),
	index(name, sex)    -- 叶子节点的key存的是：name和sex的值， data域存的是：主键id值
)engine=innodb;

select id,name ... where name='shenjian';
select id,name,sex ... where name='shenjian';

-- 都能够命中索引覆盖，无需回表。 Extra：Using index

哪些场景可以利用索引覆盖来优化SQL？

某些场景中，可以考虑将要查询的所有列变成组合索引，此时会使用索引覆盖，加快查询效率。

场景1：全表count查询优化

原表为：user(PK id, name, sex);

直接：select count(name) from user;不能利用索引覆盖。

添加索引：alter table user add key(name);就能够利用索引覆盖提效。

场景2：列查询回表优化

select id,name,sex ... where name='shenjian';
-- 将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

场景3：分页查询

select id,name,sex ... order by name limit 500,100;

-- 将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

最左原则

联合索引，对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)

联合索引需要遵循 最左匹配原则。（查询条件精确匹配索引的左边连续一列或几列，则构建对应列的组合索引树），在检索数据时 也从 联合索引的 最左边开始匹配。

create table t(
    a int not null,
    b char(10) not null,
    c int not null,
    d varchar(20) not null,
    index abc(a,b,c)  -- abc组合索引
)engine=innodb charset=utf8;
    
insert into t values(1,'hello',1,'world');
insert into t values(2,'hello',2,'mysql');

-- 会构建组合索引树
explain select *from t where a=1;
explain select *from t where a=1 and b='hello';
explain select *from t where a=1 and c='world'; -- 只会构建包含a的索引树
explain select *from t where a=1 and b='hello' and c='world';

-- 不会构建组合索引树
explain select *from t where b='hello' and c='world';
explain select *from t where c='world';
explain select *from t where b='hello';


-- and优化链接
-- MySQL查询优化器会优化and连接，将组合索引列规则排号。（比如：b and a 等同于 a and b）
explain select *from t where a=1 and b='hello';
explain select *from t where b='hello' and a=1;

再理解：

参考

创建的idx_t1_bcd(b,c,d)联合索引，相当于创建了(b)、（b、c）（b、c、d）三个索引。

我们看，联合索引是首先使用多列索引的第一列（b列）构建的索引树，当b列值相等时再以c列排序，若c列的值也相等则以d列排序。

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

• 不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎 通过索引检索到数据，并返回给MySQL服务器，然后服务器对数据进行过滤 判断数据是否符合条件 。
• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎 通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化 可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

创建索引的注意事项

1、选择合适的字段创建索引：

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2、被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3、尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4、注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5、考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

使用索引的一些建议

• 对于 中到大型表索引都是非常有效的，但是 特大型表的话维护开销会很大，不适合建索引
• 避免 where 子句中对字段施加函数，这会造成无法命中索引。
• 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
• 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

MySQL 如何为表字段添加索引

1.添加 primary key（主键索引）、聚集索引

alter table `table_name` add primary key(`colum`)

2.添加 unioue（唯一索引）

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加index（普通索引）

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加 fulltext（全文索引）

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

索引失效

参考1、参考2、参考3、参考4

1、查询条件中有or

用or分割开的条件， 如果or前的条件中的列 有索引，而后面的列中 没有索引，那么涉及的索引都不会被用到。

-- id字段是索引列 ， 而name不是索引列，中间是or进行连接是不走索引的。type为All，仍然是走的全表扫描。
explain select * from t_goods where id = '111' or name = '蓝光电视';

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2、查询条件为字符串类型（数字字符串），一定要在条件中将数据 使用引号 引用起来，未使用 ‘’ 可能导致索引失效

insert into t_goods(id, name, user_id) values ('123', '西瓜');
-- 索引失效（字符串与数字比较，类型不匹配，数据库引擎默认隐式转换为浮点数再比较）
explain select * from t_goods where id = 123;
-- 使用索引
explain select * from t_goods where id = '123';

3、联合索引，不符合最左匹配原则，索引可能失效

联合索引，最左原则

联合索引 index(b,c,d) ，相当于创建了(b)、（b、c）（b、c、d）三个索引。

4、通配符‘like’、‘_’可能导致索引失效

以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。【如果使用了，可以应用索引覆盖来解决。】

-- 索引失效
explain select * from t_goods where name like '%电视';
explain select * from t_goods where name like '%光电%';
explain select * from t_goods where name like '__电视';
explain select * from t_goods where name like '_光电_';
-- 使用索引
explain select * from t_goods where name like '蓝光%';
explain select * from t_goods where name like '蓝光__';

-- 使用索引（覆盖索引，只查询索引字段）
explain select name from t_goods where name like '%电视';
-- 使用索引（主键索引）
explain select id, name from t_goods where name like '%电视';
-- 索引失效
explain select id, name, user_id from t_goods where name like '%电视';

5、在索引列上使用MySQL内置函数，索引可能失效

-- 索引失效
explain select * from t_goods where concat(name, '1') = '蓝光电视1';

6、在索引列上使用+、-、*、/运算，索引可能失效

-- 索引失效
explain select * from t_goods where type_id + '1' = '2221';

7、左连接 或者 右连接 查询关联的字段编码格式不一样

首先建两张表，一个user，一个job，建表语句如下：

create table `user` (
	`name` varchar(255) default null,
	`age` int(11) default null,
	`address` varchar(255) default null,
	`id` int(11) not null auto_increment, 
	primary key (`id`),
    key `index_name` (`name`),
    key `index_age` (`age`),
    key `index_address` (`address`)
) engine=InnoDB auto_increment=3 default charset=utf8mb4 utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注意user 表的name字段编码是utf8mb4，而job表的name字段编码为gbk。执行左外连接查询：

explain select a.name,b.name,b.job 
from
user a
left JOIN job b
ON a.name =b.name

执行计划如下：

等等…

查看索引使用情况

show status like 'Handler_read%';

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低 越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的 性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列， 该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应 该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说 明你的表索引不正确或写入的查询没有利用索引。

一条sql语句在mysql中如何执行的

MySQL 基础架构分析

• **连接器：**身份认证 和 权限相关(登录 MySQL 的时候)。
• **查询缓存：**执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• **优化器：**按照 MySQL 认为最优的方案去执行。
• **执行器：**执行前校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

Server 层基本组件介绍

1) 连接器

连接器主要和 身份认证和 权限相关的功能相关，就好比一个级别很高的门卫一样。

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

2) 查询缓存(MySQL 8.0 版本后移除)

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

3) 分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

4) 优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

5) 执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

语句分析

查询语句

-- 查询语句
select * from tb_student  A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

美团面试： 结合MySQL的组成模块，介绍下一条sql语句在mysql中如何执行的。比如查询语句

1、先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• **连接器：**身份认证 和 权限相关(登录 MySQL 的时候)。
• **查询缓存：**执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

2、通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=‘1’。

然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是

  要先看你的 SQL 语句要干嘛，（词法分析）

  再检查你的 SQL 语句语法是否正确。（语法分析）

3、接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

  a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
  b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

优化器 根据自己的优化算法 进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• **优化器：**按照 MySQL 认为最优的方案去执行。

4、先校验权限后执行。先进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用 数据库引擎接口，返回引擎的执行结果。

• **执行器：**执行前校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

更新语句

-- 修改张三年龄，注意在实际数据库肯定不会设置年龄这个字段的。
update tb_student A set A.age='19' where A.name=' 张三 ';

其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志。

MySQL 自带的日志模块是 binlog（归档日志，Server层日志） ，所有的存储引擎都可以使用。常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志，发生在引擎层），

redo log是物理日志，记录的是“在某个数据页上做了什么修改”；

binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

InnoDB 模式下执行流程：

1、执行器先 从引擎中获取到数据，如果有缓存就直接返回该数据，如果没在内存中，就把磁盘中的数据读入到内存并返回。

2、更改数据，然后调用引擎 API 接口，重新写入这一行数据

3、InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后通知执行器执行完成了，随时可以提交。

4、执行器收到通知后记录 binlog，并写入磁盘，

5、然后调用引擎的事务提交接口，把 redo log 改成 commit状态。

6、更新完成。

**为什么要用两个日志模块？为什么两阶段提交？**为什么 redo log 要引入 prepare 预提交状态？

参考

InnoDB 引擎是插件形式插入 MySQL 的，他特有 **redo log（重做日志，发生在引擎层）**日志模块，用于保证 crash-safe 能力。（crash-safe 能力：使数据库发生异常重启，之前提交的记录都不会丢失）。

MySQL 自带的binlog（归档日志，Server层日志） 日志模块只能 用来归档，没有 crash-safe 的能力。

因为两种日志属于不同的组件，为了保证数据的一致性，保证binlog和redolog一致，所以有了二阶段提交。

将redo log的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

先写日志再写数据

因为随机读写的效率要低于顺序读写，为了保证数据的一致性，可以先将数据通过顺序读写的方式写到日志文件中，然后再将数据写入到对应的磁盘文件中，这个过程顺序的效率要远远高于随机的效率。换句话说，如果实际的数据没有写入到磁盘，只要日志文件保存成功了，那么数据就不会丢失，可以根据日志来进行数据的恢复。

…

3、InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后通知执行器执行完成了，随时可以提交。

4、执行器收到通知后记录 binlog，并写入磁盘，

5、然后调用引擎的事务提交接口，把 redo log 改成 commit状态。

当在以下阶段发生crash，然后用binlog进行主从库同步：

完成3后，还未写binlog，所以该条修改不会写到备库里，redo log为prepare状态，在崩溃恢复时发现该事务并没有完成，会回滚，主库和备库都没有修改该条数据，保持了一致性。
完成4后，写完binlog，该条修改会同步到备库里，redo log仍为prepare状态，在崩溃恢复时发现redo log有prepare状态并且binlog里也存在该条完整事务，则会提交事务，主库和备库都会有该条修改，保持了一致性。

注：崩溃恢复的判断规则：

• 判断 redo log 里面的事务 是否完整，如果判断是完整的，也就是有了commit标识，就立即提交。
• 如果 redo log 只是预提交（prepare完整）但不是 commit 状态，则判断 binlog 是否完整，如果完整就提交 redo log，不完整就回滚事务。

这样就解决了数据一致性的问题。

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

总结

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM、InnoDB、Memory 等。
• 查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
• 更新语句执行流程如下：分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

mysql执行计划：使用 explain 可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句。

explain select *from emp;

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

type ：表示表的连接类型，range是指给定范围内的检索，比如 in(xx, xx) 或者 between。该类最好的时候是const（即表示为通过索引一次就找到了），最差的时候是all（需要遍历全表）。

key：表示实际使用的索引

ref:列与索引的比较

Extra：执行情况的描述和说明

本打算展开讲一下Extra的常见的几个值：Using index，Using index condition，Using where，其中Using index 表示使用了覆盖索引，其它的都不好总结。

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

可以简单举一个例子不？

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作就是：

1. 将小明的余额减少 1000 元
2. 将小红的余额增加 1000 元。

事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。

这样就不会出现小明余额减少而小红的余额却并没有增加的情况。

数据库事务

如果你的项目属于 单体架构的话，你接触到的往往就是 数据库事务。

平时，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

数据库事务作用：数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循 ===> 要么全部执行成功，要么全部不执行 。

# 开启一个事务
start transaction;

# 多条 SQL 语句
SQL1,SQL2...

## 提交事务
commit;

关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：

ACID 特性

原一隔持

1、原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；------------------------- undo log(回滚日志)

2、一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

3、隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的； ------------------------------ 锁机制、MVCC 等

4、持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。 ----------- redo log(重做日志)

• 数据事务的实现原理（MySQL 的 InnoDB 引擎）

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

undo log实现原子性

undo log一般是逻辑日志，根据行的数据变化进行记录

undo log作用1：当事务回滚时 用于将数据恢复到修改前的样子

比如执行一条update语句： update user set name = “李四” where id = 1; —修改之前name=张三

此时undo log记录一条相反的update语句（之前的语句）： update user set name = “张三” where id = 1;

如果这个修改出现异常，可以使用undo log日志来实现回滚操作，以保证事务的一致性。

1. 事务开始

2. 记录A=1到undo log中

3. 修改A=3

4. 将undo log 持久化磁盘

5. 将数据 持久化到磁盘

6. commit提交事务

之所以能同时 保证原子性 和持久化，是因为以下特点：

​ 更新数据前记录undo log。

​ 为了保证持久性，必须 将数据在 事务提交前 写到磁盘，只要事务成功提交，数据必然已经持久化到磁盘。

​ undo log 必须 先于 数据持久化到磁盘。如果在5，6之间发生系统崩溃，undo log是完整的，可以用来回滚。

​ 如果在1，4之间发生系统崩溃，因为数据没有持久化到磁盘，所以磁盘上的数据还是保持在事务开始前的状态。

undo log 具体是怎么回滚的

参考1、参考2

注意：如果事务中所有sql语句执行正确则需要自己手动提交commit；如果有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，而不是commit会给你自动判断和回滚。

…？

并发事务带来的问题

多个事务并发运行，操作相同的数据 来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

• 脏读（Dirty read）

  ​ 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 丢失修改（Lost to modify）

  ​ 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

  ​ 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

• 不可重复读（Unrepeatable read）

  ​ 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务修改了该数据，导致第一个事务两次读取的数据可能不一样。

• 幻读（Phantom read）

  ​ 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入或者删除了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的或者少了的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或减少了。

MySQL 事务隔离级别

SQL 标准定义了四个隔离级别：

• read-uncommitted（读取未提交）： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读 和 幻读。
• read-committed（读取已提交）： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• repeatable-read（可重复读）： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• serializable（可串行化）： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

---

隔离级别	脏读	不可重复读	幻读
read-uncommitted（读取未提交）	√	√	√
read-committed（读取已提交）	×	√	√
repeatable-read（可重复读）	×	×	√
serializable（可串行化）	×	×	×

• MySQL 的默认隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 repeatable-read（可重复读）。通过SELECT @@transaction_isolation;命令来查看

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| repeatable-read |
+-----------------+

MySQL InnoDB 的 repeatable-read（可重复读）并不保证避免幻读，需要应用使用 加锁读来保证。而这个加锁度使用到的机制就是 Next-Key Locks（间隙锁）。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 read-committed（读取已提交），但是你要知道的是 InnoDB 存储引擎默认使用 repeatable-read（可重复读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 serializable（可串行化） 隔离级别。

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持 分布式事务的实现。

分布式事务指的是：允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。

另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 serializable（可串行化）。

转账事务

多版本并发控制（MVCC）

参考

MVCC(Multi-Version Concurrency Control) 多版本并发控制

MVCC在MySQL InnoDB中的实现主要是 为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读.

（1）快照读（一致性非锁定读）

读取的是历史版本的数据。

执行普通的 select 语句
（不包括 select ... lock in share mode 、 select ... for update）

​ 如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)

（2）锁定读（当前读）

锁定读（当前读current read）: 读取的是数据的最新版本。

-- 如果执行的是下列语句，就是 锁定读（Locking Reads）
-- 锁定读会对读取到的记录加锁

select ...lock in share mode  -- 对记录加 `S` 锁，其它事务也可以加`S`锁，如果加 `x` 锁则会被阻塞
select ...for update          -- 对记录加 `X` 锁，且其它事务不能加任何锁
insert、update、delete 操作

InnoDB对MVCC的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。

隐藏字段

在内部，InnoDB 存储引擎为 表的每行数据 添加了三个 隐藏字段（用户不可见）：

DB_TRX_ID    -- 最后一次插入或更新该行的事务id
DB_ROW_ID    -- 隐藏主键
DB_ROLL_PTR  -- 回滚指针,指向该行的 `undo log（回滚日志）`

• DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
• DB_ROW_ID（6字节）：隐藏主键，如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该id来生成聚簇索引
• DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log（回滚日志） 。如果该行未被更新，则为空

undo-log（回滚日志）

undo log一般是逻辑日志，根据行的数据变化进行记录

undo log 主要有两个作用：

• 当事务回滚时 用于将数据恢复到修改前的样子 （见上）
• 另一个作用是在MVCC 中存储历史版本的数据。当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读（读取快照）。

在 InnoDB存储引擎中 undo log 分为两种： insert undo log 和 update undo log：

1、insert undo log ：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作。（undo log日志的删除是通过通过后台purge线程进行回收处理的。）

insert 时的数据初始状态：

2、update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除

数据第一次被修改时：

数据第二次被修改时：

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录

ReadView

Read View就是事务进行 快照读 操作的时候生产的 读视图(Read View)

Read View 主要是用来做 可见性判断，里面保存了 当前对本事务不可见的 其他未提交的活跃事务的ID

主要有以下字段：

`m_ids`：           -- `Read View` 创建时，其他未提交的 活跃事务ID列表。当前事务也是不可见的。
`m_up_limit_id`：   -- 活跃事务列表 `m_ids` 中最小的事务ID
`m_low_limit_id`：  -- 下一个将被分配的事务ID

• m_low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。大于这个ID的数据版本均不可见
• m_up_limit_id：活跃事务列表 m_ids 中最小的事务ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个ID的数据版本均可见
• m_ids：Read View 创建时其他未提交的活跃事务ID列表。创建 Read View 时，将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
• m_creator_trx_id：创建该 Read View 的事务ID

数据可见性算法

在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个读视图（Read View）。

当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务ID进行比较，判断是否满足可见性条件

具体的比较算法如下：图源

1. 如果记录 DB_TRX_ID < m_up_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了，所以该记录行的值对当前事务是可见的
2. 如果 DB_TRX_ID >= m_low_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤5
3. m_ids 为空，则表明在当前事务创建快照之前，修改该行的事务就已经提交了，所以该记录行的值对当前事务是可见的
4. 如果 m_up_limit_id <= DB_TRX_ID < m_up_limit_id（即 DB_TRX_ID是否在活跃事务中），表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表 m_ids 进行查找（源码中是用的二分查找，因为是有序的）
   • 如果在活跃事务列表 m_ids 中能找到 DB_TRX_ID，表明：①在当前事务创建快照前，该记录行的值被事务ID为 DB_TRX_ID 的事务修改了，但没有提交；或者 ②在当前事务创建快照后，该记录行的值被事务ID为 DB_TRX_ID 的事务修改了。这些情况下，这个记录行的值对当前事务都是不可见的。跳到步骤5
   • 在活跃事务列表中找不到，则表明“id为trx_id的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见
5. 在该记录行的 DB_ROLL_PTR 指针所指向的 undo log 取出快照记录，用快照记录的 DB_TRX_ID 跳到步骤1重新开始判断，直到找到满足的快照版本或返回空

Read View 主要是用来做 可见性判断，里面保存了 当前对本事务不可见的 其他未提交的活跃事务的ID

MVCC解决不可重复读问题

在事务隔离级别 RC 和 RR （InnoDB存储引擎的默认事务隔离级别）下， InnoDB 存储引擎使用 MVCC（非锁定一致性读），但它们生成 Read View 的时机却不同

• 在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids列表)
• 在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids列表）

RC的隔离级别，每次SQL语句结束后都会调用read_view_close_for_mysql将read view从事务中删除，这样在下一个SQL语句启动时，会判断trx->read_view为NULL，从而重新申请。对于RR隔离级别，则SQL语句结束后不会删除read_view，从而下一个SQL语句时，使用上次申请的，这样保证事务中的read view都一样，从而实现可重复读的隔离级别

MVCC➕Next-key-Lock防止幻读

1、执行普通 select，此时会以 MVCC 快照读的方式读取数据

在快照读的情况下，RR 隔离级别只会在事务开启后 的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止了快照读下的 “幻读”。

2、执行select…for update/lock in share mode、insert、update、delete等 当前读

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 **Next-key Lock（间隙锁）**来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读

总结：

InnoDB存储引擎在 Repeatable Read 级别下 MVCC 实现了 可重复读 和 防止部分幻读（防止了快照读下的幻读。）

InnoDB存储引擎在 Repeatable Read 级别下通过 MVCC和 Next-key Lock（间隙锁） 来解决幻读问题：

SQL调优

了解不不全

慢查询优化，会考虑哪些优化

参考

1、优化数据库结构

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

① 将字段很多的表分解成多个表

​ 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

② 增加中间表

​ 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

2、分解关联查询

将一个大的查询分解为多个小查询。

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，例如：

select *from tag
	join tag_post on tag_id = tag_id
	join post on tag_post.post_id = post_id
	where tag.tag = 'mysql';
	
分解为：
select *from tag where tag = 'mysql';
select *from tag_post where tag_id = 1234;
select *from post where post.id in (123,456,567);

3、优化LIMIT分页

分页的操作通常会使用 limit加上偏移量 的方法实现，同时加上合适的order by 子句。

如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是 当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

优化此类查询的一个最简单的方法是 使用索引覆盖扫描**，**而不是查询所有的列

MySQL分页

参考

使用数据库提供的SQL语句

select * from 表名 limit m,n;   -- 指从第m+1条记录开始，取n条记录。
select * from stu limit 2,4;   -- 取stu表中第3至第6条，共4条记录。

• 适应场景: 数据量较少的情况(元数组 百/千级)。

• 分析:

  全表扫描，速度会很慢 且 有的数据库结果集返回不稳定（如某次返回1,2,3,另外的一次返回2,1,3）。

  Limit限制的是从结果集的M+1位置处取出N条输出，其余抛弃。

2、建立主键或唯一索引, 利用索引(假设每页10条)

使用索引覆盖扫描**，**而不是查询所有的列

select * from 表名 where id_pk > (pageNum*10) limit m

• 适应场景: 适用于数据量多的情况(元组数 上万)。

• 缺分析: 索引扫描，速度会很快。但因为数据查询出来并不是按照id_pk排序的，所以会有漏掉数据的情况，只能方法3。

**3、基于索引再排序 **order by

select * from 表名 where id_pk > (pageNum*10) order by id_pk asc limit m

-- asc  升序，默认的。
-- desc  降序。

• 适应场景: 适用于数据量多的情况(元组数上万)。最好order by后的列对象是主键或唯一索引，数据库结果集有序且稳定。

• 分析: 索引扫描，速度会很快。

4、基于索引使用prepare

（第一个问号表示pageNum，第二个？表示每页元组数）

prepare stmt_name from select *from 表名 where id_pk>(?*?) order by id_pk asc limit m

• 适应场景: 大数据量。

• 分析: 索引扫描，速度会很快。prepare语句又比一般的查询语句快一点。

5、利用MySQL支持ORDER操作可以利用索引快速定位部分元组，避免全表扫描

比如: 读第1000到1019行元组(id_pk是主键/唯一键)。

select *from 表名 where id_pk>=1000 order by id_pk asc limit 0,20

方法6: 利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组. 道理同方法5

—如(id是主键/唯一键,蓝色字体时变量):

利用子查询示例:

复制代码 代码如下:
SELECT* FROMyour_table WHEREid <=
(SELECTid FROMyour_table ORDER
BYid descLIMIT ($page-1)\ast$pagesize ORDERBYid desc
LIMIT $pagesize

利用连接示例:
复制代码 代码如下:
SELECT* FROMyour_table ASt1
JOIN(SELECTid FROMyour_table ORDERBY
id descLIMIT ($page-1)\ast$pagesize ASt2
WHERE
t1.id <= t2.id ORDERBYt1.id descLIMIT $pagesize;

方法7: 存储过程类（最好融合上述方法5/6）

—语句样式: 不再给出

—适应场景: 大数据量. 作者推荐的方法

—原因: 把操作封装在服务器，相对更快一些。

分库分表