SQL系列——MySQL

一、理解数据库设计的“三大范式”

三大范式保证了设计表的合理性和避免数据的冗余性，但是实际中还是需要结合业务设计表。

1、第一范式：每个列都不可以再拆分

第一范式保障了每列的每列都是不可再拆分的原子项

比如，设计员工信息表的时候，想要描述员工的家庭地址，最好按省-市-区/县等多个字段，不可直接用“地址”一个字段，因为“地址”这个字段可以再拆分。

2、第二范式：在第一范式的基础上，非主键列必须依赖于主键列，而不能只依赖于主键的一部分

第二范式保证了一张表只能存储一种数据，不可以将多种数据存储在一张表中。

比如下面的订单表，该表的订单变号是主键，但是商品信息等列与订单编号并不相关，所以不满足第二范式，该表需要拆分为多个表：

订单编号	商品编号	商品名称	数量	价格	客户	地址	联系方式
001	1	华为手机	1	100	小王	北京	110
002	2	小米手机	1	100	小张	上海	120

上表不符合第二范式，应该拆分为三个表：
（1）订单信息表

订单编号	客户	地址	联系方式
001	小王	北京	110
002	小张	上海	120
（2）订单详细表
订单编号	商品编号	数量
–	–	–
001	1	1
002	2	1
（3）商品信息表
商品编号	商品名称	商品价格
–	–	–
1	华为手机	100
2	小米手机	100

2、第三范式：在第二范式的基础上，非主键列不能依赖其它列

比如，下表不满足第三范式：

学号	姓名	年龄	性别	学校	学校地址	学校电话
001	小明	13	男	一中	北京	12345

因为上表的学校和学校地址有依赖，不满足第三范式，可拆为两个表：
（1）学生信息表

学号	姓名	年龄	性别	学校
001	小明	13	男	一中

（2）院校信息表
学校 | 学校地址 | 学校电话 |
|–|–|–|–|
| 一中 | 北京 | 12345

二、MySQL常用的数据类型

（1）整数型

分类	名称	说明
整型	tinyInt	8位
整型	smallint	16位
整型	mediumint	24位
整型	int(integer)	32位
整型	bigint	64位
（1）小数型
分类	名称	说明
–	–	–
小数型	float	单精度浮点数
小数型	double	双精度浮点数
小数型	decimal(m,d)	m为总位数，d代表小数位长度
(3) 文本型
分类	名称	说明
–	–	–
文本型	char(M)	M为0~255直接的整数
文本型	varchar(M)	M为0~65535之间的整数
文本型	BLOB	允许长度0~65535字节
文本型	TEXT	允许长度0~65535字节
（4）日期型
分类	名称	说明
–	–	–
日期型	year	YYYY 1901~2155
日期型	time	HH:MM:SS
日期型	date	YYYY-MM-DD
日期型	datetime	YYYY-MM-DD HH:MM:SS
日期型	timestamp	带有时区
（5）char和varchar的区别
char是定长，varchar是可变长度。比如定义一个字段类型为char(10)或varchar(10)，如果用来存储’abcd’，使用char(10)，则10个字节全占用完，'abcd’后面默认加了2个空格；如果使用varchar(10)来存储，则只占用8个字节，不会产生空格。char长度固定，所以存储速度比varchar快。

三、索引

1、 什么是索引，索引的作用？

索引是一个具有排序功能和快速查找功能的数据结构

2、MySQL中的索引？

mysql默认的存储引擎是InnoDB，它采用B+树作为索引的数据结构。而mysql的MyISAM存储引擎支持多种数据结构类型的索引，比如B+树、R树等。本文主要学习InnoDB引擎支持的B+树。

3、B+树的特征？

1、支持多叉，有效降低了树的高度。
2、数据都存储在叶子节点，非叶子节点不存储数据，可以多存储索引个数，降低树的高度，减少io的次数。
2、叶子节点的数据从左到右是有序的，且支持链表结构（mysql使用的是双向链表），方便范围查找。

4、为什么不使用以下数据结构？

（1）为什么不使用顺序链表？
因为在数据量过大的时候，顺序链表就变得很长，查找的时间复杂度过大O(n)。
（2）为什么不使用排序二叉树？
跟顺序链表存在的问题一致，排序二叉树存在极端的情况（子节点都大于父节点）就变成了顺序链表，造成时间复杂度过大O(n)。
(3) 为什么不使用平衡二叉树？
平衡二叉树有AVL树，红黑树等。平衡二叉树可以很好的解决排序二叉树存在的极端情况，但是由于平衡二叉树只支持二叉，在数据量过大的时候会造成数的高度变高，这样遍历的深度就过大，IO操作增多，遍历查找的时候也比较慢。
（4）为什么不使用B树？
B树支持排序，平衡二叉树的功能，还支持多叉的特性。能很好的解决树的高度过大的问题，但是在范围查找的时候，B树还是要遍历获取所有范围内的值。
（5）B+树解决B树范围查找存在的问题！
B+树具有支持排序，平衡二叉树的功能，也支持B树的多叉。B+树是怎样优化范围查找的呢，B+将所有的叶子节点都连接在一起，形成了链式结构。这样做的好处就在于范围查找时，只需要找到当前节点，如果是小于当前节点，就直接取当前节点所在链表的左边的所有值，大于就取右边的所有值。

MySQL中InnoDB支持的B+树图

5、MySQL中索引的分类

（1）、主键索引（聚簇索引、一级索引）

一般我们在创建表的时候会指定一个字段作为主键，这个字段一定不为null且值唯一。如果你没有指定，mysql会自动选取第一个不为null的唯一索引作为主键字段，如果没有这样的字段，则会使用行号作为主键索引，可以使用select _rowid from table; 查询。

（2）、非主键索引（非聚簇索引）

聚簇索引和非聚簇索引的最大区别就是，聚簇索引的叶子节点存储了数据行，非聚簇索引的叶子节点存储的是对应的聚簇索引值。

（3）、回表和索引覆盖

回表是指在使用非聚簇的时候，由于非聚簇的数据结构是叶子节点存储的是对应的主键索引，所以先查到的是主键索引，再使用主键索引去查询行数据。
索引覆盖，在理解了回表后，有一种特殊的操作不需要回表，当我们用非聚簇索引查询的时候，如果查询的就只有非聚簇索引的值，那么此时是不需要回表的。比如，当student表的主键索引是id，非主键索引是name，那么select id,name from student;是不需要回表的，这就是索引覆盖。

（4）、索引的设计原则

• where后面出现的列或者连接子句中指定的列
• 索引字段的区分度越大越好，比如“性别”字段不适合建索引，因为区分度较低，只有男，女，其它
• 最好使用短索引，对于较长的字段建立索引时应该指定一个较短的前缀长度，因为较短的索引涉及的磁盘IO较少，且缓存的时候可以缓存更多的索引，查询比较速度也会更快。
• 尽量要考虑到最左前缀原则

（5）、建索引语句

create index index_name on table_name (column_list);
或者 alter table table_name add index index_name (column_list);
删除索引：alter table table_name drop key index_name;

四、事务

事务是访问数据库的一个基本单位

1、 ACID特性

原子性：一个事务是一个整体，要么提交执行成功，要么提交执行失败。
一致性：事务执行的前后要保证一致性，比如转账操作，转账前后转入转出方的总和不变
隔离性：多个事务之间是相互隔离的，事务的开启和提交是不能相互影响的
持久性：事务一旦提交是具有持久性的，提交就写入到了磁盘中

2、事务之间存在的问题（脏、幻、不可重），并发一致性问题

脏读： 事务A修改了数据，但还没有提交，此时事务B读取到事务A更新后的数据，然后事务A回滚了，事务B读取到的数据就是脏数据了。
不可重复读： 是指在事务A中，多次读取数据时，读取到了事务B更改并提交了的数据，这时事务A多次读取的数据就不一致了。
幻读： 幻读，并不是说事务中多次读取获取的结果集不同，幻读更重要的是某次的 select 操作得到的结果集所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 记录不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，如同产生了幻觉

不可重复读和幻读之间的区别是，一个是修改了数据，一个是增加或者删除了数据。

3、事务的隔离级别

读未提交： 指事务A可以读取到事务B未提交的数据，此时容易产生脏读现象。
读已提交： 指事务A只能读取到事务B已经提交的数据，此隔离级别可以解决脏读问题，但是产生了不可重复读的问题。
可重复读： 指事务A多次读取数据，都是与第一次读取到的保持一致，次隔离级别解决了不可重复读的问题，但是没有解决幻读问题。（MySQL的默认隔离级别）
串行化： 是指多个事务执行的时候，串行化的去执行，可以解决脏、幻、不可重全部问题。需要加锁实现。

4、隔离级别是如何实现的？

事务的隔离级别是依靠**MVCC和锁机制**实现的，读已提交和可重复读是依靠MVCC实现的，串行化是依靠锁机制实现的。

（1）MVCC（多版本控制并发）

MVCC的作用是在不加锁的情况下，根据事务的版本号来对数据进行快照读。它可以实现读已提交和可重复读两个隔离级别，不能解决幻读。

• 事务版本号：是一个自增的ID，每开启一个事务，事务的版本号就会递增。

举例：

• select：当前事务所要查询的数据行快照的版本号必须小于当期事务的版本号，这样做的目的是保证当前事务查询的数据要么是在当前事务之前就已经存在的，要么是当前事务中修改过的。

（1）数据库的锁

什么是数据库锁，是当数据库有并发事务的时候，保证数据访问顺序的机制称为锁机制。

数据库锁与隔离级别的关系

• 读未提交：无需加锁
• 读已提交：读取时加S锁，读取完释放锁
• 可重复读：事务开启时加S锁，事务结束时释放锁
• 串行化：根据where条件锁定一定的范围

从锁的类别上分为共享锁和排他锁

• 共享锁：共享锁又称读锁，简称S锁，一个事务对数据加了S锁，那么这个事务只能读取操作不能更新操作；其他事务也只能对该数据加S锁，不能加X锁。 应用举例：select id,name from student where id=1 lock in share mode;
• 排他锁：排它锁又称写锁，简称X锁，一个事务对数据加了X锁，那么这个事务可以对数据进行读写操作，但其他事务既不能对该数据加S锁，也不能加X锁。 应用举例：select id,name from student where id=1 for update;

从锁的粒度上分为行锁，表锁，页面锁

InnoDB默认采用行级锁
行锁模式

• 意向共享锁：简称IS锁，一个事务想要打算给数据行加共享锁前必须先获取该表的IS锁。
• 意向排它锁：简称IX锁，一个事务打算给数据行加排它锁前必须先获得该表的IX锁。

有了意向锁，就不需要遍历每一行是否加了锁（效率低），只需要检查是否有其他事物对这个表加了X/IX/S/IS锁即可。

InnoDB行锁主要分为三种情况：

• Record Lock：对索引项加锁。
• Grap Lock: 对索引之间的“间隙”、第一条记录前的“间隙”或是最后一条记录后的间隙加锁。
• Next-key Lock：前两种放入组合，对记录及前面的间隙加锁。

InnoDB行锁的特性：行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录进行加锁。如果不通过索引条件检索数据，那么InnoDB将对所有的记录进行加锁，实际效果和表锁一样。

乐观锁和悲观锁

**乐观锁：**认为数据的更新在大多数时候是不会产生冲突的，所以数据库只在更新操作时对数据检测冲突，存在冲突则更新失败。一般通过版本和号和CAS算法实现。
**悲观锁：**认为每次拿数据的时候都会被别人修改，所以每次在拿数据的时候都会上锁，通过for update实现。

什么是死锁？如何避免？

死锁是指两个或两个以上的线程在执行的过程中，由于竞争资源造或者彼此通信成的一种阻塞现象。

• 如果多个线程并发操作多个表，尽量以相同的顺序访问表。
• 进行使用索引访问数据，使得加锁更加准确，从而减少锁冲突。
• 合理调整事务的大小，小事务发生锁冲突的概率更低。

五、MySql是如何保证ACID?

六、MySql性能优化

1、sql语句优化
（1）优化原则
（2）优化手段
2、分库分表
（1）分表
（2）按cust_id分片做分库
3、主从架构