MySQL

数据库的三范式是什么？

• 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
• 第三范式：任何非主属性不依赖于其它非主属性。

一张自增表里面总共有 17 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

• 表类型如果是 MyISAM ，那 id 就是 18。
• 表类型如果是 InnoDB，那 id 就是 15。

InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入”abc”三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

float 和 double 的区别是什么？

• float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
• double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

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

内连接关键字：inner join；左连接：left join；右连接：right join。

内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

左连接 ，右连接，内连接和全外连接的4者区别

MySQL的存储引擎？

InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

MEMORY： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

注意：同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

MySQL - 常见的三种存储引擎

Mysql各种存储引擎对比总结

MySQL 三大日志-binlog、redo log和undo log

• binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。
• redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。
• undo log 实现底层的原子性。undo log 主要记录了数据的逻辑变化，比如一条 ` INSERT 语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是 MVCC `(多版本并发控制)实现的关键
• 
• 补充：relay log（中继文件）：主从复制时，将 binlog 写入 relay log中，做中间的一个媒介

索引的基本原理

• 索引用来快速的寻找那些具有特定值的记录，如果没有索引，一般来说执行查询时遍历整张表
• 索引的原理：把无序的数据变成有序的查询
  1. 把创建了索引的列的内容进行排序
  2. 对排序结果生成倒排序
  3. 在倒排序内容上拼上数据地址链
  4. 在查询的时候，先拿到倒排表内容，在取出数据地址链，从而拿到具体数据

索引的优缺点

• 优点
  1. 提高数据的检索速度，降低数据库 IO 成本：使用索引的意义就是缩小表中需要查询的记录的数目，从而加快搜索的速度
  2. 降低数据排序的成本，降低 CPU 消耗：索引之所以快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本
• 缺点
  1. 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上
  2. 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变化，从而降低了表的更新速度

什么时候不要使用索引

1. 经常增删改的列不要建立索引；
2. 有大量重复的列不建立索引；
3. 表记录太少不要建立索引。

MySQL 索引有哪些

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。 按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。

索引类型：

• 主键索引：不允许有空值，创表同时创建，一个表只能有一个主键
• 唯一索引：值必须唯一，可以为空值，如果是组合索引，列值组合必须唯一
• 普通索引：最基本的索引，没有限制
• 组合索引：一个索引包含多个列，用于避免回表查询，顺序为先区分度高再区分度低
• 全文索引：一经创建不可修改，只能删除重建

• MySQL索引有哪些分类
• MySQL索引优化
• 面试官:谈谈你对mysql联合索引的认识

MySQL的索引失效

• 索引失效原理
• MySQL的索引失效

MySQL 聚簇索引和非聚簇索引区别

都是 B+ 树结构

区别：

• 聚簇索引：将数据存储与索引放在一起，并且按照一定的顺序组织，找到索引也就找到了数据，数据的物理存放顺序与索引顺序时一致的
• 非聚簇索引：叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据
• InnoDB 一定有主键，主键一定是聚簇索引，MyISAM 使用的是非聚簇索引

聚簇索引优点：

1. 查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要二次查询（非覆盖索引的情况下）效率更高
2. 聚簇索引对于范围查询的效率更高，因为数据是按照大小排列的
3. 聚簇索引适合排序场合，非聚簇索引不适合

聚簇索引缺点：

1. 维护索引费用昂贵
2. 表使用 UUID（随机 ID）作为主键，使数据存储稀疏，这会出现聚簇索引有可能比全表扫描更慢，索引建议使用 int 的 auto_increment 作为主键
3. 如果主键比较大，辅助索引也会变得更大，因为辅助索引的叶子存储的是主键值；过长的主键值会导致叶子节点占用更多的物理空间

MySQL 索引的数据结构

索引的数据结构和具体存储引擎的实现有关，再 MySql 中使用较多的是 Hash 索引和 B+ 树索引。InnoDB存储引擎默认索引实现是 B+ 树索引。对于 Hash 索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择 Hash 索引，查询性能最快；其余大部分场景，建议选择 B+ 树索引

• B+ 树索引：是一个平衡的多叉树，且叶子节点间有双链指针相互连接。在 B+ 树上常规检索，从根节点到叶子节点搜索效率基本相当，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高，因此，B+ 树索引广泛应用于数据库、文件系统等场景

  B+树最大的性能问题是会产生大量的随机IO，随着新数据的插入，叶子节点会慢慢分裂，逻辑上连续的叶子节点在物理上往往不连续，甚至分离的很远，但做范围查询时，会产生大量读随机IO。

  对于大量的随机写也一样，举一个插入key跨度很大的例子，如7->1000->3->2000 … 新插入的数据存储在磁盘上相隔很远，会产生大量的随机写IO。 链接：B-树、B+树、B*树、LSM树优缺点比较

• Hash 索引：采用一定的哈希算法，把键值换成新的哈希值，只需一次哈希算法即可定位到相应位置。只适合等值查询，不适合范围查询

MySQL 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

补充：

• 聊一下你对MySQL索引实现原理
• MySQL——索引实现原理

怎么验证 MySQL 的索引是否满足需求？

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。

explain 语法：explain select * from table where type=1。

explain结果每个字段的含义说明

慢SQL优化实战笔记

说一下 MySQL 常用的引擎，有什么区别？

1. InnoDB 引擎提供了行级锁，它的设计的目标就是处理大数据容量的数据库系统；MyISAM 不支持行级锁，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。
2. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
3. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
4. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
5. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
6. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

MySQL 事务隔离级别，默认隔离级别，为什么这么选

四个隔离级别：

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

默认隔离级别：

InnoDB 存储引擎在 REPEATABLE-READ（可重读），事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如SQL Server) 是不同的。

所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

为什么会默认可重复读：

考虑 MySQL 的主从同步，隔离级别设为可重复读(Repeatable Read)，在该隔离级别下引入间隙锁，实现了写入 Binlog 的语句串行化。解决了主从不一致的问题。

脏读、不可重复读、幻读

• 脏读（读取未提交数据）：A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。
• 不可重复读（前后多次读取，数据内容不一致）：事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。（注重数据的内容，错误发生在 update，解决方法是加行级锁）
• 幻读（前后多次读取，数据总量不一致）：事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。（注重数据的数量，错误发生在 insert 和 delete，解决方法是加表级锁、临建锁）

快速理解脏读、不可重复读、幻读和MVCC - 云+社区- 腾讯云

数据库的封锁协议

• 一级封锁协议：事务 T 在修改数据 R 之前必须要对其加上 X锁（排他锁），直到事务结束才释放，事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）

  功能：可以防止丢失修改，并保证事务 T 是可恢复的

• 二级封锁协议：一级封锁协议基础上，事务 T 在读取 R 之前必须先对其加 S锁（共享锁），读完后可释放 S锁

  功能：除了防止丢失修改，还可以防止读脏数据（数据 R 加了 X锁，就加不上 S锁，事务就会停止，就不会读到脏数据了）

• 三级封锁协议：一级封锁协议基础上，事务 T 在读取数据 R 之前必须对其加 S锁，直到事务结束才释放

  功能：除了防止丢失修改和防止读脏数据，还可以防止不可重复读

说一下 ACID 是什么？

• Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

说一下 MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

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

说一下乐观锁和悲观锁？

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

怎样尽量避免死锁的出现

1. 设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁；
2. 设置按照同一顺序访问资源，类似于串行执行；
3. 避免事务中的用户交叉；
4. 保持事务简短并在一个批处理中；
5. 使用低隔离级别；
6. 使用绑定链接。

什么是 MVCC？

英文全称为Multi-Version Concurrency Control，乐观锁为理论基础的MVCC（多版本并发控制），MVCC的实现没有固定的规范。每个数据库都会有不同的实现方式。

MVCC 手段只适用于 MySQL 隔离级别中的读已提交和可重复读，而读未提交由于存在脏读，即能读到未提交事务的数据行，所以不适用 MVCC。原因是 MVCC 的创建版本和删除版本只要在事务提交后才会产生。串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

通过以上总结，可知，MVCC 主要作用于事务性的，有行锁控制的数据库模型。

快速理解脏读、不可重复读、幻读和MVCC - 云+社区- 腾讯云

MySQL 主从同步原理

• MySQL 主从同步过程

  MySQL 的主从同步中，只要有三个线程，master（binlog dump thread）、slave（I/O thread、SQL thread）

  1. 主节点 binlog：主从同步的基础时主库记录数据库的所有变更记录到 binlog 中。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容操作的一个文件
  2. 主节点 log dump 线程：当 binlog 有变动时，log dump 线程读取其内容并发送给从节点
  3. 从节点 I/O 线程接受 binlog 内容，将其写入 relay log 文件中
  4. 从节点的 SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性

  注：主从节点使用 binlog 文件 + position 偏移量来定位主从同步的位置，从节点会保存已接受到的偏移量，如果从节点发生宕机重启，则会自动从position 位置发起同步

  

• 由于 MySQL 默认的复制方式是异步的，主库把日志发送给从库就不关心从库是否处理，这样如果主库挂了，从库处理失败，这时从库升级为主库会造成日志丢失，从而产生两个概念：

  • 全同步复制：主库写入 binlog 后强制同步日志到从库，所有从库都执行完成后才返回客户端，性能受影响
  • 半同步复制：从库写入日志成功后返回 ACK 确认给主库，主库至少收到一个从库的确认就可认为写操作完成（多少个从库确认可自己设置）

如何做 MySQL 的性能优化？

• 为搜索字段创建索引。
• 避免使用 select *，列出需要查询的字段。
• 垂直分割分表。
• 选择正确的存储引擎。

MySQL索引优化

大表如何优化

• 限定数据的范围

  务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

• 读/写分离

  主库负责写，从库负责读；

• 垂直分区

  根据数据库里面数据表的相关性进行拆分，把一张列比较多的表拆分为多张表

• 水平分区

  保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

  尽量不要对数据进行分片，因为拆分会带来 逻辑、部署、运维的各种复杂度

分库分表之后,id 主键如何处理

因为要是分成多个表之后，我们需要一个全局唯一的 id 来支持。生成全局 id 有下面这几种方式：

1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
4. Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
5. 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：Leaf——美团点评分布式ID生成系统 。

MySQL 中一条查询 SQL 是如何执行的

1. 取得链接，使用使用到 MySQL 中的连接器。
2. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。
3. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
4. 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

Reference:

• https://mp.weixin.qq.com/s/BXSfeO1B_uaPy2tSdEvp-A