MySQL 基础知识

MySQL基础知识

1. 关于MySQL

MySQL是一种流行的开源关系数据库管理系统（RDBMS)。MySQL使用标准的SQL（结构化查询语言）来处理数据库中的数据。它的应用范围非常广泛。

MySQL的主要特点包括：

  1. 灵活性和可扩展性：MySQL支持各种操作系统，包括Linux、Windows、Mac等，并且可以处理从小型应用到大型互联网应用的不同数据需求。
  2. 性能：MySQL使用了多种优化技术，包括索引（提高查询速度）和各种存储引擎（如InnoDB和MyISAM）。
  3. 安全性：MySQL提供了多种安全措施，包括密码策略、权限管理和加密数据传输。
  4. 开源：MySQL是开源的，这意味着开发者可以查看和修改其源代码，以满足自己的特定需求。
  5. 成本效益：因为MySQL是开源的，所以在不需要额外支持或特性的情况下，使用MySQL是免费的，这使得它对许多企业和开发者来说是一个有吸引力的选择。

2. MySQL的架构

MySQL可以分为应用层,逻辑层,数据库引擎层,物理层。

应用层：负责和客户端，响应客户端请求，建立连接，返回数据

逻辑层：包括SQK接口，解析器，优化器，Cache与buffer

数据库引擎层：有常见的MyISAM,InnoDB等等

物理层：负责文件存储，日志等等

2.1 一条SQL语句执行的过程

  1. 客户端首先通过连接器进行身份认证和权限相关
  2. 如果是执行查询语句的时候，会先查询缓存，但MySQL 8.0 版本后该步骤移除。
  3. 没有命中缓存的话，SQL 语句就会经过解析器，分析语句，包括语法检查等等
  4. 通过优化器，将用户的SQL语句按照 MySQL 认为最优的方案去执行。
  5. 执行语句，并从存储引擎返回数据

2.2 buffer 与 cache 的对比

  1. Cache(如Qcache)一般用来把固定语句对应的结果集放在内存，目的是为了提高读取速度
  2. buffer（如buffer pool）一般用来把页面加载到内存，每次写入时，先更新buffer pool中的日志（redo log），把要修改的页记为脏页。后台进程每隔一段时间将buffer pool中的日志进行刷盘，从而提高了磁盘IO效率，降低了大量写入带来的冲击

3. MySQL的引擎

3.1 MySQL支持的引擎

InnoDB MyISAM
支持事务 不支持
支持外键 不支持
聚集索引 非聚集索引
行级锁和表级锁 表级锁

Memory 存储引擎：

Memory存储引擎将所有数据都保存在内存，不需要磁盘 IO。支持哈希索引，因此查找速度极快。Memory 表使用表级锁，因此并发写入的性能较低。

3.2 InnoDB的索引

索引是一种数据结构，用于帮助存储引擎快速的找到数据，索引存储在内存上

3.2.1 聚集索引和非聚集索引

聚集索引中，索引和数据绑定在一起，通过查找索引可以直接返回数据。主键都是聚集索引。非聚集索引中，数据和索引分离，通过一个或多个非聚集索引的查询，查到对应某一值的主键值/地址，然后再使用主键值找到数据

有时非聚集索引无法直接找到主键值，只能查到满足非聚集索引某一组值的锁欧聚集索引，此时会回到聚集索引中根据主键值继续查找数据，这一过程称为回表查询

3.2.2 联合索引和最左匹配原则：

联合索引是指对表上的多个列的关键词进行索引。

对于联合索引的查询，如果精确匹配联合索引的左边连续一列或者多列，则mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配。where语句中，所有命中的索引被称为匹配列，没有命中但出现在索引中的成为过滤列，所有不在索引中的成为非匹配列

例如联合索引为（user, age, id), 而范围查询中的谓语为“where id = 1 and age > 10 and user = wang and sex = 'M' ”,此时：

  1. 在where语句语句中查找谓语user，命中，user为匹配列
  2. 在where语句语句中查找谓语age，命中，但age为范围查询，因此age后的列无法再命中
  3. id未命中，但出现在索引中，为过滤列
  4. sex不在索引中，为非匹配列

对于user和sex，SQL将在内存中的索引（b+树）进行查询（通过explain可查看查询方式为REF）

对于id，SQL将在符合user，age条件的子树上遍历，这样的复杂度并不降低，但由于是在内存上执行，速度仍然比回表后快（通过explain可查看查询方式为INDEX）

对于sex，SQL将先回表，返回所有满足user，age，id条件的数据结果，然后再遍历获得满足sex条件的数据结果（通过explain可查看查询方式为ALL）

Mysql会对第一个索引字段数据进行排序，在第一个字段基础上，再对第二个字段排序。

3.3 索引的数据结构

InnoDB采用的是B+Tree索引

B-Tree：一种自平衡多插树。每个节点都存储key和value。因为每个节点都有数据，所以查询效率较高

B+Tree：也是自平衡多叉树，但中间节点不存放数据只存放key。只在叶节点存放数据，结构矮胖，出度更大，可以在相同的磁盘空间下容纳更多数据。且在叶节点之间链指针，所以进行范围查询时只需要遍历叶节点即可。

Hash索引：哈希索引对于每一行数据计算一个哈希码，并将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。只有 Memory 引擎显式支持哈希索引。

为何不使用红黑树：

红黑树是二叉查找树，出度为2，因此红黑树存储一张表，高度会比B Tree大很多，IO次数多，检索时间长

4. MySQL的事务

4.1 ACID

事务满足如下几个特性：

  • 原子性（Atomicity）: 一个事务中的所有操作要么全部完成，要么全部不完成。

  • 一致性（Consistency）: 事务执行前后数据库的状态保存一致。

  • 隔离性（Isolation） 多个并发事务对数据库进行操作，事务间互不干扰。

  • 持久性（Durability） 事务执行完毕，对数据的修改是永久的，即使系统故障也不会丢失

4.2 事务的并发问题和隔离等级

并发问题：

  • 丢失修改
  • 脏读：当前事务可以查看到别的事务未提交的数据。
  • 不可重读：在同一事务中，使用相同的查询语句，同一数据资源莫名改变了。
  • 幻读：在同一事务中，使用相同的查询语句，莫名多出了一些之前不存在的数据，或莫名少了一些原先存在的数据，例如第二次读取前，表格被插入了新行。

隔离等级

  • 读未提交： 一个事务还没提交，它做的变更就能被别的事务看到。

  • 读已提交： 一个事务提交后，它做的变更才能被别的事务看到。

  • 可重复读： 一个事务执行过程中看到的数据总是和事务启动时看到的数据是一致的。在这个级别下事务未提交，做出的变更其它事务也看不到。

  • 串行化： 对于同一行记录进行读写会分别加读写锁，当发生读写锁冲突，后面执行的事务需等前面执行的事务完成才能继续执行。

并发问题的解决：

5. MySQL的锁（InnoDB）

5.1 锁的性质

5.1.1 共享性

共享锁：其他事务可以读但不能写，又称为读锁

排他锁：其他事务不能读写，又称为写锁

5.1.2 粒度

表级锁: 对当前操作的整张表加锁,实现简单，加锁快，但并发能力低。

行锁: 锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁慢，会出现死锁。

Gap 锁：锁住一个间隙以防止插入，但不包括间隙的两端。假设索引列有2, 4, 8 三个值，如果对 4 加行锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入值所对应的索引值在这两个间隙之间的记录

next-key-lock：next-key lock 实际上就是 行锁+这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:

(负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)

在 RR 隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读问题产生。

5.1.3 意向锁

意向锁必须是表级锁，可以有共享性和排他性。意向锁的作用在于保护不同级别的锁共享性不冲突，或者说提高检测这一冲突的效率。

假设事务A正在update表t的某一行r，并给这一行添加了排他锁，同时，事务B想要给表t上一个表级共享锁，为了防止共享性冲突，事务B必须检查表t的每一行，检查是否有A上的锁这样的排他锁，然后才能上锁

因此，A给表t中的行r上排他锁时，同时还会给表t上一个表级的排他意向锁，此时B所上的表级共享锁将会被阻塞

注意，意向锁只会阻塞表级锁，上例中，如果有另一事务C试图给另一行r2加共享锁，则A所加的排他意向锁不会对其进行阻塞

5.1.4 死锁

两个事物同时上锁进入互相等待的死循环。

如何解决死锁：

  1. 合理设计索引，尽可能通过索引定位行，防止过多的表和行被锁住，减少竞争
  2. 调整SQL语句顺序，update，delete等长时间持有锁的语句放在后面
  3. 把大事物分解成小事务执行
  4. 必要时，可以使用KILL杀死一些进程，释放其持有的锁

详细分析方法可参考这篇文章

5.1.5 乐观性

乐观锁：对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁，只有到数据提交的时候才通过一种机制来验证数据是否存在冲突。

悲观锁：对于数据冲突保持一种悲观态度，在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库的锁都是悲观锁

5.2 上锁方式

5.2.1 表锁

隐式上锁：使用一些关键词时，自动添加自动释放的上锁。如select语句会给表上共享锁，insert，update，delete会给表上排他锁

显示上锁：

1
2
3
4
LOCK TABLE t1 READ  -- 给t1表上共享锁
LOCK TABLE t1 WRITE  -- 给t1表上排他锁
UNLOCK TABLE t1 -- 解锁t1
UNLOCK TABLES -- 解锁所有表

5.2.2 行锁

隐式上锁：使用一些关键词时，自动添加自动释放的上锁。insert，update，delete会给选中的行上排他锁。注意select不会给行加锁

显示上锁：

1
2
3
4
5
SELECT * FROM t1 IN SHARE MODE -- 给选中的行加共享锁
SELECT * FROM t1 FOR UPDATE -- 给选中的行加排他锁
COMMIT
ROLLBACK -- 事务完成或回滚时，会解除事物中所加的行锁
KILL -- 杀死某一进程，会解除这一进程所加的行锁

6. MySQL的日志

6.1 log种类

redo log: 存储引擎级别的log（InnoDB有，MyISAM没有），该log关注于事务的恢复。在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。

undo log：是存储引擎级别的log（InnoDB有，MyISAM没有）保证数据的原子性，该log保存了事务发生之前的数据的一个版本，可以用于回滚，是MVCC的重要实现方法之一。

如果需要执行事务，使用redo log，执行失败则使用undo log，这样的组合保证了事务的一致性

bin log：数据库级别的log，关注恢复数据库的数据。有关bin log和redo log的区别

  1. redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的，会记录所有引擎对数据库的修改，具有崩溃修复能力。InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe
  2. redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，例如语句的增删改，并不记录数据页具体发生了什么改变，因此单独的bin log不具备崩溃修复能力。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

6.2 WAL技术

WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。事务在提交写入磁盘前，会先写到redo log里面去。如果直接写入磁盘涉及磁盘的随机I/O访问，涉及磁盘随机I/O访问是非常消耗时间的一个过程，相比之下先写入redo log，后面再找合适的时机批量刷盘能提升性能。

6.3 两阶段提交

为了保证binlog和redo log两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶段提交的机制。

  1. 执行器调用存储引擎接口，存储引擎将修改更新到内存中后，将修改操作记录redo log中，此时redo log处于prepare状态。
  2. 存储引擎告知执行器执行完毕，执行器生成这个操作对应的binlog，并把binlog写入磁盘。
  3. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成

6.4 MySQL 主从机制

6.4.1主从配置

一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。从服务器主要用来读，主服务器主要用写，从服务器定期与主服务器进行同步，复制其数据。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器

主从机制的优点：

  1. 数据存在多个镜像，可以防止单一主机崩溃和数据丢失，如果主机宕机可以切换到从服务器上（由于异步同步，可能数据一致性存在问题）
  2. 从服务器可以分担主服务器的读的压力

注意，部分数据实时性强，经常会被更新，这类数据不适合放在从服务器上，容易引发错误

6.4.2 主从复制

MySQL主从复制流程：

  1. 在事务完成之前，主库在binlog上记录这些改变，完成binlog写入过程后，主库通知存储引擎提交事物
  2. 从库将主库的binlog复制到对应的中继日志，即开辟一个I/O工作线程，I/O线程在主库上打开一个普通的连接，然后开始binlog dump process，将这些事件写入中继日志。从主库的binlog中读取事件，如果已经读到最新了，线程进入睡眠并等待ma主库产生新的事件。

7. 多版本并发控制(MVCC)

多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

可以认为MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此可以实现读写并发，并降低死锁概率，IO开销更低。

MVCC只在 可重复读（REPEATABLE READ） 和读已提交（READ COMMITTED） 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容

通过MVCC，不显式加锁的一般select语句使用的都是快照读，即读取数据在事务语句执行前或执行后的历史版本，而非当前数据，也即是说其他并发的事物无法影响到该快照。而显式加锁的select语句执行的是当前读，获得目标位置最新的数据

Database
#MySQL #Interview
MySQL 基础知识
http://example.com/2022/02/09/MySQL-Basic/
Author
Zhengyuan Yang
Posted on
February 9, 2022
Licensed under