MySQL-面经项目里面有没有进行过MySQL调优？有。MySQL使用的是8.0，写操作特别慢开启慢查询，分析，

项目里面有没有进行过MySQL调优？

有。MySQL使用的是8.0，写操作特别慢
开启慢查询，分析，发现写操作锁等待时间没有，自己本身执行就很慢。
修改刷盘策略参数，innodb_flush_log_at_trx_commit为2
- 0：表示每秒将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。
- 1：表示每事务提交都将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。
- 2：表示每事务提交都将"log buffer"同步到"os buffer"但每秒才从"os buffer"刷到磁盘日志文件中。

有进行sql优化吗？

有，通过Explain，分析有没有使用到索引，优化语句。

索引分类

按数据结构层次
- B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。
- Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。
- R-Tree类型：MyISAM引擎支持，也就是空间索引的默认结构类型。
- T-Tree类型：NDB-Cluster引擎支持，主要用于MySQL-Cluster服务中。
按字段数量层次
- 单列索引（基于单个字段建立的索引都可以被称为单列索引）
- 唯一索引、主键索引、普通索引
多列索引
- 多种叫法：组合索引、联合索引、复合索引、多值索引...
前缀索引
- 创建索引时指定的length字段
- length：如果字段存储的值过长，选用值的前多少个字符创建索引
按功能逻辑层次
- 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。
- 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。
- 主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。
- 全文索引
  - 只能创建在CHAR、VARCHAR、TEXT等类型字段上
  - 字符数量必须大于3才生效
- 空间索引
  - GIS空间数据
  - GEOMETRY、POINT、LINESTRING、POLYGON四种数据类型字段建立
按存储方式层次
- 聚簇索引
  - 逻辑上连续且物理空间上的连续
- 非聚簇索引
  - 逻辑上的连续，物理空间上不连续

什么是索引下推与索引覆盖，区别是什么？

索引覆盖
- 要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖
- 不需要回表
索引下推
- 将Server层筛选数据的工作，下推到引擎层处理

回表概念

除了主键索引（默认主键索引作为聚簇索引），其他都是辅助索引
通过辅助索引（辅助索引存放的是指向聚簇索引的ID值），找到聚簇索引的ID值，然后再基于查询到的ID值，再走ID字段的主键索引，最终得到一整条行数据并返回
一条查询SQL经历了两次查询才获取到数据，这个过程则被称之为回表

索引失效情况

查询中带有OR会导致索引失效
模糊查询中like以%开头导致索引失效
字符类型查询时不带引号导致索引失效
- EXPLAIN SELECT * FROM zz_users WHERE user_name = 111
索引字段参与计算导致索引失效
- EXPLAIN SELECT * FROM zz_users WHERE user_id - 1 = 1;
- 运算也包括+、-、*、/、!.....等一系列涉及字段计算的逻辑
字段被用于函数计算导致索引失效
- EXPLAIN SELECT * FROM zz_users WHERE SUBSTRING(user_name,0,1) = "竹子";
- 使用SUBSTRING函数对user_name字段进行了截取
违背最左前缀原则导致索引失效
不同字段值对比导致索引失效
- EXPLAIN SELECT * FROM zz_users WHERE user_name = user_sex;
反向范围操作导致索引失效
- >、<、between、like、in...等操作时，索引是可以正常生效的
- NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等不生效
- 并非所有的正向范围操作都会走索引，例如IS NULL就不会走，它的反向操作：IS NOT NULL同样不会走
走索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查询，转而使用全表扫描的方式检索数据

索引是不是建的越多越好呢？

不是，一般控制在3，最多不能超过5
建立索引会生成磁盘文件，占用磁盘空间
增删改语句需要维护索引

mysql索引的数据结构

默认是B+树，有叶节点和叶子节点
叶节点：不会存储数据，仅存储指向叶子节点的指针，好处让叶节点存储更多元素，确保树的高度不会由于数据增长而变得很高
叶子节点：存储实际的数据。同时叶子节点之间都有一根单向指针指向下一个节点，使得叶子节点之间形成一个单向链表结构，方便范围取值
MySQL索引底层的真正结构实际是B+Tree的变种，叶子节点之间是互存指针的，所有叶子节点是一个双向链表结构

B+树和B树的区别

B+树叶子节点是链表，适用于范围查询
B+树的非叶子节点只存索引不存数据，数据只存在叶子节点。B树所有节点都存数据和索引。

mysql日志有哪些？

bin-log、redo-log、undo-log、err-log、slow-log、relay-log、genera-log

Bin-log、Redo-log和Undo-log介绍一下？

Bin-log 主要记录MySQL数据库的所有写操作（增删改）
Redo-log 记录innodb的增删改操作
Undo-log 记录旧版本数据

Bin-log和redo-log的区别？：

用于主从复制、备份，redo-log用于宕机等的数据恢复
日志的写入方式:redo-log 两个文件循环写 bin-log是追加写
redo-log是innodb专享，bin-log所有引擎通用

两阶段提交？为什么需要两阶段提交呢？

先记录redo-log日志，标记为prepare状态，提交之后再写bin-log，然后更新redo-log为commit状态
防止redo-log记录到，bin-log没有记录到，保证数据的一致性。
写SQL执行流程
- ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
- ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。
- ③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：
  - 错误：抛出1064错误码及相关的语法错误信息。
  - 正确：将SQL语句交给优化器处理，进入第④步。
- ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
- ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。
- ⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：
  - 存在：
    - ⑦直接对缓冲区中的数据进行写操作。
    - ⑧然后利用Checkpoint机制刷写到磁盘。
  - 不存在：
    - ⑦根据执行计划，调用存储引擎的API。
    - ⑧发生磁盘IO，对磁盘中的数据做写操作。
- ⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。
- ⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。

redo-log 怎么刷盘？

写入redo-log-buffer缓冲区
由一个参数控制 innodb_flush_log_at_trx_commit
- 0：每间隔一秒时间刷写一次日志到磁盘（有事务提交的情况下）。
- 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。
- 2：刷写的时机交给操作系统控制（性能最佳）。

mysql事务四大特性：

原子性：要么成功，要么失败
一致性：数据只允许从一个一致性状态变化为另一个一致性状态
隔离性：事务相互隔离
持久性：事务一旦被提交，它会保持永久性

事务隔离级别和实现原理

1、读未提交：基于「写互斥锁」实现，写同一数据时会互斥，但读操作却并不是互斥的 2、读已提交：写操作使用写互斥锁，读操作是MVCC机制 3、可重复读：读是MVCC机制，写是临键锁 4、序列化：读操作加共享锁，写操作加临键锁

什么事幻读、脏读、不可重复读

脏读：一个事务读到了其他事务还未提交的数据
不可重复读：一个事务中，多次读取同一数据，先后读取到的数据不一致
幻读：A事务把表中所有的男性更新为女性，然后B事务王表中插入一条男性，A事务更新完成，查看男性发现又有一条

MVCC解决什么问题

解决读-写并发冲突，不会让其他事务读取正在修改的数据，而是读取上一次提交的数据

MVCC实现原理

隐藏字段、undo-log、readview

隐藏字段
- 隐藏主键 - ROW_ID
- 删除标识 - Deleted_Bit
- 事务ID - TRX_ID
- 回滚指针 - ROLL_PTR
undo-log
- 存储旧版本的数据
- 版本链
readView
一个事务在尝试读取一条数据时，MVCC基于当前MySQL的运行状态生成的快照，也被称之为读视图，即ReadView

MySQL-面经