一、常见存储引擎1.1 InnoDB InnoDB 是 MySQL 5.5 之后默认的存储引擎,它具有高可靠、高性能的特点,主要具备以下优势:
一个 InnoDB 引擎完整的内存结构和磁盘结构如下图所示: 1.2 MyISAM MyISAM 是 MySQL 5.5 之前默认的存储引擎。创建 MyISAM 表时会创建两个同名的文件:
在 MySQL 8.0 之后,只会创建上述两个同名文件,因为 8.0 后表结构的定义存储在 MySQL 数据字典中,但在 MySQL 8.0 之前,还会存在一个扩展名为 .frm 的文件,用于存储表结构信息。MyISAM 与 InnoDB 主要的区别其只支持表级锁,不支持行级锁,不支持事务,不支持自动崩溃恢复,但可以使用内置的 mysqlcheck 和 myisamchk 工具来进行检查和修复。 1.3 MEMORYMEMORY 存储引擎(又称为 HEAP 存储引擎)通常用于将表中的数据存储在内存中,它具有以下特征:
基于以上特性,MEMORY 表主要适合于存储临时数据 ,如会话状态、实时位置等信息。 1.4 CSVCSV 存储引擎使用逗号分隔值的格式将数据存储在文本文件中。创建 CSV 表时会同时创建两个同名的文件:
ARCHIVE 存储引擎默认采用 zlib 无损数据压缩算法进行数据压缩,能够利用极小的空间存储大量的数据。创建ARCHIVE 表时,存储引擎会创建与表同名的 ARZ 文件,用于存储数据。它还具有以下特点:
MERGE 存储引擎,也称为 MRG_MyISAM 引擎,是一组相同 MyISAM 表的集合。 ”相同” 表示所有表必须具有相同的列数据类型和索引信息。可以通过 UNION = (list-of-tables) 选项来创建 MERGE 表,如下: mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;复制代码 创建表时可以通过 INSERT_METHOD 选项来控制 MERGE 表的插入:使用 FIRST 或 LAST 分别表示在第一个或最后一个基础表中进行插入;如果未指定 INSERT_METHOD 或者设置值为 NO ,则表示不允许在 MERGE 表上执行插入操作。MERGE 表支持 SELECT,DELETE,UPDATE 和 DELETE 语句,示例如下: 二、索引2.1 B+ tree 数据结构如果没有特殊说明,通常大多数数据库采用的索引都是 B+ tree 索引,它是基于 B+ tree 这种数据结构构建的。为什么采用 B+ tree 而不是平衡二叉树 (AVL) 或红黑树等数据结构?这里假设索引为 1-16 的自增数据,各类数据结构的表现如下: 平衡二叉树数据结构: 红黑树数据结构: Btree 数据结构: B+ Tree 数据结构
从上面的图示中我们可以看出 B+ Tree 树具有以下优点:
对于 InnoDB ,因为主键索引是聚集索引,所以其叶子节点存储的就是实际的数据。而非主键索引存储的则是主键的值 : 对于 MyISAM,因为主键索引是非聚集索引,所以其叶子节点存储的只是指向数据位置的指针: 综上所述,B+ tree 结构普遍适用于范围查找,优化排序和分组等操作。B+ tree 是基于字典序进行构建的,因此其适用于以下查询:
使用哈希索引时,存储引擎会对索引列的值进行哈希运算,并将计算出的哈希值和指向该行数据的指针存储在索引中,因此它更适用于等值比较查询,而不是范围查询,同样也不能用于优化排序和分组等操作。在建立哈希索引时,需要选取选择性比较高的列,即列上的数据不容易重复 (如身份证号),这样可以尽量避免哈希冲突。因为哈希索引并不需要存储索引列的数据,所以其结构比较紧凑,对应的查询速度也比较快。 InnoDB 引擎有一个名为 “自适应哈希索引 (adaptive hash index)” 的功能,当某些索引值被频繁使用时,它会在内存中基于 B+ tree 索引再创建一个哈希索引,从而让 B-Tree 索引具备哈希索引快速查找的优点。 2.4 索引的优点
InnoDB 存储引擎支持以下两种标准的行级锁:
排它锁和共享锁的兼容情况如下: X X X 不兼容 不兼容 S 不兼容 兼容 3.2 意向共享锁与意向排它锁为了说明意向锁的作用,这里先引入一个案例:假设事务 A 利用 S 锁锁住了表中的某一行,让其只能读不能写。之后事务 B 尝试申请整个表的写锁,如果事务 B 申请成功,那么理论上它就应该能修改表中的任意一行,这与事务 A 持有的行锁是冲突的。想要解决这个问题,数据库必须知道表中某一行已经被锁定,从而在事务 B 尝试申请整个表的写锁时阻塞它。想要知道表中某一行被锁定,可以对表的每一行进行遍历,这种方式可行但是性能比较差,所以 InnoDB 引入了意向锁。
按照意向锁的规则,当上面的事务 A 给表中的某一行加 S 锁时,会同时给表加上 IS 锁,之后事务 B 尝试获取表的 X 锁时,由于 X 锁与 IS 锁并不兼容,所以事务 B 会被阻塞。 X IX S IS X 不兼容 不兼容 不兼容 不兼容 IX 不兼容 兼容 不兼容 兼容 S 不兼容 不兼容 兼容 兼容 IS 不兼容 兼容 兼容 兼容 3.3 一致性读1. 一致性非锁定读 一致非锁定读 (consistent nonlocking read) 是指在 InnoDB 存储引擎下,如果将要读取的行正在执行 DELETE 或 UPDATE 操作,此时不必去等待行上锁的释放,而是去读取 undo 日志上该行的快照数据,具体如下:
基于多版本并发控制和一致性非锁定读,可以避免获取锁的等待,从而提高并发访问下的性能。 2. 一致性锁定度 一致性锁定读则允许用户按照自己的需求在进行 SELECT 操作时手动加锁,通常有以下两种方式:
InnoDB 存储引擎支持以下三种锁的算法: Record Lock:行锁,用于锁定单个行记录。示例如下: -- 利用行锁可以防止其他事务更新或删除该行SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;复制代码 Gap Lock:间隙锁,锁定一个范围,但不包括记录本身,主要用于解决幻读问题,示例如下:
Next-Key Lock:等价于 行锁+间隙锁,既锁定范围,也锁定记录本身。可以用于解决幻读中的 ”当前读“ 的问题。 四、事务4.1 ACID 定义InnoDB 存储引擎完全支持 ACID 模型: 1. 原子性(Atomicity) 事务是不可分割的最小工作单元,事务的所有操作要么全部提交成功,要么全部失败回滚,不存在部分成功的情况。 2. 一致性(Consistency) 数据库在事务执行前后都保持一致性状态,数据库的完整性没有被破坏。 3. 隔离性(Isolation) 允许多个并发事务同时对数据进行操作,但一个事务所做的修改在最终提交以前,对其它事务是不可见的。 4. 持久性(Durability) 一旦事务提交,则其所做的修改将会永远保存到数据库中。即使宕机等故障,也不会丢失。 4.2 事务的实现数据库隔离性由上一部分介绍的锁来实现,而原子性、一致性、持久性都由 undo log 和 redo log 来实现。
在并发环境下,数据的更改通常会产生下面四种问题: 1.丢失更新 一个事务的更新操作被另外一个事务的更新操作锁覆盖,从而导致数据不一致: 2. 脏读 在不同的事务下,一个事务读取到其他事务未提交的数据: 3. 不可重复读 在同一个事务的两次读取之间,由于其他事务对数据进行了修改,导致对同一条数据两次读到的结果不一致: 4.幻读 在同一个事务的两次读取之间,由于其他事务对数据进行了修改,导致第二次读取到第一次不存在数据,或第一次原本存在的数据,第二次却读取不到,就好像之前的读取是 “幻觉” 一样: 4.4 隔离级别 想要解决以上问题,可以通过设置隔离级别来实现:InnoDB 支持以下四个等级的隔离级别,默认隔离级别为可重复读:
在每个级别下,并发问题是否可能出现的情况如下: 隔离级别 脏读 不可重复读 幻读 读未提交(READ UNCOMMITTED) 可能出现 可能 可能 读已提交(READ COMMITTED) 不可能出现 可能 可能 可重复读(REPEATABLE READ) 不可能 不可能 可能 串行化(SERIALIZABLE) 不可能 不可能 不可能 就数据库层面而言,当前任何隔离级别下都不会发生丢失更新的问题,以 InnoDB 存储引擎为例,如果你想要更改表中某行数据,该行数据上必然会加上 X 锁,而对应的表上则会加上 IX 锁,其他任何事务必须等待获取该锁才能进行修改操作。 五、数据库设计范式数据库设计当中常用的三范式如下: 第一范式:属性不可分要求表中的每一列都是不可再细分的原子项。这是最低的范式要求,通常都能够被满足。 第二范式:属性完全依赖于主键要求非主键列必须完全依赖于主键列,而不能存在部分依赖。示例如下: mechanism_id (组织机构代码) employee_id (雇员编号) ename (雇员名称) mname (机构名称) 28193182 10001 heibaiying XXXX公司 以上是一张全市在职人员统计表,主键为:机构编码 + 雇员编号。表中的雇员名称完全依赖于此联合主键,但机构名称却只依赖于机构编码,这就是部分依赖,因此违背了第二范式。此时常用的解决方式是建立一张组织机构与组织名称的字典表。 第三范式:避免传递依赖非主键列不能依赖于其他非主键列,如果其他非主键列又依赖于主键列,此时就出现了传递依赖。示例如下: employee_id (雇员编号) ename (雇员名称) dept_no (部门编号) dname(部门名称) 10001 heibaiying 06 开发部 以上是一张雇员表,雇员名称和所属的部门编号都依赖于主键 employee_id ,但部门名称却依赖于部门编号,此时就出现了非主键列依赖于其他非主键列,这就违背的第三范式。此时常用的解决方式是建立一张部门表用于维护部门相关的信息。 反范式设计从上面的例子中我们也可以看出,想要完全遵循三范式设计,可能需要额外增加很多表来进行维护。所以在日常开发中,基于其他因素的综合考量,可能并不会完全遵循范式设计,甚至可能违反范式设计,这就是反范式设计。 |
|
来自: 新用户49643053 > 《待分类》