声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章
              2. 由于是个人总结, 所以用最精简的话语来写文章
              3. 若有错误不当之处, 请指出

<>一、基础:

一个SQL语句的执行流程:

连接器 -> 缓存 -> 解析器 -> 优化器 -> 执行器 -> 存储引擎 -> 文件

缓存 不推荐使用, 因为更新缓存时, 一表数据变动, 整表的缓存都失效, 粒度太大

解析器: 词法分析, 语义分析

优化: 优化索引等

存储引擎: 将执行器的指令落实到数据文件上, 有InnoDB, MyISAM, Memory, Archive(档案, 只增不改不删)等

删除操作:

detele 一行一行删除指定行数据, 可回滚

truncate 直接删除表全部数据所以效率高, 不可回滚

drop 删除表结构

MySQL8新特性:

* 支持开窗
* with t1 as select … 语句,相当于临时表
* DDL语句也支持事务了
BinLog日志:

MySQL的BinLog三种模式:

*
STATEMENT 记录更新的操作日志 (对于一些系统函数如Date( ),主从执行结果不一致)

*
ROW 记录更新后实际的每一行数据

*
MIXED 混合, 优先用STATEMENT, 若问题再用ROW, 但是这个功能现在实现不完善

主从复制:

主从复制, 读写分离

优点

* 提高了并发性能
* 从机对主机相当于有备份 提高了数据安全性, 提供了高可用 高可靠保障
缺点:

* 数据一致性问题
* 延时问题
用到的线程:

master: binlog线程, 记录下所有改变了数据库数据的语句,放进master上的binlog中

slave: io线程, 在输入start slave之后,从master上拉取 binlog 内容放进自己的relay log中

slave: sql执行线程, 执行relay log中的语句

三范式:

*
第一范式: 字段不可再分 , 比如5台电脑这个字段, 可以拆成 商品和数量 两个字段

*
第二范式: 在第一范式的基础上, 其他字段必须全部依赖主键,而不能部分依赖; 比如(a,b)->y, 那么a就是y的部分的依赖; x->y,
x就是y的全部依赖

*
第三范式: 在第二范式的基础上, 其他字段必须直接依赖主键,而不能间接依赖, 比如a->b->c, 那a就是c的部份依赖

三范式好处: 规范表设计,避免产生冗余字段

坏处:

* 一些冗余字段可以提高效率,比如统计月销售额,难道每一遍都sum一下? 而冗余字段sumGMV, 可以做一个累加记录,查月销售额时直接select
sumGMA where 某一行就可以了
* 表拆分的太散,维度表大大增加, join维度表时时很耗时耗资源的
触发器:

类似于监听器 和 钩子程序, 当你对A表操作时, 触发器可以自动往B表里进行相应的同步(保持相对关系)操作

为什么避免使用外键:

* 在主表从表没有全部更新完时, 这行数据被锁住, 降低并发
* 外键字段不可使用触发器进行监听
* 迁移数据库时, 如把MySQL迁移到HBase; 而HBase不支持外键,这时没有外键约束了,而又没有开发人员额外编写应用程序维护,就会出问题
所以, 逻辑上认为它是外键, 但别显示建外键; 约束啥的靠开发人员额外编写应用程序维护, 在JavaEE的Service层里去校验是否约束合法

MySQL中MyISAM与InnoDB的区别:

*
事务: InnoDB支持事务,而MyISAM不支持事务

*
乐观锁: InnoDB支持MVCC, 而MyISAM不支持

*
外键: InnoDB支持外键,而MyISAM不支持

*
锁粒度: InnoDB支持行级锁,而MyISAM支持表级锁

*
聚簇索引方面: 在InnoDB里, 主索引是聚簇索引, 二级索引/辅助索引 是非聚簇索引

​ 在MyISAM里, 主索和二级索引 全都是非聚簇索引

主键一般用自增ID还是UUID?

使用自增ID的好处:

* 字段长度较uuid会小很多。
* 数据库自动编号,按顺序存放,利于检索
使用自增ID的缺点:

* 因为是自增,在某些业务场景下,容易被其他人查到业务量。
* 竞争自增锁会降低数据库的吞吐能力
UUID:通用唯一标识码,是基于当前时间、计数器和硬件标识等数据计算生成的。

使用UUID的优点:

* 无需担心业务量泄露的问题。
* 可以在应用层生成,提高数据库的吞吐能力。
使用UUID的缺点:

* 因为UUID是随机生成的,所以会发生随机IO,查找时不方便
* UUID占用空间较大
* UUID之间比较大小 比 自增ID的比较大小慢
一般情况MySQL主键为自增ID(username设成unique key, 用额外的自增id作为主键)

因为在MySQL的InnoDB存储引擎中,主键索引是一种聚簇索引;

主键索引的B+树的叶子节点按照顺序存储了主键值及数据,

如果主键索引是自增ID,只需要按顺序往后排列即可顺序IO,

而UUID太离散不利于排序和 查找,只能随机IO

字段为什么要设置成not null?

* NULL占用空间, 空串不占用空间, 所以优先用not null + 空串 的方式
* NULL值会影响一些函数的统计, 如count遇到NULL值, 这条记录不会统计在内
* NOT IN子查询 在有NULL值的情况下, 返回的结果都是空值
SQL执行顺序:

<>二、SQL编写:

琐碎的知识点:

* ON DUPLICATE KEY,没有就插入, 否则就更新 INSERT INTO app_use_report (device_id) VALUES
(1), ON DUPLICATE KEY UPDATE device_id= VALUES(2)
*
分组前会先排序

*
union会去重, 同一个表的元素也会去重

*
group by有去重功能

*
distinct 只能对后面紧跟的一个字段去重, 跟了两个时其实只对第一个有效; 想去重多个字段就用group by

*
=用来判断非NULL值才有效

*
<=>是既能判断NULL值,又能判断非NULL值

*
函数可以嵌套使用, 如count(if(score>=60,1,0))

*
分组后的select字段, 只能为3种情况:

* 分组字段
* 聚合函数
* 常量表达式, 如 select 1+2 from …
*
select 任何值+NULL 和都为NULL

*
sum(字段) 会忽略空值, avg, count…等聚合函数也一样

*
sum(*) 不会忽略空值

*
MyISAM存储引擎,count(*)最高
InnoDB存储引擎, count(*)和count(1)效率差不多,都>count(字段)

因为count(字段)还要每一行都去判断是否为NULL, 所以降低了执行速度

常用函数:

*
字符函数
concat: 连接
substr: 截取子串
upper: 变大写
lower: 变小写
replace: 替换
length: 获取字节长度
trim: 去前后空格
lpad: 左填充
rpad: 右填充
instr: 获取子串第一次出现的下标

*
数学函数
ceil: 向上取整
round: 四舍五入
mod: 取模
floor: 向下取整
truncate: 截断
rand: 获取随机数,返回0-1之间的小数

*
日期函数

now: 返回当前日期+时间
year: 返回年
month: 返回月
day: 返回日
date_format: 将日期转换成字符
curdate: 返回当前日期
str_to_date: 将字符转换成日期
curtime: 返回当前时间
hour: 小时
minute: 分钟
second: 秒
datediff: 返回两个日期相差的天数
monthname: 以英文形式返回月

*
其他函数
version: 当前数据库服务器的版本
database: 当前打开的数据库
user: 当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’): 返回该字符的md5加密形式

5、流程控制函数

①if(score>=60,‘success’,‘fail’)
②case两种写法:

*
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2

else 值n
end

*
case
when 条件1 then 值1
when 条件2 then 值2

else 值n
end

视图:

视图相当于临时表, 但只能读
-- 创建视图 create view 视图名 as 查询语句; -- 修改视图 方式一: create or replace view 视图名 as
查询语句; 或 alter view 视图名 as 查询语句 -- 删除视图 drop view 视图1,视图2,...; -- 查看视图 desc 视图名;
show create view 视图名;
变量:
一、系统变量

默认是session级别

*
查看系统变量

show (global/session) variables like ‘xxx’

​ select @@(global/session).变量名 / select @@变量名

*
为系统变量赋值

set **@@global.**变量名=值;
set @@变量名=值

或 set (global/session) 变量名=值

二、自定义变量
都是session级别
位置:begin end里面,也可以放在外面
使用:

①声明并赋值 / 更新:

* set @变量名=值
* set @变量名:=值
* select @变量名:=值
②更新值
另一种方式: select xxx into @变量名 from 表

③查看变量
select @变量名

局部变量:
只能放在begin end中,而且只能放在第一句

存储过程&函数:

存储过程既可以读, 又可以写

但一般函数用作读, 存储过程用作写

* 存储过程:
一、创建
create procedure 存储过程名(参数模式 参数名 参数类型) begin 存储过程体 end
注意:

* 参数模式:in、out、inout,其中in可以省略
* 存储过程体的每一条sql语句都需要用分号结尾
二、调用
call 存储过程名(实参列表)

*
调用in模式的参数:call sp1(‘@name’);

*
调用out模式的参数:
set @name; call sp1(@name); select @name;
*
调用inout模式的参数:
set @name=值; call sp1(@name); select @name;
三、查看
show create procedure 存储过程名;
四、删除
drop procedure 存储过程名;

* 函数:
一、创建
create function 函数名(参数名 参数类型) returns 返回类型 begin 函数体 end
注意:函数体中肯定需要有return语句

二、调用
select 函数名(形参列表);

三、查看
show create function 函数名;
四、删除
drop function 函数名;

<>三、事务:

ACID:

* 原子性 这里的原子并非原语操作, 而是说事务里的多个命令同生共死, 要么都执行, 要么都不执行
* 一致性 一个事务可以使数据 从一个一致状态切换到另外一个一致的状态, 比如转账时 总金额不变
* 隔离性 一个事务不受其他事务的干扰,多个事务互相隔离的
* 持久性 一个事务一旦提交了,则永久的持久化到本地, 不可撤销
insert、update、delete本身就是一种隐式的事务,只不过它们是单条命令且自动提交

事务操作:

*
开启事务

set autocommit=0;
(begin) 或者 (start transaction) 可以省略

*
设置回滚点(非必需): savepoint 回滚点名

*
提交:commit
回滚:rollback

rollback to 回滚点名

并发事务问题: 多个事务 同时 操作 同一个数据库的相同数据时会引发问题:

* 脏读: 一个事务读取了其他事务还没有提交的数据
* 不可重复读: 一个事务多次读取,结果不一样, 感知到了其他事务"update"的数据
* 幻读: 感知到了其他事务"insert"的数据
隔离级别:
脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×

read committed: 读已提交 √ × ×
repeatable read(默认): 可重复读 √ √ ×
serializable: 串行化 √ √ √

<>四、锁:

常见的是7种锁,还有一种不常见的预测锁

*
行锁(Record Locks) 行级锁,悲观锁

单个行记录上的锁

被锁的字段必需得建立了索引并且是主键索引或唯一索引, 否则会退化成临键锁

查询语句必须为精准匹配=,不能为>、<、like等,否则也会退化成临键锁

若语句中没用到任何索引,则会退化成表锁

如select * from user where id = 1 for update

*
间隙锁(Gap Locks) 行级锁,悲观锁

锁定一个区间,但不包括记录本身

被锁的字段必需得建立了唯一索引, 否则会退化成临键锁

若语句中没用到任何索引,则会退化成表锁

select * from user where id betwen 1 and 10 for update;

锁定了(1,10)区间

*
临键锁(Next-key Locks) 行级锁,悲观锁

锁定一个区间,并且锁定记录本身

被锁的字段必需得建立了非唯一索引

若语句中没用到任何索引,则会退化成表锁

如已有age={10,24,32,45}时潜在的临键锁: (-∞, 10], (10, 24], (24, 32], (32, 45], (45, +∞]

操作age=24, select * from user where age = 24 for update时, InnoDB 会获取该记录行的临键锁
(10,24], 左开右闭

临建锁=特殊间隙锁+行锁, 且间隙不是直接指定,而是根据表中已有的数据间接自动指定的

*
意向共享锁/意向排他锁(IS/IX) 表级锁,悲观锁; 是取得共享锁/排他锁的前置条件

* (IS)意向共享锁 Intention Shared Lock:当事务准备在某条记录上加S锁时,需要先在表级别加一个IS锁。
* (IX)意向排他锁 Intention Exclusive Lock:当事务准备在某条记录上加X锁时,需要先在表级别加一个IX锁。
意向锁和意向锁之间是不会有竞争冲突的

*
(读)共享锁/(写)排他锁(S/X) 行级锁,悲观锁

* 共享锁是一个事务并发读取某一行记录所需要持有的锁。针对同一份数据,多个读操作可以同时进行而不会互相影响;
* 排他锁是一个事务并发更新或删除某一行记录所需要持有的锁。当前写操作没有完成前,它会阻断其他写锁和读锁;
读锁会阻塞写,但是不会阻塞读。而写锁则会把其他线程的读和写都阻塞

S锁和IS锁之间是不会有竞争冲突的, S锁和S锁之间也不会有竞争冲突

其他的 X & X, X & IX, X& IS, S & IX 都是有竞争冲突的

*
插入意向锁(Insert Intention Locks) 特殊的间隙锁,悲观锁

*
自增锁(Auto-inc Locks) 表级锁;

AUTO_INCREMENT自增字段, 是一种特殊的间隙锁,但不同于间隙锁的是,该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间,那么插入意向锁锁住的就是一个点。

划分锁:

* 按 锁粒度来分:(锁定粒度依次递增, 锁开销递增, 并发度递减)
* 行级锁 InnoDB 中的行级锁的实现依赖于索引,一旦加锁操作没用到索引,那么就会退化为表锁
* 行锁
* 间隙锁
* 临键锁
* 页级锁 一次锁定相邻的一组记录
* 表级锁
* 按 共享策略来分:
* 共享锁
* 排他锁
* 意向共享锁
* 意向排他锁
* 按 加锁策略来分:
* 乐观锁
* 悲观锁
* 其他:
* 自增锁
MySQL 如何实现悲观锁和乐观锁?

* 乐观锁:更新时带上版本号(CAS更新, MVCC)
* 悲观锁:使用共享锁和排它锁,select...lock in share mode, select…for update
MySQL如何解决不可重复读?

用MVCC(基于乐观锁)来解决!

日志:

* undolog日志 (回滚)
* redolog日志 (事务内记录日志操作(BinLog是事务提交后), 实现重做操作,保证事务的持久性)
MVCC在每条数据后面加了隐藏的两列(创建版本号和删除版本号),每个事务在开始的时候都会有一个递增的当前事务版本号

为了避免查询到旧数据或已经被其他事务更改过的数据,需要满足如下条件

*
查询时当前事务的版本号需要>=创建版本号create_version

*
查询时当前事务的版本号<删除的版本号delete_version,或者当前删除版本号delete_version=NULL

即 (create_version <= current_version < delete_version)

或 (create_version <= current_version && delete_version-=NULL)

MySQL如何解决幻读?

幻读问题:

在T2 insert 并提交 后, T1直接select是不会查到新数据, 这里没有幻读问题
在T2 insert 并提交 后, T1 update可以看到跟新数据有关,这里有幻读问题,
并且之后的select就可以看到上次update时同步的最新数据

*
快照读:生成一个事务快照(ReadView),之后都从这个快照获取数据。普通 select 语句就是快照读。

对于快照读,MVCC因为从ReadView读取, 所以必然不会看到新插入的行, 所以就解决了幻读的问题。

*
当前读:读取数据的最新版本。常见的 update/insert/delete、还有 select ... for update、select ...
lock in share mode 都是当前读

MVCC 是无法解决的, 需要使用悲观锁 Next-Key Lock 或 Gap Lock (MySQL根据sql语句 自动选择用哪种锁)来解决。

如 select * from user where id < 10 for update 排他锁,

当使用了 Gap Lock 时,Gap 锁会锁住 id < 10 的整个范围,因此其他事务无法插入 id < 10 的数据,从而防止了幻读。

死锁:

发生条件: 两个事务互相把持对方的锁, 且都不愿意释放已有的锁, 对于内层新的锁 也不愿意tryLock而是一根筋非要Lock

如何避免死锁:

* 不同事务读取相同的多张表时, 按顺序读取
* 使用较低的隔离级别
* 合理选择事务的大小
* 尽量命中索引, 使行级锁能工作,缩小锁的粒度减少冲突
<>五、索引:

索引, 是一类数据结构, 使无序数据变得有序可寻, 将随机IO变成顺序O, 避免全表扫描, 提高查找效率

索引缺点:

* 索引要占用磁盘空间, 而且还较大
* 当对表的数据进行增删改的时候,也要修改索引,执行速度受到影响
没用上索引时会导致:

* 查询慢
* 行锁退化为表锁(InnoDB的行锁是基于索引进行实现的)
索引数据结构:

* 哈希索引(InnoDB用到): 精确查找最快, 但范围查找模糊查找时由于HashCode没规律, 就只能全表扫描了
* B树索引: 多叉树查找, 相当于二分或三分查找, 但是范围查找时要退回到父级节点再找兄弟结点, 回溯效率低, 非叶子节点也存数据
* B+树索引(InnoDB用到): 在B树的基础上, 只有叶子结点存实际数据, 叶子节点间构成了一个单链表, 范围查找很快; 缺点是树的高度比B树略高
B树B+树同时也是平衡树, 是2-3树(有的节点存两个值,有3个孩子) 是为了降低树的高度, 使查询更快, 节约IO损耗

红黑树是一种平衡不是那么严格的平衡搜索树, 是二叉树, 树的高度较高, 查询慢些, IO损耗大, 所以不适合作为索引结构

InnoDB存储引擎, 有自适应哈希索引的特点; 比如精确查找时它会使用哈希索引, 范围查询时使用B+树索引

哪些情况下适合建索引:

* 频繁作为查询条件的字段
* 与其他表关联的, 逻辑上的外键这种字段
* 要排序的字段
* 要分组的字段
哪些情况下不适合建索引:

* 表很小时
* 经常增删改的表或字段
* where过滤里用不到的字段
* 过滤性不好的字段, 如sex, 不是男就是女
索引的分类:

* 主键索引: 非空, 唯一
* 唯一索引: 唯一,但允许有空值(Null),但只允许有一个空值(Null)
* 单列索引: 又叫单值索引、普通索引
* 复合索引:一个索引可以包含多个列,多个列共同构成一个复合索引!全都命中时效率>多个单列索引
* 全文索引:Full Text, 支持全文查找(5.6版本InnoDB开始支持, MyISAM的话早就支持了)
约束:

*
非空约束

*
唯一约束

*
主键约束

*
外键约束

*
自增约束

*
默认约束 有默认初始化值

*
检查性约束 CHECK 保证列中的数据必须符合指定的条件;

​ ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

聚簇索引和非聚簇索引:

在InnoDB里, 主索引是聚簇索引, 二级索引/辅助索引 是非聚簇索引

在MyISAM里, 主索和二级索引 全都是非聚簇索引

基于InnoDB讨论:

*
存储位置特点上: 将数据存储与索引放到一起, 将数据与索引分开存储

* 聚簇索引: 叶子结点存储整行的数据, 将数据和索引存储到了一起
* 非聚簇索引: 叶子结点存储主键索引, 数据和索引分开存储
*
查询时: 如果使用辅助索引查数据,实质是先找主索引,再通过主键索引查找其他要select的字段(即回表查询, 增大了IO次数)

当要select的字段恰好仅是主索引字段时,便不需要再全表扫描了(即覆盖索引了)

主索引要用的字段:

* 默认是主键
* 如果表中没有主键索引, 那么InnoDB会选择一个唯一且非空的索引代替主键作为主索引
* 如果还没有, 那InnoDB就会隐式定义一个主键(类似于Oracle中的RowId)来做为主索引
一个表中只能有1个聚簇索引

聚簇索引的优点:

可以把相关数据保存在一起,不用找两次, 减少磁盘的IO

为什么主键(聚簇索引)建议使用自增id?

利于查找相邻数据, 寻址方便, 只要索引是相邻的,那么在磁盘上索引对应的行数据存放地址也是相邻的

为什么聚簇索引不建议使用UUID?

不要使用UUID方式,因为UUID的值离散太过极端,不适合排序, IO寻址起来麻烦

索引失效:

*
如果a>1 or b<2的a命中索引,而b没命中索引,那便会索引失效全表扫描,

因为MySQL觉得 即便a使用了索引,但b还是要全表扫描, 所以干脆全都全表扫描一遍

*
模糊查询时, 以%作为开头

*
根据最左前缀原则, (a,b,c),前面的字段没用上索引,后面的就也不能用了

*
使用!= 或者 <> 导致索引失效

*
类型不一致导致索引失效

*
函数导致的索引失效, 如 WHERE DATE(create_time) = ‘2020-09-03’;

*
运算符导致的索引失效, 如WHERE age - 1 = 20;

*
NOT IN、NOT EXISTS导致索引失效

覆盖索引: 是指你查找的字段恰好就是索引字段, 直接就找到了

前缀索引: 因为一些字段数据太长, 为节省内存 所以用前缀部分当一个大概的索引, 前缀长度的确定可由

​ select count (distinct(left(str, length)))/count(*) 来确定length多少合适,使区分度较高

索引下推:

类似于谓词下推, 不过谓词下推针对的是自己编写sql 提前用where过滤; 而索引下推,是基于存储引擎提早执行多个过滤条件,
而不是返回给了MySQL服务端再进一步过滤

这个功能是默认开启的

优点: 尽早排除非法数据从而减少没必要的额外操作(比如尽量减少回表查询), 减少IO消耗

<>六、执行计划:

explain 出来的表结构里的 重要列

*
type 判断索引是否高效命中

性能关系: ALL < index < range ~ index_merge < ref < eq_ref < const < system

const(主键索引 或者 唯一二级索引 进行等值匹配的情况下)

ref(普通的 二级索引列与常量 进行等值匹配)

index(扫描全表索引的覆盖索引)

All(全表扫描数据)

*
key 当前查询用到了哪种索引

*
rows 预估值要扫描多少行

*
Extra 额外信息, 比如是否using where之类的

<>七、优化:

*
谓词下推: 能过滤则先过滤

*
只select 必需的字段, 不用select *

*
小表驱动(join)大表: 新版本已经会自动优化这个了; 大表 left join 小表时(根据左边去查找右边), 左边大表索引用不到, 右边小表才能用到,
把索引命中放在了小表上 吃亏了

*
exists和in:

exists 适合子查询中表数据较大时:

​ 是对外表做loop循环,每次loop循环再对内表(子查询)进行查询,那么内表的查询能使用到索引,内表是 大表时大量使用了索引优势

in 适合外部表数据较大时:

​ in是把外表和内表做hash连接, 先查询内表, 再把内表结果与外表匹配,对外表使用索引,外表是 大表时大量使用了索引优势

而not in 和 not exists时,都用不到索引

对于not exists,内表有空值对查询结果没有影响, 外表有空值时则这条记录最终会输出

对于not in, 内表有空值将导致最终的查询结果为空, 外表有空值时则这条记录最终将不被输出

*
索引优化: 调整字段顺序使尽量满足符合最左前缀原则能用上索引

*

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信