MySQL

一 介绍

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

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

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

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

事务的特性（ACID）

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

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

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

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

“A向B汇钱100”

1. 读出A账号余额（500）。
2. A账号扣钱操作（500-100）。
3. 结果写回A账号（400）。
4. 读出B账号余额（500）。
5. B账号做加法操作（500+100）。
6. 结果写回B账号（600）。

原子性：

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

一致性

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

隔离性

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

持久性

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

 

2 什么是脏读？幻读？不可重复读？什么是事务的隔离级别？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。

 

3 事物隔离是怎么实现的？

是基于锁实现的.

有哪些锁？分别介绍下

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

行级锁

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

特点

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

表级锁

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

特点

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

页级锁

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

特点

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

 

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

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

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

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

 

5 SQL的生命周期？关键字的先后顺序？

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

1、 FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。

2、 ON：对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。

3、 OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留表中未找到

匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的

结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。

4、 WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。

5、 GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。

6、 CUTE|ROLLUP：把超组插入 VT5，生成 VT6。

7、 HAVING：对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7。

8、 SELECT：处理 SELECT 列表，产生 VT8。

9、 DISTINCT：将重复的行从 VT8 中删除，产品 VT9。

10、 ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。

11、 TOP：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。

6 什么是乐观锁？悲观锁？实现方式？

悲观锁：

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

乐观锁：

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

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

 

7 大数据情况下如何做分页？

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

 

8 什么是数据库连接池？

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

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

 

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

主键索引:

数据列不允许重复，不允许为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索引。

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

 

索引设计的原则？

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

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

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

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

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

 

执行计划包含的信息

id

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

• 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的信息非常丰富，常见的有：

1.Using index 使用覆盖索引

2.Using where 使用了用where子句来过滤结果集

3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。

4.Using temporary 使用了临时表

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

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

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

• 可以做表拆分，减少单表字段数量，优化表结构。
• 在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

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

垂直分表

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

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

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

水平分表

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

水平分库分表

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

水平分库分表切分规则

1. RANGE划分：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位。

• 跨分片的排序分页

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

mysql中 in 和exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

1. 如果查询的两个表大小相当，那么用in和exists差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
3. not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

---

1、数据库设计的三大范式？

• 第一范式1NF: 数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。如订单信息列为orderInfo = "DD1024 2018.5.18"，必须拆分为orderId和orderTime。
• 第二范式2NF: 在满足第一范式的基础上，表中的所有列都必需依赖于主键（和主键有关系），其他和主键没有关系的列可以拆分出去。通俗点说就是：一个表只描述一件事情。比如order表中有orderId、orderTime、userId和userName，只有前两列依赖于订单表，后两列需要拆分到user表中。
• 第三范式3NF: 在满足第二范式的基础上，要求数据不能有传递关系。表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。比如order表中有orderId、orderTime、userId和userName，根据orderId可以查出userId，根据userId又可以查出userName，这就是数据的传递性，完全可以只留下userId这一列。

2、MySql的事务隔离级别？推荐使用哪种？

• 读未提交
• 读已提交
• 可重复读
• 串行化

在具体解释上面的四个隔离级别前。有必要了解事务的四大特性（ACID）

推荐阅读这篇博客：

https://www.cnblogs.com/huanongying/p/7021555.html

• 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）：事务开始前和结束后，数据的完整性约束没有被破环。比如A向B转了钱，转账前后钱的总数不变。
• 隔离性（Isolation）：多个用户并发访问数据数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间的数据相互隔离。比如事务A和事务B都修改同一条记录，这条记录就会被重复修改或者后者会覆盖前者的修改记录。
• 持久性（Durability）：事务完成后，事务对数据库的更新被保存到数据库，其结果是永久的。

事务并发可能产生的问题： 脏数据：事务对缓冲池中的行记录进行修改，但是还没有被提交。

• 脏读：事务A读取到了事务B修改但未提交的数据。如果此时B回滚到修改之前的状态，A就读到了脏数据。
• 不可重复读：事务A多次读取同一个数据，此时事务B在A读取过程中对数据修改并提交了，导致事务A在同一个事务中多次读取同一数据而结果不同。
• 幻读：事务A对表进行修改，这个修改涉及到表中所有的行，但此时事务B新插入了一条数据，事务A就会发现居然还有数据没有被修改，就好像发生幻觉一样。

脏读是读取到事务未提交的数据，不可重复度读读取到的是提交提交后的数据，只不过在一次事务中读取结果不一样。

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

一般来说，数据库隔离级别不一样，可能出现的并发问题也不同。级别最高的是串行化，所有问题都不会出现。但是在并发下性能极低，可重复读会只会导致幻读。

所以一般使用MySQL默认的可重复读即可。MVCC（多版本并发控制）使用undo_log使得事务可以读取到数据的快照（某个历史版本），从而实现了可重复读。MySQL采用Next-Key Lock算法，对于索引的扫描不仅是锁住扫描到的索引，还锁住了这些索引覆盖的范围，避免了不可重复读和幻读的产生。

3、MySql数据库在什么情况下出现死锁？产生死锁的四个必要条件？如何解决死锁？

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，若无外力作用两个事务都无法推进，这样就产生了死锁。下去 死锁的四个必要条件：

• 互斥条件：即任何时刻，一个资源只能被一个进程使用。其他进程必须等待。
• 请求和保持条件：即当资源请求者在请求其他的资源的同时保持对原有资源的占有且不释放。
• 不剥夺条件：资源请求者不能强制从资源占有者手中夺取资源，资源只能由资源占有者主动释放。
• 环路等待条件：比如A占有B在等待的资源（B等待A释放），B占有A在等待的资源（A等待B释放）。多个进程循环等待着相邻进程占用着的资源。

避免死锁可以通过破环四个必要条件之一。

解决死锁的方法：

• 加锁顺序保持一致。不同的加锁顺序很可能导致死锁，比如哲学家问题：A先申请筷子1在申请筷子2，而B先申请筷子2在申请筷子1，最后谁也得不到一双筷子（同时拥有筷子1和筷子2）
• 超时，为其中一个事务设置等待时间，若超过这个阈值事务就回滚，另一个等待的事务就能得以继续执行。
• 及时检测出死锁，回滚undo量最小的事务。一般是采用等待图（wait-for gragh）。采用深度优先搜索的算法实现，如果图中有环路就说明存在死锁。

4、现在发现sql查询很慢，如何分析哪里出了问题，应该如何优化？

开启慢查询，查找哪些sql语句执行得慢。使用explain查看语句的执行计划，比如有没有使用到索引，是否启用了全表扫描等。查询慢，很大可能是因为没有使用索引或者索引没有被命中。还有其他的原因，比如发生了死锁，硬件、网速等原因。

优化手段：为相关列添加索引，并且确保索引可以被命中。优化sql语句的编写。

5、索引的好处？

索引是对数据库表中一个或多个列的值进行排序的结构。MySql中索引是B+树，在查找时可以利用二分查找等高效率的查找方式，以O(lg n)的时间找到。因此索引可以加快查询速度。

6、哪些情况需要建立索引？

• 在经常要搜索的列上
• 经常出现在where后面的列上
• 在作为主键的列上
• 作为外键的列上
• 经常需要排序、分组和联合操作的字段建立索引

哪些情况不适合建立索引？

• 查询中很少使用的字段
• 数值太少的字段
• 唯一性不太差的字段
• 更新频繁的字段
• 不会出现在where后的字段
• 索引适合建立在小字段上，text和blob等大字段不适合建立索引

7、索引的最左匹配原则了解吗？

建了一个(a,b,c)的联合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，但是有时在条件查询时只会匹配到a或者(a, b)而不会匹配到(a, b, c)。下面的例子

1. SELECT * FROM table WHERE a = 1 AND c = 3; // 使用了索引a，c不走索引
2. SELECT * FROM table WHERE a = 1 AND b < 2 AND c = 3; // 使用到了索引(a,b)，c不走索引

建立联合索引(a, b ,c)，所以索引是按照a -> b -> c的顺序进行排序的。a-b-c这样的索引是先找a，然后在范围里面找b，再在范围内找c。 所以上面的语句里的c 会分散在很多个b里面且不是排序的，所以没办法走索引。

举个例子比如(a, b)联合索引，先按a排序再按b排序，得到

1. (1,1)->(1, 2)->(2, 1)  (2, 4)->(3, 1)->(3, 2)

如果执行 selectafromtablewhereb=2，就没有使用到(a, b)这个联合索引，因为b的值1,2,1,4,1,2显然不是排序的。

具体来说：MySQL会从左开始一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配，比如： a = 1 AND b = 2 AND c > 3 AND d = 4，如果建立 （a,b,c,d）顺序的索引，使用了索引(a, b, c)，但是d是没有走索引的，如果建立（a,b,d,c）的索引，则可以命中索引(a, b, c, d)，其中a,b,d的顺序可以任意调整。

等于（=）和in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序。

8、如何建立复合索引，可以使sql语句能尽可能匹配到索引？

• 等于条件的索引放在前面（最左），范围查询放在后面。 a=1AND b=2AND c>3AND d=4，建立（a, b, d, c）就是不错的选择；
• 先过滤后排序（ORDER BY）如 SELECT*FROM t WHERE c=100andd='xyz'ORDER BY b建立(c, d, b)联合索引就是不错的选择
• 对于索引列的查询，一般不建议使用LIKE操作，像 LIKE'%abc'这样的不能命中索引；不过 LIKE'abc%'可以命中索引。

9、建立了索引，索引就一定会被命中吗？或者说索引什么时候失效

• 使用了 notin,<>,!=则不会命中索引。注： <>是不等号
• innoDB引擎下，若使用OR，只有前后两个列都有索引才能命中（执行查询计划，type是index_merge），否则不会使用索引。
• 模糊查询中，通配符在最前面时，即 LIKE'%abc'这样不能命中索引
• 对列进行函数运算的情况（如 where md5(password) = "xxxx"）
• 联合索引中，遇到范围查询时，其后的索引不会被命中
• 存了数字的char或varchar类型，常见的如用字符串表示的手机号，在查询时不加引号，则不会命中（如where phone=‘13340456789’能命中，where phone=13340456789不能命中）
• 当数据量小时，MySQL发现全表扫描反而比使用索引查询更快时不会使用索引。

10、为什么要使用联合索引？

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

推荐阅读这篇博客

• 减少开销。建了一个(a,b,c)的联合索引，相当于建了(a),(a,b),(a,b,c)三个索引
• 覆盖索引。减少了随机IO操作。同样的有复合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作
• 效率高。索引列越多，通过索引筛选出的数据越少。比如有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页。

11、既然索引可以加快查询速度，索引越多越好是吗？

推荐阅读这篇优博客：

https://www.jb51.net/article/81875.htm

大多数情况下索引能大幅度提高查询效率，但数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本和更多的空间 （一本100页的书，却有50页目录？）而且过小的表，建立索引可能会更慢（读个2页的宣传手册，你还先去找目录？）

12、主键和唯一索引的区别？

• 主键是一种约束，唯一索引是索引，一种数据结构。
• 主键一定是唯一索引，唯一索引不一定是主键。
• 一个表中可以有多个唯一索引，但只能有一个主键。
• 主键不允许空值，唯一索引允许。
• 主键可以做为外键，唯一索引不行；

13、B+树和B-树的区别？

B-树是一种平衡的多路查找树。2-3树和2-3-4树都是B-树的特例。一棵M阶的B-树，除了根结点外的其他非叶子结点，最多含有M-1对键和链接，最少含有M/2对键和链接。根结点可以少于M/2，但是也不能少于2对。

• 关键字集合分布在整颗树中
• 每个元素在该树中只出现一次，可能在叶子结点上，也可能在非叶子结点上。
• 搜索有可能在非叶子结点结束。
• 所有叶子结点位于同一层

B+树是B-树的变体，也是一种多路查找树。

• 非叶子结点值可以看作索引，仅含有其子树中的最大（或）最小关键字。
• 叶子结点保存了所有关键字，且叶子结点按照从小到大的顺序排列，是一个双向链表结构。
• 只能在叶子节点命中搜索

B+ 树更适合用于数据库和操作系统的文件系统中。

假设一个结点就是一个页面，B树遍历所有记录，通过中序遍历的方式，要多次返回到父结点，同一个结点多次访问了，增加了磁盘I/O操作的次数。B+因为在叶子结点存放了所有的记录，而且是双向链表的结构，只需在叶子节点这一层就能遍历所有记录，大大减少了磁盘I/O操作，所以数据库索引用B+树结构更好。

14、聚集索引与非聚集索引的区别？

• 对于聚集索引，表记录的排列顺序和与索引的排列顺序是一致的；非聚集索引不是
• 聚集索引就是按每张表的主键构造一棵B+树，每张表只能拥有一个聚集索引；一张表可以有多个非聚集索引
• 聚集索引的叶子结点存放的是整张表的行记录数据；非聚集索引的叶子结点并不包含行记录的全部数据，除了包含键值还包含一个书签——即相应行数据的聚集索引键。因此通过非聚集索引查找时，先根据叶子结点的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

15、InnoDB和MyISAM引擎的区别？

• InnoDB支持事务，MyISAM不支持
• InnoDB是行锁设计，MyISAM是表锁设计
• InnoDB支持外键，MyISAM不支持
• InnoDB采用聚集的方式，每张表按照主键的顺序进行存放。如果没有主键，InnoDB会为每一行生成一个6字节的ROWID并以此为主键；MyISAM可以不指定主键和索引
• InnoDB没有保存表的总行数，因此查询行数时会遍历整表；而MyISAM有一个变量存储可表的总行数，查询时可以直接取出该值
• InnoDB适合联机事务处理(OLTP)，MyISAM适合联机分析处理(OLAP)

16、 COUNT(*)和 COUNT(1)的区别？ COUNT(列名)和 COUNT(*)的区别？

COUNT(*)和 COUNT(1)没区别。 COUNT(列名)和 COUNT(*)区别在于前者不会统计列为NULL的数据，后者会统计。

17、数据库中悲观锁和乐观锁讲一讲？

悲观锁：总是假设在并发下会出现问题，即假设多个事务对同一个数据的访问会产生冲突。当其他事务想要访问数据时，会在临界区提前加锁，需要将其阻塞挂起。比如MySQL中的排他锁（X锁）、和共享锁（S锁）

乐观锁： 总是假设任务在并发下是安全的，即假设多个事务对同一个数据的访问不会发生冲突，因此不会加锁，就对数据进行修改。当遇到冲突时，采用CAS或者版本号、时间戳的方式来解决冲突。数据库中使用的乐观锁是版本号或时间戳。乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，不用加锁就尝试对数据进行修改，在修改之前先检查一下版本号，真正提交事务时，再检查版本号有，如果不相同说明已经被其他事务修改了，可以选择回滚当前事务或者重试；如果版本号相同，则可以修改。

提一下乐观锁和MVCC的区别，其实MVCC也利用了版本号，和乐观锁还是能扯上些关系。

MVCC主要解决了读-写的阻塞，因为读只能读到数据的历史版本（快照）；OCC主要解决了写-写的阻塞，多个事务对数据进行修改而不加锁，更新失败的事务可以选择回滚或者重试。

当多个用户/进程/线程同时对数据库进行操作时，会出现3种冲突情形：读-读，不存在任何问题；读-写，有隔离性问题，可能遇到脏读、不可重复读 、幻读等。写-写，可能丢失更新。多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，读操作只读该事务开始前的数据库的快照，实现了一致性非锁定读。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，不用加锁就尝试对数据进行修改，在修改之前先检查一下版本号，真正提交事务时，再检查版本号有，如果不相同说明已经被其他事务修改了，可以选择回滚当前事务或者重试；如果版本号相同，则可以修改。

18、MySQL的可重复读是如何实现的？

MVCC（多版本并发控制）使用undo_log使得事务可以读取到数据的快照（某个历史版本），从而实现了可重复读。MySQL采用Next-Key Lock算法，对于索引的扫描不仅是锁住扫描到的索引，还锁住了这些索引覆盖的范围，避免了不可重复读和幻读的产生。

具体来说：

在可重复读下： select....from会采用MVCC实现的一致性非锁定读，读取的是事务开始的快照，避免了不可重复读。select .....from .... for update会采用 Next-Key Locking来保证可重复读和幻读。

在读已提交下: select....from 会采用快照，读取的是最新一份的快照数据，不能够保证不可重复读和幻读；select .....from .... for update会采用Record Lock，不能够保证不可重复读/幻读。

19、覆盖索引是什么？

如果一个索引包含(或覆盖)所有需要查询的字段的值，即只需扫描索引而无须回表，这称为“覆盖索引”。InnoDB的辅助索引在叶子节点中保存了部分键值信息以及指向聚集索引键的指针，如果辅助索引叶子结点中的键值信息已经覆盖了要查询的字段，就没有必要利用指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录了。

20、MySQL中JOIN和UNION什么区别？

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相同的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。默认情况下，UNION会过滤掉重复的值。使用 UNION ALL则会包含重复的值。

JOIN用于连接两个有关联的表，筛选两个表中满足条件（ON后的条件）的行记录得到一个结果集。从结果集中SELECT的字段可以是表A或者表B中的任意列。

JOIN常用的有LEFT JOIN、RIGHT JOIN、INNER JOIN。

• LEFT JOIN会以左表为基础，包含左表的所有记录，以及右表中匹配ON条件的记录，对于未匹配的列，会以NULL表示。
• LEFT JOIN会以右表为基础，包含左表的所有记录，以及右表匹配ON条件的记录，对于未匹配的列，会以NULL表示。
• INNER JOIN，产生两个表的交集（只包含满足ON条件的记录）

21、WHERE和HAVING的区别？

• WHERE过滤的是行，HAVING过滤分组。
• WHERE能完成的，都可以用HAVING(只是有时候没必要)
• WHERE在分组前对数据进行过滤，HAVING在分组后对数据进行过滤
• WHERE后不能接聚合函数，HAVING后面通常都有聚合函数

22、SQL注入是什么，如何防止？

所谓SQL注入式攻击，就是攻击者把SQL命令插入到Web表单的输入域或页面请求的查询字符串，欺骗服务器执行恶意的SQL命令。

比如在登录界面，如果用户名填入 'xxx'OR1=1--就能构造下面的SQL语句，因为OR 1=1，password被注释掉，因此无论name和password填入什么都能登录成功。

1. SELECT * FROM USER WHERE NAME='xxx' OR 1=1 -- and password='xxx';

使用PrepareStatement，可以防止sql注入攻击，sql的执行需要编译，注入问题之所以出现，是因为用户填写 sql语句参与了编译。使用PrepareStatement对象在执行sql语句时，会分为两步，第一步将sql语句 "运送" 到mysql上预编译，再回到java端拿到参数运送到mysql端。预先编译好，也就是SQL引擎会预先进行语法分析，产生语法树，生成执行计划，也就是说，后面你输入的参数，无论你输入的是什么，都不会影响该sql语句的语法结构了。用户填写的 sql语句，就不会参与编译，只会当做参数来看。从而避免了sql注入问题。