知识点扫盲-MySQL
2021-09-18 16:34:31 55 举报AI智能生成
小白一枚，多学多记！
作者其他创作
大纲/内容
组成
Server层
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接
show processlist查看执行情况
wait_timeout 控制，默认值是 8 小时
建议长连接
定期断开重连
MySQL 5.7 后通过执行 mysql_reset_connection 来重新初始化连接资源
查询缓存
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存
MySQL 8.0 版本直接将查询缓存的整块功能删掉了
分析器
MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析
词法分析
语法分析
优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
执行器
存储引擎层
负责数据的存储和提取
存储引擎
MySQL5.5开始引擎默认InnoDB
InnoDB
默认行级锁
支持更高的并发
支持事务
RR
B+树
MyISAM
表锁
Memory
临时表
表锁
存放内存
hash索引
Archive
mysql
基础
范式
一、二、三、BC、四
因为性能会不遵守，建立冗余字段
权限
DB
user
数据类型
索引
类型
主键索引
普通索引
唯一索引
联合索引
最左匹配
根据身份证号查用户姓名，避免回表，（身份证，用户姓名）
全文索引
数据结构
B树
二叉树
平衡树
B树
预读
预读可以提高I/O效率.预读的长度一般为页(page)的整倍数
MySQL 是以「页」（page）为单位从磁盘读取数据的
B+树
在B树中，将键和值存放在内部节点和叶子节点；
但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立
但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立
hash
key-value，精确查找
redis等NoSQL
无序hash
引申：有序数组
有序的适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构
可以用来做静态存储引擎，用来保存静态数据，比如历史的订单信息等，都是不会变动的历史数据
视图
触发器
存过
事务
MVCC
Muti-Version Concurrency Control
多版本并发控制
多版本并发控制
MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
当前读/快照读
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
select for update
update
insert
delete
update
insert
delete
快照读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；
既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
实现原理
3个隐式字段
DB_ROW_ID
隐含的自增 ID（隐藏主键）
DB_TRX_ID
当前操作该记录的事务 ID
DB_ROLL_PTR
是一个回滚指针，用于配合 undo日志，指向上一个旧版本
undo日志
insert undo log
代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log
事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；
所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除
所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除
Read View
主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
RC 隔离级别下，是每个快照读都会生成并获取最新的 Read View；
而在 RR 隔离级别下，则是同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View
而在 RR 隔离级别下，则是同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View
隔离级别
读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）
MySQL默认是RR级别
Oracle默认RC
数据迁移到MySQL需要注意
启动参数 transaction-isolation 的值设置成 READ-COMMITTED
并发场景事务问题
脏读（针对未提交数据）
RC
不可重复读
同一个事物内，查到的结果都不一致
RR/S
幻读
同一个事务中，存在前后两次查询同一个范围的数据，但是第二次查询却看到了第一次查询没看到的行，
一般情况下特指事务执行中新增的其他行
一般情况下特指事务执行中新增的其他行
RR/S
MySQL在RR级别下怎么解决幻读的？
next-key lock
1.给涉及到的行加写锁（行锁）防止写操作
2.给涉及到的行两端加间隙锁(Gap Lock)防止新增行写入
如何选择？
一般选择RC，隔离级别越低，事务并发性能越好
为什么默认RR？
因此由于历史原因，mysql将默认的隔离级别设为可重复读(Repeatable Read)
保证主从复制不出问题
保证主从复制不出问题
就是在master上执行的顺序为先删后插！而此时binlog为STATEMENT格式，它记录的顺序为先插后删！
从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致
从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致
隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。
当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住
当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住
主从复制是基于binlog复制
binlog格式
statement
记录修改sql语句
row
记录每行实际数据的变更
mixed
上面两种的混合
为什么建议用RC？
在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多
在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行
在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性
引申
在RC级别下，不可重复读问题需要解决么？
不用解决，这个问题是可以接受的！毕竟你数据都已经提交了，读出来本身就没有太大问题！
Oracle的默认隔离级别就是RC，你们改过Oracle的默认隔离级别么？
Oracle的默认隔离级别就是RC，你们改过Oracle的默认隔离级别么？
在RC级别下，主从复制用什么binlog格式？
用的binlog为row格式，是基于行的复制！Innodb的创始人也是建议binlog使用该格式
锁
锁粒度
行锁
与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，最容易发生死锁
表锁
实现逻辑非常简单，带来的系统负面影响最小
页锁
锁级别
共享锁（读）
事务A对数据B加读锁，其他事务只能对B加读锁
排它锁（写）
事务A对数据B加读锁，其他事务不能对B加任何锁
意向锁
Intention Lock
意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型
意向锁是 InnoDB 自动加的，不需要用户干预
意向共享锁（IS）
意向排他锁（IX）
优化
索引知识点
select * from table where name='XXX' and age= 18;
索引 idx_name_age(name,age)
索引 idx_name_age(name,age)
回表查询
由于select *
命中索引后，数据库还必须回去聚集索引中查找其他数据
命中索引后，数据库还必须回去聚集索引中查找其他数据
索引覆盖
select name,age
name,age在索引中都能找到，不需要回表
name,age在索引中都能找到，不需要回表
最左前缀原则
最左优先，上面的索引相当于 name索引和（name,age）索引
索引下推优化
Index Condition Pushdown
ICP的诞生主要是为了进一步提高B+Tree索引查询的可用性
name like 'XX%' and age= 18
模糊匹配后结果变成无序，所以后面条件无法再使用到索引，
因此需回表提取出name like 'XX'结果集后，再通过普通查询得到age = 18的最终结果
因此需回表提取出name like 'XX'结果集后，再通过普通查询得到age = 18的最终结果
引入ICP后
在索引内部取到name结果之后，就顺便判断了结果中的age是否等于18，对于不等于18的记录直接跳过，
因此在index(name,age)这棵索引树中直接匹配到了结果记录，减少了完整查询记录（一条完整元组）读取的个数，
此时拿着结果集的id去主键索引树中回表查询全部数据，减少了二次查询时间，I/O次数也会减少
因此在index(name,age)这棵索引树中直接匹配到了结果记录，减少了完整查询记录（一条完整元组）读取的个数，
此时拿着结果集的id去主键索引树中回表查询全部数据，减少了二次查询时间，I/O次数也会减少
SELECT
语法
执行顺序
SQL优化
避免不走索引
尽量避免通配符在前的模糊查询，如like '%XX'
尽量避免使用in/not in
连续的可以换成between
子查询可以换 exists
尽量避免使用or
可以用union
尽量避免NULL值
可以设置默认值0
查询条件避免等号左侧做运算、使用函数操作
避免使用<>或!=
避免类型转换，如varchar类型字段，查询条件用123
orderby要和where条件一致
正确使用hint优化语句
如FORCE INDEX
避免走索引
数据量小的
不常用的列
频繁更新的列
差异性小的列
select语句优化
避免select *
不需要的列会增加数据传输时间和网络开销
对于无用的大字段，如 varchar、blob、text，会增加 io 操作
失去MySQL优化器“覆盖索引”策略优化的可能性
多表级联，小表在前
使用表别名
where替代having
调整where条件顺序
MySQL采用从左往右，自上而下的顺序解析where子句。
根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
性能分析
Explain
id
select_type
关联类型，决定访问表的方式
SIMPLE
简单查询，没有子查询和union
PRIMARY
如果不是SIMPLE，最外层被标记为PRIMARY
....
table
type
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
eq_ref
主键或唯一索引，最多返回一条数据
ref
多条数据，普通索引
range
范围查询
ALL
全表扫描
possible_key
可以使用哪些索引
key
实际决定使用哪个索引
key_len
索引字段的可能最大长度，不是表中实际数据使用的长度
ref
表示key展示的索引实际使用的列或者常量
rows
查询数据需要读取的行数，只是一个预估的数值，但是能很直观的看出SQL的优劣了
filtered
表示针对符合查询条件的记录数的百分比估算，用rows和filtered相乘可以计算出关联表的行数
Extra
解析查询的附加额外信息
Using index
使用覆盖索引
Using index condition
使用索引下推
索引下推简单来说就是加上了条件筛选，减少了回表的操作
Using where
where过滤
....
分库分表
概念
分库
为了减轻单台MySQL实例存储压力及可扩展性
分类
垂直分库
按业务模块/公共模块，将表分到不同的库
特点
每个库的表都不一样；
表不一样，数据就更不一样了~ 没有任何交集；
每个库相对独立，模块化
表不一样，数据就更不一样了~ 没有任何交集；
每个库相对独立，模块化
场景
可以抽象出单独的业务模块时，可以抽象出公共区时（如字典、公共时间、公共配置等）
水平分库
以行数据为依据，将一个库中的数据拆分到多个库中。大型分表体验一下？
坦白说这种策略并不实用，因为会对后台开发很不友好，有很多坑，不建议采用
坦白说这种策略并不实用，因为会对后台开发很不友好，有很多坑，不建议采用
特点
每个库的结构都一样；
每个库的数据都不一样，没有交集；
所有库的并集是全量数据；
每个库的数据都不一样，没有交集；
所有库的并集是全量数据；
场景
系统绝对并发量上来了，CPU内存压力大。分表难以根本上解决量的问题，并且还没有明显的业务归属来垂直分库，主库磁盘接近饱和。
分表
解决单张表数据过大导致的查询瓶颈问题
分类
垂直分表
按照字段的活跃性、字段长度，将表中字段拆分到不同的表（主表和扩展表）中
特点
每个表的结构都不一样；
每个表的数据也不一样，
有一个关联字段，一般是主键或外键，用于关联兄弟表数据；
所有兄弟表的并集是该表的全量数据
每个表的数据也不一样，
有一个关联字段，一般是主键或外键，用于关联兄弟表数据；
所有兄弟表的并集是该表的全量数据
场景
有几个字段属于热点字段，更新频率很高
InnoDB行锁会很麻烦
有大字段，如text,blob
InnoDB数据和索引是在同一个文件的，用select * 查询的时候磁盘IO会高消耗
明显的业务区分，或者涉及冗余
水平分表
按某列的自容性进行拆分
如日期 2018、2019、2020开头的
特点
每个表的结构都一样；
每个表的数据都不一样，没有交集；
所有表的并集是该表的全量数据；
每个表的数据都不一样，没有交集；
所有表的并集是该表的全量数据；
场景
单表的数据量过大或增长速度很快，已经影响或即将会影响SQL查询效率，加重了CPU负担，提前到达瓶颈。记得水平分表越早越好
PS
在实际工作中，我们在选择分库分表策略前，想到的应该是从缓存、读写分离、SQL优化等方面，因为这些能够更直接、代价更小的解决问题
log
binlog
记录了表结构和表数据的变更，比如update/delete/insert/truncate/create....
它不会记录select（因为这没有对表没有进行变更）
它不会记录select（因为这没有对表没有进行变更）
作用
主从复制
恢复数据
逻辑变化
redo log
修改数据库时，是根据条件找到对应的页，然后把该页加载到内存中做修改，再异步到磁盘
内存修改
记录redo log
记录在某个页上做的修改
写redo log也需要写磁盘，好处是顺序IO
什么时候flush
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”
1.InnoDB的redo log写满了，这时候系统会停止所有更新操作，
把checkpoint往前推进，redo log留出空间可以继续写
把checkpoint往前推进，redo log留出空间可以继续写
2.系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，
空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘
空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘
3.MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”
4.MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上
其实写redo log的时候，也会有buffer，是先写buffer，再真正落到磁盘中的。
至于从buffer什么时候落磁盘，会有配置供我们配置
至于从buffer什么时候落磁盘，会有配置供我们配置
异步落盘
数据库挂了导致没落盘成功，根据redo log来恢复数据
因为顺序IO，所以写入速度快
redo log记录的是物理变化，体积小
binlog 和redo log
区别
binlog的作用是复制和恢复
redo log作用是持久化
不会存储历史所有记录，落盘后数据就会无效，所以不能用来数据库数据清空后恢复
写入顺序
redo log 是事务开始，就记录每次的变更信息
binlog是事务提交才记录
数据一致性
不一致带来的问题
redo log写失败，binlog成功；内存数据没来得及落盘，数据库挂了，则主从服务器不一致
(从服务器通过binlog得到最新数据，主服务器因为redo log没有写成功，没有最新数据)
(从服务器通过binlog得到最新数据，主服务器因为redo log没有写成功，没有最新数据)
redo log成功，binlog 失败；从服务器拿不到最新数据
如何保证
两阶段提交
InnoDB redo log写盘，进入prepare状态
binlog写盘，InnoDB进入commit状态
undo log
回滚和MVCC
在数据修改的时候，不仅记录了redo log，还记录undo log
如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚
如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚
因为undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了
0 条评论
下一页
