mysql知识合集

1.基础：

SQL语句分类

1. Data Definition Language：简称DDL；数据定义语言，用于建库，建表等操作
2. Data Manipulation Language：简称DML；数据操纵语言，用于对记录的增删改操作
3. Data Query Language：简称DQL；数据查询语言，用于查询记录
4. Data Control Language：简称DCL；数据控制语言，用于对用户进行操作，如：创建用户，给用户权限

基本语句的操作实例：

https://blog.csdn.net/Bb15070047748/article/details/119609383

DQL中排序order by [ASC/DESC] ,ASC升序，DESC降序。分组group by，having by，以及聚合函数的使用；分页limit。

https://blog.csdn.net/Bb15070047748/article/details/119611568

注意：编码问题，当出现编码不一致的情况会出现乱码的问题··

-- 查询语句中涉及到的所有关键字，以及执行的先后顺序
select 查询列表         ⑦ 
from 表                ①
连接类型 join 表2       ②
on 连接条件             ③
where 筛选条件          ④
group by 分组列表       ⑤
having 分组后的筛选      ⑥
order by 排序列表       ⑧
limit 偏移，条目数       ⑨

每个命令的实现逻辑

数据库备份，约束：

备份：在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

约束：一般在创建表的时候给表的字段添加各种约束，从而保证输入到表中的数据是正确的。保证数据的正确性，完整性和有效性。违反约束的数据是不能添加到表中去的。如果表已经存在，并且表中已经有数据，添加约束的时候如果表中的数据已经违反了现在要添加的约束，约束会添加失败。

https://blog.csdn.net/Bb15070047748/article/details/119611568

数据规范化（三大范式），子查询，连接查询（内连接，左外连接，右外连接，全连接），DCL数据控制语言（用户管理，权限管理）

https://blog.csdn.net/Bb15070047748/article/details/119612071

​

JDBC，SQL注入问题，连接池

Java Database Connectivity，简称JDBC；是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

sql注入：但使用简单的sql语句对数据库操作时，接收来自前端的查询条件，但前端的查询条件可以通过特殊改变，误导数据查询。例如，当按照用户名和密码查询数据库时，如果密码输入为’ ‘123456’ or ‘1’=‘1’ ';执行操作时，即使密码不是123456，select也会返回查询成功的信息，用户也就登录了系统，显然这是错误的。这就是sql注入的问题

https://blog.csdn.net/Bb15070047748/article/details/119612982

2.数据库设计

1.三大范式

第一范式：

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式：

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情，消除局部依赖。

第三范式：

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

2.日志

Mysql中7种日志_从昨天的博客-CSDN博客_mysql日志

基于Redo Log和Undo Log的MySQL崩溃恢复流程_dz45693的博客-CSDN博客

redo log：可以用来保证提交的操作都可以被执行，且都磁盘中的数据也会一致，先写日志再写磁盘，日志中的操作是否已经执行由check point标记，正常来说在check point前的数据都被正确的写入磁盘，但会存在写入磁盘时崩溃的情况，导致数据没写完，所有就对磁盘中的数据也进行标记。通过比较两个标记就可以正确的写磁盘。

崩溃处理

首先常用日志的执行顺序：

当数据库表的更新操作发生时，当存储引擎是innodb时，redo log和binlog的记录顺序如下：

1. 执行器调引擎查询接口，查id=2这一行，如果id上有索引，引擎就会直接用树搜索找到这一行。如果id=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回；

2. 执行器得到引擎查询后的返回结果，在此结果上进行更新操作，将新的结果，调用引擎的写入接口写入

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

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

5. 执行器调用引擎的提交事务接口

6. 引擎把redo log改成commit状态，更新完成

每当对数据进行修改时都会将事务执行前的数据写入到undo log中，用于回滚操作。

Redo log（重做日志）

熟悉MySQL InnoDB引擎的人都知道，InnoDB有一个最重要的概念就是缓冲池，这是在内存中分配的一个区域，InnoDB会将数据首先缓存在此，请求首先去命中缓冲池，无法命中缓冲池的才会在磁盘上进行检索，被检索到的数据还是会缓存在缓冲池中。

但是缓冲池依赖的内存是一种易失性的存储介质，掉电以后所有的数据都会被抹掉，为了数据的持久性，任何在缓冲池中做出的变更操作，都要持久化到磁盘上，只有这样，数据库才能实现持久性，用户也才能放心的将数据放在数据库上。

当修改数据时，首先会是在内存中对数据进行修改的，在将内存中的修改结果写回磁盘之前，需要对该修改操作就行记录，以事务为单位。所以如果在事务执行完成后但内存崩了，导致修改结果没有写回磁盘时，只要查询日志文件，就可以将未完成的事物进行修复。

如何判断磁盘内的数据是否缺少？在磁盘数据和redo log中有一个坐标，如果两个不相同则说明还有没写入磁盘的数据。

undo log（回滚日志）

undo log 也是 Mysql存储引擎InnoDB的事物日志，作用是起到回滚，保证了事务的原子性。记录的是数据修改前的状态，即在数据修改的流程中，记录一条与当前操作相反的回滚日志。

需要注意的是，假如一个事务中一条记录被多次修改，但是undo log只会记录原始版本的一条数据，每当对数据进行修改时，都会写入redo log。

作用

1.实现事务原子性，利用Undo log进行回滚
2.实现mvcc机制:undo log中保存了未提交之前版本数据，所以可以作为旧版本数据的快照以便其他事务进行读取

undo log负责完成回滚，redo log负责完成前滚

回滚

未提交的事务，即事务未执行commit。但是该事务内修改的脏页中，可能有一部分脏块已经刷盘。如果此时数据库宕机，则需要用回滚来将已经刷盘的脏块从磁盘上撤销

前滚

未完全提交的事务，即事务已经执行commit,但是缓存吃中的脏页还未来的急刷盘，此时数据库宕机就需要使用redo log进行前滚刷盘恢复数据

数据库重启时，先做前滚，后做回滚。可再看一下本文第一张图，可以看到redo log、undo log、bin log都是在刷脏页之前就已经刷到了磁盘上，这俄中互相协作才能保证用户提交的数据不会丢失。

bin log（归档日志）

bin log是数据库server层日志，和存储引擎无关，以二进制形式存储在磁盘中的逻辑日志，记录了所有DDL和DML操作(不包括select和show)。默认情况下是关闭的。bin log不会向redo log那样会擦除无用日志，而是永久存储(也可以设置过期时间，默认是永久保存)，默认文件1G,超过此阈值会新建一个文件进行记录。主要作用于主从同步以及基于时间点的数据还原

主从同步

1. 主库执行DDL和DML操作，按照修改顺序以此写入bin log.
2. 从库的IO线程连接上主库并请求读取指定位置position的日志内容
3. 主库收到请求后，将制定位置position之后的内容日志、主库bin log文件名称以及在日志中的位置推送给从库。
4. 从库IO线程收到数据后，将日志内容以此写入relay log文件最末端，并将bin log文件名和位置position记录到master-info文件中，以遍下次使用。
5. 从库的sql线程检测到relay log中内容更新后，读取日志并解析成可执行的sql语句进行主从同步。

其他功能

1.基于时间点恢复数据
2.通过订阅bin log可以做很多事情，比如同步上游数据、和redis配合实现延时双删等。

bin log文件记录模式

ROW	STATEMENT	MIXED
记录每一行数据被修改的情况 ，然后Slave端对相同的数据进行修改。优点：能清除记录每一行数据修改细节，能完全实现主从数据同步和数据恢复；缺点：批量操作会产生大量日志，尤其是alter table会让日志暴涨	每一条被修改的数据的sql会记录到master的bin log中，slave在复制的时候sql进程会解析成和原来master执行过的相同的sql再次执行，简称sql语句复制。优点:日志量小，减少磁盘IO，提升存储和恢复速度；缺点:在某些情况下会导致主从数据不一致，例如now（）函数	以上两种模式混合使用，对于STATEMENT模式无法复制的使用ROW模式保存bin log,mysql会根据执行的sql自行选择写入模式

bin log 和 redo log区别

1. 层次不同：redo log是基于innodb存储引擎的，而bin log 是基于数据库服务层实现的，所以mysql数据库中任何存储引擎对数据库进行修改都会产生bin log
2. 作用不同：redo log 用于碰撞恢复(crash recovery),保证mysql宕机不会影响持久性；而bin log 用于时间点恢复数据(point-in-time-recovery),保证服务器可以基于时间点恢复数据以及主从复制
3. 内容不同：redo log是物理日志，内容基于此盘的页；bin log内容是二进制的
4. 写入方式不同：redo log采用循环写入的方式，bin log采用追加的方式
5. 刷盘时机不同:redo log在事务开始时即开始写入，而bin log在事务提交才写入

bin log和redo log 相辅相成，共同保证事务持久性

日志相关问题

崩溃后如何恢复

如果崩溃发生在时刻 A

• 如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。
• 这时候，binlog 还没写，所以也不会传到备库。

如果崩溃发生在时刻 B

崩溃恢复时的判断规则：

1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并且是否完整：
   1. 如果是，则提交事务
   2. 否则，回滚事务
3. 时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交

MySQL 怎么知道 binlog 是完整的?

• 一个事务的 binlog 是有完整格式的：
  • statement 格式的 binlog，最后会有 COMMIT
  • row 格式的 binlog，最后会有一个 XID event
• binlog 日志的完整性是由 checksum 保证，这样子可以确保 binlog 数据是完整的

redo log 和 binlog 是怎么关联起来的?

• 它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描 redo log：
  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交
  • 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务

处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?

• 其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关
• 在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用
• 所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性
• 其实说白了，两个日志都有自己的用处，所以我们设计出两阶段提交，保证两种日志的数据一致性，只要两种日志的数据是一致的，就可以提交事务

如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

• 两阶段提交是经典的分布式系统问题，并不是 MySQL 独有的
• 其实把 MySQL 的两阶段提交也可以看成两个分布式服务处理两个不同事情
• redo log 是在 InnoDB 引擎内操作的，binlog 是在 server 层操作的
• 我们就可以把 引擎层 和 server 层看作两个分布式服务，那他们要分别进行两个相关联的操作，就意味着要实现分布式事务，而两阶段提交，就是其中的一种解决方案

举一个例子：

对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）

而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

两阶段提交就是为了给所有人一个机会，当每个人都说 “我 ok” 的时候，再一起提交。

不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？

• 不可以
• 我认为还是因为 binlog 是追加写的，无法确定从哪个点开始的数据是已经刷盘了
• 而 redo log 只要在 checkpoint 后面的肯定是没有刷盘的，所以只需要重放一遍即可
• 如果 binlog 也能准确知道是从哪个点开始数据没有刷盘的，那么也可以像 redo log 一样重放一遍即可

那能不能反过来，只用 redo log，不要 binlog？

如果只从崩溃恢复的角度来讲是可以的。

你可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的

但是 binlog 有着 redo log 无法替代的功能：

1. 归档：redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用
2. MySQL 系统依赖于 binlog：binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制

redo log 一般设置多大？

• redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了
• 如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

redo log 里面到底是什么 ?

• redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况
• redo log 记录"在某个数据页上做了什么修改"，而不是"这个数据修改后最新值为什么"
• 因此是需要先把磁盘的数据读入内存再执行 redo log 中的内容的

有一个脏页要刷了是吧，现在的状态是：

• 内存中的数据页是脏页
• 脏页中的数据已经记录在 redo log 中了
• 磁盘文件中的数据页是旧的

刷脏页的时候发生了什么：

• 脏页直接写盘
• 此时 内存中的数据页、硬盘中的数据页、redo log 中对应的内容，一致

擦除 redo log 的时候发生了什么：

• 如果要擦的 redo log 中的内容，对应到内存中的数据页不是脏页，直接擦
• 如果要擦的 redo log 中的内容，对应到内存中的数据页是脏页，脏页刷盘，redo log 擦除

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;
1234

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里

• redo log buffer 就是一块内存，用来先存 redo 日志的。
• 也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。
• 但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。

3.备份

数据库备份必要性

• 保证重要数据不丢失
• 数据转移

MySQL数据库备份方法

• mysqldump备份工具
• 数据库管理工具,如SQLyog
• 直接拷贝数据库文件和相关配置文件

mysqldump客户端

作用 :

• 转储数据库
• 搜集数据库进行备份
• 将数据转移到另一个SQL服务器,不一定是MySQL服务器

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vVqrQftc-1651626535250)(D:\笔记\img\image-20220227210621239.png)]

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
 
可以-w携带备份条件
 
-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source  备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

4.mysql主从复制

概述：

简单来说就是保证主服务器（Master）和从服务器（Slave）的数据是一致性的，向Master插入数据后，Slave会自动从Master把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，就是Mysql复制。

能解决的问题：

一、高可用和故障切换
复制能够帮避免MySql单点失败，因为数据都是相同的，所以当Master挂掉后，可以指定一台Slave充当Master继续保证服务运行，因为数据是一致性的（如果当插入Master就挂掉，可能不一致，因为同步也需要时间），当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Salve同步Master

二、负载均衡
因为读写分离也算是负载均衡的一种，所以就不单独写了，因为一般都是有多台Slave的，所以可以将读操作指定到Slave服务器上（需要代码控制），然后再用负载均衡来选择那台Slave来提供服务，同时也可以吧一些大量计算的查询指定到某台Slave，这样就不会影响Master的写入以及其他查询

三、数据备份
一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全

四、业务模块化
可以一个业务模块读取一个Slave，再针对不同的业务场景进行数据库的索引创建和根据业务选择MySQL存储引擎， 不同的slave可以根据不同需求设置不同索引和存储引擎

主从配置需要注意的点
(1)主从服务器操作系统版本和位数一致；
(2) Master和Slave数据库的版本要一致；
(3) Master和Slave数据库中的数据要一致；
(4) Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；

复制原理：

复制有三个步骤：
1、Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)
2、Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)
3、Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)

第一步Master记录二进制日志， 每次提交事务完成数据更新前，Master将数据更新的时间记录到二进制日志中，MySql会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。再记录二进制日志后，主库会告诉存储引擎可以提交事务了。

第二步，Slave将Master的二进制日志复制到本地的中继日志中，首先，Slave会启动一个工作线程，成为I/O线程， I/O线程跟Master建立一个普通的客户端链接，然后再Master上启动一个特殊的二进制转储（binlog dump）线程（该线程没有对应的SQL命令），这个二进制转储线程会读取主库上的二进制日志中的事件。从库I/O线程将接受到时间记录到中继日志中。

第三步从库的SQL线程执行最后异步，该线程的从中继日志中读取事件并在从库执行，从而实现从库数据更新。

这种复制架构实现了获取事件和重放事件的解偶，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作。但这种架构页限制了复制的过程，其中最重要的一点是主库上并发运行的查询再从库只能串行化执行，因为只有一个SQL线程重放中继日志中的事件。这是很多工作负载的性能瓶颈所在。因为始终受限于单线程。

注意：

1–master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
2–slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
3–Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
4–Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
5–master和slave两节点间时间需同步

也就是说：

• 从库会生成两个线程,一个I/O线程,一个SQL线程;

• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;

• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

复制类型

1、基于语句的复制
在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制

2、基于行的复制
把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持

3、混合类型的复制
默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制

相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

具体步骤

1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave

2、从库的IO线程和主库的dump线程建立连接。

3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。

4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。

5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中

6、从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

3.视图和临时表

1.视图

什么是视图？

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

视图是干什么用的？

通过视图，可以展现基表的部分数据；
视图数据来自定义视图的查询中使用的表，使用视图动态生成。
基表：用来创建视图的表叫做基表

为什么要使用视图？

因为视图的诸多优点，如下
1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

视图的操作

创建：

CREATE [O
R REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]  
AS select_statement  
	[WITH [CASCADED | LOCAL] CHECK OPTION]

• OR REPLACE：如图视图名重复，则表示替换原有视图

• ALGORITHM：表示创建视图时所使用的算法

  • UNDEFINED：默认值，没有指定算法时一般采用merge算法
  • MERGE：合并算法，在基于视图创建新的视图时，将创建视图时所使用的select语句与将要创建新的视图的select语句进行合并处理，效率较高。
  • TEMPTABLE：临时表算法，在基于视图创建新的视图时，先执行原有视图的select语句，然后再执行新视图的select语句，查询两次，效率较低。

• DEFINER：指出谁是视图的创建者

  • USER：指定创建视图的用户
  • CURRENT_USER：不指定创建视图的用户（默认是当前操作用户）

• SQL SECURITY：设置操作的视图的安全策略

  • DEFINER：只有创建视图的用户才有权访问
  • INVOKER：任何用户都可以访问（前提必须有操作权限，如select、update权限等）
    示例：

WITH：更改视图数据时，检查数据

• LOCAL：更改视图时，如果更改后的数据不符合当初创建视图时的条件时，则不予修改，并报如下错误：

• CASCADED：在视图嵌套的过程中，修改当前视图中的数据，必须满足创建此视图所引用到的所有视图的创建条件。

视图的更改：

create or replace view view_name as select语句;
在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图

DML操作：因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中

当然，视图的DML操作，不是所有的视图都可以做DML操作。
有下列内容之一，视图不能做DML操作：
– select子句中包含distinct
– select子句中包含组函数
– select语句中包含group by子句
– select语句中包含order by子句
– select语句中包含union 、union all等集合运算符
– where子句中包含相关子查询
– from子句中包含多个表
– 如果视图中有计算列，则不能更新
– 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

WITH CHECK OPTION约束：

对于可以执行DML操作的视图，定义时可以带上WITH CHECK OPTION约束
作用：对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。

使用WITH CHECK OPTION约束时，(不指定选项则默认是CASCADED)
可以使用CASCADED或者LOCAL选项指定检查的程度：
CASCADED：检查所有的视图，会检查嵌套视图及其底层的视图
LOCAL：只检查将要更新的视图本身，嵌套视图不检查其底层的视图

2.临时表

MySQL临时表在很多场景中都会用到，比如用户自己创建的临时表用于保存临时数据，以及MySQL内部在执行复杂SQL时，需要借助临时表进行分组、排序、去重等操作。临时表的作用仅限于本次会话，等连接关闭后重新打开连接临时表将不存在。

外部临时表

通过create temporary table语句创建的临时表为外部临时表，在创建时可以手动指定临时表的存储引擎。

create temporary table temp_table(
	id int,
	name varchar(10)
) ENGINE = InnoDB;
insert into temp_table values (1,'1');

select * from temp_table ;

存储过程使用临时表；函数无法使用临时表

create procedure pro1()
begin
	create temporary table temp_table(
		id int
	);
	
	insert into temp_table values(1);
	
	select * from temp_table;
end;

call pro1();

内部临时表

通常在执行复杂SQL语句时，比如group by，distinct，union等语句，执行计划中如果包含Using temporary，那么MySQL内部将使用自动生成的临时表，以辅助SQL的执行。

临时表说明

1）临时表，顾名思义就是"临时"的表，不管是内部临时表还是外部临时表，都是临时的，我们查询不到；在服务器重启之后，所有的临时表将会被销毁。

不管是内部临时表还是外部临时表，我们都查询不到，但外部临时表可以查询到里面的数据。

2）临时表是每个进程独享的，当前进程（客户端）创建的临时表，其他进程（客户端）是查不到临时表里面的数据的。

5.7引入了临时表空间后，所有临时表都存储在临时表空间(非压缩)中，临时表空间的数据可以复用。临时表并非只支持Innodb引擎，还支持myisam引擎，memory引擎等。因此，临时表我们看不到实体(idb文件)，但其实不一定是内存表，也可能存储在临时表空间中。

MySQL临时表相关参数：

• tmpdir：临时表存储路径
• tmp_table_size：内部临时表内存最大值，超过此值将转移到磁盘中存储。
• max_heap_table_size：外部临时表内存最大值，超过此值将转移到磁盘中存储。
• default_tmp_storage_engine：外部临时表默认采用的存储引擎。
• internal_tmp_disk_storage_engine：内部临时表默认采用的存储引擎

临时表相关状态参数

• Created_tmp_disk_tables：MySQL创建内部磁盘临时表次数。

• Created_tmp_tables：MySQL创建内部临时表次数。

• Created_tmp_files：创建的临时表文件数（系统内部维护，不用我们管）。

MySQL临时表可能导致磁盘可用空间减少

在MySQL5.7版本之前，临时表的存储引擎默认为myisam，myisam临时表在SQL执行结束后，会自动删除临时表。然而从5.7版本开始，临时表的默认存储引擎变为innodb，虽然在性能上有了一定的提升，但是由于innodb引擎的临时表共用表空间ibtmp1，导致在高并发下，多个session同时创建临时表时，该表空间会变得非常大，并且不能动态缩小，除非重启MySQL，否则无法释放。

我们可以为临时表空间设置一个最大值，比如10G，如下：

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

当临时表空间达到最大值10G时，SQL执行将会报错，影响应用的正常执行。

对于临时表空间过大的问题，我们通常也有一些其他方法解决，比如：

将临时表的存储引擎设置为myisam，虽然可能有一些性能问题，但不会导致磁盘空间问题。所谓鱼和熊掌不可兼得。优化SQL，比如

（1）加上合适的索引，对于group by添加索引可以消除临时表

（2）在where条件中过滤更多的数据

（3）重写SQL，优化执行计划

（4）如果不得不使用临时表，那么一定要减少并发。如果经费不是问题，那么增大磁盘，也许是最快捷有效的方式。

3.视图和临时表的区别

表	会话结束自动释放	内存表	更新影响实体表	语法
视图	不会	是	是	create view view_name
临时表	会	否	否	create temporary table tmp_table

应用场景：

视图：

1. 保密工作 （过虑一些字段）

2. 简化查询（复杂SQL写成一个视图）

​ 视图是一个虚表

临时表：

1. 短期内大量工作，完成后删除（购物车结算）
2. 导出部分数据
3. 做测试 （在临时表做测试 正确后再操作正式表）

mysql视图和临时表的区别 - CS408 - 博客园 (cnblogs.com)

4.变量，存储过程，函数

1.变量

系统变量：
	全局变量
	会话变量
自定义变量：
	用户变量
	局部变量

#一、系统变量
说明：变量由系统提供，不是用户定义，属于服务器层面
语法：
	1.查看所有系统变量的全局变量：SHOW GLOBAL variables;
	2.查看所有系统变量的会话变量：SHOW 【SESSION】 variables;
	3.查看满足条件的部分变量：show global | 【SESSION】 variable like '';
	4.查看指定的某个系统变量的值：select @@global|【session】.系统变量名;
	5.为某个系统变量赋值：
		（1）set global|【session】.系统变量名 = 值;
		（2）set @@global|【session】.系统变量名 = 值;
注意：
	1.如果是全局级别(所有连接都有效)，则需要加上global，如果是会话级别（只对当前连接有效）则需要加上session，不写则默认session。
#全局变量
	作用域：所有设置重启后失效（重启会重新初始化），即全局变量设置不能跨重启。
#会话变量
	作用域：仅仅针对当前会话（连接）有效
#二、自定义变量
    说明：变量是用户自定义的，不是系统提供的
    使用步骤：
        声明 -> 赋值 -> 使用（查看、比较、运算等）
    #用户变量
        作用域：针对当前会话（连接）有效，同于会话变量的作用域
        #1.声明（声明时必须初始化）
        SET @用户变量名=值;或
        SET @用户变量名:=值;或
        SELECT @用户变量名:=值

        #2.赋值（更新用户变量的值）
        方式一：通过set或者select
            SET @用户变量名=值;或
            SET @用户变量名:=值;或
            SELECT @用户变量名:=值
        方式二：通过select INTO
            SELECT 字段 INTO @变量名 FROM 表;

        #3.使用（查看用户变量的值）
        SELECT @用户变量名;
     #局部变量
     	作用域：定义此变量的begin end 中有效。
     	注意：应用在begin end 中的第一句。
     	#1.声明
        DECLARE 变量名 类型;
        DECLARE 变量名 类型 DEFAULT 值;

        #2.赋值
        方式一：通过set或者select
            SET 局部变量名=值;或
            SET 局部变量名:=值;或
            SELECT @局部变量名:=值
        方式二：通过select INTO
            SELECT 字段 INTO 局部变量名 FROM 表;

        #3.使用
        SELECT 局部变量;

2.存储过程

MySQL中提供存储过程与存储函数机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

优点：

通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

1）性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

2）复用：存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

3）安全：存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

缺点：

1）如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不偏于逻辑运算。

2）很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

语法：

create procedure procedure_name([in/out/inout] 参数名  参数类型)

• in：该参数可以作为输入，也就是需要调用方传入值 , 默认
• out：该参数作为输出，也就是该参数可以作为返回值
• inout：既可以作为输入参数，也可以作为输出参数

-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
end ;


call test7(1,@class_name);	-- 创建会话变量		

select @class_name;		-- 引用会话变量

流程控制

游标

游标是用来存储查询结果集的数据类型，可以帮我们保存多条行记录结果，我们要做的操作就是读取游标中的数据获取每一行的数据。

CREATE PROCEDURE test13 ()		-- 默认是输入(in)参数
begin
	
	declare id int(11);
	declare `name` varchar(20);
	declare class_id int(11);
	-- 定义游标结束标识符
	declare has_data int default 1;
	
	declare stu_result cursor for select * from student;
	-- 监测游标结束
	declare exit handler for not FOUND set has_data=0;
	
	-- 打开游标
	open stu_result;
	
	repeat 
		fetch stu_result into id,`name`,class_id;
		
		select concat('id: ',id,';name: ',`name`,';class_id',class_id);
		until has_data=0		-- 退出条件,注意不要打分号
	end repeat;
	
	-- 关闭游标
	close stu_result;
	
end ;

call test13();

3.函数

定义：一组事先编译好的SQL语句的集合，理解成批处理语句。
好处：
1、提高代码重用性
2、简化操作
3、减少了编译次数并减少了和数据库服务器连接的此数，提高了效率
和存储过程的区别：
存储过程：可以有0个返回，也可以有多个返回。适合做插入，批量更新。
函数：有且仅有1个返回，适合做处理数据后返回一个结果。

定义：一组事先编译好的SQL语句的集合，理解成批处理语句。
好处：	
	1、提高代码重用性
	2、简化操作
	3、减少了编译次数并减少了和数据库服务器连接的此数，提高了效率
和存储过程的区别：
	存储过程：可以有0个返回，也可以有多个返回。适合做插入，批量更新。
	函数：有且仅有1个返回，适合做处理数据后返回一个结果。
创建语法：
	create function 函数名(参数列表) returns 返回类型
	begin
		函数体
	end
注意事项：
	1、参数列表包含两部分：参数名，参数类型
	2、函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不会报错，但是不建议。
	3、函数体只有一句话，则可以省略begin end
	4、使用 delimiter 设置结束标记
调用语法：
	select 函数名(参数列表)
无参有返回：
    #  案例一：返回公司的员工个数
    CREATE FUNCTION myf1() RETURNS int
    DETERMINISTIC
    BEGIN 
        DECLARE c int DEFAULT 0; # 定义变量
        SELECT count(*) INTO c FROM employees;
        RETURN c;
    END $
    # 调用
    SELECT myf1() $
有参有返回：
	# 案例一：根据员工名返回他的工资
    CREATE FUNCTION myf2(empName varchar(20)) RETURNS double 
    DETERMINISTIC
    BEGIN
        SET @sal := 0; 
        SELECT salary INTO @sal 
        FROM employees 
        WHERE last_name = empName;

        RETURN @sal;
    END $
    # 调用
    SELECT myf2('chen') $
    
    # 案例二：根据部门名，返回该部门的平均工资
    CREATE FUNCTION myf3(depName varchar(20)) RETURNS double
    DETERMINISTIC
    BEGIN
        DECLARE c double;

        SELECT avg(e.salary) INTO c FROM employees e LEFT JOIN departments d 
        ON e.department_id = d.department_id
        WHERE d.department_name = depName;

        RETURN c;
    END $
	# 调用
    SELECT myf3('Adm') $
查看函数：
	SHOW CREATE FUNCTION myf3;
删除函数：
	DROP FUNCTION 函数名;

4.存储过程和函数的区别

• 存储函数的限制比较多，例如不能用临时表,只能用表变量，而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
• 返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out，inout)。
• 调用时的不同。存储函数嵌入在SQL中使用，可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。
• 参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，in、out和inout：
  • in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量
  • out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量
  • inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

5.事务和隔离级别

1.事务

ACID

一个完整的事务具备有四个特性，分别为原子性（Atomicity）、一致性（Consistency）、隔离性、持久性。

• 原子性（Atomicity）：事务是一个业务操作的最小单元，不可再分割，要么全部执行成功，要么全部执行失败。

• 一致性（Consistency）：事务执行前与执行后，数据库中数据应该保持相同的状态。

• 隔离性（Isolation）：事务与事务之间不能互相影响，必须保持隔离性。

• 持久性（Durability）：如果事务执行成功，对数据库的操作是持久的。

事务操作

MySQL中可以有两种方式进行事务的操作：

• 1）手动提交事务
• 2）自动提交事务（MySQL默认）

查看当前MySQL是否是自动提交事务：

No（1）：开启自动提交事务（默认值）
OFF（0）：关闭自动提交事务

• 设置手动提交事务（关闭自动提交事务）：

set autocommit只是本次会话有用，下次会话又将变为自动提交事务。
如果需要永久生效，请到MySQL配置文件中增加：autocommit=0

事务执行过程

手动提交事务的SQL语句

功能	SQL语句
开启事务	start transaction
提交事务	commit
回滚事务	rollback

-- 查询两个人的余额
select * from user;

-- 开启事务
start transaction;

-- 执行SQL操作(小刚扣钱),此时并没有真正写入到数据库(因为后续操作很有可能会有错误)
update user set money=money-520 where id=1;

-- 执行SQL操作(小美加钱),此时并没有真正写入到数据库(因为后续操作很有可能会有错误)
update user set money=money+520 where id=2;

-- 提交事务(确认无误,提交事务)
commit;

事务回滚

-- 查询小刚和小美的余额
select * from user;

-- 开启一个事务
start transaction;

-- 小刚扣钱,此时并没有真正写入到数据库(因为后续操作很有可能会有错误)
update user set money=money-520 where id=1;

-- 小美加钱,此时并没有真正写入到数据库(因为后续操作很有可能会有错误)
update user set money=money+520 where id=2;

-- 发现中途操作其他业务时,出现了错误,那么此次事务作废(之前的所有操作全部不会记录到数据库)
rollback;

-- 再次查询数据库,发现余额还是原来的
select * from user;

回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是回滚所有操作，这个点称之为回滚点。

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

-- 查询原始数据
select * from user;

-- 开启事务
start transaction;

-- 执行减钱操作
update user set money=money-10 where id=1;

-- 执行加钱操作
update user set money=money+10 where id=2;

-- 设置一个保存点,防止后续出现意外要回滚本次所有的操作
savepoint one;

-- 执行减钱操作
update user set money=money-10 where id=1;

-- 执行加钱操作
update user set money=money+10 where id=2;

-- 回滚到one保存点之前的状态
rollback to one;

-- 提交本次事务
commit;

-- 再次查询数据
select * from user;

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

2.隔离级别

并发访问下事务产生的问题

• 脏读： 一个事务（用户）读取到了另一个事务没有提交的数据
• 不可重复读：一个事务多次读取同一条记录，出现读取数据不一致的情况。一般因为另一个事务更新了这条记录而引发的。
• 幻读：在一次事务中，多次读取到的数据条数不一致（InnoDB存储引擎已经解决幻读问题），因此在MySQL的默认隔离级别中幻读已经被解决

四种隔离级别：

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

四种隔离级别起的作用：

1. Serializable (串行化)： 可以避免所有事务产生的并发访问的问题 效率及其低下
2. Repeatable read (可重复读)：会引发幻读的问题（在InnoDB存储引擎已经解决）
3. Read committed (读已提交)：会引发不可重复读和幻读的问题
4. Read uncommitted (读未提交)： 所有事务中的并发访问问题都会发生

• 查询全局事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

MySQL默认的事务隔离级别为：REPEATABLE-READ（可重复读）

幻读

幻读由事务隔离级别可重复读引发，在一个事务内读取到了别的事务插入的数据，由于"可重复读"的原因，查询不到那条数据，想插入那条数据发现提示"数据已存在"，但是又查询不到，就好像出现幻觉一样。

幻读演示

在窗口一和窗口二分别开启事务，并查询当前user表信息，都发现id为3的用户，窗口一率先插入id为3的用户，**并且提交。**此时窗口二再次查询数据表信息，发现依旧没有id为3的用户，于是想插入id为3的数据，发现报错（主键冲突），此时窗口二感觉很诡异，于是再次查询数据表信息，发现依旧没有id为3的用户，就好像出现幻觉一样，明明查询不到，但是又不让插入。

解决幻读

我们再次将事务隔离级别调高，来解决幻读问题。

• 将当前MySQL的事务隔离级别设置为repeatable read（串行化）：

mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

重启MySQL客户端

• 再次查询MySQL的事务隔离级别：

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

串行化解释

通过上面的演示，我们知道隔离级别为serializable（串行化）可以解决幻读问题，但是随之而然给我们带来的弊端非常明显，那就是并发性能极差。

在窗口一的事务中，一旦有更新操作（insert、update、delete）等， 那么会将整表都锁起来，不让其他客户端（事务）操作，**而且是读写都不让！*如果是在高并发情况下，串行化毋庸置疑会*导致大量的锁等待，性能极低，因此实际生产中没有人会使用串行化的隔离级别。

serializable隔离级别详细图解：

在可重复读隔离界别下解决幻读

MVCC的快照读，在事务的第一次查询时，建立快照，之后的查询都使用该快照，快照记录了当前所有事务多数据的执行状态，基于该快照操作，同一个事务中得到的查询结果都是一样的，也就不存在幻读的问题了。

临键锁：锁住查询数据的相关位置，使其他事务无法对对影响查询结果的数据进行插入操作。

总结

1）MySQL的默认隔离级别为：repeatable read（可重复读）

2）Oracle和SQL Server的默认隔离级别为：read committed（读已提交）

3）回滚点的概念：在一次事务中，操作的N多次数据库，但由于某次操作失误，需要rollback，但是一旦执行rollback，回滚此次事务的所有操作，因此我们可以在一次事务中，执行过若干次代码后，设置一个保存点，如果后续出现上面失误操作，可以回滚到指定的位置。

4）回滚点的指令：savepoint name

5）事务在并发访问下的安全问题（隔离级别）

6）脏读、不可重复读、幻读的基本概念

7）通过隔离级别来解决不同问题

8）牢记串行化给我们带来的问题（锁表）

3.进阶

1.mysql架构

MySQL 数据库是单进程多线程的架构，和 SQL Server 类似，和 Oracle 不一样，Oracle 是多进程架构。下面我来看一下MySQL具体的架构图：

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

• Server 层包括 连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory（内存引擎）等多个存储引擎。
  • 现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
  • 不同存储引擎的表数据存取方式不同，支持的功能也不同

连接层

连接层主要负责连接管理、身份认证等，你输入的mysql -uxxx -pxxx之后，来到MySQL的第一件事就是校验你的身份，权限等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

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

连接命令一般是这么写的：

mysql -h$ip -P$port -u$user -p

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
  • 这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

查询 MySQL 连接状态

• 其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
• 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

查询 MySQL 连接超时时间

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

• 时间单位为：秒，转换为小时是：8小时

长连接 与 短连接

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

短连接和长连接是怎么设置的？是连接数据库的时候有什么参数吗？

• 这是“行为”，比如连接完，执行一个查询，就断开，这是短连接
• 执行一个查询，不断开，下次查询还用这个连接，持续使用，就是长连接
• 建立连接的过程通常是比较复杂的，所以建议尽量使用 长连接

MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

如何解决？

1. 定期断开长连接
2. 初始化连接资源，恢复到刚刚创建完时的状态

服务层

认证成功过后，接下来就来到服务层了，服务层是 MySQL 的核心，MySQL 的核心服务层都在这一层，包括你执行的任何SQL语句，普通的DDL、DML、DQL、存储过程、视图、触发器等都需要经过这一层的一些操作，最终才可以被MySQL识别。主要经过有SQL解析器、SQL优化器等，查询缓存，总之，所有跨存储引擎的功能都在这一层实现。

SQL语句的执行流程

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句

• 之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中
  • key 是查询的语句，value 是查询的结果
  • 如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？

• 因为查询缓存往往弊大于利
• 因为数据库表如果添加、删除、修改后缓存就会失效，除非这个表不会动，正常我们平时的表的改动会很频繁，这样就会造成不停的更新缓存
• 注意：8.0 之后的版本就已经将查询缓存的整块功能删掉了

分析器（解析器）

如果没有命中查询缓存，就要开始真正执行语句了

1. 先进行“词法分析”，识别传进来的字符串
2. 根据传进来的字符串用“语法分析”进行判断
3. 如果有错误，将会收到 You have an error in your SQL syntax 的报错

词法分析 和 语法分析 的区别?

• 词法分析主要做的是根据 MySQL 的关键字进行验证和解析
• 语法分析会在词法解析的基础上进一步做表名和字段名称的验证和解析

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢 ?

• 是在 分析器 阶段报出来的

优化器

通过分析器，明白你要干啥后，数据库就要针对你的需求想一个最优的解决方案，也就是执行计划，这个最优方案选择的操作，就是优化器要做的事情了。

原则是：尽可能扫描少的数据库行记录

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• join 是 inner join 的缩写
• using(ID) 是 on t1.ID == t2.ID 的简写，因为联表 on 条件是两张表中同一个字段，可以简写为 using(字段名)
• 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20
• 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10

一条 SQL 语句可能有不同的执行逻辑（或者执行顺序），而优化器就是选择最优的执行顺序。

执行器

MySQL 执行的流程包括：

• 分析器阶段的词法分析和语法分析（分析器解释出你要做什么）
• 优化器阶段的索引和连接顺序优化（优化器解释出你需要该怎么做）
• 最后由执行器开始执行（执行器负责执行）

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误

• 权限验证不仅仅在执行器这部分会做
• 在分析器之后，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做precheck。
• 而 precheck 是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。
• 连接器是 账号权限校验
• 分析器是 库权限验证
• 执行器是 语句在表中权限验证

到了执行的时候才会进入到数据库引擎，然后执行器也是通过调用 数据库引擎的API 来进行数据操作的。也因此数据库引擎才会是插件形式的。

select * from T where ID=10；

比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
4. 没有索引：执行器调用sql引擎的api进行全表扫描
5. 存在索引：执行器调用sql引擎的api，引擎通过索引的缓存快速定位到数据并返回（索引的缓存原理需要继续深入了解）

存储引擎 和 执行器 的区别?

1. 存储引擎层的各种数据获取方法都是已经定义好了的，是静态的方法；
2. 优化器 生成的执行计划，决定了 执行器会选择 存储引擎的哪个方法去获取数据，而这也决定了一个操作是否能快速执行完成；
3. InnoDB 存储引擎层的优化措施还有好多，对执行器来说只是一个黑箱，可以再深入了解。

存储引擎层

我们知道MySQL在存储引擎这方面和Oracle和SQL Server不一样，MySQL提供了可插拔式的存储引擎，即"插上什么存储引擎，就有什么功能"，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同。不同的存储引擎将直接决定了数据存储到磁盘的方式。

系统文件层

数据存储层主要是将数据存储在运行与设备的文件系统之上，并完成与存储引擎的交互。

2.MySQL缓存机制

MySQL缓存机制就是缓存SQL文本及缓存结果，用KV形式保存再服务器内存中，如果运行相同的sql,服务器直接从缓存中去获取结果，不需要在再去解析、优化、执行sql。 如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。表中得任何改变是值表中任何数据或者是结构的改变，包括insert,update,delete,truncate,alter table,drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询，显然，者对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能。

缓存命中条件

• 缓存是有命中条件的，并不是所有的SQL语句都会进入缓存查找。

缓存存在一个hash表中，通过查询SQL，查询数据库，客户端协议等作为key，在判断命中前，MySQL不会解析SQL，而是使用SQL去查询缓存，SQL上的任何字符的不同，如空格，注释，都会导致缓存不命中。如果查询有不确定的数据current_date()，那么查询完成后结果者不会被缓存，包含不确定的数的是不会放置到缓存中。

缓存工作流程

1）服务器接收SQL，以SQL和一些其他条件为key查找缓存。

2）如果找到了缓存，则直接返回缓存。

3）如果没有找到缓存，则执行SQL查询，包括原来的SQL解析，优化等。

4）执行完SQL查询结果以后，将SQL查询结果放入查询缓存。含有不确定数据的，不被放入缓存

注意：对于查询不到结果的sql语句，会被写入缓存；

-- 即使查询不到结果集,也会写入缓存
select * from goods where id=10;

-- 即使查询不到结果集也会命中缓存
select * from goods where id=10;

-- 会写入两次缓存

即使有的SQL没有查询到结果集，也会写入缓存，并且再次查询也会命中缓存

缓存说明

开启查询缓存

MySQL默认是将查询缓存关闭的，我们需要在配置文件中打开。

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF                |
+--------------------+
1 row in set, 1 warning (0.00 sec)

修改配置文件：linux的是/etc/my.conf，Windows的是C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

在mysqld组下面增加：

query_cache_type=1

重启MySQL服务：

systemctl restart mysqld

再次查看：

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| on                 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

缓存参数

• 输入如下命令查询缓存相关参数

show variables like "%query_cache%";

• have_query_cache：当前数据库版本是否支持缓存 NO：不支持 YES：支持

• query_cache_limit：查询缓存能缓存单个SQL语句产生的结果集的最大容量大小，超出此大小的SQL语句结果集则不放入缓存。

• query_cache_min_res_unit：分配内存块时的最小单位大小

  • MySQL并不是一下子分配query_cache_size大小的内存作为缓存，而且将整个查询缓存的大小分成了若干个内存块，query_cache_min_res_unit正是决定这些块的大小，需要注意的是，即使缓存的数据没有达到一个缓存块大小也需要占用一个缓存块大小的空间。如果超出单个缓存块，那么需要申请多个缓存块，当查询完发现有缓存块内存多余，则会将多余的缓存块内存释放，造成缓存碎片。

• query_cache_size：缓存使用的总内存空间大小，单位是字节，这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同

• query_cache_type

  ：是否打开缓存

  • OFF：关闭缓存（默认值）
  • ON：开启缓存
  • DEMAND：只有明确写了SQL_CACHE的查询才会写入缓存

  select SQL_CACHE * from t_goods;			-- 将查询结果放入缓存（前提缓存是开启的）			
  select SQL_NO_CACHE * from t_goods;			-- 不缓存查询结果
  

• query_cache_wlock_invalidate：如果某个数据表被锁住，是否仍然从缓存中返回数据，默认是OFF，表示仍然可以返回。

全局缓存状态

show global status like '%Qcache%';

• Qcache_free_blocks：缓存池中空闲块的个数
• Qcache_free_memory：缓存中空闲内存量
• Qcache_hits：缓存命中次数
• Qcache_inserts：缓存写入次数
• Qcache_lowmen_prunes：因内存不足删除缓存次数
• Qcache_not_cached：查询未被缓存次数，例如查询结果超出缓存块大小，查询中包含可变函数等，或者未查询到数据的行、或者SQL语句中使用了SQL_NO_CACHE等。
• Qcache_queries_in_cache：当前缓存中缓存的SQL数量
• Qcache_total_blocks：缓存总block数

缓存命中测试

-- 写入缓存+1
select * from goods g;				-- 注意仔细看SQL语句哦（取了个别名）

-- 写入缓存+1
select * from goods where id=1;

-- 写入缓存+1
select * from goods where id=2;

-- 写入缓存+1
select * from goods g where id=1;

-- 命中缓存+1
select * from goods g;

-- 命中缓存+1
select * from goods where id=1;

-- 命中缓存+1
select * from goods where id=2;

必须要一模一样的查询语句才能命中缓存

• 这里要注意一点，即使有的SQL查询不出来结果，也是会写入缓存的

-- 即使查询不到结果集,也会写入缓存
select * from goods where id=10;

-- 即使查询不到结果集也会命中缓存
select * from goods where id=10;

缓存清空测试

我们上提到过，对表的任何DML操作都会导致缓存情况，包括insert,update,delete,truncate,alter table,drop table等。

为了方便测试，我重启MySQL服务器（缓存信息全部清空）：

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from goods where id=1;		# 写入缓存
+----+-------------------------------+--------------+---------+
| id | title                         | name         | price   |
+----+-------------------------------+--------------+---------+
|  1 | 华为4G全面屏游戏手机          | 华为手机     | 5399.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |			# 当前缓存了一条SQL
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

mysql> select * from goods where id=1;			# 命中缓存
+----+-------------------------------+--------------+---------+
| id | title                         | name         | price   |
+----+-------------------------------+--------------+---------+
|  1 | 华为4G全面屏游戏手机          | 华为手机     | 5399.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |					# 当前缓存了一条SQL
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)
mysql> update goods set price=8999 where id=1;			# 清空缓存
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |				# 注意:这里显示的是你当前写入了多少次缓存
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |				# 当前缓存了0条SQL
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.01 sec)

mysql> select * from goods where id=1;				# 写入缓存
+----+-------------------------------+--------------+---------+
| id | title                         | name         | price   |
+----+-------------------------------+--------------+---------+
|  1 | 华为4G全面屏游戏手机          | 华为手机     | 8999.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 2       |					# 发现缓存写入次数增加
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |					# 当前缓存了一条SQL
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)
mysql>

通过观察Qcache_queries_in_cache参数可以发现，执行完update语句之后，有关于这张表的缓存全部清空，当再次执行SQL语句的时候，会重新写入缓存。

对MySQL表的任意DML操作都会导致有关于这张表的所有缓存全部清空。

使用SQL Hint选择缓存

我们知道MySQL的查询缓存一旦开启，会将本次SQL语句的结果集全部放入缓存中，这样其实是非常不友好的，因为我们知道，对于表的任何DML操作都会导致这张表的缓存全部清空。因此我们可以指定哪些SQL语句存入缓存，哪些不存。

• SQL_CACHE：将此次SQL语句的结果集存入缓存（前提是当前MySQL服务器时开启缓存的）
• SQL_NO_CACHE：此次SQL语句的结果集不存入缓存

-- 缓存写入次数0
show global status like '%Qcache%';

-- 存入缓存
select * from goods;

-- 缓存写入次数1
show global status like '%Qcache%';

-- 不存入缓存
select SQL_NO_CACHE * from goods g;		-- 注意:我取了个别名

-- 缓存写入次数还是1
show global status like '%Qcache%';

大数据量缓存性能测试

我们已经明白缓存何时写入、何时清空，何时命中，接下来我们插入300W数据，来执行SQL语句，体验缓存给我们带来性能上的提升。

-- 关闭唯一性校验,提高批量插入速度
set unique_checks=0;

-- 控制在一个事务,避免频繁开启/提交事务
start transaction;

-- test_insert是编写的存储过程，用来为表生成数据
call test_insert(3000000);			-- 模拟500W的数据

commit;

• 执行如下SQL测试缓存效率：

-- 第一次查询,将结果存入缓存
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

-- 走缓存,效率快
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

-- 对表进行修改,关于这张表的缓存全部清空
update userinfo set username='1' where id=1;

-- 再次查询,发现效率低,但又存入缓存了
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

-- 再次查询,走缓存,效率高
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

发现命中缓存确实比没有命中缓存查询效率高多了。

缓存的利用率

计算命中率

缓存命中率 = Qcache_hits（缓存命中次
数） / Com_select（查询次数）

计算写入率

缓存写入率 = Qcache_inserts（缓存写入次数） / Com_select（查询次数）

缓存失败情况

• 1、缓存碎片、内存不足、数据修改都会造缓存失败。如果配置了足够的缓存空间，而且query_cache_min_res_unit的大小也设置的合理。那么缓存失效应该主要是数据修改导致的。可以通过Qcache_lowmen_prunes参数来查看有多少次失效是由于内存不足导致的。
• 2、当某个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态
• 3、在Innodb中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。

减少缓存碎片

• 1）设置合适的query_cache_min_res_unit值

我们在分析缓存参的时候知道，MySQL并不是一下子分配query_cache_size内存作为缓存，而是将内存分为若干个query_cache_min_res_unit小内存，即使本次缓存没有达到一个缓存块大小也需要占用一个缓存块大小。但query_cache_min_res_unit也不能设置太小，设置太小会造成MySQL不断的分配很多个内存块来缓存本次SQL语句的结果集。

• 2）使用 FLUSH QUERY CACHE 命令整理缓存碎片

使用 FLUSH QUERY CACHE 命令可以有效的整理缓存碎片，来释放碎片占用的空间，但需要注意的是这个命令在整理缓存期间，会导致其他连接无法使用查询缓存。

缓存这么好，为什么很少用他？

既然缓存这么好，为什么咱们公司实际开发不使用它？而且官网上已经明确说明，在MySQL8.0及以上版本，MySQL的缓存功能已经被删除了（其实就光这一点，很多人就已经失去学习他的动力了）。

缓存失效很频繁

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。因此读写非常频繁的业务场景，缓存开启还不如关闭效率高。

同时将查询的结果集存放到缓存中也需要时间，会影响效率

缓存会占用我们的内存

一般来说，我们的数据库都是单独部署在一台服务器中，我们应该尽可能的减少这台服务器的压力，必要时还要进行扩容（搭建集群、读写分离、数据分片等），这些操作都是来提高我们单台MySQL的处理能力的，而不是把缓存和数据库放在一起，增加MySQL服务器的压力。如果真的需要缓存来提高响应速度，那么应该把缓存和数据库独立分开部署。

市面上已经有非常成熟的缓存中间件

在大多数情况下，我们都是采用市面上比较成熟的软件来进行缓存，如redis、memcache等，这些软件不管是性能、功能上都比MySQL缓存要强大的多，而且利于我们进行性能扩容（如搭建缓存集群）操作起来也非常方便。

MySQL的查询缓存缓存的是SQL语句筛选过后的结果集，而筛选过后的结果集通常来说不会太大，此时是可以存入缓存来提高我们的查询效率的，如果查询的结果集太大，你完全可以选择不存入查询缓存（使用SQL_NO_CACHE），因为查询缓存真的很容易失效！

3.存储引擎

1.什么是MySQL存储引擎

存储引擎概述

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。

Oracle，SQL Server等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

简单来说，MySQL提供了可插板式的存储引擎，你需要什么功能，就切换什么存储引擎。我们接下来就是要了解不同的存储引擎到底提供了什么功能。

查看存储引擎

• 查看当前数据库服务器支持哪些存储引擎：

• 查看当前数据库默认使用什么存储引擎：

修改默认存储引擎

可以看到MySQL默认使用的存储引擎是InnoDB，如果想要修改MySQL的存储引擎可以有如下操作

• 1）创建表的时候指定存储引擎

CREATE TABLE `demo1` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM default CHARSET=utf8;			-- 使用Myisam存储引擎


CREATE TABLE `demo2` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;		-- 使用InnoDB

• 2）基于已经创建好的表修改存储引擎

mysql> alter table demo2 engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

这里需要注意一点：存储引擎是基于表的，一个数据库下可以有很多不同存储引擎的表。

常见存储引擎

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁、表锁	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

我们发现，在众多存储引擎中，只有InnoDB支持事务

2.存储引擎的特点

InnoDB 存储引擎

InnoDB存储引擎是MySQL的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。并且会占用更多的磁盘空间以保留数据和索引。

• InnoDB 存储引擎特点如下：
  • 事务：支持事务的ACID
  • 外键：支持外键
  • 锁粒度：行锁（表锁）
  • 存储形式为：.frm 表定义文件 .ibd 数据文件

这里说明一下，InnoDB在没有使用到索引的情况下也会触发表级锁。

InnoDB架构

InnoDB架构图如下

内存池

主要工作：

• 维护所有进程/线程需要使用的多个内部数据结构
• 缓存磁盘上的数据，方便快速地读取，同时对磁盘文件数据修改之前在这里缓存
• 重做日志缓存

InnoDB内存池主要有以下部分

缓冲池
InnoDB是基于磁盘存储的，并将其中的记录按照页的方式进行管理。
而缓冲池就是一块内存区域，主要缓冲数据页和索引页。
InnoDB中对页的读取操作，首先判断该页是否在缓冲池中，若在，直接读取该页，若不在则从磁盘读取页数据，并存放在缓冲池中。
对页的修改操作，首先修改在缓冲池中的页，再以一定的频率（Checkpoint机制）刷新到磁盘。
参数：innodb_buffer_pool_size设置缓冲池大小

缓冲池通过LRU（Latest Recent Used，最近最少使用）算法进行管理。最频繁使用的页在LRU列表前端，最少使用的页在尾端，当缓冲池不能存放新读取的页时，首先释放LRU列表尾端的页（页数据刷新到磁盘，并从缓冲次中删除）。
InnoDB对于新读取的页，不是放到LRU列表最前端，而是放到midpoint位置（默认为5/8处）。
这是因为一些SQL操作会访问大量的页（如全表扫描），读取大量非热点数据，如果直接放到首部，可能导致真正的热点数据被移除。

关于页的概念会在存储篇解释，这里就理解为InnoDB将表数据拆分为若干固定大小的页，每页保存若干表记录。

重做日志缓存
重做日志先放到这个缓冲区，然后按一定频率刷新到重做日志文件。
参数：innodb_log_buffer_size

刷新规则：

1. Master Thread每秒将一部分重做日志缓冲刷新到重做日志文件
2. 每一事务提交时会将重做日志刷新到重做日志文件（如果配置了）
3. 重做日志缓冲区使用空间大于1/2

额外的内存池
内存堆，对InnoDB内部使用的数据结构对象进行管理

Checkpoint机制

InnoDB对于对于DML语句操作（如Update或Delete），事务提交时只需在缓冲池中中完成操作，然后再通过Checkpoint将修改后的脏页数据刷新到磁盘。

重做日志

重做日志是为了保证事务的原子性，持久性。InnoDB采用Write Ahread Log策略，事务提交时，先写重做日志，再修改页。
数据库宕机重启时通过执行重做日志恢复数据。
但由于Checkpoint机制，数据库宕机重启并不需要重做所有的日志，因为Checkpoint之前的页都刷新到磁盘了，只需执行最新一次Checkpoint后的重做日志进行恢复，这样可以缩短数据库的恢复时间。

InnoDB中重做日志文件是循环使用的。当页被Checkpoint刷新到磁盘后，对应的重做日志就不需要使用 ，其空间可以被覆盖重用。
如果待写入的重做日志文件空间不可用（脏页还没有刷新到磁盘），就需要强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

线程

主要作用：

• 负责刷新内存池中的数据，保证缓冲池的内存缓冲的是最近的数据
• 已修改的数据文件刷新到磁盘文件
• 保证数据库发生异常的情况下InnoDB能恢复到正常状态。

InnoDB运行时主要有以下线程
Master Thread
负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲（INSERT BUFFER），UNDO页的回收等。

IO Thread
负责AIO请求的回调处理。
参数：innodb_read_io_threads，innodb_write_io_threads

Purge Thread
事务提交后，undo log可能不再需要，由Purge Thread负责回收并重新分配的这些已经使用的undo页。
注意：Purge Thread需要离散地读取undo页。

Page Cleaner Thread
InnoDB 1.2.x引入，将Master Threader中刷新脏页的工作移至该线程，如上面说的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。

Master Thread

Master Thread具有最高的线程优先级别，内部由多个循环组成：主循环（loop），后台循环(backgroup loop)，刷新循环(flush loop)，暂停循环(suspend loop)，Master Thread根据数据库运行状态在以上循环切换。

InnoDB关键特性

插入缓存

插入缓冲（Insert Buffer/Change Buffer）：提升插入性能，change buffering是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效

只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

使用插入缓冲的条件：
* 非聚集索引
* 非唯一索引

二次写(double write)

Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页从innodb buffer pool中flush之后并写入到数据文件之前，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到数据文件的消耗，因为此写入操作会以一次大的连续块的方式写入

在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write

自适应哈希索引

InnoDB会监控对表上各索引页的查询执行情况，如发现建立哈希索引可以提升速度，则建立哈希索引，这是过程不需要用户干预。
参数：innodb_adaptive_hash_index，默认AHI为开启状态

异步IO

InnoDB使用异步IO操作磁盘，避免同步IO导致阻塞，也可以进行IO Merge操作，将多个IO操作合并为一个IO操作。

刷新邻接页

当刷新一个脏页时，InnoDB会检测该页所在区的所有页，如果是脏页，一起刷新，这是可以通过AIO将多个IO写入操作合并为一个IO操作。
参数：innodb_flush_neighbors，控制开关

MyISAM 存储引擎

在MySQL5.1及之前的版本，MySIAM是默认的存储引擎。MyISAM是基于ISAM引擎发展起来的，增加了许多有用的扩展。MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和修改并发。

• MyISAM存储引擎特点如下：

  • 事务：不支持事务

  • 外键：不支持外键

  • 锁粒度：表锁

  • 存储形式为：.frm 表定义文件 .myd 数据文件 .myi 索引文件

MyISAM表的存储格式

MySIAM表支持三种不同存储格式，分别为静态表、动态表、压缩表。

• 静态表：静态表是默认的存储格式。表中的字段都是定长的（非变长）。
  • 优点：存储非常迅速，容易缓存，服务器崩溃数据易恢复。
  • 缺点：占用的空间通常比动态表多。
• 动态表：动态表的字段是变长的
  • 优点：占用的空间相对较少。
  • 缺点：频繁地更新删除记录会产生碎片，需要定期使用optimize table语句优化表，整理碎片，服务器崩溃数据难恢复。
• 压缩表：MyISAM可以使用myisampack工具压缩表数据，压缩表占用磁盘空间小，每个记录是被单独压缩的，所以只有非常小的访问开支。

说了这么多类型的表，那么怎么样创建静态表、动态表、以及压缩表呢？

其实静态表和动态表不用我们创建，当表不包含变长列（VARCHAR, BLOB, 或TEXT）时，该表就是静态表，反之如果一个MyISAM表包含任何可变长度列（VARCHAR, BLOB或TEXT）那么此表就是动态表。

• 总结：
  • 当表不包含变量长度列（VARCHAR, BLOB, 或TEXT）时为静态表
  • MyISAM表包含任何可变长度 列（VARCHAR, BLOB或TEXTDynamic）时为动态表

当然了，还有一种情况，如果表中都没有变长列，我依旧想使用动态表呢（毕竟占用空间少嘛）？

答：你可以在创建表的时候就指定这张表为动态表

CREATE TABLE `demo3` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` char(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM default CHARSET=utf8 ROW_FORMAT=DYNAMIC;

ROW_FORMAT=DYNAMIC：指定该表为动态表。

你刚刚说完了静态表和动态表，那么压缩表呢？

MySQL官方对压缩表的定义就是使用过myisampack命令进行压缩的表，就是压缩表，简单吧？那么myisampack命令又是什么呢？

新建一个myisam的数据表，并插入10w条数据，查看所占内存大小

数据占用了15M，索引占用了1004KB

• 我们使用myisampack工具，对表进行压缩：

myisampack userinfo

使用myisampack工具压缩过后，发现数据文件占用6.4M，索引文件占用1KB。此时这个表就是压缩表了。

说明一下：MySQL官方提出使用myisampack压缩过后的数据为只读模式，不可修改。如果想要修改，请使用myisamchk工具先把数据解压。

MyISAM与InnoDB对比

	Innodb	MyISAM
存储文件	.frm 表定义文件 .ibd 数据文件	.frm 表定义文件 .myd 数据文件 .myi 索引文件
锁	行锁、表锁	表锁
事务	支持	不支持
CRDU	读、写	读多
count	扫表	专门存储的地方
索引结构	B+Tree	B+Tree
应用场景	事务	读取性能
外键	支持	不支持

• 存储文件

  • Innodb：.frm 表定义文件 .ibd 数据文件
  • MyIsam：.frm 表定义文件 .myd 数据文件 .myi 索引文件

• 锁

  • Innodb：锁粒度为行级锁，操作某一条数据时，只锁某一行，对其他行没有影响
  • MyIsam：锁粒度为表级锁，操作某一条数据时，锁整一张表

• 事务支持：

  • Innodb：支持
  • MyIsam：不支持

• 外键支持：

  • Innodb：支持
  • MyIsam：不支持

• 应用场景

  • Innodb：关注事务要求性高的应用场景，适用于高并发修改场景（锁的粒度小）
  • MyIsam：更关注读取的性能，不适用于高并发下频繁的修改数据（锁的粒度大）

文件结构不同

记牢：
InnoDB存储文件为两个：.frm（表定义文件）、.idb（数据&索引文件）
MyISAM存储文件为三个：.frm（表定义文件）、myd（数据文件）、（myi）索引文件）

总结：MyISAM的索引和数据文件是分开的，InnoDB的数据和索引文件是放在一起的。

锁粒度不同

InnoDB并发修改情况下：

只要不同的客户端操作的不是同一条记录，那么不会造成锁等待。

MyISAM并发修改情况下：

其实表一旦上锁了，其他线程读/写都不能操作，必须要等待上一个线程释放锁（操作完毕）。因此我们可以知道，MyISAM表在出现并发读写的情况下操作，效率极其低下。

事务支持

InnoDB是支持事务的，MyISAM是不支持事务的，这也是两个存储引擎最大的区别之一。

测试MyISAM不支持事务

发现事务还没有提交，另一个窗口就可以查询到数据，MyISAM是不支持事务的。

测试InnoDB支持事务

发现事务还未提交，另一个窗口就查询不到（我现在数据库的事务隔离级别为"可重复读"）
MyISAM存储引擎不支持事务，也自然而然不存在事务隔离级别的概念。

再提一点，MyISAM存储引擎是不支持外键约束的，我们实际开发中，通常也不使用外键约束，一般使用的都是逻辑外键（通过应用程序控制逻辑关系），因此这个我就不演示了，有兴趣的小伙伴可以自行测试。

插入性能对比

编写两张表，两个存储过程，一张存储引擎为InnoDB，一张存储引擎为MyISAM表。存储过程用于批量插入

同样是插入10W的数据，InnoDB需要31.60s，而MyISAM只需要1.30s。

在这里可能有人会想起前面的对，不是MyISAM是表级锁吗？InnoDB还是行级锁呢！为什么还比InnoDB快那么多啊！如果你这样想，那就大错特错了！记住，锁是在并发情况下的，并发！并发！咱们现在是一个客户端呢！

• 在这里稍微解释一下，为什么MyISAM插入性能比InnoDB快这么多。

InnoDB插入速度慢的很大一部分原因还是因为事务的原因。

1、频繁的开启/提交事务，会消耗相当大的一部分资源。

2、其次InnoDB需要支持事务，需要频繁记录事务日志（redo log/undo log，当然你也可以调整事务刷新频率innodb_flush_log_at_trx_commit）。

3、并且innodb需要维护MVCC一致。

这些操作极大的影响了innodb在插入时的速度，Myisam则这些问题统统不用考虑。

查询性能对比

为了把对比效果彰显的更加明显，我们把数据量切换为300W。

truncate user_innodb;

truncate user_myisam;

-- 关闭唯一性校验
set unique_checks=0;

-- 控制在一个事务中(提高插入速度)
start transaction;

call innodb_insert(3000000);

commit;

call myisam_insert(3000000);

谈谈为什么InnoDB查询效率会比MyISAM慢？

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护MVCC (Multi-Version Concurrency Control)多版本并发控制

索引上的区别

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

​ 也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

其他区别

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

那么为什么InnoDB没有了这个变量呢？

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS

Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

Memory存储引擎

Memory存储引擎将表的数据存放在内存中。每个Memory表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。Memory类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的数据就会丢失。

• 创建一张Memory存储引擎的表

CREATE TABLE `user_memory`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = memory AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 ;

insert into user_memory values(1,'zs');

insert into user_memory values(2,'ls');

发现只有表定义文件（.frm），memory的表数据是存储在内存中的。

• 我们编写一个存储过程，批量插入300W数据

create procedure memory_insert(count int)
begin
		declare i int default 1;
		while i<=count do 
			INSERT INTO user_memory values(i,
										uuid(),
										CEILING(RAND()*90+10));
			set i=i+1;
		end while;
end;

出现：ERROR 1114 (HY000): The table ‘user_memory’ is full错误

在MySQL中，有两个参数影响着memory表的使用：

• max_heap_table_size：memory表能使用的最大内存大小
• tmp_table_size：memory表超过此参数值将转化为磁盘表（即使转换为磁盘表，服务器重启后，数据仍会丢失）。

出现上面错误的原因是MySQL给Memory表分配的默认内存大小是16MB，300W的数据远远超过了16MB的存储空间，我们可以通过修改max_heap_table_size变量，来提升Memory表的存储大小限制。

16777216B / 1024K / 1024M =16‬M

修改mysql配置文件（/etc/my.cnf），添加如下配置：

max_heap_table_size=800M
tmp_table_size=800M

重启mysql服务器

再次插入300W数据：

要注意的是，在mysql服务器重启之后，memory存储引擎的表中的数据都会被清空。

使用Memory存储引擎需要注意如下几点：

• Memory不支持BLOB或TEXT列。
• Memory表在所有客户端之间共享
• Memory表内容被存在内存中，默认大小是16MB，通过max_heap_table_size参数维护，当超过此大小则会报错，Memory表中的内容超过tmp_table_size值则会采用磁盘表，此时速度低，重启服务器数据仍会丢失
• 当你不再需要Memory表的内容之时，要释放被Memory表使用的内存，你应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE）。

这里提一下：如果真的要使用内存存储数据，一般我们都会采用市面上比较流行的缓存数据库（比如Redis），而不会采用Memory存储引擎，这些专门做缓存的数据库功能、操作都比Memory存储引擎要强大的多，我个人觉得这是Memory存储引擎没落的根本原因，当然另外一个原有也有可能是Memory存储引擎提供了很多其他的高级功能，由于市面上的资料和文献相对较少，因此大众并不知道Memory存储引擎的高级功能。

Merge 存储引擎

Merge存储引擎，也被认识为MRG_MyISAM引擎，Merge表 用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它。这些MyISAM表必须结构完全相同。Merge 表本身没有数据，对Merge 类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。

Merge 数据表的定义里可以包括一个INSERT_METHOD选项，这个选项的可取值是NO、FIRST、LAST，他们的含义依次是禁止插入、插入到第一个子表、插入到最后一个子表。

create table user_01(
	id int,
	username varchar(10)
) engine = myisam default charset=utf8;

create table user_02(
	id int,
	username varchar(10)
) engine = myisam default charset=utf8;

create table user_all(
	id int,
	username varchar(10)
) engine=merge union = (user_01,user_02) INSERT_METHOD=LAST default charset=utf8;

使用union确定merge表关联的myisam表，注意，这些myisam必须一模一样。

INSERT_METHOD=LAST ：代表可以对merge表进行插入操作，进行的插入操作都到union关联的最后一张表去了（user_02）表。

• （2）在表中插入数据：

insert into user_01 values(1,'张三');
insert into user_01 values(2,'李四');

insert into user_02 values(10,'王五');
insert into user_02 values(11,'赵六');

• （3）查看表中的数据：

• 在user_all表中插入一条数据：

因为在创建Merge表时，INSERT_METHOD设置的是LAST，因此插入到最后面的表中（user_02）。

Merge表除了在创建的时候选择合并的表，还可以在创建之后再添加其他的表（表结构必须一致）

• 1）创建一张新的表：

create table user_03(
	id int,
	username varchar(10)
) engine = myisam default charset=utf8;

存储引擎必须是MyISAM，其次表结构必须完全相同。

• 2）插入数据：

insert into user_03 values(1,'小龙');
insert into user_03 values(2,'小明');

• 3）修改user_all表

alter table user_all union = (user_01,user_02,user_03);

• 4）查询user_all表：

• 5）在user_all插入一条新的数据，注意，此时的LAST表为user_03了；

insert into user_all values(1,'小刚');

当你创建一个Merge表之时，MySQL在磁盘上创建两个文件。文件名以表的名字开始，并且有一个扩展名来指明文件类型。一个.frm文件存储表定义，一个.MRG文件维护着此Merge表关联了多少张myisam表（真正的数据不存放在.MRG文件，而是存放在不同的.MYD文件中）

Merge表有以下优点：

• 容易地管理一套日志表。比如，你可以把不同月的数据放进分离的表中，用myisampack压缩其中的一些，并随后创建一个Merge表来把它们当作一个来使用。
• 获得更快的速度。你可以以一些标准来分割大的只读表，然后放进不同磁盘上的单个表中。基于此的一个Merge表可比使用大表要快得多。
• 执行更有效的搜索。如果你确切知道要搜索什么，对一些查询你可以只在被分割的表的其中之一来搜索，并且对其它使用Merge。
• 执行更有效的修补。修补被映射到一个Merge表中的单个表比修补单个大型表要更轻松。
• 超过操作系统的文件尺寸限制。每个MyISAM表都受制于这个限制，但是一个MyISAM表的集合则不然。

---

Merge表的缺点：

• Merge表只支持MyISAM表。
• 你不能在Merge表中使用很多MyISAM功能。比如，你不能在Merge表上创建FULLTEXT索引。

4.锁

1.什么是MySQL锁，作用是什么

在数据库中，除了传统的计算资源（像CPU、RAM、I/O等）的应用之外，数据也是一种供许多用户共享的资源。为了保证对数据并发访问的一致性，就需要一种机制来保证当并发访问数据库时能保持数据的一致性。这种机制就是锁。

作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

2.mysql锁的类别和作用

全局锁

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

使用场景

全局锁的典型使用场景是，做全库逻辑备份（mysqldump），重新做主从时候。也就是把整库每个表都select出来存成文本

数据库只读状态存在的问题：

• 在主库上备份，期间不能执行更新，业务基本停摆
• 在从库上备份，期间从库不能执行主库同步过来的binlog，从而导致主从延迟

表级锁

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

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

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

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大

MDL锁

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

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

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

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

注 ： 一般行锁都有锁超时时间。但是MDL锁没有超时时间的限制，只要事务没有提交就会一直锁注。

行锁

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

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

两阶段锁协议

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tevwSPTF-1651626535254)(D:\笔记\img\image-20220309135948849-16486972140611.png)]

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

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

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

死锁和死锁检测

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

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

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

页锁

页锁：页级锁是 MysQL 中锁定粒度介于行级锁和表级锁中间的一种锁．表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间，会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

间隙锁

隔离级别：可重复读隔离级别,innodb默认隔离级别

为了解决幻读问题，InnoDB引入了间隙锁，锁的就是两个值之间的空隙

跟间隙锁存在冲突关系的是往这个间隙中插入一个记录这个操作。间隙锁之间不存在冲突关系

这里sessionB并不会被堵住。因为表t里面并没有c=7会这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们用共同的目标，保护这个间隙，不允许插入值。但它们之间是不冲突的

Innodb自动使用间隙锁的条件：

（1）必须在Repeatable Read级别下

（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

图一中，where number=5的话，那么间隙锁的区间范围为（4,11）；

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：

（1）防止间隙内有新数据被插入

（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

Next-Key Lock是行锁与间隙锁的组合，当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock,即仅锁住索引本身，而不是范围！若是普通辅助索引，则会使用传统的next-key lock进行范围锁定！

对于快照读来说，幻读的解决是依赖mvcc解决。而对于当前读则依赖于gap-lock解决。

MySQL/InnoDB定义的4种隔离级别：

Read Uncommited

可以读取未提交记录。此隔离级别，不会使用，忽略。

Read Committed (RC)

快照读忽略，本文不考虑。

针对当前读，RC隔离级别保证对读取到的记录加锁 (record lock)，存在幻读现象。

Repeatable Read (RR)

快照读忽略，本文不考虑。

针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

临键锁（Next-Key lock)

隔离级别：可重复读隔离级别

也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

next-key lock加锁规则：

• 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间
• 原则2：查找过程中访问到的对象才会加锁
• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
• 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

锁是加在索引上的，lock in share mode只锁覆盖索引，但是如果是for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

乐观锁和悲观锁

乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

悲观锁：

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁的具体流程：

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

悲观锁的优点和不足：

悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。

乐观锁：

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。如MVCC

乐观锁的优点和不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

共享/排它锁

简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。于是出现了共享锁与排他锁：

• 共享锁（Share Locks，记为S锁），读取数据时加S锁

• 排他锁（eXclusive Locks，记为X锁），修改数据时加X锁

共享锁与排他锁：

• 共享锁之间不互斥，读读可以并行

• 排他锁与任何锁互斥，写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。

有没有可能，进一步提高并发呢？

即使写任务没有完成，其他读任务也可能并发，MySQL通过多版本控制解决此问题。（快照读）

意向锁

意向锁是InnoDB为了支持多个粒度上的锁定提出的概念，可以让行级锁和表级锁共存。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁的特点：

①首先，意向锁，是一个表级别的锁(table-level locking)；

②意向锁分为：

• 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

• 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

比如select … lock in share lock会加IS锁，select … for update会加IX锁。

在一个事务获取一张表S行锁之前，必须对这张表加IS锁或更高强度的锁，在一个事务获取一张表X行锁之前，必须对这张表加IX锁。

由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

	IS	IX
IS	兼容	兼容
IX	兼容	兼容

既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：

	S	X
IS	兼容	互斥
IX	互斥	互斥

补充：排它锁是很强的锁，不与其他类型的锁兼容。这也很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。

意向锁解决什么问题？

当对一个表加表锁的时候，需要知道表的内部是否有兼容或者互斥的锁，然后才能判断是否可以加锁。但逐行扫描表中的记录是否有锁比较耗时，所以就引入了意向锁，要加表锁时，通过意向锁就可判断表内部记录的加锁情况。

记录锁

记录锁又称为行锁，是InnoDB专门提供的一种锁，上面例子里，select * from MyBooks where id = 0 lock in share mode就会加上一个行锁。

行锁会在增、删、改操作里自动加上，在查询操作，可以显示加上lock in share mode或for update加上。

需要说明的是： select * from t where id=1; 则是快照读(SnapShot Read)，它并不加锁

行锁只会对索引行生效，有几个点需要注意一下:

• 命中的索引必须是主键或者唯一键索引
• 如果SQL语句没有使用索引或者优化器决定不用索引，那么就会锁全表

插入意向锁

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

存储引擎：InnoDB

隔离级别：可重复读隔离级别

插入意向锁的特性可以分成两部分：

1. 插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
2. 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

总结

1. MySql InnoDB 在 Repeatable-Read 的事务隔离级别下，使用插入意向锁来控制和解决并发插入。
2. 插入意向锁是一种特殊的间隙锁。
3. 插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。

也就是说，当使用间隙锁的时候会影响插入，但只做插入操作时，使用插入意向锁可以去除间隙不能插入数据的规则。

自增锁

自增锁是MySQL一种特殊的锁，如果表中存在自增字段，MySQL便会自动维护一个自增锁。

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插 AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

举个例子(表依然是如上的例子lock_example)，但是id为AUTO_INCREMENT，数据库表中数据为：

1, zhangsan 
2, lisi
3, wangwu

事务A先执行，还未提交：

 insert into t(name) values(xxx);

事务B后执行：

insert into t(name) values(ooo);

此时事务B插入操作会阻塞，直到事务A提交。

3.总结

按锁的互斥程度来划分，可以分为共享、排他锁也叫做读锁和写锁。

• 共享锁(S锁、IS锁)：可以通过lock in share mode实现，这时候只能读不能写，可以提高读读并发；
• 互斥锁(X锁、IX锁)：为了保证数据强一致，InnoDB使用强互斥锁(X锁、IX锁)，它会阻塞其他的写锁和读锁，保证同一行记录修改与删除的串行性；

按锁的粒度来划分，可以分为：

• 表锁：意向锁(IS锁、IX锁)、自增锁；

• 行锁：记录锁、间隙锁、临键锁、插入意向锁；

• • 乐观锁：乐观锁则通过版本号实现。（用户实现）
  • 悲观锁：悲观锁可以通过for update实现。(数据库实现）

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

• MyISAM和MEMORY存储引擎采用的是表级锁
• BDB存储引擎采用的是页面锁，但也称表级锁
• InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

MyISAM：表级锁。

InnoDB：共享/排它锁，意向锁，记录锁，间隙锁，临键锁，插入意向锁，自增锁。

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

1.表级锁定（table-level）

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

2.行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

3.页级锁定（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

使用页级锁定的主要是BerkeleyDB存储引擎。

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

4.索引

索引是帮助数据库快速查询数据的一种数据结构，在数据库中，数据库系统除了存储数据之外还维护着一种特殊的数据结构，这种数据结构以某种方式指向数据，这样就可以在这种数据结构上实现高级算法来查询数据，这种数据结构就是索引。

索引是啥？索引就是帮助我们快速查找数据的一种数据结构！没毛病！

• 索引示意图：

如图所示，索引能够帮我们快速的定位到数据的具体位置，高效查询。一般来说，索引本身也很大，不可能全部存储在内存当中，因此索引往往以索引文件的形式存储在磁盘上。索引是用来提供高性能数据库的常用工具。

1.索引的数据结构

• B-Tree：常见的索引类型，B+Tree就是在此数据结构上做的优化
• Hash：是基于哈希表实现的，只有精确匹配索引所有列的查询才会生效。对于每一行数据，存储引擎都会对所有的索引列计算一个hash code,并将有的hash code存储在索引中，同时在哈希表中保存指向每个数据行的指针。
• R-Tree：R-Tree是B-tree在高维空间的扩展，也叫空间索引，主要用于地理空间数据类型，存储高维数据的平衡树。
• Full-text：Full-text索引就是我们常说的全文索引，它的存储结构也是B-Tree。主要是为了解决当须要用like查询时的低效问题。

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持：

索引	InnoDB	MyISAM	Memory
B-Tree	支持	支持	支持
Hash	不支持	不支持	支持
R-Tree	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

哈希表（Hash）

哈希表是做数据快速检索的有效利器。

哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mTVA5I6E-1651626535256)(D:\笔记\img\image-20220227212601255.png)]

哈希算法有个数据碰撞的问题，也就是哈希函数可能对不同的 key 会计算出同一个结果，比如 hash(7)可能跟 hash(199)计算出来的结果一样，也就是不同的 key 映射到同一个结果了，这就是碰撞问题。解决碰撞问题的一个常见处理方式就是链地址法，即用链表把碰撞的数据接连起来。计算哈希值之后，还需要检查该哈希值是否存在碰撞数据链表，有则一直遍历到链表尾，直达找到真正的 key 对应的数据为止。

但是 Mysql 并没有采取哈希作为其底层算法，这是为什么呢？

因为考虑到数据检索有一个常用手段就是范围查找，比如以下这个 SQL 语句：

select \* from user where id \>3;

针对以上这个语句，我们希望做的是找出 id>3 的数据，这是很典型的范围查找。如果使用哈希算法实现的索引，范围查找怎么做呢？一个简单的思路就是一次把所有数据找出来加载到内存，然后再在内存里筛选筛选目标范围内的数据。但是这个范围查找的方法也太笨重了，没有一点效率而言。

所以，使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

二叉查找树

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5SE5QXZJ-1651626535257)(D:\笔记\img\image-20220227213051386.png)]

二叉查找树的时间复杂度是 O(logn)，比如针对上面这个二叉树结构，我们需要计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上看来是能做到高速检索的。此外二叉树的结构能不能解决哈希索引不能提供的范围查找功能呢？

答案是可以的。观察上面的图，二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也算是比较容易实现。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Egetggeo-1651626535257)(D:\笔记\img\image-20220227213156368.png)]

在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 Mysql 底层索引的。

AVL树和红黑树

二叉查找树存在不平衡问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

首先简单介绍红黑树，这是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。比如从 1 到 7 升序插入数据节点，如果是普通的二叉查找树则会退化成链表，但是红黑树则会不断调整树的形态，使其保持基本平衡状态，如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

红黑树拥有不错的平均查找效率，也不存在极端的 O(n)情况，那红黑树作为 Mysql 底层索引实现是否可以呢？其实红黑树也存在一些问题，观察下面这个例子。

红黑树顺序插入 1~7 个节点，查找 id=7 时需要计算的节点数为 4。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nyYcvyny-1651626535258)(D:\笔记\img\image-20220227213509955.png)]

红黑树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 6 次。观察一下这个树的形态，是不是当数据是顺序插入时，树的形态一直处于“右倾”的趋势呢？从根本上上看，红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗，我们数据库不可能忍受这种无意义的等待的。

现在考虑另一种更为严格的自平衡二叉树 AVL 树。因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

AVL 树顺序插入 1~7 个节点，查找 id=7 所要比较节点的次数为 3。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8bOYe4K7-1651626535258)(D:\笔记\img\image-20220227213603239.png)]

AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lXbc7A4c-1651626535258)(D:\笔记\img\image-20220227213621471.png)]

总结一下 AVL 树的优点：

1. 不错的查找性能（O（logn）），不存在极端的低效查找的情况。
2. 可以实现范围查找、数据排序。

看起来 AVL 树作为数据查找的数据结构确实很不错，但是 AVL 树并不适合做 Mysql 数据库的索引数据结构，因为考虑一下这个问题：

数据库查询数据的瓶颈在于磁盘 IO，**如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。**所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

B树

下面这个 B 树，每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树，只需要查询两个节点就可以知道 id=7 这数据的具体位置，也就是两次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Cp8Fvo5T-1651626535259)(D:\笔记\img\image-20220227214002405.png)]

下面是一个存储了 16 个数据的 B 树，同样每个节点最多存储 2 个 key，查询 id=16 这个数据需要查询比较 4 个节点，也就是经过 4 次磁盘 IO。看起来查询性能与 AVL 树一样。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CNcjuyU5-1651626535259)(D:\笔记\img\image-20220227214026033.png)]

但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致，那我们的优化思路就可以改为：尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是，每个节点能存储的 key 可以适当增加。

当我们把单个节点限制的 key 个数设置为 6 之后，一个存储了 7 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MYw1w5bp-1651626535259)(D:\笔记\img\image-20220227214057225.png)]

一个存储了 16 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。相对于 AVL 树而言磁盘 IO 次数降低为一半。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cnl04JDU-1651626535259)(D:\笔记\img\image-20220227214121476.png)]

所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。总结来说，B 树用作数据库索引有以下优点：

1. 优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；

2. 尽可能少的磁盘 IO，加快了检索速度；

3. 可以支持范围查找。

• B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针

例如要查找9，步骤如下：

• 我们与根节点的关键字 (17，35)进行比较，9 小于 17 那么得到指针 P1
• 按照指针 P1 找到磁盘块 2，关键字为(8，12)，因为 9 在 8 和 12 之间，所以我们得到指针 P2
• 按照指针 P2 找到磁盘块 6，关键字为(9，10)，然后我们找到了关键字 9

B+树

mysql的存储结构

• 表存储结构

• 单位：表 > 段 > 区 > 页 > 行
• 在数据库中， 不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说存储空间的基本单位是页
• 一个页就是一棵 B+树 的节点，数据库 I/O 操作的最小单位是页，与数据库相关的内容都会存储在页的结构里

为什么要用 B+树 索引 ?

• 数据库访问数据要通过页，一个页就是一个B+树节点，访问一个节点相当于一次I/O操作，所以越快能找到节点，查找性能越好
• B+树的特点就是够矮够胖，能有效地减少访问节点次数从而提高性能

B+树

• B+树是B树的改进，简单地说就是：只有叶子节点才存储数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

B 树和 B+树有什么不同呢？

第一，B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2YvwvwKx-1651626535260)(D:\笔记\img\image-20220227214340203.png)]

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

B+树与B树 的区别 ?

• B+树非叶子节点不存储数据只存储索引，B树非叶子节点存储数据
• B+树使用双向链表串连所有叶子节点，区间查询效率更高，因为所有数据都在B+树的叶子节点，但是B树则需要通过中序遍历才能完成查询范围的查找
• B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定
• B+树查询效率更高，因为B+树矮更胖，高度小，查询产生的 I/O操作 最少

我们把B-Tree补充完整（指针、数据块、磁盘块等）：

我们把B+Tree补充完整（指针、数据块、磁盘块等）：

在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。这样提高了磁盘块之间的区间访问性能

InnoDB数据页

操作系统的局部性原理

我们知道数据最终是存储在磁盘上，当我们要做数据处理时，需要把磁盘中的数据调出到内存中来进行处理，这个过程会产生磁盘的IO（输入输出），由于磁盘IO是非常昂贵的操作，所以计算机操作系统对此做了优化。每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中

当访问一个地址数据的时候，与其相邻的数据很快也会被访问到。每次磁盘IO读取的数据我们称之为一页（page）。一页的大小与操作系统有关，一般为4k或者8k。这也就意味着读取一页内数据的时候，实际上发生了一次磁盘IO。

InnoDB数据页管理

在InnoDB中，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

• B-Tree：

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

• B+Tree：

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3 ）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

2.聚集索引与非聚集索引

聚集索引：也叫聚簇索引（ClusterIndex），一般来说是以主键创建的索引，一张表只能有一个聚集索引，而且只有InnoDB能够创建聚集索引。

非聚集索引：也叫普通索引、辅助索引（Secondary Index），除了聚集索引外的索引都是非聚集索引。

聚集索引的数据存放在B+Tree索引树的叶子节点上，而非聚集索引的B+Tree索引树上只会存储当前索引列的数据与主键索引列的数据，并不会存放整行数据，当需要通过非聚集索引去检索一行数据时，首先非聚集索引通过索引树找到主键，然后通过主键去主键建立的B+Tree上查询出整行的数据；

聚集索引与非聚集索引底层原理图

我们通过聚集索引与非聚集索引的底层原理图来直观的感受下两者的区别

• 聚集索引与非聚集索引示意图：

在这里提一下，回表查询需要把聚集索引加载进内存，首先加载的应该是根节点，而不是直接定位到叶子节点，这里方便画图就没有画出来；

通过上面的索引图我们能够很直观的发现，普通索引（非聚集索引）的查询如果查询到了其他列的数据，那么需要借助主键索引来查询整行的数据，这个过程也称为回表查询；

索引组织表

其实，MySQL中的表的所有的数据都是按照主键排序的方式存储在一颗B+Tree上（InnoDB引擎），这颗B+Tree也叫聚集索引，所以叫MySQL叫索引组织表；

索引组织表的数据按主键排序手段被存储在B+Tree索引中，除了存储主键列值外还存储非主键列的值。普通索引只存储索引列，索引组织表就是索引（主键索引）存储表的所有列的值

聚集索引说明

关于聚集索引还有一个定义：数据行的物理排列顺序与该列值（主键）的逻辑顺序相同，并且一个表中只能拥有一个聚集索引。

什么是"数据行的物理排列顺序"？

数据行的物理排列顺序就是我们眼睛直观看到的列的排列顺序

一般来说我们主键都是自增的，大小都是从小到大，因此会忽略一个问题：数据行的物理排列顺序与聚集索引的排列顺序是保持一致的，

我们把"小军"的ID改为50，重新查看数据库表，发现排列顺序如下：

发现数据行的物理排列顺序默认是和主键顺序保存一致的；

在InnoDB中，数据表中的物理排列顺序始终会保持与聚集索引的逻辑顺序一致；而聚集索引底层是一个B+Tree索引树，自然而然逻辑排列顺序是：从小到大顺序排列；因此数据表中的物理排列顺序是根据主键来排序的，也就是我们刚刚说的聚集索引的逻辑顺序；

这也是网上很多人说聚集索引在一张表中只能有一个的原因，为什么呢？因为我数据表的逻辑排列顺序要与聚集索引的排列顺序保持一致呀！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q31wDsRL-1651626535260)(D:\笔记\img\image-20220227215047323.png)]

问题来了，为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

MyISAM引擎为什么只能创建非聚集索引？

我们都知道InnoDB是底层存储文件是分为：.frm（表空间文件）、.idb（索引文件和数据文件）；

MyISAM则是分了三个文件：.frm（表空间）、.MYD（数据文件）、MYI（索引文件）；

MySIAM引擎索引底层实现原理图

虽然MyISAM和InnoDB的索引底层采用的都是B+Tree数据结构，但MyISAM和InnoDB索引的底层实现稍有不同：

MyISAM没有聚集索引，MyISAM建立的都是普通索引（即使是主键）；

MyISAM引擎是没有聚集索引的，因为MyISAM引擎不属于索引组织表，即有单独的空间存储表数据，表数据并不是和主键建立的B+Tree存储在一起的；MyISAM表通过任何索引来查询数据都需要回表查询；

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

InnoDB表能不能没有聚集索引？

我们知道InnoDB表是属于索引组织表，整表的数据都需要根据主键（聚集索引）来排序，并且数据也是存储在主键（聚集索引）的B+Tree上的；那么万一我们在表中没有创建主键（聚集索引）那么怎么办呢？？？

InnoDB必须要有且只有一个主键（聚集索引）！！！不能没有主键（聚集索引）！！！

那奇了怪了，我在InnoDB建表的时候明明可以不创建主键（聚集索引）呀,也没见他报错呀，咋回事？？？？

是这样的，主键（聚集索引）对于InnoDB实在是太重要了，InnoDB不能没有他，如果你不创建他会根据规则来选出较为合适的一列来做聚集索引，实在不行他就帮你创建一个隐藏的列作为聚集索引，规则如下：

（1）如果表定义了主键，则该列就是聚集索引；

（2）如果表没有定义主键，则第一个not null unique列是聚集索引；

（3）以上条件都不满足：InnoDB会创建一个隐藏的row-id作为聚集索引；

3.一级索引和二级索引

大家多多少少有听过一些DBA大佬口中经常说一级索引、二级索引吧？啥是一级索引和二级索引？？？

关于一级索引的定义：索引和数据存储是在一起的，都存储在同一个B+Tree中的叶子节点。一般主键索引都是一级索引。

关于二级索引的定义：二级索引树的叶子节点存储的是本列索引值和主键值；而不是数据。在使用二级索引检索数据时，需要借助一级索引；也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。

咋一看怎么和聚集索引和非聚集索引那么像？？？

对！一级索引就是聚集索引；二级索引就是非聚集索引！！

4.其他索引

覆盖索引

覆盖索引（或称索引覆盖）：即从二级索引中就可以得到要查询的记录，而不需要查询聚簇索引中的记录（回表查询），很显然，聚簇索引就是一种覆盖索引，因为聚簇索引中包含了数据行的全部数据，而非聚集索引的话，要看SQL语句查询的列是在索引树上，如果不在则需要回表查询；简单的说就是查询的列要被所使用的索引覆盖，换句话说就是查询的列要在索引树上，不需要回表查询。

覆盖索引应用

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

我们给username列加上索引：

create index idx_name on t_user(username);

• 执行如下SQL：

-- username创建的B+Tree上有值（使用了覆盖索引）
explain select username from t_user where username='xxx';

-- username创建的B+Tree上有值（使用了覆盖索引）
explain select id,username from t_user where username='xxx';

-- username创建的B+Tree上没有值,age列需要回表到聚集索引上去查询（没有使用覆盖索引）
explain select id,username,age from t_user where username='xxx';

上面提一下，有得时候MySQL的执行计划不是非常准确，因为MySQL底层有优化器来优化SQL，所以我们看到的执行计划的信息有得时候可能不是很准确

覆盖索引：查询的数据被索引树覆盖了，即：查询的数据在索引树上，不需要回表查询；

前缀索引

前缀索引也是一种概念，或者说是操作索引的一种技巧；当索引列的数据是非常大时，那么该列建立的索引会非常大，而且检索速度也会很慢，这个时候我们考虑能否让该列的前面几个字符拿出来建立索引，而不是整列是数据建立索引，因此前缀索引的概念就由此产生；

我们知道前缀索引其实就是拿出该列数据的前几个字符出来建立索引来降低索引的大小，以及加快索引的速度的一种技巧性索引，但是毕竟前面几个字符不能够代替整列数据，有可能重复，我们应该尽量的减低重复的概率，提高不重复的概率，这样的前缀索引检索速度更快；

前缀索引的应用

• 计算不重复率：

发现不重复率在birthday字段在第9个字符时，达到100%，也就是说，在的倒数第二个字符时，数据没有重复的。

计算出不重复率最高时是在该列的低9个字符，因此我们可以把第9个字符之外的数据不用建立索引，来降低索引的大小，提高索引的检索速度；

alter table student add key(birthday(9));

下次根据birthday字段查询，会发现使用了索引查询：

explain select birthday from student where birthday='1999-02-21';

如果建立的前缀索引有重复数据会怎么样？

我们刚刚建立前缀索引时，首先要计算不重复率，然后再根据得出合适的位置来建立索引，那么如果不计算不重复率会怎么样？？

我们把刚刚建立的索引删除，在第4个字符位置上建立索引

我们随机执行了几条SQL语句，发现都没有走索引查询，而是全表顺序扫描，因为重复的数据太多了（占整表数据），MySQL优化器认为走索引还不如不走索引，因此选择顺序扫描查询；

我们切换个案例，把索引前缀字符切换为7：

辅助索引

辅助索引就是辅助我们查询的所有，一般指的就是非聚集索引（二级索引）

辅助索引就是二级索引，so，就这么简单！！！！ 关于二级索引的概念不用再说了吧？？

5.索引的类型

这么多索引我们该怎么记？？以及该如何区分？？

我们看待事物的角度不同，索引也可以分为以下角度：

• 数据结构角度：
  • B+Tree索引
  • R-Tree索引（空间索引）
  • Hash索引
  • Full-text索引（全文索引）
• 物理存储角度
  • 聚集索引 （一级索引）
  • 非聚集索引（二级索引）
• 逻辑角度
  • 主键索引
  • 普通索引（单列索引）
  • 多列索引（复合索引）
  • 唯一索引
  • 非唯一索引
  • 空间索引
• 业务角度
  • 覆盖索引
  • 前缀索引
  • 辅助索引（非聚集索引）

我们平常所说的索引，如果没有特别指明，都是指B+Tree结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

MySQL目前主要有以下几种索引类型：
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引

1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

总结

• **树的演变：**二叉树演变平衡二叉树（AVL树）、平衡二叉树演变多路平衡搜索树（B-Tree）、B-Tree演变B+Tree

---

• **B+Tree相对于B-Tree的改进：**将数据都存放在叶子节点，让每一个磁盘块都能够存储更多的指针，减少IO检索次数，并且叶子节点都加上循环链表，提高区间访问能力

---

• **操作系统的局部性原理：**计算机操作系统每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中

---

• **InnoDB数据页管理：**InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB，因此B+Tree数据结构可以使InnoDB每次读取一页时可以获取更多的指针，查询更多的数据

---

• **聚集索引与非聚集索引：**聚集索引的数据和指针是存储在一起的，非聚集索引的B+Tree上只存储索引列的值与主键值，当使用非聚集索引查询数据时，如查询的列包含其他列数据，则需要借助主键回表查询；

---

• **MyISAM引擎为什么不可以建立聚集索引？：**MyISAM存储引擎有单独的空间存储表数据，表数据并不是和主键建立的B+Tree存储在一起的，在MyISAM引擎中任何的索引都需要回表查询，具体参考3.5.1小节的：MyISAM存储引擎B+Tree底层实现原理图

---

• **索引组织表：**MySQL中的表的所有的数据都是按照主键排序的方式存储在一颗B+Tree上（InnoDB引擎），所以MySQL也叫索引组织表；

---

• **InnoDB能不能没有主键？：**InnoDB必须要有且只有一个主键（聚集索引），不能没有主键，具体为什么参考3.6小节

---

• **什么是一级索引索引？什么是二级索引？：**一级索引就是聚集索引，二级索引就是非聚集索引，MyISAM引擎不可建立一级索引，建立的都是二级索引

---

• **索引覆盖：**覆盖索引只是一个概念，即查询的数据列在二级索引上，不需要回表查询，此时就说查询的数据"被索引覆盖了"，也叫索引覆盖，或称覆盖索引。聚集索引就是一种覆盖索引，因为聚集索引上包含了整行的数据；

---

• **前缀索引：**前缀索引也是一种概念，或者说是操作索引的一种技巧，当索引列非常大时，我们可以采取索引列数据的前面几个字符来建立索引，此时建立的索引就称之为前缀索引，注意前缀索引需要计算不重复率；

---

• **辅助索引：**辅助索引，顾名思义就是辅助我们查询的所有，所有的二级索引都成为辅助索引；

---

• **索引的分类：**索引的类型有非常非常多，我们刚刚也举例了，但我们可以按照不同的角度去看待索引，把索引归类好，其实索引也不难记住；一般划分为：数据结构角度、物理存储角度、逻辑角度、业务角度等；

缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

注意事项

使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6.不使用not in和<>操作

基于innodb的索引问题

索引维护

什么是索引维护 ?

• B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护

• 所以推荐使用自增主键，就可以保证新的ID一定是在叶子节点最右边，不会影响前面的数据

• 以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置

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

• 页分裂操作还会影响数据页的利用率

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

需要逻辑上挪动后面的数据 ? 这句话是什么意思 ?

• 所谓的逻辑上挪动，就是指逻辑删除
• 很多数据表删除数据都是逻辑删除而非物理删除
• Innodb 存储引擎下，所有表都是这样的，当删除的记录达到页体积的百分之50才会尝试页合并
• 逻辑删除是指只是删除了指向这片内存的指针，也就是说，CPU无法通过原来的指针索引到这片内存了(并且操作系统已经认为这片内存已经被释放了)
• 其实，这片内存上还是有值的，就是你原来程序给这片内存赋的值，但是操作系统会认为这是一片没有数据的空闲内存

哪些场景下应该使用自增主键 ?

• 自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： not null primary key auto_increment
• 插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
• 每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
• 如果主键自增的话，MySQL 在写满一个数据页的时候，就会直接申请另一个新的数据页接着写就可以了 ！**旧数据页的数据不分离！不存在分页！**
• 从性能和存储空间方面考量，自增主键往往是更合理的选择

哪些场景下不应该使用自增主键 ?

• 一般不用业务字段做主键
• 业务字段不一定是递增的，有可能会造成主键索引的页分裂 (往中间的地方插入)，导致性能不稳定
• 二级索引存储的值是主键，如果使用业务字段占用大小不好控制，如果业务字段过长可能会导致二级索引占用空间过大，利用率不高。

什么场景下适合用业务字段直接做主键呢 ?

1. 只有一个索引
2. 该索引必须是唯一索引

• 这就是典型的 KV 场景
  • key value场景就是存在业务唯一字段列，然后整行数据相当于value
• 由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题
• 这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树

6.索引失效的问题

索引匹配规则

全值匹配

全值匹配是针对建立了复合索引的索引列，在查询条件中，复合索引的所有列都指定具体的列

• 创建复合索引：

create index idx on userinfo(username,age,phone,gender);

• 执行全值匹配SQL：

explain select * from userinfo where username='1' and age=1 and phone='1' and gender=1 ;

命中索引idx_name_age_phone_gender

全值匹配的概念很简单，在复合索引中，必须匹配复合索引的所有字段，索引才会生效。

最左前缀法则

复合属于匹配规则可不止全值匹配一种，使用索引检索时间时，如果复合最左前缀法则，那么也是可以命中索引的。

最左前缀法则指的是，查询条件中如果包含有复合索引，必须从索引的最左列开始查询。并且不跳过中间列。

最左匹配原则，必须要匹配最左的索引列，不可跳过最左边索引列

-- 不走索引
explain select * from userinfo where phone='1' and age=20 and gender='1';		

-- 不走索引
explain select * from userinfo where gender='1' and age=20 and phone='1';		

-- 不走索引
explain select * from userinfo where phone='1' and gender=20 and age='1';

-- 命中1个字段(username)
explain select * from userinfo where username='1' and phone='1' and gender='1';

-- 命中2个字段(username、age)
explain select * from userinfo where username='1' and age=1 and gender='1';

-- 命中3个字段(username、age、phone)
explain select * from userinfo where username='1' and age=20 and phone='1';	

注意：最左前缀法则指的是条件匹配时，必须匹配上创建索引时最左边的列，跟where指定的条件顺序无关！这里是很多人入的坑！

• 如果四个值都匹配了，既符合最左前缀法则，也符合全值匹配

-- 全部命中
explain select * from userinfo where username='1' and age=1 and phone='1' and gender='1';

索引失效问题

(1条消息) Mysql：索引失效的几种可能原因_蝶开三月的博客-CSDN博客_mysql 索引失效的原因有哪些

单列索引失效

• 创建单列索引：

create index idx_name on userinfo(username);

不要在索引列上做任何操作

1）不要在索引列上做任何的操作，否则索引将失效

where 子句里对索引列上有数学运算或者函数，会导致索引失效而转向全表扫描

explain select * from userinfo where username='1';		-- 走索引

explain select * from userinfo where concat(username,'1')='1';	-- 不走索引

字符串索引必须加引号

2）字符串类索引在条件查询时，条件值没有加双引号，索引失效

存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

explain select * from userinfo where username='1';			-- 走索引

explain select * from userinfo where username=1;			-- 不走索引

不要使用!=或者<>

3）使用不等于（!= 或者<>）不会命中索引

explain select * from userinfo where username='1';			-- 走索引

explain select * from userinfo where username!='1';			-- 不走索引

模糊查询时不要使用前通配符匹配

4）like，前通配符会导致索引失效，没有使用通配符或者使用后通配符会使用索引，但是会造成回表

explain select * from userinfo where username like '%1%';		-- 不走索引

explain select * from userinfo where username like '%1';		-- 不走索引

explain select * from userinfo where username like '1%';		-- 走索引,回表

explain select * from userinfo where username like '1';			-- 走索引,回表

Using index condition和NULL都是走了索引，但是会回表查询。

在使用like模糊查询的时候，前模糊查询会导致索引失效，因此一般需要频繁模糊查询并且数据量比较大的情况下，一般会取业务前缀；比如数据前面加某个前缀符。

explain select * from userinfo where username like 'T%1';

我们插入300W条数据，观察一下加前缀和未加前缀的效率：

is not null不会走索引

is null会走索引，is not null不会走索引。

explain select * from userinfo where username is null;

explain select * from userinfo where username is not null;

仔细一看两个都是命中了索引的。不是说is not null不会走索引吗？

注意：这里有个坑

执行上述的执行计划的时候userinfo表中的记录已经被情空了的！

如果userinfo表中有记录的情况下，又会出现不同的情况！

往下看：

call test_insert(20);	-- 往userinfo表中插入20条记录

explain select * from userinfo where username is null;		-- 走了索引

explain select * from userinfo where username is not null;	-- 根本没走索引！！！

当表中有记录时，is not null 会导致索引失效！这个可能是MySQL底层优化器的选择，我们记住is not null不会走索引即可；

not in会导致索引失效

使用in会走索引，not in则不会走索引。

-- 清空表
truncate userinfo;

-- in走索引
explain select * from userinfo where id in (1,2);

-- not int不走索引
explain select * from userinfo where id not in (1,2);

发现in走索引，not in没有索引，万事大吉，请你继续往下看！

注意：这里又有一个坑！！！！

请注意看我在上面的SQL语句故意加了一个清空表的操作，那是因为如果表中有数据，那么情况又不一样了！

-- 插入10条记录
call test_insert(10);

-- in走索引
explain select * from userinfo where id in (1,2);

-- 
explain select * from userinfo where id not in (1,2);

表中有数据时，发现not int语句又走了索引，这下有点晕了吧（这个有可能是MySQL执行计划的Bug），到这里咱们先这样认为：in 会走索引，not int 不走索引！具体咱们最后一章再测试。

or 导致复合索引失效

我们知道MySQL要想命中复合索引，必须按照最左前缀法则，回顾最左前缀法则：

create index idx_name_age_phone on userinfo(username,age,phone);	-- 创建复合索引
show index from userinfo; 				-- 查询当前索引有哪些

只有一个复合索引（username、age、phone）；

执行如下SQL语句，分析执行计划

explain select * from userinfo where username='1';				-- 命中一个索引

explain select * from userinfo where username='1' and age=1;	-- 命中两个索引

explain select * from userinfo where username='1' and phone='1';-- 命中一个索引(username)

explain select * from userinfo where age=1 and phone='1';		-- 不符合最左前缀法则

上述条件都是使用and拼接，如果一旦使用了or，索引会立即失效，不管是否符合最左前缀法则。也不管是否复合全值匹配

-- 符合最左前缀法则(不走索引)
explain select * from userinfo where username='1' or age=1;

-- 符合最左前缀法则也复合全值匹配(不走索引)
explain select * from userinfo where username='1' or age=1 or phone='1';

复合索引解决部分索引失效问题

当多个列都创建了索引（不是复合索引），同时使用这几个索引列查询时，MySQL只会选择其中一个索引去查询，查询到结果之后，根据另一个索引条件进行条件筛选。

• 给name、age列创建索引：

create index idx_name on userinfo(username);
create index idx_age on userinfo(age);

• 测试使用到多个单列索引：

explain select * from userinfo where username='1';				-- 走索引

explain select * from userinfo where age=1;						-- 走索引

-- 先走idx_name索引查询，然后根据age筛选剩余的数据(这个时候不走索引)
explain select * from userinfo where age=1 and username='1';	

其实这条SQL语句的效率也是非常高的，因为使用了idx_name索引先检索出来一部分数据，这个时候是非常快的，然后剩下的数据再通过age条件筛选，但是通过第一轮的筛选剩下来的数据已经非常少了。此时即使不通过索引筛选剩下的数据也是非常快的，除非第一轮筛选过后的的数据量还是非常多（十万、百万），那么第二次不使用索引筛选效率就会比较低了。

针对于上述情况，我们可以根据username和age两列建立复合索引，来解决这个问题。

建立复合索引之前删除前面的两个索引（不删除不行，因为idx_name索引先创建的，如果不删那么会默认选择idx_name索引来查询数据，而不会使用后面创建的复合索引）：

drop index idx_name on userinfo;

drop index idx_age on userinfo;

• 建立复合索引：

create index idx_name_age on userinfo(username,age);

• 执行SQL：

explain select * from userinfo where age=1 and username='1';

发现走了两个索引

诡异的or语句

首先说明一下：or不会导致索引失效

执行SQL：

truncate userinfo;

explain select * from userinfo where username='1' or username='2';

发现没有走索引

我们在表中插入10条数据测试一下：

call test_insert(10);

explain select * from userinfo where username='1' or username='2';

此时发现又走了索引。那or到底是走索引还是不走呢？答案是走索引的，具体我们看下一篇测试。

索引性能测试

咱们前面测试时发现有如下问题：

• 1）is not null：表中没有数据时走索引，有数据时不走索引
• 2）not in：表中没有数据时不走索引，走索引
• 3）or：表中没有数据时不走索引，有数据时走索引

实际上是is not null不会走索引，not in不会走索引，or拼接会走索引

is null走索引，is not null不走索引

之前,在表中没有数据的情况下，is not null 会走索引，一旦表中有数据了，is not null则不走索引，实际是不走索引的。

我们来实际测试一下，看SQL语句的执行时间

在表中准备300W数据，测试如下SQL语句：

select * from userinfo where username is null;				-- 0.00s

select * from userinfo where username is not null;			-- 6.76s（但凡使用到

in走索引，not in不走索引

在1.3.1.6小节中，在表中没有数据的情况下，not in 不走索引，有数据的情况下，走索引（和is not null 那边刚好相反）。实际是不走索引。

测试如下SQL（表中有300W记录）：

select * from userinfo where id in (1,2);			-- 0.00s

select * from userinfo where id not in (1,2);		-- 6.72s（但凡使用到了索引也不可能这么慢）

由于条件会查询出来结果集，不方便截图

or条件拼接不会导致索引失效（单列索引）

在1.3.2小节中，在表中没有数据的情况下不走索引，有数据的情况下走索引，**实际是走索引的。**要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
测试如下SQL语句（表中有300W记录）：

-- 创建索引
create index idx_name on userinfo(username);

-- 查看userinfo表中有多少个索引
show index from userinfo;

-- 查看此SQL的执行效率（有索引的情况下）
select * from userinfo where username='1' or username='2';		-- 0.00s

-- 删除索引
drop index idx_name on userinfo;

-- 查看此SQL的执行效率（没有索引的情况下）
select * from userinfo where username='1' or username='2';		-- 2.61s

总结

到这里差不多要结束了，关于索引失效的问题。主要有这几点，要大家牢记！：

• 复合索引：

1）记住全值匹配

2）牢记最左前缀法则

3）尽量避免or带来的复合索引失效

• 单列索引：

1）不要在索引列上做任何操作

2）字符串索引必须加引号

3）不要使用!=或者<>

4）模糊查询时不要使用前通配符匹配

5）is not null不会走索引

6）not in会导致索引失效

大家以后编写SQL的时候牢记这3+6法则！

5.当前读和快照读

• 当前读
  像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
• 快照读
  像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。在默认隔离级别下，select 语句默认是快照读，mysql默认隔离级别是RR（可重复读）

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

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

4.优化

执行计划

https://www.cnblogs.com/yinjw/p/11864477.html

https://blog.csdn.net/wuseyukui/article/details/71512793

1.mysql语句执行顺序

select 考生姓名, max(总成绩) as max总成绩 
 
from tb_Grade 
 
where 考生姓名 is not null 
 
group by 考生姓名 
 
having max(总成绩) > 600 
 
order by max总成绩 

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为 止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

mysql整体的执行过程如下图所示：

mysql执行过程以及顺序 - Yrion - 博客园 (cnblogs.com)

(1条消息) Mysql查询语句执行过程及运行原理_sinat_32176267的博客-CSDN博客_mysql查询语句

order by是怎么工作的？

全字段排序

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

• 假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄

select city,name,age from t where city='杭州' order by name limit 1000  ;

city 索引示意图

满足 city='杭州’条件的行，是从 ID_X 到 ID_(X+N) 的这些记录

这个语句的执行流程：

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
7. 按照排序结果取前 1000 行返回给客户端。

图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size

• sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小
• 如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成
• 如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序 (归并排序)

为什么要使用 外部排序 ?

• 一般是待排序数据量很大，在内存中放不下，不能使用内存排序算法
• 外部排序，常见为多路归并排序算法
  • 分隔： (分治思想) 分隔为多个外部子文件，单个子文件大小小于排序可用的内存大小，即子文件可以加载入内存
  • 内存排序： 对每个子文件进行内存排序
  • 归并： （多路）归并已有序的子文件，最终形成一个完整的有序文件
  • 如归并有序子文件 X 和 有序子文件 Y 为文件Z的过程：从 X 读取最小元素 x1 至内存，从 Y 读取最小元素 y1 至内存
  • 比较 x1 和 y1 ，将两者中较小的写入文件Z。依次重复此过程，直至 X、Y 文件中的元素都进入 Z 中

关于 OPTIMIZER_TRACE

• 查询 OPTIMIZER_TRACE 这个表时，需要用到临时表
• 如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1
• 如果是 MyIsAm 引擎则不会

全字段排序的缺点：

查询的数据如果单行太大的话，内存里能够同时放下的行数就会很少，要分成很多个临时文件，排序的性能会很差

rowid 排序

决定是否更改查询时所使用的算法

SET max_length_for_sort_data = 长度;

• max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数
• 它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法

rowid 排序执行流程：

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

注意：

• rowid 方式和全字段方式一样，需要先把查询到的结果全部放在内存或硬盘中，再使用相关算法进行排序
• 而排序后由于没有保存所需的字段，需要按顺序使用主键再从索引树上查询，查到一个就返回一个，而不用把所有内容查完放到内存上再一并返回

全字段排序 VS rowid 排序

• 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据

• 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据

• 如果内存够，就要多利用内存，尽量减少磁盘访问

• 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择

关于 explain 查询语句里面的 Extra 字段

• Using filesort：本次查询语句中有 order by，且排序依照的字段不在本次使用的索引中，不能自然有序，需要进行额外的排序工作
• Using index：使用了覆盖索引，即本次查询所需的所有信息字段都可以从利用的索引上取得。无需回表，额外去主索引上取数据
• Using index condition：使用了索引下推技术 ICP。（虽然本次查询所需的数据，不能从利用的索引上完全取得，还是需要回表去主索引获取。但在回表前，充分利用索引中的字段，根据 where 条件进行过滤。提前排除了不符合查询条件的列。这样就减少了回表的次数，提高了效率。）
• Using where：表示本次查询要进行筛选过滤

2.预编译

通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：

1. 词法和语义解析
2. 优化sql语句，制定执行计划
3. 执行并返回结果

我们把这种普通语句称作Immediate Statements。

但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。
如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。

所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements
预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。
当然就优化来说，很多时候最优的执行计划不是光靠知道sql语句的模板就能决定了，往往就是需要通过具体值来预估出成本代价。

通常我们发送一条SQL语句给MySQL服务器时，MySQL服务器每次都需要对这条SQL语句进行校验、解析等操作。

但是有很多情况下，我们的一条SQL语句可能需要反复的执行，而SQL语句也只可能传递的参数不一样，类似于这样的SQL语句如果每次都需要进行校验、解析等操作，未免太过于浪费性能了，因此我们提出了SQL语句的预编译。

所谓预编译就是将一些灵活的参数值以占位符?的形式给代替掉，我们把参数值给抽取出来，把SQL语句进行模板化。让MySQL服务器执行相同的SQL语句时，不需要在校验、解析SQL语句上面花费重复的时间

MySQL预编译的语法

• 定义预编译SQL语句：

-- 定义一个预编译语句
prepare name from statement; 

prepare statement_1 from 'select * from user where id=?';

• 设置参数值：

set @id=1;

• 执行预编译SQL语句：

execute statement_1 using @id;

• 释放预编译SQL语句：

deallocate prepare statement_1;

使用PreparedStatement进行预编译

开启预编译功能

PreparedStatement的预编译功能默认是关闭的，要让其生效，必须在JDBC连接的URL设置useServerPrepStmts=true，让其打开。

• 如下所示：

jdbc:mysql://localhost:3306/mybatis?&useServerPrepStmts=true

• 测试代码：

package com.lscl.test;
import org.junit.Test;
import java.sql.*;

public class Demo01 {
    @Test
    public void test1() throws Exception {

        // 获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true", "root", "admin");

        String sql = "select * from user where id = ?";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setInt(1, 1);

        // 执行查询,获取结果集
        ResultSet rs = ps.executeQuery();

        //遍历查询结果集
        while (rs.next()) {
            System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
        }
        rs.close();
        ps.close();

    }
}

• 查看MySQL的查询日志：

我们设置的是MySQL连接参数，目的是告诉MySQL JDBC的PreparedStatement使用预编译功能（5.0.5之后的JDBC驱动版本需要手动开启，而之前的默认是开启的）

cachePrepStmts参数

当使用不同的PreparedStatement对象来执行相同的SQL语句时，还是会出现编译两次的现象，我们可以开启"预编译缓存"，来实现"一次编译，到处运行"（要是同一个Connection）

开启预编译缓存：cachePrepStmts=true;

• url连接：
  jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true
• 测试代码（没有开启缓存）：

@Test
public void test1() throws Exception {

    // 获取连接
//        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true", "root", "admin");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true", "root", "admin");

    String sql = "select * from user where id = ?";

    PreparedStatement ps = connection.prepareStatement(sql);

    ps.setInt(1, 1);

    // 执行查询,获取结果集
    ResultSet rs = ps.executeQuery();

    //遍历查询结果集
    while (rs.next()) {
        System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
    }

    // 关闭对象连接
    rs.close();
    ps.close();

    ps = connection.prepareStatement(sql);

    ps.setInt(1, 1);

    // 执行查询,获取结果集
    rs = ps.executeQuery();

    //遍历查询结果集
    while (rs.next()) {
        System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
    }

    rs.close();
    ps.close();

}

• 开启预编译缓存测试（在url连接上加上cachePrepStmts=true）：

jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true

Statement是否具备预编译功能？

Statement不具备预编译功能

@Test
public void test2() throws Exception {

    // 获取连接
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useServerPrepStmts=true&cachePrepStmts=true", "root", "admin");

    String sql = "select * from user where id = 1";

    Statement statement = connection.createStatement();

    // 执行查询,获取结果集
    ResultSet rs = statement.executeQuery(sql);

    //遍历查询结果集
    while (rs.next()) {
        System.out.println(rs.getObject("id")+"---"+rs.getObject("username"));
    }

    rs.close();
    statement.close();
}

• 查看MySQL查询日志：

总结

1）到了这里，大家应该知道什么是预编译了，预编译是用来提升SQL语句的响应速度的，将一段SQL语句定制成模板，把灵活的参数作为占位符让我们传递进去，达到多次执行相同的SQL语句必须要重复校验、解析等操作；

2）默认的情况下，PreparedStatement是没有开启预编译的，需要我们在连接的url参数上指定useServerPrepStmts=true参数开启，并且预编译是支持"缓存"的，我们可以通过参数cachePrepStmts=true来设置；

3）statement是不支持预编译的，即使设置了useServerPrepStmts=true也不管用；

3.SQL语句优化

• 创建一张测试表，用于后期测试使用：

CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int(3) NULL DEFAULT NULL COMMENT '年龄',
  `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男   ‘1’-女',
  `desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍',
  `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
  `login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间',
  `pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址',
  `look` int(10) NULL DEFAULT NULL COMMENT '查看数',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

1.排序优化

索引优化

在排序时，尽量使用索引字段进行排序，否则会采用文件排序（filesort），效率低。

• 根据普通字段排序：

show index from userinfo;								-- 查询当前表有多少索引

explain select * from userinfo order by username;		-- 根据普通字段排序

• 给排序字段创建索引：

create index idx_name on userinfo(username);							-- 创建索引

explain select * from userinfo order by username;						-- filesort

explain select username from userinfo order by username;				-- Using index

explain select username,age from userinfo order by username;			-- filesort

根据索引字段排序，之后查询的数据必须是索引数上的数据，不可查询额外字段，更不能查询全表字段

其实这一段算是废话来着，谁不知道使用索引能够加快速度啊！还用你说？？？

好吧，其实这里只想跟你说一个东西：文件排序。

就是上面看到的Using filesort，其实filesort并不代表MySQL使用到了文件进行排序，文件排序只是一种算法，我们习惯把它翻译成"文件排序"而已。一旦看到了filesort，那么就意味着效率很低了。

算法优化

MySQL在排序时，如果不能够借助索引直接完成排序，那么将会使用文件排序（filesort）。如果使用了filesort，那么MySQL会将数据在内存中进行排序，排序内存由系统变量sort_buffer_size控制。默认为256KB。

注意，排序缓冲区是每个线程是独享的。因此设置太大在并发量高的情况下会消耗MySQL服务器大量内存。

MySQL的排序算法分为两种：

• 1）多扫描排序：首先根据排序条件取出排序字段的行指针信息，然后在排序缓冲区（sort_buffer_size）中进行排序。排序完毕之后会根据排序缓冲区中的行指针回表查询。操作磁盘次数多（两次），效率较低。

• 2）单扫描排序：根据条件取出所有字段的信息（不仅仅是排序字段），然后在排序缓冲区中进行排序，排序完毕之后直接将结果集返回。这一步对排序缓冲区要求比较大，但排序效率高。

1、如果排序缓冲区大小不足，那么则会采用临时表（temporary table）存储排序结果。之后临时表的行指针信息重新回表查询记录。效率低

2、每个线程都有自己独自的排序缓冲区，如果排序缓冲区设置过大，会浪费内存。

因此sort_buffer_size的大小根据你们需要排序的数据大小来决定。太大浪费内存，太小造成临时表的使用，降低效率。

那MySQL到底采用哪种排序算法？

MySQL4.1版本之前只有多扫描排序算法，单扫描排序是MySQL4.1版本推出的新排序算法，用于优化多扫描排序。MySQL主要根据系统变量max_length_for_sort_data的大小和此次Query语句所取出的所有字段大小之后对比，如果max_length_for_sort_data大，则使用单扫描排序，反之使用多扫描排序。

• 查询max_length_for_sort_data的默认值：

select @@max_length_for_sort_data;

max_length_for_sort_data默认1KB。

排序优化建议

如果是排序场景很多，且每次排序的数据量大。建议使用单排序，即max_length_for_sort_data调的尽可能大，并且保证每次排序的数据都在sort_buffer_size大小之内。

2.分组优化

在MySQL中group by语句会触发一次默认的order by排序操作，造成不必要的性能浪费。我们可以手动的禁止分组操作带来的排序操作。

explain select avg(age),c_id from student group by c_id;

explain select avg(age),c_id from student group by c_id order by null;

发现每次的group by 分组操作都会触发一次默认的排序操作，如果我们没有这样的需求，无疑是增加了SQL语句的响应时间

3.分页优化

通常使用分页查询是来提高我们的查询效率的，因为通常用户不希望一下子查询到那么多的数据，一般是查询前几条数据，此时的分页效率会比较高。但是有时候在分页查询会遇到一个比较尴尬的问题，那就是limit N,10，即前面跳过N多条记录，只查询N多条记录的后面几条记录。如果N的值非常大，那么效率必然就会很低。

• 此时数据库有300W记录：

• 假设我们需要查询2900000-2900010记录：

从执行计划可以看出，进行了全表扫描

花费的时间为：

显然，效率非常低。

分页优化一

我们可以借助索引，在索引上面排序，然后通过索引关联表查询。

分页优化二

如果id是顺序排列的话，我们可以先根据id进行排序，然后取后面10条。

查看执行消耗时间：

4.表优化

数据空洞

当我们对数据库表进行删除（delete）时，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。而这些被标记的行就是数据空洞。

我们进入/var/lib/mysql/${db_name}目录下查看当前数据库的一些磁盘存储信息。

300W记录大概花了536M的空间。

• 我们删除200W数据再次查看磁盘占用：

start transaction;							-- 控制在一个事务中(效率高)

delete from userinfo where id > 1000000;

commit;

再次查看磁盘占用空间，发现还是536M。并没有释放空间。

• 查看表的状态信息：

mysql> show table status like 'userinfo'\G;
*************************** 1. row ***************************
           Name: userinfo
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1110488
 Avg_row_length: 492
    Data_length: 547356672
Max_data_length: 0
   Index_length: 0
      Data_free: 99614720
 Auto_increment: NULL
    Create_time: 2020-06-04 20:28:45
    Update_time: 2020-06-04 20:32:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment: 
1 row in set (0.00 sec)

其中Data_free就是碎片（空洞）的数量

optimize优化表

当表中存在有大量的数据空洞时，我们可以使用optimize命令来优化表。即删除数据空洞。

InnoDB表优化

InnoDB表优化后会做一个重新构建索引+分析的一个过程。因为我们知道InnoDB引擎将数据与索引是放在一个文件中的，名为.idb，对数据进行整理后，必定会对索引造成影响。而MyISAM是将数据与索引文件分开存储的（.MYD、.MYI），因此MyISAM整理空洞不会对索引造成影响。

• 执行表优化操作：

optimize table userinfo;

InnoDB表优化后，还会对表中的索引进行重构分析。因此在同等数量的碎片整理，InnoDB花费时间比Myisam表要多一点。

• 再次查看表，发现占用磁盘空间变小。

MyISAM表优化

• 删除200W数据：

delete from userinfo_myisam where id>1000000;

• 执行表优化：

optimize table userinfo_myisam;

花费2.65s，比InnoDB效率高，因为InnoDB需要重新构建索引。

再次查看表磁盘占用情况，发现占用空间减少。

总结

总结本篇文章的重要几点：

• 1、牢记排序优化的两个参数：sort_buffer_size、max_length_for_sort_data。
• 2、分组操作默认会做一次排序操作，如果没有这个需求建议关闭（order by null）。
• 3、分页优化参考具体SQL语句。
• 4、表空洞优化

SQL优化很多方面是索引、锁、参数调优方面的优化，其中锁和索引的优化可以明显的提高SQL语句的查询速度，参数方面的优化则可以帮助我们搭建一个稳定的、高性能、高可用的MySQL集群架构，由于索引、锁、参数调优方面牵扯知识太广，本篇不做讲解。

幻读的问题

MySQL 是如何解决幻读的？ - 知乎 (zhihu.com)

面试：

索引问题

索引失效问题

mysql IS NULL使用索引案例讲解_Mysql_脚本之家 (jb51.net)

mysql的sql查询语句中使用is null、is not null、!=对索引并没有任何影响，并不会因为where条件中使用了is null、is not null、!=这些判断条件导致索引失效而全表扫描。

事实上，导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。

通过解析工具数据mysql的索引结构可以发现，null值也被储存到了索引树中，并且null值被处理成最小的值放在index_name索引树的最左侧。

为何大众误解认为is null、is not null、!=这些判断条件会导致索引失效而全表扫描呢？

导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值可以简单得记忆为20%左右。

详细的分析过程可以见笔者的另一篇博客：mysql回表致索引失效。

也就是如果一条查询语句导致的回表范围超过全部记录的20%，则会出现索引失效的问题。而is null、is not null、!=这些判断条件经常会出现在这些回表范围很大的场景，然后被人误解为是这些判断条件导致的索引失效。

此时user_info表中一共有1003条记录，其中只有1条记录的name值为null。那么is null判断语句导致的回表记录只有1/1003不会超过临界值，而is not null判断语句导致的回表记录有1002/1003远远超过临界值，将出现索引失效的现象。

mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下，需要做回表操作获取记录的所需字段。

mysql执行sql前会执行sql优化、索引选择等操作，mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价，从中选择一个mysql认为代价最小的方式进行sql查询操作。而在回表数据量比较大时，经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况。

• 如何字段类型是字符串，where时一定用引号括起来，否则索引失效

  数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

  最左匹配，组合索引，不是使用第一列索引，索引失效。

  如果索引有多列，要遵守最左前缀法则
  即查询从索引的最左前列开始并且不跳过索引中的列
  

  全值匹配是针对建立了复合索引的索引列，在查询条件中，复合索引的所有列都指定具体的列

• 在索引列上使用mysql的内置函数，对索引列进行计算操作，索引失效。

• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

  in会走索引。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

  mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）

  in通常是走索引的，当in后面的数据在数据表中超过30%(上面的例子的匹配数据大约6000/16000 = 37.5%)的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系。

  in 用不用索引_Mysql中in到底走不走索引？_weixin_39524574的博客-CSDN博客

• 索引字段上使用is null， is not null，可能导致索引失效。

  在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！！！

  结论：为指定字段设置了非空(not null)，在使用is null或is not null时是不走索引的。而列定义允许为空，查询中也能使用到索引的。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

• mysql估计使用全表扫描要比使用索引快,则不使用索引。

• 查询条件包含or，可能导致索引失效

  复合索引会失效，单列索引不会。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

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

  or的使用需要具体分析

• like通配符可能导致索引失效。

  like，前通配符会导致索引失效，没有使用通配符或者使用后通配符会使用索引，但是会造成回表；如果想让以‘%’开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列：

  优化

  • 使用覆盖索引
  • 把%放后面

  附: 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

到这里差不多要结束了，关于索引失效的问题。主要有这几点，要大家牢记！：

• 复合索引：

1）记住全值匹配

2）牢记最左前缀法则

3）尽量避免or带来的复合索引失效

• 单列索引：

1）不要在索引列上做任何操作

2）字符串索引必须加引号

3）不要使用!=或者<>

4）模糊查询时不要使用前通配符匹配

5）is not null不会走索引

6）not in会导致索引失效

索引不适合哪些场景

• 数据量少的不适合加索引
• 更新比较频繁的也不适合加索引
• 区分度低的字段不适合加索引（如性别）

索引的一些潜规则

• 覆盖索引

  尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select * 这种全字段查询（会增大数据库压力和io开销）

• 回表

• 索引数据结构（B+树）

• 最左前缀原则

• 索引下推

索引下推

五分钟搞懂MySQL索引下推 (baidu.com)

查询语句加锁的情况

快照读, 读取专门的快照 (对于RC，快照（ReadView）会在每个语句中创建。对于RR，快照是在事务启动时创建的)
简单的select操作即可
针对的也是select操作
当前读, 读取最新版本的记录, 没有快照。 在InnoDB中，当前读取根本不会创建任何快照。
select … lock in share mode 读锁
select … for update 写锁，是临键锁（加锁规则）
insert
update
delete
针对如下操作, 会让如下操作阻塞:
insert
update
delete
在RR(可重复读)级别下, 快照读是通过MVVC(多版本控制)和undo log来实现的,
当前读是通过手动加record lock(记录锁)和gap lock(间隙锁)来实现的。所以从上面的显示来看，如果需要实时显示数据，还是需要通过加锁来实现。这个时候会使用next-key技术来实现。

小表驱动大表

Mysql之小表驱动大表_七国的天下，我要九十九的博客-CSDN博客_小表驱动大表

自增主键

• 使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID哈
• 自增主键会产生表锁，从而引发问题
• 自增主键可能用完问题。

在mysql，int整型的范围如下int的取值范围为：-2^31——231-1，即-2147483648—2147483647

mysql数据库表的自增 ID 达到上限之后，这时候再申请它的值就不会再改变了，如果继续插入数据就会导致报主键冲突异常。

建议采用bigint unsigned，这个数字就大了。那有什么办法解决，答案是肯定的，解决方法也是很简单的，将Int类型改为BigInt类型，BigInt的范围如下

-2^63-1到263-1

主从同步延迟解决

MySQL主从同步延迟原因与解决方案_hsaxj的博客-CSDN博客_mysql主从同步延迟怎么解决

MySQL 主从同步延迟的原因及解决办法 - 简书 (jianshu.com)

分库分表

深度好文：全面认识MySQL分库分表篇 (baidu.com)

MySQL分库分表_Affinor的博客-CSDN博客_mysql分库分表

https://baijiahao.baidu.com/s?id=1714241516279402586&wfr=spider&for=pc

=,size_16,color_FFFFFF,t_70)

花费2.65s，比InnoDB效率高，因为InnoDB需要重新构建索引。

再次查看表磁盘占用情况，发现占用空间减少。

总结

总结本篇文章的重要几点：

• 1、牢记排序优化的两个参数：sort_buffer_size、max_length_for_sort_data。
• 2、分组操作默认会做一次排序操作，如果没有这个需求建议关闭（order by null）。
• 3、分页优化参考具体SQL语句。
• 4、表空洞优化

SQL优化很多方面是索引、锁、参数调优方面的优化，其中锁和索引的优化可以明显的提高SQL语句的查询速度，参数方面的优化则可以帮助我们搭建一个稳定的、高性能、高可用的MySQL集群架构，由于索引、锁、参数调优方面牵扯知识太广，本篇不做讲解。

幻读的问题

MySQL 是如何解决幻读的？ - 知乎 (zhihu.com)

面试：

索引问题

索引失效问题

mysql IS NULL使用索引案例讲解_Mysql_脚本之家 (jb51.net)

mysql的sql查询语句中使用is null、is not null、!=对索引并没有任何影响，并不会因为where条件中使用了is null、is not null、!=这些判断条件导致索引失效而全表扫描。

事实上，导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。

通过解析工具数据mysql的索引结构可以发现，null值也被储存到了索引树中，并且null值被处理成最小的值放在index_name索引树的最左侧。

为何大众误解认为is null、is not null、!=这些判断条件会导致索引失效而全表扫描呢？

导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值可以简单得记忆为20%左右。

详细的分析过程可以见笔者的另一篇博客：mysql回表致索引失效。

也就是如果一条查询语句导致的回表范围超过全部记录的20%，则会出现索引失效的问题。而is null、is not null、!=这些判断条件经常会出现在这些回表范围很大的场景，然后被人误解为是这些判断条件导致的索引失效。

此时user_info表中一共有1003条记录，其中只有1条记录的name值为null。那么is null判断语句导致的回表记录只有1/1003不会超过临界值，而is not null判断语句导致的回表记录有1002/1003远远超过临界值，将出现索引失效的现象。

mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下，需要做回表操作获取记录的所需字段。

mysql执行sql前会执行sql优化、索引选择等操作，mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价，从中选择一个mysql认为代价最小的方式进行sql查询操作。而在回表数据量比较大时，经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况。

• 如何字段类型是字符串，where时一定用引号括起来，否则索引失效

  数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

  最左匹配，组合索引，不是使用第一列索引，索引失效。

  如果索引有多列，要遵守最左前缀法则
  即查询从索引的最左前列开始并且不跳过索引中的列
  

  全值匹配是针对建立了复合索引的索引列，在查询条件中，复合索引的所有列都指定具体的列

• 在索引列上使用mysql的内置函数，对索引列进行计算操作，索引失效。

• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

  in会走索引。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

  mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）

  in通常是走索引的，当in后面的数据在数据表中超过30%(上面的例子的匹配数据大约6000/16000 = 37.5%)的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系。

  in 用不用索引_Mysql中in到底走不走索引？_weixin_39524574的博客-CSDN博客

• 索引字段上使用is null， is not null，可能导致索引失效。

  在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！！！

  结论：为指定字段设置了非空(not null)，在使用is null或is not null时是不走索引的。而列定义允许为空，查询中也能使用到索引的。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

• mysql估计使用全表扫描要比使用索引快,则不使用索引。

• 查询条件包含or，可能导致索引失效

  复合索引会失效，单列索引不会。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

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

  or的使用需要具体分析

• like通配符可能导致索引失效。

  like，前通配符会导致索引失效，没有使用通配符或者使用后通配符会使用索引，但是会造成回表；如果想让以‘%’开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列：

  优化

  • 使用覆盖索引
  • 把%放后面

  附: 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

到这里差不多要结束了，关于索引失效的问题。主要有这几点，要大家牢记！：

• 复合索引：

1）记住全值匹配

2）牢记最左前缀法则

3）尽量避免or带来的复合索引失效

• 单列索引：

1）不要在索引列上做任何操作

2）字符串索引必须加引号

3）不要使用!=或者<>

4）模糊查询时不要使用前通配符匹配

5）is not null不会走索引

6）not in会导致索引失效

索引不适合哪些场景

• 数据量少的不适合加索引
• 更新比较频繁的也不适合加索引
• 区分度低的字段不适合加索引（如性别）

索引的一些潜规则

• 覆盖索引

  尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select * 这种全字段查询（会增大数据库压力和io开销）

• 回表

• 索引数据结构（B+树）

• 最左前缀原则

• 索引下推

[外链图片转存中…(img-aJ5o275a-1651626535266)]

[外链图片转存中…(img-QZl5Yjxr-1651626535266)]

索引下推

五分钟搞懂MySQL索引下推 (baidu.com)

查询语句加锁的情况

快照读, 读取专门的快照 (对于RC，快照（ReadView）会在每个语句中创建。对于RR，快照是在事务启动时创建的)
简单的select操作即可
针对的也是select操作
当前读, 读取最新版本的记录, 没有快照。 在InnoDB中，当前读取根本不会创建任何快照。
select … lock in share mode 读锁
select … for update 写锁，是临键锁（加锁规则）
insert
update
delete
针对如下操作, 会让如下操作阻塞:
insert
update
delete
在RR(可重复读)级别下, 快照读是通过MVVC(多版本控制)和undo log来实现的,
当前读是通过手动加record lock(记录锁)和gap lock(间隙锁)来实现的。所以从上面的显示来看，如果需要实时显示数据，还是需要通过加锁来实现。这个时候会使用next-key技术来实现。

小表驱动大表

Mysql之小表驱动大表_七国的天下，我要九十九的博客-CSDN博客_小表驱动大表

自增主键

• 使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID哈
• 自增主键会产生表锁，从而引发问题
• 自增主键可能用完问题。

在mysql，int整型的范围如下int的取值范围为：-2^31——231-1，即-2147483648—2147483647

mysql数据库表的自增 ID 达到上限之后，这时候再申请它的值就不会再改变了，如果继续插入数据就会导致报主键冲突异常。

建议采用bigint unsigned，这个数字就大了。那有什么办法解决，答案是肯定的，解决方法也是很简单的，将Int类型改为BigInt类型，BigInt的范围如下

-2^63-1到263-1

主从同步延迟解决

MySQL主从同步延迟原因与解决方案_hsaxj的博客-CSDN博客_mysql主从同步延迟怎么解决

MySQL 主从同步延迟的原因及解决办法 - 简书 (jianshu.com)

分库分表

深度好文：全面认识MySQL分库分表篇 (baidu.com)

MySQL分库分表_Affinor的博客-CSDN博客_mysql分库分表

https://baijiahao.baidu.com/s?id=1714241516279402586&wfr=spider&for=pc