Java面试准备（五）——MySQL_myisam 引擎和 innodb 引擎都是使用 b+tree 作为索引结构,但是两者的实现方式不太-优快云博客

本文链接：https://blog.youkuaiyun.com/NA_imu/article/details/127959030

MySQL

一、MySQL理解
二、MySQL数据类型
三、MySQL存储引擎
四、MySQL索引
五、MySQL事务
六、MySQL锁
七、SQL性能分析和优化
八、分库分表

一、MySQL理解

1. MySQL基础架构

MySQL 主要由下面几部分构成：
- 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
- 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
- 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
- 优化器： 按照 MySQL 认为最优的方案去执行。
- 执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
- 插件式存储引擎 ：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。
简单来说 MySQL 主要分为 Server 层和存储引擎层：
- Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
- 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。**现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。
Server层基本组件介绍
1. 连接器
- 连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。
- 主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据。即后续只要这个连接不断开，即使管理员修改了该用户的权限，该用户也不受影响。
1. 查询缓存(MySQL 8.0 版本后移除)
- 查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
- 连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 SQL 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。
- 当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。
- MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。
  
  所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。
- MySQL 8.0 版本后删除了缓存的功能。
1. 分析器
MySQL 没有命中缓存，就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器分为几步：

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

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

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。
1. 优化器
- 优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
- 可以说，经过了优化器之后这个语句具体该如何执行就已经定下来。
1. 执行器
- 当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果

2. SQL语句在MySQL中的执行过程

1）查询语句

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案，上面的 SQL 语句，可以有两种执行方案：

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

那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
进行权限校验，如果没有权限就会返回错误信息，如果有权限执行器就会调用数据库引擎接口，返回引擎的执行结果。

2）更新语句

update tb_student A set A.age='19' where A.name=' 张三 ';

执行更新还要记录日志，这就会引入日志模块，MySQL 自带的日志模块是 binlog（归档日志）, 所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志）

先查询到张三这一条数据，如果有缓存，也是会用到缓存。
然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
更新完成。

二、MySQL数据类型

1. 整型

1）TINYINT

2）SMALLINT

3）MEDIUMINT

4）INT

5）BIGINT

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。
INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的

2. 浮点数

1）FLOAT

2）DOUBLE

3）DECIMAL

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。
FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

3. 字符串

1）CHAR

2）VARCHAR

CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。
VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。

但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

3）BLOB

4）TEXT

BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。

4. 时间和日期

1）DATETIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。
它与时区无关。
默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

2）TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。
它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。
MySQL 提供了函数可以互相转换时间戳和日期。 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。
默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。
应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高

三、MySQL存储引擎

1. 如何理解存储引擎

存储在计算机中的意思是将数据保存到某种介质中，并且保证数据的正常访问。而引擎是指发动机的核心。
组合起来的意思大致是保存数据的核心技术，也就是说，存储引擎是服务于存储服务的，通过存储引擎将数据保存。就跟计算机如何将数据保存到磁盘中一样，在数据库中，存储引擎的意思就是通过何种引擎将数据存储在磁盘中。

2. MySQL 支持哪些存储引擎？默认使用哪个？

通过 show engines 命令查看所有支持的存储引擎
MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。
可以通过 show variables like '%storage_engine%' 命令直接查看 MySQL 当前默认的存储引擎。

3. MySQL 存储引擎架构

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。
存储引擎是基于表的，而不是数据库。
并且，你还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎，区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。

MySQL 官方文档也有介绍到如何编写一个自定义存储引擎，地址：https://dev.mysql.com/doc/internals/en/custom-engine.html

4. MyISAM 和 InnoDB 的区别是什么？

1. 是否支持行级锁

MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁,默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！

2.是否支持事务

MyISAM 不提供事务支持。
InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持外键。
外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！阿里的《Java 开发手册》也是明确规定禁止使用外键的。

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

MyISAM 不支持，而 InnoDB 支持。
使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

5.是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。
MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。
讲真，这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。

6.索引实现不一样。

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
InnoDB 引擎中，其数据文件本身就是索引文件，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。 MyISAM，索引文件和数据文件是分离的。

四、MySQL索引

1.索引的介绍

索引是一种用于快速查询和检索数据的数据结构，
索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树和 Hash、红黑树。

在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构
在 MySQL 中，一个索引就是一颗 B+ 索引树

2. 索引的优缺点

优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。【索引大大提高了查询效率，同时却也降低更新表的速度】
索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

3. 索引的底层数据结构

1）Hash表

哈希表是键值对的集合，通过键(key)可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O(1)）。
为何能够通过 key 快速取出 value 呢？ 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。
但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。

就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。
为什么 MySQL 没有使用其作为索引的数据结构呢？ 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。
```
# 范围查找
SELECT * FROM tb1 WHERE id < 500;
```

2）B树

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数 (max-degree) 为5 (5阶) 的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

(树的度数指的是一个节点的子节点个数。)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l2mNlSo0-1668996796635)(C:\Users\zhangna\AppData\Roaming\Typora\typora-user-images\1668826763604.png)]
- 5阶的B树，每一个节点最多存储4个key，对应5个指针。
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
- 在B树中，非叶子节点和叶子节点都会存放数据。
- B-Tree Visualization (usfca.edu) 一个数据结构可视化的网站可以用来理解

3）B+树

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一
下其结构示意图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R5tiaihh-1668996796635)(C:\Users\zhangna\AppData\Roaming\Typora\typora-user-images\1668826957286.png)]
- 可以看到，两部分：
  绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点不放数据仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

4）B 树& B+树两者有何异同？

【数据存放方式】B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
【叶子节点有无链接】B 树的叶子节点都是独立的; B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
【检索效率是否稳定】

B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没到叶子节点，检索就结束了。

而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

5）InnoDB存储引擎为什么用B+树做索引

相对于二叉树，层级更少，搜索效率高；
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致B树一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作；

4. 索引类型

1）按索引字段特性分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
1. 主键索引 【PRIMARY】：针对于表中主键创建的索引。
  - 一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
  - 在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键

唯一索引 【UNIQUE】：避免同一个表中某数据列中的值重复。
- 唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。
普通索引：唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引 ：对字符串或二进制类型字段前几个字符或几个Bytes建立索引
全文索引 【FULLTEXT】：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。
⚠️注意：除了主键索引，其余都是二级索引（辅助索引）

2）按索引的存储形式

什么是聚集索引、非聚集索引 - 变体精灵 - 博客园 (cnblogs.com)

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

1. 聚簇索引

聚集索引是将索引列字段和行记录数据维护在了一起，聚集索引就是以主键创建的索引，它的叶子节点存储的是 完整的行记录数据，通过聚集索引能直接获取到整行数据
因为表中存储的数据是通过聚集索引组织在一起的,所以聚集索引必须要有，否则我们无法获取到表中的行数据，并且聚集索引还只能存在一个
例：一个User表（id, name, age)，id为主键，基于这张表的主键 id 建立的聚集索引如下图

2. 非聚簇索引(也叫二级索引)

非聚集索引是相比较于聚集索引来说,它是把索引和行数据分开维护，叶子节点并没有包含完整的数据记录

（叶子节点的数据区存储的是聚集索引的 id 或数据的磁盘地址。对于InnoDB引擎来说，叶子节点反对个是聚集索引的id，对于Myisam引擎来说，叶子节点存的是数据的磁盘地址)
以上述age字段建立的非聚集索引为例（InnoDB）：
- 叶子节点存放的是 索引列age的值 + 对应行记录的主键 id 值
- select * from user where age = 41 ,它的查找过程是什么样的?
  1. 首先，由于age是索引，并且where中使用了索引作为条件，需要从这个age的索引树中遍历，找到age=41的索引项（即右下角那个框）。
  2. 发现41下面还挂着该记录行的主键id13，然后根据主键id13回到主键聚集索引上找到id为13的行记录，取出对应那行数据即可。【这个过程即回表】
  3. 当然，若是 select age from user where age = 41 就不需要回表查询，直接在age索引树上就拿到了

3. 聚集索引和非聚集索引区别

聚集索引是以主键创建的索引，在叶子节点存储的是表中的数据。

非聚集索引是以非主键列创建的索引，在叶子节点存储的是索引列和主键。
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

4. question

创建表的时候并没有设置主键,表照样创建成功,那么还有没有聚集索引呢? ——有
- 如果存在主键,那么主键索引就是聚集索引
- 如果不存在主键,将会使用第一个唯一(UNIQUE)、非空的索引作为聚集索引
- 如果表中既没有主键索引,又没有合适的唯一索引,那么 Innodb 会自动维护一个 row_id(默认大小为 6B)来作为隐藏的聚集索引
为什么非聚集索引的叶子节点存储的是聚集索引的 id 值(或数据的磁盘地址值),直接跟聚集索引一样,把完整的数据放在非聚集索引的叶子节点不好吗,这样还不用回表查询,直接就能拿到结果
- 为了数据的一致性和节省磁盘空间
- 假设一张表里面有 10 个索引，就要在这 10 个索引上分别维护一份相同的表数据，而数据都是存储在磁盘上的，那么磁盘就会存储 10 份相同的数据,对磁盘的压力大
- 插入、更新、删除数据的时候,Mysql 就要同时维护 10 份一样的数据以保证数据的一致性，如果在维护数据的时候有一个出现了错误,那不就导致了数据不一致了吗。
  
  这也同时解释了为什么 Innodb 存储引擎的聚集索引只能有一个,因为只需要维护一份完整的数据就足够了

3）按组成索引的字段个数

单列索引：建立在单个列上的索引被称为单列索引。
联合索引：使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

覆盖索引：创建一个索引，该索引包含查询中用到的所有字段，称为“覆盖索引”。 即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

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

5. 索引语法

# 1.创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

# 2.查看索引
SHOW INDEX FROM table_name ;

# 3.删除索引
DROP INDEX index_name ON table_name ;

6. 索引使用

1）最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，并且不跳过索引中的列。如果跳跃某一列，后面的字段索引失效。
如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，
直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。
例：在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

2）范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
当范围查询使用>= 或 <= 时，就会走联合索引。所以，尽可能的使用类似于 >= 或 <= 这类的范围查询，

3）索引失效的情况

使用 **SELECT *** 进行查询;
创建了组合索引，但查询条件未遵守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 like '%abc';，但是像 like '软件%'; 这种%加在关键字之后就不会受影响
查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
发生隐式转换（字符串不加引号）：当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。则会发生转换隐式。

MySQL 使用操作符的一些特性：
1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。
所以，写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。
如果MySQL评估使用索引比全表更慢，则不使用索引

7. 索引优化相关

1）索引下推

索引下推（Index Condition Pushdown，简称ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。
是否使用ICP的执行流程对比：

在没有使用ICP的情况下，MySQL的查询：
- 存储引擎读取索引记录；
- 根据索引中的主键值，定位并读取完整的行记录；
- 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。
使用ICP的情况下，查询过程：
- 存储引擎读取索引记录（不是完整的行记录）；
- 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
- 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
- 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。
五分钟搞懂MySQL索引下推 - 掘金 (juejin.cn)

8. 正确使用索引的一些建议

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

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

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

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

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

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

4.注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。

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

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

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

6.避免索引失效：索引失效也是慢查询的主要原因之一

7.删除长期未使用的索引：不用的索引的存在会造成不必要的性能损耗

五、MySQL事务

1. 什么是事务

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

2. 事务四大特性ACID

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

⚠️只有保证了事务的持久性、原子性、隔离性后，一致性才能得到保障。即 A、I、D 是手段，C 是目的！

3. 并发事务带来哪些问题

脏读（Dirty read） ：一个事务读到另外一个事务还没有提交的数据。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
不可重复读（Unrepeatable read） ：指在一个事务内多次读取同一条记录，但两次读取的数据不同，称之为不可重复读。（在这个事务还没有结束时，另一个事务修改了该数据）
幻读（Phantom read） ：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。（另一个并发执行的事务插入了数据）

不可重复读和幻读有什么区别呢？

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

著作权归所有原文链接：https://javaguide.cn/database/mysql/mysql-questions-01.html

4. 事务隔离级别

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

⚠️MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

5. MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。
SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。
不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

著作权归所有原文链接：https://javaguide.cn/database/mysql/mysql-questions-01.html

六、MySQL锁

MySQL :: MySQL 5.7 Reference Manual :: 14.7.1 InnoDB Locking

1. 表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。

InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁）。对于并发写入操作来说， InnoDB 的性能更高。

1）表级锁和行级锁对比

表级锁：
1. MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁。
2. 优：实现简单，资源消耗也比较少，加锁快，不会出现死锁。
3. 缺：其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁：
1. MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。
2. 优：行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高。
3. 缺：但加锁的开销也最大（还要确定哪一行），加锁慢，会出现死锁。

2）行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。
当执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。【注意谨防索引失效】
不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

3）行级锁到底锁的什么？

2. 共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：
- 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
- 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。
排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

S 锁 X 锁
S 锁不冲突冲突
X 锁冲突冲突
由于 MVCC 的存在，对一般的 SELECT 语句，InnoDB 不会加任何锁。不过，可以显式加共享锁或排他锁。
```
# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;
```

	S 锁	X 锁
S 锁	不冲突	冲突
X 锁	冲突	冲突

3. 意向锁

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？

一行一行遍历肯定是不行，性能太差。我们需要用到意向锁来快速判断是否可以对某个表使用表锁。
意向锁是表级锁，共有两种：
- 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。
意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。
意向锁之间是互相兼容的，意向锁和表级别的共享锁排他锁互斥。

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

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

4. InnoDB 有哪几类行级锁

为了解决并发问题，引入了很多的锁机制，很多时候，数据库的锁是在有数据库操作的过程中自动添加的。

所以，这就导致很多程序员经常会忽略数据库的锁机制的真正的原理。
记录锁、间隙锁、临键锁，都是Mysql 里面InnoDB 引擎下解决事务隔离性的一系列排他锁。
InnoDB的数据是基于索引组织的，即聚簇索引所在的B+树即整张表的数据。

而行级锁，是通过对索引树上的索引项加锁来实现的，而不是对真正的数据记录加的锁。

举例：（一个记录锁：在聚簇索引树上的叶子节点那个节点上锁，因为聚簇索引叶子节点存一行数据）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ij1Lk07Z-1668996796638)(C:\Users\zhangna\AppData\Roaming\Typora\typora-user-images\1668933494331.png)]

1）记录锁（Record Lock）

锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC，RR隔离级别下都支持

例：当我们针对主键或唯一索引select / update 时，MySQL默认会对查询的这一行数据加记录锁。

-- id 列为主键列或唯一索引列， id 为 1 的记录行会被锁住。 
SELECT * FROM table WHERE id = 1 FOR UPDATE;

⚠️注意：
1. **id 列必须为唯一索引列或主键列 **，否则上述语句加的锁就会变成临键锁。
2. 查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

2）间隙锁（Gap Lock）

**间隙锁，锁定一个索引区间。**左右开区间，不包括区间两边的记录。确保索引记录区间不变，防止其他事务在这个区间进行insert，产生幻读。在RR隔离级别下支持。间隙锁：(16,18), (18,29)…
例：对于某一范围内的查询语句，会产生间隙锁
```
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
```
- 所有在（1，10）左右开区间范围内的记录行都会被锁住，会阻止其他事务将 1 到 10 之间的任何值插入到 id 字段中，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，无论这些数据行原本是否存在。但是 1 和 10 两条记录行不会被锁。【？？？疑问：表中有id2-9的数据吗】

3）临键锁（Next-key Lock）

临键锁，相当于行锁+间隙锁的组合， 它指的是加在某条记录以及这条记录前面间隙上的锁 。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BPUP1SGU-1668996796638)(C:\Users\zhangna\AppData\Roaming\Typora\typora-user-images\1668934120454.png)]
每个数据行上的非唯一索引列上都会存在一把临键锁
使用非唯一索引列进行select / update的时候，默认会加一个临键锁，锁定一个左开右闭区间的范围。例：
- 该表中 age 列潜在的临键锁有：
  (-∞, 10]，(10, 24]，(24, 32]，(32, 45]，(45, +∞]
- 执行以下语句会获取 (10, 24] 这个区间内的临键锁。
```
SELECT * FROM table WHERE age = 24 FOR UPDATE; 
```

⚠️总的来说，记录锁、临键锁、间隙锁只是锁定数据的范围不同，最终目的是为了解决幻读的问题。

七、SQL性能分析和优化

1. SQL性能分析

1）SQL执行频率

通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
```
SHOW GLOBAL STATUS LIKE 'Com_______';
```
可以查看当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。

如果是以增删改为主，我们可以考虑不对其进行索引的优化。

如果是以查询为主，那么就要考虑对数据库的索引进行优化了。
那假如是以查询为主，我们又该如何定位针对哪些查询语句进行优化呢？ ——借助慢查询日志。

2）慢查询日志

慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中所有响应时间超过阀值的SQL语句。

看慢日志是否开启

# 1.查看慢查询日志是否开启。（默认是没有）
show variables like 'slow_query_log';

# 2.开启MySQL慢日志查询开关
set global slow_query_log = 1;

查慢日志的阈值时间
```
show variables like 'long_query_time';
```
看慢日志文件中记录的信息
1. 找到my.ini文件并打开(mysql安装目录文件夹下) ，linux下好像叫my.cnf
2. 查找datadir对应的目录，日志文件就放在该目录下。

3）profile详情

show profiles 是MySql用来分析当前会话SQL语句执行的资源消耗情况，可以用于SQL的调优测量。

# 1.查看当前MySQL是否支持profile操作
SELECT @@have_profiling;

# 2.查询 profile 是否开启
select @@profiling;

# 3.开启 profile
set @@profiling=1;

# 4.查看每一条SQL的耗时基本情况
show profiles;

# 5.查看指定query_id(107)的SQL语句各个阶段的耗时情况
show profile for query 107;

# 6.查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query 150;

4）explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：直接在select语句之前加上关键字 explain / desc
```
explain select * from user where id=1;
```

explain执行计划中各字段的含义

字段	含义
id	SQL查询中的序列号(id列数字越大越先执行，若一样大，那就从上往下依次执行)
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
table	表名（不一定是实际存在的表名）
partitions	如果是使用的常数等值查询，这里会显示const；如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。
possible_keys	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
ref	该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
Extra	很多额外的信息会在 Extra 字段显示（例：distinct等）

type字段各个值的详细说明

type值	说明
NULL	没有连接
system	表只有一行，MyISAM引擎。
const	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
range	常数值的范围
index	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描(full table scan)

2. SQL优化手段

1）数据库表设计方面

不得使用外键与级联，一切外键概念必须在应用层解决。
选择合适的字段类型

【存储字节越小，占用空间也越小，性能也越好】
- 某些字符串可以转换成数字类型存储，数字是连续的，性能更好，占用空间也更小。eg：IP地址转成整型
```
# MySQL提供了两个方法来处理IP地址
INET_ATON() # 把ip转为无符号整型（4-8位）
INET_NTOA() # 把整型的ip转为地址
```
- 对于非负数据，要优先使用无符号整型，无符号相对于有符号整型多出一倍存储空间。eg：年龄，自增id
- 小数值类型优先用TINYINT类型。eg：年龄，状态01表示
- 对于日期类型，DateTime类型耗费空间更大且没有时区信息，建议用Timestamp
- 金额字段用decimal，避免精度丢失
- 尽量使用自增id做主键。因为自增id的话每次把新数据加在B+树尾部，不用从中间找加入位置。（不过，分库分表不建议用自增id做主键，要用分布式id如uuid）
时间类型数据存储建议
- 不要用字符串存日期（字符串占用空间大，且效率低（要逐个字符比对，不能用日期相关的API计算比较)
- Datetime 和 Timestamp之间，通常会首选 Timestamp 。（Datetime没有时区信息，且耗费空间更大)
- 数值型的时间戳好像也行？但是可读性不好

2）SQL语句优化方面

避免使用 select *，只返回必要的列

select * 会1消耗更多CPU， 2无用字段会增加数据传输时间， 3无法使用mysql优化器覆盖索引的优化；

select具体字段可减少表结构变更带来的影响
用 LIMIT 语句来限制返回的数据，只返回必要的行。
分页优化
- 阿里Java开发手册中描述：利用延迟关联或者子查询优化超多分页场景
- 普通的分页在数据量小的时候耗费时间还是比较短的，如果数据量变大，达到百万甚至千万级别，普通的分页耗费时间就非常长了。如：
```
select 'score', 'name' from 'cus_order' order by  'score' desc limit 10, 1000000
# limit两个参数：表示从第10条查起，返回1000000个数
```
- 【优化思路一：改为子查询】先查询出limit第一个参数对应的主键值，再根据这个主键值再去过滤并limit
  
  （不过，子查询的结果会产生一张新表，影响性能，尽量避免大量使用子查询）
```
select 'score', 'name' from 'cus_order' 
where id >= (select id from 'cus_order' limit 10, 1)
limit 1000000
```
- 【优化思路二：延迟查询】先提取对应的主键，再将这个主键表与原数据表关联
```
select 'score', 'name' from 'cus_order' a, (select id from 'cus_order' order by 'score' desc limit 10, 1000000) b 
where a.id = b.id
```
  ⚠️发现这两个优化思路都是要引入主键id进行的
  
  分页优化的两篇相关深度好文
  
  1 面试官：一千万数据，怎么快速查询？ 2 【得物技术】MySQL深分页优化 - 掘金 (juejin.cn)
尽量避免多表做join
- 两种避免多表做join的方法：【单表查询后在内存中自己做关联】对数据库做单表查询，再根据查询结果进行二次查询，以此类推，最后再进行关联
  
  一个相关讨论： MySQL多表关联查询效率高点还是多次单表查询效率高，为什么？
尽量用UNION ALL代替UNION
- UNION会把两个结果集的所有数据放到临时表中后再进行去重操作，更耗时
- UNION ALL不会再对结果集去重，获取到的数据包含重复的项
对于数据库的更新操作，能批量操作就要尽量使用批量操作，减少请求数据库的次数