三大范式之间,是逐级递进的关系,也就是说后一个范式是在前一个范式的基础上推行。
1 数据库三大范式是什么
- 第一范式,我们通常也叫 1NF
- 第一范式要求我们必须遵守原子性
- 即数据库表的每一列都是不可分割
- 每列的值具有原子性,不可再分割
- 每个字段的值都只能是单一值
- 即数据库表的每一列都是不可分割
- 首先第二范式是在满足第一范式的基础上
-
第二范式要求表中的所有列,其数据依赖于主键
-
即一张表只存储同一类型的数据,不能有任何一列数据与主键没有关系
- 如果表是单主键,那么主键以外的列必须完全依赖于主键,其它列需要跟主键有关系
- 如果表是复合主键,那么主键以外的列必须完全依赖于主键,不能仅依赖主键的一部分
-
第三范式要求表中每一列数据不能与主键之外的字段有直接关系
- 表中的非主键列必须和主键直接相关而不能间接相关
- 非主键列之间不能相关依赖,不存在传递依赖
2 mysql有哪些索引类型,分别有什么作用
-
索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构,这也是索引最基本的功能
【1】主键索引(聚簇索引)
-
一定会有主键索引
-
主键索引是用来唯一标识一条记录的索引,每个表只能有一个主键索引。
- MySQL的聚簇索引是基于B+树的数据结构实现的,它会把数据存储在索引的叶子节点上,叶子节点之间按顺序链接,使得按主键进行搜索时速度最快。
- 如果没有主键,如果按主键搜索,速度是最快的
-
注意事项:
-
若表没有定义主键,则InnoDB存储引擎会自动生成一个隐藏的6字节长字段作为主键,并将其作为聚簇索引。
-
主键索引对于按照主键进行查询的性能非常高。
-
表不建立主键,也会有个隐藏字段是主键,是主键索引
-
- 数据列不允许重复,不允许为NULL,一个表只能有一个主键。
ALTER TABLE table_name ADD PRIMARY KEY (column);
【2】普通索引(辅助索引)
-
辅助索引是为表中的某个字段建立的索引,可以加快该字段的查询速度。
- 在创建表时,我们可以通过指定字段的index属性为True来创建普通索引(例如在Django中使用"index=True")。
- 但是,对于变化较小的字段(如性别、年龄)不建议创建普通索引。
-
示例:创建普通索引
CREATE INDEX index_id ON tb_student(id);
- MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和NULL值。一个表允许多个列创建普通索引。
ALTER TABLE table_name ADD INDEX index_name (column);
【3】唯一索引(unique)
-
唯一索引用于保证数据的唯一性,即该索引所对应的字段的取值不能重复。
- 唯一索引通常使用UNIQUE关键字进行创建,它可以加速唯一性约束的查找及避免数据重复。
-
示例:创建唯一索引
CREATE UNIQUE INDEX index_id ON tb_student(id);
- 索引列中的值必须是唯一的,但是允许NULL值。
- 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
- 一个表允许多个列创建唯一索引。
ALTER TABLE table_name ADD UNIQUE (column);
【4】组合索引(联合索引)
-
组合索引是指将多个字段作为一个索引的索引类型,在查询时,可以通过多个字段联合进行快速查询。
-
在创建组合索引时,需要通过指定多个字段的顺序来定义索引的顺序。
-
django 中:class Meta:
-
-
示例:创建组合索引
CREATE INDEX index_name ON table_name (column1, column2, column3);
- 指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀原则。
- 主键索引、普通索引、唯一索引等都可以使用多个字段形成组合索引。
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 );
【5】全文索引
- 全文索引主要用于文本关键字的搜索,只能在CHAR、VARCHAR或TEXT类型的列上创建。
- 在MySQL中,只有MyISAM存储引擎支持全文索引。
- 全文索引允许在索引列中插入重复值和空值。
- 但是,对于大容量的数据表,生成全文索引会消耗大量时间和硬盘空间。
- 注意事项:
- 在创建全文索引时,需要使用FULLTEXT关键字。
- 主要是为了快速检索大文本数据中的关键字的信息。
- 字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引,基于倒排索引,类似于搜索引擎。
- MyISAM存储引擎支持全文索引,InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
ALTER TABLE table_name ADD FULLTEXT (column);
【6】前缀索引
- 在文本类型如BLOB、TEXT或者很长的VARCHAR列上创建索引时,可以使用前缀索引,数据量相比普通索引更小,可以指定索引列的长度,但是数值类型不能指定。
- ALTER TABLE table_name ADD KEY(column_name(prefix_length));
【7】空间索引
- MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
3 事务的特性和隔离级别
【1】原子性(Atomicity)
-
事务是一个不可再分割的单位,要么全部执行成功,要么全部回滚到初始状态,没有中间状态。
-
这意味着如果事务中的任何一步操作失败,整个事务都会被回滚,以确保数据的一致性。
【2】一致性(Consistency)
-
事务执行前后,数据库的状态必须保持一致。
-
这意味着事务在执行期间对数据的操作必须满足预定义的规则和完整性约束,以确保数据的有效性和正确性。
【3】隔离性(Isolation)
-
多个事务可能同时执行,事务之间应该相互隔离,互不影响。
-
隔离性确保每个事务的操作在逻辑上独立于其他并发事务的操作,从而避免了数据不一致的问题。
【4】持久性(Durability)
- 一旦事务提交,对数据库的更改应该是永久性的,即使在系统故障的情况下也不应该丢失。
- 持久性通过将事务记录在持久存储介质(如磁盘)上来实现。
【二】事务隔离级别
【1】Read uncommitted(读未提交)
- 最低的隔离级别,在这个级别下,一个事务可以读取到另一个事务尚未提交的数据,可能导致脏读(Dirty Read)问题,即读取到未经验证的数据。
【2】Read committed(读已提交)
-
在这个级别下,一个事务只能读取到已经提交的数据,避免了脏读问题。
-
但是可能会出现不可重复读(Non-repeatable Read)问题,即同一事务中,两次读取相同的记录可能得到不同的结果,因为其他事务修改了这些记录。
【3】Repeatable read(可重复读取)
-
在这个级别下,事务开始读取数据后,其他事务无法修改这些数据,保证了同一个事务内两次读取相同记录的一致性。
-
但是可能会出现幻读(Phantom Read)问题,即同一查询在同一事务中两次执行可能返回不同的结果,因为其他事务插入或删除了符合查询条件的记录。
【4】Serializable(串行化)
- 最高级别的隔离级别,要求事务串行执行,事务之间完全隔离,避免了脏读、不可重复读和幻读问题。
- 但是这会牺牲并发性能,因为并发事务被限制为顺序执行。