听说面试官喜欢问这些MySQL知识

程序大视界

发布于 2022-12-19 21:12:04

3470

发布于 2022-12-19 21:12:04

文章被收录于专栏：程序大视界

—

前言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

除了基本的学会使用MySQL的技巧以外，MySQL的这些基础知识也是必须要掌握的，尤其是在面试的时候，面试官经常会问到。

—

MySQL的并发控制

我们平常说的并发控制通常是指系统代码层面的并发控制，但是在数据库层面也有着并发控制的概念，这个并发是读与写的并发控制，共享锁和排它锁，分别称为：读锁和写锁。还有表锁和行锁，如下：

读锁：是共享的，线程互不阻塞的，多个客户同时访问读取同一个资源，互不干扰。

写锁：是排他的，一个写锁会阻塞其他的写锁和读锁。

表锁：锁定整张表，一个用户在对表进行写操作时，会先获得写锁，阻塞其他用户对该表的所有读和写操作。仅当没有写锁时，其他用户才能获得读锁，读锁之间是互不阻塞的。

行级锁：行级锁可以最大程度的支持并发处理（同时锁开销也最大），InnoDB和XtraDB以及一些存储引擎中，实现了行级锁。

通常我们系统中用大部分都是用行级锁即可解决常见的问题了，更高级别的并发控制问题，还是需要在系统代码层面来控制。

—

数据库的事务

事务是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或者其他原因无法执行，那么所有的语句都不会执行。简单说就是，事务内的语句，要么全部执行成功，要么全部执行失败。

数据库的事务包含有4个基本特征：ACID原子性、一致性、隔离性和持久性。

原子性：表示一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，不可能只执行其中一部分。

一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态。

隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。

举个简单例子：银行的数据库有2张表，支票表和储蓄表，现在用户要从支票账户转200元到她的储蓄账户，那么至少需要三个步骤：

1、检查支票账户的余额高于200元

2、从支票账户余额中减去200元

3、在储蓄账户余额中增加200元

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有步骤。

事务SQL样本如下：

试想，如果执行到第四条语句时，服务器崩溃了，会发生什么？用户可能会损失200元；再假如，执行到第三条语句和第四条语句之间时，另外一个进程要删除支票账户的所有余额，那么结果可能就是银行在不知道这个逻辑的前提下，白白给了用户200元。因此一个运行良好的系统，必须具有事务的这四个特征：原子性、一致性、隔离性、持久性。

—

隔离级别

隔离性其实比想象的要复杂，SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

读未提交（READ UNCOMMITTED）：在读未提交级别，事务中的修改，即使没有提交，对其他事务也都是可见的。也就是说一个用户可以读取另一个用户还未提交的事务处理数据，也称为“脏读”。

读提交（READ COMMITTED）：除了mysql，其他数据库大多数的默认隔离级别为读提交。这个级别的隔离性解决了前面的脏读，也就是说一个事务只能等到前面事务提交了修改等操作，才能看到修改后的数据。这个级别也叫做不可重复读吗，即两次执行相同的查询，可能出现不一样的结果（因为后一次读取时事务提交了）。

可重复读（REPEATABLE READ）：REPEATABLE READ解决了脏读的问题，保证在同一个事务中多次读取同样记录的结果是一致的。理论上，可重复读还是无法解决另一个幻读的问题，幻读是指当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，之前的事务再次读取时会产生幻行的现象。

可串行化（SERIALIZABLE）：SERIALIZABLE是最高的隔离级别。通过强制事务串行执行，避免了幻读问题。它会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际应用很少用到，仅在非常需要确保数据的一致性而且可以接受无并发的情况下，才考虑使用。

—

死锁

死锁是指两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。例如，设想下面两个事务同时处理StockPrice表：

如果凑巧，两个事务都执行了第一条update语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条update语句，却发现该行记录已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。需要外部因素介入才能解除死锁。InnoDB有死锁检测和死锁超时机制，当检测到死锁的循环依赖时，会立即返回一个错误，另一种方式是，当查询的时间达到锁等待超时的设定之后，放弃锁请求。InnoDB目前处理死锁的方法是，将持有的最少行级排他锁的事务进行回滚。

—

存储引擎

存储引擎也是很多公司面试喜欢问到的一个知识点，在实际工作中其实很少用到或涉及到这一块的知识概念，但是学习和掌握存储引擎的知识是必要的，因为当数据库一旦出现这方面的问题，如果你掌握这方面的知识，解决起来就如鱼得水非常简单了。

命令show table status可以查询数据库某个表的信息：

6.1 InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少被回滚。支持了事务的四个级别，默认是可重复读REAPATABLE READ 加上间隙锁策略防止幻读的出现。

InnoDB表是基于聚族索引建立，聚族索引对主键的查询有很高的性能，二级索引必须包含主键列，如果主键列很大其他的索引都会很大。

InnoDB从磁盘读取数据时采用可预测性读，自动在内存中创建hash索引以加速读操作的自适应哈希索引，可加速插入操作的插入缓冲区等。

InnoDB通过一些机制和工具支持热备份。

6.2 MyISAM存储引擎

mysql5.1之前默认的存储引擎是MyISAM，它提供了大量的特性，包括：全文索引、压缩、空间函数等，不支持事务和行级锁，崩溃后无法安全恢复。对于只读的数据，表比较小、可以忍受修复等操作，依然还可以继续使用MyISAM。

存储：MyISAM将表存储在两个文件中，即数据文件和索引文件，分别以.MYD 和.MYI为扩展名，可包含动态和静态行，根据表的定义来决定采取哪种格式。受限于磁盘空间，或者系统中单个文件的最大尺寸。

特性：加锁和并发，对整张表加锁，读取时对读到的所有表加共享锁，写入时则对表加排他锁。

修复：对于MySQL的MyISAM表，可手工或自动执行检查和修复操作，执行修复可能会导致数据丢失，修复操作很慢。

索引特性：BLOB和TEXT等长字段，可以基于500个字符创建索引，支持全文索引，支持复杂查询。

延迟更新索引键：每次修改完，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，清理缓冲区或者关闭表的时候，才会将对应的索引快写入到磁盘。

MyISAM最典型的问题是表锁，查询到长期处于“Locked”状态时，大概率是表锁引起。

6.3 其他索引

除了innoDB 和MyISAM，还有诸如：ARchive引擎只支持select和insert操作；blackhole引擎没有任何存储机制，会丢失插入的数据，不做任何保存，服务器记录日志；CSV引擎处理普通的CSV文件，不支持索引；Fedrated引擎访问其他mysql服务器的代理，创建远程mysql服务器的客户端连接，将查询传输到远程服务器执行，提取或发送需要的数据；memory引擎快速访问不被修改的数据，重启以后丢失也没关系时，使用memory表很合适，它比MyISAM快一个数量级，在内存中操作，不需要进行磁盘I/O，重启后表结构还在，数据丢失了。