MySQL总结

xiaolong1894

已于 2022-08-08 20:39:45 修改

阅读量1.9k

点赞数 2

文章标签： mysql 数据库面试

于 2022-03-22 01:36:44 首次发布

本文链接：https://blog.csdn.net/sober1894/article/details/123343470

版权

1.MySQL架构

1.1 一条SQL语句的执行过程

1.1.1 查询过程

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段。
服务器进行SQL解析、预处理，再由优化器生成对应的执行计划。
MySQL根据优化器生产的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。

在这里插入图片描述

1.1.2 更新过程（增删改）

在这里插入图片描述

1.2 事物

1.2.1 特性

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。

1.2.2 隔离级别

隔离级别	可能导致的问题	解决方法
读未提交	脏读：表示一个事务能够读取另一个事务中还未提交的数据	隔离级别提高到读已提交
读已提交	不可重复读：是指在一个事务内，多次读同一数据数据发生了变化	隔离级别提高到可重复读
可重复读	幻读：指同一个事务内多次查询返回的结果集不一样	1、隔离级别提高到串行化 2、使用next key lock
串行化	效率低

MySQL 中的读已提交和可重复读两个隔离级别是使用多版本并发控制 MVCC 来实现的，而不是通过添加读写锁来实现的，如果通过读写锁来实现隔离级别的话，只有读读可以并发，读写，写读，写写都不能并发，这样数据库的并发度太低了，所以一般不通过加读写锁来实现隔离级别。而如果使用 MVCC 来实现提交读和可重复读两个隔离级别的话则可以在读的时候不加锁，读写和写读可以同时进行，只有写写需要阻塞，这样就极大地提高了并发度。

MVCC 机制会记录每行数据的历史版本，通过可见性算法、undo 日志以及 read view 控制每个读操作所读取的行数据历史版本，

Repeatable Read 在事务发生第一次读的时候选定所要读取的数据行的版本，整个事务都读取这一个版本的数据行，所以可以重复读，每次读取的数据都一致。

Read Committed 在事务中每次读操作都是读取最新的行数据版本，而这最新的数据行版本很可能是某个事务进行了修改操作后提交的，所以可能会发生多次读取同一行数据，但是前后读取的数据不一致的情况。这就是不可重复读现象，所以提交读不能避免不可重复度现象。

1.2.3 log

redolog：redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。用于持久化数据，宕机恢复，存储的是修改后的数据。
undolog：提供回滚和多个行版本控制(MVCC)。存储事务开始前数据（MVCC）。
binlog：二进制文件，记录追加式的SQL语句，用于主从同步、用于时间点的数据还原。Server层面。

mysql redolog undolog binlog

WAL(Write-Ahead Logging)技术，当一条记录需要更新的时候，InnoDB引擎先把记录写到redo log，更新内存，此时即算更新完成。同时InnoDB引擎会在适当的时候，将该记录更新到磁盘里面。redo log的大小是固定的，比如配置一组4个文件，每个文件大小1GB。从头开始写，写到末尾又回到开头循环写。先写Log，后刷磁盘（write ahead log），提高写入速度。因为日志是顺序写入，磁盘是随机写入。引擎层面。

Q： MySQL如何实现事物？
A：原子性通过undolog进行回滚，持久性通过redolog持久化，隔离性是通过事物隔离，一致性是数据的完整性约束，通过数据库和应用来保证。

MySQL 中事务的实现

1.3 存储引擎

InnoDB	MyISAM
支持行锁，表锁	只有表锁
支持自增id	无自增id
支持事物	不支持事物
有聚簇索引和非聚簇索引	只有非聚簇索引
支持外键约束	不支持外键约束
InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引	MyISAM只缓存索引

还有Memory和其他引擎，主流是InnoDB。

1.4 锁

1.4.1 锁的粒度：

行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

Record lock：单个行记录上的锁。
Gap lock：间隙锁，锁定一个范围，不包括记录本身，本质锁的索引。
Next-key lock：record + gap 锁定一个范围，包含记录本身。用于解决幻读。

表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

1.4.2 锁的类型：

共享锁（Shared locks, S locks）：基本锁类型之一。加共享锁的对象只允许被当前事务和其他事务读。也称读锁。

排他锁（Exclusive locks, X locks）：基本锁类型之一。加排它锁的对象只允许被当前事务读和写。也称独占锁，写锁。

意向锁（IS lock）：加锁前要先获取意向锁。用来标识此表是否被加了行锁或表锁，可以让其他事物快速知悉此表状态，是可直接加锁还是需要等待，避免全表扫描才能知道锁的状态，减少开销。

1.4.3 抽象锁（数据库没有这种锁，是抽象概念，需要用设计实现）

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。一般用版本号控制。

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。和排它锁一样。

更新锁（Update locks, U-locks）：锁类型之一。更新锁允许其他事务在更新之前读取资源。但不可以修改。引入它是因为多数数据库在实现加X锁时是执行了如下流程：先加S锁，添加成功后尝试更换为X锁。这时如果有两个事务同时加了S锁，尝试换X锁，就会发生死锁。因此增加U锁，U锁代表有更新意向，只允许有一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。

MySQL的死锁
Mysql超详细讲解死锁问题的理解

2.索引

2.1 类型

聚簇索引：主键索引和这一行数据放在一起，顺序写入、性能高、使用自增id。主键索引。
非聚簇索引：只放索引，叶子节点存键值和主键。普通索引，唯一索引，组合索引，全文索引。

2.2 数据结构

数据结构的演变：
二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。
平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。
红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。

B树：B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息。O(log n)
B+树：B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中，相比B树存放更多数据，层高更低，查找效率稳定；B+树的叶子节点使用指针连接在一起，可以范围查询和遍历排序。O(log n)
Hash：哈希索引只适用于等值查询的场景。不能使用范围查询、排序，不能使用组合索引，大量Hash碰撞的情况性能低。O(1)

Q：一个B+树能存储多少数据
A： 2000多万条。一个节点的大小为16k，索引8个字节，指针6个字节，非叶子节点的一个节点存放 16384/14 = 1170。假设一条数据1k，叶子节点可以存放16条数据。3层索引存放 1170 * 1170 * 16 = 21902400条记录。

InnoDB一棵B+树可以存放多少行数据

3.性能优化

3.1 慢查询分析

3.1.1 慢查询日志

开启慢日志查询：slow_query_log = 1;
设置慢查询时间临界点，查询时间高于这个临界点的都会被记录到慢查询日志中：long_query_time = 1;（单位秒）
设置慢查询存储的方式为文件：log_output = file; （=table会记录到表）
慢查询分析工具：mysqldumpslow

mysql慢查询日志轮转_MySQL慢查询日志

3.1.2 查询状态 show full processlist

显示正在执行的线程状态等信息，可以查看SQL的执行状态，运行时间，连接次数等。

id #ID标识，要kill一个语句的时候很有用
use #当前连接用户
host #显示这个连接从哪个ip的哪个端口上发出
db #数据库名
command #连接状态，一般是休眠（sleep），查询（query），连接（connect）
time #连接持续时间，单位是秒
state #显示当前sql语句的状态
info #显示这个sql语句

mysql: show full processlist 详解

3.1.3 执行计划 explain

查看执行计划

id: 表示查询中select操作表的顺序,按顺序从大到依次执行

select_type :该表示选择的类型,常见可选值有: SIMPLE(简单的), PRIMARY(最外层) ，SUBQUERY(子查询中的第一个select查询)

type :该属性表示访问类型,有很多种访问类型。最常见的其中包括以下几种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (非唯一索引扫描),eq_ref(唯一索引扫描,),(const)常数引用, 访问速度依次由慢到快。其中 : range(范围)常见与 between and …, 大于 and 小于这种情况。
提示 : 慢SQL是否走索引,走了什么索引,也就可以通过该属性查看了。

table :表示输出行数据所在表

possible_keys :顾名思义,该属性给出了,该查询语句,可能走的索引,(如某些字段上索引的名字)这里提供的只是参考,而不是实际走的索引,也就导致会有possible_Keys不为null,key为空的现象。

key : 显示MySQL实际使用的索引,其中就包括主键索引(PRIMARY),或者自建索引的名字。

key_len : 表示索引所使用的字节数，

ref :连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值

rows :扫描行数,也就是说，需要扫描多少行,采能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 大部分SQL优化，都是在减少这个值的大小。
注意: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)

Extra 这个属性非常重要,该属性中包括执行SQL时的真实情况信息,如上面所属,使用到的是"using where"，表示使用where筛选得到的值,常用的有: “Using temporary”: 使用临时表 “using filesort”: 使用文件排序

Mysql 执行计划

3.2 Schema与数据类型优化

3.1.1 数据类型

尽量使用可以正确存储数据的最小数据类型。占用更少的磁盘内存和CPU缓存，但是要确保没有低估取值范围。
使用更简单的数据类型。比如用整型取代字符串存储IP地址。
尽力避免NULL。通常情况下最好制定NOT NULL，因为NULL值使索引、索引统计和值比较都更复杂。

3.1.2 Schema

避免太多的列。
避免太多的关联。单个查询最好在12个表以内关联。
使用枚举。只有2个或数个值使用枚举会提升效率。
避免过度使用枚举。枚举的值太多不要使用枚举。
避免使用复杂的默认值代替NULL。如NOT NULL DEFAULT ‘0000-00-00 00:00:00’。

3.1.3 范式与反范式

在范式化的数据库中，每个事实数据会且只会出现一次。反范式化的数据库中，信息则会冗余，出现在多个地方。
范式化的特点是表的粒度小，更新快，缺点是复杂查询需要关联多表。
反范式的优点是冗余数据多，查询快，缺点更新慢，数据一致性问题。
实际应用中需要根据业务混合使用。

第一范式：列都是不可再分。第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子
单元），则满足第一范式（1NF）
第二范式：每个表只描述一件事情。首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。第二范式要求每个表只描述一
件事情。
第三范式：不存在对非主键列的传递依赖。第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传递依赖。

3.2 索引优化

最左匹配

如果不是按索引最左列开始查找，则无法使用索引。
不能跳索引中的列。
如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。

覆盖索引
如果一个索引包含（或者覆盖）所有需要查询的字段的值，称之为覆盖索引。通常通过二级索引查到索引和主键后，再通过主键去聚簇索引查找到这一行的数据，这一过程称为回表，覆盖索引省略了回表查询。

自增主键
使用AUTO_INCREMENT自增列代替UUID作为主键。
长度短，节省空间。避免页分裂和页碎片。
因为主键是顺序的，每一条记录都存储在上一条的后面，当页填满时，下一条记录写入新的页，近似于顺序写入。

使用UUID时，因为新行的主键值不一定比之前插入的值打，所以InnDB无法简单地总是把新行插入到页面索引的最后么事需要为新的行寻找合适的位置——通常是中间位置——并且分配空间。这会增加很多额外的工作，并且导致数据分布不够优化。

写入目标页可能已经刷到磁盘并从缓存中溢出，或者是还没被加载到缓存中，InnoDB在插入前需要先从磁盘读取目标页到内存中。这将导致大量的随机IO。
因为写入是乱序的，所以不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
犹豫频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

索引失效
模糊查询 %like
索引列参与计算,使用了函数
非最左前缀顺序
where对null判断
where不等于
or操作有至少一个字段没有索引
需要回表的查询结果集过大（超过配置的范围）

3.3 查询性能优化

是否请求了不必要的数据
是否扫描了额外的记录
重构查询的方式。将一个复杂查询拆分为多个简单查询。

3.4 服务器设置和硬件优化

从内存、磁盘、线程数等方面优化。

4.主从复制

可以实现读写分离，负载均衡，备份等功能。

4.1 复制模式

基于语句的复制（MySQL5.0以前）：记录造成数据更改的SQL。实现简单，文件小；但可能会造成错误，如更新当前时间等。
基于行的复制（MySQL5.1以后）：记录实际数据在二进制文件中。会正确复制每一行，但如果是全表更新的语句会产生大量日志。

MYSQL能够在这两张复制模式间动态切换。默认使用基于语句的复制，如果发现语句无法被正确地复制，就切换到基于行的复制模式。

4.2 配置

主库和从库my.cnf配置
log_bin = mysql-bin;
server_id = （唯一id）;
从库连接主库启动复制
change master to
开始复制
start slave
其他配置
sync_binlog=1;会在提交事务前将二进制日志同步到磁盘上。

5.分库分表

5.1切分方式：

5.1.1 垂直分库（表）

垂直分表将一个表按照字段分成多表，每个表存储其中一部分字段
垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

5.1.2 水平分（库）表

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。
水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

范围法：根据自增主键（或时间）拆分
优点：扩容方便
缺点：请求量不均匀，一般新增数据更活跃
哈希法：
优点：数据分散均匀
缺点：扩容不方便，扩容时可能需要数据迁移

5.2 问题

5.2.1 主键问题

雪花id
什么是雪花ID？

5.2.2 hash法扩容与迁移

一致性哈希
一致性哈希算法的原理与实现

5.2.3 分布式事务

2PC/3PC（两段式/三段式提交）、TCC（事务补偿）、本地消息表（最终一致性）、事务消息（RocketMQ）
3PC和2PC的区别：

引入超时机制。同时在协调者和参与者中都引入超时机制。
在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是一致的。也就是说，除了引入超时机制之外，3PC 把 2PC 的准备阶段再次一分为二，这样三阶段提交就有 CanCommit、PreCommit、DoCommit 三个阶段。

分布式事务的四种解决方案

5.2.4 跨节点关联查询join问题

表冗余：多个库存放相同的表
数据组装：先查一些表，再通过外键查另外的表组装
ER分片：如果能确定数据关系，将关联的数据放在一个库中

5.2.5 跨节点分页、排序、函数问题

在不同节点分别计算再汇总。

5.2.6 使用uid来进行水平切分之后，对非uid属性上的查询

用户侧，前台访问：单条记录的查询，访问量较大，服务需要高可用，并且对一致性的要求较高
- 索引表法：数据库中记录login_name与uid的映射关系
- 缓存映射法：缓存中记录login_name与uid的映射关系；
- 生成uid法：login_name生成uid；
- 基因法：login_name基因融入uid；
运营侧，后台访问：访问模式各异、批量分页的查询、访问量很低、对可用性和一致性要求低
1. 前台、后台系统 web/service/db 分离解耦，避免后台低效查询引发前台查询抖动；
2. 可以采用数据冗余的设计方式；
3. 可以采用“外置索引”（例如ES搜索系统）或者“大数据处理”（例如HIVE）来满足后台变态的查询需求；

用户中心，1亿数据，架构如何设计？

总结：以上总结了自己的认识，他人的博客与相关书籍，MySQL的知识点相当庞杂，足以见MySQL的设计博大精深，每一个点都可以拓展到很深。此次仅梳理了概要的面试（知识）点，每一个点在网上都有许多相关文章说明，待日后有更深的理解和实践再进行细分的记录。