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