数据库基础与关系型数据库

  1. 什么存储过程?用什么调用?
    • 存储过程就是一个预编译的SQL语句,它允许模块化的设计,就是说只需要一次创建,在同一程序中就能多次调用。执行效率比普通的SQL语句高
    • 调用方式:
      • 可以使用命令对象进行调用
      • 可以使用外部程序进行调用(如:Java程序)
  2. 存储过程的优缺点
    • 优点:
      • 在一个存储过程中能够执行一系列的SQL语句
      • 执行效率比普通的SQL语句高
      • 存储过程能够多次使用,减少数据库开发人员的工作量
      • 在一个存储过程中能够引用其他的存储过程,从而可以简化复杂的操作
      • 存储过程存放在数据库中,直接使用存储过程名进行使用,减少网络通讯
    • 缺点:
      • 移植性差
  3. 索引的作用?它的优缺点?
    • 索引就是一个特殊的查询表,数据库可以利用索引加速对数据的检索。索引可以唯一,索引创建通过是一张表中的一列或多列
    • 优点:
      • 索引类似字典目录,我们不需要翻阅一本字典就能够知道数据存放的位置。
    • 缺点:
      • 降低了数据的录入速度,并且增大了数据库的尺寸大小
  4. 怎样的字段适合建索引?
    • 非空、唯一和经常被查询的字段
  5. 索引类型有哪些?
    Single Column 单行索引
    Concatenated 多行索引
    Unique 唯一索引
    NonUnique 非唯一索引
    Function-based 函数索引
    Domain 域索引

    Partitoned

    分区索引
    NonPartitoned 非分区索引
    B-tree:
      Normal
      Rever Key
      Bitmap
    正常型B树
    反转型B树
    位图索引
  6. 触发器的作用?
    • 触发器就是一种特殊的存储过程,是由事件(包括Insert、update、delete)触发执行的。触发器可以强制约束,来维护数据的完整性和一致性,可以追踪数据库中的操作不允许未经过允许的更新和变化
  7. 什么是事务?什么是锁?
    • 事务:
      • 就是绑定在一起的SQL语句组,如果事务中一个操作失败,则全部操作全会失败,并且回滚到操作之前的状态。如果我们需要将一个SQL语句组作为事务处理,则必须通过ACID(原子性、一致性、隔离性、持久性)测试
        • 原子性:事务中的操作不能分割
        • 一致性:事务执行前后数据的完整性必须一致
        • 隔离性:事务执行时不允许被其他事务干预
        • 持久性:事务执行结束将数据持久化到数据库中
    • 锁:
      • 在所有的DBMS(数据库管理系统)中,锁是实现事务的关键,锁能够保证事务的完整性和并发性。事务的隔离级别是通过锁的不同粒度实现的
  8. 数据库事务的四大特性?未设置隔离级别会出现哪些读的问题?通过设置事务隔离级别能够解决哪些读的问题?
    • ACID:
      • 原子性:事务中的操作不能分割
      • 一致性:事务执行前后数据完整性必须一致
      • 隔离性:事务执行过程中不允许被其他事务干预
      • 持久性:事务执行结束将数据持久化到数据库中
    • 未设置隔离级别可能产生的问题:
      • 脏读:当一个事务未提交其他事务就能访问此事务中的数据
      • 幻读:多次查询同一组数据,获得的结果不同(产生的操作:其他事务进行了Insert和delete操作,即获取的数据多了或者少了)
      • 不可重复读:多次查询同一数据,获得的结果不同(产生的操作:其他事务进行了update操作,即获取的数据被修改了)
    • 隔离级别:
      • 读未提交(Read_Uncommited):最低的隔离级别,当一个事务未提交其他事务就能访问此事务中的数据,会出现脏读、幻读和不可重复读的问题
      • 读已提交(Read_Commited):当一个事务已提交其他事务才能进行访问数据,会出现幻读和不可重复读的问题,Oracle和SQLServer的默认隔离级别
      • 可重复读(Read_Repeatable):事务能够保证每次使用的数据都是一致的,会出现幻读,MySQL的默认隔离级别
      • 序列化(Serializable):最高的隔离级别,可以解决脏读、幻读和不可重复读的问题
  9. 什么是视图?什么是游标?
    • 视图:
      • 视图是一个虚拟表,与基本表拥有同样的功能。我们可以对视图进行CRUD操作,这些操作也会对基本表产生影响。视图通常是一张表或多张表的行或列的子集,我们可以将视图理解为一个SQL语句,能显示一张表或多张表中的数据
    • 游标:
      • 临时存储从数据库中提取的数据块
  10. 视图的优缺点?
    • 优点:
      • 使用简单的SQL就能获得复杂SQL得到的结果
      • 更方便的访问数据库,可以在数据库中选择一部分数据查询
      • 维护了数据的独立性,可以在多张表中检索数据
      • 同一数据可以有不同的视图
    • 缺点:
      • 性能上,在查询视图,会将视图查询转化为基础表的查询。如果视图是由复杂的SQL进行定义的话,那么不能够修改视图的数据
  11. 表连接的方式有哪几种?有什么区别?
    • 内连接(inner join):只显示连接表中匹配的数据
    • 外连接:
      • 左外连接(left join):左边的表为驱动表,驱动表中所有数据都会显示,匹配表中只显示匹配的数据
      • 右外连接(right join):右边的表为驱动表,驱动表中所有数据都会显示,匹配表中只显示匹配的数据
    • 全连接(union/union all):连接表中所有数据都会显示
    • 交叉连接(cross join):笛卡尔效应,显示的结果是连接表数的乘积
  12. 主键和外键的区别?
    • 主键:唯一且不可为空
    • 外键:可以为空,与另外一张的主键关联
  13. 在数据库中查询语句速度很慢,如果优化?
    • 建立索引
    • 优化SQL,避免全表查询
    • 减少表的关联
    • 使用PreparedStatement来查询,不要使用Statement。前者能够预编译SQL语句,能够避免SQL注入的问题;后者存在SQL注入的问题
  14. 数据库的三大范式?
    • 第一范式:列不可再分
    • 第二范式:行可以唯一区分,即主键约束
    • 第三范式:表中非主属性不能与其他表中非主属性关联,即外键约束
  15. union和union all的区别?
    • union:使用union关键词进行表连接,返回的结果会将重复的数据筛选掉
    • union all:使用union all关键词进行表连接,直接返回结果不会对结果做处理。效率比union高
  16. group by和order by的区别?
    • order by:asc 升序;desc 降序
    • group by:
      • 使用group by字句的查询语句必须使用聚合函数
      • having只能用于group by字句中,并且having字句可以直接跟函数表达式
  17. 表和视图的关系?
    • 表:是关系型数据中存储数据使用的
    • 视图:是一个查询的SQL语句,用于显示一张表或多张表或其他视图中的数据
  18. truncate和delete的区别?
    • Truncate的功能与不带where字句的delete的功能一致,都是删除数据库中所有的行,但是效率要比delete高,比delete使用的系统和事务日志资源少
    • delete操作每次删除一行,并且在事务日志中记录删除的每行
    • Truncate是通过释放存储表数据的数据页进行删除数据,并且在事务日志中只记录页的释放
    • delete、Truncate、drop的区别:
      • delete:删除内容,不删除定义,不释放空间
      • Truncate:删除内容,释放空间,不删除定义
      • drop:删除内容和定义,释放空间
  19. 如何实现数据库结果去重?
    • 使用distinct关键字
  20. 数据库常见的锁有哪些?
    • 行锁:开销大、加锁慢、会出现死锁,但锁粒度最小,出现锁冲突的概率最小,并发性最高
    • 表锁:开销小、加锁快、不会出现死锁(因为MyISAM会一次性获取所有MySQL需要的锁),但锁粒度大,出现锁冲突的概率高,并发性差
    • 页锁:开销和加锁介于行锁和表锁之间,会出现死锁,锁粒度介于行锁和表锁之间,并发性一般
    • 悲观锁:顾名思义,数据库认为每个线程都会对同一数据进行修改,所以对每次访问都进行加锁,每次只允许获得锁的线程进行访问,其他线程只能等待此线程释放锁才有机会访问,适用于改多查少的场景
    • 乐观锁:顾名思义,数据库认为 每个线程都不会对同一数据进行修改,所以每次访问都不会进行加锁,但最终结果会进行判断是否被修改,我们可以使用版本号的机制进行控制,适用于查多改少的场景
  21. 常用的数据库引擎有哪些?
    • InnoDB引擎:
      • 提供了对数据库ACID事务的支持,支持行级锁和外键约束。使用行级锁的目的就是为了处理大数据容量的数据库系统。此引擎不支持全文搜索,并且启动比较慢,不会保存表的行数,当我们在进行“Select count(*) from  table”的操作时,需要扫描全表。由于锁的粒度的,在进行写的操作不会加锁。所以在需要事物的支持,并且写操作多于读操作的情况下,我们可以使用InnoDB引擎,而且在高并发的场景下效率也有提升
    • MyISAM引擎:
      • MySQL的默认引擎,不支持对数据库ACID事务的支持,不支持行级锁和外键约束,但提供了表级锁。因为使用了表级锁,所以在进行写操作时会进行加锁,这样也就会导致效率比较低。但不同于InnoDB的是,MyISAM能够保存表的行数,当我们在进行“Select count(*) from  table”的操作时,我们可以直接使用保存的数据。所以在不需要事务的支持,并且读操作多于写操作的情况下,我们可以使用MyISAM
  22. InnoDB索引和MyISAM索引的区别?
    • 数据结构都是B+树:
      • MyISAM:存储的内容是实际数据的地址。也就是说索引和实际数据是分开的,只是索引指向实际数据。这种索引被称为非聚集索引
      • InnoDB:存储的内容就是实际数据。这种索引被称为聚集索引
    • 主索引的区别:InnoDB的实际数据文件就是索引文件;MyISAM的实际数据和索引是分开的
    • 辅助索引的区别:InnoDB的辅助索引data域存储对应记录主键的内容而不是地址;MyISAM辅助索引和主索引没什么区别
  23. 什么是内存泄漏?
    • 内存泄漏一般说的是堆内存的泄漏,堆内存就是程序在堆中为其分配的内存空间,大小任意,使用结束显示的释放内存
    • 当我们使用关键字new等,创建对象时,堆就会为其分配空间,使用结束调用free或delete进行释放内存,否则此内存就无法使用,我们称内存泄漏
  24. 为什么要使用数据库连接池?
    • 在一个小型的应用程序中,我们可以在使用数据库时在创建数据库的连接,这样也不会造成资源的浪费影响效率。但是,在一个大型的应用程序中,避免不了会频繁的使用数据库,就会频繁的创建和关闭数据库的连接,会极大减低系统的性能,这样就会导致连接的使用造成系统的瓶颈
    • 数据库连接池目的:
      • 为了达到连接的复用。通过建立一个数据库连接池和数据库的管理策略,使得一个数据库连接能够高效和安全的复用,避免连接的创建和关闭带来的开销
    • 数据库连接池的原理:
      • 数据库连接池在内部对象池中维护着一定数量的连接对象,并对外部暴露获取和返回的方法。当我们要使用连接时,调用获取的方法getConnection()方法进行获取,使用结束调用返回方法releaseConnection()进行返回,此时连接并没有关闭,只是返回给数据库连接池进行管理,等待下一次的使用。
  25. 数据库语句有多少类型?
    • DDL(Data Definition Language):数据库定义语言
      • Create语句:创建数据库和数据库的一些对象
      • Drop语句:删除数据库中的表、权限、索引等
      • Truncate语句:删除表中所有数据,通过释放存储数据的数据页进行删除数据
      • Alert语句:修改数据库的数据以及定义
    • DQL(Data Query Language):数据查询语言
      • select语句:用于查询数据
    • DML(Data Manipulation Language):数据库操作语言
      • Insert语句:在数据库表中插入一条数据
      • Update语句:在数据库中修改一条数据
      • Delete语句:在数据库中删除一条数据
    • DCL(Data Control Language):数据库控制语言
      • Grant语句:允许对象创建者对某用户、某组或所有用户赋予某些权限
      • Revoke语句:允许删除是某用户、某组或所有用户的访问权限
  26. char、Varchar2和Varchar的区别?
    • char的存储长度是固定的,Varchar和Varchar2都是可变的  
      • 当在进行存储"a,b,c"时,使用char(20)存储时 20个字节都会被使用,其中“a,b,c”只占用3个字节,其他17个字节都为空;在使用Varchar(20)/Varchar2(20)时,只会占用3个字节,也就是说会根据数据的长度进行存储。从效率上来说,char要高于Varchar和Varchar2
    • Varchar和Varchar2的区别:
      • 现在这两个类型没什么区别,只是Varchar允许存储空字符串,而Oracle不允许这么做,所以Oracle自己开发了一个数据类型Varchar2,将Varchar列的空字符串设置为null,如果我们想要向后兼容,那么Oracle推荐使用Varchar2
  27. 从数据库中随机取50条数据?
    • Oracle:
      • select * from (select * from table order by id) where rownum<=50
    • Mysql:------>Round():对指定小数位的数据进行四舍五入    rand():获得0<=x<1的随机数
      • select * from table order by rand() limit 50   数据库中数据量大时效率极差
      • select * from table t1 join (select Round( Rand*((select MAX(id) from table)-(select MIN(id) from table)) +(select MIN(id) from table) ) ) t2  where t1.id=t2.id  order by t1.id  limit 50     数据库中数据量大时效率也不错
    • SQLServer:
      • select top 50 * from table order by newid()     newid():在扫描数据的时候都会生成一个数,这个数是个随机数,没有大小顺序,order by newid() 意思是按照newid()产生的值进行排序所以就能得到无序的
  28. commit在哪里运用?
    • commit作用就是提交DML操作使用的,在没有commit时数据只存在于内存中,不会写入物理文件中
    • 在进行更新操作后commit之前都是锁表的状态,其他事务无法访问这个表,如果没有进行commit操作,那么就会一直处于锁表状态
  29. Oracle序列的作用?
    • Oracle不像MySQL和SQLServer拥有自增属性设置,只能使用序列实现
    • Oracle使用序列生成唯一的编号,用于处理一个自增的字段
    • Oracle的序列是原子性对象,当访问了一个序列编号,在处理下一个请求之前会自动自增到下一个编号,从而确保不会出现重复值
  30. MySQL和Oracle的区别?
    • 对事务的支持:
      • MySQL:InnoDB引擎在行级锁的情况下支持事务
      • Oracle:完全支持事务
    • 事务的隔离:
      • MySQL:默认是Repeatable Read 隔离级别,可能会出现幻读的问题
      • Oracle:默认是Commited Read隔离级别,可能会出现幻读和不可重复读的问题
    • 事务的提交:
      • MySQL:默认自动提交事务
      • Oracle:需要我们执行commit操作
    • 分页查询:
      • MySQL:使用limit关键字完成分页查询
      • Oracle:要使用伪列Rownum完成
    • 并发性:
      • MySQL:默认是表级锁,对资源的锁定粒度较大,在一个session使用时,将整张表进行锁定,其他session无法进行操作;虽然InnoDB是行级锁,但这个行级锁依赖于索引,如果表中没有索引或SQL语句中没有索引,那么仍然使用表级锁,所以并发性较差
      • Oracle:使用行级锁,不依赖索引,对资源锁定的粒度小,也就是说只锁定SQL需要的资源,所以并发性较好
    • MySQL:轻量级的数据库,免费,没有数据恢复功能
    • Oracle:重量级的数据库,收费,Oracle公司提供很多功能
  31. 谈一谈数据库优化方法的经验?
    • 数据库的设计:
      • 数据库表:字段类型、字段长度、注释、字段命名规范
      • 数据库索引:外键、关联字段、查询频率比较高的字段
        • 如果数据库表字段>20,则最多支持16个索引
        • 如果数据库表字段<20,则根据查询效率字段来定
      • 数据库视图:相当于一张临时表,业务中,尽量少使用
      • 数据库引擎:根据业务,选择对应的表引擎技术
      • 数据库存储过程:尽量少用
      • 数据库字符:UTF-8或者页面字符保持一致
      • 数据库监听器/触发器:一般用于调用任务或者备份还原
    • 业务调用的SQL优化
      • 尽量关联表,效率最高关联4张表,如果多于4张表,则需要开启两个链接事务,但是这两个事务,必须在一个service当中
      • 如果是查询语句,则建议使用*
      • 如果是查询语句,where条件后面,最好使用索引字段进行关联
    • 数据库服务器的搭建(集群):
      • 主从配置
      • 读写分离
      • 自动化(容器)
posted @ 2019-09-26 09:51  SvaloR  阅读(235)  评论(0编辑  收藏  举报