基础篇
SQL分类
DDL：数据定义语言。 create \ alter \ drop \ rename \ truncate
DML：数据操作语言。insert \ delete \ update \ select
DCL：数据控制语言。commit \ rollback \ savepoint \ grant \ revoke
着重号``
当你的字段或表名的名字和关键字冲突时，可以用着重号包起来，查询就不会报错，如下图
describe 查看表字段详情（也可以简写成desc）
MySQL查询，在判定字符串的时候，也不区分大小写，如下图
运算符
<=> 安全等于，可以用来判断是否等于null（也拥有=号的功能，is null只能判断是否为空）
GREATEST和LEAST 求多个字段的最大值和最小值（功能和max、min不一样）
LIKE 配合%或者_使用，一个%表示0个或者多个未知的字符，_表示一个未知的字符
\ 转义字符 可以转义%和_这种特殊字符，让他表示为本来的字符，没有特殊含义。也可以自定义转义字符，通过 ESCAPE 关键字定义，如下图
REGEXP RELIKE 正则表达式
XOR 逻辑异或（条件两边不相同，则返回true） SELECT A XOR B
LIMIT和OFFSET （offset是MySQL8.0的新关键字，效果如下图）
limit 偏移量,条目数
limit 条目数 offset 偏移量
全外连接（MySQL不支持，不过有其他的实现方式UNION和UNION ALL）
FULL OUTER JOIN
NATURAL JOIN 自动查询两张表所有相同的字段，并进行连表查询
USING 填写一个字段，会自动将两个表中的此字段进行等值连接
函数（方法）
INSERT 替换自定位置的字符串
REPLACE 替换字符串
统计数量效率
count(*) = count(1) > count(字段)
GROUP BY WITH ROLLUP 对所有数据进行分组排序之后，会对分组之后的所有结果进行聚合求值
MySQL计算列
GENERATED ALWAYS AS 计算公式 VIRTUAL
MySQL中json字段的查询方式，如下图
约束
为什么需要约束
为了保证数据完整性（精确性和可靠性）
实体完整性（Entity Integrity）：同一表中，不能出现两条完全相同无法区分的数据。
域完整性（Domain Integrity）：年龄范围0-120，性别范围 男、女
引用完整性（Referential Integrity）：员工所在部门，在部门表中要能够找到这个部门
用户自定义完整性（User-defined Integrity）：用户名唯一，密码不能为空等
check约束
5.7版本不支持check约束（可以建立，但是不生效）
8.0版本支持check约束
自增列
MySQL 5.7版本，重启数据库之后，自增列的记录会消失，再次使用会查询表中最大数值作为当前自增列的值
8.0版本，自增列记录不会小时，通过redo日志重新给自增列赋值
创建表时，可以指定数据库引擎（默认是InnoDB）
视图
在对视频进行增删改查的时候，如果视图的字段能对应到表中，则也会影响表中的数据，如果表中没有此字段，则不会有影响
程序出错处理机制
DECLARE 错误名称 CONDTION FOR 错误码 （或错误条件）
循环（leave和iterate这两个关键字可以跳出循环）
LOOP
WHILE
REPEAT
窗口函数（8.0新特性）
窗口函数语法
函数 over(partition by 字段名 order by 字段名 asc | desc )
序号函数
ROW_NUMBER() 组内行号
RANK() 组内排序号
DENSE_RANK() 组内排序号
分布函数
PERCENT_RANK()
CUME_DIST() 组内小于等于当前排序值的数据量比例
前后函数
LAG(expr,n) 查询组内当前行往前数第n行数据
LEAD(expr,n) 查询组内当前行往后数第n行数据
首尾函数
FIRST_VALUE(expr) 查询组内第一行数据
LAST_VALUE(expr) 查询组内最后一行数据（同上原理）
其他函数
NTH_VALUE(expr,n) 查询组内第n条数据
NTILE(n) 组内分组（分成n组）
公用表表达式
CTE实现（with as）
递归公用表表达式
高级篇
SQL编写建议
关键字和函数名全部大写
数据库名、表名、表别名、字段名、字段别名等全部小写
SQL语句必须以分号结尾
sql_mode（检查sql的严格度）
数据库文件结构
默认数据库文件存储路径为/var/lib/mysql
其中一个库为一个文件夹，进入一个数据库文件夹中，会看到多个ibd文件（如果使用的是InnerDB存储引擎的话），idb文件里包含了表结构和表数据，以及字符集等信息（8.0之前的版本，会有frm文件记录表结构，opt文件记录字符集等信息）。
操作用户
#创建用户
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是%
CREATE USER 'li4'@'localhost' IDENTIFIED BY '123456';
#修改用户
UPDATE mysql.user SET USER='li4' WHERE USER='wang5';
FLUSH PRIVILEGES; #修改之后要刷新，否则不生效
#删除用户
DROP USER 'li4'; #默认删除host为%的用户
DROP USER 'li4'@'localhost';
#修改密码
修改当前用户密码（用户登录后执行下面的SQL修改自己的密码）
ALTER USER USER() IDENTIFIED BY 'new_password';
使用root用户登录后，可以使用SET语句修改密码
SET PASSWORD='new_password';
修改其他用户的密码（需要拥有修改用户的权限）
ALTER USER 'zhang3'@'%' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'li4'@'localhost'='new_password'
MySQL的权限分布
查看当前用户所拥有的权限
show grants;
赋予某个用户一些权限
grant select,update on test.* to 'zhang3'@'%'; #赋予zhang3 查询，修改的权限（test数据库中的所有表）
grant all privileges on . to 'li4'@'%'; #赋予li4所有数据库的所有表的所有权限
grant all privileges on . to 'li4'@'%' with grant option; #赋予li4所有数据库的所有表的所有权限（并且能够将自己的权限赋予给其他用户）
回收权限
revoke select,update on test.* from 'zhang3'@'%'; #回收zhang3 查询，修改的权限（test数据库
权限表
权限表中最重要的是user表，db表。除此之外，还有table_priv、column_priv、proc_priv表。MySQL启动时，服务器会将这些数据库表中权限信息的内容读入内存。
MySQL逻辑架构
8.0之后删除了查询缓存
SQL执行流程
SQL执行流程
select @@profiling; #查询是否记录SQL执行流程，默认为不记录（默认为查询session级别的变量，可以select @@global.profiling 查询全局变量）
使用set可以临时改变变量值，要永久改变，需要修改配置文件
数据库缓冲池（buffer pool）
数据加载到内存中，读取数据时从内存中读取，避免从磁盘中读取，减少与磁盘直接进行io的时间。
位置*频次是缓冲原则。
数据是以页的方式存储的，每个页的大小固定（16kb），缓冲数据时，会把相邻页的数据也加入到缓冲池中。
如果命中缓冲池中的数据是更新操作，会先改缓冲池中的数据，然后数据库会以一定的频率将缓冲池中的数据更新到磁盘中（刷盘）
缓冲池大小默认为128MB（总量，每个缓冲池的容量=总量/缓冲池个数）
缓冲池的默认实例个数是1（这些变量也可以通过配置文件实现改变，只有缓冲池大小总量>1G，这个配置才生效，要不然就是1）
存储引擎（表处理器，以表为单位）
默认的存储引擎，可以通过设置变量修改
InnoDB：具备外键支持的事务存储引擎（5.5之后的版本默认的存储引擎）
InnoDB写的处理效率一般，对内存要求高
索引和表数据存放在一起（聚簇索引）
MyISAM：非事务处理的存储引擎
提供大量特性，包括全文索引、压缩、空间函数（GIS）等。但不支持事务，行级锁，外键。崩溃后数据无法恢复。
访问速度快，对事务完整性没有要求的select、insert为主的应用
统计数据count(*)效率高
数据和索引是分开存储（.frm为表结构 .MYD为表数据 .MYI为索引）
两个存储引擎的区别
Archive：数据归档
仅支持插入和查询操作
使用zlib压缩库，对数据进行压缩，节约空间。
行级锁，支持AUTO_INCREMENT列属性，可设置唯一索引或非唯一索引
CSV：存储数据时，以逗号分隔数据项
可以将普通的CSV文件当作MySQL的表文件处理，不支持索引
可以用作数据交换的机制使用（csv文件可以用excel软件打开，也能用文本工具打开）
Memory：内存表
数据存储在内存中，表结构存储在frm文件中。要求数据是数据长度不变的格式，Blob和Text类型不可用。
支持哈希（HASH）索引和B+树索引。（哈希索引相等比较快，范围比较慢。默认为哈希索引，速度比B型树（BTREE）索引快。希望使用B树索引，可以在创建索引时选择使用）
表的大小是受限制的。max_rows和max_heap_table_size决定表的大小，创建表的时候指定。
索引
索引是排好序的快速查找数据结构
索引可以加快查询速度，但同时减低数据更新的速度
MySQL的InnoDB存储引擎中采用B+树数据结构设计索引
B+树结构如图（示例图）
建立索引结构首先要将数据按照建立顺序摆放，然后数据的存储是按页存储的，每个页的大小为16kb。B+树结构是多层目录的结构，每层目录都记录这下一层的页数据信息，记录了页码和他的最小值（索引列数据的最小值）。
结构有点类似磁盘的逻辑地址。
如下图，如果要想找数据值为20的数据，则数据会先从顶层开始查找，找到1，1小于20，再找到320，320大于20，说明数据在第一条数据指向的页中。然后再往下找，他指向的页号是30，然后找到页号30的页，找到1，小于20，找到5，小于20，找到12，小于20，找到209，大于20，说明数据在前一条数据指向的页。我们来到9号页，依次查找，最终找到想要的数据。实际查找目录和数据的时候，应该是用二分法找。下面这种索引就是聚簇索引的结构，最底层是真正的数据，高层都是目录。（页之间是双向链表，数据之间是单项链表）
B树结构和B+树类似，最大的区别就是B+树的非叶子节点不存放真正的数据，B树的非叶子节点会存放数据
Hash索引
Hash索引在进行= <> in 的比较中，时间复杂度为O(1)，是比较快的，但是在进行范围比较时，时间复杂度会变为O(n)，就是顺序查找。Hash索引不能用在重复值比较多的列中。（只在Memory存储引擎中支持）。InnoDB存储引擎中存在自适应Hash索引，当一个值经常被访问时，且满足某一些条件，便会将这个值放入Hash索引中。可以通过设置一个变量来开启自适应hash索引，默认是开启的
B+树索引无论在进行范围比较还是等值比较时，时间复杂度均为O(log₂n)
聚簇索引
在InnoDB中
聚簇索引是一棵B+树，最底层（叶子节点）存放的是真实的数据，上层存放的是目录
非聚簇索引也可以是一棵B+树，最底层（叶子节点）存放的是当前索引列的值和聚簇索引列的值，当使用非聚簇索引查询其他列的值时，需要有回表的动作，因为最底层只有聚簇索引和当前索引的值，没有其他列的值，其他列的值需要通过聚簇索引的值去聚簇索引中查找。
一定会有主键，没有设置主键时，系统会挑选一个唯一索引当作主键，没有唯一索引时，系统会自动生成一个列，当作主键（隐藏列）
在MyISAM中
没有聚簇索引，B+数中的叶子节点存放的是数据的地址。数据存入之后，不会按顺序存放。因为数据和索引是分开存放的。MyISAM的回表动作比InnoDB快很多，因为是拿着数据的地址直接去找数据
可以没有主键
InnoDB数据存储结构
适合建索引的字段
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
频繁作为where条件查询的字段
经常group by和order by的列
distinct字段需要创建索引
多表连接字段创建索引（多表连接条件建议不要超过三张）
使用列的类型小的创建索引（例：int能满足列的条件，就不要用bigint类型，类型大，占用空间就大，B+树就更高）
使用字符串的前缀创建索引
区分度高的，散列性高的列适合作索引
使用最频繁的列放在联合索引的左侧
在多个字段都要用到索引时，联合索引优于单值索引
索引要限制数量，建议单表不要超过6个
不适合建索引的字段
where中使用不到的字段
数据量小的表最好不要使用索引
有大量重复数据的列上不要创建索引
避免对经常更新的表创建过多的索引
不建议用无序的值作为索引（身份证，uuid，md5，hash，无序长字符串），在索引比较时需要转ASCII，插入数据时可能会导致页分裂
删除不再使用或者很少使用的索引
不要定义冗余或重复的索引
性能分析工具
last_query_cost（最后一次查询的成本）
show status like 'last_query_cost'
慢查询日志
相关变量
slow_query_log（是否开启慢查询日志）
long_query_time（慢查询阈值）
min_examined_row_limit（查询扫描过的最少记录数）
show global status like '%Slow_queries%';（显示慢查询的SQL数量）
MySQL默认不开启慢查询日志，慢SQL的默认阈值是10s，可以修改long_query_time调整阈值。（如果不是为了调优，不建议开启慢查询日志，开启会有性能影响）
慢查询日志分析工具（mysqldumpslow）
慢查询日志分析步骤
首先，登录ssh，进入MySQL的慢查询日志文件目录，默认是在/var/lib/mysql下
慢查询SQL分析完之后，可以将慢查询日志删除，然后做一个慢查询日志的刷新
若将slow参数去除，则刷新所有日志（redo，undo，binlog）
mysqladmin -uroot -p flush-logs slow
show global variables like '%profiling%'; # 查看SQL执行流程设置是否开启
set profiling=on; # 开启执行流程设置
show profile cpu,block io for query 6; # 查看第六条SQL的执行流程，包括cpu和磁盘io以及执行时长情况
show profile相关参数和注意事项
explain（分析查询语句）
describe和explain的功能一样，只是关键字不一样，我们通常使用explain分析查询语句
分析SQL，优化SQL主要看type，key_len，rows，extra
id
SQL中每有一个select关键字，就会有一个id（一般情况是这样，也有可能会更少，因为优化器会对你的语句进行优化，重写语句。还有在使用union对结果集合并去重时，会用到临时表，会多一条记录）
id相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
每个id代表一个独立的查询，id越少越好
select_type
SIMPLE：不包含union和子查询的SQL，都是simple
包含union的SQL，结果如图
SUBQUERY 子查询
DEPENDENT SUBQUERY 相关子查询
相关子查询可能会被执行多次
DEPENDENT UNION
DERIVED 派生表查询
partitions
代表查询的数据在哪个分区
type
执行查询时的访问方法
效率由前到后性能越来越差
system
当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM，Memory
const
根据主键查询或者唯一二级索引进行等值匹配时，访问方法是const
eq_ref
通过主键或者唯一二级索引连表查询，访问方式是eq_ref
ref
对普通索引进行等值比较时，访问方式是ref（不能有隐式转换）
ref_or_null
对普通索引进行等值比较时，该索引的值也可以是null时，访问方法是ref_or_null
index_merge
多个索引条件查询时（并集），会将索引合并成一个大索引使用
unique_subquery
子查询等值判断
range
使用索引获取范围区间的记录，访问方法是range，如下图
index
索引覆盖，使用联合索引，查询时使用的条件和需要查询的字段都在联合索引中，则称为索引覆盖（数据可以直接从索引中拿，不需要回表）
possible_key和key
possible_key表示可能会使用到的索引
key表示实际使用到的索引
实际开发中，possible_key越少越好（至少一个），因为多了，优化器要一个一个去判断，判断使用哪个索引的成本是最低的
key_len
实际使用到的索引长度（字节数）
这个值针对联合索引有一定的参考意义，对于联合索引，这个值越大越好
字符长度计算
ref
使用索引进行等值查询时，与索引列进行等值匹配的对象信息
rows
预估的需要读取的记录数，值越小越好
filtered
rows的百分比
连表查询时更有意义，代表了被驱动表要执行的次数
extra
No tables used：没有使用到表
explain select 1;
Impossible WHERE：不可能的条件
explain select * from s1 where 1 != 1;
Using where：全表扫描方式使用where
explain select * from s1 where common_field = 'a';
下面的情况也会显示Using where
Using index：覆盖索引
Using index condition：索引条件下推
Using where; Using join buffer（hash join）
连表没有用到索引
Using where; Not exists：使用条件，数据不存在
Using union ； Using where
Using filesort： 使用文件排序
字段没有索引，然后对这个字段进行查询排序，会提示这个（效率比较低下，看到这个提示，可以考虑加个索引，或者换一个字段进行排序）
Using temporary：使用临时表（在没有利用到索引时会出现这个）
小结
explain不考虑Cache
explain不能显示MySQL在执行查询时所做的优化工作
explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
部分统计信息是估算的，并非精确值
四种输出格式
传统格式
这种就是传统格式
JSON格式
TREE格式
执行完explain之后，执行show warnings，可以得到真正执行的语句（经过优化器优化之后的语句）
索引优化和查询优化
有哪些维度可以进行数据库调优？
索引失效、没有充分利用索引--建立索引
关联查询太多JOIN（设计缺陷或不得已的需求）--SQL优化
服务器调优及各个参数设置（缓冲、线程数等）--调整my.cnf
数据过多--分库分表
虽然查询优化的技术很多，但是大方向上可以分为物理查询优化和逻辑查询优化
物理查询优化是通过索引和表连接方式等技术来进行优化，重点需要掌握索引的使用。
逻辑查询优化是通过SQL等价变换提升查询效率，就是换一个查询写法，执行效率可能更高。
索引失效案例
全值匹配我最爱
当查询存在多个条件时，建立联合索引会比各个条件单独建立索引效果会更好。此时优化器会优先使用联合索引
最佳左前缀规则
联合索引，要使用上，必须要按字段顺序先后匹配，例如：id_name_age三个字段组成的联合索引，必须要有id条件才能用上这个索引，只有name和age是用不上这个联合索引的。如果有id和age，则只有id会被索引匹配，age不会，匹配长度是id的长度（key_len）
主键插入顺序
索引是有序的b+树，数据插入的时候最好是递增的，若是无序的，则会影响性能。因为无序的数据插入，索引要重新排序，可能会导致页分裂
计算、函数、类型转换（手动或自动）都会导致索引失效
函数案例
select * from table_name where left(name,3) = 'abc';
计算案例
select * from table_name where age+1 = 20;
类型转换案例
select * from table_name where name = 20;
范围条件右边的列索引失效
联合索引，其中一个字段使用了范围条件，那个字段后面的索引会失效（无法使用这个联合索引），如下图。
这个查询只用到了age和classId的索引匹配（因为key_len是10），name没有用到
不等于（!=或者<>）索引失效
例子
select * from table_name where name <> 'abc';
is null可以使用索引，is not null不能使用索引
例子
select * from table_name where name is not null;
like以通配符%开头索引失效
例子
select * from table_name where name like '%abc';
or 前后存在非索引的列，索引失效
例子（假设age上有索引，name上没有索引。若两个字段都有索引，则能够使用到索引）
select * from table_name where name = '张三' or age = 20;
数据库的表字符集统一使用同一个（建议utf8mb4）
因为字符集不一样的话，不同的表之间的字段做比较，会进行转换，从而导致索引失效
外连接和内连接查询优化
外连接
连接条件加索引，如果只有一张表可以加索引，则给被驱动表加索引
例：
这个例子中的被驱动表是b表
select * from a left join b on a.id = b.id;
内连接
查询优化器可以决定谁是驱动表，谁是被驱动表
若连接条件只有一个表中有索引，则有索引的表会被作为被驱动表
在内连接中，若连接条件都有索引，则查询优化器会将数据量少的一张表作为驱动表
小表驱动大表
不管是内连接还是外连接，查询优化器都有可能会改变驱动表或者被驱动表
Simple Nested-Loop Join（简单嵌套循环连接）
就是逐行嵌套循环匹配
Index Nested-Loop Join（索引嵌套循环连接）
如图，驱动表需要全表扫描一次，被驱动表不要扫描，直接查索引，有的话就直接匹配
Block Nested-Loop Join（块嵌套循环连接）
在简单循环连接的基础上进行改进，加载数据进行匹配时，不是一条一条加载，而是多条数据一次性加载，然后进行匹配，减少了io次数
整体效率比较：INLJ>BNLJ>SNLJ
从8.0.20开时，已经废弃了BNLJ，改用了Hash Join
小结
子查询优化
子查询结果会被存放在临时表中，用完再自动销毁。会消耗cpu和io资源
临时表不会存在索引
结果集比较大的子查询，查询性能影响也大
排序优化
排序有两种方式，FIleSort和Index
索引是已经排好序的，使用索引进行排序很节省时间
排序的时候，若查询的数据量比较大，有可能不会使用索引，因为使用索引，还需要回表（除非你查询的字段就是排序索引所包含的字段）
排序多个字段时，都是倒序或者都是升序，才能使用上索引（如果这几个字段组合成了联合索引的话）
排序算法
双路排序是先排序单个字段，再根据字段值去找那条数据
单路排序是直接那到整条数据，对那个字段进行排序
单路排序更快，如果sort_buffer足够的话
可以提高sort_buffer_size，提高没有索引情况下的排序效率（默认是1m）
分组优化
和排序优化差不多
能不分组和排序的语句就不要分组和排序，尽量在程序中实现分组和排序，SQL中实现耗费数据库的CPU资源
分页查询优化
直接按数据偏移量去查询，数据量大的时候查询会很慢
可以先查出来需要查询到数据的id（这个查询可以使用到索引）
然后再用这些id去当前页的数据
覆盖索引
当我们查询使用到二级索引时（非聚簇索引），查询的字段在索引中有记录时，就不需要回表操作，这种就叫做覆盖索引
如下图，这个查询，查了所有的字段，而索引字段只包含主键和czsj两个字段，需要查询其他字段的信息，则需要进行回表操作
下面这个情况就是索引覆盖，查询使用了索引，不需要回表操作（JBXX_ID是主键字段）
优点
可以免去回表的操作
可以将随机io变成顺序io
缺点
占用资源
索引下推（ICP Index Condition Pushdown ）
举例1：
第一个条件可以使用上索引，得到一个结果集之后不会立即回表，而是对这个结果集进行第二个条件的筛选，这就叫索引下推（这个例子要满足一个条件，就是两个筛选条件刚好是索引字段，而第二个条件的写法用不上索引）
举例2（更贴合实际情况，或者说更典型）：
这里是一个联合索引，部分条件用上了索引，其他条件在联合索引中，但是用不上，此时便会通过能用上索引的字段先筛选一遍，得到的结果集再对剩余的字段进行筛选（可以筛选的原因是联合索引，索引中包含这些字段的信息）。需要这个查询要进行回表操作才算
开启索引下推（默认是开启的，要关闭把on替换成off）
set optimizer_switch = 'index_condition_pushdown=on';
icp的使用条件
其他查询优化
exists和in的区分
遵循小表驱动大表的原则
当in里面的结果集比A表小时，适合用in查询
select * from A where id in（select wjid from B）
当A表比B表或者说exists里的结果集小时，适合用exists查询
select * from A where exists (select wjid from B where B.wjid = A.id)
count(*)、count(1)、count(具体字段)效率
首先，count(*)和count(1)的效率基本上是一样的，统计可以使用count(1)也可以使用count(*)。使用这两个方法统计，数据库会自动选择一个占用空间比较小的二级索引加载到内存中进行统计
在MyISAM存储引擎中，表的统计是有一个字段维护的，统计表数据时间是O(1)级别，直接取出那个字段的值就行了。
在InnoDB中，没有那个字段，统计需要循环+计数的方式进行统计，时间是O(n)级别的
count(具体字段)，可以选择占用空间最小的二级索引进行统计，效率和其他两个是一样的，选择其他的会占用更多的内存
关于select *
不建议使用select ，使用select 之后，MySQL在执行的时候，会将*转换成具体的字段（会从系统表查询这张业务表有哪些字段，这个过程会消耗资源）
使用 * 一般不能覆盖索引
关于limit 1
针对会进行全表扫描的语句时，使用limit 1之后，就不会全表扫描，返回一条结果
如果数据表建立了唯一索引，可以通过索引进行查询，不会全表扫描的话，就不需要加limit 1了
多使用commit
在事务该提交的时候，尽量多提交事务
commit所释放的资源：
回滚段上用于恢复的信息
被程序语句获得的锁
redo / undo log buffer中的空间
管理上述3种资源的内部花费
淘宝数据库主键设计
主键设计的思路，保证唯一性，单调递增
默认的UUID是无序的，并且是唯一的。
下图是它的组成部分
我们可以将时间低位，中位和高位更改顺序，这样我们就能得到一个单调递增并且有序的UUID了，数据库还提供了二进制的数据存储方式，占用16个字节，如果采用字符形式存储，占用36个字节（包含四个-）
数据库设计规范
范式
超键：能唯一标识一条信息的属性集（可能会有多余的属性，比如id和age也是超键）
候选键：超键中去除多余的属性（比如，身份证号，学生证号）
主键：从候选键中选一个作为主键
主属性：包含在任意候选键中的属性称为主属性
非主属性：除了主属性，其他都是非主属性
第一范式
每个字段都具有原子性，不可拆分
举个例子
其中user_info还能再拆分，这就不符合第一范式
第二范式
满足第一范式，并且每条数据都是可唯一标识的，也就是有主键，其他的非主键字段，都必须完全依赖主键，不能只依赖主键的一部分
举例
这是满足第二范式的例子
这是不满足第二范式的例子以及不满足第二范式可能会带来的一些问题
第三范式
满足第二范式，并且数据表中的所有非主键字段不能依赖于其他非主键字段。
例子
第一张表中的设计不满足第三范式，因为商品类别名称是依赖于商品类别id的，把商品类别名称删除就满足第三范式了
反范式化
这里的第一张表就是反范式化，增加商品类别名称，增加了冗余字段，不用连表查询，加快了查询速度
反范式化的问题和适用场景
BCNF（巴斯范式）
在第三范式的基础上消除了主属性对候选键的部分依赖或传递依赖关系
下面这张图的表就是符合第三范式的，但是不符合巴斯范式，要将它拆分成仓库表和库存表就符合巴斯范式
第四范式
第五范式
数据库其他调优策略
MySQL配置优化
innodb_buffer_pool_size：InnoDB类型表和索引的最大缓存
key_buffer_size：索引缓冲区的大小
table_cache：同时打开的表的个数
query_cache_size：查询缓冲区的大小
query_cache_type：查询缓冲类型。0代表关闭查询缓冲。1表示所有查询都使用缓冲（除非语句指定SQL_NO_CACHE）。2表示只有查询语句中使用SQL_CACHE才会使用缓冲。
sort_buffer_size：需要进行排序的线程分配的缓冲区大小。order by和group by都能使用上
join_buffer_size：联合查询所使用的缓冲区大小。每个连接独享的内存
read_buffer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区大小
innodb_flush_log_at_trx_commit：何时缓冲区的数据写入日志文件
0：每秒1次写入日志文件并写入磁盘
1：每次提交事务时写入日志文件并写入磁盘（默认值）
2：每次提交事务写入日志文件，每秒写入磁盘
innodb_log_buffer_size：InnoDB的事务日志所使用的缓冲区
max_connections：允许连接到MySQL数据库的最大数量。默认是151
back_log：用于控制MySQL监听TCP端口时设置的挤压请求栈大小
thread_cache_size：线程池缓存线程数量的大小
wait_timeout：一个请求的最大连接时间
interactive_timeout：服务器再关闭连接前等待行动的秒数
优化数据库结构
拆分表：冷热数据分离
增加中间表，将经常需要连表查询，并且不会频繁更新的字段合并构成一张新的表，我们查询的时候就不需要连表查询了，直接单表查询就行了。不过后续要保证数据一致性
增加冗余字段
优化数据类型，选择符合要求的最小的数据类型。
对于非负数的整型，优先使用UNSIGNED来存储。
既可以使用文本类型也可以使用整型，选择整型
避免使用TEXT、BLOB。一定要用，要额外建一张表存储
避免使用ENUM。修改ENUM值需要使用ALTER语句。order by操作效率低
使用TIMESTAMP存储时间
使用DECIMAL代替FLOAT和DOUBLE
优化插入记录速度
MyISAM存储引擎中
禁用索引
禁用唯一性检查
使用批量插入
使用LOAD DATA INFILE
InnonDB存储引擎中
禁用唯一性检查
禁用外键检查
禁用自动提交
使用非空约束。可以给列设置默认值，字段如果存在NULL，进行比较时，需要比较NULL。而且列可以为NULL，需要额外的空间来存储。
分析表、检查表与优化表
分析表
可以刷新索引的区分度，在查询优化器选择索引时，会判断索引的区分度。分析表时，会对表加一个只读锁
analyze table table_name;
检查表
check table table_name;
优化表
将表中的碎片化文件重新整理，优化完之后可以减少磁盘的占用。执行过程中会加只读锁
optimize table table_name;
大表优化
限定查询范围
增加查询条件或者限制查询的数量
读写分离
数据库部署主从，主库负责写，从库负责读，降低数据库的压力
垂直拆分
将一个表拆成多张表，然后进行连表查询
水平拆分
将数据拆分，分配到不同的数据库服务器中，降低单台服务器的请求压力（分片集群，水平拆分最好配合分库，才有意义）
事务
事务的四大特性（ACID）
原子性（atomicity）：指不可分割的工作单位
一致性（consistency）：总体一致性。举例：A有500块钱，B有500块。总计1000。无论A怎么转账给B，A的余额都不会小于0，并且A和B的总余额都等1000
隔离性（isolation）：一个事务的执行不能被其他事务干扰。和多线程类似
持久性（durability）：持久性是指一个事务一旦被提交，他对数据库中数据的改变是永久性的。持久性是通过事务日志来保证的，重做日志（redo）和回滚日志（undo）
事务的状态
活动的（active）
正在执行的过程
部分提交（partially committed）
事务中的操作都执行完了，但改变的数据还没有刷新到磁盘中
失败的（failed）
活动的或者部分提交的状态，遇到某些错误，就是失败的状态
中止的（aborted）
当事务变为失败的状态之后，先回滚，然后会变成中止的状态
提交的（committed）
部分提交完成只会就是提交的状态
显式事务和隐式事务
显式事务
使用start transaction 或 begin开启
start transaction后面可以跟：read only / read write（默认） / with consistent snapshot
保存点
savepoint s1;
删除保存点
release savepoint s1;
隐式事务
autocommit;
set autocommit = false;
当自动提交开启时，每条dml语句都会自动提交，都是单独的事务
事务隔离级别
脏写
A事务修改了数据1，B事务也修改了数据1，A事务先提交了，然后B事务回滚了，导致A事务的修改没生效，这就是脏写。
脏读
A事务修改数据1，B事务也修改数据1，B事务先完成了修改动作，但是没有提交，然后A事务读取数据1，读取到的是B事务修改之后的值，这就是脏读
不可重复读
A事务读取了一个字段，B事务更新了该字段，之后A事务再次读取这个字段，发现值不一样了。这就是不可重复读。
幻读
A事务读取了一张表的数据，B事务插入了一些数据，之后A事务再次读取这张表，发现表中多了一些数据，这就是幻读
SQL中的四种隔离级别
读未提交
读已提交
可重复读
可串行化
查看事务的隔离级别
show variables like '%transaction_isolation%'
select @@transaction_isolation
设置事务的隔离级别
SET [GLOBAL | SESSION] TRANSACTION_ISOLATION = ''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
MySQL事务日志
事务的隔离性由锁机制实现
事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证
redo log称为重做日志。在事务提交之后，这个事务里所有的对数据的操作都会被记录到日志文件中。以 防数据库宕机导致这些操作丢失了（事务提交之后，只是修改了被加载到内存中的数据，内存中的数 据会以一定的频率更新到磁盘文件中）
undo log称为回滚日志
undo log会在数据更新前记录相反的操作，比如，在删除一条数据前，会先记录这条数据，回滚时执行新增语句。在新增数据前，会记录新数据主键，回滚时执行删除操作。在更新数据前，会记录更新前的值，回滚操作会把值更新成旧值。
undo做的事情是将数据回滚到和之前的状态一样，并不是物理层面的回滚，是反向操作达到回滚的效果。
undo还有个作用是mvcc
MySQL锁机制
事务的隔离性由锁来实现
对数据的操作类型划分
读锁/共享锁（S锁） 写锁/排他锁（X锁）
锁粒度划分
表级锁
表级别的S锁、X锁
意向锁
自增锁
MDL锁
行级锁
Record Locks
Gap Locks
Next-Key Locks
插入意象锁
页级锁
对待锁的态度划分
悲观锁 乐观锁
加锁方式
隐式锁 显式锁
其他
全局锁 死锁
对读取的记录加S锁
SELECT ... LOCK IN SHARE MODE;
#或者
SELECT ... FOR SHARE；#8.0新增语法
对读取的记录加X锁
SELECT ... FOR UPDATE;
表锁
InnoDB中对表加锁
LOCK TABLES t READ 对t表加S锁
LOCK TABLES t WRITE 对t表加X锁
意向锁
当我们在给某一行数据加上排它锁时，系统会自动给它的更大一级的空间增加意向锁，比如表级别或者页级别。当其他事务需要增加表级锁或者页锁时，就不需要检查每条数据（检查是否存在行锁），而是检查是否存在意向锁
自增锁
主键设置为自增时会存在的锁
元数据锁
它属于表锁范畴。当对一个表做增删改查操作时，加MDL读锁。当对表结构变更操作时，加MDL写锁。元数据锁由数据库自动加上，不需要我们手动操作。这个锁能保证我们在操作数据时，表结构的一致性和完整性
行锁
行锁就是对数据行进行加锁。行级锁只在存储引擎层实现。
优点：锁力度小，发生冲突概率低，可实现的并发度高
缺点：锁的开销较大，加锁比较慢，容易出现死锁
记录锁
针对某一条记录加锁
间隙锁
间隙锁是为了防止在一个范围内插入数据而生的。若没有间隙锁，在一个事务在对一些数据进行操作时，另一个事务往这些数据范围内插入新的数据，就会导致幻读
如图，下面两个语句都是给数据加间隙锁，给id为3-8之间加锁，此时这个范围内的id是无法添加数据的
select * from student where id = 5 for update;
select * from student where id = 5 lock in share mode;
临键锁
记录锁和间隙锁的合体，既能保护该条记录，又能保护该记录前面的间隙
插入意向锁
当一个事务在插入一条数据时，它因为其他事务加了间隙锁而在等待时，就会生成一个锁，叫插入意向锁
页锁
乐观锁、悲观锁
悲观锁
通过数据库的锁来实现。共享资源每次只给一个线程使用，其他线程阻塞，用完后再把资源转让给其它线程。
注意：select ... for update语句执行过程中所有扫描的行都会被锁上，因此在MySQL中用悲观锁必须确定使用了索引，而不是全表扫描，否则会把整个表锁住
乐观锁
不采用数据库本身的锁机制，而是通过程序来实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。
乐观锁的版本号机制
在表中设计一个版本字段，第一次读取的适合，会获取version字段的取值，然后对数据进行修改，会更新version字段（update ... set version=version+1 where version=version），此时如果已经有事务对这条数据进行了更改，修改就不会成功。此时就能保证我们更改数据时，获取的值时最新的值
乐观锁的时间戳机制
和版本号同理。判断时间戳是否相同
1、乐观锁适合读操作多的场景，程序实现的，不存在死锁问题。
2、悲观锁适合写操作多的场景
显式锁、隐式锁
全局锁和死锁
全局锁是对整个数据库实例加锁，当你需要让整个库处于只读状态的时候，可以使用这个指令。使用场景：全库逻辑备份。
flush tables with read lock
死锁是两个事务持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁
多版本并发控制（MVCC）
mvcc实现依赖于：隐藏字段、Undo Log（多版本）、Read View（控制）
不加锁的简单select都属于快照读，例如
select * from user where ....
当前读就是读取数据的最新版本
mvcc解决了幻读的问题（同时也解决了脏读和不可重复读）
隐藏字段、Undo Log版本链
隐藏字段：trx_id（操作当前数据的事务id）、roll_pointer（旧版本数据信息的指针）
Read View：是事务A在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造一个数组，用来记录并维护系统当前活跃事务的ID（“活跃”指的是启动了但还没提交）
READ UNCOMMITTED隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了
SERIALIZABLE隔离级别的事务，InnoDB规定使用加锁的方式来访问记录
READ COMMIT和REPEATABLE READ隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如另一个事务修改了但未提交，则不能直接读取最新版本的数据。核心问题是需要判断版本链中哪个版本是当前事务可见的，这是Read View要解决的主要问题。
Read View中包含的4个比较重要的内容，分别如下：
creator_trx_id，创建这个Read View的事务id
trx_ids，在生成Read View时当前系统中活跃的读写事务的事务id列表
up_limit_id：活跃的事务中最小的事务id
low_limit_id：生成Read View时系统应该分配给下一个事务的id值，low_limit_id是系统最大的事务id
在read commit事务隔离级别下，事务中的每次查询，都会生成read view，然后根据read view中的版本数据信息去判断哪些数据是自己可见的
在repeatable read事务隔离级别下，只有事务的首次查询，才会生成read view，事务中的其他查询，都是根据这个read view去判断哪些数据是自己可见的
日志
慢查询日志：
记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化
通用查询日志：
记录所有执行连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助
错误日志：
记录MySQL服务的启动、运行或停止时出现的问题
二进制日志：
记录所有更改数据的语句，可用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损恢复
中继日志：
用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的中间文件，从服务器通过读取中继日志的内容，来同步主服务器上的操作
数据定义语句日志：
记录数据定义语句执行的元数据操作
评论区