MySQL|MySQL相关知识【索引, 锁, 事务...】(个人总结) database|数据库

声明: 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种情况:
1. 分组字段
2. 聚合函数
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 中的行级锁的实现依赖于索引，一旦加锁操作没用到索引，那么就会退化为表锁
  1. 行锁
  2. 间隙锁
  3. 临键锁
- 页级锁一次锁定相邻的一组记录
- 表级锁
按共享策略来分：
- 共享锁
- 排他锁
- 意向共享锁
- 意向排他锁
按加锁策略来分：
- 乐观锁
- 悲观锁
其他：
- 自增锁

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的字段恰好仅是主索引字段时,便不需要再全表扫描了(即覆盖索引了)
主索引要用的字段:
1. 默认是主键
2. 如果表中没有主键索引, 那么InnoDB会选择一个唯一且非空的索引代替主键作为主索引
3. 如果还没有, 那InnoDB就会隐式定义一个主键(类似于Oracle中的RowId)来做为主索引
一个表中只能有1个聚簇索引

聚簇索引的优点:
可以把相关数据保存在一起，不用找两次, 减少磁盘的IO
为什么主键(聚簇索引)建议使用自增id?
利于查找相邻数据, 寻址方便, 只要索引是相邻的，那么在磁盘上索引对应的行数据存放地址也是相邻的
为什么聚簇索引不建议使用自增id?
不要使用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，内表有空值将导致最终的查询结果为空, 外表有空值时则这条记录最终将不被输出
【MySQL|MySQL相关知识【索引, 锁, 事务...】(个人总结)】索引优化: 调整字段顺序使尽量满足符合最左前缀原则能用上索引