MySQL必须必会，一图概览
2024-01-18 11:23:25 4 举报AI智能生成
MySQL是一种关系型数据库管理系统，它是最流行的开源数据库之一。它使用结构化查询语言（SQL）来管理数据，并提供了丰富的功能和灵活性。MySQL支持多用户同时访问，具有高性能、高可靠性和易用性等特点。它可以用于各种应用程序，包括网站、企业级应用和嵌入式系统等。MySQL的安装和配置也非常简单，可以通过图形界面或命令行进行操作。总之，掌握MySQL是每个开发人员必备的技能之一。
作者其他创作
大纲/内容
连接层
服务层
存储引擎
模块
支持异步，同步
支持长连接，短连接
支持TCP，Unix Socket
最大十万连接数，默认151
连接
缓存（默认关闭）
解析树
词法，语法解析
语法解析
语义解析（表不存在等）
预处理器
解析器
确认最优执行路径（索引的使用，语法优化，条件消除，连接嵌套消除等）
执行计划
优化器
执行SQL，通过存储引擎获取数据
执行器
查询SQL执行过程
事务开始，经过查询过程，从buffer pool内存或者磁盘获取数据返回给server执行器
执行器修改数据
记录数据到undo log
记录数据到redo log
调用存储引擎接口，记录数据到buffer pool内存
事务提交
更新SQL执行过程
内存
Memory
可压缩
不支持和事务
只有表锁
MyISAM
支持事务
默认page 16kb
MySQL正常关闭
主线程每秒或每十秒定期刷盘
log buffer，buffer pool等空间不足
持久化数据机制
buffer pool LRU算法淘汰数据
非唯一索引的数据，避免唯一性校验，提升性能
change buffer
redo log的缓冲区
0：每秒撷取缓冲，刷盘一次
1：默认，每次提交事务就刷盘
2：先写入缓冲，操作系统每秒刷盘
数据满了强制刷盘
刷盘机制
log buffer
部分写失效的备份数据
double write buffer
InooDB
崩溃恢复
redo log
事务回滚
undo log
server层的，所有存储引擎共有的 主从同步
bin log
中继日志，从节点同步的缓冲区
relay log
慢查日志
slow query log
log
排序的数据结构
树越深，查询越慢
二叉查找树
磁盘操作多，查询慢
平衡二叉查找树AVL
减少磁盘操作
每个节点存储数据
多路平衡二叉树 B树
多适用在内存中
红黑树
存在冲突问题
innodb中不支持hash索引，底层结构中存在自适应hash索引但不需要人工创建
hash索引
只有叶子节点存储数据
叶子节点间是双向链表
加强版多路平衡查找树B+树
索引结构推演
扫表，扫库能力更强
效率更加稳定
排序能力更强
磁盘读写能力更强
B+树优势
聚簇索引
回表
覆盖索引
非聚簇索引
物理结构维度
建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。
更新数据需要维护索引
缺点
主键索引
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。
唯一索引
建立在普通字段上的索引被称为普通索引。
普通索引
对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引
前缀索引
字段特性分类
索引分类
条件过滤的操作从server层下推到存储引擎层
索引条件下推ICP
where，order join on group by的字段中
个数不能多
区分度底的不要
频繁更新的不要
无序的不要
复合索引散列性高的在前面
创建复合索引而不是修改单列索引
过长字段可以创建前缀索引
索引创建规则
函数，表达式
隐式转换
like 前面有%
负向查询可能会失效 != not in not exist
索引失效
子查询优化
id 过滤
记录上一页的最大ID，下一页查询根据id > max_id_in_last_page查询
记录上一个ID
深分页优化
索引
A 原子性
C 一致性
I 隔离性
D 持久性
四大特性
读到其他未提交的事务的数据
脏读
有更新和删除，两次读取数据不一致
不可重复度
有新增，两次读取数据行数不一致
幻读
并发问题
什么也没解决
RU
解决脏读
RC
解决不可重复读和脏读，在innodb中也解决了幻读
RR
解决幻读，不可重复读，脏读
所有语句都隐式加锁
Serrialize
隔离级别
建立快照，同一个事务无论查询多少次都能看到相同的数据
第一次查询之前已经提交的事务的修改
本事务的修改
一个事务能看到的数据版本
第一次查询之后创建的事务
活跃的（未提交的）事务的修改
一个事务不能看到的数据版本
db_row_id
db_trx_id
db_roll_ptr
隐藏字段
m_ids
min_trx_id
max_trx_id
creator_trx_id
RC 中是事务每次查询的时候建立的
RR 中是事务第一次查询的时候建立的
read view
核心思想
MVCC
事务
innodb支持行锁和表锁，myisam支持表锁
是行锁，又叫读锁
lock in share mode
共享锁
是行锁，又叫写锁，X锁
增删改默认加锁，for update
排他锁
表锁，IS
表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁
意向共享锁
表锁，IX
表示事务准备给数据行加入排他锁，也就是说事务在一个数据行加排他锁前必须先取得该表的IX锁
意向排他锁
如果加表锁需要判断每行数据是否有锁存在，意向锁就是一个标识，提高判断的效率
为什么要有意向锁
解决并发的问题
锁的作用
索引，如果没有索引就是锁表
锁住了什么
记录锁
只有RR才有间隙锁，只是为了阻塞插入
间隙锁
左开右闭
解决innodb的幻读的问题
临键锁
行锁算法
RR的间隙锁会导致锁定范围的扩大
条件列未使用到索引，RR锁表，RC锁行
RC的半一致性读可以增加update操作的并发性
RR和RC的区别
不可剥夺
环路等待
原因
顺序访问
数据排序
申请足够级别的锁
避免没有where条件的或不命中索引的操作
大事务分解成小事务
使用等职查询而不是范围查询
避免
死锁
锁
主从复制
增大数据库连接数减少客户端连接数
各种内存的大小设置
配置参数
SQL优化
增加缓存
集群模式--读写分离
业务层划分
垂直分库
水平分库分表
分库分表
如何优化
id
select type
type
possible_keys
key
key_len
ref
rows
filtered
extra
explain
性能优化
redo log 刷盘机制
buffer pool 持久化数据机制
0：默认，系统自己判断何时刷盘
1：每次事务提交后刷盘
N：每N个事务提交后刷盘
binlog 刷盘机制
Mysql
0 条评论
回复 删除
下一页
