长沙网站建设开发,职业培训机构资质,wordpress禁止别人访问,网络推广有哪些途径前言#xff1a;这篇文章主要总结事务#xff0c;锁、索引的一些知识点#xff0c;然后分享一下自己学习小心得#xff0c;我会从点到线在到面展开说说#xff0c;对于学习任何知识#xff0c;我们都应该藐其全貌#xff0c;不要一开始就选入细节
基础
一、基础架构这篇文章主要总结事务锁、索引的一些知识点然后分享一下自己学习小心得我会从点到线在到面展开说说对于学习任何知识我们都应该藐其全貌不要一开始就选入细节
基础
一、基础架构一条查询sql是怎么执行的 二、 日志系统一条更新语句是怎么执行的
1.redolog与binlog区别
① redolog是innodb存储引擎实现而binlog是在Server层实现
② redolog是物理存储而binlog是逻辑存储
③ redolog是循环写入空间会用尽所以每隔一段时间就需要擦除而binlog是追加写入所以说他是归档日志
2、二阶段提交是怎么样
3、第一步写入redolog处于准备阶段
4、第二步写入binlog
5、第三步redolog提交阶段、
备注将 redo log 的写入拆成了两个步骤prepare 和 commit这就是两阶段提交。
我的理解
从 “两阶段提交”的执行流程看“ binlog 成功redo log失败”的场景备注数据库 crash-重启后会对记录对redo log进行检查
1、如果 redo log 已经commit则视为有效。
2、如果 redo log prepare 但未commit则check对应的bin log记录是否记录成功。
① bin log记录成功则将该prepare状态的redo log视为有效
② bin log记录不成功则将该prepare状态的redo log视为无效 3、一些参数补充说明
binlog日志模块
sync_binlog1的时候表示每次事务的 binlog 都持久化到磁盘建议设置成1
补充binlog一共有三种模式
① statement模式记录sql语句优点占用内存少如果出现函数可能到回到数据不一致
② row模式直接记录数据占用内存大
③ mixed以上两种模式的混合
redolog日志模块
innodb_flush_log_at_trx_commit{0|1|2} 指定何时将事务日志刷到磁盘默认为1
0表示每秒将log buffer同步到os buffer且从os buffer刷到磁盘日志文件中。
1表示每事务提交都将log buffer同步到os buffer且从os buffer刷到磁盘日志文件中。
2表示每事务提交都将log buffer同步到os buffer但每秒才从os buffer刷到磁盘日志文件中。
问题前面我说到定期全量备份的周期“取决于系统重要性有的是一天一备有的是一周一备”。那么在什么场景下一天一备会比一周一备更有优势呢或者说它影响了这个数据库系统的哪个指标
这个需要根据业务进行评估空间换时间
三、 事务隔离为什么你改了我还是看不见
1、Mysql有哪些隔离级别
① 读未提交别人改数据的事务尚未提交我在我的事务中也能读到。
② 读已提交别人改数据的事务已经提交我在我的事务中才能读到。
③ 可重复读别人改数据的事务已经提交我在我的事务中也不去读。
④ 串行我的事务尚未提交别人就别想改数据
2、事务隔离的实现
RC每次查询的时候都会去创建一个视图
RR事务启动的时候就会创建视图
RU不存在视图
串行化不存在视图这个概念都是通过加锁方式实现事务的隔离
3、事务启动方式
① 把自动提交关掉set autocommit0
② 例子-显示启动事务
begin;
update t1 set a1 where id 2;
commit;
rollback;这里有个需要注意的点begin的时候是还没启动事务在执行第一条sql才会启动
③ 如何查询长事务查询持续时间超过60秒的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))60; *4、**问题如何避免长事务对业务的影响*
1、set global general_logon;开启通用日志在业务功能测试阶段要求输出所有的 general_log分析日志行为提前发现问题
1、确认是否有不必要的只读事务。把select语句放在事务外
2、通过 SET MAX_EXECUTION_TIME 命令避免单sql执行时间过长
3、监控 information_schema.Innodb_trx 表设置长事务阈值超过就报警 / 或者 kill
4、innodb_undo_tablespaces设置成 2即是不是用系统表空间使用独立空间如果真的出现大事务导致回滚段过大这样设置后清理起来更方便
四、 深入浅出索引上
总结
*第一点索引模型的选择*
1.索引的作用提高数据查询效率
2.常见索引模型哈希表、有序数组、搜索树
3.哈希表键 - 值(key - value)。
4.哈希思路把值放在数组里用一个哈希函数把key换算成一个确定的位置然后把value放在数组的这个位置
5.哈希冲突的处理办法链表
6.哈希表适用场景只有等值查询的场景
7.有序数组按顺序存储。查询用二分法就可以快速查询时间复杂度是O(log(N))
8.有序数组查询效率高更新效率低 9.有序数组的适用场景静态存储引擎。
10.二叉搜索树每个节点的左儿子小于父节点父节点又小于右儿子
11.二叉搜索树查询时间复杂度O(log(N))更新时间复杂度O(log(N))
12.数据库存储大多不适用二叉树因为树高过高会适用N叉树
*第二点Innodb的索引模型介绍*
\1. InnoDB中的索引模型BTree
2.索引类型主键索引、非主键索引 主键索引的叶子节点存的是整行的数据(聚簇索引)非主键索引的叶子节点内容是主键的值(二级索引)
3.主键索引和普通索引的区别主键索引只要搜索ID这个BTree即可拿到数据。普通索引先搜索索引拿到主键值再到主键索引树搜索一次(回表)
\4. 一个数据页满了按照BTree算法新增加一个数据页叫做页分裂会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并合并的过程是分裂过程的逆过程。
5.从性能和存储空间方面考量自增主键往往是更合理的选择。
*问题说说下面重建索引的一些方案是否合理*
*1.重建k索引*
*alter table T drop index k;*
*alter table T add index(k);*
*2.重建主键索引*
*alter table T drop primary key;*
*alter table T add primary key(id);*
*重建索引 k 的做法是合理的可以达到省空间的目的。但是重建主键的过程不合理。不论是删除主键还是创建主键都会将整个表重建。所以连着执行这两个语句的话第一个语句就白做了。这两个语句你可以用这个语句代替 alter table T engineInnoDB*
五、 深入浅出索引下
总结:
\1. 覆盖索引如果查询条件使用的是普通索引或是联合索引的最左原则字段查询结果是联合索引的字段或是主键不用回表操作直接返回结果减少IO磁盘读写读取正行数据
\2. 最左前缀联合索引的最左 N 个字段也可以是字符串索引的最左 M 个字符
\3. 联合索引根据创建联合索引的顺序以最左原则进行where检索比如agename以age1 或 age 1 and name‘张三’可以使用索引单以name‘张三’ 不会使用索引考虑到存储空间的问题还请根据业务需求将查找频繁的数据进行靠左创建索引。
\4. 索引下推like hello%’and age 10 检索MySQL5.6版本之前会对匹配的数据进行回表查询。5.6版本后会先过滤掉age10的数据再进行回表查询减少回表率提升检索速度
*问题*
*CREATE TABLE geek (*
*a int(11) NOT NULL,*
*b int(11) NOT NULL,*
*c int(11) NOT NULL,*
*d int(11) NOT NULL,*
*PRIMARY KEY (a,b),*
*KEY c (c),*
*KEY ca (c,a),*
*KEY cb (c,b)*
*) ENGINEInnoDB;*
*针对下面两条sql以上那个索引可以不用为什么*
*select * from geek where cN order by a limit 1;*
*select * from geek where cN order by b limit 1;*
*答案ca索引可以不用永久记住最左匹配以最左为主*
六、 全局所有和表锁给表加个字段有这么多障碍吗
1. *MySQL从加锁范围上分为哪三类?*
全局锁、表级锁、行级锁
*2. 全局锁加锁方法的执行命令是什么?主要的应用场景是什么?*
flush tables with read lock 应用场景全库逻辑备份
*3. 做整库备份时为什么要加全局锁?*
不加锁的话系统备份得到的库不是同一个逻辑时间点的会导致数据不一致。
*4. MySQL的自带备份工具, 使用什么参数可以确保一致性视图, 在什么场景下不适用?*
使用参数-single-transaction 只适用于所有表使用事务引擎的库部分表使用的引擎不支持事务的话不能用该方法。
*5. 不建议使用set global readonly true的方法加全局锁有哪两点原因?*
① 有些系统用readonly判断是主库还是备库修改这个值对整个系统影响太大
② 如果客户端发生异常数据库就会一直保持readonly会导致长时间无法写入数据风险很高。
*6. 表级锁有哪两种类型? 各自的使用场景是什么?*
①表锁。lock tables xxx read/write。数据库引擎不支持行锁时才会用到表锁。
②元数据锁。MDL分为MDL 读锁和 MDL 写锁。执行DML的时候会申请 MDL读锁执行DDL的时候会申请 MDL写锁。
7. *MDL中读写锁之间的互斥关系怎样的?*
读读共享读写互斥写写互斥。
*8. 如何安全的给小表增加字段?*
①减少长事务避免跟长事务竞争 MDL锁如果获取 MDL写锁阻塞会影响后面 MDL读锁获取导致所有会话阻塞。
②Alter table语句设置超时时间超过时间未获取到 MDL写锁则放弃后面再进行重试避免影响后面的会话。
问题
备份一般都会在备库上执行你在用–single-transaction 方法做逻辑备份的过程中如果主库上的一个小表做了一个 DDL比如给一个表上加了一列。这时候从备库上会看到什么现象呢
DDL binlog同步到备库后此时备库有MDL读锁而同步过来的DDL变更到备库上需要MDL写锁那么这个DDL会被阻塞所以该DDL不会反应在备份的数据里面。当使用该备份数据进行恢复时由于加了一列那么恢复会异常
七、 行锁的功过怎么减少行锁对性能的影响
\1. 行锁两阶段协议不是事务开始的时候开启而是语句执行的时候上锁
这个就涉及到插入和更新sql的先后顺序去优化减少锁冲突
\2. 死锁互相持有对方资源
\3. 死锁检测优劣怎么避免大量死锁检测、高并发下避免死锁检测带来的负面影响
① 确保业务上不会产生死锁直接将死锁检测关闭。innodb 自带死锁检测
② 在数据库中间件中统一对更新同一行的请求进行排队控制并发度。
③ 业务逻辑上进行优化将一行数据分解成多行降低写入压力。
八、 事务到底是隔离还是不隔离
\1. mysql中有两个视图的概念
① 一个是view 用来创建虚表
② 另一个一致性视图主要用来解决rr和rc问题
“快照”在 MVCC 里是怎么工作的
参考事务隔离流程图
实战
一、唯一索引和普通索引怎么选
1、唯一索引和普通索引怎么选
① 查询情况下普通索引和唯一索引性能都差不多因为mysql以页形式读到内存在内存判断是很快的
② 更新情况下唯一索引没有用到change buffer而普通索引有用到因为唯一索引就一条记录如果内存有直接就可以在内存操作就没必要用change buffer
③ chang buffer与merge结合使用有change buffer如果数据不存在就不用重新查磁盘直接写入到change buffer等一下次查询就合并磁盘和增量修改或者定时merge增量修改
④ 所以怎么选写入比较频繁的建议用普通索引如果是写完马上读会触发mergeio次数不会减少反而增加change buffer维护代价
2、merge 的执行流程是这样的
① 从磁盘读入数据页到内存老版本的数据页
② 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个依次应用得到新版数据页
③ 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
问题如果某次写入使用了 change buffer 机制之后主机异常重启是否会丢失 change buffer 和数据。
这个问题的答案是不会丢失虽然是只更新内存但是在事务提交的时候我们把 change buffer 的操作也记录到 redo log 里了所以崩溃恢复的时候change buffer 也能找回来。
二、mysql为什么有时候会选错索引
*1、为什么有时候mysql会选错索引*
扫描的行数过多情况下优化器会选择扫描全表而不用索引
*2、解决办法*
通过explain sql后发现选错索引可以采取以下方式
① 通过指定索引 force index
② 通过sql优化让其走预期的索引
③ 删除不必要的索引
备注这种情况生产上是比较少发生索引不必过多关注
三、怎么给字符串加索引
*1.为什么需要优化字符串上的索引*
如果字符串较长索引字段占用内存空间大B树高度较高这样查询IO次数较多耗时长。 个人认为未出现性能瓶颈不需要过度优化全字段索引也ok。
*2.优化方法及优缺点、适用场景*
方法一前缀索引 概念在建立索引的时候指定索引长度且该长度的字段区分度高
优点a. 相比全字段索引每页存储的索引更多查询索引IO次数少效率高。即既节省了内存空间又提高了查询效率。
缺点
a. 指定索引长度的区分度低的话扫描主键索引次数就会多效率低
b. 不会使用覆盖索引即使索引长度定义为全字段也会去主键索引查询 适用场景前缀索引区分度高
方法二倒序存储 概念字段保存的时候反序存储 优点同前缀索引
缺点
a.只适用于等值查询不适用于范围、模糊查询。
b.每次保存、查询时需调用reverse()函数
c.若后缀索引区分度低扫描行数会增多。 适用场景索引字段后缀区分度高前缀区分度低。
方法三添加hash字段作为索引 概念在表中添加一个hash字段并加索引用于存储索引字段的哈希值如使用crc32()哈希函数每次查询时先计算出字段的hash值再利用hash字段查询。可能存在hash冲突所以where需要加索引字段字段的等值条件。
优点哈希函数冲突概率低的话平均扫描行数接近1。
缺点只适用于等值查询不适用于范围、模糊查询。 适用场景只适用于等值查询不适用范围查询或模糊查询。
四、为什么我的mysql会抖一下
*1、首先理解一下什么是脏页什么是干净页*
① 脏页内存与磁盘的内容不一致的时候我们称这个内存也就是脏页
② 干净页内存写入磁盘后内容一致了该内存页就是干净页
深层理解每个表都是一个ibd文件, 每个文件都是分成n个16kb的pagepage是IO的基本单位, 也就是从硬盘到内存每次都载入一个page所以用到的page既在内存也在硬盘ibd文件里. 在内存page上写写改改后, 这个page没写回ibd文件, 就成了脏页
2、*那么什么时候会刷脏页呢*
① *redolog写满了*redo log是在同一块地方进行循环的写redo log记录的变动会被清除在清除时可能redo log中有记录变更的数据还未刷入磁盘中这时就得需要去判断这些变动的数据是否刷入磁盘没有则进行刷脏页
② *内存满了*内存不够用的时候就要淘汰一些数据页空出内存给别的数据页使用。如果淘汰的是“脏页”就要先将脏页写到磁盘
③ 系统空闲的时候
④ mysql正常关闭
说明第②点为什么要刷脏页反证法-如果内存满了不刷脏页到磁盘中下次请求磁盘中的干净页到内存时还是要额外判断redolog是否对该也有修改有修改的话还是要刷到磁盘中这样还不如在内存满了的时候直接将它刷到磁盘中
*3、InnoDB 刷脏页的控制策略*
*影响因素有*
\1. 脏页比例
\2. 脏页刷盘速度
\3. 刷新相邻页面策略 bufferpool脏页比例 或 redolog 都可能成为读写sql的瓶颈
*参数控制*
\1. 脏页比例默认75%一定不要让其接近75%脏页/总页
\2. 刷脏页速度 innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度
\3. innodb_flush_neighbors0不开启脏页相邻淘汰 对于机械硬盘顺序读写会有提升ssd无提升mysql8直接默认不开启
\4. 避免大量刷脏页脏页flush可能会产生内存抖动
五、为什么数据删减一半表文件大小不变
