1. 一条查问 SQL 的执行过程
- 客户端 => 连接器 权限验证(连贯断开开释内存,对长连贯须要 reset_connection)
- 查问缓存(尽量不要应用,缓存命中率比拟低,8.0 后齐全删除)
- 分析器 语法分析 词法剖析 MySQL 晓得你要做什么
- 优化器 优化查问打算:抉择哪个索引?表连贯的时候决定表的连贯程序
- 执行器:调用存储引擎接口取得每行数据,将合乎的后果存储在后果集中
2. 一条更新 SQL 的执行过程
- redo log:先写日志,再写磁盘 => 保障 crash safe
-
Update Set WHERE id = 2
1. 执行器找存储引擎取 ID = 2 的这一行,ID 是主键索引,引擎间接通过树搜寻取得该行。如果原本就在内存中,间接返回;否则,从磁盘中加载这一行 1. 执行器将该行数据更改后调用引擎接口写入这行新数据 1. 引擎将数据更新到内存,同时写 redo log,redo log 处于 prepare 状态,同时引擎告知执行器随时能够提交 1. 执行器写 binlog 并落盘 1. 执行器调用引擎的提交事务接口,引擎将 redo log 的状态改为提交,更新实现
- redo log 和 bin log 都能够示意事务的提交状态,而两阶段提交保障了二者逻辑一致性
- 全量备份 + bin log:复原、备库
3. 事务隔离
- 读未提交是指,一个事务还没提交时,它做的变更就能被别的事务看到。
- 读提交是指,一个事务提交之后,它做的变更才会被其余事务看到。
- 可反复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是统一的。当然在可反复读隔离级别下,未提交变更对其余事务也是不可见的。
- 串行化,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当呈现读写锁抵触的时候,后拜访的事务必须等前一个事务执行实现,能力继续执行。
- 可反复读的实现:回滚段 => 尽量避免长事务
4. 索引
- 为什么要用自增主键作为主键索引?(话中有话主键是间断的):益处:每次插入都是追加操作,不须要移动其余记录,也不波及叶子节点的决裂。而采纳有业务逻辑的字段作为主键,则不能保障是有序插入,波及节点的决裂、合并。
- 自增主键字段空间更小(4 或 8),则一般索引的叶子节点小,单页存储更多。
- 个别状况下都应用自增主键,只有 KV 场景(只有一个惟一索引)例外。
- 笼罩索引:不用回表
- 最左前缀:建设联结索引时思考索引的程序
5. 行锁
- 两阶段锁:当须要时加锁,事务提交时才开释锁 => 尽可能将影响并发度的锁后推
- 高并发时,死锁检测很有可能占用大量 CPU 资源
6. 事务到底是隔离的还是不隔离的
视图
- view => 查问语句定义的虚构表
- 实现 MVCC 用到的一致性读视图 =>
事务 ID 每行数据不同版本 row_trx_id
为每个事务结构了事务启动时以后沉闷的事务 ID (启动了然而尚未提交)
低水位:数组外面最小 trx_id
高水位:以后零碎创立的最大 trx_id + 1
- 如果落在绿色局部,示意这个版本是已提交的事务或者是以后事务本人生成的,这个数据是可见的;
- 如果落在红色局部,示意这个版本是由未来启动的事务生成的,是必定不可见的;
- 如果落在黄色局部,那就包含两种状况
a. 若 row trx_id 在数组中,示意这个版本是由还没提交的事务生成的,不可见;
b. 若 row trx_id 不在数组中,示意这个版本是曾经提交了的事务生成的,可见。
以后读:update 或 select for update / lock in share mode
RC 和 RR 的区别就是 RC 每个语句创立新的视图,RR 事务开始创立新的视图,前面查问沿用
7. 一般索引
change buffer => 非惟一一般索引
8. 空洞
删除只是打标记示意可复用,插入也可能造成页决裂导致呈现空洞
1. 解体复原
Prepare(工夫 A)=> binlog(工夫 B)=> commit
- 如果 A 处 crash,则事务回滚
-
如果 B 处 crash
- 如果 redo log 有了 commit 标记,则提交
-
如果 redo log 只有 prepare
- 如果 bin log 是残缺的,则提交
- 否则回滚事务
- 为什么须要两阶段提交?先写 redo log,再写 bin log,解体复原的时候,两个日志都残缺即可,行不行?
不行,这是一个 2PC 的问题,对于 InnoDB 来说,redo log 提交后就无奈回滚(如果回滚,则可能笼罩其余事务的更新);而如果 redo log 胜利,bin log 失败,此时就会呈现不统一。
-
只用 bin log 能不能实现解体复原?
- 历史起因 不提供 crash safe
- bin log 不记录数据页的更改,解体复原时无奈复原尚未写入到磁盘的提交
-
只用 redo log 行不行?
- bin log 在主从复制方面还有不可代替的作用
- bin log 能够归档,而 redo log 是循环写
2. explain 应该关注哪些字段?
Column 含意
- id 查问序号
- select_type 查问类型(simple、primary、union)
- table 表名
- partitions 匹配的分区
-
type join 类型
- index:索引全表扫描
- index_merge:两个或以上的索引,最初取交加或并集
- range:范畴查问
- ref:匹配多行记录(非惟一索引)=> 多表 join 时,后面表的一行能匹配到前面表的多行
- eq_ref:匹配一行记录(主键或惟一索引)=> 多表 join 时,后面表的一行能匹配到前面表的一行
- prossible_keys 可能会抉择的索引
- key 理论抉择的索引
- key_len 索引的长度
- ref 与索引作比拟的列(如果是应用的常数等值查问,这里会显示 const,如果是连贯查问,被驱动表的执行打算这里会显示驱动表的关联字段)
- rows 要检索的行数 (估算值)
- filtered 查问条件过滤的行数的百分比(存储引擎返回的数据在通过 server 过滤后,有多少满足比例)
-
Extra 额定信息
- distinct
- Using filesort:不能通过索引进行排序,须要额定的索引操作
- Using index:应用笼罩索引
- Using temporary:应用长期表
3. order by
- 全字段排序
- rowid 排序:全字段长度太大,一个文件中放入的行数少,文件多,须要很屡次归并排序,那么缩小不必要的字段,只保留排序字段和 id,最初从主键索引按序返回
- 联结索引:防止排序
- 笼罩索引:防止二次查问
4. 幻读
- 语义问题:并没有保障所有的行都被选中
- 数据不统一:先插入后更新,bin log 记录的和理论产生的不统一
- 间隙锁:开区间
- next-key lock:前开后闭区间
-
加锁准则:
- 准则 1:加锁的根本单位是 next-key lock。
- 准则 2:查找过程中拜访到的对象才会加锁。
- 优化 1:索引上的等值查问,给惟一索引加锁的时候,next-key lock 进化为行锁。
- 优化 2:索引上的等值查问,向右遍历时且最初一个值不满足等值条件的时候,next-key lock 进化为间隙锁。
- 一个 bug:惟一索引上的范畴查问会拜访到不满足条件的第一个值为止。
5. 主备统一
bin log -> dump_thread -> io_thread -> sql_thread
bin log 格局
- statement:SQL 语句(当应用 Limit 时,如果索引抉择不同,可能造成主备更改不统一)
- row:逻辑更改
- mixed
双 M 构造,互为主备
可用性:
- 备库机器性能差
- 备库跑一些经营剖析内容
- 大事务执行很久
主备切换的策略:
可靠性优先:(一旦检测到主备差距达到阈值),则主库改为 read only,期待备库追赶
可用性优先:备库间接可读写,可能产生数据不统一(同时接管客户端申请和 relay log)
可用性取决于主备提早
问题:
- 如何正确的主备切换?次要是如何持续同步 relay log:GTID(global transaction ID)
- 读写拆散哪些坑?过期读:semi-sync、判断是否齐全同步、强制走主库
6. 如何抉择驱动表?
应该尽量抉择行数少的表(小表)作为驱动表。
当可能应用被驱动表的索引时,就能够走树搜寻。Index Nested Loop
不能应用索引时,就将每行取出,逐个比拟。Block Nested Loop
MRR:multi-range-read
BKA:Index Nested Loop 为了用上 MRR
目标:应用上被驱动表的索引
长期表上建设索引
哈希 join,而不是无序数组
7. 长期表
长期表只在对其余线程不可见,session 完结时主动删除,可用于排序