

首页  思维导图  详情



MySQL核心深入剖析

2021-03-30 18:58:34   27  举报





AI智能生成

MySQL核心剖析

MySQL

数据库

Java

面试

模版推荐

作者其他创作

大纲/内容

并发和事务

事务基础

事务的定义

事务的特点

事务的基本操作

设置手动提交事务

并发事务问题

脏读

不可重复读

幻读

事务隔离级别

查看事务隔离级别

读未提交

读已提交

可重复读

串行化

MySQL的锁机制

表级锁

读锁(read lock)

写锁(write lock)

加锁和释放锁

查看表锁使用情况

元数据锁

查询语句的时候会自动添加元数据读锁

对于MDL写锁,在执行完成之后,会自动释放,和当前的事务没有关系

行级锁

共享锁(S)

排它锁(X)

加锁和释放锁

行锁的算法

临键锁

对于加锁的基本单位就是临键锁next key lock, 其中临键锁的规则是前开后闭

对于在执行过程中扫描到的记录都会加锁

对于索引上等值查询,如果查询条件是唯一索引(包括主键索引), 那么临键锁会退化为记录锁(Record Lock)

对于索引上的等值查询, 会继续扫描到第一个不符合条件的记录值, 转换为间隙锁(GAP Lock)

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

间隙锁

记录锁

意向锁

MVCC并发原理剖析

隐藏字段

Undo Log

一致性视图(Read View)

案例分析

InnoDB存储架构

Buffer Pool

磁盘预读

局部性原理

数据淘汰算法

Change Buffer

Log Buffer

课程说明

课程目标

掌握MySQL常见的存储引擎

掌握MySQL的数据存储结构B+树

掌握MySQL的索引优化

掌握MySQL的事务和锁

掌握MVCC原理和实现

掌握binlog,undolog,redolog日志系统

掌握回表, 索引覆盖,索引下推, 最左前缀等高级概念

掌握InnoDB内存架构

课程定位

有MySQL的基本使用经验

有一定的开发经验, 并且对数据结构和算法有基本的认识

参考资料

官方文档

丁奇-MySQL实战45讲

MySQL技术内幕(InnoDB存储引擎)第2版

公众号架构师之路

MySQL数据库架构

MySQL的基本认识

MySQL数据库架构

常见的存储引擎

InnoDB

MyISAM

MEMORY

Archive

NDB

常见命令

查看默认的存储引擎

查看存储引擎

指定存储引擎

查看文件存储路径

区别

索引

索引基础

什么是索引

为什么需要使用索引

设置初始数据

测试查询性能

索引分类

主键索引

唯一索引

普通索引

查看所有信息

基本操作

创建索引

主键索引

唯一索引

普通索引

添加索引

删除索引

复合索引

索引结构

Hash

Hash结构模拟

Hash算法基本流程

根据id 进行hash运算, 获取到对应的hash值22

进行取模运算 22%7=1, 找到对应的位置

判断位置上是否有值, 如果有, 则插入链表尾部, 如果没有, 插入对应的数据

Hash算法的优缺点

单值查询非常快

hash索引范围查询比较麻烦

hash索引结构没有顺序, 对数据排序需要重新进行排序

hash索引, 不能使用部分索引, 比如前缀索引, 以及像 like 'xxx%'模糊查询

如果大量的hash值相同, 会出现hash碰撞, 也会影响性能

B-Tree

常见的B树

3阶B树

4阶B树

5阶B树

5阶B树特点

一个节点可以存放多个数据(值) , 其中最多可以是4个数据

这是一个多叉树, 一个节点可以有多个子节点, 其中最多是5个子节点

数据是有顺序的, 查找速度比较快

整个树是处于又胖又矮的一个状态

树比较平衡, 每个节点的所有的子树高度比较一致

B树索引结构图

B+Tree

常见的B+树

3阶B+树

4阶B+树

5阶B+树

特点

在叶子节点存储了所有的节点数据

叶子节点直接通过链表的方式连接

B+树索引结构图

索引架构

MyISAM

主键索引

辅助索引

InnoDB

主键索引

辅助索引

聚镞索引

索引的选择

好处

提高表数据的检索效率

如果排序的列是索引列，大大降低排序成本

在分组操作中如果分组条件是索引列，也会提高效率

不足

增加数据的维护成本, 新增, 删除, 修改的时候, 需要对索引的结构进行修改维护

如何选取索引

较频繁的作为查询条件的字段应该创建索引

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件, 比如性别

更新非常频繁的字段不适合创建索引

不会出现在WHERE 子句中的字段不该创建索引

索引分析工具

Explain

基本使用

核心字段

id

select_type

union

table

type

possible_keys

key

key_len

ref

rows

Extra列

Profiling

开启

监控SQL语句

trace

开启trace

跟踪分析SQL

索引高级

索引的选择性

回表

索引覆盖

索引下推ICP

前缀索引

索引优化

前缀索引优化

最左前缀匹配

复合索引优化

最左前缀字符串匹配

范围查询

函数转换

Join优化

连接查询的基本流程

Index Nested-Loop Join

Simple Nested-Loop Join

Block Nested-Loop Join

优化原则

小结果集驱动大结果集

尽量使用Index Nested-Loop Join, 给匹配的条件添加索引

给定 join buffer size 设置适当的大小, 增大内存区域以后, 缓存的数据量就会越多, 就会减少被驱动表的扫描次数, 我们可以通过参数join_buffer_size进行调整

减少不必要的字段的查询

排序优化

排序原理

排序算法

单路排序

双路排序

排序的几点思考

排序的优化

尽量保证使用单路排序

去掉不必要的返回字段

适当增加sort_buffer的大小

SQL优化的常用原则

创建的索引的选择性需要比较高, 有比较的区分度, 对于性别等字段不适合创建索引

强烈建议不要使用select * , 如果确实需要所有的字段, 建议全部写出来

对于常用的查询尽可能多用复合索引, 避免创建多个单个索引

推荐使用自增的和业务无关的主键

查询尽量使用索引, 包括索引覆盖, 索引下推等特性, 减少回表的次数

对于where 判断条件, order 排序字段, join 关联字段推荐创建索引

索引的个数不用太多

对于频繁更新的字段, 不适合创建索

对于Join连接尽量使用Index Nested-Loop Join方式, 尽量避免使用Block Nested-Loop Join

对于Join连接,使用小结果集驱动大结果集

对于Join连接可以适当的调整join_buffer_size

对于Join连接减少不必要的查询字段

对于order by 排序的时候, 尽量使用索引列, 并且使用limit限制查询数量

对于order by 查询的时候, 尽量使用单路排序

适当的设置InnoDB的buffer大小innodb_buffer_pool_size, 一般设置为内存的60%-80%

优化案例

filesort 优化

连接优化

复合索引_前缀索引

排序优化

 Collect

Get Started

核心模块设计图

 Collect

Get Started

深入剖析ARM Cortex-A8

 Collect

Get Started

PRISM-RUNNER-核心池

 Collect

Get Started

Agent 智能体核心架构与深度认知





0 条评论

下一页