面试复盘 |农研java软开一面

隔离级别？

未提交读：事务中的修改即使没有提交，对其他事务也是可见的。这个级别存在脏读、不可重复读和幻读，很少使用。

Ø 脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的

Ø 不可重复读：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了

Ø 幻读：对于两个事务T1,T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行.

提交读：多数数据库的默认隔离级别，事务只能看见已提交事务的修改。存在不可重复读和幻读。

可重复读（MySQL默认的隔离级别）：解决了不可重复读，保证同一个事务中多次读取同样的记录结果一致，InnoDB 通过 MVCC 解决。存在幻读。

可串行化：最高隔离级别，通过强制事务串行执行避免脏读、不可重复读和幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少使用，只有非常需要确保数据一致性时考虑。

事务以及实现？

事务是一组原子性的 SQL 语句，当有任何一条语句因崩溃或其他原因无法执行时，所有语句都不会执行。事务内的语句要么全部执行成功，要么全部执行失败。

原子性：一个事务在逻辑上是必须不可分割的最小单元，整个事务中的所有操作要么全部成功，要么全部失败。

一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性：针对并发事务而言，要隔离并发运行的多个事务之间的影响，数据库提供了多种隔离级别。

持久性：一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

l 原子性和持久性是通过 redo log （重做日志）来保证的；

l 一致性是通过 undo log（回滚日志）来保证的；

l 隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；

什么是锁？事务和锁有什么区别？

事务隔离级别是并发控制的整体解决方案，其实际上是综合利用各种类型的锁和行版本控制，来解决并发问题。

锁是数据库并发控制的内部机制，是基础。

对用户来说，只有当事务隔离级别无法解决一些并发问题和需求时，才有必要在语句中手动设置锁。不适当的设置锁，可能会导致严重的阻塞和死锁。建议，只有在完全了解锁机制的情况下，才可以在语句中手动设置锁，否则应该使用事务隔离级别。

存储引擎？

InnoDB

① MySQL5.1 开始的默认引擎，最大的优点是支持事务和外键，InnoDB 的性能和自动崩溃恢复特性使它在非事务型需求中也很流行，一般应该优先考虑使用 InnoDB。② 底层存储结构是 B+ 树，每个节点都对应 InnoDB 的一个页。非叶子节点只有 key 值，叶子节点包含完整的数据。③ 支持行锁，采用 MVCC 支持高并发，实现了四个标准的隔离级别，默认级别是可重复读，通过间隙锁防止幻读。④ 基于聚簇索引，对主键查询有很高的性能。⑤ 内部做了很多优化，例如加速读操作的自适应哈希索引、加速插入操作的缓冲区等。

MyISAM

① MySQL5.1及之前的默认引擎，提供的特性包括全文索引、空间索引等，不支持事务、行锁和外键。② 最大的缺陷是崩溃后无法恢复，在插入和更新数据时需要锁定整张表，效率低。③ 对于只读的数据或者表比较小、可以忍受修复操作的情况可以使用 MyISAM。

Memory

① 如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，可以使用 Memory 表。② 数据保存在内存，不需要磁盘 IO，表的结构在重启后会保留，数据会丢失。③ 支持哈希索引，查找速度快。④ 使用表锁，并发性能低。

聚簇索引是什么？

聚簇索引不是一种索引类型，而是一种数据存储方式。一旦创建了聚簇索引，表中的所有列都根据构造聚簇索引的关键列来存储，如按照主键Id递增方式依次物理顺序存储，该聚簇索引可以帮助表优化增删改查操作。通常MySQL将使用主键作为聚簇索引，因为聚簇索引是按该列的排序存储的，因此一个表只能有一个聚簇索引。

优点：按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：如果数据全部在内存中会失去优势；更新代价高，强制每个被更新的行移动到新位置；插入行或主键更新时，可能导致页分裂，占用更多磁盘空间。

聚簇索引和非聚簇索引区别？

（1）一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

（2）聚集索引，索引中键的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

（3）聚簇索引：索引的叶节点就是数据节点，非叶子结点是索引，因此索引项与数据项在一张表。而非聚簇索引的叶节点仍然是索引节点(主键)，只不

过有一个指针指向对应的数据块，索引项与数据项在两张表。

（4）聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

普通索引和唯一索引在查询效率上有什么不同？

唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索。而普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页全部加载到内存中进行读取。InnoDB 存储引擎的页大小为 16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次判断下一条记录的操作，对于 CPU 来说，这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。

查询优化？

① 避免全表扫描：考虑在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，尽量用 BETWEEN 取代。

② 优化 COUNT：某些业务不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。

③ 避免子查询：在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。

④ 禁止排序：当查询使用 GROUP BY 时，结果集默认会按照分组字段排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。

⑤ 优化分页：从上一次取数据的位置开始扫描，避免使用 OFFSET。

⑥ 优化 UNION：MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。

⑦ 使用用户自定义变量：用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。

delete、drop、truncate的区别？

delete 可以删除部分数据也可以删除全部数据，不删除表的结构；truncate删除全部数据，不删除表的结构，drop 会删除表的结构。

delete 是 DML 操作，可以进行回滚；drop 和 truncate 是 DDL，不能进行回滚。

速度来说，一般 drop > truncate > delete。

count(*)、count(1)、count(字段)的区别？

count(字段)：统计该字段非空值的个数

count(*)：统计结果集的行数

count(1)：统计结果集的行数(即在表最左边加了一列常量值，统计常量值的个数，常用1)

效率上：MyISAM存储引擎，count(*)最高；

InnoDB存储引擎(目前使用)，count(*)和count(1)效率>count(字段)

视图的优点？

视图是一个虚拟表，是存储在数据库中的查询 SQL 语句，视图只是一个逻辑，具体结果在引用视图时动态生成。

优点：① 具有安全性，可以进行权限控制，创建只读视图，公开给特定用户。② 可以简化复杂的查询，保存其逻辑。

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

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。存储过程具有以下特点：

l 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；

l 当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；

l 减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小；

l 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

有哪些索引？

B-Tree：大多数引擎都支持这种索引，但底层使用不同结构，例如 NDB 使用 T-Tree，InnoDB 使用 B+ Tree。所有的值都是顺序存储的，并且每个叶子页到根的距离相同。B-Tree 索引能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取数据，而是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点的指针指向的是被索引的数据，而不是其他节点页。B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

Hash：哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。

空间索引：MyISAM 的一个特殊索引类型，用作地理数据存储。

全文索引：MyISAM 的一个特殊的 B-Tree 索引，一共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的文档指针。用于通过关键字匹配进行查询。

Hash 索引和 B+ 树索引区别是什么？

B+ 树可以进行范围查询，Hash 索引不能。

B+ 树支持联合索引的最左侧原则，Hash 索引不支持。

B+ 树支持** order by 排序**，Hash 索引不支持。

B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

Hash 索引在等值查询上比 B+ 树效率更高。

什么是最左前缀原则？什么是最左匹配原则？

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

当我们创建一个组合索引的时候，如** (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和(a1,a2,a3)三个索引**，这就是最左匹配原则。

索引不适合哪些场景?

数据量少的不适合加索引，更新比较频繁的也不适合加索引，区分度低的字段不适合加索引（如性别）

MySQL 有哪些聚合函数？

① max 求最大值。② min 求最小值。③ count 统计数量。④ avg 求平均值。⑤ sum 求和。

索引的优点与缺点?

优点：通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；可以大大加快数据的检索速度，这也是创建索引的最主要的原因；帮助服务器避免排序和临时表；将随机IO变为顺序IO；可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

缺点：创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）；会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

MYSQL的主从复制

步骤一：主库的更新事件(update、insert、delete)被写到binlog

步骤二：从库发起连接，连接到主库。

步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。

步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log