常见面试题 - 4（MySQL）_mysql串行化 serializable-CSDN博客

MySQL

事务的特性？

事务（Transaction）是一个操作序列，不可分割的工作单位，以BEGIN TRANSACTION开始，以ROLLBACK/COMMIT结束

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

并发一致性问题？

丢失修改：一个事务对数据进行了修改，在事务提交之前，另一个事务对同一个数据进行了修改，覆盖了之前的修改；
脏读（Dirty Read）：一个事务读取了被另一个事务修改、但未提交（进行了回滚）的数据，造成两个事务得到的数据不一致；
不可重复读（Nonrepeatable Read）：在同一个事务中，某查询操作在一个时间读取某一行数据和之后一个时间读取该行数据，发现数据已经发生修改（可能被更新或删除了）；
幻读（Phantom Read）：当同一查询多次执行时，由于其它事务在这个数据范围内执行了插入操作，会导致每次返回不同的结果集（和不可重复读的区别：针对的是一个数据整体/范围；并且需要是插入操作）

事务隔离级别的理解？

未提交读（Read Uncommited）：在一个事务提交之前，它的执行结果对其它事务也是可见的。会导致脏读、不可重复读、幻读；
提交读（Read Commited）：一个事务只能看见已经提交的事务所作的改变。可避免脏读问题；
可重复读（Repeatable Read）：可以确保同一个事务在多次读取同样的数据时得到相同的结果。（MySQL的默认隔离级别）。可避免脏读、不可重复读；
可串行化（Serializable）：强制事务串行执行，使之不可能相互冲突，从而解决幻读问题。可能导致大量的超时现象和锁竞争，实际很少使用。

MVCC(多版本并发控制)

MVCC在每行记录后面都保存有两个隐藏的列，用来存储创建版本号和删除版本号。

创建版本号：创建一个数据行时的事务版本号（事务版本号：事务开始时的系统版本号；系统版本号：每开始一个新的事务，系统版本号就会自动递增）；
删除版本号：删除操作时的事务版本号；
各种操作：
- 插入操作时，记录创建版本号；
- 删除操作时，记录删除版本号；
- 更新操作时，先记录删除版本号，再新增一行记录创建版本号；
- 查询操作时，要符合以下条件才能被查询出来：删除版本号未定义或大于当前事务版本号（删除操作是在当前事务启动之后做的）；创建版本号小于或等于当前事务版本号（创建操作是事务完成或者在事务启动之前完成）

通过版本号减少了锁的争用，提高了系统性能；可以实现提交读和可重复读两种隔离级别，未提交读无需使用MVCC

约束类型？

主键约束（Primary Key）
唯一约束（Unique）
检查约束（check）
非空约束(NOT NULL)
外键约束（Foreign Key）

MySQL 架构?

Server 层：连接器、查询缓存、分析器、优化器、执行器、内置函数
存储引擎：数据的存储和提取。InnoDB、MyISAM

一条 SQL 语句在数据库框架中的执行流程？

应用程序把查询 SQL 语句发送给服务器端执行；
查询缓存，如果查询缓存是打开的，服务器在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据，如果存在，则直接返回给客户端。只有缓存不存在时，才会进行下面的操作；
查询优化处理，生成执行计划。这个阶段主要包括解析 SQL、预处理、优化 SQL 执行计划；
MySQL 根据相应的执行计划完成整个查询；
将查询结果返回给客户端。

什么是视图？什么是游标？

视图：从数据库的基本表中通过查询选取出来的数据组成的虚拟表（数据库中存放视图的定义）。可以对其进行增/删/改/查等操作。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；可以跟基本表一样，进行增删改查操作(增删改操作有条件限制)；如连表查询产生的视图无法进行，对视图的增删改会影响原表的数据。好处：
- 通过只给用户访问视图的权限，保证数据的安全性；
- 简化复杂的SQL操作，隐藏数据的复杂性（比如复杂的连接）；
游标（Cursor）：用于定位在查询返回的结果集的特定行，以对特定行进行操作。使用游标可以方便地对结果集进行移动遍历，根据需要滚动或对浏览/修改任意行中的数据。主要用于交互式应用。

char 和 varchar 的区别？

**char(n) ：**固定长度类型，比如：订阅 char(10)，当你输入”abc”三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
**varchar(n) ：**可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节。

索引的分类？

从数据结构角度

树索引 (O(log(n)))
Hash 索引

从物理存储角度

聚集索引（clustered index）
非聚集索引（non-clustered index）

从逻辑角度

普通索引
唯一索引
主键索引
联合索引
全文索引

索引的理解？

建立索引的原则：

在最频繁使用的、用以缩小查询范围的字段上建立索引；
在频繁使用的、需要排序的字段上建立索引。

不适合建立索引的情况：

对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引；
对于一些特殊的数据类型，不宜建立索引，比如：文本字段（text）等。

索引的底层使用的是什么数据结构？

InnoDB 存储引擎：B+ 树

使用索引的优缺点？

大大加快了数据的检索速度；
可以显著减少查询中分组和排序的时间；
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）

缺点：建立和维护索引耗费时间空间，更新索引很慢。

哪些情况下索引会失效？

以“%(表示任意0个或多个字符)”开头的LIKE语句；
OR语句前后没有同时使用索引；
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
对于多列索引，必须满足 最左匹配原则/最左前缀原则 (最左优先，eg：多列索引col1、col2和col3，则索引生效的情形包括 col1或col1，col2或col1，col2，col3)；
如果MySQL估计全表扫描比索引快，则不使用索引（比如非常小的表）

B+ 树的理解？

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。
进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

IO次数少：B+树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；
范围查询效率更高：B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表；
查询效率更加稳定：每次查询都需要从根结点到叶结点，路径长度相同，所以每次查询的效率都差不多

哈希索引的理解？

哈希索引能以 O(1) 时间进行查找，但是失去了有序性。无法用于排序与分组、只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+ 树索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如：快速的哈希查找。

怎么知道创建的索引有没有被使用到？

使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

查询性能的优化方法？

只返回必要的列：最好不要使用 SELECT * 语句。
只返回必要的行：使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

InnoDB 和 MyISAM 的比较？

事务：MyISAM不支持事务，InnoDB支持事务；
全文索引：MyISAM 支持全文索引，InnoDB 5.6 之前不支持全文索引；
关于 count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描。意思就是对于 select count() from table; 如果数据量大，MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描；
外键：MyISAM 不支持外键，InnoDB 支持外键；
锁：MyISAM 只支持表锁，InnoDB 可以支持行锁。

MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

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

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

乐观锁和悲观锁？

悲观锁：认为数据随时会被修改，因此每次读取数据之前都会上锁，防止其它事务读取或修改数据；应用于数据更新比较频繁的场景；
乐观锁：操作数据时不会上锁，但是更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试；适用于读多写少的场景。乐观锁的实现方式有：
- 加一个版本号或者时间戳字段，每次数据更新时同时更新这个字段；
- 先读取想要更新的字段或者所有字段，更新的时候比较一下，只有字段没有变化才进行更新

DROP、DELETE 与 TRUNCATE 的区别？

三种都可以表示删除，其中的细微区别之处如下：

	DROP	DELETE	TRUNCATE
SQL 语句类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	从数据库中删除表，所有的数据行，索引和权限也会被删除	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据
删除速度	删除速度最快	删除速度慢，需要逐行删除	删除速度快

因此，在不再需要一张表的时候，采用 DROP；在想删除部分数据行时候，用 DELETE；在保留表而删除所有数据的时候用 TRUNCATE。

什么是主从复制？实现原理是什么？

主从复制（Replication）是指数据可以从一个MySQL数据库主服务器复制到一个或多个从服务器，从服务器可以复制主服务器中的所有数据库或者特定的数据库，或者特定的表。默认采用异步模式。

实现原理：

主服务器 binary log dump 线程：将主服务器中的数据更改（增删改）日志写入 Binary log 中；
从服务器 I/O 线程：负责从主服务器读取binary log，并写入本地的 Relay log；
从服务器 SQL 线程：负责读取 Relay log，解析出主服务器已经执行的数据更改，并在从服务器中重新执行（Replay），保证主从数据的一致性

为什么要主从复制？

读写分离：主服务器负责写，从服务器负责读
- 缓解了锁的争用，即使主服务器中加了锁，依然可以进行读操作；
- 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
- 增加冗余，提高可用性
数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
降低单个服务器磁盘I/O访问的频率，提高单个机器的I/O性能