MySQL
2021-04-15 19:07:55 352 举报AI智能生成
登录查看完整内容
索引、事务、锁、mvcc、存储引擎、常见SQL书写技巧、窗口函数等
作者其他创作
大纲/内容
date：3字节，日期，格式：2014-09-18
time：3字节，时间，格式：08:42:30
datetime：8字节，日期时间，格式：2014-09-18 08:42:30，范围为1000-01-01 00:00:00/9999-12-31 23:59:59
timestamp：4字节，自动存储记录修改的时间，时间戳，范围为1970-01-01 00:00:00/2038
year：1字节，年份
mysql官方文档日期和时间类型
日期和时间类型
tinyint：1字节，范围（-128 ~ 127）
smallint：2字节，范围（-32768 ~ 32767）
mediumint：3字节，范围（-8388608 ~ 8388607）
int或integer：4字节，范围（-2147483648 ~ 2147483647）
bigint：8字节，范围（+|-9.22 * 10的18次方）
注意：上面定义的都是有符号的
加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了。例如tinyint unsigned的取值范围为0 ~ 255
表示数量的字段建议加上unsigned关键字，例如库存、余额等，如果程序计算库存小于0了，在UPDATE或者是INSERT的时候直接报错，保证库存不超卖
数据范围
mysql官方文档整型
整型（有符号或者无符号）
DECIMAL和NUMERIC 类型的存储精确的数值数据。这些类型用于保持精确精度很重要的情况，例如货币数据
插入123.45678，最后查询得到的结果为99.999。高版本MySQL直接报错
插入123.456，最后查询结果为99.999。高版本MySQL直接报错
插入12.34567，最后查询结果为12.346
在使用浮点型的时候，还是要注意陷阱的，要以插入数据库中的实际结果为准
浮点型
数值类型
char(n)：固定长度，最多255个字符
varchar(n)：可变长度，最多65535个字符
tinytext：可变长度，最多255个字符
text：可变长度，最多65535个字符
mediumtext：可变长度，最多2的24次方-1个字符
longtext：可变长度，最多2的32次方-1个字符
char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候（UTF8）意味着可以插入n个中文，但是实际会占用n * 3个字节
同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n
超过char和varchar的n设置后，直接报错
对于char类型的字段不要设置的太大，如果不确定字段是否有值，建议设置成varchar(255)
注意
mysql官方文档字符串数据类型
字符串数据类型
mysql官方文档数据类型
MySQL常用数据类型
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句
这些语句定义了数据库、表、字段、用户的访问权限和安全级别
一般是由DBA进行设置
创建用户
给用户授权
撤销授权
查看权限
删除用户
DCL（数据库控制语言）
数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
创建数据库
SHOW DATABASES
查询所有数据库
SHOW CREATE DATABASE db_name;
查询数据库建表时的sql
查询数据库
USE db_name;
选择数据库
DROP DATABASE db_name;
删除数据库
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
修改数据库的字符编码和排序方式
SET NAMES UTF8
命令行设置之后操作的编码格式
数据库操作
SQL : CREATE TABLE tb_name (字段名、类型、长度、约束、默认、注释)
PRIMARY KEY
主键
AUTO_INCREMENT
自增
UNIQUE
唯一约束，取值不允许重复，可以为NULL
并不是指只有一行字段为NULL，可以有多个为NULL
唯一
NOT NULL
非空
UNSIGNED
只能用于数值
对于库存或者是余额意义的字段，建议加上该约束，如果程序出现错误新增或者修改后的值为负数，直接报错
非负
FOREIGN KEY
现在很少使用物理外键，一般使用逻辑外键
可以在开发环境或者测试环境加上外键，在正式环境去除外键
外键
DEFAULT
默认
COMMENT
注释
约束
CREATE UNIQUE INDEX index_name(field_name);
创建索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name(field_name);
表字段修改
添加
DROP INDEX index_name ON table_name
删除
唯一索引
ALTER TABLE table_name ADD INDEX index_name(field_name);
CREATE INDEX index_name ON table_name(field_name);
普通索引
ALTER TABLE table_name ADD PRIMARY KEY (field_name)
ALTER TABLE table_name DROP PRIMARY KEY;
DROP INDEX index_name ON table_name
联合索引
表字段索引
创建表
ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';
ALERT TABLE tb_name ADD 添加字段 字段类型 非空约束 默认 注释
字段添加
ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址';
ALERT TABLE tb_name MODIFY 字段名称 新的字段类型 非负 非空 默认 注释
字段类型修改
ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';
ALTER TABLE tb_name CHANGE 旧的字段名 新的字段名 新的类型 约束 默认 注释
字段名称类型修改
DESC tb_name;
字段类型查询
ALTER TABLE tb_name DROP addr;
ALTER TABLE 表名 DROP 删除的字段名
字段删除
表字段的增删改查
ALTER TABLE tb_name RENAME TO new_tb_name;
ALTER TABLE 旧表名 RENAME TO 新表名
表名修改
ALTER TABLE tb_name ENGINE = InnoDB;
ALTER TABLE 表名 ENGINE = 新的引擎名称
引擎修改
表修改
修改表
DROP TABLE tb_name;
删除表
SHOW TABLES;
查询所有表
SHOW CREATE TABLE tb_name;
查询建表时的sql
查询表
数据库表操作
DDL（Data Definition Language）
数据操作语言，用来定义数据库记录：增、删、改表记录
添加单条
添加多条
增
DELETE FROM tb_name WHERE ...
sql
删除时必须加WHERE条件
删
修改时必须加WHERE条件
改
DML（Data Manipulation Language）（重点）
数据查询语言，用来查询记录，查询数据并不会改变原有的数据
SELECT * FROM tb_name
基础的查询
大于、小于、等于、不等于、大于等于、小于等于
SELECT * FROM tb_name WHERE user_id >10;
比较运算符
逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号
SELECT * FROM tb_name WHERE user_id > 10 AND sex = '男'
逻辑运算符
%（百分号）匹配零个或者多个任意字符
_（下划线）匹配一个任意字符
通配符
SELECT * FROM tb_name WHERE username LIKE '张%';查找username开头是张的数据
SELECT * FROM tb_name WHERE username LIKE '%张%';查询username中含有张的数据
SELECT * FROM tb_name WHERE username LIKE '%张';查询username字段的数据以张结尾的
SELECT * FROM tb_name WHERE username LIKE '张_';查询username以张开头后边有一个字符的数据
LIKE模糊查询
IN字段指定多个值查询
field BETWEEN value1 AND value2
字段的值大于等于value1同时小于等于value2
SELECT * FROM user WHERE user_id BETWEEN 2 AND 9;查询user表中user_id大于等于2小于等于9的所有值
BETWEEN AND 区间查询
WHERE子句
COUNT(*)：统计所有行
COUNT(field)：获取符合条件出现的非NULL值的次数
SUM(field)：获取所有符合条件的数据的总和
AVG(field)，取平均值
MAX(field)，取最大值
MIN(field)，取最小值
GROUP_CONCAT(field)，可以将分组的字符串以\
聚合函数
GROUP BY分组查询
对聚合值或者是字段进行过滤
WHERE不能对聚合值进行过滤
HAVING对聚合值进行过滤
ORDER BY field DESC;降序查询
ORDER BY field ASC;升序查询
查询顺序
SELECT * FROM tb_name ORDER BY id DESC; 查询tb_name表中所有数据，按id的降序来查找
索引的顺序和ORDER BY子句的顺序完全一致
索引中所有列的方向（升序、降序）和ORDER BY子句完全一致
当多表连接查询时ORDER BY中的字段必须在关联表中的第一张表中
通过有序索引顺序扫描直接返回有序数据，通过explain分析显示Using Index，不需要额外的排序，操作效率比较高
FileSort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序中进行排序
可以理解为归并排序
如果内存装载不下，就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集
通过对返回数据进行排序，也就是FileSort排序，所有不是通过索引直接返回排序结果的都叫FileSort排序
ORDER BY是否使用索引的严格要求
ORDER BY查询排序
LIMIT后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形
参数
SELECT * FROM tb_name LIMIT 5;查询tb_name表中的所有数据，只要前边的5条数据
LIMIT查询结果截取
JOIN连接查询总共有7种（内连接、左连接（全A）、右连接（全B）、左连接（只A）、右连接（只B）、全外连接、交叉外连接）
内连接：SELECT <select...list> FROM TableA A INNER JOIN TableB B ON A.Key=B.Key;
左连接（全A）：SELECT <select...list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;
右连接（全B）：SELECT <select...list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;
左连接（只A）：SELECT <select…list> FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL;
右连接（只B）：SELECT <select…list> FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;
由于MySQL不支持FULL JOIN这种语法（在orcale可行），所以使用UNION关键字拼接左连接（全A）和右连接（全B）结果并去重来达到效果
SELECT <select...list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;UNIONSELECT <select...list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;
全外连接：SELECT <select…list> FROM TableA a FULL OUTER JOIN TableB b ON A.Key=B.Key;
同理，使用UNION关键字拼接左连接（只A）和右连接（只B）达到效果
SELECT <select…list> FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL;UNIONSELECT <select…list> FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;
交叉外连接
JOIN连接查询
如果是内连接将过滤条件写在ON和WHERE的效果一样
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
JOIN时使用ON和WHERE过滤的区别
BEGIN：开启事务
ROLLBACK：事务回滚
COMMIT：事务提交
关键词
表的存储引擎为InnoDB
必备条件
事务
DQL（Data Query Language）（重点）
基本SQL语句
(8) SELECT (9) DISTINCT (10) <TOP_specification> <select_list> （窗口函数） (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> （表连接条件） (4) WHERE <where_condition> （一级过滤，WHERE后面不能使用别名，不能过滤聚合函数） (5) GROUP BY <group_by_list> （识别SELECT中的别名，这里只能识别简单的SELECT字段的别名，不包含窗口函数的别名，后面的语句中都可以使用） (6) WITH {CUBE | ROLLUP} (对分组后的数据进行汇总，对分组的字段取别名) (7) HAVING <having_condition> （二级过滤，过滤聚合函数的值，以及普通字段）(11) ORDER BY <order_by_list> （对数据集进行排序，可以识别窗口函数的别名）(12) LIMIT OFFSET NUMBER (分页功能)
了解SQL语句的执行顺序对于书写SQL非常重要
WHERE和ON的区别，ON为表连接条件，WHERE进行字段的一级过滤，将过滤条件能够写到ON的尽量写到ON中，减少连接的数据集
WHERE和HAVING的区别，WHERE只是对字段进行简单过滤，HAVING除了能对字段简单过滤以外，还能够对聚合值进行过滤
SQL语句的执行顺序（重点）
ABS()：绝对值
PI()：π值
mysql官方文档数学函数
数学函数
UCASE(str)或UPPER(str)：返回将字符串str中所有字符转变为大写后的结果
TRIM(str)：去除字符串首部和尾部的所有空格
RTRIM(str)：返回字符串str尾部的空格
LTRIM(str)：从字符串str中切掉开头的空格
LENGTH(str)：返回字符串str中的字符数
ASCII(char)：返回字符的ASCII码值
mysql官方文档字符串函数
字符串函数
CURDATE()或CURRENT_DATE()：返回当前的日期
CURTIME()或CURRENT_TIME()：返回当前的时间
FROM_UNIXTIME(时间戳)：格式化传入的时间戳，转成日期格式
UNIX_TIMESTAMP()：获取系统当前的时间戳
NOW()：返回当前的时间的日期
周一
周日
本周周一和周日
第一天
LAST_DAY(CURRENT_DATE)
最后一天
本月第一天和最后一天
当天零点
当天最大时间
当天零点和当天最大时间
日期、日期字符串、时间戳互相转换
mysql官方文档日期和时间函数
日期和时间函数（重要）
ISNULL(expression)：判断表达式是否为 NULL，为NULL返回1也就是true，否则返回0，也就是false
简单CASE函数
枚举这个字段所有可能的值，如果没有返回default，同时取别名
如果要判断字段的值是否为NULL，只能用搜索CASE函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END AS 别名
搜索CASE函数
如果exprN表达式为真，则返回resultN，否则返回default，同时取别名
CASE WHEN [expr1] THEN [result1]...ELSE [default] END AS 别名
简单CASE函数，适用于可以枚举col_name的值情况，然后根据col_value的值，设置另外的值。例如根据用户级别字段的值（1、2、3），设置普通用户、会员用户、VIP用户，不能判断字段的值为NULL的情况，需要判断字段是否为NULL，要使用搜索CASE函数
搜索CASE函数，适用于复杂场景，可以通过表达式进行各种判断，进而设置值或者返回SQL语句
建表语句
INSERT语句
统计2019-10-21 00:00:00~2019-12-02 23:59:59时间段内的用户并标记新老用户，user_id重复即为老用户
统计SQL
CASE WHNE函数案例
返回值类型的CASE WHEN函数两种用法的场景
数据SQL
SELECT\t*FROM\tcategoryWHERECASE category_id\tWHEN 1 THEN last_update = '2006-02-14 20:46:27'\tELSE `name` LIKE 'A%'END
这里想要查询当category_id等于1时，last_update等于2006-02-14 20:46:27或者name以A开头的category，可以理解为两种WHERE过滤条件后的数据集进行UNION去重的竖向拼接，这对于一些特殊场景的过滤非常有用
查询得到的结果
使用简单CASE WHEN函数进行过滤
SELECT\t*FROM\tcategoryWHERECASE \tWHEN category_id = 1 THEN last_update = '2006-02-14 20:46:27'\tELSE `name` LIKE 'C%'END
这里想要查询当category_id等于1时，last_update等于2006-02-14 20:46:27的category或者是name以'C'开头的category，可以理解为两种WHERE过滤条件后的数据集进行UNION去重的竖向拼接，这对于一些特殊场景的过滤非常有用
使用搜索CASE WHEN函数进行过滤
两种CASE WHEN函数除了返回特定的值，也可以返回SQL语句，然后在WHERE语句中进行过滤，进行一些复杂条件的判断；或者是ORDER BY语句中，进行特殊的排序
mysql官方文档控制函数
控制函数（重要）
AVG(col)：返回指定列的平均值，如果为NULL不进行计数
COUNT(*)返回检索到的行数的计数，无论它们是否包含 NULL值
COUNT(DISTINCT col)：返回指定列中非NULL值的个数，DISTINCT可以进行去重
结果集为空时，则SUM结果为NULL
累加字段出现NULL时，不参与累加计算
对非数值字段使用SUM，返回0而非NULL
SUM(col)：返回指定列的所有值之和
MAX(col)：最大值
MIN(col)：最小值
GROUP_CONCAT
GROUP_CONCAT(col)：将分组之后的值，使用\
mysql官方文档聚合函数
聚合函数
JSON函数
MD5(str)：计算字符串str的MD5校验和
PASSWORD(str)：返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法
SHA(str)：计算字符串str的安全散列算法(SHA)校验和
加密函数
DATABASE()：返回当前数据库名
USER()或SYSTEM_USER()：返回当前登陆用户名
VERSION()：返回MySQL服务器的版本
CONNECTION_ID()：返回当前客户的连接ID
系统信息函数
MySQL函数（重要）
在进行报表统计分析时常常会遇见如下需求：用户上次下单时间、topN、百分比计算等
对于这样的需求，使用传统的SQL实现起来比较困难
这类需求都有一个共同的特点，需要在单表中满足某些条件的记录集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的
要解决此类问题，最方便的就是使用窗口函数
前言
MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库中早已支持，也叫分析函数（OLAP函数）
窗口函数常用语统计topN、排名、累积计算等
在报表等分析型查询中窗口函数能优雅地表达某些需求，发挥不可替代的作用
窗口函数只能在SELECT语句中或者ORDER BY语句中
简介
窗口可以理解为根据OVER()表达式到当前行额外的数据集（多行数据）
与发生函数求值的当前行相关的查询行构成当前行的窗口
窗口函数为每行数据进行一次计算：输入多行（窗口范围内的数据），返回一个值
窗口函数对一组查询行执行类似聚合的操作。但是，聚合操作将查询行分组为单个结果行，而窗口函数为每个查询行生成一个结果
对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口
有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口
窗口和窗口函数概念
窗口函数不改变原有数据集，可能会改变顺序
对窗口范围内的数据集进行统计计算
同时具有分组和排序的功能
特点
在窗口函数执行时，数据集已经确定也就是说WHERE和HAVING已经执行完毕（窗口函数在ORDER BY和LIMIT前执行）
如果想要对窗口函数计算的值进行过滤，只能使用子查询然后使用WHERE进行过滤
如果想要对窗口函数计算的值进行排序，使用ORDER BY 窗口函数计算值别名 即可
执行时机
window_function(expression) OVER ( [PARTITION BY part_list] [ORDER BY order_list] [{ROWS | RANGE } BETWEEN frame_start AND frame_end])
其分区内的当前行数，数值相同时排名没有重复
ROW_NUMBER()
其分区内当前行的排名，有间隙，数值相同时，排名重复，例如（1、2、2、4）
RANK()
其分区内当前行的排名，没有间隙，数值相同时，排名重复，例如（1、2、2、3）
DENSE_RANK()
排名函数
百分比排名值
(rank - 1) / (rows - 1)，其中rank是行排名也就是RANK()函数的排名， rows是窗口范围内数据集的行数
PERCENT_RANK()
返回一组值中某个值的累积分布
这表示窗口分区的窗口排序中当前行之前或与之对等的行数除以窗口分区中的总行数。返回值范围从0到1
即分区值小于或等于当前行中的值的百分比
CUME_DIST()
分布函数
窗口范围内前N行的参数值
窗口范围内后N行的参数值
前后函数
窗口范围内第一行的参数值
FIRST_VALUE(col)
窗口范围内最后一行的参数值
LAST_VALUE(col)
头尾函数
窗口范围内第N行参数值（N从1开始）
NFILE()
分桶函数
专用窗口函数
SUM()
AVG()
MAX()
MIN()
COUNT()
mysql官方文档窗口函数
window_function
表示将数据先按part_list进行分区，PARTITION BY子句并不是必须的，如果没有则表示，执行HAVING过滤后的全部数据集
PARTITION BY子句
按照order_list进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和PARTITION BY子句配合使用，也可以单独使用
ORDER BY子句
FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用
通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录
CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
基于行
基于范围
滑动窗口的范围指定方式有两种基于行和基于范围
mysql官方文档窗口函数frame子句
FRAME子句
mysql官方文档命名窗口
命名窗口
基本语法
聚合函数是将多条记录聚合为一条
窗口函数是每条记录都会执行，不会改变原有的数据集
聚合函数也可以用于窗口函数中
窗口函数和聚合函数区别
插入SQL语句
需求：查询订单信息并且给出每个用户订单金额的排名
SQL语句
执行效果
如果比较的值没有相同，三个函数的执行效果一致
rank函数，当值重复时，排名也重复，后续排名不连续。如果有并列名次的行，会占用下一名次的位置
dense_rank函数，当值重复时，排名也重复，后续排名连续。如果有并列名次的行，不占用下一名次的位置。dense就是稠密的意思
row_number函数，当值重复时，排名并不重复，不考虑并列的情况
如果比较的值相同
三个排名函数的区别与联系
具体使用哪个排名函数需要根据具体的业务需求来定
使用排名函数时必须有ORDER BY子句，不然没有排序依据没法进行排名
是否有PARTITION BY子句需要根据业务需求来定，一般而言都是需要的
注意事项
案例一（3个排名函数）
需求：查询每个用户订单金额最高的前三个订单
这里只能用子查询后进行WHERE过滤
窗口函数的执行时机在SELECT之后，ORDER BY之前。因此不能使用直接WHERE过滤
排名函数，只能使用ROW_NUMBER()，不能使用RANK()或者DENSE_RANK()。因为可能存在多个并列的排名，无法确定WHERE过滤的值
分析
案例二（topN）
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响
案例三（聚合函数作为窗口函数）
案例
窗口函数
窗口函数（重要）
SUM(IF)和SUM(CASE WHEN)
CASE WHEN函数技巧
ORDER BY
https://blog.csdn.net/ManyPeng/article/details/105387061?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-1&spm=1001.2101.3001.4242
ORDER BY FIELD
https://blog.csdn.net/weixin_38626799/article/details/80107697
ORDER BY IF
ORDER BY IN
ORDER BY CASE WHEN
ORDER BY函数技巧
mysql官方文档GROUP BY修饰符
WITH ROLLUP
mysql官方文档CTE表达式
CTE（通用表达式）
ALL（SOME）、IN、ANY
mysql官方文档行子查询
行子查询
mysql官方文档子查询
子查询
常见SQL书写技巧（重要）
为了建立冗余较小、结构合理的表，设计数据库时必须遵循一定的规则
在关系型数据库中这种规则就称为范式
范式是符合某一种设计要求的总结
要想设计一个结构合理的关系型数据库，必须满足一定的范式。比较常见的是三范式，第一范式、第二范式和第三范式
表中必须要有主键，不能出现重复记录，每个字段都是原子性的不能再分
联系方式列不是原子性的，可以再次拆分成邮箱和手机号
不符合第一范式的实例
解决方案
关于列不可再分，应该根据具体的业务情况来决定。但是一个表必须要有主键，且数据不能出现重复
第一范式
第二范式是建立在第一范式基础上的，要求所有非主键字段完全依赖主键，不能产生部分依赖，一张表只描述一件事
不符合第二范式的案例：其中学生编号和课程编号为联合主键
这张表描述了三件事：学生信息、课程信息、学生的成绩信息
虽然存在着主键（学生编号 + 课程编号），但是表中出现了大量冗余数据，例如课程名称中java、mysql、html多次出现
出现冗余的原因在于，学生信息部分依赖了主键的一个字段学生编号，和课程编号没有关系。同时课程的信息只是依赖课程id，和学生id没有关系。只有成绩一个字段完全依赖主键的两个部分，这就是第二范式部分依赖
上面的表其实是多对多的关系，多对多一般存在联合主键
学生表：学生编号为主键
课程表：课程编号为主键
成绩表：中间表，学生编号和课程编号为联合主键
解决方案：将表进行拆分：学生表、课程表、成绩表
第二范式
建立在第二范式基础上的，所有非主键字段和主键字段之间不能产生传递依赖
不满足第三范式的例子：其中学生编号是主键
何为传递依赖：专业编号依赖学生编号，应为该学生学的就是这个专业啊。但是专业名称和学生其实没多大关系，专业名称依赖于专业编号。某一个字段不直接依赖主键，而是另一个字段（外键），外键依赖于主键，形成传递依赖
学生表，学生编号为主键
专业表，专业编号为主键
解决方法
以上设计是典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方
第三范式
一张表必须要有主键，最好是和业务无关的字段，例如自增主键
一般而言所有的表都会满足第一范式，不满足第一范式的表一眼都能看出来
关于列是否必须为原子性，可以根据具体的业务需求来定
如果感觉表很奇怪，一般都是不满足第二范式，没有做到一张表只描述一件事
在设计表的时候一般满足第一和第二范式即可，如果需要冗余部分字段来减少关联查询可能不满足第三范式。由于进行了字段冗余，就需要必须要做到多张表数据的一致性
总结
数据库三范式
一个表和另一张表存在的关系是一对一
两张表共享主键，其实就是纵向拆分，将一张表拆成多张表，将部分字段拆分出去形成多张表
使用这种方式，一般存在主表和次表。主表的数据不会发生弃用，例如不会删除或者是status设置为0
次表的数据可能发生弃用
外键字段添加到主表中，引用次表的主键。可以清晰地看到使用的次表的数据
外键字段添加到次表中，关联主表的主键。可以看到主表使用的历史次表数据。需要保证次表数据只能引用一次主表数据（例如主表 A JOIN 次表B ON 主表A.bid = 次表B.id AND 次表B.status = 1）
外键字段添加到哪里？
两张表，各自有主键，外键唯一
如何设计表
一对一
第三范式的例子
两张表，外键建在多的一方，引用主表的主键
这种可以记录两张表之间的引用关系
优点：如果表之间关系发生变更，从一对多变成多对多，这样不用修改表结构和数据
缺点：增加额外的表，查询起来较为复杂
三张表，中间表分别引用另外两张表的主键形成联合主键，或者使用自增主键
一对多
第二范式的例子
三张表，外键设置在中间表，分别引用主表的主键
使用联合主键（引用另外两张表的外键）或者是自增主键
多对多
常见表关系
架构图
大部分的服务都在这一层：包括查询解析、分析、优化、缓存、内置函数、存储过程、触发器、视图等
服务层（Server）
Server层通过API和存储引擎通信，不同的存储引擎之间不会通信
可以理解为面向接口编程，屏蔽底层实现细节。存储引擎可以按照需求进行设置
存储引擎层负责数据的存储和提取，支持InnoDB、MyISAM、Memory等存储引擎
不同的存储引擎共用同一个Server层
存储引擎设置在表上
注意：存储引擎是不会解析SQL的
存储引擎层（Storage Engine）
MySQL分为Server和存储引擎两大部分
连接到MySQL服务器终端。例如JDBC的Java程序、Navicat、命令行终端等
客户端
连接器负责跟客户端建立连接、获取权限、维持和管理连接
连接器
检查SQL语法是否正确
MySQL需要识别出里面的字符串分别是什么，代表什么
MySQL从你输入的\"SELECT\"这个关键字识别出来，这是一个查询语句
它也要把字符串\"T\"识别成\"表名\"T\"，把字符串\"ID\"识别成\"列ID\"
词法分析
根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法
语法分析
经过词法分析和语法分析形成抽象语法树
分析器
优化器是在表里面有多个索引的时候，决定使用哪个索引
或者在一个语句有多表关联（JOIN）的时候，决定各个表的连接顺序
优化器阶段完成后，这个语句的执行方案就确定了
优化器
进行权限检查，判断是否具有查询表的权限
调用存储引擎的接口，获取数据
执行器
查询数据时，先去缓存找，如果缓存命中，直接返回；如果没有命中，走解析器 -> 优化器 -> 存储引擎
查询缓存
MySQL架构
MySQL属于关系型数据库，而关系型数据库的存储是以表的形式进行的，对于表的创建，数据的存储、检索、更新等都是由MySQL存储引擎完成的，这也是MySQL存储引擎在MySQL中扮演的重要角色
MySQL的存储引擎种类比较多，如MyISAM、InnoDB和Memory存储引擎
在MySQL架构中执行器调用存储引擎的API获取和保存数据，可以让存储引擎以插件的方式存在，按需设置存储引擎，存储引擎设置的级别是表
存储引擎的作用：管理表创建、数据检索、数据保存和修改、索引创建和索引修改等等
从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了
支持事务。默认的事务隔离级别为可重复度，通过MVCC（多并发版本控制）来实现读写不冲突
使用的锁粒度为行级锁，可以支持更高的并发，但是容易发生死锁
支持外键
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，在.ibd文件中
具有如下特点
InnoDB存储引擎
在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少
不支持事务
不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用
对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存
默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁
支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch、Solr、Sphinx等
MyISAM存储引擎
InnoDB支持事务
MyISAM不支持事务
InnoDB支持外键
MyISAM不支持外键
InnoDB有表锁和行锁，默认锁的粒度为行级锁，并发度更高，但是更容易发生死锁
MyISAM只有表锁，默认锁的粒度为表锁，并发度较低，但是发生死锁的概率较低
锁
InnoDB的索引是聚簇索引，索引和数据放在一起，B+树叶子节点放置主键和实际数据
MyISAM是非聚簇索引，索引文件和数据文件分开放置，B+树的叶子结点放置数据地址指针
索引
InnoDB的索引为聚簇索引，分为.frm（表空间）和.idb（数据和索引）两个文件
MyISAM的索引为非聚簇索引，分为.frm（表空间）、.myi（索引）和.myd（数据）三个文件
文件
InnoDB查询会同时缓存索引和数据
MyISAM查询只有缓存索引
缓存
InnoDB和MyISAM两种存储引擎的区别（重点）
使用场景是否需要事务支持
是否需要支持高并发，InnoDB的并发度远高于MyISAM
是否需要支持外键
高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引
索引，不同存储引擎的索引并不太一样
如果不知道如何选择，默认选择InnoDB即可
如何选择
MySQL存储引擎（重点）
索引本质上是一种排好序的数据结构，是真实存在的，存储到物理磁盘文件中
帮助MySQL快速获取数据，可以将随机IO转变成顺序IO，加快数据的查询速度
对指定的列或者多列添加额外的数据结构，让查找变得更快，可能降低新增、修改、删除的时间
没有特别说明，一般说的索引都是指B树或者B+树
可以理解为书本前面的目录，通过目录快速定位对应的页码，加快查找的过程。如果没有目录就需要从开始遍历数据进行查找
索引是什么
可以大大提高查询速度
可以显著的减少查询中分组和排序的时间
可以加速表与表的连接
可以通过创建唯一索引，可以保证每一行数据的唯一性
优点
创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作
索引需要磁盘的空间进行存储，如果针对单表创建了大量的索引，可能比数据文件更快达到大小上限
当对表中的数据进行新增、修改或者删除时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作的性能，增加执行时间
缺点
使用索引的优点和缺点
使用主键字段的索引就是主键索引，其他都是辅助索引
辅助索引以非主键字段生成的称为辅助索引，又称为次级索引，二级索引
主键索引、辅助索引
如果没有主键，使用第一个非空唯一键；如果也没有，默认生成row_id（6字节）作为主键
聚簇索引只有Innodb存储引擎支持，使用主键生成的索引
除了聚簇索引之外的索引，其他都是非聚簇索引
聚簇索引、非聚簇索引
一个或者多个字段添加了唯一约束，形成唯一索引
唯一索引（添加了唯一约束）
较少使用。一般使用ElasticSearch、Solr等搜索引擎代替
全文索引
单列索引
多个字段组成形成复合索引
复合索引（联合索引、组合索引）
按照使用字段的个数
使用的字段为主键
主键索引
使用非主键的字段
辅助索引（次级索引、二级索引）
按照使用字段是否为主键
表的存储引擎为InnoDB，且使用主键或者非空唯一键或者默认row_id作为索引
聚簇索引（聚集索引）
表的存储引擎为InnoDB，除了主键或者非空唯一键或者row_id，之外的其他字段，作为索引列都是非聚簇索引
表的存储引擎为MyISAM，使用到的索引都为非聚簇索引
非聚簇索引（非聚集索引）
按照索引和数据是否存储在一起（重点）
B树索引（B树和B+树）
Hash索引
R-索引（空间索引）
全文索引（倒排索引）
按照底层数据结构
索引的分类（重点）
数组数据查询时，需要将数据全部加载到内存，如果数据量较大，占据大量内存。查询时需要进行全表扫描，获取数据
链表不使用大内存，查询时仍然需要进行全表扫描
数组和链表
类似于Java中的HashMap，字段的值通过hash函数进行散列，然后使用链地址法解决hash冲突
可以快速进行等值查询，但是涉及到范围查找，只能进行全表扫描
在特定使用常见下合适，不适合大规模使用
hash
首先二叉搜索树也是一棵二叉树
二叉搜索树的任意结点A， 其左子树的所有结点的值都小于结点A的值，其右子树的所有结点都大于结点A的值；前提是任意结点A的左右子树不为空
二叉搜索树的左右子树也是一棵二叉搜索树
二叉搜索树没有值相等的结点
简单定义
二叉搜索树所存储的元素必须具有可比较性，也就是说字段的值必须存在，不能为NULL
二叉搜索树搜索元素的时间复杂度为O(logN) ~ O(N)，N为元素的个数
图示
在顺序（递增或者递减）写入的情况下，会退化成链表。查询数据时需要全表扫描，时间复杂度为O(N)
二叉搜索树
由于二叉搜索树在最坏的情况下（顺序写入）会退化成链表，搜索时的时间复杂度高
这里AVL树在节点进行插入、删除、修改的时候进行了自平衡，让整棵树不至于过于倾斜
树的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树
某结点的左子树与右子树的高度（深度）差即为该结点的平衡因子（BF、Balance Factor）
AVL树由于自平衡的存在，使得树的高度不会太高，平均搜索的时间复杂度为O(logN)
右边二叉树中节点值为10的左右子树高度差为2
如果插入新节点时发现左右子树的平衡因子的绝对值大于2，通过LL、LR、RR、RL的操作保证平衡因子的绝对值小于等于1
自平衡手段
树的高度较高，需要多次IO操作
AVL树
红黑树存在的问题和AVL树类似，在于当数据量很大的时候，树的高度太高，IO的次数太多，效率较低
红黑树
二叉树查找的时间复杂度为O(log N)，AVL树和红黑树等通过自平衡手段让二叉树不那么倾斜，但是二叉树的高度还是太高了，需要进行多次磁盘IO操作
为了减少磁盘IO的次数，必须降低树的深度，将\"瘦高\"的树变得\"矮胖\"
基本的思路就是每个节点存储多个元素，摒弃二叉树结构使用多叉树
B树，这里的B表示balance（平衡的意思），B树是一种多路自平衡的搜索树。它类似普通的平衡二叉树，不同的一点是B树允许每个节点有更多的子节点
每个节点至多可以拥有m棵子树
根节点，只有至少有2个节点（要么极端情况，就是一棵树就一个根节点，单细胞生物，即是根，也是叶，也是树)
非根非叶的节点至少有的Ceil(m / 2)个子树
从根到叶子的每一条路径都有相同的长度，也就是说，叶子节在相同的层，并且这些节点不带信息，实际上这些节点就表示找不到指定的值，也就是指向这些节点的指针为空
B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针
m阶B树（m为子树个数）基本定义
叶子结点和非叶子均存储数据
任何一个关键字只存在于一个结点中，也就是说关键字在B树内不会重复
按照索引查询，并不需要在叶子结点结束，可能在非叶子节点结束。B树的查询效率，最好为O(1)，最差为树的高度，查询效率不稳定
查询时，在关键字内全集内做一次查找，性能逼近二分查找
新增、修改、删除数据需要重新维护B树
B树
非叶子节点只存储关键字，叶子节点存储关键字和实际数据
关键字在整颗B+树内可以重复
非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字
数据库具体实现的时候，一般都会为所有叶子结点增加了双向指针
非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层
不可能非叶子节点命中返回，必须命中叶子结点返回（索引覆盖除外）。查询效率较为稳定，需要IO的次数就为B+树的高度
B+树
各种数据结构
前面讲述了大量的数据结构，最后发现B树和B+树是较为合理的，可以作为索引底层的数据结构
评价数据结构是否适合作为索引的标准就是查询数据时磁盘IO的次数，因为磁盘IO的速度比起内存IO要慢上几个数量级
由于B树在非叶子节点同时存储数据和关键字，造成一个节点能够存储的数据个数不会太多，那么B树的高度就会比较高，磁盘IO的次数就会较多
B+树非叶子节点只存储关键字，因此能够存储的关键字更多，B+树的高度就不会太高，一般为3 ~ 4层。因此B+树的高度比B树低，磁盘IO次数更少
综上所述B+树更适合作为索引底层的数据结构
总结各种数据结构
硬盘的最小读写单元，一般是4KB
扇区
操作系统对硬盘读取的最小单元，一般是扇区的2的N次方
磁盘块
操作系统对内存操作的最小单元，一般是4KB
内存页
当一个数据被用到时，其附近的数据也通常会马上被使用
局部性原理
程序运行期间所需要的数据通常比较集中
通常程序读取数据，并不是只读取需要的，而是将附近的数据都读取出来
通常是读取一整个磁盘块
磁盘预读
和操作系统读取磁盘类似，InnoDB读取数据是以页为单位进行读取的
页（Page）是Innodb存储引擎用于管理数据的最小磁盘单位
页大小默认为16KB
可以通过SQL：SHOW GLOBAL STATUS LIKE 'innodb_page_size';进行查看
InnoDB数据页
综上所述，将B+树一个节点就设置成16KB，就是一个数据页大小。读取节点数据时将整个数据页一次性加载到内存，减少IO操作的次数
B+树根节点常驻内存，搜索时，遍历整个B+树需要进行磁盘IO的次数为h - 1（h为B+树的高度），一般B+树的高度一般为3 ~ 4层，那么只需要2 ~ 3次磁盘IO就可以获取数据。通常存储引擎会缓存索引，因此查询速度会更快
扇区、磁盘块、内存页、局部性原理、磁盘预读、InnoDB数据页
普通索引：idx_字段1_字段2_...字段N
唯一索引：ux_字段1_字段2_...字段N
索引命名
建表时
ALTER TABLE db_name.tbl_name ADD INDEX idx_name(filed_name(20));
建表后
新增
ALTER TABLE db_name.tbl_name DROP INDEX idx_name;
一般将索引列在WHERE或者是ON的后面，同时注意索引不要失效
使用索引进行查询
索引的基本操作
MyISAM所有的索引都是非聚簇索引
数据的存储不是按主键顺序存放的，按写入的顺序存放
这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意
可以看出MyISAM的索引文件非叶子节点存放主键，叶子结点存放主键和数据地址
主键索引和辅助索引的区别在于主键不能重复，辅助索引的值可以重复
辅助索引
主键索引和辅助索引
MyISAM存储引擎的索引文件和数据文件分开存放，因此也叫作非聚簇索引
如果表使用的存储引擎为MyISAM，存在三个文件.frm（表定义文件）、.MYD（数据文件）、.MYI（索引文件）
MyISAM
只有InnoDB支持聚簇索引，只有InnoDB的主键索引是聚簇索引，除此之外的所有索引都是非聚簇索引
数据写入的顺序是按照主键的大小升序写入
InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有主键会去寻找第一个唯一非空键，如果也没有默认生成6字节的row_id作为主键。索引数据和真实数据放置在一起，在同一个文件中
非叶子节点存放关键字，叶子节点存放关键字以及实际数据
同时叶子节点键增加指向前后节点的顺序指针，对于范围查找非常方便
对于查找如果找到主键，那么就直接找到了整行数据，不需要进行额外IO操作
如果使用InnoDB存储引擎，存在两个文件.frm（表定义文件）和.idb（数据和索引文件）
关键字可以重复，非叶子节点存放关键字，叶子节点存放关键字和主键索引的值
辅助索引查找整行数据，需要先根据辅助索引的值进行定位到主键索引的值，然后去主键索引树查找整行数据，这里需要进行两次索引树的查找，因此整个过程叫回表
InnoDB
InnoDB和MyISAM存储引擎如何使用B+树索引
这里以InnoDB的主键索引为例
B+树的一个节点大小为一个数据页，默认为16KB
B+树非叶子节点存储的数据为关键字和指针，主键字段的数据类型为bigint（8字节），同时存在着指向下一个主键的指针（6字节）
一个节点中主键的个数为16 * 1024 / (8 + 6) ≈ 1170个
叶子节点中存储主键和实际数据，一般而言实际数据大小远远大于主键大小，主键大小可以忽略不计。实际数据大小假定为1KB，那么叶子节点可以存储16KB / 1KB = 16条数据
通常B+树的高度为3层，整颗B+树能够存储的数据就是1170 * 1170 * 16 ≈ 2200 0000，大约等于2000万
也就是说MySQL单表能够存储的数据量大概在2000万左右
MySQL单表能够存储的数据量计算
这里主要都是InnoDB的主键索引，也就是聚簇索引
InnoDB存储引擎在写入数据时，按照主键的值进行顺序写入，也就是说如果主键的值如果不是自增（例如UUID杂乱无序），可能会将后面的数据，写入到前面的数据页中，可能造成数据页重建以及分裂
雪花ID的插入顺序略低于自增主键
由于主键自增，容易被猜出业务增长量
在高并发写入情况下，可能造成对锁的争抢严重，造成写入性能下降
使用自增ID的缺点
为什么MySQL推荐使用自增主键，而不推荐使用UUID或者雪花ID
联合索引指多个字段共同建立索引
减少索引开销
为什么使用联合索引
如果使用了联合索引在进行WHERE过滤时，需要注意MySQL会一直向右进行匹配，遇到范围查找就停止（>、<、BETWEEN、LIKE）
最左匹配原则（重要）
联合索引（复合索引）（重要）
又称为覆盖索引，本质上是一种现象，并不是一种实际存在的索引
查找数据时，只查询索引的值，不查询其他数据，过滤时只通过索引列进行过滤，命中索引就会直接返回索引数据，不需要查询实际数据，大大提高了查询效率
因此在写SQL时，不需要的字段没有必要查询出来，尤其要避免SELECT *的写法
然而理想很丰满，现实很骨感。这样的SQL业务场景非常有限，几乎没有
索引覆盖（重要）
联合索引，没有遵守最左前缀原则
联合索引，范围（>、<、BETWEEN AND）之后的字段索引失效
在高版本的MySQL似乎进行了优化
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转而向全表扫描
使用不等于（!=或者<>），索引失效
可以使用LIKE 'abc%'，避免全表扫描，前面必须要有字段
使用LIKE '%XXX%'进行模糊查询
字符串不使用单引号索引失效
InnoDB存储引擎允许字段的值为NULL，也可以添加索引
在MySQL不同版本，不同数据量的情况下，优化器会根据情况进行判断是否使用索引，因此存在着4不同的情况
一般而言MySQL的版本越高，基本上都会走索引
使用IS NULL或者IS NOT NULL进行WHERE过滤
在不同版本的MySQL，情况不相同，可能会走，也可能不会走索引
IN关键字
如果想要OR也是用索引，过滤的每一列都需要添加索引
OR关键字
MySQL判断全表扫描比索引查找更快，索引失效
总结：上述的几种情况，在高版本的MySQL中都进行了优化，同时和数据量也有一定的关系，因此走不走索引并不是绝对的
索引失效
对于经常WHERE、ORDER BY、GROUP BY、聚合函数的字段建立索引
外键字段建立索引
字段具有唯一性，建议生成唯一索引。在数据库的层面，保证数据正确性
对于经常一起出现的字段，推荐建立联合索引，需要注意最左前缀原则，将经常过滤的字段放在前面
能够使用覆盖索引，就不要查询出无用字段，减少回表操作
应该
数据量不大，没有必要建索引，全表扫描可能更快
例如性别，一般而言只有男、女，建立索引的意义不大
可以使用SELECT COUNT(DISTINCT col) / COUNT(*)进行判断区分度
对于数据区分度不高的字段，不要建立索引
对于频繁发生修改的字段，不要建立索引
参与计算的列，不要建立索引
没有必要为每个字段建立索引，索引存储会消耗磁盘空间
不应该
索引使用事项
B树和B+树有什么区别，为什么MySQL使用B+树作为索引底层的数据结构
MyISAM和InnoDB是如何使用B+树索引的
聚簇索引和非聚簇索引有什么区别
索引覆盖是什么？什么是回表？
常见面试题
索引（重点）
EXPLAIN关键字查看MySQL对SELECT类型的SQL的执行计划。从而知道MySQL是如何处理查询SQL的
当前测试的MySQL服务器版本为5.7.28，操作系统为Window10
在原来的SELECT的SQL上增加EXPLAIN关键字即可
语法
查看表的读取顺序
查询类型
哪些索引被使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
作用
subject（学科表）
teacher（教师表）
student（学生表）
student_score（学生成绩表）
测试使用的表
会在EXPLAIN的基础上额外提供一些查询优化信息
紧随其后SHOW WARNINGS命令可以查看优化后的查询语句，从而看出优化器优化了什么
EXPLAIN EXTENDED SELECT * FROM film WHERE id = 1;SHOW WARNINGS;
结果
在未来版本的MySQL可能会删除这个关键字
EXPLAIN EXTENDED
相比EXPLAIN多了个PARTITIONS字段，如果查询是基于分区表的话，会显示查询将访问的分区
高版本的MySQL已经默认带上了PARTITIONS字段
EXPLAIN PARTITIONS
有几个SELECT就有几个id
执行顺序从上到下
执行计划
执行的顺序为teacher -> subject -> student_score，并不是按照书写的顺序查询的
id相同
如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
执行的顺序为teacher -> subject -> score
id不同
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;
执行的顺序为2.teacher -> 2.subject -> 1.subject -> 1.teacher
id相同又不同
id为NULL的最后执行
union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL
id的值为NULL
执行顺序
id：表示查询中执行SELECT子句或操作表的顺序
简单的SELECT查询，查询中不包括子查询或者UNION
SIMPLE
查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY
PRIMARY
在select或where列表中包含了子查询
SUBQUERY
在FROM列表中，包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里
MySQL5.7+进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率
当派生子查询存在以下操作时该特性无法生效：UNION 、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作
EXPLAIN SELECT t1.* FROM (SELECT * FROM subject WHERE id = 1 GROUP BY id) t1
DERIVED
若第二个SELECT出现在UNION之后，则被标记为UNION
若UNION包含在FROM子句的子查询中，外侧SELECT将被标记为DERIVE
UNION
从UNION表获取结果的SELECT
UNION RESULT
select_type：表示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询
显示数据来自于哪个表，有时不是真实的表的名字（对表取了别名，显示别名），虚拟表最后一位是数字，代表id为多少的查询
table
在表中找到所需行的方式NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > in dex_subquery > range > index > ALL。效率从高到低
掌握常见8种即可：NULL > system > const > eq_ref > ref > range > index > ALL
一般来说达到range级别就可以，最好达到ref级别
MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
explain select min(id) from subject;
NULL
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问
如将主键或者是唯一键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
explain select * from teacher where teacher_no = 'T2010001';
const、system
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;
eq_ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体
ref
只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引 一般就是在你的where语句中出现between、<>、in等的查询
explain select * from subject where id between 1 and 3;
range
Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的
通常见于索引覆盖或者是JOIN连接查询中（外键设置了索引）
explain select id from subject;
index
Full Table Scan，将遍历全表以找到匹配行
explain select * from subject;
ALL
type（重点）
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示NULL）
可能出现possible_keys有列，而显示NULL的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询
possible_keys
实际使用到的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表
key（重点）
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
按照生成索引字段的数据类型来计算
整型（有符号）
MySQL常用数据类型（日期|时间、数值、字符串）
key_len计算规则如下
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
生成索引的字段，建议设置非空约束。如果存在非空，建议使用其他值代替
不损失精确性的情况下，长度越短越好
对于字符类型的字段，如果没有必要使用全部数据，可以只是用字符前几位。例如INDEX(name(21))
key_len
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
rows
表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%
filtered
当查询中包含order by操作，而且无法利用索引完成的排序操作称为\"文件排序\"，这里的文件排序是指先在内存中进行排序，当内存排序无法完成时，使用临时文件帮助排序
出现了Using filesort就需要对SQL语句进行优化
explain select * from subject order by name;
Using filesort
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见group by、order by、distinct、union等关键字
出现Using temporary，说明使用了临时表，可能需要对SQL语句进行优化
Using temporary
关于Using where、Using index、Using index & Using where、Using index condition详细解释说明：https://www.cnblogs.com/kerrycode/p/9909093.html
改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
Using join buffer
这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）
Impossible where
这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
Select tables optimized away
Query语句中使用from dual 或不含任何from子句
No tables used
Extra（重要）
EXPLAIN中的列
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
EXPLAIN（重点）
在MySQL数据库中只有InnoDB存储引擎支持事务，MyISAM存储引擎不支持事务。默认的存储引擎是InnoDB
事务用来保证数据的完整性、正确性，操作的原子性以及并发访问时数据的隔离性
事务用来管理INSERT、UPDATE、DELETE等DML语句
事务必须手动开启、提交、回滚，也可以进行自动提交事务
事务的作用范围是一个SESSION中，一个SESSION中可以有多个事务。常见的SESSION：JDBC中的一个Connection对象，也就是一个线程；命令行窗口也是一个SESSION
事务有中有安全点，可以理解为将大事务拆分成小事务，回滚时只回滚到对应的安全点，并不是回滚全部回滚数据
事务是一个操作最基本单元，其对数据的新增、修改、删除，要么全都执行，要么全都不执行，不会结束在中间某个环节
事务一旦被提交提交，在事务期间对数据的新增、修改、删除，必须全部执行
事务一旦被回滚，在事务期间对数据的新增、修改、删除，必须全部回退。新增数据，必须删除；修改数据必须恢复；删除数据必须重新回归，就好像这个事务从来没有被执行过一样
在一个SESSION中，也就是一个会话期间，事务既没有提交也没有回滚，当会话结束时，数据并不会发生修改
原子性（Atomicity）
数据库允许多个事务同时对相同的数据进行读取和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致
隔离性（Isolation）
事务完成之后，它对于数据的修改是永久性的，即使系统出现故障也不会丢失
持久性（Durable）
在事务开始之前和事务结束以后，数据库数据的完整性没有被破坏
这表示写入的数据必须完全符合所有的预设规则，这包含数据的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到）
原子性、隔离性、持久性三个特性实现一致性
一致性（Consistent）
事务四大特性（ACID）
两个或者多个事务，同时对一个数据进行修改，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
事务A的回滚覆盖了事务B已提交的数据
第一类更新丢失
事务A的提交覆盖了事务B提交的数据
第二类更新丢失
更新丢失问题有两类
两个事务同时根据主键id更新数据时，一个事务会获取意向排它锁（IX）、排它锁（X锁）和记录锁（Record Lock），X锁是排他性的，当前事务没有提交或者回滚，其他事务不允许操作当前数据
第一类更新丢失是错误，必须要避免。MySQL已经解决，不用担心
如果业务允许，那就无所谓了
但是在有些业务系统中是必须要避免的。例如余额表中的金额。多个事务并发修改同一个用户的余额，如果扣款前进行判断余额是否满足扣款，结果由于并发扣款，可能导致最后余额存在负数，这是绝对不允许的
乐观锁本质上是无锁的方式
增加版本号或者时间戳字段（记录的修改时间），进行失败重试的方式保证更新操作的正确性
对表记录修改前先进行查询，然后更新数据时，带上WHERE判断条件版本号或者是时间戳。同时判断是否执行成功，如果没有成功说明其他事务对当前数据进行了修改，进行失败重试，当达到最大重试次数直接报错，结束运行
开启事务，START;
先查询数据，SELECT * FROM account WHERE id = 1;
业务处理后准备更新余额
UPDATE语句会返回0或者1。0表示失败，1表示成功
如果返回0，表示没有更新成功，有其他事务进行了修改，可以直接抛出异常，进行事务回滚。或者进行失败重试，直到达到最大重试次数后抛出异常，结束程序
如果返回1，表示更新成功，程序结束
乐观锁
显式加排它锁SELECT ... FOR UPDATE，锁住当前数据
这样在当前事务还没有提交前，其他事务不能读取该数据
悲观锁
直接修改当前会话的事务隔离级别为串行化，让并发的事务，串行执行
事务串行执行肯定能保证更新操作成功执行
串行化
如果并发激烈，建议使用悲观锁，减少锁的冲突；如果并发不激烈，建议使用乐观锁；对于串行化，不建议使用，效率太低
常见的方式有乐观锁、悲观锁或者设置当前事务的隔离级别为串行化
第二类更新丢失并不是错误，需要根据具体业务情况来定。MySQL在默认隔离级别（RR）没有解决第二类更新丢失
更新丢失（Lost Update）
查询全局、以及当前会话的事务隔离级别
SELECT span class=\"tag\
0表示禁止自动提交，1表示自动提交事务
SET AUTOCOMMIT= 0;
显式地开启一个事务
BEGIN;
提交事务，并使已对数据库进行的所有修改成为永久性的
COMMITT;
回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
ROLLBACK;
SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT
如果业务需求，只能全部回滚
如果不需要，可以设置一些保存点，将发生错误时，回滚到最近的保存点上，而不是回滚所有数据
这对于复杂的大事务非常有帮助，例如批量新增1000万的数据，如果中间某一条失败，需要将1000万数据全部回滚吗
SAVEPOINT identifier;
删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
RELEASE SAVEPOINT identifier;
把事务回滚到标记的保存点
ROLLBACK TO identifier;
修改事务隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ |SERIALIZABLE];
GLOBAL，设置全局事务隔离级别
SESSION，设置当前会话的隔离级别
如果GLOBAL和SESSION都不设置，表示修改的事务隔离级别将应用于当前session内的下一个还未开始的事务
SET TRANSACTION ISOLATION LEVEL 隔离级别;
事务控制语句
脏读/不可重复读/幻读都可能
读未提交（Read uncommitted）
脏读不可能，不可重复读/幻读都可能
读已提交（Read committed简称RC）
可重复读的隔离级别下使用了MVCC机制，让读写不冲突，快照读的读视图相同，读取到的数据永远都是一样的
要避免幻读可以用间隙锁
不可重复读/脏读不可能，幻读都可能
可重复读（Repeatable read简称RR）
脏读/不可重复读/幻读都不可能
MySQL中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到
串行化（Serializable）
分类
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
脏读（Dirty Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做不可重复读
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
不可重读（Non-Repeatable Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为\"幻读\"
幻读（Phantom Reads）
show variables like 'transaction_isolation';
select @@transaction_isolation;
查看事务隔离级别
InnoDB存储引擎默认的事务隔离级别是Repeatable read（可重复读），同时通过MVCC + Next-Key Lock可以解决幻读问题
事务隔离级别
-- UNSIGNED代表无符号数，不能是负数
建表SQL
测试数据
SQL执行流程
脏读违反了一致性，是必须要避免的
读未提交，出现脏读
只能够读到其他事务已经提交的事务，但是通过相同的SQL语句，多次读取可能读到的数据不相同。可能是其他事务执行了UPDATE和DELETE语句
出现不可重复读并不是错误，这需要在业务进行判定，业务上是否允许
Oracle数据库默认的隔离级别就是读已提交
读已提交，避免脏读和出现不可重复读
可重复读SQL执行流程
事务A先查询所有数据，发现只有2条数据，准备插入id为3的数据
事务B插入id为3的数据，并且提交事务
事务A插入id为3的数据，报主键重复错误
这里出现了幻读，事务A查询的时候明明没有id为3的数据，但是插入数据的时候却报主键冲突
再次查询仍然没有id = 3的数据，这是可重复读机制（MVCC实现）
出现幻读SQL执行流程
MySQL默认事务隔离级别为可重复读
想要解决幻读可以显示加锁SELECT ... LOCK IN SHARE MODE或者是SELECT ... FOR UPDATE
可重复读，避免脏读、避免不可重复读和出现幻读
将写操作进行串行化处理，多个读操作可以同时进行，读写冲突，写写冲突，直到其中一个事务提交前
串行化，避免脏读、避免不可重复读和避免幻读
演示事务隔离级别
undo log
原子性
MVCC（依赖于undo log、read view、数据行的隐式字段（trx_id、回滚指针））、锁
事务A还没有提交时，释放排它锁。事务B能够读取该数据
读未提交
事务A提交后，释放排它锁。事务B只能够读取到已提交的数据。同时read view在每次快照读重新生成一份，每次读取已提交的数据
读已提交
每次快照读复用第一次快照读生成的read view，因此每次读到的数据相同
可重复读
对于每个写操作串行处理
隔离性
redo log
持久性
通过原子性、隔离性、持久性实现一致性
一致性
InnoDB存储引擎如何实现四大特性
ACID中的C是一致性，在一个事务中，多次对数据进行操作后，数据仍然保证正确性，并不会凭空增加、减少或者消失
CAP中C也是一致性，主要指在分布式系统中，一份数据要在多个服务间进行同步。数据的同步是需要时间，复制数据可能失败，需要保证多个服务间数据的一致性。常见的有强一致性、弱一致性和最终一致性
二者目的不同，因而达到的效果也不同。ACID更强调的是单个数据的正确性、完整性，CAP更强调的是多个服务间数据的一致性
关系型数据中的ACID与分布式理论中的CAP中，这两个C有什么区别
事务（重点）
数据库中的锁是为了保证并发访问时数据的一致性，使各种共享资源在被访问时变得有序而设计的一种规则
MySQL中关于锁的知识和事务隔离级别、索引、MVCC杂合在一起，显得非常乱。且锁的各种名词让人眼花缭乱
本质上是无锁的方式，总是乐观地认为不会发生锁冲突，如果发现更新失败，则进行失败重试，直到达到最大重试次数，回滚事务
根据版本号或者是时间戳控制
只要是加了锁都是悲观锁
针对同一份数据，多个读操作可以同时进行而不会互相影响
不能进行写操作
读锁（共享锁）
当前写操作没有完成前，它会阻断其他写锁和读锁
写锁（排他锁）
锁定表或者行，让其他数据操作等待
加锁机制
表锁是指对一整张表加锁。表锁由MySQL Server层实现
表锁
行锁是锁定某行、某几行或者行之间的间隙，由存储引擎实现，不同存储引擎实现不同。目前只有InnoDB存储引擎支持行锁，如没有特殊说明，行锁就是指InnoDB存储引擎的行锁
行锁
锁粒度
加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁
共享锁（Share Lock，简称S锁）
允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
排它锁（Exclusive Lock，简称X锁）
兼容性
读意向锁（IS锁）
写意向锁（IX锁）
意向锁
获取共享锁、排它锁之前需要先获取意向共享锁、意向排它锁
表共享读锁（Table Read Lock）
表独享写锁（Table Write Lock）
存储引擎
锁的分类
普通的SELECT语句不会加共享锁
想要显式加共享锁，可以加SELECT ... LOCK IN SHARE MODE子句
为了确保自己查询的数据一定是最新的数据，不会被其他事务进行修改
被读取的行记录或者行范围其他SESSION可以读，也可以加共享锁，但是其他事务无法获取排它锁，也就是说S锁不冲突，S锁和X锁冲突
其他事务可以进行普通的SELECT、SELECT ... LOCK IN SHARE MODE，但是不能进行UPDATE、DELETE、INSERT操作
普通的UPDATE、INSERT、DELETE语句都会加排它锁
想要对SELECT语句显式加排它锁，可以加SELECT ... FOR UPDATE子句，相当于UPDATE语句
保证读取到的数据一定是最新的，不允许其他事务进行修改
其他事务可以进行普通的SELECT，但是不能进行SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT操作
二者都可以读取到最新的数据，保证不让其他事务进行修改
可以让普通的SELECT读，UPDATE、DELETE和INSERT语句阻塞
相同
共享锁和共享锁不是互斥的，排它锁和排它锁、共享锁和排它锁是互斥的
两个事务同时进行LOCK IN SHARE MODE，且对锁住的数据执行UPDATE语句，会造成死锁
排它锁只有一个事务能进行FOR UPDATE，不会发生死锁
LOCK IN SHARE MODE适用于并发度低，且不会执行UPDATE锁住数据的场景，FOR UPDATE适用于并发度高，且执行UPDATE锁住数据的场景
不同
LOCK IN SHARE MODE和FOR UPDATE的相同和不同
更新数据时根据索引进行更新。记录锁最简单的一种行锁形式，记录锁是加在索引上的
如果更新语句中WHERE过滤条件不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意
记录锁（Record Lock）
当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁
而对于键值在条件范围内但并不存在的记录，就叫做间隙，InnoDB在此时也会对间隙加锁，这种记录锁+间隙锁的机制叫Next-Key Lock
间隙锁是一个索引值的左开右开的区间
间隙锁（Gap Lock）
临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开右闭的区间
临键锁（Next-key Lock）
记录锁、间隙锁、临建锁之间的关系示意图
插入意图锁是一种间隙锁，在行执行INSERT之前的插入操作设置
如果多个事务INSERT到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突
插入意向锁（Insert Intention Lock）
第一行表示已有的锁，第一列表示要加的锁
插入意向锁不影响其他任何锁
间隙锁和Next-Key与插入意向锁冲突
间隙锁和除了插入意向锁之外的锁都不冲突
记录锁和记录锁冲突，记录锁和Next-key冲突，Next-key和Next-key冲突
锁模式下各种锁之间的兼容矩阵
锁模式
由于表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，必须要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突
意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）
当事务要在记录上加上行锁时，要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率
意向锁是InnoDB自动加的，不需要用户干预
读写意向锁
AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的
自增锁
第一行是已存在的锁，第一列是想要获取的锁
读意向锁、写意向锁、自增锁、共享锁、排它锁之间的兼容性
当不存在冲突时，InnoDB存储引擎并不会默认生成锁，而是当多个事务冲突后才会生成锁
表锁为意向锁，意向锁主要是为了简化表锁和行锁之间的逻辑，表锁是InooDB存储引擎自己加上的，一般不用关注
如何描述一个行锁呢？现有锁的模式（共享、排他），然后有锁的类型。例如共享记录锁、排他记录锁
lock_mode，表示锁模式，主要有S、X、IS、IX、GAP、AUTO_INC
lock_type，表示锁类型，主要有Record Lock、Next-key Lock、Insert Intention Lock
可以在information_schema.INNODB_LOCKS系统表中查看当前InnoDB存储引擎中存在的锁
较为复杂的是行锁，行锁有两种模式，一种是S锁，一种是X锁。行锁的类型又可以细分成记录锁、间隙锁、临建锁等
InnoDB锁详细解释
一般而言表使用的存储引擎都是InnoDB，下面所有案例都是InnoDB存储引擎，这里只描述行锁中的X锁
事务隔离剂级别取读已提交和可重复读
索引取主键索引、二级唯一索引、二级非唯一索引
执行SQL后究竟加什么锁和事务隔离级别、索引、是否命中数据均存在关系
UPDATE语句会获取X锁，同时根据主键id，命中会加记录锁
记录锁和记录锁之间是冲突的
SQL执行流程图
由于Record Lock之间冲突，所以在information_schema.INNODB_LOCKS才能看到
加锁记录
在RC和RR隔离等级下的加锁，两种隔离等级下没有任何区别，都是对id = 10这个索引加排他记录锁
案例1：聚簇索引，查询命中
间隙锁和插入意向锁，如果插入的数据在间隙锁的区间内就冲突，否则不冲突
案例2：聚簇索引，查询未命中
在InnoDB存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁，无论是RC还是RR隔离级别
案例3：二级唯一索引，查询命中
在RR隔离等级下未命中时的加锁情况，RC隔离等级下该语句未命中不会加锁。在 N0007 和 Suprenum Record 之间加了间隙锁
在SHOW EGINE INNODB STATUS的日志中出现了插入意向锁等待间隙锁
案例4：二级唯一索引，查询未命中
案例5：二级非唯一索引，查询命中
案例6：二级非唯一索引，查询未命中
案例7：无索引
案例8：聚簇索引，范围查询
案例9：二级索引，范围查询
案例10：修改索引值
案例11：DELETE语句加锁分析
案例12：INSERT语句加锁分析
具体场景分析
常见加锁场景分析
当前正在等待锁定的数量
Innodb_row_ lock_current_wait
从系统启动到现在锁定总时间长度
Innodb_row_ lock_time
每次等待所花平均时间
Innodb_row_ lock_time_avg
从系统启动到现在等待最长的一次所花时间
Innodb_row_ lock_time_max
系统启动后到现在总共等待的次数
Innodb_row_ lock_waits
show status like'innodb_row_lock%';
行锁分析
Session _1执行：select *from account where i d= 1 for update;Session _2执行：select *from account where i d= 2 for update;Session _1执行：select *from account where i d= 2 for update;Session _2执行：select *from account where i d= 1 for update;查看近期死锁日志信息：show engine innodb status;
死锁
把MySQL中的各种锁及其原理都画出来
锁（重点）
MVCC英文全称Multi-Version Concurrency Control，中文翻译多版本并发控制，是一种用来解决读 - 写冲突的无锁并发控制技术，同时也是解决事务中隔离性的关键
MVCC并不是为每个事务将数据全量复制一份，每个事务只读自己的数据，这样的效率太低，性能太差
InnoDB存储引擎为每个事务都分配一个自增的事务id。在事务中修改数据时，并不是覆盖修改原有数据，而是保持原有数据不变，生成一个新的数据。每次修改或者删除都产生一个新的版本（这就是多版本的含义）指向旧版本的数据，形成一个版本链。修改或者删除的数据版本与当时操作的事务id关联。Read View（读视图）决定当前事务能够读取的数据版本，它包含了多个事务id。根据读视图从数据版本链中选择合适的数据版本让当前事务读，这样即使其他事务在写，但是不会阻塞读请求，可以读取历史版本的数据，让读写不冲突
注意：begin/start transaction命令并不是一个事务的起点，执行UPDATE、DELETE、INSERT语句，事务才真正启动，才会向MySQL申请事务id。SELECT语句是不会分配事务id的。MySQL内部是严格按照事务的启动顺序来分配事务id的
不存在任何问题，也不需要任何并发控制
读 - 读
可能存在数据安全问题，因为一个事务在读，另外一个事务在写。从事务的隔离级别这个角度而言，可能产生脏读、不可重复读和幻读
读 - 写
可能存在数据安全问题，多个事务同时对同一个数据进行修改可能产生更新丢失问题，也就是第一类更新丢失和第二类更新丢失
MySQL数据库本身已经解决了第一类更新丢失
第二类更新丢失可以通过悲观锁或者乐观锁（版本号或者时间戳）进行解决
写 - 写
数据库并发场景分为三种
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写能力
同时还可以解决脏读、不可重复读、幻读事务隔离问题，但是不能解决第二类更新丢失问题。第二类更新问题需要使用悲观锁或者是乐观锁解决
MVCC的作用
MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突
小结一下：MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读 - 写冲突问题，而提出解决方案。所以在数据库中有了MVCC，可以形成如下两种组合
在学习MVCC之前需要先了解一下MySQL中InnoDB存储引擎下的当前读和快照读
像SELECT LOCK IN SHARE MODE（共享锁）、SELECT FOR UPDATE、UPDATE、INSERT、DELETE（排它锁）这些操作都是当前读
当前读读取的都是记录的最新数据版本，读取时需要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
当前读
快照读就是普通的SELECT，即不加锁的非阻塞读
快照读的前提是事务隔离级别不是串行，在串行级别下快照读会退化成当前读
快照读是为了提高并发度，让读写不冲突
快照读是基于MVCC实现，读到的是某一个版本的数据，既然是某一个版本的数据，就并不一定是最新的数据，可能是历史数据
快照读
SQL执行顺序
在上表的顺序下，事务B的在事务A提交修改后的快照读是旧版本数据，而当前读是实时新数据400
案例1
在这里的顺序中，事务B在事务A提交后的快照读和当前读都是实时的新数据400
案例2
快照读非常依赖于读操作首次出现的时机，它有决定该事务后续快照读结果的能力
当前读和快照读
MVCC主要通过表数据中的3个隐式字段、undo log、Read View来实现
每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
6字节，最近修改（修改/插入）事务id，记录创建这条记录/最后一次修改该记录的事务id
DB_TRX_ID
7字节，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
DB_ROLL_PTR
6字节，隐含的自增id（隐藏主键），如果表没有主键，InnoDB会自动以DB_ROW_ID生成一个聚簇索引
DB_ROW_ID
实际上还有一个删除flag隐藏字段，既记录被删除并不代表真的删除，而是删除flag变了
删除flag隐藏字段
记录图示
表数据3个隐式字段
undo log主要分为两种insert undo log、update undo log
代表事务在insert新数据时产生的undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃
insert undo log
事务在进行update或者delete时产生的undo log
不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除
只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
update undo log
从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的delete_bit，并不真正将过时的记录删除
为了节省磁盘空间，InnoDB有专门的purge线程来清理delete_bit为true的记录
为了不影响MVCC的正常工作，purge线程自己维护了一个read view（这个read view相当于系统中最老活跃事务的read view）
如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的
purge线程
对MVCC有帮助的实质是update undo log，undo log实际上就是存在rollback segment中旧记录链
比如persion表有一条记录，记录如下，name为Jerry，age为24岁，隐式主键是1，事务id和回滚指针，我们假设为NULL
在事务1修改该行（记录）数据时，数据库会先对该行加排他锁
然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本
拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务id为当前事务1的id，我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，即表示我的上一个版本就是它
事务提交后，释放锁
现在来了一个事务1对该记录的name做出了修改，改为Tom
在事务2修改该行数据时，数据库也先为该行加锁
然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
修改该行age为30岁，并且修改隐藏字段的事务id为当前事务2的id，那就是2 ，回滚指针指向刚刚拷贝到undo log的副本记录
事务提交，释放锁
又来了个事务2修改person表的同一个记录，将age修改为30岁
从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表
undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）
update undo log执行流程
事务在进行快照读操作的时候产生的读视图，在该事务执行快照读的那一刻，会生成数据库系统当前对于事务的一个快照，记录并维护系统当前活跃事务的id
读视图并不是事务开始后就生成了，而是在进行快照读的时候才会产生，当前读（加排它锁）不会产生读视图
读视图的作用就是通过记录的事务id，在数据版本链中寻找一个合适的版本让当前事务读
在可重复读隔离级别下，不管有多少个快照读，使用的都是第一个快照读进行时生成的读视图，读视图从始至终都不会发生变化
在读已提交隔离级别下，每进行一次快照读，都会产生一个读视图，也就是说每次快照读，读视图都不相同，读视图随着执行快照读而发生变化
综上所述，在读已提交隔离级别下，即使是相同的SELECT语句，由于读视图可能不同（每次产生新的读视图），读取到的数据可能不同；在可重复读隔离级别下，相同的SELECT语句，读视图一定相同（沿用第一次快照读产生的读视图），读取到的数据一定相同。这也是可重复读的含义，相同的SELECT语句，查询的数据一定相同
在不同的隔离级别下，读视图也不相同
读视图由查询时所有未提交事务id数组（数组里最小的id为min_id）和已提交的最大事务id（max_id）组成。查询的数据结果需要和读视图做对比从而得到快照结果
事务id和事务是否提交的关系图
trx_id并不是指当前session的事务id，而是版本链中数据行对应生成该数据的事务id
creator_trx_id：表示生成该ReadView的事务的事务id
如果是只读事务，那么该事务的事务id就是0
当前事务自己修改的数据，对自己一定是可见的
如果被访问版本的trx_id与creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
如果落在绿色部分（trx_id < min_id），表示这个版本是已提交的事务生成的，这个数据是可见的
如果落在红色部分（trx_id > max_id），表示这个版本是由将来启动的事务生成的，是肯定不可见的
若trx_id在数组中，表示这个版本是由还没有提交的事务生成的，对当前事务不可见
若trx_id不在数组中，表示这个版本是已经提交的事务生成的，对当前事务可见
如果落在黄色部分（min_id <= trx_id <= max_id）那就包含两种情况
如果某个版本的数据对当前事务不可见，根据回滚指针，需要顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，直到找到版本链中的最后一条数据。如果最后一个版本也不可见，那意味着该条记录对该事务完全不可见，查询结果不包含该记录
版本链的比对规则
对于删除的情况可以认为是update的特殊情况。会将版本链上最新的数据复制一份，然后将trx_id修改成操作的trx_id，同时在该记录的头信息（record header）里的（deleted flag）标记位写上true，意味着记录已被删除，不返回数据
Read View（读视图）
前面理解了隐式字段、undo log、Read View、当前读和快照读的概念后，整体来看MVCC的执行流程
假定存在5个session，其中三个写事务，两个读事务（默认事务隔离级别是可重复读）
SQL语句执行顺序
此时undo log版本链。第三列是生成改行数据的事务id
第一条数据的row的trx_id为300
查询到的数据就是lilei300
第一次快照读
此时undo log版本链
第二次快照读
读视图仍然沿用第一次快照读生成的读视图，由于读视图不变，根据版本比对规则，到最后也会找到lilei300这条数据
第三次快照读
在可重复读隔离级别下，三次快照读，无论其他事务是否提交事务，读到的数据均相同，满足了可重复读的语义，根本的原因是在RR隔离级别下，读视图均沿用第一次快照读生成的读视图
三个写事务和其中一个读事务（select1）
undo log日志数据版本链
第一条数据trx_id为200，在未提交事务id数组[200]中，对当前事务不可见
顺着回滚指针找到第二条数据，trx_id为200，对当前事务同样不可见
顺着回滚指针找到第三条数据，trx_id为100，小于min_id200，属于已提交的事务，对当前事务可见，停止查找，返回lilei2
版本比对规则
三个写事务和其中一个读事务（select2）
整体流程
B站图灵诸葛MVCC实现原理
MVCC实现原理
在RC隔离级别下，是每个快照读都会生成并获取最新的Read View
在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是第一次快照读生成的Read View
正是由于在不同事务隔离级别下，Read View可能不同（活跃事务提交），造成读取的数据可能不同
RC、RR级别下的InnoDB快照读有什么不同
多个事务对同一条数据进行修改或者删除时，通过undo log形成数据版本链的链表，版本数据中有额外的隐式字段事务id，回滚指针。回滚指针指向老数据，形成链表，链表的头部就是最新的数据，尾部就是最老的数据
读视图就是对当前系统中所有事务进行一个快照，包含已提交和未提交事务，用来判断数据的可见性。读视图在RC和RR隔离级别生成时机不同
查找数据时，到undo log日志中进行查找，根据读视图，进行版本比对，判断可见性，找到合适的数据，进行返回
MVCC是避免读写冲突的关键技术，同时也是实现事务隔离性的关键技术
MVCC（重点）
binlog用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中
binlog是MySQL的逻辑日志，并且由Server层进行记录，使用任何存储引擎的MySQL数据库都会记录binlog日志
binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志
在实际应用中， binlog的主要使用场景有两个，分别是主从复制和数据恢复
在Master端开启 binlog ，然后将binlog发送到各个Slave端， Slave端重放binlog从而达到主从数据一致
主从复制
通过使用mysqlbinlog工具来恢复数据
数据恢复
例如canal监听mysql的binlog，然后将数据同步数据源中，例如将mysql数据导入到hive中
数据同步
使用场景
对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢
0：不去强制要求，由系统自行判断何时写入磁盘
1：每次commit的时候都要将binlog写入磁盘
N：每N个事务，才会将binlog写入磁盘
mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N
从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值
但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能
binlog刷盘时机
binlog日志有三种格式，分别为STATMENT、ROW和MIXED
在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定
在某些情况下会导致主从数据不一致，比如执行sysdate() 、slepp() 等
STATMENT
不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题
会产生大量的日志，尤其是`alter table` 的时候会让日志暴涨
ROW
MIXED
binlog日志格式
binlog
事务四大特性之一为持久性，只要事务提交成功，那么对数据库的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态
最简单的做法就是每次事务提交时，将事务涉及到修改的数据页全部刷新到磁盘中
InnoDB是以页为单位与磁盘进行交互，一个数据页大小为16kb，一个事务可能只修改一个数据页里面几个字节，如果将完整的数据页刷新到磁盘，太浪费资源
一个事务可能涉及到多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差
上述做法存在着严重的性能问题
常见做法是修改数据时，先将数据读取到内存的缓冲池中，然后进行修改。数据在内存中被修改，与磁盘中相比就存在了差异，这种有差异的数据成为脏页
因此MySQL设计了redo log， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了（相对而言文件更小并且是顺序IO）
为什么需要redo log
redo log包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo logfile）
MySQL每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file
这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL（Write-Ahead Logging）技术
redo log基本概念
用户空间（user space）下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过内核空间（kernel space）缓冲区（OS Buffer）
redo log buffer写入redo logfile实际上是先写入OS Buffer，然后通过系统调用fsync()将其刷到redo log file中
0（延迟写）
1（实时写，实时刷）
2（实时写，延迟刷）
MySQL支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下
redo log刷写时机
redo log记录形式
redo log的大小固定
binlog可通过参数max_binlog_size设置每个binlog文件的大小
文件大小
redo log是InnoDB引擎层实现的，并不是所有引擎都有
binlog是Server层实现，所有引擎都可以使用binlog日志
实现方式
redo log采用循环写的方式，当写到结尾时，会回到开头循环写日志
binlog通过追加的方式记录，当文件大小大于设定值后，后续日志会记录到新的文件上
记录方式
redo log适用于崩溃恢复（crash-safe）
binlog适用于主从复制和数据同步
redo log和binlog的区别
一条UPDATE的SQL执行流程
mysql三大日志
undo log、redo log和binlog（重点）
SHOW WARNINGS 在explain执行后执行，查看翻译后的sql
使用EXPLAIN关键字去查看SQL的执行计划
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
最佳左前缀法则
存储引擎不能使用索引中范围条件右边的列EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 使用索引EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 未使用索引
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
字符串不加单引号索引失效EXPLAIN SELECT * FROM employees WHERE name = '1000';EXPLAIN SELECT * FROM employees WHERE name = 1000;
or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的
in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内
union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表
order by如果根据多个值进行排序，那么排序方式必须保持一致，要么同时升续，要么同时降续，排序方式不一致不走索引
索引优化规则
不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer
字段的数据类型
数据类型的长度
表的存储引擎
优化数据库表结构的设计
分库分表
数据库参数配置优化
主从复制，读写分离
数据库编码: 采用utf8mb4而不使用utf8
字段名
不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高
字符类型
金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围不建议使用ENUM、SET类型，使用TINYINT来代替
日期类型
是否为null
字段类型
where中如果有多个过滤条件，在没有索引的情况下将过滤多的写在前面，过滤少的写在后面
实例
禁止使用select *，需要什么字段就去取哪些字段
不要使用count(列名)或 count(常量)来替代 count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和非NULL无关。 说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。避免使用存储过程、触发器
优化方式
MySQL常见优化手段（重点）
主从同步
和单个磁盘或者文件系统分区相比，可以存储更多数据优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
优缺点
1）、当需要删除一个分区上的\"旧的\
基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义
Range
设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列
List
range columns
常规hash分区
常规hash分区在管理上带来了的代价太大，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡
线性hash分区使用的是一个线性的2的幂运算法则
线性hash分区
hash分区
partition by key(expr) partitions num;-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键partition by key() partitions num;-- linear keypartition by linear key(expr)
按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式，而key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键
key分区
是分区表中对每个分区的再次分割，又被称为复合分区，支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录
子分区
分区方式
管理分区
分区
打开cmd命令
打开到mysql文件夹下的bin目录
通过mysqldump来执行导出
命令：mysqldump -u root -p 数据库（class15） > 要导出的文件名如：（test.sql）
导出之后的文件会出现在bin目录下
数据库导出
cmd打开到mysql的bin目录下
通过mysql -uroot -p 输入密码的形式进入到数据库中
选择数据库USE db_name;
执行导入命令：source d:\\datafilename.sql后边路径是sql文件存放的物理路径
sql文件导入
数据库导入导出
MySQL
0 条评论
回复 删除
下一页
