MySQL 思维导图模板_ProcessOn思维导图、流程图

索引

聚簇索引

主键索引

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

查询只扫描一行

非聚簇索引

唯一索引

为了值唯一

普通索引

可以使用change buffer

节省的则是随机读磁盘的 IO 消耗

扫描多

为了查询更快

联合索引

覆盖索引

数据结构

哈希

适用于只有等值查询

有序数组

适用于静态存储引擎

B树

很好地配合磁盘的读写特性

优化

最左前缀原则

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

考虑空间

索引下推

索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

MySQL选索引

扫描行数

区分度

show index 查看基数

采样统计

analyze table t 重新统计

选错索引

force index 强制指定索引

建立更适合的索引

删除误用的索引

字符串索引

倒叙索引

会增加查询扫描次数，并且不能使用覆盖索引

哈希索引

索引失效

索引上使用了函数

索引字段做了操作

where a +1 >1000

隐式类型转换

where "10" > 9

隐式字符编码转换

utf8 - utf8mb4

Server层

连接器

分析器

判断表存在

优化器

执行器

接SQL引擎

查询缓存

新版本已废弃

binlog

数据恢复

主从模式的数据一致性

数据格式

statement

SQL语句原文

最后有COMMIT

有些 statement 格式的 binlog 可能会导致主备不一致

row

记录所有的改动值

最后有XID event

占空间

mixed

折中

先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件

一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里

复制表

mysqldump

导出CSV再导入

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

物理复制

1. 执行 create table r like t

2. 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

3. 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

此时表只读

4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；需要rw权限

5. 执行 unlock tables，这时候 t.cfg 文件会被删除；

6. 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间

函数/关键字

count

主键

把每一行的 id 值都取出来,判断是不为空，按行累加

其他字段

定义不为null，从记录里面读出这个字段，判断不能为 null，按行累加

可以为null，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加

count(1)

遍历整张表，但不取值, 判断是不可能为空的，按行累加

count(*)

count(*) 肯定不是 null，按行累加。

order by

全字段排序

一次回表

rowid排序

两次回表

可使用覆盖索引

不用回表

join

尽量小表驱动大表

两个表按照各自的条件过滤完，计算参与 join 的各个字段的总数据量，数据量小的那个表，应该作为驱动表。

被驱动表最好有索引

Index Nested-Loop Join

有索引的join

Simple Nested-Loop Join

真正的遍历join，一般不用

Block Nested-Loop Join

读到内存中join，快些

join_buffer_size 控制内存大小

内存不足，分组 join，需要多次遍历被驱动表

Multi-Range Read 优化

大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

对被驱动表的主键进行排序

Batched Key Access

NLJ优化，利用join_buffer

使用临时表

按条件先去取出需要join的内容，做成临时表

使用临时表join，减少扫描行

hash join

MySQL不支持，应用层用map处理最好

straight_join关键字

指定join顺序

group by

临时表，来记录并统计结果

调大tmp_table_size，避免使用磁盘临时表

优化

使用索引

直接排序- 数据量大

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

如果对结果没有排序要求，要在语句后面加 order by null；

内存

change buffer

sort buffer

有序数组

redolog buffer

binlog cache

join buffer

无序数组

数据页

Buffer Pool管理内存

加速更新

加速查询

内存命中率

LRU

全表扫描会导致缓存被全刷新

改进LRU

按照 5:3 的把 LRU 链表分成 young 和 old 

处于 old中，在缓存中存在的时间超过了 1 秒，才它移动到链表头部

全表扫描只会影响old部分内存

问题

短连接风暴

先处理掉那些占着连接但是不工作的线程

减少连接过程的消耗。

跳过权限验证，–skip-grant-tables

慢SQL

查询长时间不返回

等 MDL 锁

前面有改表操作，即MDL写锁

show processlist

Waiting for table metadata lock

等行锁

有insert或update，即行写锁

等 flush

Waiting for table flush

查询慢

坏查询不一定是慢查询

优化

查询带上lock in share mode，返回当前读

可能的原因

索引没有设计好

SQL 语句没写好

SQL审计

MySQL 选错了索引

连接慢

表名补全的功能，客户端构建哈希表

-A / -q跳过

QPS 突增问题

线程

kill失败

发信号

线程中依靠埋点判断信号

发信号没走到埋点判断也不行

可以调大并发度innodb_thread_concurrency

自增主键

MyISAM 引擎的自增值保存在数据文件中。

InnoDB 引擎的自增值，其实是保存在了内存

第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

不连续

唯一键冲突

事务回滚

批量插入，批量申请id，没用完

上限

表的自增 id 达到上限后，再申请时它的值就不会改变，导致继续插入报主键冲突

row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。

Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。

InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，会脏读

thread_id 会归零重记

分区表

分布式

HA

主备多从

主备延迟

备库机器性能差

备库的压力大

大事务

必须等事务执行完成才会写入 binlog

一次性地用 delete 语句删除太多数据

就是大表 DDL

主备切换

基于位点的主备切换

要先主动跳过这些错误

set global sql_slave_skip_counter=1;

slave_skip_errors 参数

基于 GTID 的主备切换

故障检测

select 1

查表判断

更新判断

更新一个时戳

内部统计

读写分离

客户端直连

带 proxy

延迟解决方案

强制走主库方案

sleep

判断主备无延迟

配合 semi-sync

等主库位点

等 GTID

策略

可靠性优先

建议

可用性优先

复制策略

按表分发策略

两个事务更新不同的表，它们就可以并行

按行分发策略

如果两个事务没有更新相同的行，它们在备库上可以并行执行

要求 binlog 格式必须是 row

按行并行策略在决定线程分发的时候，需要消耗更多的计算资源

表物理空间大小

回收表磁盘

alter table t engine=InnoDB；

事务

特性

一致性

数据一致

隔离性

事务相互不影响

原子性

要么全做，要么全不做

持久性

存盘

隔离级别

读未提交

A事务可以读到B事务未提交的内容

读已提交

解决脏读

A事务可以读到B事务已提交的内容

一致性读

解决脏读、一致性读

串行化

MVCC

快照读/一致性读

事务内的select是快照读

当前读

事务内更新需要当前读

select 语句如果加锁，也是当前读

组提交

多个事务一起提交

减少IOPS

binlog_group_commit_sync_delay/binlog_group_commit_sync_no_delay_count

WAL

预写日志

两阶段提交

redo log和binlog达成共识后一起提交

一起回滚

流程

一起prepare，同时都写如磁盘，在一起commit

恢复时，有prepare和commit就提交，只有prepare就看其他人是否都这样

还要有checksum

将随机写转换成了顺序写

存储引擎

Innodb

redo log

事务提交，崩溃恢复

两阶段提交，和binlog配合

节省的是随机写磁盘的 IO 消耗（转成顺序写）

undo log

事务失败回滚

锁

全局锁

Flush tables with read lock

备份

表级锁

MDL锁

DDL加写锁

DML加读锁

有写就互斥

改表会导致卡主

ALTER TABLE tbl_name NOWAIT add column<br>ALTER TABLE tbl_name WAIT N add column

表锁

lock tables xxx read/write<br>unlock tables

连接断开自动解锁

连接池会有问题，需要手动释放

行级锁

gap lock

解决幻读

行锁

两阶段锁

需要时加上，事务结束释放

死锁

死锁检测

业务层保证并发

考虑通过将一行改成逻辑上的多行来减少锁冲突，原来的一行等于10行相加，略类似ConcurrentHashMap

next-key lock

前开后闭区间

gap lock + 行锁

加锁规则

锁是加在索引上

基本单位是 next-key lock

查找过程中访问到的对象才会加锁

覆盖索引会导致主键索引未加锁

索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

数据恢复

误删行

Flashback

误删库 / 表

全量备份 + binlog

需要有binlog

预防误删库 / 表的方法

分权

临时表

一般用于处理比较复杂的计算逻辑

线程自己可见

可以重命名

线程退出自动删除

二维表结构

权限管理

权限内存中和数据库中都有，使用正常的grant/revoke没有问题

手动修改mysql.user表，导致内存与表中数据不一致，才需要flush privileges