MySQL
2023-10-08 09:48:16 0 举报AI智能生成
学习笔记
作者其他创作
大纲/内容
索引
数据结构
B树
B+树
Hash
联合索引
存储引擎
MyISAM
索引实现
InnoDB
索引实现
存储数据计算方式
假设数据的大小是1k，那么一个页可以存放16行这样的数据
假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节
假设树高度为3，那么索引2层，数据1层
计算结果
EXPLAIN
作用
使用
列
id
select_type
table
partitions
type
system
const
eq_ref
ref
range
index
all
possible_keys
key
key_len
ref
rows
filtered
Extra
优化
排序
单路排序
双路排序
索引设计原则
代码先行，索引后上
联合索引尽量覆盖条件
不要在小基数字段上建立索引
长字符串我们可以采用前缀索引
where与order by冲突时优先where
基于慢sql查询做优化
分页查询优化
ID自增且连续的主键排序
id>数量，limit 需要数量
优化原理：查询走了索引，查询数量较少
非主键字段排序
根据主键和排序字段搜索出所需记录，然后根据主键再去匹配，找到完整记录
优化原理：直接找出所有字段，mysql分析认为需要记录太多，回表太麻烦，不如直接filesort。分两次查询，第一次走索引，第二次匹配有限记录，去掉了filesort
表JOIN关联原理及优化
嵌套循环连接算法 Nested-Loop Join（NLJ）
算法流程
取出驱动表中的一行数据
在这行数据中取到关联字段
根据关联字段取出另一张表中的数据
遍历驱动表中的数据，循环以上三个步骤。返回两张表的结果合集
总结
关联字段需要添加索引，否则被驱动表每次都要全表扫描，有10000条数据就要扫描10000次，效率会很低
小表驱动大表
写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间
基于块的嵌套循环连接算法 Block Nested-Loop Join (BNL)
算法流程
取出驱动表中的数据，放到Join_buffer中
扫描被驱动表
把被驱动表中的每一行数据取出来和join_buffer中的数据做对比
返回结果合集
总结
扫描次数看join_buffer中一次能放多少数据
in和exists优化
in
原则：小表驱动大表
exists
EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
COUNT(*)优化
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)
总结
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出 字段，所以理论上count(1)比count(字段)会快一点。
count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用 count(列名)或count(常量)来替代 count(*)。
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索 性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
优化方法
查询mysql自己维护的总行数
show table status
将总数维护到Redis里
增加数据库计数表
事务
特性
A 原子性
C 一致性
I 隔离性
D 持久性
问题
脏写
脏读
不可重读
幻读
隔离级别
Read uncommitted
Read committed
Repeatable read
Serializable
MVCC
全称
多版本并发控制 Multi-Version-Concurrency-Controller
基础概念
Undo log 版本链
Innodb存储引擎，每个聚簇索引中都存在两个隐藏字段
trx_id 事务ID
roll_pointer
read view机制
核心内容
m_ids
min_trx_id
max_trx_id
creator_trx_id
判断流程
如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问
4种隔离级别下的调用方式
Read uncommitted
直接读取记录的最新版本就好了
read commmitted
每次读取数据前都生成一个ReadView
repeatable read
在第一次读取数据时生成一个ReadView
serializable
使用加锁的方式来访问记录
锁
锁分类
性能上分
乐观锁
悲观锁
对数据操作上分
读锁（共享锁，S锁(Shared)）
写锁（排它锁，X锁(eXclusive)）
对数据操作粒度上分
表锁
语法
手动增加锁
查看表上加过的锁
删除表锁
支持的存储引擎
Innodb
MYIsam
适用场景
以查询为主，只有少量按索引条件更新数据的应用
加锁方式
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁
通过给索引上的索引项加锁来实现的
行锁
支持的存储引擎
Innodb
加锁方式
通过给索引上的索引项加锁来实现的
使用场景
有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用
总结
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁
锁优化
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
扩展内容
间隙锁
锁的就是两个值之间的空隙
例子
临键锁
是行锁与间隙锁的组合。
间隙锁的例子，三个范围区间整合就是就是临键锁的范围。（3,20]
无索引行锁会升级为表锁
0 条评论
下一页
