MySQL
Mysql索引、Mysql优化、日志
执行一条 select 语句，期间发生了什么？
第一步：连接器
连接的过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的
第二步：查询缓存
对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。
所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。
对于 MySQL 8.0 之前的版本，如果想关闭查询缓存，我们可以通过将参数 query_cache_type 设置成 DEMAND
第三步：解析 SQL
解析器
第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方面后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
第四步：执行 SQL
预处理器
我们先来说说预处理阶段做了什么事情。
- 检查 SQL 查询语句中的表或者字段是否存在；
- 将 select * 中的 * 符号，扩展为表上的所有列
优化器
经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划，这个工作交由「优化器」来完成的。
优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
逻辑优化查询：怎么查询效率更高
物理优化查询：索引等
执行器
经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。
接下来，用三种方式执行过程，跟大家说一下执行器和存储引擎的交互过程（PS ：为了写好这一部分，特地去看 MySQL 源码，也是第一次看哈哈）。主键索引查询
全表扫描
索引下推
总结
执行一条 SQL 查询语句，期间发生了什么？
连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
Mysql explain 执行计划
事务的四大特性？
事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
- 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
- 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
- 隔离性。跟隔离级别相关，如
read committed，一个事务只能读到已经提交的修改。 - 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
数据库范式
一范式、二范式、三范式、巴斯-科德范式、第四范式、第五范式（完美范式）
「第一范式」：数据库中的字段具有「原子性」，不可再分，并且是单一职责
| 国家 | 省 | 市 | 区 | 街道 |
|---|---|---|---|---|
| 中国 | 上海 | 上海 | 宝山区 | 上大路99号 |
「第二范式」：「建立在第一范式的基础上」，第二范式要求数据库表中的每个实例或行必须「可以被唯一地区分」。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键。（任何字段只能依赖主键）
| 订单编号 | 商品编号 | 用户ID | 下单时间 | 商品名称 |
|---|---|---|---|---|
| 4654641666 | 4553 | 2424 | 2022-07-04 | 洗衣机 |
很显然，商品名称和订单无关，商品名称是依赖商品编号的，这是部分依赖！不应该放在同一张表格里面，应该拆成订单表和商品表。
「第三范式」：「建立在第一，第二范式的基础上」，确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息
其中总价是通过前面两个字段计算得到，数据库不要有数学计算的操作，业务需要的时候通过代码进行计算，不要入库
| 商品 | 单价 | 数量 | 总价 |
|---|---|---|---|
| 12456 | 10 | 50 | 500 |
但是在我们的日常开发当中，「并不是所有的表一定要满足三大范式」，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的
Order By 为什么会导致索引失效
字段： a\b\c\d,索引：b\c\d
1
EXPLAIN SELECT * FROM t1 ORDER BY b,c,d;
基于锁的分类：共享锁、排他锁
基于锁的粒度：行级锁、表级锁、页级锁、记录锁、间隙锁、临键锁
基于锁的状态：意向共享锁、意向排他锁
表级锁具体内容
表锁
1
2
3lock tables 表名…… read/write
lock tables- 表共享读锁
- 表独占写锁
元数据锁（共享锁）：在select和update时候都会自动加上
- 系统自动，无需显示使用，访问一张表会自动加上。当这张表上有未提交的事物，就不能修改表结构，被阻塞
意向锁
行锁
- 共享锁：
- 排他锁：
间隙锁（一个范围，不包含该记录），确保索引间隙不变，防止其他事务在这个间隙进行insert导致幻读
临键锁：行锁+间隙锁，同时锁住数据和间隙
MySQL数据库中什么情况下索引无法使用
不符合最左匹配原则
字段进行了隐私数据类型转化
走索引没有全表扫描效率高
为什么B+树比B树更适合实现数据库索引？
由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。
B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。
B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
MyISAM和InnoDB
myISAM InnoDB 不支持事务，每次查询都是原子的 ACID，事务，支持四种隔离级别 表锁 行锁，支持并发写 无MVCC MVCC 三个文件：索引文件、表结构文件、数据文件 除了主键以外，其他索引只存储索引内容 存储了表的总行数 没有存表行数 索引数据分开 主键索引文件存了所有的数据
注意：MyISAM引擎的主键索引，B+数的叶子节点存储的是主键和
什么是MVCC
MVCC 多版本并发控制，读取的时候通过快照的方式将数据存下来，这样读锁写锁不冲突，不同事物session会看到自己的版本链
MVCC只在已提交读和可重复读两个隔离级别下工作
InnoDB在每行数据都增加三个隐藏字段，一个唯一行号，一个记录创建的版本号，一个记录回滚的版本号。
聚簇索引记录中有3个隐藏列 trx_id 和roll_pointer和ROW_ID
- trx_id：用来存储每一次对某条聚簇索引记录修改时的事务id
- roll_pointer：修改时，将老版本写入undo log中，roll_pointer存了一个指针，指向上一个版本记录的位置，通过它来获得上一条记录的信息
- ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成隐藏字段
已提交读和可重复读的区别在于他们生成的ReadView策略不同
- 开始事务时创建ReadView，维护事务的id（即未提交的事务），排成一个数组
- 已提交读：事务每次查询开始都声称一个独立的ReadView
- 可重复读：第一次读的时候生成一个ReadView，之后复用之前的ReadView
通过版本链实现并发读写。通过ReadView生成策略的不同实现不同的隔离级别
什么是脏读、幻读、不可重复读
脏读：一个事务修改了一个值，但是需要回滚 回滚前另一个事务读到了被修改后的值
幻读：一个事务插入了一条数据。插入前后另一个事务分别读取，读取的记录数不一样
不可重复读：一个事务修改前后，另一个事务读到的数据不一致
事务的基本特性和隔离级别
ACID
- 原子性：全执行/不执行
- 一致性：
- 隔离性：事务事物之间互不干扰
- 持久性：写在磁盘
隔离级别
索引分类
- 功能逻辑上：普通索引、唯一索引、全文索引、单列索引
- 物理实现：聚簇索引、非聚簇索引
- 作用字段个数：单列索引、联合索引
普通索引：可以在任何数据类型
唯一索引：该值必须唯一，允许有空值，比如邮箱、身份证、手机号
主键索引：聚簇索引、非聚簇索引
单列索引：一个字段
联合索引：idx_id_name_gender 多个字段，使用要遵守最左前缀原则
什么是索引覆盖
SQL执行的时候可以利用索引快速查找。字段在索引中都包含了，不需要回表，所有数据都在叶子节点上.
- 理解方式1：索引是高校找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此他不必读取整个行，毕竟索引叶子节点储存了他们的索引数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了，一个索引包含了满足查询结果的所有数据就叫组覆盖索引
- 理解方式2：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN、WHERE子句中做到所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）
简单地说就是：索引列+主键 包含SELECT到FROM之间的查询列
聚集索引、非聚集索引
InnoDB中，主键索引和每一条数据都放在同一个文件中。聚集索引的叶子节点包含了完整的数据记录
MyISAM的索引和主键分别放在myi和myd中，每次查询的时候从myi查到数据的存放位置，然后去myd中查出来，类似于一种回表的操作
聚簇索引和二级索引
另外，索引又可以分成聚簇索引和非聚簇索引（二级索引），它们区别就在于叶子节点存放的是什么数据：
聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是主键值，而不是实际数据。
因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。
InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：
- 如果有主键，默认会使用主键作为聚簇索引的索引键；
- 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
- 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；
一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。
二级索引的 B+ 树如下图，数据部分为主键值：
因此，如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。
MySQL三大日志(binlog、redo log和undo log)详解
- redo log:（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
- 比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。
- MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。
- 后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
- 更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。
- 然后会把“在某个数据页上做了什么修改，比如页号xxx,偏移量yyy，写入了zzz数据”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。（物理级别的修改）
- binlog
- redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。
- 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
- 在事务执行的过程中，redo log会不断顺序记录，直到这个给事务提交，才会一次性写到bin log 中。
- undo log
- 我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。
- 另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改
- MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
Mysql主从复制
(1) 为什么要做主从复制？
1、在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2、做数据的热备
3、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
(2) 什么是mysql的主从复制
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
(3) 主从复制原理
- master服务器将数据的改变记录在二进制binlog日志上，当master上的数据发生改变时，将其写入二进制文件中;
- slave服务器会在一定时间间隔内对master二进制日志进行探测是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件
- 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制时间，并保存至 从节点 本地的中继日志中，从节点 将启动sql线程从中继日志中读取二进制日志，在本地释放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。
简单说：
从库会生成两个线程,一个I/O线程,一个SQL线程;
主库会生成一个log dump线程,用来给从库I/O线程传binlog;
I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行。
Innodb如何实现事务（update语句为例）
Buffer Pool: update语句—> 找到数据所在页-> 缓存在Buffer Pool中
执行update语句 修改Buffer pool中的数据
针对update语句生成redolog对象，存入logBuffer中
针对update语句生成undo日志作为回滚使用
如果事务提交，Redolog持久化，后续有机制将BufferPool 中所修改的数据页持久化到磁盘中
如果事务回滚，则用undo日志进行回滚
Innodb 事务为什么要两阶段提交?
两段式提交，就是我们先把这次更新写入到redolog中，并设redolog为prepare状态，然后再写入binlog,写完binlog之后再提交事务，并设redolog为commit状态。也就是把redolog拆成了prepare和commit两段！
其实redolog是后来才加上的，binlog是之前就有的。一开始存储引擎只有MyISAM,后来才有的InnoDB,然后MyISAM没有事务，没有crash-safe的能力。所以InnoDB搞了个redolog。然后为了保证两份日志同步，所以才有了两段式提交。
你假设一下如果先保存好redolog,然后再记录binlog。如果redolog写好了之后挂了。ok你看起来好像是没问题了，但是你的binlog还没记录，所以这条记录就少了！如果你备份这份binlog之后，你这条记录就永远的少了！
那如果先写binlog再写redolog呢？那binlog写完了，你数据库挂了，那redolog是不是没有，没有的意思就是你以前你没更新成功。但是binlog已经记录好了，在它那边反正是成功了，所以那备份的binlog也不对！
WAl 是什么?有什么好处?
WAI主要先写日志、再写磁盘
WAL(Write Ahead Log)预写日志，是数据库系统中常见的一种手段，用于：
1、保证数据操作的原子性和持久性。
2、使得随机写变为顺序写提高性能。
WAL 的优点：
- 读和写可以完全地并发执行，不会互相阻塞（但是写之间仍然不能并发）。
- WAL 在大多数情况下，拥有更好的性能（因为无需每次写入时都要写两个文件）。
- 磁盘 I/O 行为更容易被预测。
- 使用更少的 fsync()操作，减少系统脆弱的问题。
什么是索引下推? INDEX CONDITION PUSHDOWN
索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
例如：
1 | EXPLAIN SELECT * FROM S1 WHERE key1 > 'z' AND key1 LIKE '%a'; |
- 对于我们理解的而言，查询的顺序应该是：
- 先找到 key1 > ‘z’的行，然后回表查询，最后筛选key1 LIKE ‘%a’的数据返回
- 但是对于查询优化器而言：
- 先找到 key1 > ‘z’的行，这个时候先不回表，继续执行key1 LIKE ‘%a’，直接在索引中挑选出来，最后把符合这两个条件的数据进行回表查找。此时减少了回表的次数
例如：
索引为zipcode,lastname,address 联合索引
- 索引中包含了后面查询田中的字段，在回表前索引下推机制是会先做判断的
一条 Sql 语句查询偶尔慢会是什么原因?
- 数据库刷新脏页当我们要往数据库中插入一条数据或者更新一条数据时，数据库会在内存中把对应字段的数据更新了，但是更新完毕之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到redo log日志中去，只有等到空闲的时候才会通过redo log里的日志把最新的数据同步到磁盘里。这里redo log的容量是有限的，所以如果数据库一直很忙且更新有很频繁，那么这个时候redo log很快就会被写满，从而没办法等到空闲时再把数据同步到磁盘，只能暂停其他操作，全身心来把数据同步到磁盘中去，造成的表象就是我们平时正常的SQL语句突然会执行的很慢。也就是说，数据库在同步数据到磁盘的时候就有可能会导致我们的SQL语句执行的很慢。
- 无法获取锁资源执行的时候遇到了表锁或者行锁。如果我们要执行的SQL语句，其涉及到的表正好别人在用并且加锁了，或者表并没有加锁，但是要使用到的某一行被加锁了，那么我们便无法获取锁，只能慢慢等待别人释放锁了。如果要判断是否真的在等待锁资源，我们可以使用”show processlist”命令来查看当前的状态。
主从延迟要怎么解决?
分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。
打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。
重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。
如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。不推荐这种方法，你要是这么搞，读写分离的意义就丧失了。
删除表数据后表的大小却没有变动,这是为什么?
1、释放这些空间的操作本身就需要时间，如果每次删除数据都去进行这个操作，显然会影响性。
2、第二个原因则是因为表里后续还是会有新的数据插入，这些删除的数据空间可以在新的数据插入进来后重新利用即可，这样也避免了新增数据要去重新申请新的空间。为什么 VarChar 建议不要超过255?
首先VARCHAR不是定长的，而是可变的，所以一般业务开发我们都要尽量使用最小的长度来满足需求，以免浪费空间，影响性能，而既然是可变的长度，那就得有保存长度的地方，所以如果VARCHAR的长度设置在255以下，那只会使用额外一个字节来保存长度，但是如果VARCHAR的长度设置在255以上，那么就会使用额外的两个字节来保存长度，无形中就浪费了存储空间。
Redis和MySQL如何保证数据的一致性
问题：一份数据同时保存在数据库里和redis里面，数据发生变化的时候redis和MySQL变化是有先后顺序的
先更新数据库再更新缓存/先更新缓存再更新数据库
- 一个改还没同步，另一个查 不一致
先删除缓存再更新数据库
延时双删
保证高一致性：
MQ手动应答确保redis删除
Canal组件监听binlog日志