MySQL
2020-12-02 09:53:54 0 举报AI智能生成
MySQL知识体系
作者其他创作
大纲/内容
MySQL
索引
聚簇索引
主键索引
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
查询只扫描一行
非聚簇索引
唯一索引
为了值唯一
普通索引
可以使用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
主键
其他字段
定义不为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，避免使用磁盘临时表
使用索引
直接排序- 数据量大
如果对结果没有排序要求，要在语句后面加 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 columnALTER TABLE tbl_name WAIT N add column
表锁
lock tables xxx read/writeunlock 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
0 条评论
下一页
