Java面试之MySQL知识点总结

赴前尘

于 2022-06-07 14:41:57 发布

阅读量143

点赞数

分类专栏： Java面试文章标签： java 面试 mysql

本文链接：https://blog.csdn.net/qq_36940806/article/details/125165053

版权

Java面试专栏收录该内容

4 篇文章 0 订阅

订阅专栏

事务

定义：事务是指逻辑上的一组操作，事务中包含的各操作要么都完成，要么都不完成(要么一起成功，要么一起失败)
特性
- 原子性（Atomicity）
  - 原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
    - 通过undo log 保证原子性
    - 原子性只能保证单个事务的一致性，不能保证多个事务的一致性
- 一致性（Consistency）
  - 执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
- 隔离性（Isolation）
  - 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
    - 实现方式
      - MVCC(多版本并发控制)
- 持久性（Durability
  - 一个事务被提交之后，它对数据库中数据的改变是持久的，即使发生宕机，仍然可以依靠事务日志完成数据的持久化。
    - 通过redo log 保证持久性
并发问题
- 脏读（Dirty read）
  - A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。
- 丢失修改（Lost to modify）
  - A，B事务读取同一数据并修改，A事务提交修改，然后B事务又提交了修改，B提交的结果破坏了A提交的结果，导致了A的修改失效了。
- 不可重复读（Unrepeatable read）
  - 是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
- 幻读（Phantom read）
  - 事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。
隔离级别
- READ-UNCOMMITTED(读取未提交)
  - 会出现脏读，不可重复读，幻读
- READ-COMMITTED(读取已提交)
  - 会出现不可重复读，幻读
- REPEATABLE-READ(可重复读)
  - 会出现幻读
- SERIALIZABLE(可串行化)

MVCC(多版本并发控制)

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。每个事务读到的数据项都是一个历史快照，被称为快照读，不同于当前读的是快照读读到的数据可能不是最新的，但是快照隔离能使得在整个事务看到的数据都是它启动时的数据状态。而写操作不覆盖已有数据项，而是创建一个新的版本，直至所在事务提交时才变为可见。
优缺点
- MVCC 使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作

存储引擎

MyISAM
- 表级锁
InnoDB（5.5版本默认）
- 行级锁
  - 记录锁（Record Locks）
  - 间隙锁（Gap Locks）
  - 临键锁（Next-Key Locks）
- 支持事务
  - 默认事务隔离级别：REPEATABLE-READ
- 外键
- 安全恢复
- 支持MVCC
MyISAM和InnoDB的区别
- InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务
- InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败
- InnoDB是聚集索引，使用B+Tree作为索引结构；MyISAM是非聚集索引，也是使用B+Tree作为索引结构。
- InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
  - 为什么InnoDB不记录行数？
    - 因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。
- InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
- InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而MyISAM可以没有
如何选择？
- 是否要支持事务，如果要请选择InnoDB，如果不需要可以考虑MyISAM
- 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB
- MySQL5.5版本开始InnoDB已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差

索引

用途：快速寻找那些具有特定值的记录，如果没有索引，执行查询时遍历整张表。
索引结构
- hash
  - 实现方式
    - 数组+链表
- B+树
  - 为什么选用B+树？
    - 一个节点可以存储多个元素，从而使得B+树的高度不会太高；叶子节点之间有指针，可以很好地支持全表扫描，范围查找等SQL语句。
  - B+树和二叉查找树，平衡二叉树，红黑树，B树的区别？
    - 二叉查找树（BST树）
      - 性质
        
        若当前节点的左子树不空，则左子树上所有节点点的值均小于当前节点的值
        当前节点的右子树上所有节点的值均大于当前节点的值
        当前节点的左、右子树也分别为二叉查找树
      - 缺点
        
        当先后插入的关键字有序时，BST退化成单支树结构。此时树高n。平均查找长度为(n+1)/2，查找的平均时间复杂度在O(N)数量级上。
        新结点插入到树的叶子上，完全不需要改变树中原有结点的组织结构。插入一个结点的代价的平均时间复杂度在O(N)数量级上
    - 平衡二叉树（AVL树）
      - 性质
        
        是一颗空树，或者当前节点的左右两个子树的高度差(平衡因子)的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树
      - 缺点
        
        追求严格平衡，插入和删除调整树的平衡代缴较高
    - 红黑树
      - 性质
        
        只要求部分地达到平衡要求，降低了对旋转的要求，从而提高了性能
      - 优点
        
        插入和删除操作改变树的平衡性的概率要远远小于AVL（RBT不是高度平衡的）。因此需要的旋转操作的可能性要小，而且一旦需要旋转，插入一个结点最多只需要旋转2次，删除最多只需要旋转3次(小于AVL的删除操作所需要的旋转次数)
    - B树
      - 缺点
        
        B树在提高了IO性能的同时并没有解决元素遍历效率低下的问题
      - 性质
        
        B/B+树是为了磁盘或其它存储设备而设计的一种平衡多路查找树(相对于二叉,B树每个内节点有多个分支),与红黑树相比,在相同的的节点的情况下,一颗B/B+树的高度远远小于红黑树的高度
    - B+树
      - 性质
        
        B+树是在B树的基础上基础上进行改造，它的数据都在叶子节点，同时叶子节点之间还加了指针形成链表
      - 缺点
        
        B+树只需要去遍历叶子节点就可以实现整棵树的遍历
- hash和B+树的区别？
  - 在精确查找的情况下：hash索引要高于btree索引，因为hash索引查找数据基本上能一次定位数据（大量hash值相等的情况下性能会有所降低，也可能低于btree）,而btree索引基于节点上查找，所以在精确查找方面hash索引一般会高于btree索引。
  - Hash索引不能进行范围查询，而B+树可以。
    这是因为Hash索引指向的数据是无序的，而B+ 树的叶子节点是个有序的链表。
  - hash是不支持索引排序的，索引值和hash计算出来的hash值大小并不一定一致。
  - Hash索引无法进行模糊查询。而B+ 树使用 LIKE 进行模糊查询的时候，以%结尾的话走索引
聚簇索引
- 表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚簇索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
非聚簇索引
- 表数据存储顺序与索引顺序无关。对于非聚簇索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
优点
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
使用场景
- 在经常需要搜索的列上，可以加快搜索的速度
- 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度
拒绝使用场景
- 对于那些在查询中很少使用或者参考的列不应该创建索引
  - 既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求
- 对于那些只有很少数据值的列也不应该增加索引
  - 由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引
  - 这些列的数据量要么相当大，要么取值很少
- 当修改性能远远大于检索性能时，不应该创建索引
  - 修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
索引创建方式
- 直接创建索引
  - create index indexName on tableName(fieldName);
- 间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引
- 建表时创建索引
  - create table(
    tid int,
    tname varchar(20),
    gender varchar(1),
    index [indexName] (fieldName)
    );
- 为表字段添加索引
  - alter table tableName add unique index indexName (fieldName);
索引删除方式
- drop index [indexName] on tableName;
主键和唯一索引的区别
- 1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
- 1. 主键创建后一定包含一个唯一性索引，唯一性索引不一定就是主键
- 1. 唯—性索引列允许空值，而主键列不允许为空值。
- 1. 主键可以被其他表引用为外键，而唯一索引不能。
- 1. 一个表最多只能创建一个主键，但是可以创建多个唯一索引
- 1. 主键更适合那些不容易改变的唯一标识，如自动递增列，身份证号等

基本操作

表
- DROP:删除表
- TRUNCATE:清空表
- ALTER:修改表结构
- CREATE:创建表
数据类型
- varchar：变长字符串
- blob:二进制字符串
- text:非二进制字符串
查询
- 关键字顺序：SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
- 连接查询
  - 交叉连接
    - 笛卡尔积
  - 自然连接（默认）
  - 内连接
    - inner join
      - 组合两个表中的记录，返回关联字段相符的记录
  - 外连接
    - 左外连接
      - left join
        
        左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，记录不足的地方均为NULL
    - 右外连接
      - right join
        
        右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录，记录不足的地方均为NULL
- 分页查询
  - 直接使用数据库提供的SQL语句
    - 使用方式：SELECT * FROM 表名称 LIMIT M,N；查询[M,M+N)区间的记录
    - 缺点：全表扫描,速度会很慢，且有的数据库结果集返回不稳定(如某次返回1,2,3,另外一次返回2,1,3)。
  - 子查询
    - SELECT * FROM 表名称 WHERE id >=( SELECT id FROM 表名称 LIMIT M, 1 ) LIMIT N; 先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况
  - 建立主键或唯一索引
    - 使用方式：SELECT * FROM 表名称 WHERE pk_id > (pageNumpageSize) LIMIT M；查询（pageNumpageSize,pageNum*pageSize+M]的记录
    - 缺点：索引扫描,速度会很快，只适用于pk_id递增的情况
备份与还原
- 导出：mysqldump
- 导入：source
视图
- CREATE VIEW …
触发器
- CREATE TRIGGER …
事务
- 开启：START TRANSACTION; 或者 BEGIN;
- 提交：COMMIT
- 回滚：ROLLBACK
- 数据定义语言（DDL）不能被回滚
- 事务不能被嵌套
锁
- 加锁：LOCK TABLES table_name
- 解锁：UNLOCK TABLES table_name

数据库范式

1NF（第一范式）
- 属性不可再分
2NF（第二范式）
- 满足1NF，消除了非主属性对于主键的部分函数依赖，非主属性完全依赖于主关键字
3NF（第三范式）
- 满足2NF，消除了非主属性对于主键的传递函数依赖，非主属性不依赖于其它非主属性

日志

重做日志（redo log）
- 我们通过事务修改数据时，首先会将数据库变化的信息记录到重做日志中，然后再对数据库中的数据进行修改。这样即使数据库系统发生奔溃，我们还可以通过重做日志进行数据恢复。确保事务的持久性。
回滚日志（undo log）
- 当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
- undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。
二进制日志（binlog）
- 逻辑格式的日志，用于记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制日志，以二进制的形式保存在磁盘中。
物理日志和逻辑日志的区别
- 物理日志：存储内容：存储数据库中特定记录的变更，通常是 page oriented，即描述具体某一个 page 的修改操作；
  例子：一条更新请求对应的初始值（original value）以及更新值（after value）；
- 逻辑日志：存储内容：存储事务中的一个操作；
  例子：事务中的 UPDATE、DELETE 以及 INSERT 操作。

锁

表锁
间隙锁（Gap Locks）：锁定索引记录间隙，保证某个间隙内的数据在锁定情况下不会发生任何变化。
- 唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
  普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
  间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现幻读现象；
记录锁（Record Locks）：锁直接加在索引记录上面，锁住的是key，锁定特定行不允许进行修改
临键锁（Next-Key Locks）：记录锁和间隙锁组合起来就叫Next-Key Lock
- 采用临键锁解决Repeated-Read隔离级别下出现的幻读问题

SQL优化

避免不走索引的场景
- 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
  SELECT * FROM t WHERE username LIKE ‘%陈%’
  - 优化方式：尽量在字段后面使用模糊查询。
    SELECT * FROM t WHERE username LIKE ‘陈%’
- 尽量避免使用 in 和 not in，会导致引擎走全表扫描
  SELECT * FROM t WHERE id BETWEEN 2 AND 3
  - 优化方式：
    如果是连续数值，可以用 between 代替。
    SELECT * FROM t WHERE id IN (2,3)
    如果是子查询，可以用 exists 代替。
    SELECT * FROM t WHERE id BETWEEN 2 AND 3
- 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描
  SELECT * FROM t WHERE id BETWEEN 2 AND 3
  - 优化方式：可以用 union 代替 or。
    SELECT * FROM t WHERE id = 1
    UNION
    SELECT * FROM t WHERE id = 3
SELECT 语句其他优化
- 避免出现 select *
- 多表关联查询时，小表在前，大表在后
  - 将小表放在前面，先扫小表，扫描快效率较高
- 使用表的别名
  - 当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
- 用 where 字句替换 HAVING 字句
增删改 DML 语句优化
- 如果同时执行大量的插入，建议使用多个值的 INSERT 语句。这比使用分开 INSERT 语句快，一般情况下批量插入效率有几倍的差别

MySQL和Oracle的区别

对事务的提交
- MySQL默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮
分页查询
- MySQL是直接在SQL语句中写"select… from …where…limit x, y",有limit就可以实现分页;而Oracle则是需要用到伪列ROWNUM和嵌套查询
端口
- mysql默认端口：3306，默认用户：root
  oracle默认端口：1521，默认用户：system
数据库结构层次
- mysql：默认用户是root，用户下可以创建好多数据库，每个数据库下还有好多表，一般情况下都是使用默认用户，不会创建多个用户；
  oracle：创建一个数据库，数据库下有好多用户：sys、system、scott等，不同用户下有好多表，一般情况下只创建一个数据库用。
字段类型
- mysql：int、float、double等数值型，varchar、char字符型，date、datetime、time、year、timestamp等日期型。
  oracle：number（数值型），varchar2、varchar、char（字符型），date（日期型）等…
主键自增
- mysql一般使用自动增长类型，在创建表时只要指定表的主键auto increment，插入记录时，不需要再指定该记录的主键值，mysql将自动增长。
  oracle没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值赋给该字段即可