面试常问的MySQL问题

发布时间：2019-03-16作者：spider阅读（2316）

MySQL是一个关系型数据库管理系统，目前属于 Oracle 旗下产品。虽然单机性能比不上oracle，但免费开源，单机成本低且借助于分布式集群所以受到互联网公司的青睐，是互联网公司的主流数据库。

互联网公司面试必问的MySQL题目(上)

01什么是数据库事务?如果没有事物会有什么后果?事务的特性是什么?

事务是指作为单个逻辑工作单元执行的一系列操作，可以被看作一个单元的一系列SQL语句的集合。要么完全地执行，要么完全地不执行。

如果不对数据库进行并发控制，可能会产生脏读、非重复读、幻像读、丢失修改的异常情况。

事务的特性(ACID)

A, atomacity 原子性 事务必须是原子工作单元;对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

C, consistency 一致性

事务将数据库从一种一致状态转变为下一种一致状态。也就是说，事务在完成时，必须使所有的数据都保持一致状态(各种 constraint 不被破坏)。

I, isolation 隔离性 由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。换句话说，一个事务的影响在该事务提交前对其他事务都不可见。

D, durability 持久性

事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

“A向B汇钱100”

读出A账号余额(500)。

A账号扣钱操作(500-100)。

结果写回A账号(400)。

读出B账号余额(500)。

B账号做加法操作(500+100)。

结果写回B账号(600)。

原子性：

保证1-6所有过程要么都执行，要么都不执行。如果异常了那么回滚。

一致性

转账前，A和B的账户中共有500+500=1000元钱。转账后，A和B的账户中共有400+600=1000元。

隔离性

在A向B转账的整个过程中，只要事务还没有提交(commit)，查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。

持久性

一旦转账成功(事务提交)，两个账户的里面的钱就会真的发生变化

02什么是脏读?幻读?不可重复读?什么是事务的隔离级别?Mysql的默认隔离级别是?

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

小A去买东西(卡里有1万元)，当他买单时(事务开启)，系统事先检测到他的卡里有1万，就在这个时候!!小A的妻子要把钱全部转出充当家用，并提交。当系统准备扣款时，再检测卡里的金额，发现已经没钱了(第二次检测金额当然要等待妻子转出金额事务提交完)。A就会很郁闷

分析：这就是读提交，若有事务对数据进行更新(UPDATE)操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

Repeatable read

重复读，就是在开始读取数据(事务开启)时，不再允许修改操作

事例：小A去买东西(卡里有1万元)，当他买单时(事务开启，不允许其他事务的UPDATE修改操作)，收费系统事先检测到他的卡里有1万。这时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读?

事例：小A去买东西，花了2千元，然后他的妻子去查看他的消费记录(全表扫描FTS，妻事务开启)，看到确实是花了2千元，就在这个时候，小A花了1万买了一部电脑，INSERT了一条消费记录，并提交。当妻子打印小A的消费记录清单时(妻子事务提交)，发现花了1.2万元，似乎出现了幻觉，这就是幻读。

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

Mysql的默认隔离级别是Repeatable read。

03事物隔离是怎么实现的?

是基于锁实现的.

有哪些锁?分别介绍下

在DBMS中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

特点

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

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

特点

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

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点

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

04什么是死锁?怎么解决?(前几问题是我个人最喜欢的连环炮，基本可以看出面试者的基础功)

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对付的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率;
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率;

如果业务处理不好可以用分布式事务锁或者使用乐观锁

05SQL的生命周期?关键字的先后顺序?

应用服务器与数据库服务器建立一个连接
数据库进程拿到请求sql
解析并生成执行计划，执行
读取数据到内存并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉连接，释放资源

FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
ON：对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。
OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。
WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。
GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。
CUTE|ROLLUP：把超组插入 VT5，生成 VT6。
HAVING：对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7。
SELECT：处理 SELECT 列表，产生 VT8。
DISTINCT：将重复的行从 VT8 中删除，产品 VT9。
ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。
TOP：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。

06什么是乐观锁?悲观锁?实现方式?

悲观锁：

悲观锁指对数据被意外修改持保守态度，依赖数据库原生支持的锁机制来保证当前事务处理的安全性，防止其他并发事务对目标数据的破坏或破坏其他并发事务数据，将在事务开始执行前或执行中申请锁定，执行完后再释放锁定。这对于长事务来讲，可能会严重影响系统的并发处理能力。自带的数据库事务就是典型的悲观锁。

乐观锁：

乐观锁(Optimistic Lock)，顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

一般是加一个版本号字段每次更新时候比较版本号

07大数据情况下如何做分页?

可以参考阿里巴巴java开发手册上的答案

08什么是数据库连接池?

从上一个sql生命周期题目，可以看到其中的连接在里面发挥着重大作用，但频繁的创建和销毁，非常浪费系统资源。由于数据库更适合长连接，也就有个连接池，能对连接复用，维护连接对象、分配、管理、释放，也可以避免创建大量的连接对DB引发的各种问题;另外通过请求排队，也缓解对DB的冲击。

互联网公司面试必问的MySQL题目(下)

什么是数据库索引?索引有哪几种类型?什么是最左前缀原则?索引算法有哪些?有什么区别?

索引是对数据库表中一列或多列的值进行排序的一种结构。一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

主键索引:

数据列不允许重复，不允许为NULL.一个表只能有一个主键。

唯一索引:

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过

ALTER TABLE table_name ADD UNIQUE (column);

创建唯一索引

可以通过

ALTER TABLE table_name ADD UNIQUE (column1,column2);

创建唯一组合索引

普通索引:

基本的索引类型，没有唯一性的限制，允许为NULL值。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引：

是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

最左前缀

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

还有一个就是生效原则比如

index(a,b,c) 
where a=3    只使用了a 
where a=3 and b=5    使用了a,b 
where a=3 and b=5 and c=4    使用了a,b,c 
where b=3 or where c=4    没有使用索引 
where a=3 and c=4    仅使用了a 
where a=3 and b>10 and c=7    使用了a,b 
where a=3 and b like ' xx%' and c=7    使用了a,b

索引算法有 BTree Hash

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，

例如：

select * from user where name like 'jack%';

如果一通配符开头，或者没有使用常量，则不会使用索引，例如：

select * from user where name like '%jack';

Hash

Hash索引只能用于对等比较，例如=,<=>(相当于=)操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

BTree索引是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符

例如：

只要它的查询条件是一个不以通配符开头的常量select * from user where name like 'jack%'; 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： select * from user where name like '%jack';

Hash

索引设计的原则?

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的类，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

如何定位及优化SQL语句的性能问题?

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划。

我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。

而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

执行计划包含的信息

有一组数字组成。表示一个查询中各个子查询的执行顺序;

id相同执行顺序由上至下。
id不同，id值越大优先级越高，越先被执行。
id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type

每个子查询的查询类型，一些常见的查询类型。

id	select_type	description
1	SIMPLE	不包含任何子查询或union等查询
2	PRIMARY	包含子查询最外层查询就显示为 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查询
4	DERIVED	from字句中包含的查询
5	UNION	出现在union后的查询语句中
6	UNION RESULT	从UNION中获取结果集，例如上文的第三个例子

table

查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id

partitions

表分区、表创建的时候可以指定通过那个列进行表分区。举个例子：

create table tmp ( 
    id int unsigned not null AUTO_INCREMENT, 
    name varchar(255), 
    PRIMARY KEY (id) 
) engine = innodb 
partition by key (id) partitions 5;

type(非常重要，可以看到有没有走索引)

访问类型

ALL 扫描全表数据

index 遍历索引

range 索引范围查找

index_subquery 在子查询中使用 ref

unique_subquery 在子查询中使用 eq_ref

ref_or_null 对Null进行索引的优化的 ref

fulltext 使用全文索引

ref 使用非唯一索引查找数据

eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

possible_keys

可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

key_length

索引长度

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

返回估算的结果集数目，并不是一个准确的值。

extra

extra的信息非常丰富，常见的有：

Using index 使用覆盖索引
Using where 使用了用where子句来过滤结果集
Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
Using temporary 使用了临时表

sql优化的目标可以参考阿里开发手册

某个表有近千万数据，CRUD比较慢，如何优化?分库分表了是怎么做的?分表分库了有什么问题?有用到中间件么?他们的原理知道么?

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

可以做表拆分，减少单表字段数量，优化表结构。

在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

主要两种拆分垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的，数据较大，长度较长(比如text类型字段)的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。切分后，要放在多个服务器上，而不是一个服务器上。为什么? 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前，全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上，随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平分表

针对数据量巨大的单张表(比如订单表)，按照某种规则(RANGE,HASH取模等)，切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

水平分库分表切分规则

1.RANGE从

0到10000一个表，10001到20000一个表;

2.HASH取模

一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。取用户id，然后hash取模，分配到不同的数据库上。

3.地理区域

比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

4.时间

按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

分库分表后面临的问题

事务支持

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价; 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库join

只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

分库分表方案产品

跨节点的count,order by,group by以及聚合函数问题

这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题

来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性(如对4取余得1的数对2取余也是1)来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

ID问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的;另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由.

一些常见的主键生成策略

UUID

使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。

Twitter的分布式自增ID算法Snowflake

在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位机器ID 10位毫秒内序列12位。

跨分片的排序分页

般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下图所示：