2020数据库最新面试题常考汇总

2020-10-26 10:44:52 浏览数 (1)

[toc]

MySQL索引

1.数据库中有哪些索引类型?

索引的几种类型：唯一索引、主键索引、聚集索引、普通索引、组合索引、全文索引

唯一索引：在创建唯一索引时要不能给具有相同的索引值。在MySQL中创建索引：

代码语言：javascript复制
CREATE UNIQUE INDEX indexName ON mytable(username(length));

主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。

代码语言：javascript复制
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引。

普通索引：它的结构主要以B 树和哈希索引为主，主要是对数据表中的数据进行精确查找。

代码语言：javascript复制
CREATE INDEX indexName ON mytable(username(length));

ALTER TABLE mytable ADD INDEX username(length);

组合索引：一个表中含有多个单列索引不代表是组合索引,通俗一点讲组合索引是:包含多个字段但是只有索引名称

代码语言：javascript复制
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

全文索引：它的作用是搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询。

代码语言：javascript复制
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

使用索引的优点：

  • 提高数据的搜索速度
  • 加快表与表之间的连接速度
  • 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

使用索引的缺点:

  • 在我们建立数据库的时候，需要花费的时间去建立和维护索引，而且随着数据量的增加，需要维护它的时间也会增加。
  • 在创建索引的时候会占用存储空间。
  • 在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。

2.数据库索引底层实现

不懂数据库索引的底层原理？

MySQL中，索引的底层实现构主要是：哈希索引和B 树索引。

哈希索引的底层数据结构就是哈希表，查询性能最快；

B树索引：B树实现

B 树索引：B 树实现

3. 为什么选用B 树？

红黑树等数据结构也可以用来实现索引，但是文件系统以及数据库系统普遍采用B-Tree/B Tree作为索引结构。

原因：索引本身也很大，因此索引往往是以索引文件的形式存储在磁盘上。所以，索引查找的过程就会产生磁盘的I/O操作，相比于内存存取，I/O存取消耗要高几个数量级，所以索引的优劣最重要的指标就是在查找过程中的磁盘I/O存取次数。

4. 哈希索引与B 树如何选用？

Hash索引是无序的，所以只适用于等值查询，而不能用于范围查询。大多数场景下，都会有范围查询、排序、分组等查询特征，用B 树索引就可以了。

5. 聚集索引和非聚集索引的区别

聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

区别：

  1. 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
  2. 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
  3. 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

6. 什么情况下设置了索引但是会失效？

  1. 条件中有or
  2. 对于多列索引，不是使用的第一部分，则不会使用索引
  3. like查询时以%开头
  4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

SQL优化

1. 怎么对一条查询语句进行调优

逻辑层优化：如分步查询，然后在代码层进行拼接；用临时表；改变sql语句的写法等等

实现层的优化：然后我们看看每条sql语句执行的执行计划，用explain命令查看。可以看到这些表的执行顺序，可能使用到的索引，实际使用到索引，索引的长度，可能要扫描的行数等等信息，然后对这些信息分析，进行索引的优化。

2. 优化数据库的方案

  • 参数优化
  • MySQL设计优化

MySQL锁

1. MySQL有哪几种锁，分别怎么实现？

  1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MySQL事务

事务时逻辑上的一组操作，要么都执行，要么都不执行。

1. 事务的四大特性

记忆：ACID

  1. 原子性（Atomicity）：事务是最小的执行单位，不允许分割。原子性就是为了保证动作要么全部完成，要么完全不起作用；
  2. 一致性(Consistency)：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性(Durability)：一个事务被提交之后。它对数据库中数据的改变是持久的，即数据库发生故障也不应该对其有任何影响。

2 有哪些数据库引擎，各自的区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。

两者的对比：

  1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
  2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  3. 是否支持外键： MyISAM不支持，而InnoDB支持。
  4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL-InnoDB-MVCC多版本并发控制
  5. ......

3. MySQL四种隔离引擎，底层实现

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

4. 数据库如何解决幻读

幻读：一次事务里，多次查询后，结果的个数不一致的情况叫做幻读。

如何解决：MVCC 和next-key（当前读）

next-key原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取数据是一致的。

next-key包含：记录锁、间隙锁。

记录锁：加在索引上的锁；间隙锁：加在索引之间的锁。

其他

1. 数据库的三大范式

1、第一范式：一个单元格只存储一个值。

当关系模式R的所有属性都不能在分解zhi为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

2、第二范式：满足所有的属性字段唯一依赖主键。

如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

3、第三范式：消除传递依赖。

设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。

全文检索 数据库 sql 云数据库SQLServer mvc

0 人点赞