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