文章目录
MySQL
数据库范式
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:
- 第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式
(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。
第一范式(1NF):
第一范式就是无重复的域,即在关系模型中,对域添加的一个规范要求,所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。
第二范式(2NF):
第二范式就是在第一范式的基础上属性完全依赖于主键,即要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组,作为实体的唯一标识。
第三范式(3NF):
第三范式就是在 2NF 基础上消除传递依赖,即非主属性不依赖于其它非主属性。
巴斯-科德范式(BCNF):
巴斯-科德范式就是在3NF基础上消除对主码子集的依赖,即主属性对于码的部分函数依赖与传递函数依赖。
总结:
- 1NF:字段不可分;
- 2NF:有主键,非主键字段依赖主键;
- 3NF:非主键字段不能相互依赖;
存储引擎
mysql可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。每种存储引擎提供的功能不同。
常见的两种存储引擎:MyISAM、InnoDB
MyISAM
MySQL 5.5版之前的默认数据库引擎。
首先介绍表锁和行锁的概念:
表级锁: 每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;
行级锁: 每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
MyISAM主要特点:
- 使用表锁,不支持行锁
- 不支持事务、不支持外键、不支持崩溃后的安全恢复
- 支持全文索引、支持延迟更新索引、支持数据压缩
InnoDB
MySQL 5.5版之后的默认数据库引擎。
主要特点:
- 使用行锁
- 支持事务、支持外键、支持崩溃后的安全恢复
- 不支持全文索引
区别和总结
区别:
- count(*): 由于在缓存时,InnoDB 缓存的是索引+数据块。 而 MyISAM 只缓存索引,且有专门的表记录行数等信息,所以快。
- 事务和恢复: MyISAM 每次查询都需要具有原子性,所以比 InnoDB 快,但不提供事务支持。 而 InnoDB 则具有支持事务、外键的高级数据库功能,具有事务回滚的恢复功能。
- 外键: MySIAM不支持、InnoDB支持。
总结:
MySAM适合读密集的表,适合数据量大且不需要支持事务时可以选择。
InnoDB适合写密集的表,适合需要较高并发读取以及事务的支持时可以选择。
字符集及校对规则
字符集指的是一种从二进制编码到某类字符符号的映射。常见的有ASCII字符集、UTF8字符集、GBK字符集等等。
校对规则指某种字符集下的排序规则。Mysql中每一种字符集都会对应一系列的校对规则。
MySQL 中的字符集
对MySQL进行操作期间(建库建表,增删改查),存在字符集之间的转换:
character_set_client -> character_set_connection -> 内部操作字符集
而内部操作字符集的确认是以类似继承的方法指定,其过程如下:
- 使用每个数据字段的CHARACTER SET设定值;
- 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值;
- 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
- 若上述值不存在,则使用character_set_server设定值;
校对规则
校对规则(collation):是在字符集内用于字符比较和排序的一套规则,比如有的规则区分大小写,有的则无视。
查询数据库支持的校对规则:
show collation;
建表时指定校对规则:
create table t2(id int,name varchar(20)) character set=gbk collate=gbk_bin;
在查询时,会体现校验规则,如查询 “a”,则结果集中只有 a,而没有 A。
事务
事务的概念来自于两个独立的需求:并发数据库访问,系统错误恢复。
一个事务是可以被看作一个单元的一系列SQL语句的集合。
事务的特性
事务的特性,ACID:
- 原子性: 务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用。
- 一致性: 事务将数据库从一种一致状态转变为下一种一致状态。也就是说,事务在完成时,必须使所有的数据都保持一致状态。
- 隔离性: 并发访问数据库时,一个用户的事物不被其他事务所干扰,各并发事务之间数据库是独立的。
- 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库 发生故障也不应该对其有任何影响。
事务的隔离级别
数据库多个事务并发操作下会产生的问题:
- 脏读: 一个事务读取了另一个事务未提交的修改。
- 不可重复度: 一个事务对同一行数据重复读取两次,但是却得到了不同的结果。
- 幻读: 事务在操作过程中进行两次查询,第二次查询的结果包含了第一次查询中未出现的数据。
- 丢失修改,分两种情况:
(1)当两个事务更新相同的数据源,如果第一个事务被提交,第二个却被撤销,那么连同第一个事务做的更新也被撤销。
(2)有两个并发事务同时读取同一行数据,然后其中一个对它进行修改提交,而另一个也进行了修改提交。这就会造成第一次写操作失效。
为了达到上述事务特性,数据库定义了几种不同的事务隔离级别:
- 未提交读(READ_UNCOMMITTED):允许读取尚未提交的数据变更。会导致脏读、幻读或不可重复读。
- 提交读(READ_COMMITTED):允许读取并发事务已经提交的数据。可以阻止脏读,但是幻读或不可重复读仍有可能发生。
- 可重复读(REPEATABLE_READ):对同一字段的多次读取结果都是一致的。可以阻止脏读和不可重复读,但幻读仍有可能发生。
- 串行(SERIALIZABLE):完全服从ACID的隔离级别。可以防止脏读、不可重复读以及幻读。
注: Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别。
隔离级别的实现
事务隔离机制的实现基于锁机制和并发调度。
数据库锁的分类
两种数据库存储引擎使用的粒度锁:
- 表级锁:其锁定粒度最大,触发锁冲突的概率最高,并发度最低。
- 行级锁:其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
而上面两种粒度锁又可以进一步细分为:
- 共享锁(S锁):锁定共享资源,不会阻止其他用户读,但是阻止其他的用户写和修改。
- 排他锁(X锁):若事务T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T释放A上的锁。
索引
索引: 是帮助MySQL高效获取数据的数据结构。
数据页
MySQL 中基本的存储结构是数据页:
几个关键的部分:
- File Header:里面有两个指针,分别指向前一个数据页和后一个数据页。
- User Records:存储我们添加的记录,每个记录都有一个向下的指针。
- Page Directory:根据当前页的记录生成页目录。
由此可知:
- 各个数据页可以组成一个双向链表。
- 每个数据页中的记录又可以组成一个单向链表。
- 主键查询时只需在页目录中使用二分查找,而其他列作为搜索条件时,只能从最小记录开始遍历。
所以我们正常情况下的非主键查询过程如下:
- 遍历双向链表,定位到记录所在的数据页。
- 遍历所在页的单链表,找到相应的记录
在数据量很大的情况下,以上过程会相当慢
B+树索引
Mysql 数据库使用 B+树索引提高检索速度。
首先介绍一下 B-Tree 和 B+Tree:
B-Tree: https://www.cnblogs.com/dongguacai/p/7239599.html
m阶B-Tree满足以下条件:
- 每个节点最多拥有m个子树
- 根节点至少有2个子树
- 分支节点至少拥有m/2颗子树(除根节点和叶子节点外都是分支节点)
- 所有叶子节点都在同一层、每个节点最多可以有m-1个key,并且以升序排列
B+Tree: https://www.cnblogs.com/dongguacai/p/7241860.html
m阶B+Tree满足以下条件:
- 有m个子树的节点包含有m个元素
- 根节点和分支节点中不保存数据,只用于索引,所有数据都保存在叶子节点中
- 所有分支节点和根节点都同时存在于子节点中,在子节点元素中是最大或者最小的元素。
- 叶子节点会包含所有的关键字,以及指向数据记录的指针,并且叶子节点本身是根据关键字的大小从小到大顺序链接
为什么使用B树或B+树?与二叉查找树的区别
数据库索引是存放在磁盘上的,当数据量非常大时,我们不可能将所有索引一次加载到内存当中,只能逐一加载每个磁盘页,这里的磁盘页就对应索引树的节点。而加载一次磁盘页就是一次磁盘IO,二叉查找树的磁盘IO次数会比B树多(由树高度影响)。
Mysql内部使用 B+树:
索引提高了检索速度,但降低了增删改的速度:因为这些操作会破坏它的原有结构。
哈希索引
哈希索引:
采用哈希算法,把键值换算成不同的哈希值,检索时只需要进行一次哈希算法就可以直接定位到相应的位置,所以速度非常快。
局限性:
- 不支持最左匹配原则
- 没办法利用索引完成排序
- 哈希冲突,在大量重复键值情况下效率低
- 不支持范围查询
聚集和非聚集索引
B+树索引可以分为聚集索引和非聚集索引:
聚集索引:
- 叶子节点存的是整行数据,直接通过这个聚集索引的键值找到某行
- 数据的物理存放顺序与索引顺序是一致的
- 一个表只能有一个聚集索引
非聚集索引:
- 叶子节点存的是字段的值,再通过字段的值找到表的某行记录
MyISAM: 因为缓存的是只有索引,即为使用非聚集索引。
InnoDB: 因为缓存的是索引+数据块,即为使用聚集索引。
最左匹配原则
索引可以是简单的一个列,也乐意是复杂的多个列,多个列被称为联合索引。
如建表时定义联合索引:
CREATE TABLE `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL DEFAULT '',
`password` varchar(20) NOT NULL DEFAULT '',
`usertype` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`userid`),
KEY `a_b_c_index` (`username`,`password`,`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
会使用索引的情况:
select * from user where username = '1' and password = '1';
select * from user where password = '1' and username = '1';//顺序没有影响
不会使用索引的情况:
select * from user where password = '1';
mysql从索引最左开始匹配条件,一直到遇到范围查询(>、<、between、like)就停止匹配。且不用考虑=,in等顺序的情况,mysql会自动优化为定义的索引的顺序。
大表优化
当MySQL单表记录数过大时,数据库的CRUD性能就会明显下降,常见的优化措施如下:
限定数据范围: 禁止不带任何限制数据范围条件的查询语句。
读写分离: 主库负责写,从库负责读。
垂直分区: 指数据表列的拆分,把一张列比较多的表拆分为多张表。如用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表。
优缺点:可以使行数据变小,但主键会出现冗余且会让事务变得更加复杂。
水平分区: 保持数据表结构不变,通过某种策略存储数据分片,这样就可以支撑非常大的数据量。分表仅仅是解决了单一表数据过大的问题,但由于表的数据还是在同一台机器上,其实对于提升MySQL并发能力没有什么意义,所以 水平拆分最好分库。
优缺点:支持非常大的数据录存储,但也会使分片事务复杂。