mysql由浅到深

1、InnoDB和MyISAM的区别？
MyISAM：最小粒度为表锁、不支持事务、不支持外键、非聚簇索引、支持fullText类型全文索引、不支持MVCC、使用MyISAM引擎的表会在本地生成三个磁盘文件（frm、myd、myi）。
InnoDB：最小粒度为行锁、支持事务、支持外键、聚簇索引、不支持fullText类型全文索引但是InnoDB支持sphinx插件全文索引效果更好、支持MVCC、使用InnoDB引擎的表会在本地生成两个磁盘文件（frm、idb）。
注意：
索引支持 ：为什么InnoDB支持聚簇索引而MyISAM不支持聚簇索引就是因为MyISAM表数据和索引数据在两个文件中而InnoDB表数据和索引数据在一个文件中。
事务机制：InnoDB中由两个日志文件undolog,redolog当在redolog遇到故障时可以通过redolog日志恢复数据而MyISAM无这个日志文件会丢失数据。

2、什么是数据库范式？为什么要反范式？
数据库范式是为了减少数据的冗余，提高了数据的一致性，反范式则是提高了查询性能,简化了查询语句。
数据库范式是设计数据库表结构的一组规则,保证数据不会冗余储存,提高数据库的性能和可靠性,反范式的设计是为了优化查询的速度，减少冗余的重复数据。通常设计表的时候应该要遵循一定的准则,比如每张表都应该有一个主键,核心表的主键尽量不要使用自增，使用 uuid 或自定义的主键,每张表的数据应该做一个备份,查询时尽量用主键关联查询，避免数据的冗余，在一些特定的场景下,可以使用反范式进行设计，提升查询性能。

3、说一下事务的隔离级别有哪些？ 它们分别解决了什么问题？

3.1、事务四大特性：
原子性：事务中的全部操作在数据库中是不可分割的，要么全部成功，要么全部失败。
一致性：一个事务执行前和执行后数据库仍是一个完整状态。
隔离性：多个并发事务同时对其数据进行修改互不干扰。
持久性：事务处理结束之后数据的修改是永久的。

3.2、事务隔离级别：
读未提交：一个事务读到了另一个事务还没有提交的数据。
读已提交：一个事务要等另一个事务提交后才能读取数据。
可重复读：同一事务下，事务在执行期间，多次读取同一数据时，能够保证读取到的数据是一致的。
串行化：它是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率最低，比较耗费数据库性能，一般不推荐使用。

总结：

mysql默认隔离级别：可重复读。

4、什么是脏读、脏写、幻读、不可重复读？
脏读：一个事务处理过程中读到另一个未提交的事务中的数据。
脏写：在一个事务内覆盖掉了另一个事务内未提交的数据。
幻读：在同一个事务内，每次查询的条件一样，都会有不同的结果。
不可重复读：在同一个事务内，由于其他事务的修改，每次查询的结果都不一样。

5、如何理解MVCC。
MVCC（多版本并发控制）：同一条数据允许在同一时刻由不同的事务或线程对它进行修改，主要控制的是读取的结果。
多版本：一条数据修改生成版本链
版本链：undo log(回滚日志)和mysql针对每行数据的隐藏字段->事务ID(trx_id)加上个版本的指针。

ReadView（读快照）：每当有一个事务到来的时候，Mysql 就会创建一个ReadView（读快照），相当于相机拍下了那一时刻的事务状况，ReadView记录了在当前事务之前有哪些事务处在活跃状态，有哪些事务的历史记录允许被看到等等

ReadView四个值：
min_id:最小事务id,这个值存在的意义就是为了提高查询效率。
max_id:最大事务id。
cur_trx_id:当前事务id。
active_id_list:当前活跃的事务列表。

数据可见性：
比最小事务id小的一定读。
比最大事务id大的一定不读。
当前事务id一定读。
看在不在活跃事务列表中，在就不读，不在就读。

可重复读的readView只会在第一次查询的时候生成，而读已提交在每一次查询的时候都会生成一个新的readView。

幻读：在同一个事务内，每次查询的条件一样，都会有不同的结果。解决方案：可以不让他插入，比如通过间隙锁+行锁。

行锁：锁了一行数据，再去改这一行数据就会卡住。
表锁：索引失效就会产生表锁。
间隙锁：当进行范围修改数据比如a>1 and a< 9 这个范围修改数据现在想在这个中间插入数据就会卡住。

什么是索引？
索引是数据库中快速查询的一种数据结构，减少了系统中的 io。

索引是越多越好吗？
不是，索引是优化查询的一种结构，但是创建索引也需要额外的内存。有索引也会影响数据的插入删除更新等操作的速度，当进行这些操作时，也会更新响应的索引。所以索引过多也会影响效率。

建立索引注意事项：
创建在唯一的列上。
创建在连接的字段上：提高连接效率。
最常用的字段放在最左边：最左匹配原则。
字符串有规律且长的话可以创建前缀索引。

InnoDB中的索引类型：
聚簇索引：（我理解就是主键索引）将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
主键索引：主键创建索引，主键索引的叶子节点保存了行数据
联合索引：按照最左匹配原则是最佳选择，当然如果不是按照顺序走索引就会产生索引下推（5.6版本之后引入索引下推）
唯一索引：唯一性约束
普通索引：普通索引的叶子节点上存的是主键

额外：
索引覆盖：查询的字段在索引中都存在就是索引覆盖
回表：当查询的字段在使用的索引中不存在就会触发回表机制拿着主键id到主键索引的叶子节点上去找相应的数据。

示例：
select id,name from user where num = 20 会先走普通索引然后进行回表根据id查主键索引。

B树和B+树的区别：
B树是非叶子节点也存数据，B+树是叶子节点村数据。

为什么B树：因为非叶子节点放的值越多层数就会越高，每一层筛选就是一次磁盘IO会慢，性能更低。
为什么不用二叉平衡树也是因为二叉的层数更高对范围查询支持比较差。
为什么使用B+树：B+树的特点就是矮胖，所以是多叉的，比较低所以磁盘IO次数少所以快。所有叶子节点用双向链表连接。

什么是回表？如何减少回表次数？
回表是在非聚簇索引中，需要查询的字段不在索引中。解决办法，索引覆盖。

覆盖索引是什么？索引下推是什么？

覆盖索引是指一个索引包含了查询所需的所有字段，而不需要回表查询原始数据。
索引下推是指在使用联合索引时，将过滤条件下推到索引层，从而减少回表的次数。

索引失效场景：
1、不满足最左匹配原则
2、索引列上有计算（+、-、*、/、!=、<>、is null 、is not null、or 、not in）
3、索引列用了函数（sum()、round()、substr()）
4、字段类型不同(字符串字段不加引号，整形加引号)
5、使用了select *
6、like左边包含%
7、列对比（假如我们现在有这样一个需求，过滤出表中某两列值相同的记录。比如user表中id字段和height字段，查询出这两个字段中值相同的记录）
8、使用or关键字（只要由一个字段不加索引就会失效）
9、not in和not exists
10、order by的坑（没加where或limit不走索引、对不同的索引做order by、不满足最左匹配原则）

MySQL索引一定遵循最左前缀匹配吗？
不是，普通索引需要遵循，聚簇索引和全文索引不需要遵循。

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

mysql优化：
硬件和操作系统的优化：
架构设计优化：创建mysql集群、读写分离、分库分表、热点数据加缓存
mysql程序优化：配置文件修改（mysql连接数大小配置）
sql优化：慢sql用慢sql查询分析工具定位分析、通过执行计划分析（explain->type key rows filtered ext）、使用show profiles 分析（show profiles 是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果）

查看是否开启
show variables like “%profiling%”;
开启
set profiling = 1;
开始分析
先执行要分析的SQL语句
执行show profiles;会出现如下结果

总结：
1、sql查询一定要基于索引查询
2、避免索引失效
3、where中like放在最右边->最左匹配原则
4、联合索引命中越多越好
5、尽量使用索引完成排序
6、尽量不用select *
7、小表驱动大表（连接次数不一样）
8、用联合查询代替子查询
9、如果group by中的字段用的比较频繁也可以加索引
10、用union all代替union,因为union all包含重复数据，union不包含重复数据，在没有去重的前提下，使用union all的执行效率要比union高
11、join连接的表不宜过多

为什么sql查询时不用select * ?
用select * 全字段增加查询分析器分析成本
增加字段容易与resultMap配置不一致
无用字段增加网络消耗，尤其是text类型的字段

从架构的方面考虑如果用到一些常用的字典可以存到缓存里面，但是要保证一个双写一致性。如果下文多次使用到的一些值可以把他存到一个list里直接去拿就可以，不用再去查询库（避免多次查询）。

limit优化：
select * from user limit 100000 20查询慢可以改为select * from user where id > 99999 limit 20 这样会快一点但是必要条件是id自增，需要前端吧上一次请求的最后一个id（99999）传过来。

时间类型选择：（我们一般使用timestamp就够了）
datetime：占8字节
timestamp:占4字节
date:占3字节

每个字段占用大小的选择。

固定长度的字符串可以用char()不用varchar()，应为varchar()是变长的要维护多余的长度，cahr()是定长的。比如性别，手机号这些就完全可以用char()来保存

关联查询如果关联两个表的字段类型不一致就会索引失效，查询变慢。

范围查询尽量放到最后。

可重复读锁表：
如果一个update操作中where后面的字段没有建索引读已提交隔离级别下是不会卡住的，但是在可重复读隔离级别下是会卡住的，所以一般where后面跟的都是主键或唯一索引

可重复读解决了不可重复读问题是通过MVCC解决的。
MVCC(多版本并发控制)：支持并发修改下的快照读

udate user set name=‘李四’ where age=20使用的是表锁还是行锁？
首先看使用的是什么存储引擎，如果是InnoDB看where后的ages是否创建了索引，如果创建了索引及时使用的行锁，如果没有创建索引则使用的是表锁。如果是myIsam则使用的是表锁。

redis为什么快？
基于内存（为什么基于内存就快？离CPU近，不用磁盘IO）、IO多路复用（一个线程监控多个IO连接）、单线程减少上下文切换。

MVCC:undo log版本链加上readView构成一个数据过滤的逻辑。

快照读：根据undo log版本链查询数据的过程叫做快照读，比如可重复读读到的不一定是最新的数据查到的是readView这一时刻瞬间生成的数据快照，其他最提交的数据是看不到的。（普通select都叫快照读）。查询出来最新的数据叫做当前读（update,insert,select for uodate,delete都是当前读）。

mysql有没有解决幻读的问题》
只解决了快照读下的幻读问题，当前读并未彻底解决（为什么说并未彻底解决，如果在快照读中混入当前读就会出现幻读那要解决可以加行锁）。
间隙锁主要解决幻读的问题，间隙锁之间是不互斥的，但是间隙锁和插入语句是互斥的。

读已提交（RC）发生幻读怎么解决？
幻读其实并不是一个服务性故障的问题，只是一种现象，说白了插入了一个新的数据就应该被读到的。

mysql底层的数据是通过数据也存储的 （extra 1MB，其中有64个数据页（page）一个数据页的大小为16KB）

mysql读取数据时时通过数据页去读的，会以数据页为单位加载比如一个数据页中有1-100的数据，他会全部拿到内存中操作，而不是只拿一条

MySQL–SQL执行过程

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。

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

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

死锁问题 行锁的理解？

死锁就是两个或多个线程并发访问数据库，造成两个个或多个线程相互等待对方的资源，而造成的相互等待问题。
行锁是innodb 存储结构，是一种粒度比较小的锁。有行互斥锁，和行共享锁。因为粒度比较小，所以死锁的概率也是比较大。此外行锁也会额外的占用系统资源

都有哪些日志，分别有什么作用？
1、错误日志：主要记录启动运行数据库过程中的错误,查询错误。
2、查询日志：记录所有的查询日志包括增删改查。
3、慢查询日志：查询时间超过阈值的记录一下。
二进制日志：记录所有数据库经过更改的记录，主要作用与数据库的备份，主从复制等。
4、事务日志:支持事务的原子性和一致性

什么是排他锁、共享锁、意向锁？

排他锁也称独占锁,或者写锁,当一个线程拥有该锁，则其他线程进入等待。只有在该锁释放时其他线程才能进行获取该锁的资源。
共享锁，也称读锁，允许多个线程获取相同资源的锁，但是会阻塞其他事务的排他锁，只有共享锁的释放，其他事务才能获取该资源的排他锁。
意向锁,优化锁机制的辅助机制。例如当某个事务获取某个表的排他锁时，会先获取该表的意向排他锁，意向锁不会阻塞其他事务的意向锁获取，但是会阻塞行锁或表锁。

mysql底层分别是怎么实现ACID的？

原子性:由undo log日志保证，它记录了需要回滚的日志信息，事务回滚是撤销已经执行成功的 sql
一致性：由其他三大特新保证、程序代码要保证业务员上的一致性I隔隔离性：由MVCC （多版本并发控制）来保证
持久性:由内存+ redo log来保证，mysql 修改数据同时在内存和 redo log 记录这次操作，宕机的时候可以从 redo log 恢复