MySQL
基础
1.数据库的三范式是什么?
数据库范式是设计数据库时,需要遵循的一些规范。各种范式是条件递增的联系,越高的范式数据库冗余越小。常用的数据库三大范式为:
- 第一范式(1NF):每个列都不可以再拆分,强调的是列的原子性,即数据库表的每一列都是不可分割的原 子数据项。
- 第二范式(2NF):在满足第一范式的基础上,非主属性完全依赖于主码(主关键字、主键),消除非主属性对主码的部分函数依赖。
- 第三范式(3NF):在满足第二范式的基础上,表中的任何属性不依赖于其它非主属性,消除传递依赖。简而言之,非主键都直接依赖于主键,而不是通过其它的键来间接依赖于主键。
2. MySQL 支持哪些存储引擎? 🔥
● MySQL 支持的引擎包括:InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE 等;
● MySQL5.5 之前的默认存储引擎是 MyISAM,5.5 之后就改为了 InnoDB。
3. MyISAM 和 InnoDB 的区别有哪些?🔥
MyISAM 存储引擎:
● 特点:不支持事务和外键;
● 索引:MyISAM 是非聚簇索引,索引文件和数据文件是分离的,索引保存的是数据的地址;
● 检索:MyISAM 支持全文索引,查询效率上 InnoDB 要高;
● 锁:MyISAM 支持表级锁,每次操作对整个表加锁,一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限;
● 存贮方式: 存贮表的总行数,执行 select count() from table 时只需要读出该变量即可,速度很快;
● 应用场景:查询和插入操作为主,只有很少更新和删除操作,并对事务的完整性、并发性要求不高。 因为MyISAM管理非事务表。它提供高速存储和检索,以及全文搜索能力。
InnoDB 存储引擎:
● 特点:支持事务和外键操作,支持并发控制;
● 索引:InnoDB 是聚簇索引,索引和数据保存在同一个 B+ 树中,因此从聚簇索引中获取数据比非聚簇索引更快;
● 检索:Innodb 不支持全文索引,执行 select count() from table 时需要全表扫描;
● 锁:Innodb 支持行级锁和表级锁,默认为行级锁;
● 存贮方式:不存贮表的总行数,MyISAM 用一个变量保存了整个表的行数,执行 select count(*) from table 时只需要读出该变量即可,速度很快;
● 应用场景:MySQL 5.5版本后默认,对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,读写频繁的操作
4. MySQL 的逻辑架构
- 第一层是网络连接层,主要完成一些类似于连接处理、授权认证、及相关的安全方案。
- 第二层是核心服务层,包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数,所有跨存储引擎的功能都在这一层实现,例如存储过程、触发器、视图等。
- 第三层是存储引擎层,存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 和存储引擎进行通信,不同的存储引擎具有不同的功能,共用一个 Server 层,可以根据开发的需要,来选取合适的存储引擎。
- 第四层是系统文件层,主要是将数据存储在文件系统之上,并完成与存储引擎的交互。
索引
1. MySQL有哪几种索引类型?
- 从数据结构上来划分:哈希索引,B树索引,B+树索引。
- 从功能层次上来划分:普通索引,唯一索引,主键索引,联合索引。
● 普通索引:即一个索引只包含单个列,一个表可以有多个单列索引。
● 唯一索引:索引列的值必须唯一,但允许有空值。
● 主键索引:一种特殊的唯一索引,不允许有空值,一般在建表时同时创建主键索引;
● 联合索引:多列值组成一个索引,专门用于组合搜索。 - 从物理存贮上来划分:聚簇索引,非聚簇索引。
2. 聚簇索引和非聚簇索引?🔥
聚簇索引: 聚簇索引是按照每张表的主键构造一颗B+树,叶子节点中存放的就是整张表的数据,将聚簇索引的叶子节点称为数据页。
聚簇索引的特点:
聚簇索引不需要我们显式去创建,InnoDB 存储引擎会自动的为我们创建聚簇索引,并且在 InnoDB 存储引擎中, 聚簇索引 就是数据的存储方式(所有的用户记录都存储在了叶子节点 ),也就是所谓的索引即数据,数据即索引。
聚簇索引的优点:
● 数据访问更快,聚簇索引将索引和数据保存在同一个 B+ 树中,因此从聚簇索引中获取数据比非聚簇索引更快;
● 聚簇索引对于主键的排序查找和范围查找速度非常快。
聚簇索引的缺点:
● 插入速度严重依赖于插入顺序,按照主键的顺序(递增)插入是最快的方式,否则将会出现页分裂,严重影响性能。
● 更新主键的代价很高,将会导致被更新的行移动,所以对于 InnoDB 表,一般定义主键为不可更新。
● 聚簇索引只能在搜索条件是主键值时才能发挥作用,因为 B+ 树中的数据都是按照主键进行排序的。
非聚簇索引:非聚簇索引叶子节点存储的是主键值,而不是数据的物理地址,所以访问数据需要二次查找,推荐使用覆盖索引,可以减少回表查询。
非聚簇索引的特点:
● B+ 树的叶子节点存储的并不是完整的用户记录,而只是 索引列+主键 这两个列的值。
● 以索引列大小排序的 B+ 树只能确定我们要查找记录的主键值,所以如果我们想根据 c2 列的值查找到完整的用户记录的话,仍然需要到 聚簇索引 中再查一遍,这个过程也被称为 回表。
非聚簇索引的优点:
● 非聚簇索引由于不存储实际数据,所以实际文件较小,相比于聚簇索引再读取时可以减少磁盘IO。
● 非聚簇索引使用主键作为”指针” 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作。
非聚簇索引的缺点:
● 需要进行回表查询,即查询到对应的聚簇索引之后再通过聚簇索引查询到所需数据。
事务
1. 什么是脏读?幻读?不可重复读?丢失更新?(事务的并发问题)
- 脏读:一个事务读取了另一个事务未提交的数据。
- 不可重复读: 就是在一个事务范围内,两次相同的查询会返回两个不同的数据,这是因为在此间隔内有其他事务对数据进行了修改。
- 幻读: 幻读是指当事务 不是独立执行时 发生的一种现象,例如事务A对表中的数据进行了修改,这种修改涉及到表中的全部数据行,同时,另一个事务B也修改这个表中的数据,这种修改是向表中 插入一行新数据。那么,事务A的用户发现表中还有没有修改的数据行,就好像发生了幻觉一样。
- 丢失更新(脏写):
两个事务同时读取同一条记录,事务 A 先修改记录,事务 B 也修改记录(B 是不知道 A 修改过),当 B 提交数据后, 其修改结果覆盖了 A 的修改结果,导致事务 A 更新丢失。