MySQL中

基础

一个SQL语句在MySQL中的执行流程

首先登录（连接器），然后查缓存（redis8.0后废除），缓存没有就分析SQL语句（分析器），看是要干嘛，同时检查SQL语句是否正确。然后按照MySQL认为最优的方案执行。然后执行语句（执行器），执行前判断是否有权限，没权限报错。

MyISAM和InnoDB的区别？

MyISAM不支持行级锁和事务，InnoDB支持行级锁和事务

MyISAM不支持外键，InnoDB支持外键

MyISAM不支持数据库异常奔溃后的安全修复，InnoDB支持

MyISAM不支持MVCC，InnoDB支持MVCC

他们底层的索引虽然都是B+树，但是实现方式不一样。

MyISAM中，B+树的叶节点的data存的是data所在的地址。索引的时候，先找到data所在的地址，然后再根据这个地址读取相关数据，属于非聚簇索引。

InnoDB中，分为主键索引和辅助索引。主键索引中，B+树的叶节点的data存的就是data，属于聚簇索引。辅助索引中，B+树的叶节点的data存的是记录主键的值而不是地址。然后根据主键的值，再走一遍主键索引。

char和varchar的区别是什么？

char是定长字符串，varchar是变长字符串。

存储：

char会在右边填充空格达到指定长度，检索时去掉空格。适合存储长度较短或长度差不多的字符串。

varchar会使用1个或2个额外字节记录字符串的长度，检索时不处理。适合存储长度不确定或差异较大的字符串。

varchar（10）和varchar（100）的区别是什么？

如果两个存储相同的字符串，所占用磁盘的存储空间是一样的，但是varchar（100）会消耗更多的内存。因为varchar在分配内存的时候，会分配固定大小的内存块来保存值，当进行排序的时候，varchar（100）是按照100这个长度进行的，也就会消耗更多内存

NULL和‘ ’的区别是什么？为什么MYSQL不建议使用null作为默认值？

null和空字符串是两个完全不一样的值，区别如下：

null代表一个不确定的值，两个null也不一定相等。

空字符串的长度是0，是不占用空间的，而null是占用空间的

null会影响聚合函数的结果，count* 的时候会包括null值，如果是count name则会忽略null值

查询null时，必须通过is null 或 is not null来判断，不通过=、！=比较。空字符串可以。

事务

何为数据库事务？

数据库事务可以保证多个对数据库的操作构成一个逻辑上的整体。遵循要么全部执行，要么全部不执行。

ACID指什么？

ACID指持久性、原子性、隔离性和一致性。只有保证了事务的前三者，才能保证一致性。AID是手段，C才是目的。

如何保证持久性？原子性？

保证原子性是通过undo log来实现的。当事务回滚时能够撤销所有已经成功执行的sql语句。

持久性是通过redo log来实现的。为提高效率，数据写入磁盘前会先写入Buffer，读取也是先读取Buffer，没有就从磁盘读到Buffer上。那么如果在Buffer刷盘这一过程宕机了，数据就丢失了，就无法实现持久性。因此引入了redo log。数据修改时，除了修改Buffer的操作，也会记录一份到redo log中。事务提交时，会调用fsync对redo log刷盘。redo log采用的是WAL（预写式日志），所有修改先写到redo log中，再更新到Buffer。

为什么都要写入磁盘，redo log更快？

redo log采用的是追加写的方式，属于顺序IO。而Buffer刷盘数据随机IO，因为每次修改的数据位置不定。

Buffer刷盘是按照页为单位的，默认是16KB，而redo log只需要写入追加的部分，无效IO大大减少

并发事务带来了哪些问题？

脏读、可重复读、不可重复读、幻读

不可重复读的重点是：内容修改或者记录少了，多次读取一条记录发现某些记录的指被修改

幻读的重点是：记录新增了，多次执行一条查询语句时，发现查到的记录增加了

事务隔离级别，解决了什么问题？

读取未提交：解决不了脏读，不可重复读和幻读

读取已提交：解决了脏读。解决不了不可重复读和幻读

可重复读（默认级别）：解决了脏读，不可重复读。解决不了幻读。（快照读和当前读）

串行化：全部解决

如何实现一致性非锁定读和锁定读？

一致性非锁定读通过版本号或时间戳实现。更新数据时版本+1，或更新时间戳。只可能查到小于当前版本号的记录。如果读取的数据正在执行delete、update操作，系统无需等待锁的释放，会去读取数据的一个快照，也称为快照读。在可重复读和读取已提交两个隔离级别下，执行普通的select会使用一致性非锁定读。

锁定读就是读取最新的数据，也被称为当前读。

• select ... lock in share mode 对记录加共享锁S锁
• select ... for update 对记录加排他锁X锁
• insert、update、delete 对记录加排他锁X锁

什么是MVCC？有什么用？原理是什么？

MVCC是一种并发控制机制，用于多个并发事务读写数据库时保持数据的一致性和隔离性。通过在每个数据行上维护多个版本的数据来实现。当一个事务对数据库的数据进行修改时，MVCC会为该事务创建一个数据快照，而不是直接修改实际的数据行。

MVCC在MySQL中实现依赖的手段是：隐藏字段、read view、undo log

隐藏字段、read view：用来判断当前版本数据的可见性

undo log： 记录某行数据的多个版本的数据

隐藏字段有哪些字段

• DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
• DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
• DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

read view有哪些字段

• m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
• m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
• m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
• m_creator_trx_id：创建该 Read View 的事务 ID

InnoDB事务隔离级别实现原理

基于锁和MVCC机制共同实现的。

串行化隔离级别是通过锁来实现。

可重复读RR和读取已提交RC隔离级别是通过MVCC来实现的。

• 在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表）
• 在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表）

MVCC通过+Next-key-Lock防止幻读

只有在当前读下，才有可能产生幻读。通过Next-key-Lock锁定读取的记录的同时，锁定他们的间隙。

MySQL锁

表级锁和行级锁的区别？

表级锁是锁整个表，而行级锁（默认）的颗粒度更细，只锁某行或者多行记录，不影响其他行的读写

行级锁的使用注意什么？

行锁是针对索引字段加的锁，表锁是针对非索引字段加的锁，当执行update、delete语句时，如果where条件中字段没有命中或者索引失效的话，就会导致扫描全表，对所有记录进行加锁。

InnoDB有3中行锁方式：

记录锁Record Lock：单个行记录上的锁

间隙锁Gap Lock：锁定一个范围，不包括记录本身

临键锁Next-key-Lock：记录锁+间隙锁的组合，锁定一个范围，包含记录本身。防止幻读问题。

共享锁和排他锁

不论是表锁还是行锁，都存在共享锁（S锁）和排他锁（X锁）两类

意向锁有什么用？

快速判断是否可以对某个表使用表锁。意向锁是表锁，共有两种，并且意向锁之间是互相兼容的。

意向共享锁 IS锁、意向排他锁 IX锁

意向锁对表级别的共享锁和排他锁互斥，对行级别不会。

InnoDB有哪几类锁？分别介绍下

有记录锁、间隙锁、临键锁

记录锁Record Lock：单个行记录上的锁

间隙锁Gap Lock：锁定一个范围，不包括记录本身

临键锁Next-key-Lock：记录锁+间隙锁的组合，锁定一个范围，包含记录本身。防止幻读问题。

MySQL索引

索引的底层数据结构

在MySQL中MyISAM何InnoDB都是使用B+Tree作为索引结构，但是两者的实现方式不太一样。

MyISAM中，B+树的叶节点的data存的是data所在的地址。索引的时候，先找到data所在的地址，然后再根据这个地址读取相关数据，属于非聚簇索引。

InnoDB中，分为主键索引和辅助索引。主键索引中，B+树的叶节点的data存的就是data，属于聚簇索引。辅助索引中，B+树的叶节点的data存的是记录主键的值而不是地址。然后根据主键的值，再走一遍主键索引。

为什么使用B+Tree？

对比二叉查询树、平衡二叉树、红黑树、B树分析

对比 Hash：虽然Hash可用快速找到某个值，但是它不支持顺序存储和范围查找。

对比二叉查找树（BST，左<根<右）：当BST不平衡的时候，树变成了斜树，查找的时间复杂度从log2N一下变到了N。

对比平衡二叉树：虽然能保证树高度差不超过1，但是它需要频繁的进行旋转操作保证树的平衡，需要较大的计算开销。同时每个树节点只存一个数据，导致每次磁盘IO只读取一个节点的数据，如果，如果查询的数据分布在多个节点上，就需要多次磁盘IO，非常耗时

对比红黑树：因为红黑树不要求绝对平衡，所以它的高度会较高，通常也就需要多次磁盘IO才能查询到数据。（数据在内存中时，用红黑树多，例如TreeMap、TreeSet和JDK1.8后的HashMap）

对比B树：B树的所有节点是既存key又存data，而B+树只有叶子节点存key和data，其他只存key。同时B树的叶节点都是独立的，而B+树的叶子节点有一条引用链指向相邻的叶节点。当进行范围查找时，B树需要先找到下限，然后对B树中序遍历找到上限。而B+树就只用通过链表遍历就行。

主键索引和二级索引

数据表的主键列使用的就是主键索引。二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是通过二级索引可以定位主键的值，然后再走一遍主键索引，找到对应的data。唯一索引，普通索引，前缀索引属于二级索引

聚集索引和非聚集索引

聚集索引就是索引结构和数据一起存放的索引。主键索引就属于聚集索引。

非聚集索引就是索引结构和数据不一起存放的索引。二级索引就属于非聚集索引。

聚集索引比非聚集索引少了一次读取数据的IO操作。但是更新的代价大

覆盖索引

覆盖索引指需要查询的字段正好是索引的字段，那就直接根据该索引就查到数据了，无需回表查询。

一个索引包含（覆盖）所有要查询到字段的值。

例如 select id from table where id=1；

联合索引

使用多个字段创建索引就是联合索引，也叫组合索引和复合索引。

最左前缀匹配原则

在MySQL建立联合索引时回遵循，即最左有限，在检索数据时从联合索引的最左边开始匹配

所以在使用联合索引的时候，要考虑区分度高的放左边，尽可能多的过滤数据。

创建索引需要注意什么？

选择合适的字段创建索引、尽可能考虑建立联合索引而不是单列索引。

索引失效场景

1、索引建立不当或使用顺序未遵循最左匹配原则。

2、索引列上使用了函数

3、索引列上用了计算操作

4、like左边包含%

5、使用OR关键字

6、in使用不当

7、not in和not exists

8、order by使用不当

日志篇

常见的日志类型

错误日志error log、二进制日志binlog、一般查询日志general query log、慢查询日志slow quert log、事务日志redo log、undo log

慢查询日志有什么用？

慢查询日志记录了执行时间超过 long_query_time（10s）的所有查询，在解决SQL慢查询（执行时间过长）问题的时候会用到

binlog（二进制日志）记录了什么？有什么用？

binlog指二进制日志文件，主要记录了更改MySQL数据库中数据的所有操作，对于select、show这类不对数据库更改的操作是不包括的。

对表结构和表数据修改，虽然没对里面的数据进行修改，也会记录进binlog

可以通过 show binary logs 查看所有binlog日志列表

通过 show variables like '%binlog_format%' 查看binlog使用的模式

通过 show binlog events in 'binlog.00008' limit 10 查看日志的前10条数据，也可以通过binlog查看工具mysqlbinlog，可以解析二进制文件

binlog通过追加的方式进行写入，大小没限制。并且可以通过max_binlog_size参数设置每个binlog文件的最大容量，当文件大小达到定值后，会生成新的binlog文件来保存日志，不会出现前面写的日志被覆盖的情况。

主要作用：主从复制，需要依赖binlog同步数据，保证数据一致性。数据恢复

刷盘时机：事务执行时，先将日志写入到binlog chche，当事务提交时才将binlog cache中的日志持久化到磁盘上的binlog文件中。我们通过 sync_binlog 控制binlog的刷盘时机，取值范围0-N

0：不强求，系统自动判断何时写入磁盘

1：每次提交事务的时候都将binlog写入磁盘

N：每N个事务，才将binlog写入磁盘

redo log（事务日志：重做日志）如何保证事务的持久性

redo log的作用就是记录页的修改，比如某个页面某个偏移量处修改了几个字节的值以及具体修改的内容是什么。事务提交时，我们将redo log按照刷盘策略刷到磁盘上去，这样即使MySQL宕机了，重启之后也能恢复未能写入磁盘的数据，从而保证事务的持久性。

刷盘策略 innodb_flush_log_at_trx_commit 

0：事务提交时不刷盘，性能最高，但最不安全，MySQL宕机了会丢失1s内的事务

1：事务提交就刷盘，性能最低，但最安全，事务只要提交成功了，redolog记录一定在磁盘中

2：事务提交时，只把log buffer里的redo log内容写入 page cache（文件系统缓存）。安全和性能介于前两者之间

页修改后为什么不直接刷盘呢？

性能会非常差！InnoDB页的大小一般为16KB，而页又是磁盘和内存交互的基本单位，就会导致即使我们只修改了页中的几个字节数据，一次刷盘也需要将16KB大小的页全部刷到磁盘中。而且修改的页可能还不相邻。

binlog和redolog有什么区别？

用途：binlog用于数据库还原，属于数据级别的数据恢复。redolog用于保证事务的持久性，属于事务级别的数据恢复

写入方式：binlog以追加的方式写入，大小没有限制。redolog以循环写的方式写入，大小固定，写到结尾时，会回到开头循环写

日志级别：binlog属于逻辑日志，主要记录更改数据库的所有操作，redolog属于物理日志，主要记录某页的修改。

是否是InnoDB独有：binlog是所有存储引擎共有的，在Server层实现。redolog是InnoDB独有的。

undo log（事务日志：回滚日志）如何保证事务原子性？

每一个事务对数据的修改都会记录到undo log中，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL可以利用undo log将数据恢复到事务开始之前的状态。

属于逻辑日志，记录的是SQL语句，当读到undo log是一条delete语句，数据库就会实现一条delete语句。undo log的信息也会记录到redo log中，因为undo log也要持久性保护。并且undo log本身会被删除清理的。

undo log还有助于MVCC的实现。

优化

读写分离和分库分表

读写分离

考虑采用一主多从的形式实现读写分离。通过ShardingSphere实现读写分离和分库分表

读写分离 :: ShardingSpherehttps://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/usage/read-write-splitting/

• 主库将数据库中数据的变化写入到 bin log
• 从库连接主库
• 从库会创建一个 I/O 线程向主库请求更新的 binlog
• 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
• 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
• 从库的 SQL 线程读取 relay log 同步数据到本地（也就是再执行一遍 SQL ）。

分库

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

举个例子：说你将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

举个例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。

分表

分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

数据冷热分离

数据冷热分离是指根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。

冷热数据常按时间维度或访问频率区分。

慢查询问题排查

找到慢sql是第一步，然后再用explain命令对慢sql进行分析，获取执行计划相关信息

先通过 show variables like “slow _query_log” 查看慢日志是否开启。

没开启可以通过 set global slow _query_log=ON 开启

可以通过 show variables like '%long_query_time%'查看默认消耗时间

通过 set global long_query_time=1 修改

一般借助mysql官方提供的慢查询分析调优工具 mysqldumpslow

查询语句个数 show global status like '%show_queries%'

然后通过explain 获取执行计划的相关信息

执行计划指：一条SQL语句经过MySQL查询优化器的优化后，具体的执行方式

通过explain 可以了解数据表的查询顺序、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

可以通过 show profile 和 show profiles 展示SQL语句的资源使用情况，包括CPU的使用，上下文切换，IO等待，内存使用等。