MySQL数据库总结

---

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版之后的默认数据库引擎。

主要特点：

• 使用行锁
• 支持事务、支持外键、支持崩溃后的安全恢复
• 不支持全文索引

---

区别和总结

区别：

1. count(*)： 由于在缓存时，InnoDB 缓存的是索引+数据块。 而 MyISAM 只缓存索引，且有专门的表记录行数等信息，所以快。
2. 事务和恢复： MyISAM 每次查询都需要具有原子性，所以比 InnoDB 快，但不提供事务支持。 而 InnoDB 则具有支持事务、外键的高级数据库功能，具有事务回滚的恢复功能。
3. 外键： 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. 不可重复度： 一个事务对同一行数据重复读取两次，但是却得到了不同的结果。
3. 幻读： 事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据。
4. 丢失修改，分两种情况：
   （1）当两个事务更新相同的数据源，如果第一个事务被提交，第二个却被撤销，那么连同第一个事务做的更新也被撤销。
   （2）有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。

为了达到上述事务特性，数据库定义了几种不同的事务隔离级别：

• 未提交读(READ_UNCOMMITTED)：允许读取尚未提交的数据变更。会导致脏读、幻读或不可重复读。
• 提交读(READ_COMMITTED)：允许读取并发事务已经提交的数据。可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• 可重复读(REPEATABLE_READ)：对同一字段的多次读取结果都是一致的。可以阻止脏读和不可重复读，但幻读仍有可能发生。
• 串行(SERIALIZABLE)：完全服从ACID的隔离级别。可以防止脏读、不可重复读以及幻读。

注： Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别。

---

隔离级别的实现

事务隔离机制的实现基于锁机制和并发调度。

数据库锁的分类
两种数据库存储引擎使用的粒度锁：

1. 表级锁：其锁定粒度最大，触发锁冲突的概率最高，并发度最低。
2. 行级锁：其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

而上面两种粒度锁又可以进一步细分为：

• 共享锁（S锁）：锁定共享资源，不会阻止其他用户读，但是阻止其他的用户写和修改。
• 排他锁（X锁）：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

---

索引

索引： 是帮助MySQL高效获取数据的数据结构。

---

数据页

MySQL 中基本的存储结构是数据页：

几个关键的部分：

• File Header：里面有两个指针，分别指向前一个数据页和后一个数据页。
• User Records：存储我们添加的记录，每个记录都有一个向下的指针。
• Page Directory：根据当前页的记录生成页目录。

由此可知：

1. 各个数据页可以组成一个双向链表。
2. 每个数据页中的记录又可以组成一个单向链表。
3. 主键查询时只需在页目录中使用二分查找，而其他列作为搜索条件时，只能从最小记录开始遍历。

所以我们正常情况下的非主键查询过程如下：

1. 遍历双向链表，定位到记录所在的数据页。
2. 遍历所在页的单链表，找到相应的记录
   在数据量很大的情况下，以上过程会相当慢

---

B+树索引

Mysql 数据库使用 B+树索引提高检索速度。

首先介绍一下 B-Tree 和 B+Tree：
B-Tree： https://www.cnblogs.com/dongguacai/p/7239599.html
m阶B-Tree满足以下条件：

1. 每个节点最多拥有m个子树
2. 根节点至少有2个子树
3. 分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
4. 所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

B+Tree： https://www.cnblogs.com/dongguacai/p/7241860.html
m阶B+Tree满足以下条件：

1. 有m个子树的节点包含有m个元素
2. 根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中
3. 所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小的元素。
4. 叶子节点会包含所有的关键字，以及指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序链接

为什么使用B树或B+树？与二叉查找树的区别
数据库索引是存放在磁盘上的，当数据量非常大时，我们不可能将所有索引一次加载到内存当中，只能逐一加载每个磁盘页，这里的磁盘页就对应索引树的节点。而加载一次磁盘页就是一次磁盘IO，二叉查找树的磁盘IO次数会比B树多（由树高度影响）。

Mysql内部使用 B+树：

索引提高了检索速度，但降低了增删改的速度：因为这些操作会破坏它的原有结构。

---

哈希索引

哈希索引：
采用哈希算法，把键值换算成不同的哈希值，检索时只需要进行一次哈希算法就可以直接定位到相应的位置，所以速度非常快。

局限性：

1. 不支持最左匹配原则
2. 没办法利用索引完成排序
3. 哈希冲突，在大量重复键值情况下效率低
4. 不支持范围查询

---

聚集和非聚集索引

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并发能力没有什么意义，所以 水平拆分最好分库。
优缺点：支持非常大的数据录存储，但也会使分片事务复杂。