关于java:持续输出面试题系列之MySQL篇

开篇介绍

大家好，我是 Java 最全面试题库 的提裤姐，明天这篇是数据库面试题系列的第一篇，次要总结了 MySQL 相干的面试题；在后续，会沿着第一篇开篇的常识线路始终总结上来，做到日更！如果我能做到百日百更，心愿你也能够跟着百日百刷，一百天养成一个好习惯。

SQL 的 select 语句残缺的执行程序？

1、from 子句组装来自不同数据源的数据；
2、where 子句基于指定的条件对记录行进行筛选；
3、group by 子句将数据划分为多个分组；
4、应用 汇集函数 进行计算；
5、应用 having 子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、应用 order by 对后果集进行排序。

左连贯和右连贯的区别?

外连贯：

• 左连贯（左外连贯）：以左表作为基准进行查问，左表数据会全副显示进去，右表如果和左表匹配的

数据则显示相应字段的数据，如果不匹配则显示为 null。

• 右连贯（右外连贯）：以右表作为基准进行查问，右表数据会全副显示进去，左表如果和右表匹配的

数据则显示相应字段的数据，如果不匹配则显示为 null。

全连贯：
先以左表进行左外连贯，再以右表进行右外连贯

内连贯：
显示表之间有连贯匹配的所有行。

什么是 sql 注入？如何避免 sql 注入？

sql 注入
通过在 Web 表单中输出（歹意）SQL 语句失去一个存在安全漏洞的网站上的数据库，而不是依照设计者用意去执行 SQL 语句。
举例：当执行的 sql 为 select * from user where username = "admin" or "a"="a"时，sql 语句恒成立，参数 admin 毫无意义。

避免 sql 注入的形式：

1. 预编译语句：如，select * from user where username =？，sql 语句语义不会产生扭转，sql 语句中变量用？示意，即便传递参数时为 ”admin or ‘a’= ‘a'”，也会把这整体当做一个字符创去查问。
2. Mybatis 框架中的 mapper 形式中的 # 也能很大水平的避免 sql 注入（$ 无奈避免 sql 注入）。

有哪些 sql 优化办法？

1、当只有一行数据时应用 limit 1
查问时如果已知会失去一条数据，这种状况下加上 limit 1 会减少性能。因为 mysql 数据库引擎会在找到一条后果进行搜寻，而不是持续查问下一条是否符合标准直到所有记录查问结束。

2、抉择正确的数据库引擎
Mysql 中有两个引擎 MyISAM 和 InnoDB，每个引擎有利有弊。

①MyISAM 实用于一些大量查问的利用，但对于有大量写性能的利用不是很好。甚至你只须要 update 一个字段整个表都会被锁起来。而别的过程就算是读操作也不行要等到以后 update 操作实现之后能力持续进行。另外，MyISAM 对于 select count(*)这类操作是超级快的。
②InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的利用会比 MyISAM 还慢，然而反对“行锁”，所以在写操作比拟多的时候会比拟优良。并且，它反对很多的高级利用，例如：事务。

1. 用 not exists 代替 not in

Not exists 用到了连贯可能施展曾经建设好的索引的作用，not in 不能应用索引。Not in 是最慢的形式要同每条记录比拟，在数据量比拟大的操作时不倡议应用这种形式。

1. 对操作符的优化，尽量不采纳不利于索引的操作符

如：in not in is null is not null <> 等
某个字段总要拿来搜寻，为其建设索引：
Mysql 中应用 alter table 语句来为表中的字段增加索引：alter table 表明 add index (字段名)；

Mysql 存储引擎有哪些？

1.InnoDB 存储引擎
InnoDB 是事务型数据库的首选引擎，反对事务平安表（ACID），反对行锁定和外键，InnoDB 是默认的 MySQL 引擎。
2.MyISAM 存储引擎
MyISAM 基于 ISAM 存储引擎，并对其进行扩大。它是在 Web、数据仓储和其余应用环境下最常应用的存储引擎之一。MyISAM 领有较高的插入、查问速度，但不反对事物。
3.MEMORY 存储引擎
MEMORY 存储引擎将表中的数据存储到内存中，未查问和援用其余表数据提供快速访问。
4.NDB 存储引擎
DB 存储引擎是一个集群存储引擎，相似于 Oracle 的 RAC，但它是 Share Nothing 的架构，因而能提供更高级别的高可用性和可扩展性。NDB 的特点是数据全副放在内存中，因而通过主键查找十分快。
对于 NDB，有一个问题须要留神，它的连贯 (join) 操作是在 MySQL 数据库层实现，不是在存储引擎层实现，这意味着，简单的 join 操作须要微小的网络开销，查问速度会很慢。
5.Memory (Heap) 存储引擎
Memory 存储引擎（之前称为 Heap）将表中数据寄存在内存中，如果数据库重启或解体，数据失落，因而它非常适合存储长期数据。
6.Archive 存储引擎
正如其名称所示，Archive 非常适合存储归档数据，如日志信息。它只反对 INSERT 和 SELECT 操作，其设计的次要目标是提供高速的插入和压缩性能。
7.Federated 存储引擎
Federated 存储引擎不存放数据，它至多指向一台近程 MySQL 数据库服务器上的表，十分相似于 Oracle 的通明网关。
8.Maria 存储引擎
Maria 存储引擎是新开发的引擎，其设计指标是用来取代原有的 MyISAM 存储引擎，从而成为 MySQL 默认
的存储引擎。

上述引擎中，InnoDB 是事务平安的存储引擎，设计上借鉴了很多 Oracle 的架构思维，一般而言，在 OLTP
利用中，InnoDB 应该作为外围利用表的首先存储引擎。InnoDB 是由第三方的 Innobase Oy 公司开发，现已被 Oracle 收买，创始人是 Heikki Tuuri，芬兰赫尔辛基人，和驰名的 Linux 创始人 Linus 是校友。

事务的四大特色是什么？

数据库事务 transanction 正确执行的四个基本要素。ACID，原子性 (Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性 ：整个事务中的所有操作，要么全副实现，要么全副不实现，不可能停滞在两头某个环节。事务在执
行过程中产生谬误，会被回滚（Rollback）到事务开始前的状态，就像这个事务素来没有执行过一样。
一致性 ：在事务开始之前和事务完结当前，数据库的完整性束缚没有被毁坏。
隔离性 ：隔离状态执行事务，使它们如同是零碎在给定工夫内执行的惟一操作。如果有两个事务，运行在相
同的工夫内，执行 雷同的性能，事务的隔离性将确保每一事务在零碎中认为只有该事务在应用零碎。这种属性有时称为串行化，为了避免事务操作间的混同，必须串行化或序列化请 求，使得在同一时间仅有一个申请用于同一数据。
持久性：在事务实现当前，该事务所对数据库所作的更改便长久的保留在数据库之中，并不会被回滚。

MySQL 索引的“应用”注意事项

1. 防止在 WHERE 子句中应用 != 或 <> 操作符，否则将引擎放弃应用索引而进行全表扫描。优化器将无奈通过索引来确定将要命中的行数, 因而须要搜寻该表的所有行。

2. 防止在 WHERE 子句中应用 OR 来连贯条件，否则将导致引擎放弃应用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20。

3. 防止在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃应用索引而进行全表扫描。

4. 防止在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃应用索引而进行全表扫描。

5.LIKE 查问，% 不能在前，因为无奈应用索引。如果须要含糊匹配，能够应用全文索引。

为什么 mysql 倡议应用自增主键？

1、如果咱们定义了主键 (PRIMARY KEY)，那么 InnoDB 会抉择主键作为汇集索引。
如果没有显式定义主键，则 InnoDB 会抉择第一个不蕴含有 NULL 值的惟一索引作为主键索引。
如果也没有这样的惟一索引，则 InnoDB 会抉择内置 6 字节长的 ROWID 作为隐含的汇集索引(ROWID 随着行记录的写入而主键递增，这个 ROWID 不像 ORACLE 的 ROWID 那样可援用，是隐含的)。

2、数据记录自身被存于主索引（一颗 B +Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键程序寄存。
因而每当有一条新的记录插入时，MySQL 会依据其主键将其插入适当的节点和地位，如果页面达到装载因子（InnoDB 默认为 15/16），则开拓一个新的页（节点）

3、如果表应用自增主键，那么每次插入新的记录，记录就会程序增加到以后索引节点的后续地位，当一页写满，就会主动开拓一个新的页。

4、如果应用非自增主键（如果身份证号或学号等），因为每次插入主键的值近似于随机，因而每次新纪录都要被插到现有索引页得两头某个地位，此时 MySQL 不得不为了将新记录插到适合地位而挪动数据，甚至指标页面可能曾经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这减少了很多开销，同时频繁的挪动、分页操作造成了大量的碎片，失去了不够紧凑的索引构造，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

mysql rr 级别如何解决幻读问题？

该隔离级别是 MySQL 默认的隔离级别 ，在同一个事务里，select 的后果是事务开始时工夫点的状态，因而，同样的 select 操作读到的后果会是统一的，然而，会有幻读景象。
MySQL 的 InnoDB 引擎能够通过 next-key locks 机制来防止幻读。InnoDB 存储引擎应用三种行锁的算法用来满足相干事务隔离级别的要求:
Record Locks
该锁为索引记录上的锁，如果表中没有定义索引，InnoDB 会默认为该表创立一个暗藏的聚簇索引，并应用该索引锁定记录。

Gap Locks
该锁会锁定一个范畴，然而不括记录自身。能够通过批改隔离级别为 READ COMMITTED 或者配置 innodb_locks_unsafe_for_binlog 参数为 ON。

Next-key Locks
该锁就是 Record Locks 和 Gap Locks 的组合，即锁定一个范畴并且锁定该记录自身。InnoDB 应用 Next-key Locks 解决幻读问题。须要留神的是，如果索引有惟一属性，则 InnnoDB 会主动将 Next-key Locks 降级为 Record Locks。

举例：如果一个索引有 1, 3, 5 三个值，则该索引锁定的区间为 (-∞,1], (1,3], (3,5], (5,+ ∞)。

MySQL 主从复制的流程是怎么样的?

1、Master 下面的 binlog dump 线程，该线程负责将 master 的 binlog event 传到 slave。
2、Slave 下面的 IO 线程，该线程负责接管 Master 传过来的 binlog，并写入 relay log。
3、Slave 下面的 SQL 线程，该线程负责读取 relay log 并执行。
4、如果是多线程复制，无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制，SQL 线程只做 coordinator，只负责把 relay log 中的 binlog 读出来而后交给 worker 线程，woker 线程负责具体 binlog event 的执行。

Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

1、InnoDB 反对 事务 ，MyISAM 不反对
对于 InnoDB 每一条 SQL 语言都默认封装成事务，主动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务；

2、InnoDB 反对 外键，而 MyISAM 不反对。对一个蕴含外键的 InnoDB 表转为 MYISAM 会失败；

3、InnoDB 是 汇集索引 ，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。
然而辅助索引须要两次查问，先查问到主键，而后再通过主键查问到数据。因而主键不应该过大，因为主键太大，其余索引也都会很大。
而 MyISAM 是非汇集索引，数据文件是拆散的，索引保留的是数据文件的指针。主键索引和辅助索引是独立的。

4、InnoDB 不保留表的具体行数，执行 select count(*) from table 时须要全表扫描。而 MyISAM 用一个变量保留了整个表的行数，执行上述语句时只须要读出该变量即可，速度很快；

5、Innodb不反对全文索引 ，而 MyISAM 反对全文索引，查问效率上 MyISAM 要高；

mysql 事务隔离级别?

Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都能够看到其余未提交事务的执行后果。本隔离级别很少用于理论利用，因为它的性能也不比其余级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简略定义：一个事务只能看见曾经提交事务所做的扭转。这种隔离级别 也反对所谓的不可反复读（Nonrepeatable Read），因为同一事务的其余实例在该实例解决其间可能会有新的 commit，所以同一 select 可能返回不同后果。

Repeatable Read（可重读）
这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过实践上，这会导致另一个辣手的问题：幻读（Phantom Read）。简略的说，幻读指当用户读取某一范畴的数据行时，另一个事务又在该范畴内插入了新行，当用户再读取该范畴的数据行时，会发现有新的“幻影”行。

InnoDB 和 Falcon 存储引擎通过多版本并发管制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能互相抵触，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时景象和锁竞争。

MVCC 流程?

mvcc 依据 undo log 来实现
RR 级别下，事务中的 第一个 SELECT申请才开始创立 read view；
RC 级别下，事务中 每次 SELECT申请都会从新创立read view；

ReadView 中是以后沉闷的事务 ID 列表，称之为 m_ids，其中最小值为 up_limit_id，最大值为 low_limit_id，事务 ID 是事务开启时 InnoDB 调配的，其大小决定了事务开启的先后顺序，因而咱们能够通过 ID 的大小关系来决定版本记录的可见性，具体判断流程如下：

• 如果被拜访版本的 trx_id 小于 m_ids 中的最小值 up_limit_id，阐明生成该版本的事务在 ReadView 生成前就曾经提交了，所以该版本能够被以后事务拜访。
• 如果被拜访版本的 trx_id 大于 m_ids 列表中的最大值 low_limit_id，阐明生成该版本的事务在生成 ReadView 后才生成，所以该版本不能够被以后事务拜访。须要依据 Undo Log 链找到前一个版本，而后依据该版本的 DB_TRX_ID 从新判断可见性。
• 如果被拜访版本的 trx_id 属性值在 m_ids 列表中最大值和最小值之间（蕴含），那就须要判断一下 trx_id 的值是不是在 m_ids 列表中。如果在，阐明创立 ReadView 时生成该版本所属事务还是沉闷的，因而该版本不能够被拜访，须要查找 Undo Log 链失去上一个版本，而后依据该版本的 DB_TRX_ID 再从头计算一次可见性；如果不在，阐明创立 ReadView 时生成该版本的事务曾经被提交，该版本能够被拜访。

此时通过一系列判断咱们曾经失去了这条记录绝对 ReadView 来说的可见后果。此时，如果这条记录的 delete_flag 为 true，阐明这条记录已被删除，不返回。否则阐明此记录能够平安返回给客户端。

mysql 什么时候会呈现数据页预读?

1、有一个参数是innodb_read_ahead_threshold，他的默认值是56，意思就是如果程序的拜访了一个区里的多个数据页，拜访的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去

2、如果 Buffer Pool 里缓存了一个区里的 13 个间断的数据页，而且这些数据页都是比拟频繁会被拜访的，此时就会 间接触发预读机制，把这个区里的其余的数据页都加载到缓存里去 这个机制是通过参数 innodb_random_read_ahead 来管制的，他默认是 OFF，也就是这个规定是敞开的

3、全表扫描

mysql 有哪些 binlog 录入格局？

• statement，statement 模式下，记录单元为语句。即每一个 sql 造成的影响会记录。因为 sql 的执行是有上下文的，因而在保留的时候须要保留相干的信息，同时还有一些应用了函数之类的语句无奈被记录复制。
• row，row 级别下，记录单元为每一行的改变，根本是能够全部记下来然而因为很多操作，会导致大量行的改变(比方 alter table)，因而这种模式的文件保留的信息太多，日志量太大。
• mixed，一种折中的计划，一般操作应用 statement 记录，当无奈应用 statement 的时候应用 row。