数据库三大范式

学生信息表

学号	姓名	班级	学生宿舍	成绩	课程编号	课程名称
1	小明	1	广东省深圳市福田区	22	3	语文
2	小明	1	广东省深圳市福田区	67	1	英语
3	小红	2	广东省深圳市福田区	99	2	数学

第一范式

每一个字段都具备原子性，不可再区分。
例子中地址几不符合原子性，应该将地址在划分

第二范式

非码属性需要完全依赖候选码

分析依赖关系

推出候选码为学号，课程编号。

依赖关系
学号，课程编号->姓名,班级,学生宿舍，课程名称，成绩
课程编号->课程名
学号->姓名，班级，学生宿舍班级->学生宿舍

由此可知非主属性并不完全依赖于候选码。此时应该将课程编号与学号分为两张表。
学生信息表

| 学号 | 姓名 | 班级 | 学生宿舍 | | -- | -- | -- | -- | -- | | 1 |小明 |1 | 广东省深圳市福田区 | | 2 |小明 |1 | 广东省深圳市福田区 | | 3 |小红 |2 | 广东省深圳市福田区 |

成绩表

学号	成绩	课程编号
1	22	3
2	67	1
3	99	2

课程表

| 课程编号 | 课程名称 | | -- | -- | -- | | 1 | 语文 | | 2 | 英语 | | 3 | 数学 |

第三范式

在第二范式的基础上，消除传递依赖。

举个例子

| 学号 | 姓名 | 班级 | 学生宿舍楼 | | -- | -- | -- | -- | -- | | 1 |小明 |1 | 广东省深圳市福田区 | | 2 |小明 |1 | 广东省深圳市福田区 | | 3 |小红 |2 | 广东省深圳市福田区 |

依赖关系 学号->姓名，(班级)->学生宿舍楼
此时存在传递依赖，需要消除

学生表

| 学号 | 姓名 | 班级 | | -- | -- | -- | -- | | 1 |小明 |1 | | 2 |小明 |1 | | 3 |小红 |2 |

班级表

| 班级 | 学生宿舍楼 | | -- | -- | -- | |1 | 广东省深圳市福田区 | |1 | 广东省深圳市福田区 | |2 | 广东省深圳市福田区 |

触发器(trigger)

什么是触发器？

触发器是与表有关的数据库对象，当表满足定义的条件，则执行触发器中定义的语句集合

触发器的特性

begin、end体
什么条件触发
什么时候触发
触发频率
触发器定义在表上

尽量少使用触发器

注意begin-end期间的语句执行效率一定要高，触发器针对的是每一行，对增删改频率高的表上最好不要使用触发器

创建触发器

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。

tigger_event详解：

　　①INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；

　　②UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；

　　③DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

trigger_order是MySQL5.7之后的一个功能，用于定义多个触发器，使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。

创建只有一个执行语句的触发器

CREATE TRIGGER trig AFTER INSERT
ON work FOR EACH ROW
INSERT INTO name VALUES('cctv');

创建由多个执行语句的触发器

CREATE TRIGGER trig2 BEFORE DELETE
ON work FOR EACH ROW
BEGIN
INSERT INTO name VALUES('cctv');
INSERT INTO sex VALUES('boy');
END

NEW与OLD

Mysql定义NEW和OLD，来表示触发器中发生变化的内容

在INSERT中，NEW表示将要或者已经插入的数据
在UPDATE中，OLD表示将要或者已经被修改的原数据，NEW表示将要或者已经修改的新数据
在DELETE中，OLD表示将要或者已经被删除的原数据

注意OLD为只读，而NEW可以在触发器中使用SET赋值

示例

IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;

查看触发器

SHOW TRIGGERS

显示所有触发器信息

select * from school.triggers 
where trigger_name='upd_check';

显示某表中某个触发器的信息

删除触发器

DROP TRIGGER school.triggerName

mysql的逻辑架构

总体概述

客户端访问mysql服务

首先访问连接池
Caches&Buffers缓存、缓冲访问缓存，如果查找到需要的信息便可提高查询效率缓存：读信息缓冲：写信息

想要命中缓存，必须sql完全相同，因为使用的是键值对存储的方式，关键字为sql语句

SQL Interface接口
Parser解析器
Optimizer优化器
不改变结果的情况下，调整sql语句的顺序决定执行sql的执行计划
Pluggable Storage Engines 存储引擎真正去处理数据
写入部分内容在缓存中
返回结果给客户端

利用show profile命令查看sql的执行周期

1. mac下配置my.cnf文件

在文件夹/etc中创建my.cnf文件，注意创建的过程中可能会因为权限不够无法创建，使用sudo即可

2. 配置参数

在my.cnf文件中配置以下参数

query_cache_type=1;
query_cache_size=6000000;

3. 进入mysql中

输入set profiling=1
show profiles即可
也可更加详细 show profile cpu,block io for query (查询编号)，此时可以看到执行过程

mysql存储引擎

SHOW ENGINES命令

查看当前mysql提供的存储引擎

各个引擎的介绍

Archive引擎

只支持INSERT、SELECT操作，在Mysql5.1之前不支持索引。
适合日志和数据采集类应用
比MyISAM和InnoDB表要小很多

Blackhole引擎

没有实现任何存储机制，会丢弃所有插入的数据。服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。

CSV引擎

将普通的CSV文件作为mysql的表处理，但是不支持索引。
可以作为一种数据交换机制
存储的数据可以直接在操作系统里使用文本编辑器或者表格读取
CSV文件，列与列之间使用，分隔。行与行之间使用回车分隔

Memory引擎

将数据存储在内存中，并且这些数据不会被修改，重启后丢失也没关系

Federated引擎

场景：两台不同的机器，需要关联他们各自数据库中各自的某张表
在一台机中使用该引擎，创建一张和另一张表名相同的表，不存储数据，存储访问另一张表的信息
效率底下，只能进行简单的直接关联

最常用的存储引擎MyISAM、InnoDB

	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发操作	行锁，操作时只锁某一行，对其他行没有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	索引和数据都缓存
关注	节省资源、消耗少	并发写、事务、更大

设置外键的问题
1. 每插入一个新的数据都需要重新扫描，数据大时效率底下
2. 初始化数据库的时候，插入表会有先后顺序，表数量多时会很麻烦一般来说使用代码逻辑来限制表与表之间的关系

Join图

//sql实现
全连接
SELECT * FROM A
LEFT JOIN B
ON A.KEY = B.KEY
UNION
SELECT * FROM B
LEFT JOIN A
ON A.KEY = B.KEY
WHERE A.KEY IS NULL
A、B表独有
SELECT * FROM A
LEFT JOIN B
ON A.KEY = B.KEY
WHERE B.KEY IS NULL
UNION
SELECT * FROM B
LEFT JOIN A
ON A.KEY = B.KEY
WHERE A.KEY IS NULL

mysql不支持全连接，使用union连接左右连接即可实现
如果想用union进行连接，需要确保字段数、字段类型相同

UNION和UNION ALL的区别：后者不去重
如果确定上下结果不重复时，使用UNION ALL，因为去重需要时间

sql语句优化

查询变慢的原因

数据过多 -- 分库分表
关联太多的表 -- SQL优化
没有充分利用索引 -- 建立索引优化效果最明显
mysql创建主键时，会自动创建索引（主键索引）
服务器调优以及各个参数设置 -- 调整my.cnf

索引

何为索引

定义

帮助mysql高效获取数据的数据结构。

理解

数据库除了存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据。一般来说索引本身很大，不可能存储在内存中，往往以文件的形式存储在磁盘上。

优势

类似图书馆简历数目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势

虽然索引大大提高了查询速度快，但是会降低更新表的速度，如INSERT、UPDATE、DELETE操作。Mysql不仅要保存数据，还要保存一下索引文件每次更新添加索引列的字段，调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

mysql索引结构

BTree索引

结点内容

关键字
指向下一个子树的指针
指向记录的指针

时间复杂度

B+Tree索引

结点内容

关键字
指向下一个子树的指针
叶子结点有指向记录的指针，所以每次查询都需要查到叶子结点
叶子结点有指向下一个叶子结点的指针

聚族索引与非聚族索引

聚族索引

一种数据存储方式，"聚族"表示数据行和相邻的键值聚族的存储在一起，如上图左侧部分，数据行在磁盘中的排序与索引的排序一致

聚族索引的好处

查找一定范围的数据时，由于数据存储的位置都紧密相连，因此发生的IO次数将大大减少

聚族索引的限制

mysql只有innodb数据引擎支持聚族索引，而Myisam不支持
因为数据物理存储排序方式只有一种，所以每个mysql表只能有一个聚族索引，一般来说就是主键索引
为了充分利用聚族索引的特性，因此innodb表尽量使用有序的顺序id，而尽量不使用无序id

为何mysql选择了B+树

在内存有限的情况下，因为B+树非叶子结点占用空间小，每次IO读取的节点数更多，因此在基数大的情况下B+树的IO次数明显要比B树的IO次数少。

B树与B+树之间的区别

B树关键字和记录指针是存放在一起的，而B+树只有叶子结点才存放记录指针。
B树查找的关键字越接近根节点查找速度越快，只需要找到关键字即可查找到记录的存在。而B+树查找时间基本相同，每次都需要从根节点查找到叶子结点。从此看好似B树性能要比B+树好，但是在实际的操作中，B+树叶子结点没有存放记录指针，因此每个结点容纳的元素比B树多，发生IO的次数可以减少，虽然查找的次数变多，但是每次发生一次IO的时间就相当于多次在内存中进行比较操作的时间。
B+树叶子结点使用指针连接在一起，方便遍历

索引分类

基本语法

查看：SHOW INDEX FROM tb
创建：CREATE INDEX ON tb
删除：DROP INDEX [indexName] ON tb

单值索引

一个索引包含单个列，一张表可以有多个单值索引

语法

单独创建 CREATE INDEX inx_name ON tb(cl)
随表创建

CREATE TABLE tb(
	XXXXXX
	PRIMARY KEY(XX)
    KEY(ZZ)
)

唯一索引

索引列的值必须唯一

语法

单独创建 CREATE UNIQUE INDEX inx_name_union ON tb(cl)
随表创建

CREATE TABLE tb(
	XXXXXX
	PRIMARY KEY(XX)
    UNIQUE(ZZ)
)

主键索引

设定为主键后数据库会自动创建索引，innoDB为聚族索引

语法

单独创建 ALTER TABLE tb ADD PRIMARY KEY (cl)
随表创建

CREATE TABLE tb(
	XXXXXX
	PRIMARY KEY(XX)
)

复合索引

一个索引包含多个列组合索引在上一个列结果的基础上查找下一个字段对应的列，意味着组合索引命中必须要有先后顺序并且不可跳过。

语法

单独创建 CREATE INDEX inx_name_union ON tb(cl1,cl2)
随表创建

CREATE TABLE tb(
	XXXXXX
	PRIMARY KEY(XX)
    KEY(cl1,cl2)
)

哪些情况需要创建索引

主键自动创建唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
- mysql只会选择它认为最好的索引，即使多个字段分别都有单值索引，mysql也只会选择一个单值索引，因此组合索引的性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- ORDER BY的字段也应该建立索引，因为B+树叶子结点已经全部按照数据大小的顺序排序好并且连接在一起了
查询中统计
- COUNT()意味着需要遍历所选字段，B+树叶子结点通过指针全部连接在一起了，基数大的时候查询效率会更高

哪些情况不需要创建索引

表记录太少（几十条几百条）
经常增删改查的表或者字段
WHERE条件用不到的字段不创建索引
过滤性不好的不适合建索引
- 比如性别，总共就男女两种，无法区分记录

性能分析 Explain

使用Explain关键字可以查看执行计划，模拟优化器执行SQL查询语句，从而知道Mysql是如何处理sql语句的，以此来分析sql语句或者是表结构的性能瓶颈

Explain的作用

查看表的读取顺序由ID序号实现
哪些索引可以使用
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被物理查询

如何使用Explain

Explain+sql语句
执行计划包含的信息
建表脚本

字段解释

id

select查询的序号，包含一组数字，表示查询中执行select子句或者操作表的顺序

三种情况
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高
- id有相同的也有不同的，从大到小，由上到下

作用

id每个号码代表一次独立的查询，查询数越少越好

select_type

SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中包含任何负责的子部分，最外层查询则被称为Primary
DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MYSQL会递归执行这些子查询，把结果放在临时表里。
SUBQUERY 在SELECT或者WHERE列中包含了子查询
DEPENDENT SUBQUERY 在SELECT或者WHERE列中包含了子查查询，子查询基于外层（关键字IN）
UNCACHEBLE SUBQUERY @@XXX系统变量，变量的值随时会改变，所以不需要缓存
UNION
UNION RESULT UNION组成后的结果

table

显示该行数据是关于哪张表的

partitions

表示分区表中的命中情况，非分区表该项为null

type

显示查询使用了何种类型最好->最差 system>const>eq_ref>ref>range>index>ALL

system 表中只有一行记录（等于系统表），这是const类型的特例，平时不会出现
const 表示通过索引一次找到，const用于比较primary 可以或者unique索引。因为只匹配一行数据，所以速度很快，如果将主键置于where列表中，Mysql就能将查询转换为一个常量
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键和唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，数据查找和扫描的混合体
！range 检索给定范围的行，一般出现了between、<、>、in等查询
！！index 出现了索引，但是没有使用将索引作为条件进行过滤，一般只是用了覆盖或者排序
！！！all 遍历全表来找到匹配的行
index_merge 查询中使用了多个索引组合，一般出现了or关键字
ref_of_null 对于某个字段需要关联条件也需要null值，查询优化器会选择ref_or_null连接查询
index_subquery 利用索引来关联子查询，不进行全表扫描
unique_subquery 使用唯一索引来关联子查询

possible_keys

显示可能应用在这张表中的索引，一个或者多个
查询涉及到的字段上若存在索引，则该索引被列出，但不一定被实际使用

key

实际使用的索引，如果为空则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠
- 覆盖索引：索引中包含了要查询的数据列，不需要去查询记录

key_len

WHERE后筛选条件命中索引的长度，命中字段越多，查询效率越高

rows

物理扫描的行数

ref

显示索引的哪一列被使用了，越少越好

filtered

存储引擎返回的数据在server层过滤后剩下多少满足查询的记录数量的比例

Extra

额外信息

！！！Using filesort：ORDER BY没有使用索引
！！！Using temporary：GROUP BY没有使用索引
！！！Using join buffer：两个表关联没有使用索引
impossible where：sql逻辑错误
USING index
- 使用了覆盖索引避免访问数据行。
- 如果同时出现了USING WHERE表明索引被用来执行索引键值查找
- 没有出现USING WHERE表示索引知识用于读取数据
- 利用索引进行了排序和分组
USING WHERE：表明WHERE过滤条件使用了索引
select tables optimized away：访问了优化器，在MyISAM中已经存储了所有记录的数量，不需要打开表进行计算

查询优化

批量数据脚本
单表使用索引以及常见索引失效
关联查询优化
子查询优化
排序分组优化
最后使用索引的手段：覆盖索引

单表索引优化

全值匹配

条件有多少，索引有多少。组合索引中，改变字段顺序，优化器会自动调整使得索引可以被使用

最左匹配原则

查询从索引的最左端开始并且不跳过任何索引中的列

不在索引列做任何操作，如计算、函数、类型转换，会导致索引失效

出现范围查询时右侧的列索引失效，在建立索引时需要把范围查找的列放在最右边

使用不等于时（！=或者<>）无法使用索引，会导致全表扫描

IS NOT NULL无法使用索引，IS NULL可以使用

LIKE以通配符开头索引会失效，会变成全表扫描

字符串不加单引号索引失效

一般性建议

单键索引尽量选择针对当前查询过滤性更好的索引
在选择组合索引的时候，按照过滤性好坏的顺序一次从前往后排
在选择组合索引的时候，尽量选择可以能够包含当前查询中的where中更多字段的索引
在选择组合索引的时候，如果某个字段出现范围查询，尽量把该字段放在索引次序的最后
书写sql时尽量避免索引失效的情况

关联查询优化

无法避免驱动表的全表扫描

建议

保证被驱动表的join字段已经被索引
LEFT JOIN时选择小表作为驱动表，大表作为被驱动表
INNER JOIN，mysql自己选择驱动表和被驱动表
子查询尽量不要放在被驱动表，可能使用不到索引
能够直接关联的尽量直接关联，不要进行子查询

子查询优化

尽量不使用NOT IN或者NOT EXISTS使用LEFT JOIN ON XXX WHERE XXX IS NULL代替

排序分组优化

没有过滤条件，索引无效

顺序错，必定排序

方向反，必定排序

索引的选择

mysql会选择效率更高的索引

如果排序列不在索引中，filesort有两种算法

双路排序

Mysql4.1之前使用的事双路排序，

单路排序

FAQ

sql_mode：sql语句检查的原则

在开发测试时，将测试环境中的数据拉下来的同时记得将测试环境中的sql_mode设置成相同