数据库面试复习

数据库一轮复习

数据库基础

1、数据库的三大范式

• 第一范式：满足
• 第二范式：解除了非主属性对主属性的部分函数依赖
• 第三范式：解除了。。。的传递函数依赖

2、数据类型

• varchar（255）：char[255]
• 时间类型的存储：DateTime和TimeStamp（√）之间的选择
  • 时区问题
  • 存储大小

3、基本命令

• 删除
  • drop：drop table stu; drop database 库名;（DDL，数据库定义语言）
  • truncate：truncate stu;
  • delete：delete from stu where sno = 1 （DML，数据库操纵语言）
• 创建
  • create：create database 库名（character set utf8）; create table 表名(字段列表);
  • insert：insert into 表名 values (字段列表);
• 更新
  • UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
• 查询
  • select sno from stu(表) where sname = '张三'
  • distinct
  • order by
    • SELECT column_name, function(column_name) FROM table_name WHERE column_name GROUP BY column_name;
    • funcation
      • count
      • sum
      • avg
    • having
  • 连接查询
    • 外连接
      • 左连接
      • 右连接
      • 全外连接
    • 内连接
      • 不加on会做笛卡儿积
• 索引
  • 创建索引
    • alter table stu add index index_name (字段1，字段2);
    • create index index_name on table_name (字段1);
  • ⭐explain
  • 删除索引
    • alter table stu drop key index_name;

MySQL基础架构

1、基础架构

• server层：（binlog：归档日志）
  • 连接器：权限校验
  • 缓存
  • 分析器：语法和句法的分析
  • 优化器：选择索引
  • 执行器：调用引擎
• 引擎层：（redolog 重做日志）
  • MyISAM
  • InnoDB（默认）
    • InnoDB和MyISAM的区别
      • innodb支持行锁，myisam表锁（并发效率）
      • innodb支持事务（并发）
      • innodb支持外键
      • innodb支持安全恢复（redo log）
      • innodb支持MVCC（多版本并发控制）

2、sql执行过程

• 读
  • 权限校验——查询缓存——分析器——优化器（索引）——权限校验——执行器——引擎
• 写
  • 分析器——权限校验——执行器——引擎——更新数据——relog（prepare）——提交事务——binlog——redolog（完成）

索引

1、⭐数据结构

• B+树（多路平衡查找树）
  • 特点
    • 只有叶子节点存放key和data，其他只存放key
    • 叶子节点指向他相邻的叶子节点
  • 与B树的比较
    • b+空间利用率高，叶子节点不放数据查的快
    • 叶子节点相互连接，遍历快
  • 与hash表比较
    • hash不支持模糊查询，b+用最左前缀原则
    • hash无法范围查找
    • hash会有hash碰撞，时间不稳定

2、使用索引的优缺点

• 优点：加快查找速度
• 缺点：占用空间，增改时降低效率

3、⭐类型

• 主键索引

  • 一张表只能有一个，非空，不能有重复索引
  • 聚集索引：一张表只能有一个，数据和索引在一起

• 二级索引/辅助索引

  • 唯一索引：不能重复，可以为空，一张表可以多个唯一索引

  • 普通/单列索引：可重复、可为空

  • ⭐联合索引：多个字段

    • 最左前缀原则：a、b、c、d建立索引，左边的存在才会走索引
    • < > between 和 以%开始的like，会停止匹配
    • 例题：a、b、c、e做索引，查select * from table where a=1 and b=1 orderby c,e会走索引吗？（会）怎么走的？
      • a、b会走联合索引

  • 前缀索引：字符串类型匹配

  • 全文索引：每一个词统计出现的次数，出现在哪

  • 非聚集索引

    • 与聚集索引的区别
      • 聚集索引一张表只能有一个（主键索引），非聚集索引可以多个
      • 聚集索引数据物理上是连续存储的，非聚集索引只是逻辑上连续，物理上并不连续
      • 二级索引属于非聚集索引，主键索引为聚集索引

• 覆盖索引

  • 是一种查询方式，不是索引类型
  • 如果直接通过索引的key值找数据，不用回表。这种方式就叫覆盖索引
  • 回表
    • 二级索引中data区域存的不是数据地址，而是主键的值，要拿到具体的数据还需要用主键在主键索引中进行查找，这种方式叫做回表。
    • 不一定要回表，覆盖索引

事务

1、⭐特性（ACID）

• A（Atmo） 原子性

  • 一组操作要么都成功，要么都失败
  • 保证：undo log

• C （Constant）一致性

  • 最终目的，操作前后数据总量不变
  • 保证：bin log

• ⭐I（Island）隔离性

  • 保证事务之间是独立的——多线程

  • 问题

    • 脏读：读到了还未提交的事务（读写）
    • 幻读：读出来和上一次不同（插入/删除）（读写）
    • 不可重读：读出来和上一次不同（更新）（读写）
    • 丢失更新（写写）

  • 解决

    • 隔离级别

      • 读取未提交（脏读、幻读、不可重读）
      • 读取已提交（幻读、不可重读）
      • 可重复读（幻读）：MVCC + 间隙锁
      • 可串行（不能并发，效率低）

    • 锁

• D（during）持久性

  • 事务一旦提交，改变永久的
  • 保证：redo log

2、日志

• bin log归档日志
  • 逻辑日志，存的具体的sql语句，属于server层
  • 格式
    • row：看不到详细信息，会占资源，不会有数据不一致
    • statement：存的是sql的原文，datetime = now()会导致和存的不一样
    • mixed混合的
  • 两阶段提交
    • 更新数据——redo log——提交事务——binlog——redolog
• redo log重做日志
  • 物理日志，用于数据库崩溃恢复，innodb引擎层
• undo log回滚日志
  • 回滚，只记录增删改
  • MVCC（多版本的并发控制）

MySQL的锁机制

1、乐观锁

• 版本号
• 时间戳

2、悲观锁

• 表锁

  • 表锁（手动）
    • 读锁：
      • lock table table_name read;
      • unlock tables;
    • 写锁：
      • lock table table_name write;
      • unlock tables;
  • 元数据锁（自动）:主要是针对数据库定义语言（DDL）加锁
    • CURD：默认加读锁（R）
    • DDL：默认加写锁（W）
  • 意向锁（自动、innodb）：协调行锁和表锁的关系
    • 类别：意向排他锁（IX）、意向共享锁（IS）
    • 当加行锁的时候，加S锁会自动获得IS锁，加X锁会自动获得IX锁
    • IX锁与R、W互斥（有一行在写不能锁表），IS锁与W互斥（有一行在读，不能写锁）

• 行锁（行锁锁的是索引，没有索引会升级成表锁）

  • 按照锁的粒度划分（当前读的实现方式）
    • record lock 记录锁（RC、RR）
    • Gap lock 间隙锁（RR）
    • next key lock 邻键锁（RR）
  • 按照功能分类
    • 共享锁S
      • select * from table_name lock in share mode;
      • 释放：commit/rollback
    • 排他锁X
      • insert/update/delete自动加锁
      • 查询：select * from table_name for update;
      • 释放：commit/rollback
  • ⭐数据读取方式：区别读的时候加不加锁
    • 快照读（MVCC）
      • select * from table_name
      • 实现
        • 行的隐藏字段
          • DB_TRX_ID：最后插入或者更新该行的事务id
          • DB_ROLL_PTR：回滚指针，指向该行的undo log
          • DB_ROW_ID：如果没主键或者设置唯一索引，就用这个做聚簇索引
        • Read View
          • m_low_limit_id：m_ids中最小的id
          • m_up_limit_id：当前事务id+1
          • m_ids：未提交的事务id
          • m_creator_trx_id：创建read view事务id
        • undo log
          • 如果当前版本的数据对事务不可见，就读取之前版本的数据
      • 数据可见性算法
        • 1、DB_TRX_ID < m_low_limit_id：可见
        • 2、DB_TRX_ID >= m_low_limit_id：不可见，跳5
        • 3、m_ids为空：可见
        • 4、m_up_limit_id <= DB_TRX_ID < m_low_limit_id：判断m_ids中有没有，有就不可见跳第5步，没有就可见
        • 5、在该记录行的 DB_ROLL_PTR 指针所指向的 undo log 取出快照记录（找上一个版本），用快照记录的 DB_TRX_ID 跳到步骤 1 重新开始判断，直到找到满足的快照版本或返回空
    • 当前读（锁）
      • 手动加锁需要关闭数据库中的自动提交，因为事务提交后会放锁
      • 当前读的具体加锁方式（next-key lock）（具体过程见思维导图）
        • 唯一索引（会退化）
        • 非唯一索引（先锁非唯一索引，再锁唯一索引）

• 关闭自动提交

  • set autocommit = 0;

• 死锁

  • 表死锁
    • A锁表1等表2，B锁表2等表1
  • 行死锁
    • 行锁升级成表锁
    • A锁表1等表2，B锁表2等表1
  • 共享锁转排他锁

慢查询

1、sql慢查询的原因

• 线程没获得锁
• 没建立索引或者索引回表多

2、定位慢查询sql（慢查询日志）

• 查询是否开启：show variables like "%slow_query_log%";
• 开启set global slow_query_log = on;

3、慢查询优化

• 不使用子查询
• 避免函数索引
• like无法用索引
• 减少order by

other

1、主键自增策略

• 自增
• uuid
• redis生成
• 雪花算法

2、sql注入

• Java中prepareStatement

• mybatis中的#{}

• 本质是把sql中的特殊字符转义