Mysql之八股文数据库三大范式第一范式：每个列都不可以再拆分第二范式：在第一范式的基础上，非主键的列完全依赖于主键

Mysql基础

数据库三大范式

第一范式：每个列都不可以再拆分
第二范式：在第一范式的基础上，非主键的列完全依赖于主键，而不能是依赖主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

Mysql架构

mysql总体上可以分为server层和存储引擎层

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

连接器

连接器主要负责客户端和数据库服务的连接工作。
可以通过show processlist查询当前可用的连接进行的状态。
客户端连接后，如果长时间没有通讯连接，就会自动断开链接，默认wait_timeout参数的值是8小时

分析器

分析器主要通过词法分析你的SQL语句，用来告诉MySQL你要干什么。
这个时候，如果你的sql语句有语法错误，就会报异常：“You have an error in your SQL syntax”

优化器

优化器主要是通过你的sql语句，选择一种最优的方式，告诉MySQL该如何执行该语句。

执行器

执行器通过操作引擎，将SQL语句执行的结果，返回给客户端

查询缓存

查询缓存顾名思义是用来缓存查询结果的。
为什么放在最后来说，是因为MySQL8.0版本已经没有了这个功能。这个功能弊大于利，当查询结果命中查询缓存时，会直接返回结果。但大多数时候，我们的使用场景更新的频率会非常频繁，当某一个表中有一条更新数据时，会将该表的查询缓存结果全部清空，效率会非常低。
可以使用在一些系统配置表，等更新不频繁的表中。当然我们可手动选择是否开启，参数是：query_cache_type.

Sql执行顺序

FORM 子句，组装来自不同数据源的数据
WHERE 子句基于指定的条件对记录进行筛选
GROUP BY 子句将数据划分为多个分组
使用聚合函数进行计算
使用having子句筛选分组
计算索引的表达式
使用order by对结果集进行排序
select 获取相应列
limit 截取结果集

Mysql存储引擎，myisam 和 innodb 的区别

锁粒度方面：由于锁粒度不同，Innodb 比 Myisam 支持更高的并发；innodb的锁粒度为行锁，myisam的锁粒度为表锁，行锁需要对每一行进行加锁，所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说更容易死锁。
可恢复性上：由于Innodb是有事务日志的，所以在产生由于数据库崩溃等条件后，可以根据日志文件进行恢复。而Myisam 则没有事务日志。
查询性能上：Myisam要优于Innodb，因为innodb在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而Myisam可以直接定位到数据所在的内存地址,可以直接找到数据
表结构文件上:myisam的表结构文件包括：frm(表结构文件)，.MYI(索引)，.MYD(数据)；而innodb的表数据文件为：ibd 和 frm(表结构定义)。

Mysql事务

什么是事务？

数据库事务指的是一组数据操作，事务内的操作要么是全部成功，要么是全部失败。

事务具有A(原子性)、C(一致性)、I(隔离性)、D(持久性)四个特性；

原子性：当前事务的操作要么同时成功，要么同时失败。原子性由undo log日志来保证，因为undo log记载着数据修改前的信息。

隔离性：在事务「并发」执行时，他们内部的操作不能互相干扰。如果多个事务可以同时操作一个数据，那么就会产生脏读、重复读、幻读的问题。

持久性：一旦提交了事务，它对数据库的改变就应该是永久性的。说白了就是，会将数据持久化在硬盘上。而持久性由redo log 日志来保证，当我们要修改数据时，MySQL是先把这条记录所在的「页」找到，然后把该页加载到内存中，将对应记录进行修改。

一致性：数据库在事务执行前后状态都必须是稳定的或者是一致的，就是说事务开始和结束后，数据库的完整性不会被破坏。

关于事务的隔离基本要解决的实际问题是什么？

脏读

脏读是指的是读到了其他事务未提交的数据，未提交数据意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束钱的任意时刻读到的同一批数据都是一致的。通常针对数据的更新(update)操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（update）操作。

幻读

幻读是针对数据插入（insert）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务的隔离级别

读未提交
读已提交
可重复读
串行化

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是mysql的默认隔离级别。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

mvcc是什么？

就是多版本并发控制，MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

mvcc只在读已提交和可重复度两个隔离级别下工作。其他的2个隔离级别和mvcc不兼容，因为读未提交总是读取到最新的数据行，而不是符合当前事务版本的数据行，而串行化则对对所有读取的行都加锁。

Redo log、bin log、undo log

Innodb中通过undo log实现了数据的多版本，而并发控制通过锁来实现。

undo log 除了实现mvcc外，还用于事务的回滚。Mysql innodb中存在多种日志，除了错误日志、查询日志外，还有很多和数据持久性、一致性有关的日志。

undo log 除了记录redo log外，当进行数据修改时还会记录undo log，undo log 用于数据的撤回操作，它记录了修改的反向操作，比如。插入对应删除，修改对应修改为原来的数据，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

binlog，是mysql服务层产生的日志，常用来进行数据恢复、数据库复制，常见的mysql主从架构，就是采用slave同步master的binlog实现的，另外通过解析binlog日志能够实现mysql到其他数据源（如ES）的数据复制。

redo log记录了数据操作在物理层面的修改，mysql使用了大量缓存，缓存存在与内存中，修改操作时候回直接操作内存，而不是立刻修改磁盘，当内存和磁盘的数据不一致时候，称内存的中数据为脏页。为了保证数据的安全性，事务进行中会不断的产生redo log，在事务提交时进行一次flush操作，保存到磁盘中，redo log是按照顺序写入的，磁盘的顺序读写的速度大于随机读写。当数据库或者主机失效重启时，会根据redo log进行数据的恢复，如果redo log中有事务提交，则进行事务提交修改数据。这样实现了事务的原子性、一致性、持久性。

Mysql索引

什么是索引

索引是帮助Mysql高效获取数据排好序的数据结构。

索引有哪几种类型

从存储结构划分：--这里描述的索引是索引存储保存的形式

BTree索引（B-索引或者B+索引）
Hash索引
full-text全文索引

从应用层次来分：

主键索引：数据列不允许重复，不允许为null，一个表只能有一个主键
唯一索引：数据列不允许重复，允许为null值，一个表允许多个列创建唯一索引
普通索引：基本的索引类型，没有唯一性的限制，允许为null值
复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，Innodb的聚簇索引其实就是在同一结构中保存了B-tree索引和数据行。
非聚簇索引

根据数据的物理顺序与键值的逻辑顺序关系：

聚集索引
非聚集索引

Myisam索引与Innodb索引的区别

Innodb是聚簇索引，Myisam索引是非聚簇索引
Innodb的主键索引的叶子节点存储这行数据，因此主键索引非常高效
Myisam索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
Innodb非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

什么是最左前缀原则？什么是最左匹配原则？

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where字句中使用最频繁的一列放在最左边。最左前缀匹配原则，mysql会一直向右匹配到遇到范围查找（>、<、between、like）就会停止匹配。

=和in可以乱序，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

聚簇索引与非聚簇索引

在innodb里面，索引B+Tree的叶子节点存储了整行数据的主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+Tree的叶子节点存储了主键的值是非主键索引，也被称之为非聚簇索引、二级索引

聚簇索引与非聚簇索引的区别：

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键
对于Innodb来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，称为回表。第一次索引一般是顺序IO,回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于全表扫描
通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次，当然，如果是覆盖索引查询的话，查一次即可
注意：Myisam无论主键索引还是二级索引都是非聚簇索引，而Innodb的主键索引是聚簇索引,二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

hash索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1)，一般多用于精确查找。

B+树索引

B+树的索引又可以分为主索引和辅助索引。其中主索引为聚簇索引，辅助索引为非聚簇索引。聚簇索引是以主键作为B+ 树索引的键值所构成的B+树索引，聚簇索引的叶子节点存储着完整的数据记录；非聚簇索引是以非主键的列作为B+树索引的键值所构成的B+树索引，非聚簇索引的叶子节点存储着主键值。所以使用非聚簇索引进行查询时，会先找到主键值，然后到根据聚簇索引找到主键对应的数据域。上图中叶子节点存储的是数据记录，为聚簇索引的结构图。

Hash索引和B+树的区别？

因为两者数据结构上的差异导致它们的使用场景也不同，哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下，会选择使用B+树索引。

哈希索引不支持排序，因为哈希表是无序的。
哈希索引不支持范围查找。
哈希索引不支持模糊查询及多列索引的最左前缀匹配。
因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点

数据库为什么使用B+树而不是B树？

B树适用于随机检索，而B+树适用于随机检索和顺序检索
B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快。
B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便
B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不在叶子节点，在内部节点就已经找到。

那在什么情况适合使用B树呢，因为B树的内部节点也可以存储值，所以可以把一些频繁访问的值放在距离根节点比较近的地方，这样就可以提高查询效率。综上所述，B+树的性能更加适合作为数据库的索引。

Mysql优化

查询语句无论是使用哪种判断条件 等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1
不要直接使用 SELECT *，而应该使用具体需要查询的表字段。
为每一张表设置一个 ID 属性
避免在 WHERE 字句中对字段进行 NULL 判断
避免在 WHERE 中使用 != 或 <> 操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引
对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等
拆分大的 DELETE 或 INSERT 语句
选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用 NOT NULL
进行水平切割或者垂直分割

mysql锁

锁粒度划分：表锁、页锁、行锁

思想划分：乐观锁、悲观锁

乐观锁

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

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

悲观锁

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

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

行锁

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎。

共享锁

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。

排他锁

排他锁 exclusive lock（也叫 writer lock）又称写锁，排它锁是悲观锁的一种实现。

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

记录锁-record lock

单条索引上加锁，record lock 永远锁的是索引，而非数据本身，如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

间隙锁-Gap lock

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

Next-Key Lock

这个锁机制其实就是前面两个锁相结合的机制，既锁住记录本身还锁住索引之间的间隙。

innodb 行锁优化建议

特别注意的事情，innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的

(1) 要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；

b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

(2) 由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

Mysql日志相关

mysql的binlog有几种录入格式？分别有什么区别？

有三种格式，statement、row、mixed

statement模式下，每一条会修改数据的SQL都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高了性能。由于Sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row级别下，不记录Sql语句上下文相关的信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动（比如 alter table），因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

Mysql其他总结

什么是临时表，何时删除临时表？

mysql在执行SQL语句的过程中，通常会临时创建一些存储中间结果集的表，临时表只对当前连接可见，当连接关闭后，临时表会被删除并释放所有表空间。

临时表分为两种：一种是内存临时表（memory存储引擎），一种是磁盘临时表（Myisam引擎）。

产生临时表的几种情况：

使用union查询
from的子查询
explain执行计划结果，extra列中的"Using Temporary"
distinct查询并且加上 order by 时候

使用union 和 union all 时候需要注意什么？

通过union连接的SQL分别单独取出的列数必须相同。使用union时候，多个相等的行将会被吞并，相当于对结果集做了一下去重，由于合并比较耗时，一般不直接使用union，使用union查询会产生临时表。而使用union all，则不会排重，返回所有的行。