MySQL笔记：基础、索引

MySQL

基础

1.数据库的三范式是什么？

数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为：

1. 第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性，即数据库表的每一列都是不可分割的原 子数据项。
2. 第二范式（2NF）：在满足第一范式的基础上，非主属性完全依赖于主码（主关键字、主键），消除非主属性对主码的部分函数依赖。
3. 第三范式（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 的逻辑架构

1. 第一层是网络连接层，主要完成一些类似于连接处理、授权认证、及相关的安全方案。
2. 第二层是核心服务层，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。
3. 第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 和存储引擎进行通信，不同的存储引擎具有不同的功能，共用一个 Server 层，可以根据开发的需要，来选取合适的存储引擎。
4. 第四层是系统文件层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

索引

1. MySQL有哪几种索引类型？

1. 从数据结构上来划分：哈希索引，B树索引，B+树索引。
2. 从功能层次上来划分：普通索引，唯一索引，主键索引，联合索引。
   ● 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。
   ● 唯一索引：索引列的值必须唯一，但允许有空值。
   ● 主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引;
   ● 联合索引：多列值组成一个索引，专门用于组合搜索。
3. 从物理存贮上来划分：聚簇索引，非聚簇索引。

2. 聚簇索引和非聚簇索引？🔥

聚簇索引： 聚簇索引是按照每张表的主键构造一颗B+树，叶子节点中存放的就是整张表的数据，将聚簇索引的叶子节点称为数据页。
聚簇索引的特点：
聚簇索引不需要我们显式去创建，InnoDB 存储引擎会自动的为我们创建聚簇索引，并且在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了叶子节点 ），也就是所谓的索引即数据，数据即索引。
聚簇索引的优点：
● 数据访问更快，聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快；
● 聚簇索引对于主键的排序查找和范围查找速度非常快。
聚簇索引的缺点：
● 插入速度严重依赖于插入顺序，按照主键的顺序（递增）插入是最快的方式，否则将会出现页分裂，严重影响性能。
● 更新主键的代价很高，将会导致被更新的行移动，所以对于 InnoDB 表，一般定义主键为不可更新。
● 聚簇索引只能在搜索条件是主键值时才能发挥作用，因为 B+ 树中的数据都是按照主键进行排序的。

非聚簇索引：非聚簇索引叶子节点存储的是主键值，而不是数据的物理地址，所以访问数据需要二次查找，推荐使用覆盖索引，可以减少回表查询。
非聚簇索引的特点：
● B+ 树的叶子节点存储的并不是完整的用户记录，而只是 索引列+主键 这两个列的值。
● 以索引列大小排序的 B+ 树只能确定我们要查找记录的主键值，所以如果我们想根据 c2 列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程也被称为 回表。
非聚簇索引的优点：
● 非聚簇索引由于不存储实际数据，所以实际文件较小，相比于聚簇索引再读取时可以减少磁盘IO。
● 非聚簇索引使用主键作为”指针” 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作。
非聚簇索引的缺点：
● 需要进行回表查询，即查询到对应的聚簇索引之后再通过聚簇索引查询到所需数据。

事务

1. 什么是脏读？幻读？不可重复读？丢失更新？(事务的并发问题)

1. 脏读：一个事务读取了另一个事务未提交的数据。
2. 不可重复读： 就是在一个事务范围内，两次相同的查询会返回两个不同的数据，这是因为在此间隔内有其他事务对数据进行了修改。
3. 幻读： 幻读是指当事务 不是独立执行时 发生的一种现象，例如事务A对表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，另一个事务B也修改这个表中的数据，这种修改是向表中 插入一行新数据。那么，事务A的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。
4. 丢失更新（脏写）：
   两个事务同时读取同一条记录，事务 A 先修改记录，事务 B 也修改记录（B 是不知道 A 修改过），当 B 提交数据后， 其修改结果覆盖了 A 的修改结果，导致事务 A 更新丢失。