MySQL
MySQL 相关的知识
一切随缘~~~
最难不过坚持。
展开
-
为什么实际开发中不推荐使用外键?
一般数据库都不会启用读锁,例如MYSQL事务级别设置为读提交,个人认为没有理由使用其它事务级别。数据库的瓶颈在IO,不使用外键代码里做数据完整性检查,磁盘IO省不了,网络IO占用增加。,无论是否使用外键,只要要做数据完整性检查,磁盘IO是省不了的。读写分离也是很成熟的方案。数据库水平扩展不易?64核cpu已发布,最大支持4T内存,主流数据库均支持外键约束。为什么实际开发中不推荐使用外键?只有一个场景不能使用外键,就是。,其它描述都不是真正原因。原创 2023-10-21 22:31:35 · 387 阅读 · 2 评论 -
插入大批数据,怎么优化?
每提交一次事务,redo log buffer,bin log 就要刷一次盘。每一个单独的insert语句,都会隐式开启事务,语句执行完毕自动提交事务。所以优化点,就在于控制日志刷盘次数,减少磁盘IO的次数。我们可以手动的提交事务,批量插入数据之后,再提交事务。让多个commit提交,合并成一个commit提交。其实是牺牲数据的安全性来换取性能的提升。插入大批数据,怎么优化?性能跟日志息息相关。原创 2023-05-28 11:16:17 · 130 阅读 · 1 评论 -
MySQL 锁的分类
那么有了「意向锁」,由于在对记录加独占锁前,先会加上表级别的意向独占锁,那么在加「独占表锁」时,直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁,这样就不用去遍历表里的记录。在线程B阻塞后,后续所有对该表的select语句,都会被阻塞,如果这时候恰好有大量的select请求过来,就会有大量的线程被阻塞住,这时数据库的线程很快就会爆满了。然后来了个一个线程B,开启了一个新的事务,要变更表结构,会申请 MDL 写锁,读写互斥,所以无法申请到 MDL 写锁,所以线程B就被阻塞了。原创 2023-05-28 11:15:39 · 949 阅读 · 0 评论 -
什么是 MVCC?
MVCC就是一个数据快照,不同的事务访问的是不同版本的数据快照,从而保证了事务之间的隔离性,当然这是在隔离级别为 RR 可重复读的时候才可以保证隔离性,如果在隔离级别为 RC 读已提交的情况下, 是不能保证的。还有一个是 roll_pointer 回滚指针,每次对某条聚簇索引记录进行改动时,都会把旧版本的记录写入到 undo 日志中,然后这个隐藏列是个指针,指向每一个旧版本记录,于是就可以通过它找到修改前的记录。MVCC就是指多版本并发控制,也可以理解为一致性非锁定读,用来提高并发场景下,读的性能的。原创 2023-05-27 10:31:28 · 166 阅读 · 0 评论 -
Read View 数据快照,在MVCC里是如何工作的?
● 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值,表示这个版本的记录是在创建 Read View 后才启动的事务生成的,所以该版本的记录对当前事务不可见。● 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值,表示这个版本的记录是在创建 Read View 前已经提交的事务生成的,所以该版本的记录对当前事务可见。● trx_id,当一个事务对某条聚簇索引记录进行改动时,就会把该事务的事务 id 记录在 trx_id 隐藏列里;原创 2023-05-27 10:28:36 · 658 阅读 · 0 评论 -
InnoDB 是如何实现事务四种隔离级别的?
● 对于「读提交」和「可重复读」隔离级别的事务来说,它们是通过 Read View 来实现的,它们的区别在于创建 Read View 的时机不同,大家可以把 Read View 理解成一个数据快照,就像相机拍照那样,定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View,而「可重复读」隔离级别是「启动事务时」生成一个 Read View,然后整个事务期间都在用这个 Read View。注意,执行「开始事务」命令,并不意味着启动了事务。原创 2023-05-27 10:27:32 · 181 阅读 · 0 评论 -
InnoDB 在可重复读 RR 隔离级别下,能解决幻读问题吗?
InnoDB 在可重复读 RR 隔离级别下,能解决幻读问题吗?MySQL 在「可重复读」隔离级别下,可以很大程度上避免幻读现象的发生(注意是很大程度避免,并不是彻底避免),所以 MySQL 并不会使用「串行化」隔离级别来避免幻读现象的发生,因为使用「串行化」隔离级别会影响性能。解决方案有两种:● 针对快照读(普通的select语句),是通过MVCC的方式解决了幻读问题。原创 2023-05-27 10:13:48 · 1139 阅读 · 0 评论 -
目前互联网大厂 MySQL 使用最多的隔离级别是什么?
可重复读多好啊,解决了脏读,不可重复读问题,又有next-key lock,可以很大程度上避免幻读问题。好的方面:很真实,真实的反映了数据的变化,变迁。RC,就是真实的反映了当前数据的最新状态。因为 RR 隔离级别和Binlog的Statement格式,才能很好的配合,才不会出现主从数据不一致的问题。RC,读已提交,多不好啊,仅仅解决了脏读问题,存在不可重复读,幻读的问题。首先要纠正一个观点:不可重复读,幻读,并不是什么严重的问题。不可重复读,幻读,只是两种现象,并不是什么错误。幂等校验,强制加锁。原创 2023-05-26 17:20:00 · 592 阅读 · 2 评论 -
事务的隔离级别有哪些?
● 串行读,会对记录加上读写锁,在多个事务对这条记录进行读写操作时,如果发生了读写冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行;● 可重复读,RR,指一个事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,MySQL InnoDB 引擎的默认隔离级别;● 在「读提交」隔离级别下,可能发生不可重复读和幻读现象,但是不可能发生脏读现象;● 在「可重复读」隔离级别下,可能发生幻读现象,但是不可能脏读和不可重复读现象;事务的隔离级别有哪些?原创 2023-05-26 17:18:05 · 70 阅读 · 0 评论 -
事务并发执行的时候,会出现什么问题?
如果是本事务导致的数据变化,那么不属于幻读问题。幻读问题指的是一个事务按照一定条件查询数据时,发现这些数据存在且符合条件,但在当前时刻某些行被其他事务插入(或删除)。也就是说,在此期间,一个事务按照一定的条件获取数据时,发现了超出预期的数据,这些非预期的数据就称为幻像。幻读问题:在一个事务内,多次查询符合某个查询条件的记录数量,发现前后两次得到的【记录数量】不一样。不可重复读问题:在一个事务内,多次读取同一个数据,发现前后两次读取的数据不一样。脏读问题:一个事务,读取到另一个未提交事务修改过的数据。原创 2023-05-26 17:16:41 · 126 阅读 · 0 评论 -
事务的四大特性
A,就是指原子性,原子性就是一个事务中的操作,要么就全部完成,要么就全部不完成。不会结束在中间某个环节,而且事务在执行过程中,如果发生错误,就会回滚到事务开始之前的状态,就好像这个事务没有执行过一样。并不是所有的引擎都支持事务的。D,就指是持久性,事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。事务是由MySQL的存储引擎层面实现的,我们常见的InnoDB是支持事务的。InnoDB 引擎通过什么技术来保证事务的这四个特性的呢?● 原子性是通过 undo log(回滚日志) 来保证的;原创 2023-05-26 17:16:01 · 49 阅读 · 0 评论 -
redo log 故障恢复
● redo log 的 prepare 状态写入了,bin log 也记录了,redo log commit 状态写入的时候故障了, 故障恢复的时候,会把redo log commit 状态写入,恢复这条数据。● redo log 的 prepare 状态写入了,bin log 也记录了,redo log commit 状态也写入,这时候发生故障,故障恢复的时候,会恢复这条数据。● redo log 的 prepare 状态记录了,写入 bin log 的时候故障了,故障恢复的时候,也不会恢复。原创 2023-05-26 17:15:26 · 229 阅读 · 0 评论 -
事务的两阶段提交
● 会先记录 redo log,此时redo log 处于 prepare 阶段,并没有提交。作用:保证redo log 和 bin log两份日志之间的逻辑一致。● 最后将 redo log 状态改为 commit。● 然后记录 bin log。原创 2023-05-26 17:14:40 · 53 阅读 · 0 评论 -
Write Ahead Log
优点:MySQL 的写操作从磁盘的「WAL 技术指的是,原创 2023-05-25 10:32:38 · 93 阅读 · 0 评论 -
undo log
redo log 会每秒刷盘,提交事务时也会刷盘,数据页和 undo 页都是靠redo log机制保证持久化的。● 在删除一条记录时,要把这条记录中的内容都记下来,这样之后回滚时再把由这些内容组成的记录插入到表中就好了;● 在插入一条记录时,要把这条记录的主键值记下来,这样之后回滚时只需要把这个主键值对应的记录删掉就好了;● 在更新一条记录时,要把被更新的列的旧值记下来,这样之后回滚时再把这些列更新为旧值就好了。undo log 和 数据页的刷盘策略是一样的,都需要通过 redo log 保证持久化。原创 2023-05-25 10:30:24 · 809 阅读 · 0 评论 -
redo log
我们知道 redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的,那么如果随着系统运行,Buffer Pool 的脏页刷新到了磁盘中,那么 redo log 对应的记录也就没用了,这时候我们擦除这些旧记录,以腾出空间记录新的更新操作。当发生故障的时候,就算脏页没有来得及刷到磁盘中,但是 redo log 已经持久化到磁盘了,接着MySQL重启后,通过磁盘中的老的数据 + redo log,就可以将数据恢复到最新的状态。因为在MySQL启动的时候,就向操作系统申请了一段连续的内存空间。原创 2023-05-25 10:27:38 · 1030 阅读 · 0 评论 -
bin log
事务执行过程中,先把日志写到 binlog cache(Server 层的 cache),事务提交的时候,再把 binlog cache 写到 binlog 文件中。:MySQL 5.7 版本之后增加的一种复制方式,介于两者之间,事务线程不用等待所有的从库复制成功响应,只要一部分复制成功响应回来就行,比如一主二从的集群,这种半同步复制的方式,兼顾了异步复制和同步复制的优点,即使出现主库宕机,至少还有一个从库有最新的数据,不存在数据丢失的风险。主从复制,就是通过 bin log 日志文件,来进行数据同步的。原创 2023-05-25 10:17:10 · 762 阅读 · 0 评论 -
脏页什么时候会刷入磁盘?
MySQL的时候,会将所有的脏页刷新回磁盘中。的时候,后台线程会定期将脏页刷新回磁盘中。,需要根据淘汰算法,淘汰一些数据页,,会主动触发脏页刷新到磁盘中。,需要先将脏页刷新到磁盘中。脏页什么时候会刷入磁盘?原创 2023-05-25 10:12:28 · 252 阅读 · 0 评论 -
Buffer Pool
如果使用简单的 LRU 链表,进行全表扫描的时候,就可能将 Buffer Pool 中的大部分页给替换出去,导致大量热点数据被淘汰了,等下次访问这些热点数据的时候,由于缓存未命中,就会导致大量的磁盘IO,导致MySQL性能会急剧下降。如果这些预读页如果一直不会被访问到,就会出现一个很奇怪的问题,不会被访问的预读页却占用了 LRU 链表前排的位置,而末尾淘汰的页,可能是频繁访问的页,这样就大大降低了缓存命中率。所以,MySQL 在加载数据页时,会提前把它相邻的数据页一并加载进来,目的是为了减少磁盘 IO。原创 2023-05-25 10:12:32 · 918 阅读 · 0 评论 -
MySQL 调优思路分析
还有就是降低磁盘写入次数,可以设置 innodb_flush_log_at_trx_commit控制redo log刷新到磁盘的策略,默认是1,innodb_max_dirty_pages_pct,设置脏页占比比例触发刷新脏页到磁盘中,推荐值25%~50%。要想优化SQL,首先我们可以通过慢查询日志定位到查询时间比较长的sql语句,然后使用explain执行计划对sql语句进行分析,查看sql是否使用索引,以及索引级别,和预估的扫描行数等,再根据情况去具体优化sql。(通用日志,慢查询日志,错误日志)原创 2023-05-24 14:40:05 · 789 阅读 · 0 评论 -
什么是 limit 深分页问题?怎么解决?
当深分页的时候,优化器都不会选择走key这个辅助索引了,而是选择type = All,全表扫描,因为优化器这时候觉得,你走辅助索引,还要大量的回表,可能效率还不如直接全表扫描 + 排序呢。// 走key的索引,快速定位,然后再往后取10条记录就行了,type = range,extra = using where。先通过子查询拿到第99000页的所有记录的id值,然后和原表进行一次join表连接,比对id值,减少大量的回表操作。我们想到给这个要排序的字段加上索引,但是加上索引之后,查询的速度依旧很慢。原创 2023-05-24 14:39:48 · 1081 阅读 · 0 评论 -
MySQL limit 是怎么工作的?原理是什么?
如果 B type = index,意味着要进行 5001 次回表操作,优化器觉得执行这么多次回表,代价太大了,还不如直接进行全表扫描 + filesort。两个sql语句从发送给服务端到返回给客户端,耗时时长的区别就在网络开销上面(因为一个是返回的1条记录,一个是返回的1w条记录,数据量不同造成的)。在带有limit的查询语句中,你先把limit删掉,再查看数据,看看数据是不是你想要过滤的。而 B语句 type = all,没有走二级索引,走的是主键索引,进行全表扫描。假设这张表有 1 w 条数据。原创 2023-05-23 17:50:44 · 1109 阅读 · 0 评论 -
left join 和 right join 什么区别?
MySQL的左连接和右连接都是一种关联查询,它们的区别在于连接的方式和连接的表。left join 和 right join 什么区别?原创 2023-05-23 17:46:00 · 43 阅读 · 0 评论 -
MySQL join 是如何工作的?join 优化?
● 如果是大表 join 小表的话,时间复杂度是 n * logm。● 如果是小表 join 大表的话,时间复杂度是 m * logn。MySQL join 是如何工作的?● 显然小表 join 大表的时候,时间复杂度较小。● 假设小表有 m 条记录,大表有 n 条记录。● join 的 字段,可以加索引,来优化。● 建议 join 拆分成多次单表的操作。驱动表 join 被驱动表。原创 2023-05-23 17:45:28 · 42 阅读 · 0 评论 -
排序如何优化?如何避免排序?Using filesort
比如说有 1000 行,sort_buffer 一次性放不下,只能一次排序 100 行,然后将排序后的结果放进一个临时文件里面,file1,然后再对 101 - 200 行进行排序,然后将排序结果再放进一个临时文件里面,file2…,一次性读进sort_buffer,进行排序,排序完成之后,就可以直接返回了,这种情况就是 using tempory,使用。所以,如果排序的字段上面刚好有索引,并且可以走这个索引的话,那么就不需要再排序了,避免排序了。当然,如果符合覆盖索引,就不用回表了。原创 2023-05-23 17:44:34 · 749 阅读 · 0 评论 -
什么是覆盖索引?Using index
=我们在使用select的时候,尽量做到用哪个字段,再去查那个字段,不要一上来就select * ==查询的时候,会走 a 索引,然后a 索引上面正好就有 b字段,就。有联合索引 index(a, b),直接就找到了所需的所有字段了。的情况下,可能触发。原创 2023-05-22 16:01:16 · 62 阅读 · 0 评论 -
什么是索引下推?Index Condition Pushdown,简称 ICP
判断(根据二级联合索引中有的字段进行判断)。如果符合条件的话,再回表;不符合条件,不回表。而不是,回表之后,再判断字段符不符合条件。Index Condition Pushdown,简称ICP。MySQL 的各种优化手段,目的最终还是为了。mysql 5.6 之后出现的特性。● 说白了,目的就是为了。● 在二级索引的时候,key(a, b)是。原创 2023-05-22 15:59:47 · 53 阅读 · 0 评论 -
MRR 优化[Multi-Range Read optimization] (MySQL5.6)
在通过辅助索引检索大量数据时,性能提升明显,磁头无需来回寻道,page只需读取一次,且较好利用了innodb线性预读功能。BKA:SQL通过辅助索引要访问表数据时候,将大量的随机访问放入缓存,交给MRR接口合并为顺序访问。MRR:在BKA算法应用之后,通过MRR接口合并随机访问为顺序访问,再去检索表数据。没有MRR,在辅助索引上,找到一个id,就要到聚簇索引回表一次。有了MRR,在辅助索引上,原创 2023-05-22 16:00:07 · 89 阅读 · 0 评论 -
什么是索引合并(Index Merge)
a, b 字段建立了索引,会分别从 a 索引树,b索引树中查到 id,然后对 id 取交集或者取并集去重,然后拿着 id 再去主键索引,回表查询,,多表查询使用索引合并效率太低了。直接去重的时间复杂度O(n^2)时间复杂度O(nlogn)原创 2023-05-22 15:53:54 · 128 阅读 · 0 评论 -
索引的底层数据结构,讲讲 B+ 树,B树
B + 树每次查询都会查到叶子节点,而B树的查询具有不稳定性,可能在非叶子节点就查到了。● 最主要的:相同的数据量,B + 树的层数肯定是低于 B 树的。B树:所有节点(非叶子节点 + 叶子节点),都存储真正的行数据,:B + 树的叶子节点是通过双向链表连接的,范围查询很方便。使用B + 树有什么好处呢?为啥不使用 B 树呢?原创 2023-05-21 09:52:05 · 340 阅读 · 0 评论 -
联合索引是如何定位一行记录的?
select from table where b = x and a = x,这样也能使用索引,优化器会调整字段的顺序,进行优化。select from table where a = x and b = x,这样就能使用索引。select from table where b = x and c = x,这样就不能使用索引。查找的时候,先根据 a 字段,进行查找,然后再根据 b 字段进行查找。联合索引,是指一个索引中包含多个字段,使用时要保证符合。比如 index(a, b,c),原创 2023-05-21 09:49:17 · 34 阅读 · 0 评论 -
普通索引和唯一索引的区别?
唯一索引字段,更新插入的时候,要先查询一下,看看能不能查询到,是不是已经存在这个字段值了。,需要看看自己要更新的值,是不是已经存在了,如果已经存在了,则更新失败。如果已经存在了,则直接报错,插入失败;如果不存在,则可以插入。,要先去唯一索引B+树上面查询一下,看看是不是已经存在了。唯一索引,就是要求索引列的值必须。普通索引和唯一索引,原创 2023-05-21 09:46:16 · 108 阅读 · 0 评论 -
索引的分类
● 二级索引,非叶子节点,叶子节点都不存储真正的行数据,只存储索引 + 指针,所以需要。● 聚簇索引,只有叶子节点才存储真正的行数据,非叶子节点只存储主键 + 指针。按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)。按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类:单列索引、联合索引。主键索引,也叫聚簇索引。原创 2023-05-21 09:44:35 · 175 阅读 · 0 评论 -
MySQL 的 or 走不走索引?
● 如果 a 字段有索引,b 字段也有索引,那么会走索引,此时就是。有可能走,也可能不走。原创 2023-05-20 10:28:12 · 2510 阅读 · 2 评论 -
MySQL is null 走不走索引?
因为可为 NULL 的列会使索引、索引统计和值比较都更复杂,比如进行索引统计时,count 会省略值为NULL 的行。比如,select * from table where a = xxx or a is null;● 第二个原因:NULL 值是一个没意义的值,但是它会。网上很多的说法,都是说不能走索引。通过explain 看这条sql的执行计划,为了更好的利用索引,索引列字段要设置为。● 第一原因:索引列存在 NULL 就会。,这条sql语句会查询两次,其实也是又可能走索引的。原创 2023-05-20 10:25:39 · 2706 阅读 · 4 评论 -
什么样的列不适合建立索引?
建立索引的目的,就是为了提高查询速度。你又不经常查询这个字段,那你还建立啥索引啊。查询时根据索引并不能很好地筛选出数据,还会增加索引的维护和查询代价。,只有男或者女,顶多再有一个人妖。原创 2023-05-20 09:41:41 · 58 阅读 · 0 评论 -
索引什么场景下会失效?
● 对索引列进行隐式类型转换,比如索引字段是varchar类型的,你查询的时候输入的字段参数是整数类型的话,这里就会对字段进行类型转换,导致索引失效。,直接全表扫描(Type = All),把主键索引的叶子节点全部走一遍的效率(全表扫描),比走二级索引,然后再回表的效率高。● like “%xx” "%xx%”,这种左模糊查询或者左右模糊查询,就可能导致索引失效,当然。● 对索引列进行计算,或者使用函数,那么也会导致索引失效。,如果两个字段都是索引字段,那么会走索引,这种情况就是。原创 2023-05-20 09:39:54 · 790 阅读 · 0 评论 -
什么是索引?谈谈对索引的理解?
需要占用磁盘空间,当对表进行。原创 2023-05-20 09:37:29 · 38 阅读 · 0 评论 -
MySQL 执行计划(Explain)
主要就看一下,type 是什么,走没走索引,走了哪个索引,extra 看看,有没有索引覆盖啊,索引合并啊,索引下推啊…的时候, 这时不得不选择相应的排序算法进行,甚至可能会通过文件排序,效率是很低的,所以要避免这种问题的出现。type这里,网上的各种错误的博客,对 type = all,type = index有很多错误的观点。● key 字段表示实际用的索引,如果这一项为 NULL,说明没有使用索引;:所需数据只需在索引即可全部获得,不须要再到表中取数据,也就是使用了。,避免了回表操作,效率不错。原创 2023-05-20 09:32:40 · 611 阅读 · 0 评论 -
InnoDB 一条更新语句的执行过程
的关键),更新数据内存页缓冲区,然后进行提交事务,此时redo log会进行落盘,redo log落盘成功则事务提交成功,否则事务回滚,释放插入意向锁,执行结束。首先由server层的连接器建立连接,通过解析器解析sql语句,通过优化器进行sql优化,在使用执行器把sql语句交给引擎层去执行,在InnoDB引擎中,的redo log缓冲区,然后记录undo log缓冲区,再去记录redo log缓冲区,(这里undo log是保证。连接器,解析器,优化器,执行器,这里就不细说了。原创 2023-05-19 23:02:04 · 1000 阅读 · 0 评论