# mysql

# 进阶知识( 面试常问)

## MYSQL的索引

MYSQL的索引  ( 主键索引、唯一索引、普通索引、全文索引)

```mysql
MYSQL的索引主要分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE) ，普通索引(INDEX)和全文索引(FULLTEXT) 。

主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。

普通索引：这是最基本的索引，它没有任何限制。

全文索引：FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。
```

个人理解 :    普通索引, 唯一索引等都是  二级索引 ( 辅助索引 )  不是直接通过主键索引来直接查询

二级索引的问题 :

二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的					一行记录。

```mysql
1.相比于叶子节点中存储行指针，二级索引存储主键值会占用更多的空间，那为什么要这样设计呢？

InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。

2.那么InnoDB有了聚簇索引，为什么还要有二级索引呢？

聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引。

3.为什么一般情况下，我们建表的时候都会使用一个自增的id来作为我们的主键？

InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的，每插入一条记录，其实都是增加一个叶子节点，如果主键是顺序的，只需要把新增的一条记录存储在上一条记录的后面，当页达到最大填充因子的时候，下一跳记录就会写入新的页中，这种情况下，主键页就会近似于被顺序的记录填满。

若表的主键不是顺序的id，而是无规律数据，比如字符串，InnoDB无法加单的把一行记录插入到索引的最后，而是需要找一个合适的位置（已有数据的中间位置），甚至产生大量的页分裂并且移动大量数据，在寻找合适位置进行插入时，目标页可能不在内存中，这就导致了大量的随机IO操作，影响插入效率。除此之外，大量的页分裂会导致大量的内存碎片。
　　
　　因为主键聚集索引是需要根据 这个主键来构造 B+ 树的 如果自增的话, 数据的插入与查询都会快很多,物理存储顺序与主键顺序一致, 只需要在刚才的最后一条记录后面, 如果不是主键,那么B+ 树就会出现大量的分裂平衡移动, 影响效率. 
```

InnoDB**聚集索引**的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

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

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

**什么是回表查询？**

举个栗子，不妨设有表：

*t(id PK, name KEY, sex, flag);*

*画外音：id是聚集索引，name是普通索引。*

表中有四条记录：

*1, shenjian, m, A*

*3, zhangsan, m, A*

*5, lisi, m, A*

*9, wangwu, f, B*

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy4dc2650d-9b4d-49e6-ad62-9d158e162391.png)

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

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

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

既然从普通索引无法直接定位行记录，那**普通索引的查询过程是怎么样的呢？**

通常情况下，需要扫码两遍索引树。

例如：

```
select` `* ``from` `t ``where` `name``=``'lisi'``;　
```

**是如何执行的呢？**

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudyabf37f3f-d482-463c-b799-c0ff306fba2d.png)

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

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

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

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

就是普通索引中叶子节点存储的是主键值, 然后根据主键值再去找 聚集索引的 记录  后面这个过程叫做 回表查询

**什么是索引覆盖****(Covering index)****？**

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

个人理解 : 就是在一个索引表中就可以得到想要的数据列信息, 不需要回表 .

**如何实现索引覆盖？**

常见的方法是：将被查询的字段，建立到联合索引里去。

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

第一个SQL语句：

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy168d6b5d-114a-4cbb-bbc1-460c90be209e.png)

```
select id, name  from user where name='shenjian';　
```

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

*画外音，Extra：**Using index**。*

第二个SQL语句：

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudyf55b76a9-fede-4bb7-be74-a50957369233.png)

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

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

*画外音，Extra：**Using index condition**。*

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

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

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy4c79ac5a-1c78-4287-a90e-cc885c15c8ff.png)

可以看到：

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

都能够命中索引覆盖，无需回表。

MySQL索引使用的数据结构主要有**BTree索引** 和 **哈希索引** 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

## 事务

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

![undefined](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudyafe3e4eb-56a1-403b-aca4-5508069f0839.png "undefined")

原子性(Atomic)
   	    整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚			(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性(Consist)
			一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务			有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an 			Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时			发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总			额也应该还是500元,这就是保护性和不变性。
隔离性(Isolated)
			隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功			能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混			淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
持久性(Durable)
			在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

```java
1. 原子性（Atomicity）：** 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）：** 执行事务后，数据库从一个正确的状态变化到另一个正确的状态；
3. 隔离性（Isolation）：** 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）：** 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
    
    ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者.这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的结果就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.

```

小 tips :  对于 一致性的 认知:  对于ACID 这个几个性质来说 , 一致性一直是最为难理解的一个,也可以说是最为模糊的一个, 网上博客视频的例子都是说 转账 100 ,到账 100这个 , 但我一直认为例子是帮助我们来更深入的理解的 , 而这个例子本质上并没有说明这个一致性到底是什么,   难道是 两边钱 转账前后必须总数不变 叫做一致性吗 ? 我看了 没几个人这么说, 但却都拿这个例子来说, 这不就成了为了自圆其说,没有什么实质上的理解 .  我带着疑问,一直想比较深入的搞懂这个 一致性到底是什么东西 :

发现网上关于这个问题的本质疑问的人也是很多, 不乏很多工作很多年以及一些大佬 :

```mysql
ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者.这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的结果就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.
```

这个回答有很多大佬表示赞同, 以我们现在的水平 自己的理解只来的最快的也就是站在大佬的肩膀上.

<< 数据密集型应用系统设计>> 中描述 : 一致性主要是对数据有特定的预期状态, 任何数据更改必须满足这些状态约束 . 也就是说, **一致性是指数据要和预期的状态保持一致** .   Joe Hellerstein 曾评论 : 严格意义上说, 一致性并不是在数据库的特性, 而是应用层的特性 .  这个时候如果拿转账的例子来说, 那转账100 这边得到100  其实数据库确实是没有办法控制这个过程 , 不想 AID 三个特性, 数据库直接给解决了 .

以上总结了一下我的最终个人理解 :  一致性是数据库需要靠应用层来实现的一个达到我们预期结果的一个功能, 转账 100 ,对方收到100 ,这个功能数据库并不能帮我们控制, 需要在应用层开发人员来进行控制 . 只要达到我们的预期结果其实也就是实现了一致性, 毕竟程序也只是为了服务人, 服务开发者来满足用户的一个工具 . 那么 现在转账 100, 生活中肯定是要给到对方100, 但是我现在 就给对方转 50 , 剩下的我吃回扣 ( 突然想到, 其实微信的提现扣我手续费不就是如此吗, 难道说它没有保证一致性吗) ,只要实现了我开发者的预期, 其实就是满足了一致性 .  以上均为我个人理解, 如有不对, 大佬多多指教

并发事务带来哪些问题?

- **脏读（Dirty read）:** 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- **丢失修改（Lost to modify）:** 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
- **不可重复读（Unrepeatableread）:** 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- **幻读（Phantom read）:** 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务隔离级别有哪些?MySQL的默认隔离级别是?

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

- **READ-UNCOMMITTED(读取未提交)：** 最低的隔离级别，允许读取尚未提交的数据变更，**可能会导致脏读、幻读或不可重复读**。

- **READ-COMMITTED(读取已提交)：** 允许读取并发事务已经提交的数据，**可以阻止脏读，但是幻读或不可重复读仍有可能发生**。

- **REPEATABLE-READ(可重复读)：** 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，**可以阻止脏读和不可重复读，但幻读仍有可能发生**。

- **SERIALIZABLE(可串行化)：** 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，**该级别可以防止脏读、不可重复读以及幻读**。

| 隔离级别         | 脏读 | 不可重复读 | 幻影读 |
| ---------------- | ---- | ---------- | ------ |
| READ-UNCOMMITTED | √    | √          | √      |
| READ-COMMITTED   | ×    | √          | √      |
| REPEATABLE-READ  | ×    | ×          | √      |
| SERIALIZABLE     | ×    | ×          | ×      |

MySQL InnoDB 存储引擎的默认支持的隔离级别是 **REPEATABLE-READ（可重读**)

oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。

默认系统事务隔离级别是READ COMMITTED,也就是**读已提交**

SQL Server  默认系统事务隔离级别是read committed,也就是**读已提交**

**ACID靠什么保证的呢？**

```java
A 原子性   由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C 一致性   一般由代码层面来保证

I 隔离性   由MVCC来保证

D 持久性   由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕			  机的时候可以从redo log恢复

```

##  MyISAM   InnoDB   MEMORY区别

存储引擎就是指***表的类型以及表在计算机上的存储方式\***

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）.

**两者的对比：**

1. **是否支持行级锁** : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
2. **是否支持事务和崩溃后的安全恢复： MyISAM** 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是**InnoDB** 提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. **是否支持外键：** MyISAM不支持，而InnoDB支持。
4. **是否支持MVCC** ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 `READ COMMITTED` 和 `REPEATABLE READ` 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：[MySQL-InnoDB-MVCC多版本并发控制](https://segmentfault.com/a/1190000012650596)
5. ......

《MySQL高性能》上面有一句话这样写到:

> 不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB的速度都可以让MyISAM望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择MyISAM也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

```java
**对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度**
```

**MEMORY**是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且**数据全部放在内存中**。这些特性与前面的两个很不同。因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失 .

MEMORY默认使用**哈希索引**。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

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

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

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

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudyf5ea6827-eff7-4ca8-8b2c-c590b9986ad5.png)

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy6764f337-2b49-47bf-8c28-904303d5423f.png)

**如何选择：**

**1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；**

**2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。**

**3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；**

**4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。**

个人总结

```java
Innodb 与 myisam 
    
    Innodb 中的主键索引 用的是聚簇索引 , 以这个主键来建立 B+ 树, 数据的物理存放顺序与索引顺序是一致的， 即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。 所以插入与查询更快(顺序读写磁盘的速度很快) , 非叶子节点存储的是 索引, 叶子节点存储的是 具体主键索引对应的数据, 可以直接获取到所需要的数据 . 
    
    Innodb 的非聚簇索引, 也就是普通索引, 唯一索引等 , 虽然也是用　Ｂ+ 树进行存储的， 但是叶子节点数据并不是这条整个数据, 而是这个普通索引对应的主键, 然后通获取到的主键去进行聚簇索引来查询数据.  
    
    Myisam 的主键索引与辅助索引, 虽然使用的 B+ 树, B+ 树的非叶子节点也都是索引, 但是叶子节点 data域存储的是数据指针, 而不是innodb 的直接存储的数据信息 . 
    
```

## MySQL 基础架构分析

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

先简单介绍一下下图涉及的一些组件的基本作用

**•连接器：**    身份认证和权限相关(登录 MySQL 的时候)。

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

•**分析器:** 	  没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语					 句语法是否正确。

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

•**执行器:** 	  执行语句，然后从存储引擎返回数据。

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudya44bc0bc-8ed1-4d4b-90bb-f82a1d31fc80.webp)

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

•**Server 层**：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

•**存储引擎**： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。**现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。**

Server 层基本组件介绍

1) 连接器

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

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

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

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

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

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

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

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

3) 分析器

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

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

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

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

4) 优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

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

5) 执行器

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

感兴趣或者想更深入的同学可以去深入研究里面每一个过程的详细步骤, 这里只是大概介绍列举.

## redo log、undo log和binlog

事务的实现

redo log保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudyca7dc93b-f110-44ee-90dc-8bd8713a452b.png)

**undo  log**

```mysql
undo是逻辑日志，只是将数据库逻辑地恢复到原来的样子；所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能不大相同。

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

InnoDB 存储引擎回滚时，对于每个INSERT，会完成一个DELETE；对于每个DELETE，会执行一个INSERT；对于每个UPDATE，会执行一个相反的UPDATE，将修改前的行放回去。

MVCC： 当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
```

**redo log**

```mysql
redo log 是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。

有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe。

redo log日志的大小是固定的，即记录满了以后就从头循环写。

Write Ahead Log策略
事务提交时，先写重做日志再修改页；当由于发生宕机而导致数据丢失时，就可以通过重做日志来完成数据的恢复。

InnoDB首先将重做日志信息先放到重做日志缓存
按一定频率刷新到重做日志文件
```

**binlog日志**

```mysql
binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有`crash-safe`能力的
```

**redo log和binlog区别**

```mysql
* redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

* redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑

* redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

* binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
```

## MVCC多版本并发控制

什么是MVCC?

> MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。

MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的.

**MVCC能解决什么问题，好处是？**

数据库并发场景有三种，分别为：

- 读-读：不存在任何问题，也不需要并发控制
- 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
- 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

## **MVCC的实现原理**

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。所以我们先来看看这个三个point的概念

**隐式字段**

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

- DB_TRX_ID
   6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

- DB_ROLL_PTR
   7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

- DB_ROW_ID
   6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy75761729-7aa9-411e-8279-5b05536edcfc.webp)
- 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudye293fc5c-2cb7-4115-a7a6-8b95705c1461.webp)
如上图，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

**undo日志**

undo log主要分为两种：

- insert undo log
   代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
- update undo log
   事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

> purge
>
> - 从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
> - 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链，它的执行流程如下：

一、 比如一个有个事务插入persion表插入了一条新记录，记录如下，name为Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudye7532846-dedd-4633-9ecb-d5a4d75c6d97.webp)

二、 现在来了一个事务1对该记录的name做出了修改，改为Tom

- 在事务1修改该行(记录)数据时，数据库会先对该行加排他锁

- 然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本

- 拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务ID为当前事务1的ID, 我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，既表示我的上一个版本就是它

- 事务提交后，释放锁

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy6d538f5c-6aa2-43f7-bee7-3d19a30cb382.webp)
三、 又来了个事务2修改person表的同一个记录，将age修改为30岁

- 在事务2修改该行数据时，数据库也先为该行加锁

- 然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面

- 修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录

- 事务提交，释放锁

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudya017ec05-0ece-43f2-a800-853895820dae.webp)
从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

**Read View(读视图)**

什么是Read View?

什么是Read View，说白了Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)

所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudycfc6e7c5-0602-4a48-a224-fbe49083cd1f.webp)如上，它是一段MySQL判断可见性的一段源码，即changes_visible方法（不完全哈，但能看出大致逻辑），该方法展示了我们拿DB_TRX_ID去跟Read View某些属性进行怎么样的比较

在展示之前，我先简化一下Read View，我们可以把Read View简单的理解成有三个全局属性

> trx_list（名字我随便取的）
>  一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
>  up_limit_id
>  记录trx_list列表中事务ID最小的ID
>  low_limit_id
>  ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1

- 首先比较DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
- 接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断
- 判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的

**整体流程**

我们在了解了隐式字段，undo log， 以及Read View的概念之后，就可以来看看MVCC实现的整体流程是怎么样了

整体的流程是怎么样的呢？我们可以模拟一下

- 当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务ID为2，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了，所以Read View记录了系统当前活跃事务1，3的ID，维护在一个列表上，假设我们称为trx_list

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy02f20125-d8e6-489f-8d76-5c6aafdcd92e.webp)
- Read View不仅仅会通过一个列表trx_list来维护事务2执行快照读那刻系统正活跃的事务ID，还会有两个属性up_limit_id（记录trx_list列表中事务ID最小的ID），low_limit_id(记录trx_list列表中事务ID最大的ID，也有人说快照读那刻系统尚未分配的下一个事务ID也就是目前已出现过的事务ID的最大值+1，我更倾向于后者；所以在这里例子中up_limit_id就是1，low_limit_id就是4 + 1 = 5，trx_list集合的值是1,3，Read View如下图

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudyeed341af-01b2-46ae-82ef-8ba0ede65de9.webp)
- 我们的例子中，只有事务4修改过该行记录，并在事务2执行快照读前，就提交了事务，所以当前该行当前数据的undo log如下图所示；我们的事务2在快照读该行记录的时候，就会拿该行记录的DB_TRX_ID去跟up_limit_id,low_limit_id和活跃事务ID列表(trx_list)进行比较，判断当前事务2能看到该记录的版本是哪个。

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy62b1b9c7-977b-4a3d-b0f0-4e7ec78193a5.webp)
- 所以先拿该记录DB_TRX_ID字段记录的事务ID 4去跟Read View的的up_limit_id比较，看4是否小于up_limit_id(1)，所以不符合条件，继续判断 4 是否大于等于 low_limit_id(5)，也不符合条件，最后判断4是否处于trx_list中的活跃事务, 最后发现事务ID为4的事务不在当前活跃事务列表中, 符合可见性条件，所以事务4修改后提交的最新结果对事务2快照读时是可见的，所以事务2能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本
![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy9e9e9bd2-2cb5-4aee-acf0-572dcb58cd5a.webp)

- 也正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

MVCC 部分知识来自于 :  https://www.jianshu.com/p/8845ddca3b23

小 tips :   网上绝大部分博客说的 MVCC 的实现都是有一个创建 iD 删除 iD 那种,  那种我们同学也请教过我们老师, 我们老师一块探讨查找, 发现那种情况是不对的 , 官网也确实不是那种说法, 是这种 readView  undo log 的, 不怕不会,就怕学习了很长时间, 发现自己研究学习的结论本身就是错误的 .  博客中不太懂的话 我给大家找到了个视频 , 大家可以研究下 :

https://www.bilibili.com/video/BV1xh411Z79d?p=10   个人感觉 mvcc 应该是在我们这个阶段比较难的一个问题, 大家需要花点时间来理解 .

##  查询缓存的使用

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

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果也不会被缓存。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。
```

## 为什么 索引选择使用 B+ 树

**二叉查找树**

一个二叉查找树是由n个节点随机构成，所以，对于某些情况，二叉查找树会退化成一个有n个节点的线性链

**AVL树**

AVL树是带有平衡条件的二叉查找树，一般是用平衡因子差值判断是否平衡并通过旋转来实现平衡，左右子树树高不超过1，和红黑树相比，它是严格的平衡二叉树，平衡条件必须满足（所有节点的左右子树高度差不超过1）。不管我们是执行插入还是删除操作，只要不满足上面的条件，就要通过旋转来保持平衡，而旋转是非常耗时的，由此我们可以知道AVL树适合用于插入删除次数比较少，但查找多的情况。 看出，任意节点的左右子树的平衡因子差值都不会大于1。

**（2）局限性**

由于维护这种高度平衡所付出的代价比从中获得的效率收益还大，故而实际的应用不多，更多的地方是用追求局部而不是非常严格整体平衡的红黑树。当然，如果应用场景中对插入删除不频繁，只是对查找要求较高，那么AVL还是较优于红黑树。

**红黑树**

一种二叉查找树，但在每个节点增加一个存储位表示节点的颜色，可以是red或black。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍。它是一种弱平衡二叉树(由于是若平衡，可以推出，相同的节点情况下，AVL树的高度低于红黑树)，相对于要求严格的AVL树来说，它的旋转次数变少，所以对于搜索、插入、删除操作多的情况下，我们就用红黑树。

B+树的树高比AVL树、红黑树低，IO次数少

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/02/25/kuangstudy06376ffa-9bb4-4680-ae27-0d8dbe18a5cf.webp)

总结一下为什么不用 B 树而用 B+ 树 :

首先红黑树跟 AVL 树当数据较多的时候, 树的高度比B树高 , 磁盘 IO 次数多, 效率自然相对就较低 .

B 树 因为每个索引节点上 data 域都存储有 数据, 占用更多的空间, 这样索引页所存储的节点就比 B+ 树少, 磁盘 IO次数也就比B+ 多, 效率比 B+ 树低, 还有是B+ 树叶子节点都有指向下一个叶子节点的指针, 链表, 这样在遍历的时候,只需要遍历叶子节点的即可,更快更方便

## 索引优化

```mysql
1. 最左前缀法则 : 如果索引了多列, 遵守最左前缀法则, 指的是查询从索引的最左列开始并且不跳过索引中的列.
2. 不在索引列上做任何操作( 计算 函数 类型转换 ),会导致索引失效而转向全表扫描.
3. 存储引擎不能使用索引中范围条件右边的列(范围之后的索引会失效)
4. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)), 减少select * 
5. mysql在使用 != 的时候无法使用索引会导致全表扫描
6. is null , is not null 也无法使用索引
7. like 以通配符 %开头的索引失效会变成全表扫描
8. 字符串不加单引号导致索引失效(隐式的类型转换)
9. 少用 or , 用它也会索引失效
```

## 锁机制与InnoDB锁算法

**表级锁和行级锁对比：**

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

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

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

**相关知识点：**

1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

## 分表后的ID怎么保证唯一性的呢

```mysql
设定步长，比如1-1024张表我们分别设定1-1024的基础步长，这样主键落到不同的表就不会冲突了。

分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种

分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。
```

其实还有不知识 : 垂直水平拆分 , 集群  ,  读写分离 , 优化.......  ,数据库的知识实在太多了, 以后学习透彻了, 再给加上.

# 基础知识

## select 语法

```sql
1 SELECT [ALL | DISTINCT]
2 {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
3 FROM table_name [as table_alias]
4   [left | right | inner join table_name2]  -- 联合查询
5   [WHERE ...]  -- 指定结果需满足的条件
6   [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
7   [HAVING]  -- 过滤分组的记录必须满足的次要条件
8   [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
9   [LIMIT {[offset,]row_count | row_countOFFSET offset}];
10
   --
指定查询的记录从哪条至哪条
```

## distinct (去重)

作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

```sql
1 -- # 查看哪些同学参加了考试(学号)
2 SELECT * FROM result; -- 查看考试成绩 去除重复项
3 SELECT studentno FROM result; --
4 SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)
```

## % 与 _ 区别

```sql
like结合使用的通配符 :  % (代表0到任意个字符)      _ (一个字符)
```

## in 的使用

```sql
-- IN
28 -- =============================================
29 -- 查询学号为1000,1001,1002的学生姓名
30 SELECT studentno,studentname FROM student
31 WHERE studentno IN (1000,1001,1002);

33 -- 查询地址在北京,南京,河南洛阳的学生
34 SELECT studentno,studentname,address FROM student
35 WHERE address IN ('北京','南京','河南洛阳');
```

## JOIN 对比

[inner join 与 left join 之间的区别](https://www.cnblogs.com/assasion/p/7768931.html)

| 操作符名称 | 描述                                      |
| ---------- | ----------------------------------------- |
| INNER JOIN | 如果表中有至少一个匹配,则返回行           |
| LEFT JOIN  | 即使右表中没有匹配,也从左表中返回所有的行 |
| RIGHT JOIN | 即使左表中没有匹配,也从右表中返回所有的行 |

```sql
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
   
内连接 inner join      查询两个表中的结果集中的交集
外连接    outer join
左外连接  left join    (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接  right join   (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)

```

MySQL的默认端口号是**3306**

## drop、truncate、delete三者删除的区别

```java
一：用法和区别

drop：drop table 表名

删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。

truncate (清空表中的数据)：truncate table 表名

删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。

truncate不能删除行数据，虽然只删除数据，但是比delete彻底，它只删除表数据。

delete：delete from 表名 （where 列名 = 值）

与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete即可以对行数据进行删除，也可以对整表数据进行删除。

二：注意

1.delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

2.执行速度一般来说：drop>truncate>delete

3.delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

4.truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发trigger。

5.truncate语句执行以后，id标识列还是按顺序排列，保持连续；而delete语句执行后，ID标识列不连续
```

## datatime 和 timestamp

|           | 字节  | 范围        | 受时区影响 |
| --------- | ----- | ----------- | ---------- |
| datetime  | 8字节 | 1000 - 9999 | 否         |
| timestamp | 4字节 | 1970 - 2038 | 是         |

## 外键

[数据库中为什么不推荐使用外键约束]: 	"https://www.cnblogs.com/rjzheng/p/9907304.html"

其实这个话题是老生常谈，很多人在工作中确实也不会使用外键。包括在阿里的JAVA规范中也有下面这一条

> **【强制】不得使用外键与级联，一切外键概念必须在应用层解决。 **

> **每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方**

## varchar与char有什么区别

```mysql
区别一，定长和变长
char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。

因为其长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。

区别二，存储的容量不同
对 char 来说，最多能存放的字符个数 255，和编码无关。
而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。
```

本人菜鸡一枚, 如有不正确的地方, 望大佬指教,  及时改正.

Mysql面试高频问点

总共已有 0 条评论