MYSQL
2022-07-28 17:37:19 0 举报AI智能生成
MYSQL知识图谱
作者其他创作
大纲/内容
核心组件
连接器
管理连接，权限验证
查询缓存
mysql接收一个查询请求是，会先查询缓存，之前的sql语句为key，结果为value 缓存起来，如果命中缓存，就直接返回给给客户端
一般不建议使用查询缓存，因为查询缓存的更新太频繁了，表的数据更新，会清空所有的查询缓存，mysql8.0版本后取消了查询缓存
分析器
进行词法分析、语法分析，判断是否符合mysql语法规范
优化器
对你的查询语句进行适当的优化，表中有多个索引时，决定使用哪个索引，多表关联(join)查询时，决定表的顺序。
执行器
执行语句，返回结果，这个过程还会校验是否有表的权限
存储引擎
InnoDB
Myisam
Memory等
binlog(归档日志)
binlog是mysql server 层维护的一种二进制日志
事务
ACID
Atomicity(原子性)
事务是一个不可分割的单位，其中的操作要么全部做，要么全部不做；事务中某一个sql执行失败，已执行的sql必须回滚，数据库回退到事务执行之前的状态。
Consistency(一致性)
事物执行结束后，数据库的完整性约束没有被破坏，事物执行的前后都是合法的数据状态。
Isolation(隔离性)
事务内部的操作与其他事务是隔离的，并发执行的各个事务之前不能互相干扰。
隔离级别
读未提交
读已提交
可重复读
可串行化
Durability(持久性)
指事务一旦提交，它对数据库的改变是永久性的，接下的其他操作和故障都不应该对其产生了遗憾。
MVCC
多版本并发控制：在同一时刻，不同事务读取的数据可能是不同的版本。让读写不冲突，读不需要加锁，作为一致性视图，用于读已提交，可重复读隔离级别的实现。
分类
当前读：sql读取到数据时，都是读取当前最新的数据。mysql通过锁机制确保获取数据时没有其它事务在修改数据，一般非select语句都是当前读。
快照读：不主动加锁的select语句就是快照读，读取的是数据的快照版本。innodb快照读，数据的读取由cache(原本数据)与undo(当前事务修改或者插入之前的数据)两部分组成 (在不同的隔离级别下，快照读是有区别的，在RC下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次select也可以看到其他事务commit的数据的更改，也就是不可重复读。但是在RR级别下，快照会在事务中第一次select语句执行时生成，只有在本事务中对数据修改才会更新快照，因此，只能看到第一次select之前已经提交事务的数据)
实现原理
mysql隐藏列
锁
分类
表级锁
特点
开销小，加锁快
不会出现死锁
锁粒度大，容易出现锁冲突，并发度低
类型划分
意向共享锁（intention shared lock, IS）
事物有意向对表中的某些行加共享锁(S锁)
意向排它锁（intention exclusive lock, IX）
事物有意向对表中的某些行加排它锁(X锁)
自增锁
事物在向包含AUTO_INCRMENT列的表中新增数据时会持有自增锁。假设事物A正在执行这个操作，此时另一个事物B尝试执行INSERT语句，事物B会被阻塞住，知道事物A释放自增锁。
行级锁
特点
开销大，加锁慢
会出现死锁
锁粒度小，不容易出现冲突，并发度高
类型划分
共享锁
允许一个事物读取数据，不允许修改数据，如果其他事务要再对改行进行加锁，只能加共享锁
加锁方式：select * from xx where id =x LOCK IN SHARE MODE
排他锁
事务在修改数据时加点锁，可以读取和修改数据，一旦一个事物对改行加排它锁，其他事务不能再对改行数据加任何锁。
加锁方式：delete/update/insert默认加上X锁，查询:select * from xxx where id =x for update;
行锁算法
记录锁
锁住具体的索引项，当sql执行按照唯一索引进行数据检索的时候，查询条件等值匹配的时候并且数据存在的时候，这个sql加上的就是记录锁
间隙锁
锁住数据不存在的区间(左开右闭(]),在sq执行按照索引进行数据检索的时候，查询数据不存在，这时SQL加上的锁为间隙锁，锁住了不存在的区间
邻键锁
等于(Record locks+Gap locks)(左开右闭(])，当sql执行按照索引进行数据检索时，查询条件为范围查找，并有数据命中，这个sql语句加上的锁就是临键锁，锁住记录+区间（这个锁解决了幻读问题）
死锁
是指两个或者两个一上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，他们将无法推进下去
产生原因
因为系统资源不足
进程运行推进的顺序不合适
资源分配不当等
产生死锁的四个必要条件
互斥条件：一个资源每次只能被一个进程使用
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
不剥夺条件：进程已获得的资源，在未用完之前，不能强行剥夺。
循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系
如任避免死锁
加锁顺序一致性
基于primary或者unique key更新数据
单次操作数据量不宜过多，表尽量少
减少表上的索引，减少单次定的资源
死锁检测
innodb_deadlock_detect 默认为0n，50s发现 死锁就好超时退出、
索引
索引的本质
一种排好序的数据结构
优缺点
优点
大大提高查询速度
通过创建唯一索引，可以保证数据库每一行数据的唯一性
可以加深表与表之间的连接
可以显著减少查询中分组和排序的时间
缺点
创建索引和维护索引需要时间，而且数据量越大时间越长
创建索引需要占磁盘空间，如果有大量索引，可能比数据文件更快达到最大文件尺寸
当对表中的数据进行增加、修改、删除的时候，索引也要同时维护，降低了数据的维护速度
分类
从存储结构上划分
Btree索引(B+tree、B-tree) Mysql默认索引
优缺点
优点
相比较于传统二叉树，避免了数据量大时造成的树的高度较高，而导致I/O次数高，速度变慢，B+TREE树的高度很低，从而减少了查找数据时的I/O次数
缺点
增加了额外的空间
添加，删除，修改索引列时，会伴随索引分裂，页空洞等性能损耗
存储方式分类
聚簇索引
将索引与数据放在一起，并且在叶子节点存放数据（innodb中主键索引就是聚簇索引）
非聚簇索引
通过索引检索到行号，再通过行号找到数据（Innodb是找到对应的主键id，再通过主键id找到对应的数据）
功能分类
普通索引
唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”，且不允许有空值。
全文索引
哈希索引
优缺点
优点
检索速度快，时间复杂度O(1)
缺点
数据量较大的情况下，出现大量哈希碰撞，导致检索效率降低
无法排序，范围查找
不支持最左匹配原则
full-index 全文索引
Rtree（空间数据索引）
索引创建规范
单张表索引数量不超过5个，单个索引中的字段不超过5个
表必须有主键，推荐使用自增字段作为主键（自增id、雪花算法等）
禁止冗余索引(索引|(a,b,c),索引|(a,b))，禁止重复索引(索引|(a),索引|(a,主键ID))
联表查询时，join列的数据类型必须相同，并且要建立索引
不在低基数列建立索引,如性别
选择区分度大的列建立索引，组合索引中，区分度大的放前面
合理创建组合索引(a,b,c)相当于(a),(a,b),(1,b,c)
合理使用覆盖索引减少IO跟避免排序
索引优化分析
EXPLAIN SELECT * from ** WHERE *** =****
执行计划字段解释
id：编号
id值相同,从上往下 顺序执行
id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)
select_type ：查询类型
SIMPLE
简单查询（不包含子查询、union）
PRIMARY
包含子查询SQL中的 主查询 （最外层）
SUBQUERY
包含子查询SQL中的 子查询 （非最外层）
DERIVED
衍生查询(使用到了临时表)
table ：输出结果集的表
type ：访问类型
从左到右，性能由好到最差，system>const>eq_ref>ref>range>index>all （ref到range之间还有一些其他的不过不太常见）（要对type进行优化的前提：有索引）(一般优化后的type必须在range以上)
分类
system
只有一条数据的系统表 ；或 衍生表只有一条数据的主查询（基本不能达到可忽略）
const
仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 （类型 与索引类型有关）
eq_ref
唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
ref
非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
range
检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引all)
index
全索引扫描，查询全部索引中数据
all
全表扫描，查询全部表中的数据
possible_keys ：可能用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
用于判断复合索引是否被完全使用 （a,b,c）
demo
在utf8编码中：1个字符占3个字节，如果索引的字段可以为null，则会使用一个字节用来标识 ，如果是varchar这种，会用两个字节表示可变
例如 ：某个字段 name（varchar（20））,如果用到了这个字段的索引那么lenth就是 20*3+1（null）+2（可变字符） =63
例如 ：某个字段 name（varchar（20））,如果用到了这个字段的索引那么lenth就是 20*3+1（null）+2（可变字符） =63
ref :表之间的引用
rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
Extra :额外的信息
using index
性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）只要使用到的列 全部都在索引中，就是索引覆盖using index
using where
表示进行了回表查询
using filesort
性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
using temporary
性能损耗大 ，用到了临时表。一般出现在group by 语句中。
impossible where
where子句永远为false
避免索引失效的一些原则
复合索引，不能跨列使用（index（a，b，c），你的索引使用就不要 where a=x，c=x）
复合索引尽量使用全索引匹配
不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效
复合索引不要使用不等（！= ,<>，>），这样会导致自身以及右侧的索引失效**(这个不是一定的有概率的**)
like尽量以“常量”开头，不要以'%'开头，否则索引失效
尽量不要使用or，否则索引失效（字段都是单值索引是可以的）
常见问题
为什么我们经常被要求，只查询需要的字段？
在例如你查询只用到name时，select * from user where iphone=xxx，索引为（iphone，name）的时候，我们根据上面的innodb的索引结构学习到，你要查询索引中没有的字段的时候，需要先通过辅助索引找到主键索引的值，然后再找到对应的数据值（过程叫做回表），这样就多了I/O操作，如果你的sql改成select name from user where iphone=xxx，这个name值在辅助索引中已经有了，所以不需要再去主键索引中找数据，就提高了查询效率(这种情况叫做覆盖索引)
经常在数据量很大的情况下我们要把主键用整数代替字符串，并且是增长的？
在使用BigInt的时候比UUID(为保证不重复一般都是UUID),占用的字节少，那么在B+Tree的结构中，就能存储更多的值。
比字符串好排序
如果你生成的是自增的（分布式id生成策略中都是增加的）,在插入时，就可以尽可能减少页分裂
主从复制
基本原理
master上所有的修改都会保存二进制binary log中，slave开启一个I/O thread，来读binary log然后写到本地的一个realy log里面。同时slave上开启一个sql thread 进行执行获取到的realy log
0 条评论
下一页
