1、事务具有四个特征

事务就是一组原子性的操作，这些操作要么全部发生，要么全部不发生。事务把数据库从一种一致性状态转换成另一种一致性状态。

• 原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

• 一致性。事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

• 隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的//操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

2、 Mysql的四种隔离级别以及带来的问题

• Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓 的 不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。

• Serializable（可串行化）

通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。



【拓展】

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

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

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

不可重复读侧重于修改，幻读侧重于新增或删除（多了或少量行），脏读是一个事务回滚影响另外一个事务。

InnoDB 默认隔离级别为repeatable read，但是通过next-key lock 解决了幻读，保证了ACID

3、 MySQL支持哪些存储引擎?

MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎。

MyISAM 和 InnoDB 的区别有哪些：

• InnoDB支持事务，MyISAM不支持

• InnoDB支持外键，而MyISAM不支持

• InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

• Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

• InnoDB不保存表的具体行数，MyISAM用一个变量保存了整个表的行数。

• MyISAM采用表级锁(table-level locking)；InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁。

4 、聚簇索引

在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引. 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询.

那非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。比如：SELECT id FROM t_user WHERE id =1;

5、MySQL中的varchar和char有什么区别.

char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char。

6、超大分页怎么处理?

数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快。

7. 什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。

1、更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。 2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好。

8、B+树索引 和 哈希索引、B树 的区别

b树：叶子节点跟非叶子节点都储存数据，有序数组+平衡多叉树

b+树：只有在叶子节点储存数据，有序数组链表+平衡多叉树。

hash索引：Hash 索引仅仅能满足"=",和"<=>"等值查询，不能使用范围查询。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，但是Hash 索引在任何时候都不能避免表扫描。

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

9、事务的实现原理

事务是基于重做日志文件(redo log)和回滚日志(undo log)实现的。

每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。

每当有修改事务时，还会产生undo log，如果需要回滚，则根据undo log 的反向语句进行逻辑操作，比如insert 一条记录就delete 一条记录。undo log 主要实现数据库的一致性。

10、drop、delete与truncate的区别

• drop直接删掉表有关的一切（数据/结构/约束…），不会记录日志，为DDL(Data Definition Language,数据库定义语言)操作。

• truncate 删除表中所有数据（再插入时自增长id又从1开始），该操作也不会记录日志所以比较快，为DDL操作。只能删table。

• DELETE语句执行删除的过程是每次从表中删除一行，需要记录日志，比较慢，可以加where 语句，为DML（Data Manipulation Language, 数据操纵语言）。

速度上drop > truncate > delete

11、 MySQL执行查询的过程

1. 客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配

2. 查缓存。（当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。）

3. 语法分析（SQL语法是否写错了）。 如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。

4. 优化。是否使用索引，生成执行计划。

5. 交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。



更新语句执行会复杂一点。需要检查表是否有排它锁，写binlog，刷盘，是否执行commit。

12、哪些情况需要创建索引？

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段

3. 查询中与其他表关联的字段，外键关系建立索引

4. 单键/组合索引的选择问题，高并发下倾向创建组合索引

5. 查询中排序的字段，排序字段通过索引访问大幅提高排序速度

6. 查询中统计或分组字段

13、哪些情况不要创建索引？

1. 表记录太少

2. 经常增删改的表

3. 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）

4. 频繁更新的字段不适合创建索引（会加重IO负担）

5. where条件里用不到的字段不创建索引

14、百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）

2. 然后删除其中无用数据（此过程需要不到两分钟）

3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。

4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

15、索引

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree索引和Hash索引。

索引一般分为：

• 普通索引：对关键字没有限制

• 唯一索引：要求记录提供的关键字不能重复

• 主键索引：要求关键字唯一且不为null

删除索引语法：

alter table 表名 drop KEY 索引名

16、MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed.

• statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.

• row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。

• mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row. 此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.

17、为啥要使用not null？

MySQL中not null其实占空间的，空值才是不占空间的，但这不是禁止使用null的重点。

• （1）所有使用NULL值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。

• （2）NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。

注意：但把NULL列改为NOT NULL带来的性能提示很小，除非确定它带来了问题，否则不要把它当成优先的优化措施，最重要的是使用的列的类型的适当性。

• （3）NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp

• （4）NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错。

18、如何查看慢SQL？

MySQL为我们提供了 explain 关键字来直观的查看一条SQL的执行计划。

例如执行：





执行计划：





（1）id

SELECT识别符， 这个不重要。

（2）select_type

SIMPLE: 指示非子查询和union的简单查询。

PRIMARY:最外面的SELECT。

UNION:UNION中的第二个或后面的SELECT语句

（3）table

输出的行所引用的表。

（4）type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

system:表仅有一行(=系统表)。这是const联接类型的一个特例。

const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。

ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。

ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。

index_merge:该联接类型表示使用了索引合并优化方法。

unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range:只检索给定范围的行,使用一个索引来选择行。

index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，说明查询就需要优化了。

一般来说，得保证查询至少达到range级别，最好能达到ref。

（5）possible_keys

表示查询时，可能使用的索引，但是不一定使用了这个索引。

（6）key

实际上是使用的索引

如果没有选择索引,键是NULL。PRIMARY表示使用了主键索引。

可以看到上面 t_images 这个表没有命中索引。

（7）key_len

显示MySQL决定使用的键长度。如果key是NULL,则长度为NULL。在不损失精确性的情况下，长度越短越好。

（8）ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，这里是const 表示，左连接是使用了索引。

（9） rows

很重要的一个字段。mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好。

可以看到上面t_images 这个表扫描了41673行。

（10）extra

explain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:

• using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。

• using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。

• using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。

• using where ：表名使用了where过滤。

分享

这只是面试题的一部分，完整整套面试PDF版，只需关注公众号【Java斗帝】回复“666”即可免费获取；