MySQL
2020-09-07 10:53:17 0 举报AI智能生成
MYSQL知识汇总
作者其他创作
大纲/内容
架构
连接器Connectors
管理连接，权限验证
连接池Connectors Pool
解析器Parser
检测语法是否正确
查询优化器Optimizer
索引
多表关联
小表在前，大表在后
where 条件
寻找过滤力度最大的先执行
用explain执行计划分析
查询缓存Cache和Buffer
存储引擎Pluggable
InnoDB
支持外键，支持事务，支持行锁，支持回滚，支持并发控制，比MyISAM稍慢
MyISAM
高速引擎，不支持事务，不支持行锁，不支持外键，不支持持久化，不支持并发
内存引擎
使用场景
只读场景
表级锁
读取和写入操作互斥
存储结构
表空间
table space
段
segment
簇
extent
页
page
行
row
日志系统
redo Log
物理日志
如果一条语句要进行修改，会先写入redo log日志，磁盘空闲进行执行。
redo log 只有InnerDB引擎专属日志
循环写入 执行完成后新的更新语句会进行覆盖
checkpoint 往前推进
bin Log
逻辑日志
bin Log 记录了完整的逻辑日志，在数据库恢复时是以 bin为基础，通过其记录的完整逻辑操作
备份出一个和原库完整的数据。
备份出一个和原库完整的数据。
属于所有引擎
追加式写入，不会覆盖
binlog 的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器
的 binlog，然后执行一遍。
的 binlog，然后执行一遍。
undo Log
事务日志
记录了事务发生前的数据
回滚事务
日志系统一致性
按照顺序执行语句会先写入redo Log处于PrePare阶段再写入 bin Log提交事务处于commit状态
保证顺序使用二阶段事务提交
索引
索引一般以文件形式存储在磁盘上
索引的出现是为了加速查询效率
索引数据结构
哈希表(Hash)
二叉树（Binary Trees）
二叉树是每个节点最多只有两个分支（即不存在分支度大于2的节点）的树结构。通常被称之为“左子树”和“右子树”
左子树<父节点<=右子树
二叉树不适合用作当作索引的，数据量庞大的话，二叉树的层数会很大，查找效率固然也很慢了。
图片
红黑树（Red-Black Trees）
是一种自平衡二叉查找树，典型用途是实现关联数组。
红黑树的结构复杂，但它的操作有着良好的最坏情况运行时间，并且在实践中高效：它可以在O(log n)时间内完成查找，插入和删除，这里的n是树中元素的数目。
红黑树遵行以下原则：
节点是红色或黑色。
根是黑色。
所有叶子都是黑色（叶子是NIL节点）。
每个红色节点必须有两个黑色的子节点。
从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点。
同样红黑树也不适用于MySQL的索引，数据量庞大之后，数层也会变大。
图片
总结一下二叉树和平衡二叉树区别
向上天借6个数字
123456
二叉树
平衡二叉树
为什么不用红黑树
当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。
InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是 16K(16384 字节)。
那么，一个树的节点就是 16K 的大小。
如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个
或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候，
浪费了大量的空间。
所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多
的节点，意味着跟磁盘交互次数就会过多。
如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数
越多，消耗的时间就越多。
InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是 16K(16384 字节)。
那么，一个树的节点就是 16K 的大小。
如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个
或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候，
浪费了大量的空间。
所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多
的节点，意味着跟磁盘交互次数就会过多。
如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数
越多，消耗的时间就越多。
B树（B-Trees）
节点数量为N，子节点数量为N+1
多路平衡查找树
通过合并和分裂来保证平衡
键值的方式存储数据
节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。
B+树
节点数量为N，子节点数量为N
根节点和叶子节点用来存放引用叶子节点，叶子节点用来存储数据
每一个叶子节点会有一个指针，指向下一个叶子节点
有序链表
范围查找的时候，查找到一个叶子节点直接链表操作，避免重复IO 操作
排序能力更强，效率更加稳定
算法
B+Tree
Hash
缺点
无序
索引效率极低
二叉树
逻辑约束
左小右大
红黑树
逻辑约束
节点是红色或黑色
根节点是黑色
每个红色节点的两个子节点都是黑色
新插入的节点默认是红色
平衡措施
变色
自旋
缺点
读磁盘太多
索引方法
HASH
聚集索引
索引的键值的逻辑顺序
跟表数据行的物理存储数据是一样的
跟表数据行的物理存储数据是一样的
主键索引就是聚簇索引
没有聚集索引怎么办
1.会查找不包含null的值来选为唯一索引作为值的存放
2.会使用Rowid来选为聚集索引来组织值的存放
主键索引和普通索引的区别
主键索引
直接查询主键
普通索引
通过字段搜索主键通过主键查询普通字段
普通索引会造成回表的操作
联合索引
创建两个索引
最左匹配原则
最左匹配
优先匹配左边索引
如果是两个字段顺序不通，优化器会来解决
覆盖索引
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
建立索引就是字段就是查询的条件
Extra
Using index
索引类型
Normal
普通索引
唯一索引
UNIQUE
主键索引
子主题
全文索引
Full text
Explanin
查看使用索引
索引是不是越多越好呢
列的离散（s sà àn n ）度
count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。
数据行数相同的情况下，分子越大，列的离散度就越高。
数据行数相同的情况下，分子越大，列的离散度就越高。
简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。
如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差
不了多少的时候，就算建了索引，也不一定会走索引。
不了多少的时候，就算建了索引，也不一定会走索引。
前缀索引
当字段比较长时建立索引需要消耗大量的时间
这时候就可以创建前缀索引了
普通索引还是唯一索引
change buffer
普通索引使用Change Buffer
减少IO操作
analyze table
重新选择索引
force index
强行选择一个索引
事务
事务的特性
原子性
要么全部成功，要么全部失败
回滚
undo Log实现
逻辑日志
一致性
事务执行成功的完整性，数据没有被破坏
隔离性
事务隔离
隔离性实现主要使用读写锁和MVCC读写锁多版本并发控制
持久性
只要事务执行成功，那么数据永远是永久性的
事务的崩溃恢复是使用 redo Log
持久性是使用redo Log 和double write双写缓冲来实现的
事务的启动方式
显式启动事务语句
begin 或 start transaction。配套的提交语句是 commit
回滚语句是 rollback。
回滚语句是 rollback。
set autocommit=0
这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。
这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致
接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。
接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。
事务并发会带来什么问题
幻读
insert突然增加了一条数据叫做换读
脏读
不可重复读
update/delete是不可重复读
隔离级别
当数据库有多个事务执行的时候，就可能会出现，脏读，幻读，不可重复读的问题，所以就出现了事务隔离级别的概念。
在事务隔离级别之前你要知道，你隔离的越严实效率就会越低，所以要平衡找到一个平衡点。
隔离级别
读未提交
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
对其他事务可见，会出现脏读问题
读提交
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
解决脏读问题
可重复读
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是
一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
解决不可重复读问题
串行化
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出
现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
解决所有问题
读一致性问题
inner DB
第一种方案
查询的时候加锁
第二种方案
查询的时候，生成时间点的一致性快照，并用这个快照提供一致性级别
MVCC
一致性执行方案
MVCC
DB_TRX_ID
6字节
插入或更新最后一个事务的ID，事务ID自增
创建版本的事务
DB_ROLL_PTR
7字节
回滚事务，插入版本
删除版本的事务
我可以查到在我这个事务开始之前已经存在的数据，即使它
在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。
在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。
在 InnoDB 中，MVCC 是通过 Undo log 实现的。
LVCC
MySQL锁
表锁，顾名思义，是锁住一张表；行锁就是锁住表里面的一行数据。锁定粒度，表
锁肯定是大于行锁的。
锁肯定是大于行锁的。
表级锁
前提
加表锁必须这张表里面的任意一行数据，没有被其他事物锁定
标记效率
意向锁
意向锁是系统自己维护的，用户无法手动操作意向锁
意向共享锁
表示事物准备给数据加入共享锁
意向排它锁
表示事物准备给数据加入排他锁
行锁
共享锁
读锁
加锁方式
在查询语句后面加上Lock in share mode
释放锁
事务结束
commit/rollback
排它锁
写锁
加锁方式
在查询语句后面加上For update
insert/update/delete
自动加锁
释放锁
事务结束
commit/rollback
阻塞所有事务里面的关于自己的排他和共享
行锁的原理
没有索引的表
会锁住整张表
有主键索引的表
会锁住当前id的数据
唯一索引
会锁住当前数据
MyISAM
只支持表锁
为什么表里面没有索引的时候，锁住一行数据会导致锁表？
所以，为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐
藏的聚集索引都锁住了。
藏的聚集索引都锁住了。
如果锁住的是索引，一张表没有索引怎么办？
如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索
引作为主键索引。
引作为主键索引。
如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作
为隐藏的聚集索引，它会随着行记录的写入而主键递增。
为隐藏的聚集索引，它会随着行记录的写入而主键递增。
InnerDB存储引擎
内存池
维护所有进程/线程需要访问内部的数据结构
缓存磁盘上的数据，方便快速的读取
同时在对磁盘文件的修改之前，重新缓存
同时在对磁盘文件的修改之前，重新缓存
图片
重新写入日志(redo Log)
缓存池
InnerDB是基于磁盘存储的，并将其中的记录按页的方式进行管理
由于CPU速度和磁盘速度有鸿沟，所以用到了缓存池
缓存
在数据库中读取页的操作，首先从磁盘读到的数据放在缓存池中，这个过程叫做FIX
再读的时候先读缓存池，查看是否缓存
修改数据会先刷新到缓存池，再使用MasterTread刷新到磁盘
缓存类型
索引页
数据页
undo页
插入缓冲
自适应哈希索引
锁的信息
数据字典
图片
内存管理
数据库中的缓存池是通过LRU来管理的
最频繁的页在LRU的最前端
最少的在LRU的后端
淘汰算法
重新做缓冲日志redo Log
CheckPoint
如果缓冲区数据正在刷新到磁盘突然磁盘宕机的怎么办
redo Log
如果磁盘写到一半数据磁盘宕机又怎么办
被称为数据失效
innerDB提供双写
需要还原这个页
double Write
多线程模型
1.MasterThread
非常核心的后台线程
负责将缓冲池中数据异步刷新到磁盘，保证数据的一致性
2.IO Thread
InnerDB大量使用了AIO异步非阻塞来处理IO请求，这样可以提高数据库的性能
3.Purge Thread
事务被提交后，所使用的undoLog可能不再需要，需要Purge Thread来回收
逻辑存储结构
表空间
段
区
页
一条修改语句执行流程
update user set name = 'penyuyan' where id=1;
1、事务开始，从内存或磁盘取到这条数据，返回给 Server 的执行器；
2、执行器修改这一行数据的值为 penyuyan；
3、记录 name=qingshan 到 undo log；
4、记录 name=penyuyan 到 redo log；
5、调用存储引擎接口，在内存（Buffer Pool）中修改 name=penyuyan；
6、事务提交。
后台线程
Master thread
负责刷新缓存数据到磁盘并协调调度其它后台进程。
IO thread
分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer、
重做日志、读写请求的 IO 回调。
重做日志、读写请求的 IO 回调。
purge thread
用来回收 undo 页。
page cleaner thread
用来刷新脏页。
数据存储文件
存放在磁盘
show VARIABLES LIKE 'datadir';
InnoDB
.frm
.ibd
MyISAM
.frm
.MYI
索引文件
.MYD
数据文件，存放数据记录
性能优化
配置优化
连接
从服务端来说，我们可以增加服务端的可用连接数。
（1）修改配置参数增加可用连接数，修改 max_connections 的大小：
（2）或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时
间都是 28800 秒，8 小时，我们可以把这个值调小。
间都是 28800 秒，8 小时，我们可以把这个值调小。
连接池
架构优化
缓存
Redis
主从复制
binLog
读写分离
分库分表
垂直分库
垂直分库，减少并发压力。水平分表，解决存储瓶颈。
垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：
图片
水平分库
水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。
图片
解析器
词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由 Sever
自己处理，跳过。
自己处理，跳过。
SQL语句优化于分析
记录慢查询SQL日志
show variables like 'slow_query%';
EXPLAIN 执行计划
id
id顺序根据数据量决定的
select type查询类型
类型总结
SIMPLE
简单查询，不包含子查询，不包含关联查询 union。
PRIMARY
子查询 SQL 语句中的主查询，也就是最外面的那层查询。
SUBQUERY
DERIVED
衍生查询，表示在得到最终查询结果之前会用到临时表。例如：
UNION
用到了 UNION 查询。同上例。
type连接类型
所有的连接类型中，上面的最好，越往下越差。
在常用的链接类型中：system > const > eq_ref > ref > range > index > all
const
主键索引或者唯一索引，只能查到一条数据的 SQL。
system
system 是 const 的一种特例，只有一行满足条件
eq_ref
通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的
一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
eq_ref 是除 const 之外最好的访问类型。
ref
查询用到了唯一索引，或者关联操作只使用了索引的最左匹配
range
索引范围扫描。
index
Full Index Scan，查询全部索引中的数据（比不走索引要快）。
all
Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。
总结
以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个
状态。
状态。
一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。
ALL（全表扫描）和 index（查询全部索引）都是需要优化的。
ALL（全表扫描）和 index（查询全部索引）都是需要优化的。
possible_key
可能用到的索引
key
实际用到的索引
key_len
索引使用长度
索引的长度（使用的字节数）。跟索引字段的类型、长度有关。
rows
MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。
filtered
这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数
量的比例，它是一个百分比。
量的比例，它是一个百分比。
ref
使用哪个列或者常数和索引一起从表中筛选数据。
Extra
执行计划给出的额外的信息说明。
using
用到了覆盖索引，不需要回表。
EXPLAIN SELECT tid FROM teacher
using where
使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要
在 server 层进行过滤（跟是否使用索引没有关系）
在 server 层进行过滤（跟是否使用索引没有关系）
EXPLAIN select * from user_innodb where phone ='13866667777';
Using index condition
索引条件下推
using filesort
不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。
EXPLAIN select * from user_innodb where name ='青山' order by id;
总结
模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。
通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后，就是对 SQL 语句的具体优化。
通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后，就是对 SQL 语句的具体优化。
规则
存储引擎的选择
为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用 MyISAM。
临时数据用 Memeroy。常规的并发大更新多的表用 InnoDB。
临时数据用 Memeroy。常规的并发大更新多的表用 InnoDB。
字段 定义
原则：使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型：
整数类型
INT 有 8 种类型，不同的类型的最大存储范围是不一样的。
性别？用 TINYINT，因为 ENUM 也是整型存储。
性别？用 TINYINT，因为 ENUM 也是整型存储。
字符类型
变长情况下，varchar 更节省空间，但是对于 varchar 字段，需要一个字节来记录长
度。固定长度的用 char，不要用 varchar。
度。固定长度的用 char，不要用 varchar。
非空
非空字段尽量定义成 NOT NULL，提供默认值，或者使用特殊值、空串代替 null。
NULL 类型的存储、优化、使用都会存在问题。
NULL 类型的存储、优化、使用都会存在问题。
不要用外键、触发器、视图
降低了可读性；
影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；
数据的完整性应该在程序中检查。
大文件存储
不要用数据库存储图片（比如 base64 编码）或者大文件；
把文件放在 NAS 上，数据库只需要存储 URI（相对路径），在应用中配置 NAS 服
务器地址。
务器地址。
表拆分
将不常用的字段拆分出去，避免列数过多和数据量过大。
总结 ：优化体系
0 条评论
下一页
