数据库面试题（持续更新中）

目录

1.oracle 与 mysql 的区别

2.三范式

3.事务的特性（ACID）

4.事务隔离级别

 5.脏读、不可重复读、幻读

非重复度和幻像读的区别

6.explain查看sql的执行计划

7.查询SQL语句的执行过程

8.更新sql语句的执行过程

9.锁

加锁机制分为

兼容性上分为

10. 索引失效的原因

11. 数据库中的事务是什么，MySQL中是怎么实现的

12. mysql 的引擎

13.MyISAM索引与InnoDB索引的区别

14.聚簇索引与非聚簇索引的区别

15.创建索引的三种方式

---

1.oracle 与 mysql 的区别

1. 对事务的提交

    MySQL默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮

2. 分页查询

    MySQL是直接在SQL语句中写"select... from ...where...limit  x, y",有limit就可以实现分页;而Oracle则是需要用到伪列ROWNUM和嵌套查询

3. 事务隔离级别

      oracle是read commited的隔离级别，而mysql是repeatable read的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的

    读一致性。每个session提交后其他session才能看到提交的更改。Oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session

    查询时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session构造它查询时的旧的数据块

    MySQL没有类似Oracle的构造多版本数据块的机制，只支持read commited的隔离级别。一个session读取数据时，其他session不能更改数据，但

    可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据

4. 对事务的支持

    MySQL在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务

5. 保存数据的持久性

    MySQL是在数据库更新或者重启，则会丢失数据，Oracle把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，可以随时恢复

6. 并发性

    MySQL以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。

  虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。

  Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以Oracle对并

  发性的支持要好很多。

7. 逻辑备份

    MySQL逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用,Oracle逻辑备份时不锁定数据，且备份的数据是一致

8.  复制

    MySQL:复制服务器配置简单，但主库出问题时，丛库有可能丢失一定的数据。且需要手工切换丛库到主库。

    Oracle:既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库出现问题是，可以自动切换备库到主库，但配置管理较复杂。

9. 性能诊断

    MySQL的诊断调优方法较少，主要有慢查询日志。

    Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等    

10. 权限与安全

    MySQL的用户与主机有关，感觉没有什么意义，另外更容易被仿冒主机及ip有可乘之机。

    Oracle的权限与安全概念比较传统，中规中矩。

11. 分区表和分区索引

    MySQL的分区表还不太成熟稳定。

    Oracle的分区表和分区索引功能很成熟，可以提高用户访问db的体验。

12. 最重要的区别

    MySQL是轻量型数据库，并且免费，没有服务恢复数据。

    Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务。

2.三范式

第一范式：表的每一列不可分割的原子数据项

第二范式：要求的实体的属相完全依赖主关键字，（完全是指不能只依赖主关键字的一部分）

第三范式：任何非主属性不能依赖其他非主属性

3.事务的特性（ACID）

（1）原子性（Atomicity）事务中所涉及的程序对数据库的修改操作要么全部成功，要么全部失败。

（2）一致性（Consistency）执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的

（3）隔离性（Isolation）并发时每个事务是隔离的，相互不影响。

（4）持久性（Durability） 一个事务被提交之后。它对数据库中数据的改变是持久的

4.事务隔离级别

（1）read uncommitted 未提交读

所有事务都可以看到没有提交事务的数据。

（2）read committed 提交读

事务成功提交后才可以被查询到。

（3）repeatable 重复读

同一个事务多个实例读取数据时，可能将未提交的记录查询出来，而出现幻读。mysql默认级别

（4）Serializable可串行化

强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争。

 5.脏读、不可重复读、幻读

（1）Dirty Reads 脏读

一个事务正在对数据进行更新操作，但是更新还未提交，另一个事务这时也来操作这组数据，并且读取了前一个事务还未提交的数据，而前一个事务如果操作失败进行了回滚，后一个事务读取的就是错误数据，这样就造成了脏读。

（2）Non-Repeatable Reads 不可重复读

一个事务多次读取同一数据，在该事务还未结束时，另一个事务也对该数据进行了操作，而且在第一个事务两次次读取之间，第二个事务对数据进行了更新，那么第一个事务前后两次读取到的数据是不同的，这样就造成了不可重复读。

（3）Phantom Reads 幻读

第一个数据正在查询符合某一条件的数据，这时，另一个事务又插入了一条符合条件的数据，第一个事务在第二次查询符合同一条件的数据时，发现多了一条前一次查询时没有的数据，仿佛幻觉一样，这就是幻像读。

非重复度和幻像读的区别

非重复读是指同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。(A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data. )

幻像读是指同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻像读。(A transaction reexecutes a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition. )

6.explain查看sql的执行计划

EXPLAIN  SELECT * from project

sql的执行计划不合理时，可以通过添加索引和强制驱动表的顺序，通过hints方式干预sql的执行计划。另外，mysql优化器的一些参数也可以进行修改，来控制优化器的一些行为

7.查询SQL语句的执行过程

1.先在查询缓存中查询，如果缓存没有命中，将会进行查表操作
2.将sql交给解析器处理，生成一个解析树
3.预处理器会处理解析器，重新生成一个解析器，这个过程中将会改写sql
4.改写后的解析器交给查询优化器，查询优化器生成sql的执行计划
5.执行计划交给执行引擎调用存储引擎的的API接口，查询数据

8.更新sql语句的执行过程

update person set age = 30 where id = 1;

1.分配事务 ID ，开启事务，获取锁，没有获取到锁则等待。

2.执行器先通过存储引擎找到 id = 1 的数据页，如果缓冲池有则直接取出，没有则去主键索引上取出对应的数据页放入缓冲池。

3.在数据页内找到 id = 1 这行记录，取出，将 age 改为 30 然后写入内存

4.生成 redolog undolog 到内存，redolog 状态为 prepare

5.将 redolog undolog 写入文件并调用 fsync

6.server 层生成 binlog 并写入文件调用 fsync

7.事务提交，将 redolog 的状态改为 commited 释放锁

9.锁

锁是为了对共享资源进行并发访问控制，从而保证数据的完整性和一致性

加锁机制分为

悲观锁

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

乐观锁

乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据

判断的两种方式：

1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式（加一个版本号）。

2.时间戳

Java JUC中的atomic包就是乐观锁的一种实现，AtomicInteger 通过CAS（Compare And Set）操作实现线程安全的自增。

兼容性上分为

共享锁 S锁

又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁 X锁

又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改

10. 索引失效的原因

1. 查询条件使用函数，导致不能有效利用索引。

2. 对索引列使用不符合预期的操作，如使用!=或<>。

3. 使用了复合索引，但是没有正确地遵循最左前缀原则。

4. 使用了不等于(!=)或者<>操作符，这些操作符会导致索引失效。

5. 类型不一致，如在索引的列上使用了不匹配的数据类型进行查询。

6. 使用了IS NULL或者IS NOT NULL，通常情况下，这也会导致索引失效。

7. 使用了LIKE关键字，并以通配符开始('%keyword')，这将使得索引失效。

8. 对索引列进行了计算或者函数运算。

9. 查询条件中使用了OR，且OR的各个条件中有条件不使用索引。

10. 查询的数据量过大，导致优化器决定全表扫描比使用索引快

11. 数据库中的事务是什么，MySQL中是怎么实现的

​​​​​事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

回滚日志以及锁技术就是实现事务的基础。
事务的原子性是通过 undo log 来实现的
事务的持久性性是通过 redo log 来实现的
事务的隔离性是通过 (读写锁+MVCC)来实现的
而事务的终极大 boss 一致性是通过原子性，持久性，隔离性来实现的

12. mysql 的引擎

MyISAM
支持表级锁定和全文本搜索。它非常适合读多写少的应用场景，因为在写入数据时需要锁定整个表，因此并发性能相对较差。

MyISAM 不支持事务和行级锁定，因此不适合处理大量写入和并发操作

InnoDB
InnoDB 是 MySQL 的默认存储引擎，支持事务、行级锁定和外键约束。它适合处理高并发的 OLTP（联机事务处理）应用，能够提供更好的并发性和容错性。由于 InnoDB 支持行级锁定，所以多个用户可以同时读取同一张表中的不同行，提高了并发性能。另外，它还支持事务，可以保证数据的一致性和完整性。但 InnoDB 存储引擎的空间利用率较低，会占用更多的存储空间。

Memory
Memory 存储引擎它将数据存储在内存中，适合用于处理临时数据，如缓存、临时表等。由于数据存储在内存中，所以 Memory 存储引擎的读写速度非常快，但是也有一个缺点，就是如果服务器宕机，数据将会丢失

Archive（档案馆）
Archive 存储引擎适合用于存储大量历史数据，它采用压缩算法存储数据，能够大幅减少存储空间。Archive 存储引擎不支持索引、更新和删除操作，只能进行插入和查询

13.MyISAM索引与InnoDB索引的区别

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

14.聚簇索引与非聚簇索引的区别

聚簇：数据行被按照一定顺序紧密地排列在一起

（1）聚簇索引（Clustered Index）

表中的数据行实际上按照聚簇索引的键值顺序存储在磁盘上。

索引树的叶子节点上存储着主键与数据行，在InnoDB存储引擎中，每个表只能有一个聚簇索引，通常默认情况下，它由主键组成。如果没有显式定义主键，则会选择唯一非空索引 或者创建一个隐式主键来作为 聚簇索引

优点：数据访问更快，全盘扫描更快，因为索引和数据保存在一起

缺点：主键更新的代价大，可能会涉及大量的数据的磁盘中的从新排列

           删除记录可能导致数据页留有空洞，需要维护碎片

（2）非聚簇索引（NoClustered Index）

又叫二级索引，索引树的叶子节点上存储着数据行的地址（mysql 的innoDB 存储的是主键），每个表中可以有多个非聚簇索引

优点：多个非聚簇索引可以存在于同一表中

           更新非聚簇索引通常比更新聚簇索引代价低

           非聚簇索引可以包含更多的列，而不必受限于聚簇索引的要求

缺点：访问数据时需要两次查找：先找到索引条目，然后通过指针定位数据行

15.创建索引的三种方式

1.建表时

CREATE TABLE t_index (
	id INT auto_increment PRIMARY KEY,
	first_n VARCHAR (16),
	last_n VARCHAR (16),
	id_card VARCHAR (18),
	UNIQUE KEY (id_card)
);

2. ALTER TABLE...ADD INDEX...

ALTER TABLE table_name ADD INDEX index_name (column_list);

3. create index...on...

CREATE INDEX index_name ON table_name (column_list);

16.InnoDB存储引擎的锁的算法有三种

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

17.什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理

18.mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接

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

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