MySQL实战
2021-12-22 14:14:57 25 举报AI智能生成
为你推荐
查看更多
极客时间-《MySQL 实战 45 讲》林晓斌 思维导图笔记
作者其他创作
大纲/内容
网络、系统资源
mysql数据库本身被堵住了
show processlist;
等MDL锁
等flush
等行锁
长时间不返还
rr下，为了数据一致性，有大量回滚
表中数据导致，回表次数庞大
只查一行也慢
关掉占着连接不工作的线程
跳过权限验证：重启数据库，并使用-skip-grant-tables参数启动，慎用
减少连接过程的消耗
短连接风暴
索引没生效
索引没有设计好
query_rewrite
sql语句没写好
应急方案：force index
mysql选错了索引
查询慢
如果业务方会下掉业务，从白名单去掉sql
单独数据库用户的话，删掉账户
查询重写成select 1返回，慎用
QPS突增
慢sql
主库A执行完一个事务，写入binlog，记为T1时刻之后传给备库B，备库B接收完这个binlog的时刻记为T2备库B执行完这个事务，这个时刻记为T3
主备延迟 T3-T1
每个事务的binlog都有一个时间字段，用于记录主库上写入的时间
备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到second_behind_master
seconds_behind_master 表示当前备库延迟了多少秒
备库 show slave status
备库服务器比主机差
主库和从库的参数配置不一样
备库压力大
备库上在进行备份操作
表上无主键的情况
设置的延迟备库
备库空间不足
主库DML语句并发大，从库QPS高
一次性用delete删除大量数据
建议使用gh-ost方案
大表DDL
大事务
按表分发策略
按行分发策略
MySQL5.5
按库分发策略
MySQL5.6
组提交
再一组里面一起提交的事务，有一个相同的commit_id，下一组就是commit_id+1;commit_id直接写到binlog里面；传到备库应用的时候，相同commit_id的事务分发到多个worker执行；这一组全部执行完成后，coorinator再去取下一批
MariaDB
配置为DATABASE，使用MySQL5.6版本的按库并行策略
配置为LOGICAL_CLOCK，表示使用类似MariaDB的策略
slave-parallel-type
同时处于prepare的事务，在备库执行时是可以并行的；处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的
思想
MySQL5.7
COMMIT_ORDER，根据同时进入prepare和commit来判断是否可以并行
WRITESET，对于事务涉及更新的每一行，计算出这行的hash值，组成集合writeset。如果这两个事务没有操作相同的行，也就是它们的writeset没有交集，就可以并行
WRITESET_SESSION，是在WRITESE的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序
binlog-transaction-dependency-tracking
基于WRITESET
MySQL5.7.22
备库的并行复制策略
原因
主备延迟
主库更新事务后用set global server_id=x修改了server_id
三个节点
循环复制
双M
判断备库B现在的seconds_behind_master，如果小于某个值，比如5s，继续下一步，否则持续重试这一步;把主库A改成只读状态，即把readonly设置为true;判断备库B的second_behind_master的值，直到这个值变成0为止；把备库B改成可读写状态，也就是把readonly设置成false；把业务请求切到备库B。
可靠性优先策略
可用性优先策略
主备切换
将必须要拿到最新结果的请求强制发到主库；将可以读到旧数据的请求发到从库
强制走主库方案
主库更新后，读从库之前先sleep一下，让主从间同步
sleep方案
每次从库查询前，先判断seconds_behind_master是否已经为0，如果还不为0，就等待此参数变为0
Master_log_File和Read_Master_Log_Pos，表示的是读到的主库的最新位点
Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是备库执行的最新位点
对比位点确保主备无延迟show slave status
Auto_Position=1，表示这对主备关系使用了GTID协议
Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合
Executed_Gtid_Set，是备库所有已经执行完成的GTID集合
对比GTID集合确保主备无延迟show slave status
判断主备无延迟方案
事务提交的时候，主库把binlog发给从库；从库收到binlog以后，发回给主库一个ack，表示收到了；主库收到这个ack以后，才能给客户端返回“事务完成”的确认
配合semi-sync方案
在从库执行，参数file和pos是主库上的文件名和位置，timeout参数可选，设置为正整数N表示这个函数最多等待N秒。正常返回结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。如果执行期间备库同步线程发生异常返回NULL，如果等待超过N秒，返回-1，如果刚开始执行的时候，就发现已经执行过这个位置了，则返回0
等主库位点方案
等待，直到这个库执行的事务中包含传入的gtid_set，返回0；超时返回1
将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包中解析出GTID的值即可
如何让返回包带GTID？
等GTID方案
过期读
主备
修改binlog的内容，拿到原库重放。前提是binlog_format=row和binlog_row_imagFULL
Flashback
误删行
全量备份+增量备份
误删表/库
延迟复制备库
账号分离
把待删除表加后缀做改名，观察一段时间后再删除，并且只能删除特定后缀表
回滚脚本
验证脚本
执行脚本
备份脚本
通过chatrr +i命令给所有重要的文件增加i权限属性
制定操作规范
防范
误删数据
如果可以使用被驱动表的索引，join语句还是有其优势
不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句尽量不要使用
会在join_buffer进行主键排序，然后去主键索引树上一个个的查找，因为按照主键顺序去主键索引树上查找，性能较高，MRR优化接近顺序读，性能会比较高
MRR
一次取出一批数据的字段到join_buffer中，然后批量join，性能会比较好。依赖MRR，因为批量join找到被驱动表的非聚集索引字段通过MRR去查找行数据。
BKA
优化
Index Nested-Loop Join
Simple Nested-Loop Join
可能会多次扫描被驱动表，占用磁盘IO资源
判断join条件需要执行M*N次对比(M、N分别是两张表的行数)，如果是大表就会占用非常多的CPU资源
可能会导致Buffer Pool的热数据被淘汰，影响内存命中率
问题
转成BKA，加索引
创建使用临时表
Block Nested-Loop Join
小表N行，大表M行
小表做驱动表
在使用join的时候，应该让小表做驱动表
join
建表语法是create temporary table...
一个临时表只能被创建它的seesion访问，对其他线程不可见，session结束后会自动删除临时表。
临时表可以与普通表同名
session A内有同名的临时表和普通表的时候，show create语句，以及增上改查语句访问的是临时表
show tables 命令不显示临时表
临时表frm存在临时目标下，#sql{进程id}_{线程id}_序列号.frm，使用select @@tmpdir查看临时文件位置
临时表
语句执行过程不可以一边读数据一边直接得到结果，需要额外内存；join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表
什么时候使用
如果对group by的结果没有排序要求，要在语句后面加order by null;尽量让group by过程用上索引，确认方法是explain结果里没有Using temporary和Using filesort;如果group by需要统计的数据量不大，尽量只使用内存临时表，可以通过适当调大tmp_table_size参数来避免用到磁盘临时表；如果数据量大，使用SQL_BIG_RESULT来告诉优化器直接使用排序算法得到group by的结果
group by
内存临时表
唯一键冲突
回滚
批量申请自增id，按照1，2，4，8翻倍递增申请，没用到的就会空了
自增id不连续
表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误
row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据
Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计
InnoDB的max_trx_id递增值每次MySQL重启都会被保存起
thread_id达到上限后会归0再递增，底层有代码逻辑去判断不冲突使用
自增id上限
自增id
逻辑架构
write pos代表当前记录位置，一边写一边后移；checkpoint代表当前要擦除的位置，擦除记录前把记录更新到数据文件，擦除后后移
如果write pos追上checkpoint，就没有记录空间了，需要把checkpoint向后推进擦除一些记录
redo log保证数据库发生异常重启时之前提交的记录不会丢失，这个能力成为crash-safe
物理日志，循环写，记录页做了什么改动
每次事务提交时都只是把redo log留在redo log buffer中
0
每次事务提交都将redo log直接持久化到磁盘
1
每次事务提交时都只是把redo log写到page cache
2
innodb_flush_log_at_trx_commit
Innodb特有日志 redolog
一个事务的binlog不论多大，都要确保一次性写入，不能被拆分开
系统给每个binlog cache分配一片内存，每个线程一个，binlog_cache_size控制单个线程内binlog cache所占内存大小，若超过参数大小，就暂存到磁盘。
事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache
逻辑日志，追加写
两种模式，statement格式会记录sql语句，row格式记录行的内容，记两条，更新前和更新后
每次提交事务都把日志写到文件系统的page cache，不持久化到磁盘
每次提交事务都把日志写到文件系统的page cache，并且持久化到磁盘
每次提交事务都把日志写到文件系统的page cache，累积N个事务后持久化到磁盘
N
sync_binlog
表示延迟多少微秒后才调用fsync
binlog_group_commit_sync_delay
表示累积多少次以后才调用fsync
binlog_group_commit_sync_no_delay_count
binlog组提交
Server层日志 binlog
InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作
prepare 阶段
第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；
第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。
commit 阶段
如果数据库在记录此事务的binlog之前和过程中发生crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。
如果数据库在记录此事务的binlog之后发生crash。此时，即使是redo log中还没有记录此事务的commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog写入成功就认为事务成功提交了）。它会扫描最后一个binlog文件，并提取其中的事务ID（xid），InnoDB会将那些状态为Prepare的事务（redo log没有记录commit 标签）的xid和Binlog中提取的xid做比较，如果在Binlog中存在，则提交该事务，否则回滚该事务。这也就是说，binlog中记录的事务，在恢复时都会被认为是已提交事务，会在redo log中重新写入commit标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在binlog中已经有了此事务的记录，所有从库也会有此事务的数据修改。
崩溃恢复
设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
将sync_binlog设置为大于1的值，比较常见是100-1000。风险是主机掉电时会丢binlog日志
将innodb_flush_log_at_trx_commit设置为2。风险是主机掉电时会丢数据
IO瓶颈
日志系统
别人改数据的事务尚未提交，我在我的事务中也能读到
一个事务还没提交时，它做的变更就能被其他事务看到
读未提交
别人改数据的事务已经提交，我在我的事务中才能读到
一个事务提交后，它做的变更才会被其他事务看到
读已提交
别人改数据的事务已经提交，我在我的事务中也不去读
一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致。
可重复读的核心是一致性读，开启事务时建立视图，后面读的数据都从视图中读。更新数据都是先读后写，读的是当前值，称为“当前读”。如果当前记录的行锁被其他事务占用的话，就需要进入锁等待
对于可重复读，查询只承认在事务启动前就已经提交完成的数据
对于读提交，查询只承认在语句启动前就已经提交完成的数据
Innodb的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性
可重复读
我的事务尚未提交，别人就别想改数据
对于同一行记录，写会加写锁，读会加读锁。当读写锁冲突时，后访问的事务必须等前一个事务完成
串行化
隔离级别
事务B虽然没提交，但是已经被A看到了
V1 2，V2 2，V3 2
事务B的更新在提交后才能被A看到
V1 1，V2 2，V3 2
之所以V2还是1，是因为事务在执行期间看到的数据必须前后一致
V1 1，V2 1，V3 2
在事务B执行将1改成2时，会被锁住。直到事务A提交后事务B才能继续执行，从A角度看V1、V2是1，V3是2
示例
show variables like 'transaction_isolation'
oracle默认rc，mysql默认rr
查看长事务
Innodb支撑RC和RR隔离级别实现是用的一致性视图(Consistent read view)
基于整个库的意思是说一个事务内，整个库的修改对该事务都是不可见的。如果在事务内select t表，另外的事务执行了DDL t表，根据发送的时间，要么锁住要么报错
事务在启动时会排一个快照，这个快照是基于整个库的
每个事务都有一个事务ID，叫做transaction id，严格递增
事务在启动时，找到已提交的最大事务ID记为up_limit_id
事务在更新一条语句时，比如id=1改为id=2，会把id=1和该行之前的row trx_id写到undo log里，并且在数据页上把id的值改为2，并且把修改这条语句的transaction id记到该行行头
再定一个个规矩，一个事务要查看一条数据时，必须先用该事务的up_limit_id<transaction id，则只能去undo log去取，去undo log查找数据的时候，也需要做比对，必须up_limit_id>transaction id，才返回数据
事务是如何实现的MVCC的
当前读都是先读后写，只能读当前值，会更新事务内的up_limit_id为该事务的transaction id
什么是当前读
快照读的情况下，RR不能更新事务内的up_limit_id，而RC每次都会把up_limit_id更新为快照读之前最新已提交事务的transaction id，则RC不能可重复读
当前读的情况下，RR是利用record + gap lock 来实现的，而RC没有gap，所以RC不能可重复读
为什么RR能实现可重复读而RC不能
事务隔离
哈希表
有序数组
Innodb使用
搜索树
索引模型
叶子节点存的正行数据，聚簇索引
主键索引
主键的值，二级索引
非主键索引
索引类型
select的数据列只用从索引中就能够取得，不必读取数据行
将被查询的字段，建立到联合索引（如果只有一个字段，普通索引也可以）里去。
覆盖索引
节省空间
会增加查询成本
不能使用覆盖索引
前缀索引
读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
索引下推
普通索引和唯一索引在读性能上相差无几，由于普通索引还需要多做一些检索和判断操作，性能稍差，但可忽略，因为大概率这些操作是在内存中的数据页上进行操作的
普通索引和唯一索引在写性能上也相差无几，由于唯一索引要做唯一性校验，性能稍差，并且不能利用changebuffer来优化性能
如果所有的更新后面都马上伴随着对这个记录的查询，那么应该关闭change buffer
只限于普通索引，适用于写多读少及写后延迟读的场景，如账单类、日志类
对唯一索引/主键索引不适用，因为它们需要唯一性的判断，需要将数据及时拿到内存中
记录的是数据操作的历史记录
针对的操作包括增加、修改、删除
本质是一个B+树，存在共享表空间中，使用changebuffer后可能导致innodb_buffer_pool的使用内存变大
master线程每隔1秒或10秒刷新，随机选取changebuffer的叶子节点进行merge操作
插入changebuffer后，立即执行了读取操作
changebuffer的空间不满足要求时(当检测到插入的数据大于制定叶的容量限度时)
merge的合并时机
change buffer
唯一索引与普通索引的选择
例如身份证号，前缀区分度不明显，后缀明显
倒序存储
在表上多创建一个字段来保存身份证的校验码，同时在这个字段上创建索引，以增加区分度
使用hash字段
缺点：没法范围查找
增大区分度
索引优化
alter table T engine=InnoDB
重建主键索引
select count(*) from tracelog where month(createtime)=7;
条件字段函数操作
字符串->数字
转换规则
实际执行select * from tracelog where CAST(traceid AS signed int)=11011;
select * from tracelog where traceid=11011;traceid是varchar类型
隐式类型转换
select * from trace_detail where CONVERT(traceid USING utf8mb4)=d.traceid;
utf8mb4是utf8的超集，utf8会转utf8mb4。自动转换的时候，为了避免数据在转换过程中由于截断导致数据错误，都是按数据长度增加的方向转换
隐式字符编码转换
数据量太少，系统觉得没必要走索引
走索引不比全表扫描代价低
in()中的数量>全表总行数x9.3%
索引失效
索引
全局读锁 Flush Tables with read lock(FTWRL)
场景：全库逻辑备份
全局锁
lock tables ...read/write
lock tables 语法除了会限制别的线程读写外，也限定了本线程接下来的操作对象
表锁
当对一个表做增删改查操作时，加MDL读锁；当对表结构做变更操作时，加MDL写锁
读锁之间不互斥，可以多个线程同时对一张表做增删改查
读写锁之间、写锁之间互斥。
元数据锁MDL
表级锁
行锁
间隙锁
间隙锁+行锁
前开后闭
next-key lock
锁
redo log写满了。系统停止所有更新操作，把checkpoint往前推进，给redo log留出空间
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是脏页，就要先将脏页写到磁盘
MySQL认为系统空闲的时候
MySQL正常关闭的情况。MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读取数据，启动速度会很快。
出现的原因
mysql5.6开始引入了page cleaner线程，专门负责刷脏
建议设置成磁盘的IOPS
innodb_io_capacity
show status like 'Innodb_buffer_pool_pages%';
Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
innodb_max_dirty_pages_pct 脏页比例上限，默认75%
InnoDB用来控制buffer pool刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘
如果MySQL服务器磁盘是传统的HDD存储设备，打开该参数，能够减少I/O磁盘寻道的开销，提高性能，而对于SSD设备，寻道时间的性能影响很小，关闭该参数，反而能够分散写操作，提高数据库性能。由于SSD设备的普及，MySQL 8.0 将该参数的默认值由1调整为0。
5.7版本为1， 8.0版本为0
innodb_flush_neighbors
脏页比例
redo log写盘速度
刷盘速度
flush-list（依据时间管理缓冲池中更改页面的列表）
LRU-list（用于管理缓冲池中非常用页面的列表）
刷脏算法
刷脏页
抖动
某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。
优点：可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。
缺点：所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
共享表空间
每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。
优点： 1.每个表都有自已独立的表空间。 2.每个表的数据和索引都会存在自已的表空间中。 3.可以实现单表在不同的数据库中移动。 4.空间可以回收（除drop table操作处，表空不能自已回收） a.Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。 b.对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。 c.对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：单表增加过大，如超过100个G。
独立表空间
先逻辑备份，然后修改配置文件my.cnf中的参数innodb_file_per_table参数为1，重启服务后将逻辑备份导入即可。
原来库中的表中的数据会继续存放于ibdata1中，新建的表才会使用独立表空间
修改配置文件my.cnf中的参数innodb_file_per_table参数为1，重启服务后将需要修改的所有innodb表都执行一遍：alter table table_name engine=innodb;
共享表空间转化为独立表空间的两种方法
innodb_file_per_table（独立表空间）
information_schema.innodb_trx
information_schema.innodb_locks
information_schema.innodb_lock_waits
事务锁
MySQL实战
0 条评论
回复 删除
下一页
