关于java:阿里18道常见的MySQL面试题含解析

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”即可收费获取；

看完三件事❤️

如果你感觉这篇内容对你还蛮有帮忙，我想邀请你帮我三个小忙：

1. 点赞，转发，有你们的『点赞和评论』，才是我发明的能源。
2. 关注公众号『Java 斗帝』，不定期分享原创常识。
3. 同时公众号内回复“666”即可收费支付 1000 道互联网面试题