数据库面试必备

什么是 SQL？

结构化语言(Structured Query Language)简称 SQL，是数据库查询语言(3306)

什么是 MySQL？

MySQL 是一个关系型数据库管理系统。

数据库三大范式

• 按照范式设计的优劣：

  • 修改、删除性能高
  • 查询时联合多个表，查询性能低

• 第一范式：每个列都不可再拆分

• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖主键的一部分

• 第三范式：在第二范式的基础上，非主键列只依赖主键，不依赖其他非主键

数据库的数据类型

• 整数 int(4字节)
• 小数
  • float 最多存储8位十进制数，4字节
  • double 最多存储18位十进制数，8字节
• 字符串
  • varchar(可变长度—节省空间)
  • char(不可变)——用于经常变更的数据(不容易产生碎片)
• 时间
  • year YYYY(年)
  • time HH:MM:SS(时分秒)
  • date YYYY-MM-DD(年月日)
  • datetime YYYY-MM-DD HH:MM:SS(年月日 时分秒)
  • timestamp YYYY-MM-DD HH:MM:SS(年月日 时分秒)—空间效率更高

索引

• 数据库索引是数据库管理系统中一个排序的数据结构，用来快速查询和更新数据库中的数据

• 数据库常见的索引是 B 树和 B+ 树、hash

• 索引的优点：

  • 加快数据检索速度
  • 利用索引在查询时使用优化隐藏器可提高性能

• 索引缺点：

  • 需要占用物理空间
  • 在创建索引和维护索引时要耗费时间，会降低增删改的执行效率

• 常见的索引类型

  • 主键索引：数据列不能重复，不能为 null，一个表只能有一个主键
  • 唯一索引：数据列不能重复，可以为 null，一个表可以有多个唯一索引
  • 普通索引：没有唯一性限制，可以为 null
  • 创建索引——ALTER TABLE table_name ADD INDEX index_name(column1, column2)创建联合索引

• 索引的数据结构

  • 等值查询—— hash 索引
  • B-Tree 索引优点：
    • 时间复杂度低， 查找、删除、插入操作可以在对数时间内完成
    • 存储在B-Tree中的数据是有序的
  • 当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载每个磁盘页，磁盘页对应索引树的节点。那么 Mysql 衡量查询效率的标准就是磁盘 IO 次数。如果我们利用二叉树作为索引结构，那么磁盘的 IO 次数和索引树的高度是相关的。
  • 为了提高查询效率，就需要减少磁盘 IO 数。为了减少磁盘 IO 的次数，就需要尽量降低树的高度，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好，因此B树正好符合我们的要求。
  • B 树和 B+ 树：
    • B+ 树是 B 树的一个变形，非叶子节点只保存索引，不保存实际的数据，数据都保存在叶子节点的链表中，所以 B+ 树磁盘页能容纳更多节点元素，树更矮。
    • B 树和 B+ 树同样适用于高度越低，查询越快。
    • B+ 树的查询效率更加稳定。B 树查找节点，B+ 树只需要查询所有节点（索引），在极端情况下，需要全查询索引和数据，不如 B+ 树稳定。
    • 对于范围查找来说，B+树只需遍历叶子节点链表即可，B树却需要重复地中序遍历，所以B+ 树效率更高。

• 创建索引的三种方式：

  • 建表时创建索引
  • 使用 ALTER TABLE 命令去增加索引
  • 使用 CREATE INDEX 命令创建(不能创建主键)

事务

• 事务：一个连续的、不可分割的数据库操作。

• 事务的四大特性(ACID):

  • 原子性：事务执行的最小单位，不可中断
  • 一致性：执行事务前后，数据要保持一致
  • 隔离性：并发访问数据时，一个用户的事务不被其他事务干扰，各自独立
  • 持久性：一个事务提交之后，对数据库的改变是持久的，不随意变化

• 脏读、幻读、不可重复读

  • 脏读：当一个事务正在访问数据并进行修改，而这种修改还没有提交到数据库时，另外一个事务也访问这个数据，然后使用了这个数据
  • 幻读：当一个事务对表中的数据进行了修改，同时第二个事务也修改这个表中的数据，使第一个事务修改的结果不存在——修改的结果不一样
  • 不可重复读：当一个事务多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的——读到的结果不一样

• 事务的隔离级别

  • 为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别
  • 读未提交：允许读取未提交的数据，可能发生脏读、幻读、不可重复读
  • 读已提交：允许读取并发事务已提交的数据，可防止脏读，可能发生幻读、不可重复读
  • 可重复读：对同一字段多次读取结果是一致的，可防止脏读和不可重复读，可能发生幻读
  • 可串行化：所有事务逐个执行，事务之间不产生干扰。

SQL 的生命周期

1. 应用服务器与数据库服务器建立一个连接
2. 数据库进程拿到请求sql
3. 解析并生成执行计划，执行
4. 读取数据到内存并进行逻辑处理
5. 通过步骤一的连接，发送结果到客户端
6. 关掉连接，释放资源

SQL 的优化

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

字段为什么要求定义为not null？

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

锁

锁分为 共享锁(读锁)、排它锁(写锁)

• 读锁：多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改（共享锁）
• 写锁：即线程之间相互排斥的锁，此锁同一时刻只能被一个线程拥有（Lock、synchronized 都是互斥锁）
• 读取到数据时进行判断：
  • 若仅仅读取数据，加读锁，其他线程要加读锁的话，不需等待。直接获取，并在读锁计数器加1。
  • 若要修改更新数据，加写锁，此时无论别的线程要读还是写，都要等待锁释放。

进行并发控制

• 乐观锁
  • 乐观锁认为每次拿数据都不会修改，在读取至更新这段时间别人有没有修改数据，如果修改了，就重新读取，再次尝试更新，经过循环此步骤更新成功。
  • 适合写操作比较少，冲突发生少的场景，减少锁的开销，提高性能
• 悲观锁
  • 悲观锁认为每次拿数据都会修改数据，所以在每次拿数据之后上锁，直到悲观锁释放，此数据才能被再次获取。
  • 适合写操作比较多的场景

Redis 和 MySQL 的区别

• 类型：MySQL 是关系型数据库，Redis 是缓存数据库

• 作用：MySQL用于持久化的存储数据到硬盘,功能强大,但是速度较慢

  ​ Redis用于存储使用较为频繁的数据到缓存中,读取速度快

• 数据存放位置：MySQL：数据放在磁盘 Redis：数据放在内存

• mysql 偏向于存数据，redis 偏向于快速取数据，但 redis 查询复杂的表关系时不如mysql，所以可以把热门的数据放redis，mysql存基本数据

JDBC 六部曲

try {
     //1.导包
     //2.加载驱动类 Driver
     Class.forName("com.mysql.jdbc.Driver");
     //3.获取连接
     String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
     String user = "root";
     String password = "root";
     Connection conn = DriverManager.getConnection(url,user,password);
     //4.创建状态参数
     Statement stat = conn.createStatement();
     //5.执行数据库操作
     stat.executeUpdate("insert into student(sid,sname) value(6,'小黑')");
     System.out.println("执行完毕");
     //6.关闭连接
     stat.close();
     conn.close();
 } catch (ClassNotFoundException e) {
     e.printStackTrace();
 } catch (SQLException throwables) {
     throwables.printStackTrace();
 }

数据库例题

• 查询平均成绩大于60分的学生：

  select sid,avg(score) from sc
  group by sid 
  having avg(score)>60;
  

• 查询姓“李”的老师个数：

  select count(tname)
  from teacher 
  where tname like '李%';
  

• 查询课程2成绩的比课程1成绩低的学生

  select sid,sname
  from (select student.sid,student.sname,score,
       (select score from sc as sc_2 
        where sc_2.sid = student.sid 
        and sc_2.cid = '2') as score2 
        from student,sc 
        where student.sid=sc.sid and cid = '1') s_2
  where score2<score;
  

• 查询所有成绩低于60分的学生

  select sid,sname
  from student
  where sid not in 
  (select s.sid 
  from student s,sc 
  where s.sid=sc.sid and score>60 );
  

• 查询没有学全所有课的学生

  select s.sid,s.sname
  from student s ,sc sc 
  where s.sid = sc.sid
  group by s.sid,s.sname
  having count(sc.cid)<(
  select count(cid) 
  from course);
  

视图

• 使用视图的优点
  • 简化 SQL 查询
  • 保护数据安全
  • 具有数据逻辑独立性
• 视图的特点：
  • 视图的列可来自不同的表
  • 视图是由基本表产生的不真实存在的表
  • 对视图内容的增删改直接影响基本表
  • 当视图来自多个基本表时，不能添加和删除数据

慢查询

• 慢查询：全名慢查询日志，用来记录在 MySQL 中响应时间超过阈值的语句