©2020-2024 ioDraw All rights reserved,
Privacy Policy
声明: 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， 内表有空值将导致最终的查询结果为空, 外表有空值时则这条记录最终将不被输出
*
索引优化: 调整字段顺序使尽量满足符合最左前缀原则能用上索引
*