MySQL

基础

MySQL架构

MySQL 的架构共分为两层：Server 层和存储引擎层

Server 层负责建⽴连接、分析和执⾏ SQL
存储引擎层负责数据的存储和提取, ⽀持 InnoDB、MyISAM、Memory 等多个存储引擎, 现在最常⽤的存储引擎是 InnoDB，其⽀持索引类型是 B+ 树索引。

MySQL执行引擎

类别

在MySQL中，可以通过 SHOW ENGINES; 命令查看当前数据库⽀持的存储引擎。InnoDB是较为通⽤和常⽤的存储引擎。
InnoDB : MySQL默认的事务性存储引擎，⽀持事务的提交（commit）和回滚（rollback），提供了⾏级锁定，⽀持外键约束和MVCC
MyISAM : MyISAM 使⽤表级锁定, 不⽀持事务，⽀持全⽂索引，适⽤于以读操作为主的应⽤
Memory : 将数据放在内存中，数据处理速度很快，但是当数据库重启或崩溃时，存储在内存中的数据将丢失。

数据库引擎InnoDB与MyISAM的区别和适⽤场景？

1. 事务⽀持
   InnoDB: ⽀持事务，具有ACID（原⼦性、⼀致性、隔离性、持久性）特性，适合需要数据⼀致性和完整性的应⽤，如银⾏系统或在线购物平台。
   MyISAM: 不⽀持事务，不具备ACID特性，适⽤于读密集、写少的场景，如博客系统或新闻⽹站。

2. 锁
   InnoDB: ⾏级锁，多个事务可以同时访问同⼀表的不同⾏。
   MyISAM: 表级锁，对整个表进⾏锁定，导致并发性能下降，特别是在有⼤量写操作时。

3. 外键约束:
   InnoDB: ⽀持外键约束，确保数据的⼀致性和完整性。
   MyISAM: 不⽀持外键约束

4. 崩溃恢复(Crash Recovery):
   InnoDB: ⽀持崩溃恢复
   MyISAM: 在崩溃后恢复可能会导致数据损失。

5. 全⽂索引(Full-text Indexing):
   InnoDB: ⽀持全⽂索引。
   MyISAM: 也⽀持全⽂索引，并且在这⽅⾯表现的更加好。

总结：
InnoDB: 适⽤于需要事务⽀持、并发性能好、具有⾼写⼊需求的应⽤
MyISAM: 适⽤于读操作频繁、写⼊操作较少的应⽤

执⾏⼀条select语句，发⽣了什么？

连接器

连接器跟客户端建⽴连接、获取权限、然后后⾯的权限逻辑判断都基于此时读取到的权限。

Mysql 会定期⾃动清理"空闲"连接，由参数 wait_timeout 控制的，默认值是 8 ⼩时。
由于建⽴连接⽐较复杂，所以尽量使⽤⻓连接，⽽不是短连接（少量查询后，就断开连接)
但是，当⻓连接过多时，可能导致内存占⽤太⼤，被系统强⾏杀掉（OOM），会导致 MySQL 异常重启
解决⽅案为:
定期断开⻓连接
MySQL 5.7以后版本，可以通过执⾏ mysql_reset_connection 来重新初始化连接资源

查询缓存

执⾏查询语句前，先看下查询缓存中是否有结果，如果有，则不必执⾏查询语句，直接取出缓存结果，如果没命中缓存，则执⾏查询语句，并将执⾏查询语句后的结果，放⼊查询缓存中。

不建议使⽤查询缓存（当 数据表频繁更新时，最新查询结果可能和查询缓存中存放的结果不⼀致)，MySQL 8.0 已删除该模块；

解析SQL

你输⼊的是由多个字符串和空格组成的⼀条SQL语句，MySQL需要识别出⾥⾯的字符串分别是什么，代表什么。

通过解析器对 SQL 查询语句进⾏词法分析、语法分析，然后构建语法树。
词法分析：根据你输⼊的字符串识别出关键字出来
语法分析：根据词法分析的结果判断是否符合SQL语法，并构建SQL语法课

执行SQL

分为三个阶段：预处理阶段、优化阶段、执⾏阶段

预处理阶段：判断表和字段是否存在

优化阶段：将 SQL 查询语句的执⾏⽅案确定下来，⽐如在表⾥⾯有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使⽤哪个索引, 或者在⼀个语句有多表关联（join）的时候，决定各个表的连接顺序。

执⾏阶段：MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进⼊了执⾏器阶段，开始执⾏语句（执⾏语句时，⾸先会判断当前⽤户是否有执⾏权限），从存储引擎读取记录，返回给客户端。

索引

索引分类

按数据结构

哈希索引
使⽤ key-value 对，存储数据 ，可能存在 hash 冲突（多个 value 对应同⼀个 key）
适合等值查询，检索效率⾼，⼀次到位

全文索引
MyISAM 和 InnoDB 中都⽀持使⽤全⽂索引，⼀般在⽂本类型char，text，varchar类型上创建

B+树索引
InnoDB 使⽤了 B+ 树索引模型，每⼀个索引在 InnoDB ⾥⾯对应⼀棵 B+ 树。
所有数据存储在叶⼦节点，复杂度为O(logn)，适合范围查询。

按物理存储

聚簇索引:数据存储与索引⼀起存放，叶⼦节点会存储⼀整⾏记录，找到索引也就找到了数据。

⾮聚簇索引:数据存储与索引分开存放，叶⼦节点不存储数据，存储的是数据⾏地址。

按字段特性

主键索引：建⽴在主键字段上的索引，⼀张表最多只能有⼀个主键索引，不允许有空值。
唯⼀索引：建⽴在 UNIQUE 字段上的索引，⼀张表可以有多个唯⼀索引，索引列的值必须唯⼀，但是允许有空值。
普通索引：建⽴在普通字段上的索引
前缀索引：对字符类型字段的前⼏个字符建⽴的索引，⽽不是在整个字段上建⽴的索引，可以减⼩索引的⼤⼩，适⽤于较⻓列值的情况。

按字段个数

单列索引：建⽴在单列上的索引称为单列索引，⽐如主键索引
联合索引：由多个列组合⽽成的索引。适⽤于多列的查询条件

什么时候需要创建索引

频繁⽤于查询的列需要创建索引

⼤表：对于很⼤的表，建⽴索引可以提⾼查询速度

唯⼀性要求：主键列和唯⼀性约束的列会⾃动创建唯⼀索引，但如果查询中经常包含唯⼀性条件，可以额外创建唯⼀性索引。

连接表的外键列：为外键列创建索引可以提⾼连接的效率。

频繁使⽤排序和分组的列：如果某列经常⽤于 ORDER BY 或 GROUP BY ⼦句可以创建索引

什么时候不需要创建索引

很少⽤于查询的字段

⼩表：在⼩表上，索引不会带来显著的性能提升，还会增加维护开销。

经常进⾏更新的字段不需要创建索引

字段中存在⼤量重复数据，不需要创建索引，⽐如性别字段

索引失效

使⽤左或者左右模糊匹配：⽐如 LIKE ‘%abc’ 这样的查询会导致索引失效。

在索引列上使⽤函数或表达式： 索引列上参与计算，索引失效
SELECT * FROM table WHERE UPPER(column) = ‘VALUE’;

在 WHERE ⼦句中，如果在 OR 前的条件列是索引列，⽽在 OR 后的条件列不是索引列，那么索引会失效。

违背最左匹配原则，索引失效

数据分布不均匀： 如果数据分布不均匀，例如某个索引列的⼤多数值都相同，选择性降低，导致索引失效。

隐式类型转换： 如果查询中的条件涉及到隐式类型转换，例如将字符串与数字⽐较，索引可能⽆法被使⽤。

MySQL为什么采用B+树而不是B树作为索引结构？

范围查询性能：B+树在范围查询方面具有更好的性能。由于B+树的叶子节点形成有序链表，可以非常高效地执行范围查询操作，例如大于、小于、区间查询等。对于数据库系统来说，范围查询是非常常见的操作，因此B+树更适合作为索引结构。

顺序访问性能：B+树在顺序访问方面也表现较好。由于B+树的叶子节点形成有序链表，可以按顺序访问数据，例如排序、分页和顺序遍历等操作。对于一些特定的查询需求，B+树的顺序访问性能更高。

更低的树高度：B+树相对于B树来说，具有更低的树高度。这是因为B+树的关键字全部存储在叶子节点中，非叶子节点只包含关键字范围和指向子节点的指针。较低的树高度意味着在查询过程中需要更少的磁盘访问，提高了查询效率。

内存占用：B+树的节点大小比B树相对较小，可以容纳更多的节点在内存中，从而提高了缓存的效率。这对于数据库系统来说尤为重要，因为它们需要频繁地从磁盘加载节点到内存中进行查询操作。

适应大规模数据集：MySQL作为一种常用的关系型数据库系统，通常需要处理大规模的数据集。B+树对于大规模数据集的索引具有较好的扩展性，能够高效地处理大量的数据和高并发访问。

事务

事务四大特性ACID

1. 原子性（Atomicity）
   事务是⼀个不可分割的最小⼯作单元，要么完全执⾏，要么完全不执⾏。如果在事务执⾏的过程中发⽣了错误，系统会撤销事务中已经执⾏的操作，将数据库恢复到事务开始前的状态。原⼦性是通过 undo log（回滚⽇志） 来保证的。

2. 一致性（Consistency）
   确保事务将数据库从⼀个⼀致的状态转变为另⼀个⼀致的状态。事务执⾏的结果必须满⾜数据库的完整性约束和规则，保持数据库的⼀致性。⼀致性则是通过持久性+原⼦性+隔离性来保证的。

3. 隔离性（Isolation）
   多个事务并发执⾏时，每个事务都不能看到其他事务的中间状态。每个事务都应该感觉就像它是唯⼀在数据库上运⾏的事务⼀样。防⽌了多个事务之间的相互⼲扰。隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的。

4. 持久性（Durability）
   ⼀旦事务被提交，其结果将永久保存在数据库中，即使系统发⽣故障。即使系统发⽣崩溃，事务的结果也不应该丢失，持久性是通过 redo log （重做⽇志）来保证的。

并行事务会出现什么问题

并⾏事务是指多个事务同时执⾏，这可以提⾼数据库系统的性能和吞吐量。但是并⾏事务也可能引发⼀些问题

1. 脏读：读到其他事务未提交的数据
   ⼀个事务读取了另⼀个事务未提交的数据，如果另⼀个事务后来回滚，读取的数据就是⽆效的。读到了并⼀定最终存在的数据，这就是脏读。

2. 不可重复读：前后读取的数据不⼀致
   在⼀个事务内，同⼀查询可能返回不同的结果，因为在事务执⾏期间其他事务可能修改了数据。

3. 幻读：前后读取的记录数量不⼀致
   在⼀个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不⼀样的情况，就意味着发⽣了「幻读」现象。

这三个现象严重性排序如下：

隔离级别

隔离级别是指多个并发事务之间相互隔离的程度，SQL标准定义了4个隔离级别

1. 读未提交：
   最低的隔离级别。在这个级别下，⼀个事务可以读取到另⼀个事务未提交的数据。这可能导致脏读（Dirty Reads）和不可重复读、幻读等问题。

2. 读提交
   在这个级别下，⼀个事务只能读取到已经提交的其他事务的数据。这解决了脏读的问题，但仍可能遇到不可重复读的问题。

3. 可重复读
   在这个级别下，⼀个事务在其⽣命周期内多次执⾏相同的查询，将始终看到相同的数据，但是，仍可能发⽣幻读。
   也是MySQL InnoDB 引擎的默认隔离级别。

4. 串行化
   提供了最⾼的隔离级别。会对记录加上读写锁，在多个事务对这条记录进⾏读写操作时，如果发⽣了读写冲突的时候，后访问的事务必须等前⼀个事务执⾏完成，才能继续执⾏，在这个级别下，事务的执⾏效果就好像它们是按顺序执⾏的，事务之间没有并发。这可以防⽌脏读、不可重复读和幻读，但也可能导致性能下降，因为并发性降低。

选择隔离级别需要根据应⽤程序的要求和性能需求进⾏权衡。较低的隔离级别提供更⾼的并发性能，但可能牺牲⼀致性。较⾼的隔离级别提供更强的⼀致性，但可能降低并发性能。

事务隔离的实现

不同隔离级别的实现方式

读未提交：可以读到未提交事务修改的数据，所以直接读取最新的数据就可以。

串行化：加读写锁的⽅式来避免并⾏访问

读提交和可重复读，通过 Read View 来实现的
「读提交」隔离级别是在每个 select 都会⽣成⼀个新的 Read View，也意味着，事务期间的多次读取同
⼀条数据，前后两次读的数据可能会出现不⼀致，因为可能这期间另外⼀个事务修改了该记录，并提交
了事务。
「可重复读」隔离级别是启动事务时⽣成⼀个 Read View，然后整个事务期间都在⽤这个 Read View，
这样就保证了在事务期间读到的数据都是事务启动前的记录。

MVCC

核心思想和作用

MVCC（Multi-Version Concurrency Control）多版本并发控制，⽤于管理多个事务同时访问和修改数据库的数据，⽽不会导致数据不⼀致或冲突。MVCC的核⼼思想是每个事务在数据库中看到的数据版本是事务开始时的⼀个快照，⽽不是实际的最新版本。这使得多个事务可以并发执⾏，⽽不会互相⼲扰。

MySQL的事务有ACID四⼤特性，其中的隔离性可以通过锁和MVCC来实现，MVCC适合在⼀些锁性能较为差的情况下使⽤，提⾼效率。

如何实现

每⼀个 UndoLog ⽇志中都有⼀个 roll_pointer （回滚指针）⽤于指向上⼀个版本的 Undo Log 。这样对于每⼀条记录就会构成⼀个版本链，⽤于记录所有的修改，每⼀次进⾏新的修改后，新的 Undo Log 会放在版本链的头部。

在我们进⾏查询的时候应该查询哪个版本呢？这时候就可以通过 ReadView 来实现。
在事务SELECT查询数据时，就会构造⼀个 ReadView ，它包含了版本链的统计信息如下：
m_ids 当前数据库中「活跃事务」的事务 id 列表, 表示启动了但还没有提交的事务
min_trx_id 「活跃事务」中事务 id 最⼩的事务
max_trx_id 当前数据库中应该给下⼀个事务的 id 值
creator_trx_id 创建该 Read View 的事务的id

查询规则：
该版本是否为当前事务创建（读取⾃⼰修改的数据），如果是就返回，否则进⼊下⼀个判断
该版本的事务id是否⼩于 min_trx_id（在ReadView创建之前，数据已经提交），可以直接访问
该版本的事务id是否⼤于 max_trx_id（在ReadView创建后，该版本才开启），不能被访问
该版本事务 id 在 [min_trx_id, max_trx_id] 之间，则判断当前版本事务id是否在 m_ids 中，如果不在，说明事务已经提交可以访问，否则不能访问。

锁

锁的种类

全局锁

全局锁就是对整个数据库实例加锁。

表级锁

元数据锁（meta data lock，MDL）

MDL 不需要显式使⽤，在访问⼀个表的时候会被⾃动加上。
MDL 的作⽤：保证读写的正确性。

在 MySQL 5.5 版本中引⼊了 MDL

当对⼀个表做增删改查操作的时候，加 MDL 读锁；
当要对表做结构变更操作的时候，加 MDL 写锁。

事务中的 MDL 锁，在语句执⾏开始时申请，但是语句结束后并不会⻢上释放，⽽会等到整个事务提交后再释放。（这可能会产⽣死锁的问题）

意向锁

意向锁⽤于指示⼀个事务在未来可能会请求对某些资源（如数据⾏）的锁定

意向共享锁：表示事务打算在资源上获得共享锁。其他事务可以继续获得共享锁，但不能获得排他锁。
意向排他（独占）锁： 表示事务打算在资源上获得排他锁。

对某些记录加上「共享锁」之前，需要先在表级别加上⼀个「意向共享锁」，对某些纪录加上「独占锁」之前，需要先在表级别加上⼀个「意向独占锁」。

意向共享锁和意向独占锁是表级锁，不会和⾏级的共享锁和独占锁发⽣冲突，意向锁之间也不会发⽣冲突，只会和共享表锁和独占表锁发⽣冲突。意向锁的⽬的是为了快速判断表⾥是否有记录被加锁。

AUTO-INC 锁

作⽤：表⾥的主键通常都会设置成⾃增的，之后可以在插⼊数据时，可以不指定主键的值，数据库会⾃动给主键赋值递增的值通过AUTO-INC 锁实现的。在插⼊数据时，会加⼀个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插⼊语句执⾏完成后，才会把 AUTO-INC 锁释放掉。其他事务的如果要向该表插⼊语句都会被阻塞，从⽽保证插⼊数据时字段的值是连续递增的。

缺陷：对⼤量数据进⾏插⼊的时候，会影响插⼊性能，因为其他事务中的插⼊会被阻塞。

改进：InnoDB 存储引擎提供了⼀种轻量级的锁来实现⾃增。在插⼊数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值⼀个⾃增的值，就把这个轻量级锁释放了，⽽不需要等待整个插⼊语句执⾏完后才释放锁。

行级锁

概念

⾏锁就是针对数据表中⾏记录的锁（也有⼈称为记录锁）。
这很好理解，⽐如事务 A 更新了⼀⾏，⽽这时候事务 B 也要更新同⼀⾏，则必须等事务 A 的操作完成后才能进⾏更新。

类型和各自作用

Record Lock，记录锁，仅仅把⼀条记录锁上，记录锁分为排他锁和共享锁。

Gap Lock，间隙锁，锁定⼀个范围，但是不包含记录本身，只存在于可重复读隔离级别，⽬的是为了解决可重复读隔离级别下幻读的现象。间隙锁之间是兼容的，两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系。

Next-Key Lock：Record Lock + Gap Lock 的组合，锁定⼀个范围，并且锁定记录本身。next-key lock 即能保护该记录，⼜能阻⽌其他事务将新纪录插⼊到被保护记录前⾯的间隙中。

锁的划分

从数据库角度

1. 共享锁（共享锁也叫读锁或 S 锁 ）
   共享锁锁定的资源可以被其他⽤户读取，但不能修改。
   在进⾏SELECT的时候，会将对象进⾏共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
   如果我们想要给某⼀⾏加上共享锁呢，⽐如想对 user_id=10的数据⾏加上共享锁，可以像下⾯这样：

SELECT user_id FROM product_comment WHERE user_id = 10 LOCK IN SHARE MODE;

2. 排他锁（排它锁也叫独占锁、写锁或 X 锁）
   排它锁锁定的数据只允许进⾏锁定操作的事务使⽤，其他事务⽆法对已锁定的数据进⾏查询或修改。
   如果我们想要在某个数据⾏上添加排它锁，⽐如针对 user_id=10的数据⾏，则写成如下这样：

SELECT user_id FROM product_comment WHERE user_id = 10 FOR UPDATE; 

3. 共享锁和排他锁的区别
   排它锁是独占的，⼀次只能由⼀个事务持有。它阻⽌其他事务获取相同资源的任何锁。共享锁是⾮独占的，允
   许多个事务同时持有相同资源的共享锁。它允许多个事务同时读取相同的数据。
   排它锁通常⽤于写操作，例如更新或删除数据，共享锁通常⽤于读操作，例如查询数据。

从程序员角度

1. 乐观锁（Optimistic Locking）
   认为对同⼀数据的并发操作不会总发⽣，属于⼩概率事件，不⽤每次都对数据上锁，也就是不采⽤数据库⾃身的锁
   机制，⽽是通过程序来实现。在程序上，我们可以采⽤版本号机制或者时间戳机制实现。

2. 悲观锁（Pessimistic Locking）
   也是⼀种思想，对数据被其他事务的修改持保守态度，会通过数据库⾃身的锁机制来实现，从⽽保证数据操作的排
   它性。

3. 乐观锁和悲观锁的适⽤场景
   （1）乐观锁
   适合读操作多的场景，相对来说写的操作⽐较少。它的优点在于程序实现，不存在死锁问题，不过适⽤场景也会相
   对乐观，因为它阻⽌不了除了程序以外的数据库操作。
   （2）悲观锁
   适合写操作多的场景，因为写的操作具有排它性。采⽤悲观锁的⽅式，可以在数据库层⾯阻⽌其他事务对该数据的
   操作权限，防⽌读 - 写和写 - 写的冲突。但是加锁的时间会⽐较⻓，可能会⻓时间限制其他⽤户的访问，也就是说
   他的并发访问性不好。

日志

类别

undo log（回滚⽇志）：是 Innodb 存储引擎层⽣成的⽇志，实现了事务中的原⼦性，主要⽤于事务回滚和 MVCC。

redo log（重做⽇志）：是 Innodb 存储引擎层⽣成的⽇志，实现了事务中的持久性，主要⽤于掉电等故障恢复。

bin log （归档⽇志）：是 Server 层⽣成的⽇志，主要⽤于数据备份和主从复制。

慢查询

概念

数据库查询的执⾏时间超过指定的超时时间时（long_query_time：默认10秒 ），就被称为慢查询。

原因

1. 查询自身
   查询语句⽐较复杂：查询涉及多个表，包含复杂的连接和⼦查询，可能导致执⾏时间较⻓。
   查询数据量⼤：当查询的数据量庞⼤时，即使查询本身并不复杂，也可能导致较⻓的执⾏时间。

2. 数据库缺陷
   缺少索引：如果查询的表没有合适的索引，需要遍历整张表才能找到结果，查询速度较慢。
   数据库设计不合理：数据库表设计庞⼤，查询时可能需要较多时间。

3. 资源相关
   并发冲突：当多个查询同时访问相同的资源时，可能发⽣并发冲突，导致查询变慢。
   硬件资源不⾜：如果MySQL服务器上同时运⾏了太多的查询，会导致服务器负载过⾼，从⽽导致查询变慢

怎么优化

1. 运⾏语句，找到慢查询的sql
2. 查询区分度最⾼的字段
3. explain：显示mysql如何使⽤索引来处理select语句以及连接表，可以帮助选择更好的索引、写出更优化的查询语句
4. order by limit 形式的sql语句，让排序的表优先查
5. 考虑建⽴索引原则
6. 了解业务使⽤场景