数据库面试总结

最新推荐文章于 2024-07-07 13:00:43 发布

ellieokokok

最新推荐文章于 2024-07-07 13:00:43 发布

阅读量126

点赞数

分类专栏：数据库文章标签：数据库面试

原文链接：https://blog.csdn.net/justloveyou_/article/details/78308460

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

范式

范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法
高级别范式的依赖于低级别的范式，1NF 是最低级别的范式

第一范式就是属性不可分割，每个字段都应该是不可再拆分的
第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。
第三范式就是无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。

实践中如何优化MySQL

实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面，如下图所示：
在这里插入图片描述

SQL语句的优化

SQL语句的优化主要包括三个问题，即如何发现有问题的SQL、如何分析SQL的执行计划以及如何优化SQL，下面将逐一解释。

怎么发现有问题的SQL?（通过MySQL慢查询日志对有效率问题的SQL进行监控）

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。
　　通过MySQL的慢查询日志，我们可以查询出执行的次数多占用的时间长的SQL、可以通过pt_query_disgest(一种mysql慢日志分析工具)分析Rows examine(MySQL执行器需要检查的行数)项去找出IO大的SQL以及发现未命中索引的SQL，对于这些SQL，都是我们优化的对象。

在这里插入图片描述

通过explain查询和分析SQL的执行计划

使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。

SQL语句的优化

优化insert语句：一次插入多值；
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
优化嵌套查询：子查询可以被更有效率的连接(Join)替代；很多时候用 exists 代替 in 是一个好的选择

索引优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形：

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配； OR语句前后没有同时使用索引；

数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；

对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则

索引生效的情形包括col1或col1，col2或col1，col2，col3)。

数据库表结构的优化

数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。
(1). 选择合适数据类型

使用较小的数据类型解决问题；
使用简单的数据类型(mysql处理int要比varchar容易)；
尽可能的使用not null 定义字段；
尽量避免使用text类型，非用不可时最好考虑分表；

(2). 表的范式的优化

一般情况下，表的设计应该遵循三大范式。

(3). 表的垂直拆分

把含有多个列的表拆分成多个表，解决表宽度问题，具体包括以下几种拆分手段：

把不常用的字段单独放在同一个表中；
把大字段独立放入一个表中；
把经常使用的字段放在一起；

这样做的好处是非常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。

(4). 表的水平拆分

表的水平拆分用于解决数据表中数据过大的问题，水平拆分每一个表的结构都是完全一致的。一般地，将数据平分到N张表中的常用方法包括以下两种：

对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值；
针对不同的hashID将数据存入不同的表中；

表的水平拆分会带来一些问题和挑战，包括跨分区表的数据查询、统计及后台报表的操作等问题，但也带来了一些切实的好处：

表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度；
表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
需要把数据存放到多个数据库中，提高系统的总体可用性(分库，鸡蛋不能放在同一个篮子里)。

系统配置的优化

操作系统配置的优化：增加TCP支持的队列数

mysql配置文件优化：Innodb缓存池设置(innodb_buffer_pool_size，推荐总内存的75%)和缓存池的个数（innodb_buffer_pool_instances）

硬件的优化

CPU：核心数多并且主频高的
内存：增大内存
磁盘配置和选择：磁盘性能

NOSQL数据库 —— Redis

Redis是一款基于内存的且支持持久化、高性能的Key-Value NoSQL 数据库，其支持丰富数据类型(string，list，set，sorted set，hash)，常被用作缓存的解决方案。

Redis具有以下显著特点：

速度快，因为数据存在内存中，类似于HashMap，HashMap的优势就是查找和操作的时间复杂度都是O(1)；
支持丰富数据类型，支持string，list，set，sorted set，hash；
支持事务，操作都是原子性，所谓的原子性就是对数据的更改要么全部执行，要么全部不执行；
丰富的特性：可用于缓存，消息，按key设置过期时间，过期后将会自动删除。

Redis作查询缓存需要注意考虑以下几个问题，包括防止脏读、序列化查询结果、为查询结果生成一个标识和怎么使用四个问题，具体如下：
(1). 防止脏读
　　对一张表的查询结果放在一个哈希结构里，当对这个表进行修改、删除或者更新时，删除该哈希结构。对这张表所有的操作方法，使用注解进行标记，例如：

Hash   KEY(表名)  k(查询结果标识) : v()   k:v   k:v   k:v   k:v

// 表示该方法需要执行 (缓存是否命中 ? 返回缓存并阻止方法调用 : 执行方法并缓存结果)的缓存逻辑
@RedisCache(type = JobPostModel.class)
JobPostModel selectByPrimaryKey(Integer id);

// 表示该方法需要执行清除缓存逻辑
@RedisEvict(type = JobPostModel.class)
int deleteByPrimaryKey(Integer id);

我们缓存了查询结果，那么一旦数据库中的数据发生变化，缓存的结果就不可用了。为了实现这一保证，可以在执行相关表的更新查询(update,delete,insert)查询前，让相关的缓存过期。这样下一次查询时程序就会重新从数据库中读取新数据缓存到redis中。
那么问题来了，在执行一条insert前我怎么知道应该让哪些缓存过期呢？对于Redis，我们可以使用Hash结构，让一张表对应一个Hash，所有在这张表上的查询都保存到该Hash下。这样当表数据发生变动时，直接让Set过期即可。我们可以自定义一个注解，在数据库查询方法上通过注解的属性注明这个操作与哪些表相关，这样在执行过期操作时，就能直接从注解中得知应该让哪些Set过期了。

(2). 序列化查询结果
　　利用JDK自带的ObjectInputStream/ObjectOutputStream将查询结果序列化成字节序列，即需要考虑Redis的实际存储问题。
　　
(3). 为查询结果生成一个标识

被调用的方法所在的类名，被调用的方法的方法名，该方法的参数三者共同标识一条查询结果。也就是说，如果两次查询调用的类名、方法名和参数值相同，我们就可以确定这两次查询结果一定是相同的（在数据没有变动的前提下）。因此，我们可以将这三个元素组合成一个字符串做为key，就解决了标识问题。
(4). 以 AOP 方式使用Redis

方法被调用之前，根据类名、方法名和参数值生成Key；
通过Key向Redis发起查询；
如果缓存命中，则将缓存结果反序列化作为方法调用的返回值，并将其直接返回；
如果缓存未命中，则继续向数据库中查询，并将查询结果序列化存入redis中，同时将查询结果返回。

例如，插入删除缓存逻辑如下：

/**
     * 在方法调用前清除缓存，然后调用业务方法
     * @param jp
     * @return
     * @throws Throwable
     */
    @Around("execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.insert*(..))" +
            "|| execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.update*(..))" +
            "|| execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.delete*(..))" +
            "|| execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.increase*(..))" +
            "|| execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.decrease*(..))" +
            "|| execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.complaint(..))" +
            "|| execution(* com.fh.taolijie.dao.mapper.JobPostModelMapper.set*(..))")
    public Object evictCache(ProceedingJoinPoint jp) throws Throwable {}

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程具有以下特点：

存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；
当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；
减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小；
通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全

drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；
Truncate删除表中的所有数据，Truncate不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，drop命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

视图？游标？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增，删，改，查等操作。特别地，对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

在操作mysql的时候，我们知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是与 SQL语句相匹配的行（零行或多行）。使用简单的 SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

触发器？

触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。

MySQL中的悲观锁与乐观锁的实现

悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中一个非常基础的概念。
　　
(1). 悲观锁

悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。
　　通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select … for update时会获取被select中的数据行的行锁，因此其他并发执行的select … for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。`select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

这里需要特别注意的是，不同的数据库对select… for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项。
另外，mysql还有个问题是: select… for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此，如果在mysql中用悲观锁务必要确定使用了索引，而不是全表扫描。

(2). 乐观锁

乐观锁的特点先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。

一般的做法是在需要锁的数据上增加一个版本号或者时间戳，然后按照如下方式实现：

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根据获取的数据进行业务操作，得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：
在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

(3). 悲观锁与乐观锁的应用场景

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

JDBC 对事务的支持

对于JDBC而言，每条单独的语句都是一个事务，即每个语句后都隐含一个commit。
　　实际上，Connection 提供了一个auto-commit的属性来指定事务何时结束。当auto-commit为true时，当每个独立SQL操作的执行完毕，事务立即自动提交，也就是说，每个SQL操作都是一个事务；当auto-commit为false时，每个事务都必须显式调用commit方法进行提交，或者显式调用rollback方法进行回滚。auto-commit默认为true。
为了能够将多条SQL当成一个事务执行，必须首先通过Connection关闭auto-commit模式，然后通过Connection的setTransactionIsolation()方法设置事务的隔离级别，最后分别通过Connection的commit()方法和rollback()方法来提交事务和回滚事务。

try {  
    conn.setAutoCommit(false);  //将自动提交设置为false        
    ps.executeUpdate("修改SQL"); //执行修改操作  
    ps.executeQuery("查询SQL");  //执行查询操作                 
    conn.commit();      //当两个操作成功后手动提交     
} catch (Exception e) {  
    conn.rollback();    //一旦其中一个操作出错都将回滚，使两个操作都不成功  
    e.printStackTrace();  
}

MySQL存储引擎中的MyISAM和InnoDB区别详解

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然MyISAM性能极佳，但却有一个显著的缺点：不支持事务处理。不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

InnoDB是MySQL的数据库引擎之一，其由Innobase oy公司所开发，2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。具体地，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：

存储结构：每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。
AUTO_INCREMENT：在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
全文索引：MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
CURD操作：在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
外键：MyISAM不支持外键，而InnoDB支持外键。

　　通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁、外键等等。尤其在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，必须需要注意的是，任何一种表都不是万能的，合适的才是最好的，才能最大的发挥MySQL的性能优势。如果是不复杂的、非关键的Web应用，还是可以继续考虑MyISAM的，这个具体情况具体考虑。

参考

ellieokokok

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库面试总结

文章目录MySQL 中的存储引擎B+树结构是一种多路查找树的结构MySQL 中的存储引擎1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。2、通过show engines；来查看mysql支持的存储引擎。3、在mysql中用的最多的存储引擎有：innodb， myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务My...
复制链接

扫一扫