从0开始回顾MySQL---系列一

基础

1、数据库的三范式是什么？

数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为：

1. 第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性，即数据库表的每一列都是不可分割的原 子数据项。
2. 第二范式（2NF）：在满足第一范式的基础上，非主属性完全依赖于主码（主关键字、主键），消除非主属性对主码的部分函数依赖。
3. 第三范式（3NF）：在满足第二范式的基础上，表中的任何属性不依赖于其它非主属性，消除传递依赖。简而言之，非主键都直接依赖于主键，而不是通过其它的键来间接依赖于主键。

2、MySQL 支持哪些存储引擎?  ⚡

• MySQL 支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE 等；
• MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后就改为了 InnoDB。

3、MyISAM 和 InnoDB 的区别有哪些？⚡

MyISAM 存储引擎：

• 特点：不支持事务和外键；
• 索引：MyISAM 是非聚簇索引，索引文件和数据文件是分离的，索引保存的是数据的地址；
• 检索：MyISAM 支持全文索引，查询效率上 InnoDB 要高；
• 锁：MyISAM 支持表级锁，每次操作对整个表加锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限；
• 存贮方式：  存贮表的总行数，执行 select count(*) from table 时只需要读出该变量即可，速度很快；
• 应用场景：查询和插入操作为主，只有很少更新和删除操作，并对事务的完整性、并发性要求不高。 因为MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。

InnoDB 存储引擎：

• 特点：支持事务和外键操作，支持并发控制；
• 索引：InnoDB 是聚簇索引，索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快；
• 检索：Innodb 不支持全文索引，执行 select count(*) from table 时需要全表扫描；
• 锁：Innodb 支持行级锁和表级锁，默认为行级锁；
• 存贮方式：不存贮表的总行数，MyISAM 用一个变量保存了整个表的行数，执行 select count(*) from table 时只需要读出该变量即可，速度很快；
• 应用场景：MySQL 5.5版本后默认，对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作。

4、MySQL 的逻辑架构了解吗？

1. 第一层是网络连接层，主要完成一些类似于连接处理、授权认证、及相关的安全方案。
2. 第二层是核心服务层，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。
3. 第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 和存储引擎进行通信，不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取合适的存储引擎。
4. 第四层是系统文件层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

5、SQL 约束有哪几种？

1. NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
2. UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
3. PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
4. FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
5. CHECK: 用于控制字段的值范围。

6、自增主键（AUTO_INCREMENT）理解?

自增主键:

InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：

• 在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id) + 1作为这个表当前的自增值
• 举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。也就是说，MySQL重启可能会修改一个表的AUTO INCREMENT的值。
• 在MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值，才有了“自增值持久化”的能力。

自增值修改机制

如果id字段被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

1. 如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段;
2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值 。

自增值新增机制:

1. 如果准备插入的值>=当前自增值，新的自增值就是 “准备插入的值+1”;
2. 否则，自增值不变。

7、为什么自增主键不连续？

唯一键冲突：

• 由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键 = 现在变了的自增值+1，所以不连续；
• 举例：假设执行 SQL 的时候 user 表 id = 10，此时在内存中的自增 id 为11，此时发生唯一键冲突写库失败，则 user 表没有 id = 10 这条记录，之后 id 从11开始写入，因此 id 是不连续的。  

事务回滚：

• 自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突；
• 举例：  假设同时需要对 user、staff 表进行写库操作，执行 SQL 的时候 user 表 id = 10，此时在内存中的自增 id 为11；staff 表 id = 20，此时内存中的自增 id 为21，一旦事务执行失败，事务回滚，写库失败，则 user 表没有 id = 10 这条记录，staff 表没有 id = 20 这条记录，user 表从11开始写入，staff 表从21开始写入，如此产生 id 不连续的现象。

8、为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

1. InnoDB优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为row_id 的隐藏列作为主键。
2. 使用自增主键好处：

• 使用自增ID，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费;
• 新插入的行一定会在原有的最大数据行下一行，MySQL定位和寻址很快，不会为计算新行的位置而做出额外的消耗;
• 减少了页分裂和碎片的产生。

3. 如果使用非自增主键:

• 由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页中间的某个位置  ， 此时MySQL不得不为了将新记录插到合适位置而移动数据 ，无疑增加了很多开销，同时分页操作也造成了大量的碎片。

9、VARCHAR 和 CHAR有什么区别？

1. 固定长度 & 可变长度

• CHAR用于存储固定长度字符串，假如申请了char(10)的空间，那么无论实际存储多少内容，该字段都占用 10 个字符。
• VARCHAR用于存储可变长度字符串， MySQL会根据定义的字符串长度分配足够的空间。

2. 存储方式

• VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。  
• CHAR适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的 MD5 值。对于经常变更的数据，CHAR 也比 VARCHAR更好，因为定长的 CHAR 不容易产生碎片。

3. 占用字节

• CHAR的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
• VARCHAR的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都是非unicode的字符数据。  

4. 存贮效率

• CHAR的存取速度比VARCHAR要快得多，因为其长度固定，方便程序的存储与查找；但是CHAR也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，是以空间换取时间效率 。
• VARCHAR是以空间效率为首位的。  

10、MySQL中in和exists区别？

exists用于对外表记录做筛选：

• exists会遍历外查询表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)

使用场景：

• 子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度；
• 当外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度；

11、什么是存储过程？有哪些优缺点？

存贮过程： 存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合。用户可以像使用自定义的函数―样重复调用这些存储过程，实现它所定义的操作。这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。

优点：

• 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用；
• 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可；
• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率；
• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理。

缺点：

• 互联网项目中，迭代太快，项目的生命周期也比较短，在这样的情况下，存储过程的管理不是特别友好，同时复用性也没有写在服务层那么好。

12、MySQL 执行查询的过程？

1. 客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配。
2. 客户端发送一条查询给服务器，服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一阶段。
3. 分析器进行词法分析，语法分析。
4. 优化器执行计划生成，索引选择。
5. 最后交给执行器，操作引擎，返回结果。

13、删除表的三种方式？

1. delete from 

• delete 是删除表中的数据，不删除表结构，速度最慢，但可以与where连用，可以删除指定的行；

delete from user;  -- 删除user表的所有数据
delete from user where user_id = 1; --删除user表的指定记录

2. drop table

• drop 是直接删除表信息，速度最快，但是无法找回数据 ；

drop table user; -- 删除 user 表

3. truncate (table)

• truncate 是删除表数据，不删除表的结构，速度排第二，但不能与where一起使用；  

truncate table user; --删除 user 表

三种方式的区别：

14、count(1)、count(*) 与 count(列名) 的区别？

执行效果：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL ；
• count(1) 计算一共有多少符合条件的行 ，用1代表代码行，在统计结果的时候，不会忽略列值为NULL；
• count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这⾥的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行速度：

• 列名为主键，count(列名)会比count(1)快；
• 列名不为主键，count(1)会比count(列名)快；
• 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*);
• 如果有主键，则 select count（主键）的执行效率是最优的;
• 如果表只有一个字段，则 select count（*）最优。

15、MySQL 的内连接、左连接、右连接有有什么区别？

MySQL的连接主要分为内连接和外连接，外连接常⽤的有左连接、右连接。

1. inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集；
2. left join左连接在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
3. right join右连接在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

16、MySQL使用枚举类型的优缺点？

优点：

1. 减少空间：枚举类型只存储了预先定义好的几种取值，而不是存储字符串或数字，因此能够节省存储空间。
2. 限制字段值：使用枚举类型可以限制字段的取值范围，确保存储的数据的正确性。
3. 数据安全：使用枚举类型可以降低因人为失误导致的数据错误的风险。
4. 提高代码可读性：使用枚举类型可以使代码更容易理解，因为每个枚举值都有其明确的含义。

缺点：

1. 可扩展性差：枚举类型定义了一组固定的值，如果要增加或删除枚举类型的值，需要修改数据库的表结构。
2. 代码依赖性高：枚举类型定义在数据库中，如果需要修改枚举类型的值，需要修改代码。
3. 限制：枚举类型只允许存储预先定义的有限集合中的值，不能存储其他值。