MySQL v2.0笔记
2021-07-02 21:46:46 4 举报AI智能生成
登录查看完整内容
MySQL优化，索引实现，BufferPool，锁，事务，MVCC，日志,更新部分知识点
作者其他创作
大纲/内容
分支主题
安装
单链表
数据量大的时候高度不可控，磁盘IO不可控
维持平衡代价大
AVL树
红黑树
二叉树
不支持范围，只有=和in
hash函数要求高
冲突问题
不能利用索引来排序
也没有最左匹配等等查询优化
hash表
Btree
与B树的区别
数据分块存储，一块就是一页
所有的值是按照顺序存储的，每一个叶子到根的距离是一致的
非页节点存储数据的边界。叶子节点存储这下宁数据行的指针（MyISAM）
B+树
数据结构
表数据文件本身就是按B+Tree组织的一个索引结构文件
MySQL实现
索引结构
针对表而不是库的
frm
索引，存行的指针
myi
数据
myd
结构
非聚集
MyISAM
ibd
磁盘存储结构
聚集索引
有了主键或者唯一键才可以用来在B+树中建立索引
相当于是追加数据，而不是从中间插入数据，减少磁盘的随机IO
为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
一致性和节省存储空间
为什么非主键索引结构叶子节点存储的是主键值？
按照从左到右的顺序来排序，只有第一个索引一致的情况下，第二个索引才是有序的
联合索引的底层存储结构长什么样？
InnoDB支持事务（TRANSACTION）
InnoDB支持行级锁
innodb
存储引擎
越大优先级越高，相等的时候从上往下执行
一个select一个ID，一个select涉及多个表那么ID是一样的
就是一个复杂的SQL里可能会有很多个 SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个唯一的id
id
简单查询。不包含子查询和union
simple
复杂查询最外面的select
primary
select中的子查询
subquery
from中的子查询
结果放在一个派生表里面，子查询执行后的结果集会物化为一个内部临时表，外层查询针对这个临时的物化表
derived
union中首先执行的查询，也就是后面的查询
在使用union语句的时候，会有第三条执行计划，这个第三条执行计划意思是针对两个查询的结果依托一个临时表进行去重，这个第三条执行计划的select_type就是union_result。
union
select_type
正在查询的表
普通查询
<deriveN>，当前查询依赖id= N的查询，所以先执行id=N 的查询
子查询
table
MySQL如何查表里面的行数据
优化器就可以得到结果，不用查索引
NULL
表里只有一条元素且匹配
system
只需要匹配表里的一条唯一键的元组，几次磁盘IO就可以定位到了。
使用主键或unique键的连接查询，被驱动表就是这个
eq_ref
使用非唯一索引查询
ref
基于二级索引进行范围查询
range
二级索引全索引扫描不回表（覆盖索引）
index
聚簇索引全索引扫描
ALL
针对单表查询可能会基于多个索引提取数据后进行合并，此时查询方 式会是index_merge这种。
type
等值匹配
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引 对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能，然后用 explain 查看效果
possible_keys列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
key列
mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通 过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
存汉字长度就是 3n 字节
char(n)
存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度
varchar(n)
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引。
字符串
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
数值类型
date：3字节
timestamp：4字节
datetime：8字节
时间类型
如果字段允许为 NULL，需要1字节记录是否为 NULL
key_len
查询方式是索引等值匹配的时候，比如const、ref、 eq_ref、ref_or_null这些方式的时候，此时执行计划的ref字段告诉你的就是：你跟索引列等值匹配的是 什么？是等值匹配一个常量值？还是等值匹配另外一个字段的值？
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
rows列
就是经过搜索条件过滤之后的剩余数据的百分比。
filtered
使用覆盖索引（查找方式），不回表，索引树都能找到就是覆盖索引
Using index
查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
也可能是覆盖了但是除了前导列的范围加了其他的范围
Using index condition
使用 where 语句来处理结果，并且查询的列未被索引覆盖，或者使用了索引但是还有其他where条件
Using where
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
Using filesort
没有索引，需要创建一个临时表去重
用group by、union、distinct之类的语法的时候，万一你要是没法直接利 用索引来进行分组聚合，他会直接基于临时表来完成，也会有大量的磁盘操作
有索引的话，一边扫描一边去重，就不会创建临时表，就是Using index
Using temporary
使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是
Select tables optimized away
Extra
doc
explain工具
主键目录
主键目录也放在数据页里
基于主键的索引查找过程
主键索引（聚簇索引）
为什么索引页也要维护主键
二级索引
联合索引
聚簇索引维护
二级索引维护
插入数据时候的索引维护
耗费空间
维护耗费时间
索引缺点
为什么不使用单值索引
案例
联合索引查询原理
索引结构B+树
根据使用的SQL设计索引，代码先行
联合索引+最左：针对你的SQL语句里的where条件、order by条件以及 group by条件去设计联合索引
基数：不要在小基数字段上建立索引，还不如全表扫描了，
前缀索引：字段类型比较小的设计索引，或者取前缀做索引
where与order by冲突时优先where
读多写多，尽量少一点，需要维护索引，频繁增删改的字段不要建立索引
主键自增
两三个联合索引覆盖百分之八十，加两三个特殊索引覆盖剩下的，复杂联表交给java。计算不能让MySQL来。再复杂的SQL交给大数据来处理。报表交给java比SQL高效，报表也难以优化，一般是大数据计算好落地MySQL，不用计算功能
索引设计原则
trace工具
possible keys
计算全表扫描的成本
索引的成本计算方法
总结
MySQL内部基于成本计算实现执行计划优化
删除括号
常量替换
没有意义的直接删
物化表
半连接semi join
MySQL内部基于规则的执行计划优化
优化SQL语句的清晰语 义，方便后续在索引和数据页里进行查找。
SQL执行计划
索引数量限制
锁等待和死锁
MySQL连接池以及redo log文件
更新语句
对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算
MVCC机制不适合维护，不同事务的值不一样
可以有一个大致的维护好的值 show table status 结果中 rows
对于大数据总数维护到Redis原子计数器 incr 和 decr，有数据库和Redis的一致性问题
CUD的时候，增加数据库计数表，让他们在同一个事务里面
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
InnoBD
字段有索引：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版本才优化)。
执行计划一样，说明这四个sql执行效率应该差不多
count(*)查询优化
被驱动表：大表。驱动表：小表
小表驱动大表
select * from A where id in (select id from B)
当B表的数据集小于A表的数据集时，in优于exists
select * from A where exists (select 1 from B where B.id = A.id)
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
当A表的数据集小于B表的数据集时，exists优于in
in 和 exists关联查询优化
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动 表）里取出满足条件的行，然后取出两张表的结果合集。
驱动表200行全表扫描，然后被驱动表使用索引查询
当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表， 当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
一般 join 语句中，如果执行计划 Extra 中没有出现 Using join buffer 则表示使用的 join 算 法是 NLJ。
嵌套循环连接（有索引）Nested-Loop Join 算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
join_buffer 是无序的
适合没有索引，没有索引意味着全盘扫描，使用块嵌套就是内存扫描很快而不是磁盘
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下驱动表 的所有数据话，策略很简单， 就是分段放。
比对结束拿出来放新的再比对
分段放
基于块的嵌套循环（没有索引）Block Nested-Loop Join 算法
关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间
straight_join只适用于inner join，并不适用于left join，right join。因为left join，right join已经代表指定了表的执行顺序
对于关联sql的优化
join关联查询优化
先根据排序字段从索引里面查出来需要的ID，之后和聚簇索引join查询使用eq_ref查询
可以直接使用有主键索引+where实现索引查询，而不是ALL全表扫描
如果不是连续的话使用where查询可能会出现结果不是自己想要的
根据自增且连续的主键排序的分页查询
先使用主键和排序字段筛选出特定的区间，之后再使用这个结果回表取出所有的记录
根据非主键字段排序的分页查询
分页查询优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
基于慢sql查询做优化
（Index Condition Pushdown，ICP）
like KK%相当于=常量，%KK和%KK% 相当于范围
like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。
like查询索引下推
不在索引做任何操作
覆盖索引优化
回表的危害
最左侧列匹配
最左前缀匹配原则（like）
范围查找规则
等值匹配+范围匹配规则
在where里面使用联合索引
order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。
默认全部升序可以使用
全部指定降序也可以，Mysql8以上版本有降序索引
可以使用
有的升序有的降序不可以使用索引
order by的字段使用了复杂函数不可以使用索引
如果order by的条件有不在索引列上的，就会产生Using filesort。
不可以使用
MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
单路排序
需要回表，参与排序只有ID和排序字段
首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段
双路排序
优化SQL让使用的buffer尽可能少
大小可以改，内存够的话可以大一点就不需要回表了
sort buffer
单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
对比
Using filesort文件排序
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模∙式。
排序使用索引
借助索引的最左侧字段已经分好组
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
分组使用索引
查询语句
最左匹配
建立索引
schema优化
explain查询计划成本计算
自动调换位置
条件下推
分区优化
底层查询优化
聚簇索引
非聚簇索引
主键索引
覆盖索引
最左前缀匹配原则
索引下推
索引使用
一条SQL太慢怎么办
索引及SQL优化
mysql-connector-java就是面向Java语言的 MySQL驱动
MySQL驱动，他会在底层跟数据库建立网络连接，有网络连接，接着才能去发送请求给数据库服务器
数据库连接池应对多个连接并发，避免连接创建之后就销毁
MySQL驱动
服务端也是需要连接池的
session 建立，还会将mysql.user表的用户的权限放在session内存空间，每次请求到来都要看客户端权限
默认8小时长连接，连接完成后，如果你没有后续的动作，这个连接就处于空闲状态
可以不断开连接清空缓存的内容
注意重建连接，权限的修改才会生效，否则一直使用session里面的内存信息
管理连接和权限校验
连接器
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
在静态表里使用查询缓存
mysql8.0已经移除了查询缓存功能
缓存
“词法分析”
“语法分析”
语法树
构造执行树
分析器
执行计划生成，选择合适索引
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
优化器
调用执行引擎接口，返回查询结果
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
执行器
buffer pool 首先会判断hash表的数据页有没有加载进来， 有热点数据的缓存，以及LRU的淘汰，
子主题
一条SQL执行流程
server层
binlog 归档记录CUD操作
sql本身
资源消耗小
可能主从不一致，可能在不同库使用的索引优化之后不一致
binlog‐format=statement
记录执行后的结果
数据量大，安全性好
canal默认就是使用这个
binlog‐format=ROW（推荐）
上面两个
binlog‐format=mixed
server层记录SQL语句的执行原始逻辑
刷盘策略--sync_binlog参数
binlog（server）
通常设置为1可以严格的保证提交事务之后，数据是绝对不会丢失的
innodb_flush_log_at_trx_commit
在redo里面写入更新的物理记录之后，变为prepare阶段
在binlog里面记录当前执行的SQL语句
本次更新对应的binlog文件名称和这次 更新的binlog日志在文件里的位置，都写入到redo log日志文件里去。在redo log日志文件里写入一个commit标 记。
两阶段提交
恢复机制
一句 log 的结构
12字节的header头又分为了4个部分
redo log block
innodb_log_buffer_size
redo log buffer
流程
redo log group 事务
buffer中的block写入磁盘的时机
文件个数设置
redo重做日志（存储引擎）
开启事务之后只有执行更新操作行记录才会有新的事务ID
日志版本链
视图 = 所有未提交的事务组成的事务ID数组 + 以及一个已提交的最大的事务ID
视图不是开启事务的时候生成的，而是第一次执行select时候生成的
基于readview生成一个区间，有三部分
readview 机制
读已提交
可重复读
版本链比对规则
INSERT语句的undo log
undo回滚日志 （存储引擎）
缓存页一个就是16kb，数据比较大，缓存页刷入磁盘是随机写磁盘，性能是很差的
如果是写redo log，第一个一行redo log可能就占据几十个字节，就包含表空间好、数据页号、磁盘文件偏移 量、更新值，这个写入磁盘速度很快。
意义
日志
确定合适的大类型：数字、字符串、时间、二进制；
确定具体的类型：有无符号、取值范围、变长定长等。
尽量把字段定义为NOT NULL，避免使用NULL。
DATE
datetime
2038年
timeStamp
日期和时间
CHAR
VARCHAR
BLOB
TEXT
数据类型选择
使用undolog实现：灾备恢复的时候，redo保证持久性，但是事务可能在宕机的时候还没有执行结束，就需要undo实现事务的回滚，将redo恢复的数据但是事务没有执行完的回滚了
操作：全执行全不执行
A原子性
事务实现
数据：完整性
C一致性
使用隔离级别实现MVCC
不受其他事务影响
I隔离性
使用redolog实现
修改后的数据不能丢失
D持久性
属性
脏写
脏读
不可重复读
幻读
注意第一个是写的问题，后三个是读的问题，读的问题是使用MVCC解决，写的问题是使用锁来实现的
事务并发问题
无论是脏写还是脏读，都是因为一个事务去更新或者查询了另外一个还没提交的事务 更新过的数据。
innodb事务（并发）
读未提交
序列化
SQL标准的事务隔离级别
增加行级锁之前，InnoDB会自动给表加意向锁
执行DML语句，会自动给记录加独占锁
select from where lock in share mode
共享锁S
select from where for update
排它锁X
sql采用范围条件时，InnoDB对不存在的记录自动加间隙锁，二级索引即使是等值update也会加间隙锁
间隙锁NK
where条件边界正好是存在的数据，就会从间隙变为临键锁
临键锁
执行DQL语句
1.提高了是否可以加表锁的判断效率。有没有行锁一下就知道了不需要遍历2.实现了行锁和表锁的多粒度锁机制，使得表锁和行锁可以共存。
意向锁，排它锁，共享锁
RR隔离级别聚簇索引，等值查询是记录锁；非聚簇索引，等值查询且存在是间隙锁非聚簇索引，左闭区间范围查询，边界命中，边界是记录锁，覆盖的范围是临键锁非聚簇索引，区间范围查询无数据命中，边界后面的下一个数据是临键锁
加锁
每次select都会生成最新的read view
MVCC实现
只是在select的时候不会查出来，但是在update的时候会使用最新的视图，就会幻读，指的是其他的事务已经提交了CIA会幻读，其他事务没有提交此时是有临键锁或者间隙锁的是不会幻读的
可以解决幻读
读取是事务开启时的数据
更新是实时的数据
第一次select会生成read view
MVCC实现（读）
间隙锁 在某些情况下可以解决幻读问题。
间隙指的是当前已经有的行记录之间的间隙，锁定要更新区间所在的间隙
间隙锁是在可重复读隔离级别下才会生效。
间隙锁GAP（写或者范围条件查询的时候）范围内不存在的记录加锁防止幻读，满足恢复和复制的需要
行锁+间隙锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为 表锁。
无索引行锁会升级为表锁
临键锁Next Key（写）
可重复读（默认）
读写都会加锁
锁实现
可序列化
MYSQL的事务隔离级别
undo版本链
read-view机制
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。
实现RC
解决重复读：只在事务的第一次查询生成readview
解决幻读：根据事务ID的条件范围查询的时候，每次读到的数据 都是一样的，不会读到人家插入进去的数据
实现RR
对一行数据的读写是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操 作加锁互斥来实现的。
对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被 删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数 据。
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句， 事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。
MVCC多版本并发控制只是针对读
多个事务更新同一行数据时，是如何加锁避免脏写的？
当前session和其他session都可以读该表 。当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
读锁（悲观）（共享锁）
当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
当有人在更新数据的时候，其他的事务可以读取这行数据吗（基于MVCC）
当有人在更新数据的时候，其他的事务可以读取这行数据吗（基于共享锁）
查询操作还能加互斥锁，他的方法是：select * from table for update。
写锁（悲观）（排它锁，独占锁）
操作
悲观锁（数据库都是）
版本号机制
无锁的方式实现有锁的感觉
涉及 内存值，旧值，新值，采用自旋操作，
误以为没有更新过
ABA
循环时间太长开销会变大
只能一个共享变量的原子操作
问题
CAS算法
乐观锁（自定义）
乐观悲观锁
默认：对同一行数据的更新操作加的行级独占锁是互斥，跟读操作都是不互斥的，读操作默认 都是走mvcc机制读快照版本的！
独占锁和独占锁是互斥的，此时别 人不能更新；但是此时你要查询，默认是不加锁的，走mvcc机制读快照版本，但是你查询是可以手动 加共享锁的，共享锁和独占锁是互斥的，但是共享锁和共享锁是不互斥的
不是太建议在数据库粒度去通过行锁实现复杂的业务锁机制，而 更加建议通过redis、zookeeper来用分布式锁实现复杂业务下的锁机制，分布式系统里的复杂业务的一些锁机制依托数据库查询的时候，在SQL语句里 加共享锁或者独占锁，会导致这个加锁逻辑隐藏在SQL语句里，在你的Java业务系统层面其实是非常的 不好维护的
开销大（查找），加锁慢（）
粒度小，冲突少，并发度高
其他事务会阻塞
锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定 行的session提交
Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。
当系统并发量高的时候，Innodb 的整体性能和MYISAM相比就会有比较明显的优势了。 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现 不仅不能比MYISAM高，甚至可能会更差。
行锁
整表迁移场景
开销小，加锁快
unlock tables;
DDL语句和增删改操作，确实是互斥的，但是不是表级锁，是MySQL通用的元数据锁，这里的表锁是存储引擎自己的锁
表锁
表级的意向锁(常见)
表上面手动加的独占锁和共享锁，以及更新数据和查询 数据默认自动加的意向独占锁和意向共享锁，他们互相之间的互斥关系你如果手动加了表级的共享锁或者独占锁，此 时是会阻塞掉其他事务的一些正常的读写操作的，因为跟他们自动加的意向锁都是互斥的。
粒度
版本对比来实现
乐观
悲观
性能
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
优化
InnoDB在执行查询语句SELECT时(非串行隔离级别)，因为有MVCC不会加锁。但是update、insert、delete操作会加行 锁。
两个事务，A先修改1再修改2 ，B先修改2再修改1
场景
InnoDB会自动检测死锁使得一个回滚，另一个继续
设置超时等待参数 innodb_lock_wait_timeout;
解决
不同业务并发访问多个表的时候，应该约定以相同的顺序访问这些表
以批量的方式处理数据，事先对数据排序，保证线程按固定的顺序处理数据
在事务中，如果要更新记录，应直接申请足够级别的锁，也就是排它锁
避免
死锁
锁针对写和序列化读
间隙锁锁定间隔，防止间隔中被其他事务插入；
临键锁锁定索引记录+间隔，防止幻读；
面试题
多事务并发问题
因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能 保证各种异常情况下的数据一致性。更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。
来一个请求就直接对磁盘文件进行随机读写，然后 更新磁盘文件里的数据，虽然技术上是可以做到的，但是那必然导致执行请求的性能极差。
为什么不直接更新磁盘上的数据
SQL语句会对应修改buffer pool的缓存数据，写undo 日志，写redo log buffer ，写binlog
事务提交的时候回将redo 写入磁盘，binlog写入磁盘，之后设置redo中的事务标记为commit
最后后台的IO线程会随机将buffer pool里的脏数据写到磁盘里面
更新数据的流程
意义：增删改主要是针对内存缓存的磁盘数据，因为磁盘太慢了
系统启动就会申请一块内存区域
配置大小
缓存页
缓存页的描述信息
空间分配
free 链
缓存页hash表
flush链
缺点
为什么使用
MySQL预读机制
全表扫描
冷数据区域的数据是什么
何时到热数据区
解决：冷热数据
淘汰策略
刷盘时机
Redis的缓存预热可以联想这里
热数据的移动策略
LRU链
结合CRUD场景描述缓存以及几个链表的运作
加锁访问
加锁之后的性能
设置多个Buffer Pool来优化他的并发
多个buffer pool
直接全部复制不可取
什么是chunk
基于chunk机制动态调整buffer pool大小
缓冲池Buffer Pool--加载数据页为缓存页（innoDB存储引擎）
设置方法
引入变长字段的长度列表，解决一行数据的读取问题
二进制bit位来存储多个NULL字段值
40个bit位的数据头
实际数据
拆解
数据读取方法
行溢出
行记录
双向链表
页目录
槽
自增主键
非自增主键
维护上层索引条目
页分裂
索引页
数据页
只适合主键搜索使用
表空间
数据区extent
组
表空间以及划分多个数据页的数据区
buffer pool满了
redo log 文件写满了
电池放电
性能抖动
从库返回成功再提交事务（5.7默认）
发出从库同步的时候就提交事务，从库返回之后直接返回客户端
半同步复制
要求不高：异步+从库多线程复制数据
要求高：半同步+从库多线程复制数据
主从延迟
读写分离
MHA
高可用
主从复制架构
数据大，索引大，高度增加，查询变慢，而且缓存可以放的数据页比较小
单表一百万，数据库建好索引，用上索引
用户系统
订单系统
跨库/表分页
分库分表扩容
分库分表
数据库领域建模
数据库设计
设计数据表
数据库范式
主键索引和唯一索引的区别？
如何验证mysql的索引是否满足需求
数据索引的缺点
为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql独独喜欢B+树？
事务级别
InnoDb的事务级别是可重复读，会有幻读的情况，如果解决？
数据库事务特性？
记录锁(Record-Lock)
间隙锁
next-key 锁
InnoDB 锁类型有哪些？
limit取靠后的数据，耗时的问题。
优化经验
查询事务隔离级别 select @@tx_isolation
查询死锁日志 show engine innodb status
根据死锁日志的SQL语句分析代码
页锁
记录锁
意向锁
锁时序分析
利用索引提速
利用explain执行计划优化SQL
利用show processlist，然后kill 慢SQL
慢SQL
set global max_connections 增大连接数
show processlist，然后kill慢查询
连接过多
数据库线上问题排查
数据库
关系型数据库和非关系数据库的特点
MySQL
0 条评论
回复 删除
下一页
