数据库—Mysql相关

SQL语言分类

1. DDL(Data Definition Language)：数据定义语言，用来对数据库/数据表创建(定义)，更新，删除
2. DML(Data Manipulation Language)：数据操作语言，用来对数据表中的数据新增，更新，删除
3. DCL(Data Control Language)：数据控制语言，用来对数据库访问权限和安全级别控制
4. DQL(Data Query Language)：数据查询语言，用来查询想要的数据记录

Centos安装msyql

参考资料

https://my.oschina.net/iyinghui/blog/2246986

Mysql设置远程连接

参考资料

https://my.oschina.net/iyinghui/blog/2246986添加链接描述

Mysql性能分析

# 查询sql执行时间
show proriles;

# 查看是否支持profiling
select @@have_profiling;

# 查看是否开启profiling
select @@profiling;

# 查看pro相关参数设置;
show variables like "%pro%";

# 开启profiling
set profiling=1;

# 查看单条sql的执行完整过程
show profile for query query_id;

Mysql函数分类

内置函数

1.字符串函数
2. 算数函数
3. 日期函数
4. 转换函数

自定义函数

​ 。。。

使用函数注意事项

​ 不同数据库软件版本可能支持的函数各有不同，大部分 DBMS 会有自己特定的函数，意味着SQL函数的代码移植性很差

字符串函数

--  LEFT() 返回字符串从左开始n各元素， 同理RIGHT()
SELECT name, LEFT(name, 1) FROM user WHERE id='141992f4b24711eba4690ed0b0dde040';
SELECT LEFT("adb", 2), RIGHT("ABS", 2);

-- LENGTH() 返回字段的长度，一个中文三个字符，一个数值或字母一个字符
SELECT LENGTH(name) FROM user WHERE id='141992f4b24711eba4690ed0b0dde040';

-- CHAR_LENGTH() 返回字段的长度
SELECT id, full_name_cn, CHAR_LENGTH(full_name_cn) FROM user;

-- LOCATE() 返回字串在字符串中第一个出现的位置,从1起始
SELECT LOCATE("dm", name) FROM user WHERE id='141992f4b24711eba4690ed0b0dde040';
SELECT LOCATE("ab", "xaab");

-- UPPER(),LOWER() 字符传大小写转换
SELECT name, UPPER(name), LOWER(name) FROM user WHERE id='141992f4b24711eba4690ed0b0dde040';
SELECT LOWER("ADMIN"), UPPER("admin");

-- LTRIM() 去掉字符串左边空格，同理RTRIM()和TRIM()
SELECT " ADMIN",  LTRIM("    ADMIN"), RTRIM("   ADMIN    "), TRIM("   ADMIN    ");

-- CONCAT() 字符串拼接函数
SELECT CONCAT('hello',',world') AS concat_str;

-- REPLACE() 可以替换布尔、整数类型、字符串
SELECT REPLACE(true, true, 1);
SELECT REPLACE(100, 1, 2);
SELECT REPLACE('www.lvtao.net', 'www', 'http://www');

-- SUBSTRING() 字符串中提取子字符串

-- 从postion位置往后截取字符串，起始值为1，注意postion为0则返回空字符串，postion支持负数(表示从后向前提取)
SUBSTRING(string,position) 或 SUBSTRING(string FROM position)  
SELECT SUBSTRING('fabcd', 2);

-- 指定提取的起始和结束位置
SUBSTRING(string,start, end)
SELECT SUBSTRING('fabcd', 1,7);

时间和日期处理函数

-- CURRENT_DATE() 系统当前日期
SELECT CURRENT_DATE();

-- CURRENT_TIME() 系统当前时间，没有具体日期
SELECT CURRENT_TIME();

-- CURRENT_TIMESTAMP() 系统当前时间，日期+时间
SELECT CURRENT_TIMESTAMP();

-- EXTRACT() 时间提取函数
SELECT EXTRACT(YEAR FROM '2021-06-10 12:11:07');
SELECT EXTRACT(MONTH FROM '2021-06-10 12:11:07');
SELECT EXTRACT(DAY FROM '2021-06-10 12:11:07');
SELECT EXTRACT(HOUR FROM '2021-06-10 12:11:07');
SELECT EXTRACT(MINUTE FROM '2021-06-10 12:11:07');
SELECT EXTRACT(SECOND FROM '2021-06-10 12:11:07');

-- DATE() 返回时间的日期部分,同样YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()返回对应的部分
SELECT DATE('2021-06-10 12:11:07');

-- DATE_SUB() 时间计算
SELECT DATE_SUB(now(), INTERVAL 6 MONTH) AS HALF_YEAR;

数值处理函数

-- 常用的数值函数
ABS()    -- 绝对值
	SELECT ABS(-3);
	
MOD()    -- 取余
	SELECT MOD(100, 3);
	
ROUND()  -- 四舍五入
	SELECT ROUND(0.2356, 2);

转换函数

-- CAST(): AS左边为原始数据，右边为转换后的目标数据类型;DECIMAL(3,2): 第一个数为整数和小数的位数和，第二个数位保留的小数位数
SELECT CAST('13.225' AS DECIMAL(3,2));
SELECT CAST('13.225' AS INT);  -- 会报错

-- COLESCE(): 返回第一个非空数值，不论CALESCE()里面有多少个数;可用户数据库字段为空时，添加缺省值
SELECT COALESCE(null,NULL,1);
SELECT COALESCE(null,1);

聚合函数

常用的聚合函数

1. COUNT()
2. MAX()
3. MIN()
4. AVG()
5. SUM()

1)COUNT(字段)COUNT(*)区别
    COUNT(*)统计满足条件的所有行数,不管字段是否为NULL
    COUNT(字段)统计满足条件的行数，还会过滤调字段的值不为NULL的行
2)AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行
3)MAX和MIN也可以对字符串做统计，英文字母按A-Z顺序排列，值依次递增;汉字按全拼音排列
 SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

COUNT()常配合GROUP BY使用
SELECT COUNT(*), role_main FROM heros GROUP BY role_main;
注意：GROUP BY 多个字段时，其实时对多个字段先联合再分组(把这些字段可能出现的所有的取值情况都进行分组)

HAVING与WHERE过滤的区别
WHERE：用于数据行的过滤
HAVING：用于分组过滤，分组后的过滤如果使用WHERE会报错
-- HAVING语法
SELECT field FORM table GROUP BY field HAVING expression ORDER BY field DESC;
-- HAVING和WHERE同时使用，过滤数据行》分组》对分组进行过滤
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

如何使用条件判断

-- 支持多重判断 WHEN 期望值 THEN 展示结果
SELECT
	id,
	(CASE result WHEN 1 THEN '是'
	WHEN 0 THEN '否'
	ELSE '否' END) AS result
FROM
	log_records 
LIMIT 100;

关于查询缓存

-- mysql8.0后取消查询缓存功能
查询缓存一旦数据表有更新，缓存都将清空
真实使用场景：数据表是静态或很少发生变化时才使用

-- 查询mysql是否开启查询缓存命令
show variables like '%query_cache%'

存储引擎类型

查看存储引擎

查看mysql存储引擎列表:

show engines;

查看mysql当前默认的存储引擎:

show variables like '%storage_engine%';

查看某个表用了什么引擎(在显示结果里参数engine后面):

show create table 表名;

mysql存储引擎类型简介

InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

-- mysql不同的表可以指定不同的存储引擎

SELECT关键字执行顺序

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

执行顺序说明：

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；

2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；

3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

   当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

4. 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2

5. 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。

6. 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。

7. 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。

8. 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。

Mysql过滤条件

比较运算符

-- 常见的比较运算符
不等于： != 或 <>
小于等于： <= 或 !>    (不大于)
大于于等于： >= 或 !<  (不小于)
指定值之间： BETWEEN A AND B

逻辑运算符

-- 常用的逻辑运算符
AND OR NOT(否) IN

-- 逻辑运算符优先级
() > AND > OR

SELECT name, hp_max, mp_max FROM heros WHERE (hp_max+mp_max) > 8000 OR hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC

SELECT name, hp_max, mp_max FROM heros WHERE ((hp_max+mp_max) > 8000 OR hp_max > 6000) AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC

通配符过滤

-- 通配符查询，使用LIKE操作符

匹配任意字符，任意次数 % 
匹配单个字符 _

-- 注意
1.通配符区分大小写
2.不同数据库管理系统，通配符可能不相同
3.通配符需要数据库花更长的时间匹配，比如 LIKE '%' 开头会让索引失效，从而全表扫描

Mysql子查询

子查询分类

1. 关联子查询

   子查询需要执行多次(即采用循环的方式)，先从外部查询开始，每次都传入子查询进行查询

   SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
   

2. 非关联子查询

   如果子查询的结果只执行一次，且子查询的结果作为主查询的判断条件执行，则称为非关联子查询

   SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player);
   

集合比较子查询

1. IN：判断是否在集合中
2. ANY：要与比较符一起使用，与只查询返回的任何值做比较
3. ALL：要与比较符一起使用，与只查询返回的所有值做比较
4. SOME：等同于ANY,作用相同

IN和EXIST比较

# IN子查询
SELECT player_id, team_id, player_name FROM player WHERE player_id IN (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

# EXISTS子查询
SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

-- A: player B: player_score
结论：
表A比表B大，IN效率更高
表A比表B小，EXISTS效率更高

-- ALL用法
SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)

-- ANY用法
SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)

子查询作为计算字段

SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team

SQL连接

常见的sql标准

sql92、sql99

SQL92标准

1. 笛卡尔积(交叉连接)
2. 等值连接
3. 非等值连接
4. 外连接(左外连接，右外连接)
5. 自连接

-- 笛卡尔积连接 >> 第一个对象来自x, 第二对象来自Y的所有可能
SELECT  * FROM player, team;  -- 结果player和team表记录相乘

-- 多表连接，如果使用等号连接就是等值连接，如果不是用等号连接(如between and)就是非等值连接
-- 等值连接
SELECT player_id, a.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id;

-- 非等值连接
SELECT p.player_name, p.height, h.height_level FROM player AS p, height_grades AS h WHERE p.height BETWEEN h.height_lowest AND h.height_highest

SQL92 中，只有左外连接和右外连接，没有全外连接

SQL92 中采用（+）代表从表所在的位置，左外连接，就是指左边的表是主表，需要显示左边表的全部行，而右侧的表是从表，（+）表示哪个是从表。

SELECT * FROM player, team where player.team_id = team.team_id(+)

-- 等同于sql99中，因此当前使用较多的还是sql99标准
SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id

自连接：可以对多个表进行操作，也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段。

-- 子查询
select * from player where height > (SELECT height FROM player where player_name='布雷克-格里芬');
-- 自连接
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;

SQL99标准

1. 交叉连接
2. 自然连接
3. ON连接
4. USING连接
5. 外连接
6. 自连接

-- 交叉连接(CROSS JOIN),实际上就是sql92中的笛卡尔乘积
SELECT * FROM team CROSS join player;

-- 自然连接(NATURAL JOIN)，等同于等值连接

-- ON连接(JOIN table ON filter)，支持等值连接和非等值连接

-- USING连接(JOIN table USING(field))，field是连接表的同名字段
SELECT * FROM team JOIN player USING(team_id);

-- 外连接
左外连接：LEFT JOIN 或 LEFT OUTER JOIN
右外连接：RIGHT JOIN 或 RIGHT OUTER JOIN
全外连接：FULL JOIN 或 FULL OUTER JOIN

-- 自连接
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;
-- 自连接替代方案(子查询)
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;

-- 表连接注意：
1.连接表的数量控制：表连接相当于嵌套for循环，是非常消耗资源的
2.表连接注意where过滤条件
3.使用自联结而不是子查询
	在许多DBMS的处理过程中，对于自连接的处理速度要比子查询快得多。原因：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件	  判断，因此在大部分DBMS中都对自连接处理进行了优化

Mysql视图

1. 视图原理
2. 视图应用场景
3. 视图CRUD
4. 视图、临时表、存储过程的区别

-- 视图原理
创建视图，其实就是创建一张虚拟表，封装了底层与数据表。可以简化复杂的sql查询，视图编写完成后就可以重复使用，不用关心具体的查询语句

-- 应用场景
1.大型项目或数据表比较复杂时，利用视图完成复杂的连接
2.利用视图对数据格式化(配合CONCAT等场景)

-- 视图CRUD操作
-- 创建视图(查询比格里芬高的所有球员)
CREATE VIEW v_player_height as SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;

-- 根据视图的结果集查询结果
SELECT * FROM v_player_height;

-- 查询数据库中所有视图
show table status where comment='view';

-- 查询视图详情
desc v_player_height;

-- 嵌套视图(利用已有视图创建新的视图)
-- 查询出比格里芬高的身高所有球员的平均值更高的球员，注意：基础视图更新后，可能导致嵌套视图失效
CREATE VIEW v_higher_player_height as SELECT * FROM v_player_height WHERE height > (select avg(height) from v_player_height);

-- 修改视图
ALTER VIEW v_player_height as SELECT CONCAT(b.player_name, "(", b.height, ")") as player_height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;

-- 删除视图
DROP VIEW v_player_height;

-- 视图、临时表、存储过程的区别
视图是虚拟表，本身不存储数据，视图通常用来封装sql查询，不会更新数据表的数据。
临时表是实际存在的表，临时表只在当前连接中存在，关闭连接后，临时表会自动释放
存储过程也是对sql语句的封装，但存储过程更像是封装的函数，可以支持入参和返回结果

Mysql事务

事务特性

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

事务控制

1.START: TRANSACTION 或者 BEGIN，作用是显式开启一个事务。
2.COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。
3.ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务。意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。
4.SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
5.RELEASE SAVEPOINT：删除某个保存点。
6.SET TRANSACTION，设置事务的隔离级别。

事务类型

1. 显示事务
2. 隐式事务

-- 查询事务开启状态
SHOW VARIABLES LIKE 'autocommit'

-- 显示事务：手动提交
-- 隐式事务：自动提交

事务参数

autocommit

-- 事务开启/关闭自动提交
set autocommit =1/0;  

completion_type

1.completion=0，这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要我们使用 START TRANSACTION 或者 BEGIN 来开启
2.completion=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务（隔离级别会在下一节中进行介绍）
3.completion=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接

事务隔离级别

1. 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到
2. 读提交：一个事务提交之后，它做的变更才会被其他事务看到
3. 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见
4. 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

注意：以上事务隔离级别依次递增，也就是控制更严格，同理就牺牲系统的并发性越大

事务隔离解决了哪些问题

1. 脏读：读取到了其他事务未提交的数据
2. 不可重复读：相同的过滤条件，两次没有读到相同的内容，因为其他事务更改了满足过滤条件的数据(查询的结果改变了，重点UPDATE/DELETE)
3. 幻读：事务A根据条件查询到了N条数据，事务B更新或增加满足A过滤条件的M条数据，当事务A再次查询时获取了N+M条数据(结果范围变化,重点INSERT)

事务隔离级别对应的异常解决

备注：√：可读 ×：不可读

隔离界别	脏读	不可重复度	幻读
读未提交(READ UNCOMMITTED)	√	√	√
读已提交(READ COMMITTED)	×	√	√
可重复读(REPEATABLE READ)	×	×	√
串行化(SERIALIZABLE)	×	×	×

事务隔离级别实现原理

1. 读未提交：直接返回记录的最新值
2. 读提交：视图是在每个 SQL 语句开始执行的时候创建的，不同时间，视图的值可能会更新，因此起到隔离的效果
3. 可重复读：在事务启动时创建，整个事务过程中都使用这个视图的数据
4. 串行化：通过加锁来避免并发访问

Mysql游标

应用场景

游标是面向过程的编程方式，可以对结果集合的数据进行不同逻辑处理；在mysql中游标只能用于存储过程和函数

游标的使用

1. 定义游标
2. 打开游标
3. 从游标中获取数据
4. 关闭游标
5. 释放游标

-- 定义
DECLARE cur_hero CURSOR FOR 
	SELECT hp_max FROM heros;

-- 打开
OPEN cursor_name

-- 获取
FETCH cursor_name INTO var_name ...

-- 关闭
CLOSE cursor_name

-- 释放
DEALLOCATE PREPARE

Python操作Mysql

DB API规范：为实现对不同DBMS的统一访问，python需要遵循的规范

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E6wlAadF-1624693449648)(D:\Users\ex-shigl001\AppData\Roaming\Typora\typora-user-images\image-20210623110644074.png)]

python操作DBMS的一般步骤

1. 引入 API 模块；
2. 与数据库建立连接；
3. 执行 SQL 语句；
4. 关闭数据库连接。

ORM框架操作Mysql

了解orm之前先了解下持久化，持久化层在业务逻辑层和数据库层起到了衔接的作用，它可以将内存中的数据模型转化为存储模型，或者将存储模型转化为内存中的数据模型。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Go57k0w-1624693449650)(D:\Users\ex-shigl001\AppData\Roaming\Typora\typora-user-images\orm持久化.png)]

常用的ORM框架

1. django
2. sqlalchemy
3. peewee

# django:一个python的web框架，采用MTV框架模式，Model（模型），View（视图）和 Template（模版），框架中Model部分实现了orm功能
# sqlalchemy:python中比较常用的orm框架，提供sql工具包和orm工具包，如果想同时支持orm和原生sql时sqlalchemyw完全支持，同时sqlalchemy社区比较活跃
# peewee:轻量级orm框架，采用了 Model 类、Field 实例和 Model 实例来与数据库建立映射关系，从而完成面向对象的管理方式。

数据库调优

思考

1. 数据库调优的目的是什么？
2. 数据库调优可以从那几个维度选择？
3. 如果思考和分析数据库调优这件事？

获取数据库调优反馈的途径

1. 用户反馈
2. 日志分析
3. 服务器资源使用监控
4. 数据库内部状况监控

数据库调优的选择维度

1. 选择合适的DBMS
2. 优化表设计
3. 查询优化
   1. 逻辑查询优化：更换sql查询方式
   2. 物理查询优化：使用索引/表连接
4. 使用外部缓存软件(redis，memcached)
5. 库级优化(分库分表)

数据库范式

数据库范式类型

​ 按照范式级别，从低到高分别是：1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BCNF（巴斯 - 科德范式）、4NF（第四范式）和 5NF（第五范式，又叫做完美范式）

数据表中的键

范式的定义会使用到主键和候选键（因为主键和候选键可以唯一标识元组），数据库中的键（Key）由一个或者多个属性组成。我总结了下数据表中常用的几种键和属性的定义：

• 超键：能唯一标识元组的属性集叫做超键。
• 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
• 主键：用户可以从候选键中选择一个作为主键。
• 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。
• 主属性：包含在任一候选键中的属性称为主属性。
• 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性

举例说明：

球员表（player）：球员编号、姓名、身份证号、年龄和球队编号

球队表（team）：球队编号、主教练和球队所在地

超键：包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等
候选键：最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）
主键：我们自己选定，也就是从候选键中选择一个，比如（球员编号）
外键：球员表中的球队编号

常见的3NF

1. 1NF 指的是数据库表中的任何属性都是原子性的，不可再分

2. 2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系

   理解：一张表就是一个独立的对象，也就是一张表只表达一个意思
   

3. 3NF 在满足 2NF 的同时，对任何非主属性都不传递依赖于候选键

   理解：不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况
   

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hQBifGMZ-1624693449651)(D:\Users\ex-shigl001\AppData\Roaming\Typora\typora-user-images\image-20210624202953355.png)]

Mysql索引

使用索引的目的

进行特定条件的快速定位和查找，提升数据库查询效率

索引本质上就是提升DBMS获取数据效率的***数据结构***

索引使用原则

什么情况需要创建索引？

1. 字段值有唯一性的限制，比如用户名

   理解：索引本身可以起到约束作用，比如唯一索引，主键索引
   

2. 数据表较大时，且频繁被WHERE当作过滤条件

3. 经常需要ORDER_BY或GROUP_BY的字段

   理解：索引就是让数据按照某种顺序进行存储或检索
   

4. UPDATE/DELETE的WHERE条件列，一般也需要创建索引

   理解：UPDATE/DELETE也是要先通过WHERE条件查到数据行，在进行UPDATE/DELETE操作
   

5. DISTINCT字段需要创建索引

   理解：DISTINCT字段增加了索引后，字段是按照顺序展示的，所以去重会更快
   

6. 多表JOIN操作，需注意如下

   1. 连接表的数量尽量不要超过 3 张，因为多个join就相当于嵌套循环
   2. 对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤
   3. 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致

什么情况不需要创建索引？

1. WHERE条件(GROUP BY、ORDER BY)不会使用的字段,

2. 表记录比较少的情况

3. 当字段的数据重复度比较高(>10%),不建议使用索引，会增加回表的次数。比如性别字段

   -- 理解：增加索引的目的也是为了提高查询特定条件下的数据,通常是要查询的是少部分(相对于整个数据库表)数据
   
   -- 性别字段要不要增加索引的问题？
   当性别中某个一类的数据行数数量差别非常大时，索引就还是很有意义的
   

什么情况下索引会失效？

判断索引是否失效，使用EXPLAIN关键字(后面sql分析中会介绍)，重点看possible_keys | key的结果

1. 索引字段进行了表达式计算

   EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001
   

2. 对索引使用了函数

   -- 使用了SUBSTRING()
   EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'
   
   -- 替换方案，模糊查找
   SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE 'abc%'
   

3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

   理解：只有or左右的条件只有都创建了索引，mysql才能一起生效
   

4. 使用 LIKE 进行模糊查询的时候，匹配字符串的第一个字符不能是 %

   理解：同理一本字典从目录中查找，也是从首位进行匹配的
   

5. 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效

理解：索引不存储空值，因此在创建索引时，字段最好加上不为空的约束(考虑使用默认值代替空值)

6. 对联合索引在在查询时未遵循最左原则

索引分类

按逻辑功能分类

1. 普通索引：索引列无任何约束
2. 唯一索引：索引列值必须唯一，可以为空
3. 主键索引：索引列值必须唯一，且不为空
4. 全文索引：mysql自带的全文索引只支持英文，通常采用专业的全文搜索引擎ES(ElasticSearch)

按物理实现分类

1. 聚集索引

   理解：按主键排序，存储数据，值是数据行
   

2. 非聚集索引：别名二级索引或辅助索引

   理解：数据库系统为非聚集索引分配单独的存储空间，索引项按顺序存储，索引值是随机存储。系统会进行两次查找，第一次查找索引，第二次根据索引对应的位置找数据行
   

聚集索引与非聚集索引的原理不同，在使用上也有一些区别：

1. 聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。
2. 一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
3. 使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低

按字段个数分类

1. 单一索引：索引列只有一个字段
2. 联合索引：索引列有多个字段

最左匹配原则

联合索引中存在，即按照最左列字段优先进行索引的匹配

-- 如联合索引为(x,y,z)
SELECT * FROM tb WHERE x=xx;  # 索引生效
SELECT * FROM tb WHERE x=xx and y=xx;  # 索引生效
SELECT * FROM tb WHERE x=xx and y=xx and z=xx;  # 索引生效

SELECT * FROM tb WHERE y=xx;  # 索引不生效

索引基本操作

查看表的索引

SHOW INDEX FROM table_name;
# 示例
SHOW INDEX FROM user;

创建索引

# 直接创建索引
CREATE INDEX indexName ON table_name (column_name)
# 示例
CREATE INDEX user_index ON user (name);

# 修改表结构时创建索引
ALTER TABLE table_name ADD INDEX index_name(cloumn_name)
# 示例
ALTER TABLE user ADD INDEX user_index(name);

删除索引

DROP index_name ON table_name;
# 示例
DROP INDEX user_index ON user;

使用索引前后性能对比

执行时间

# 开启慢查询分析sql执行时间
show profiles;

# 查看profiles是否开启
show variables like '%pro%';
profiling ON：表示开启  OFF：表示关闭

# 开启profiling
set profiling=1

sql分析

EXPLAIN 命令
EXPLAIN query_sql;

EXPLAIN 结果字段说明
id：标识符，表示执行顺序

select _type：查询类型
>>>simple：简单的select查询
>>>primary：最外面的查询 或者 主查询，在有子查询的语句中，最外面的select查询就是primary
>>>subquery：子查询
>>>union：union语句的第二个或者说是后面那一个select
>>>union result：union之后的结果
>>>dependent unoin：unoin 中的第二个或随后的 select 查询，依赖于外部查询的结果集
>>>dependent subquery：子查询中的第一个 select 查询，依赖于外部 查询的结果集
>>>derived：衍生表（5.7版本中不存在）

table：输出行所引用的表

partitions：使用的哪个分区，需要结合表分区才可以看到

type：按某种类型来查询，例如按照索引类型查找，按照范围查找
>>>从好到差的连接类型为const、eq_reg、ref、range、indexhe、all

possible_keys：mysql认为可能用到的索引，保存的是索引名称，多个索引用逗号隔开

key：实际用到的索引，保存的是索引名称，多个索引用逗号隔开

key_len：本次查询中，选择的索引长度有多少字节
>>>用到组合索引的时候判断索引是否完全用上

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：显示mysql认为执行查询时扫描的行数

filtered：通过过滤条件之后对比总数的百分比

extra：额外的信息，例如：using file sort ，using where， using join buffer，using index

Mysql锁机制

为什么要有锁?

确保并发情况下数据一致性

锁粒度划分类型

-- 按控制的数据范围粒度来分
行锁
页锁
表锁
说明：粒度越大，发生锁冲突概率越大，数据访问时并发度越低

-- 按数据库管理角度分
共享锁：别名读锁、S锁
共享锁锁定的资源可以被其他用户读取，但不能修改
备注：进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改

排他锁：写锁、独占锁、X锁
排他锁锁定的数据，只允许锁定操作的事务使用，其他事务无法对锁定的数据进行查询或修改

-- 从程序员角度划分
乐观锁：认为同一数据并发情况不会发生，不用每次对数据上锁，即不采用数据库锁机制，通过程序实现
悲观锁：也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性

不同存储引擎和数据库对行锁、页锁、表锁的支持

	行锁	页锁	表锁
Innodb	√		√
MyISAM			√
DBD		√	√
Oracle	√		√
SQLSERVER	√	√	√

乐观锁实现方式

-- 版本号机制
为数据库表新增版本号version字段，再进行数据update/delete操作时，执行UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功，因为version的值发生了改变

-- 时间戳机制
在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突

乐观锁和悲观锁适用场景

1.乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。

2.悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突

共享锁会发生死锁的情况

当多个事务对同一行数据加读锁时，如果其中一方为提交事务，则另一方进行数据更改时就会一直死锁等待