MySQL面试题，面不上算我输！
点击上方"数据与人", 右上角选择“设为星标”分享干货，共同成长！
话不多说，直接上干货。
SQL 语句执行流程
MySQL 大体上可分为 Server 层和存储引擎层两部分。整体架构图
MySQL的架构不同于其他数据库，它的插件式的存储引擎架构可以在多种不同场景中应用并发挥良好作用。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。各层介绍：连接层最上层是客户端，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。服务层
引擎层存储引擎负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。show engines:查看所有的数据库引擎
show variables like '%engine%' 查看默认的数据库引擎
MyISAM和InnoDB对比
存储层数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。流程图：
SQL执行顺序
BinLog、RedoLog、UndoLog
binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。逻辑日志：可以理解为记录的就是sql语句.
物理日志：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更 。
数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。
1：每次 commit 的时候都要将 binlog 写入磁盘；
N：每N个事务，才会将 binlog 写入磁盘。
ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。
MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog
从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件 。
主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。
从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！
B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。
B+树中更有利于对数据扫描，可以避免 B 树的回溯扫描。
提高查询速度 ；
加速表与表的连接 ；
显著的减少查询中分组和排序的时间；
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作 ；
索引需要磁盘的空间进行存储，磁盘占用也很快；
当对表中的数据进行 CRUD 的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作性能。
频繁更新的数据，不宜建索引；
数据量小的表没必要建立索引。
数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性 ；
频繁 group by、order by 的列建议生成索引。可以大幅提高分组和排序效率 ；
经常用于查询条件的字段建议生成索引。通过索引查询，速度更快。
隐式类型转换：比如 select * from t where name = xxx , name 是字符串类型，但是没有加引号，所以是由 MySQL 隐式转换的，所以会让索引失效 3、当语句中带有 or的时候：比如 select * from t where name=‘sw’ or age=14
不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只 where 了 C 或 B 或只有 B,C
一致性（Consistency）：理解成一串对数据进行操作的程序执行下来，不会对数据产生不好的影响，比如凭空产生，或消失；
隔离性（Isolation，又称独立性）：隔离性的意思就是多个事务之间互相不干扰，即使是并发事务的情况下，他们只是两个并发执行没有交集，互不影响的东西；当然实现中，也不一定需要这么完整隔离性，即不一定需要这么的互不干扰，有时候还是允许有部分干扰的。所以 MySQL 可以支持 4 种事务隔离性；
持久性（Durability）：当某个操作操作完毕了，那么结果就是这样了，并且这个操作会持久化到日志记录中。
不可重复读(non-repeatable read)：不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了，只需要锁住满足条件的记录 ；
幻读(phantom read)：事务 A 先修改了某个表的所有纪录的状态字段为已处理，未提交；事务 B 也在此时新增了一条未处理的记录，并提交了；事务 A 随后查询记录，却发现有一条记录是未处理的造成幻读现象，幻读仅专指新插入的行。幻读会造成语义上的问题跟数据一致性问题；
在可重复读 RR 隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。要用间隙锁解决此问题。
- 从 MyISAM 所支持的锁中也可以看出，MyISAM 是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。
间隙锁和行锁合称 NextKeyLock，每个 NextKeyLock 是前开后闭区间。
查找过程中访问到的对象才会加锁。
索引上的等值查询，给唯一索引加锁的时候，NextKeyLock 退化为行锁。
索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock 退化为间隙锁。
唯一索引上的范围查询会访问到不满足条件的第一个值为止。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
缓冲池(buffer pool) 是一种常见的降低磁盘访问 的机制；
缓冲池通常以页(page 16K)为单位缓存数据；
缓冲池的常见管理算法是 LRU，memcache，OS，InnoDB 都使用了这种算法；
InnoDB 对普通 LRU 进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，该页被访问，才进入新生代，以解决预读失效的问题页被访问。且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题。
按照主键 ID 将 A 数据一行行读取同步到表 B；
用表 B 替换表 A 实现效果上的瘦身。
InnoDB 引擎由于 MVCC 的原因，需要把数据读出来然后累计求和
性能来说，由好到坏：count(字段) < count(主键id) < count(1) ≈ count(*)，尽量用 count(*)。
子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。
两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。
查询用 not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。not exists 都比 not in 要快。
使用 explain 执行计划查看索引使用情况 ；
重点关注(一般情况下根据这 4 列就能找到索引问题)：key(查看有没有使用索引)、key_len(查看索引使用是否充分)、type(查看索引类型)、Extra(查看附加信息：排序、临时表、where 条件为 false 等)；
根据上 1 步找出的索引问题优化 sql 5、再回到第 2 步。
VARCHAR 的长度只分配真正需要的空间 。
尽量使用 TIMESTAMP 而非 DATETIME 。
单表不要有太多字段，建议在 20 以内。
避免使用 NULL 字段，很难查询优化且占用额外索引空间。字符串默认为''。
垂直分表：一般将不常用字段跟数据较大的字段做拆分。
水平分表：根据场景选择什么字段作分表字段，比如淘宝日订单 1000 万，用 userId 作分表字段，数据查询支持到最近 6 个月的订单，超过 6 个月的做归档处理，那么 6 个月的数据量就是 18 亿，分 1024 张表，每个表存 200W 数据，hash(userId)%100 找到对应表格。
ID生成器：分布式ID 需要跨库全局唯一方便查询存储-检索数据，确保唯一性跟数字递增性。
优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；
缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等 。
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；
缺点：会产生大量的日志，尤其是` alter table ` 的时候会让日志暴涨
