MySql超神之路
文章平均质量分 96
本专栏参考Mysql45讲,从根上理解Mysql,Mysql运维内参等等书籍和精品资源整理而来。
Binary Oracle
一名热爱开源和技术的Coder , 开源框架spring committer , golang开源网络库netpoll committer; (脱产备战25考研中,停更一年)
展开
-
Mysql 中令人稀里糊涂的Explain
Mysql 中令人稀里糊涂的Explain原创 2023-09-15 14:43:08 · 230 阅读 · 0 评论 -
为什么 MySQL 使用 B+ 树
首先需要澄清的一点是,MySQL 跟 B+ 树没有直接的关系,真正与 B+ 树有关系的是 MySQL 的默认存储引擎 InnoDB,MySQL 中存储引擎的主要作用是负责数据的存储和提取,除了 InnoDB 之外,MySQL 中也支持 MyISAM 作为表的底层存储引擎。我们在使用 SQL 语句创建表时就可以为当前表指定使用的存储引擎,你能在 MySQL 的文档MyISAMCSVMEMORY。转载 2023-02-19 19:22:11 · 437 阅读 · 0 评论 -
Mysql番外篇--最左原则-03
为了故事的顺利发展,我们需要先建立一个表:我们为这个single_table表建立了1个聚簇索引和4个二级索引,分别是:然后我们需要为这个表插入10000行记录,除id列外其余的列都插入随机值就好了,具体的插入语句我就不写了,自己写个程序插入吧(id列是自增主键列,不需要我们手动插入)。我们画一下single_table表的聚簇索引的示意图:如图所示,我们把聚簇索引对应的复杂的B+树结构搞了一个极度精简版。可以看到,我们忽略掉了页的结构,直接把所有的叶子节点中的记录都放在一起展示,为了方便,我们之后就把聚转载 2022-11-21 22:23:44 · 340 阅读 · 0 评论 -
Mysql番外篇--查询成本和扫描区间-02
至此,我们分别分析了拥有IS NULL、、!=这三个条件的查询是在什么情况下使用二级索引来执行的,核心结论就是:成本决定执行计划,跟使用什么查询条件并没有什么关系。优化器会首先针对可能使用到的二级索引划分几个扫描区间,然后分别调查这些区间内有多少条记录,在这些扫描区间内的二级索引记录的总和占总共的记录数量的比例达到某个值时,优化器将放弃使用二级索引执行查询,转而采用全表扫描。小贴士:其实扫描区间划分的太多也会影响优化器的决策,比方说IN条件中有太多个参数,将会降低优化器决定使用二级索引执行查询的几率。转载 2022-11-19 11:09:18 · 275 阅读 · 0 评论 -
Mysql番外篇--server层如何与存储引擎层交互-01
很多同学在这里有一个疑惑:到底是一次性把所有符合条件的二级索引都取出来之后再统一进行回表操作,还是每从二级索引中取出一条符合条件的记录就进行回表一次?可以联想一下我们生活中的老板,老板在布置任务的时候会告诉你:小王啊,今天把这些砖从A地搬到B地啊,然后就没然后了。,对于等值查询的这种情况,设计MySQL的大叔在InnoDB存储引擎层有特殊的处理方案,是不作为ICP条件进行处理的。的提示,意味着server层在接收到存储引擎层返回的记录之后,接着就要判断其余的WHERE条件是否成立(就是再判断一下。转载 2022-11-18 22:20:44 · 208 阅读 · 0 评论 -
Mysql实战篇之如何正确地显示随机消息--08
原因是,这条 SQL 语句是 limit 1000,如果使用优先队列算法的话,需要维护的堆的大小就是 1000 行的 (name,rowid),超过了我设置的 sort_buffer_size 大小,所以只能使用归并排序算法。因为将 max_length_for_sort_data 设置成 16,小于 word 字段的长度定义,所以我们看到 sort_mode 里面显示的是 rowid 排序,这个是符合预期的,参与排序的是随机值 R 字段和 rowid 字段组成的行。因此,这类方法的应用还是比较广泛的。转载 2022-11-18 14:32:37 · 221 阅读 · 0 评论 -
Mysql实战篇之order by是如何工作的--07
也就是说,在我们这个例子里,只需要扫描 1000 次。需要说明的是,最后的“结果集”是一个逻辑概念,实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的。在这个索引里面,我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录,并且额外确保了,接下来按顺序取“下一条记录”的遍历过程中,只要 city 的值是杭州,name 的值就一定是有序的。转载 2022-11-15 09:54:39 · 251 阅读 · 0 评论 -
Mysql实战篇之count(*)这么慢,我该怎么办?--06
试想如果刚刚在数据表中插入了一行,Redis 中保存的值也加了 1,然后 Redis 异常重启了,重启后你要从存储 redis 数据的地方把这个值读回来,而刚刚加 1 的这个计数操作却丢失了。当然了,这还是有解的。count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加。所以结论是:按照效率排序的话,count(字段)转载 2022-11-06 21:17:29 · 429 阅读 · 0 评论 -
Mysql实战篇之可以通过删除记录来缩小表空间大小吗?--05
经常会有同学来问我,我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?那么今天,我就和你聊聊数据库表的空间回收,看看如何解决这个问题。这里,我们还是针对 MySQL 中应用最广泛的 InnoDB 引擎展开讨论。一个 InnoDB 表包含两部分,即:表结构定义和数据。在 MySQL 8.0 版本以前,表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。接下转载 2022-11-05 21:22:59 · 738 阅读 · 0 评论 -
Mysql实战篇之Mysql抖动现象--04
上述的计算流程比较抽象,不容易理解,所以我画了一个简单的流程图。图中的 F1、F2 就是上面我们通过脏页比例和 redo log 写入速度算出来的两个值。现在你知道了,InnoDB 会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。所以,无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句,都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。转载 2022-11-04 20:31:22 · 386 阅读 · 0 评论 -
Mysql实战篇之怎么给字符串加索引--03
因为维护的只是一个学校的,因此前面 6 位(其中,前三位是所在城市编号、第四到第六位是学校编号)其实是固定的,邮箱后缀都是 @gamil.com,因此可以只存入学年份加顺序编号,它们的长度是 9 位。但是,对于这个查询语句来说,如果你定义的 index2 不是 email(6) 而是 email(7),也就是说取 email 字段的前 7 个字节来构建索引的话,即满足前缀’zhangss’的记录只有一个,也能够直接查到 ID2,只扫描一行就结束了。这种方法,既可以占用更小的空间,也能达到相同的查询效率。转载 2022-11-04 11:23:38 · 419 阅读 · 0 评论 -
Mysql实战篇之Mysql为什么会选错索引?--02
当然,这种修改并不是通用的优化手段,只是刚好在这个语句里面有 limit 1,因此如果有满足条件的记录, order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行,逻辑上一致,才可以这么做。显然,一个索引上不同的值越多,这个索引的区分度就越好。之前优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b 的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。转载 2022-11-04 09:50:38 · 129 阅读 · 0 评论 -
Mysql原理篇之锁--14
如果我们的一些业务场景不允许读取记录的旧版本,而是每次都必须去读取记录的最新版本,比方在银行存款的事务中,你需要先把账户的余额读出来,然后将其加上本次存款的数额,最后再写到数据库中。总结一下:IS、IX锁是表级锁,它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁,以避免用遍历的方式来查看表中有没有上锁的记录,也就是说其实IS锁和IX锁是兼容的,IX锁和IX锁是兼容的。而写操作肯定针对的是最新版本的记录,读记录的历史版本和改动记录的最新版本本身并不冲突,也就是采用。转载 2022-11-03 15:53:11 · 139 阅读 · 0 评论 -
Mysql原理篇之事务隔离级别和MVCC--13
虽然真正的insert undo日志占用的存储空间被释放了,但是roll_pointer的值并不会被清除,roll_pointer属性占用7个字节,第一个比特位就标记着它指向的undo日志的类型,如果该比特位的值为1时,就代表着它指向的undo日志类型为insert undo。隔离级别的事务来说,都必须保证读到已经提交了的事务修改过的记录,也就是说假如另一个事务已经修改了记录但是尚未提交,是不能直接读取最新版本的记录的,核心问题就是:需要判断一下版本链中的哪个版本是当前事务可见的。有一帮人(并不是设计。转载 2022-11-02 23:12:29 · 266 阅读 · 0 评论 -
Mysql原理篇之undo日志--下--12
在写入。转载 2022-11-02 22:01:51 · 309 阅读 · 0 评论 -
Mysql实战篇之普通索引和唯一索引--01
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。因为 merge 的时候是真正进行数据更新的时刻,而 change buffer 的主要目的就是将记录的变更动作缓存下来,所以在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。所以,我在图中就没画出这两部分。转载 2022-10-31 21:41:23 · 237 阅读 · 0 评论 -
Mysql原理篇之undo日志--上--11
我们说过事务需要保证原子性,也就是事务中的操作要么全部完成,要么什么也不做。但是偏偏有时候事务执行到一半会出现一些情况,比如:这两种情况都会导致事务执行到一半就结束,但是事务执行过程中可能已经修改了很多东西,为了保证事务的原子性,我们需要把东西改回原先的样子,这个过程就称之为回滚(英文名:rollback),这样就可以造成一个假象:这个事务看起来什么都没做,所以符合原子性要求。从上边的描述中我们已经能隐约感觉到,每当我们要对一条记录做改动时(这里的改动可以指INSERT、DELETE、UPDATE),都需要转载 2022-10-28 12:04:55 · 497 阅读 · 0 评论 -
Mysql基础篇之事务真的是隔离的吗?--08
和你讲事务隔离级别的时候提到过,如果是可重复读隔离级别,事务 T 启动的时候会创建一个视图 read-view,之后事务 T 执行期间,即使有其他事务修改了数据,事务 T 看到的仍然跟在启动时看到的一样。中,和你分享行锁的时候又提到,一个事务要更新一行,如果刚好有另外一个事务拥有这一行的行锁,它又不能这么超然了,会被锁住,进入等待状态。因为之后的更新,生成的版本一定属于上面的 2 或者 3(a) 的情况,而对它来说,这些新的数据版本是不存在的,所以这个事务的快照,就是“静态”的了。转载 2022-10-28 11:04:59 · 135 阅读 · 0 评论 -
Mysql原理篇之redo日志--下--10
这时应该想到:redo日志只是为了系统崩溃后恢复脏页用的,如果对应的脏页已经刷新到了磁盘,也就是说即使现在系统崩溃,那么在重启后也用不着使用redo日志恢复该页面了,所以该redo日志也就没有存在的必要了,那么它占用的磁盘空间就可以被后续的redo日志所重用。日志文件组中包含的所有文件大小总和不得超过512GB,一个block大小是512字节,也就是说redo日志文件组中包含的block块最多为1GB个,所以有1GB个不重复的编号值也就够用了。的属性,该属性值记录了当前block里使用了多少字节的空间。转载 2022-10-27 20:43:23 · 261 阅读 · 0 评论 -
Mysql原理篇之redo日志--上--09
本篇文章为redo日志的原理篇,本文更多注重介绍redo日志的实现原理,可能对在Innodb存储引擎中一些作用和注意事项没有深入展开讲解,因此强烈建议大家先阅读此篇文章,否则阅读本文是可能会出现理解障碍: redo-log和bin-log的联系与区别我们知道InnoDB存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。我们前边唠叨Buffer Pool的时候说过,在真正访问页面之前,需要把在磁盘上的页缓存到内存中的Buffer Po转载 2022-10-27 19:12:42 · 331 阅读 · 0 评论 -
Mysql原理篇之事务--08
对于已经提交的事务来说,该事务对数据库所做的修改将永久生效,对于处于中止状态的事务,该事务对数据库所做的所有修改都会被回滚到没执行该事务之前的状态。MySQL中并不是所有存储引擎都支持事务的功能,目前只有InnoDB和NDB存储引擎支持(NDB存储引擎不是我们的重点),如果某个事务中包含了修改使用不支持事务的存储引擎的表,那么对该使用不支持事务的存储引擎的表所做的修改将无法进行回滚。当事务中的最后一个操作执行完成,但由于操作都在内存中执行,所造成的影响并没有刷新到磁盘时,我们就说该事务处在。转载 2022-10-27 11:16:48 · 131 阅读 · 0 评论 -
Mysql原理篇之Innodb如何调整磁盘与CPU之间的矛盾--07
磁盘太慢,用内存作为缓存很有必要。本质上是InnoDB向操作系统申请的一段连续的内存空间,可以通过来调整它的大小。向操作系统申请的连续内存由控制块和缓存页组成,每个控制块和缓存页都是一一对应的,在填充足够多的控制块和缓存页的组合后,剩余的空间可能产生不够填充一组控制块和缓存页,这部分空间不能被使用,也被称为碎片。InnoDB使用了许多链表来管理。free链表中每一个节点都代表一个空闲的缓存页,在将磁盘中的页加载到时,会从free链表中寻找空闲的缓存页。为了快速定位某个页是否被加载到,使用。转载 2022-10-26 20:18:38 · 144 阅读 · 0 评论 -
Mysql原理篇之系统表空间---06
但是每当我们向一个表中插入一条记录的时候,MySQL先要校验一下插入语句对应的表存不存在,插入的列和表中的列是否符合,如果语法没有问题的话,还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面,然后把记录插入对应索引的。了解完了独立表空间的基本结构,系统表空间的结构也就好理解多了,系统表空间的结构和独立表空间基本类似,只不过由于整个MySQL进程只有一个系统表空间,在系统表空间中会额外记录一些有关整个系统信息的页面,所以会比独立表空间多出一些记录这些信息的页面。转载 2022-10-25 22:10:05 · 270 阅读 · 0 评论 -
Mysql原理篇之表空间---05
通过前边儿的内容大家知道,表空间是一个抽象的概念,对于系统表空间来说,对应着文件系统中一个或多个实际文件;对于每个独立表空间来说,对应着文件系统中一个名为表名.ibd的实际文件。在我们目前的认知中: 一个表的索引和数据信息由一颗聚簇索引树提供,这颗聚簇索引树每个节点都对应一个数据页,数据越多,树越高,节点越多,当前索引树占据数据页越多,而表空间就是负责管理当前表占用的一堆数据页的。今天我们就来聊聊表空间是如何管理这一堆数据页的。转载 2022-10-25 21:32:11 · 2339 阅读 · 0 评论 -
Mysql基础篇之行锁--07
在上一篇文章中,我跟你介绍了 MySQL 的全局锁和表级锁,今天我们就来讲讲 MySQL 的行锁。MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。所以 MyISAM 不适合高并发的场景。它更适合读多写少的场景。转载 2022-10-24 14:42:06 · 141 阅读 · 0 评论 -
Mysql基础篇之全局锁和表锁--06
今天我要跟你聊聊 MySQL 的锁。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。今天这篇文章,我会和你分享全局锁和表级锁。而关于行锁的内容,我会留着在下一篇文章中再和你详细介绍。这里需要说明的是,锁的设计比较复杂,这两篇文章不会涉及锁的具体实现细节,主要介绍的是碰到锁时的现象和其背后的原理。转载 2022-10-23 22:25:41 · 214 阅读 · 0 评论 -
Mysql基础篇之索引下--05
在上一篇文章中,我和你介绍了 InnoDB 索引的数据结构模型,今天我们再继续聊聊跟 MySQL 索引有关的概念在开始这篇文章之前,我们先来看一下这个问题:在下面这个表 T 中,如果我执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?下面是这个表的初始化语句。在 k 索引树上找到 k=3 的记录,取得 ID = 300;(遍历二级索引)再到 ID 索引树查到 ID=300 对应的 R3;(回表)转载 2022-10-23 20:49:58 · 173 阅读 · 0 评论 -
Mysql原理篇之数据目录---05
我们前边提到了MySQL的几个系统数据库,这几个数据库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息,我们现在稍微了解一下。mysql这个数据库贼核心,它存储了MySQL的用户账户和权限信息,一些存储过程、事件的定义信息,一些运行过程中产生的日志信息,一些帮助信息以及时区信息等。这个数据库保存着MySQL服务器维护的所有其他数据库的信息,比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引吧啦吧啦。这些信息并不是真实的用户数据,而是一些描述性信息,有时候也称之为元数据。转载 2022-10-23 11:12:12 · 135 阅读 · 0 评论 -
Mysql原理篇之索引不懂不要瞎用---04
上边只是我们在创建和使用B+树索引的过程中需要注意的一些点,后边我们还会陆续介绍更多的优化方法和注意事项,敬请期待。B+树索引在空间和时间上都有代价,所以没事儿别瞎建索引。B+树索引适用于下边这些情况:全值匹配匹配左边的列匹配范围值精确匹配某一列并范围匹配另外一列用于排序用于分组在使用索引时需要注意下边这些事项:只为用于搜索、排序或分组的列创建索引为列的基数大的列创建索引索引列的类型尽量小可以只对字符串值的前缀建立索引。转载 2022-10-23 10:21:06 · 237 阅读 · 0 评论 -
Mysql基础篇之索引上--04
提问:转载 2022-10-22 22:09:59 · 181 阅读 · 0 评论 -
Mysql原理篇之索引是如何一步步实现的---下--03
至此,我们介绍的都是InnoDB存储引擎中的索引方案,为了内容的完整性,以及各位可能在面试的时候遇到这类的问题,我们有必要再简单介绍一下MyISAM存储引擎中的索引方案。我们知道InnoDB中索引即数据,也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了,而MyISAM的索引方案虽然也使用树形结构,但是却将索引和数据分开存储:将表中的记录按照记录的插入顺序单独存储在一个文件中,称之为数据文件。这个文件并不划分为若干个数据页,有多少记录就往这个文件中塞多少记录就成了。转载 2022-10-22 21:31:47 · 202 阅读 · 0 评论 -
Mysql原理篇之索引是如何一步步实现的---上--02
Mysql索引的实现是在存储引擎层完成的,因此本文所讲内容是以Innodb存储引擎为基础展开的,核心是讲清楚Innodb的数据存储结构。数据库是用来存储数据的,那么如何组织存储这些数据就是决定一个数据库好坏的重中之重了,对于关系型数据库而言,我们需要存储的数据往小了看是一条条的记录,往大了看就是一张表,一整个数据库。而一张表又是由成千上万的记录组织起来,因此,我们需要先解决一条记录该如何存储,该以什么格式进行存储,多条记录该如何编排管理…因此,第一步我们就从一条记录开始说起。原创 2022-10-22 20:29:03 · 788 阅读 · 0 评论 -
Mysql基础篇之事务隔离---03
当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。转账过程具体到程序里会有一系列的操作,比如查询余额、做加减法、更新余额等,这些操作必须保证是一体的,不然等程序查完之后,还没做减法之前,你这 100 块钱,完全可以借着这个时间差再查一次,然后再给另外一个朋友转账,如果银行这么整,不就乱了么?今天的文章里,我将会以 InnoDB 为例,剖析 MySQL 在事务支持方面的特定实现,并基于原理给出相应的实践建议,希望这些案例能加深你对 MySQL 事务原理的理解。转载 2022-10-21 19:56:44 · 280 阅读 · 0 评论 -
Mysql原理篇之MVCC原理--01
MVCC(Mutil Version Concurrency Control)多版本并发控制,是一种并发控制的方法(而非具体实现),一般在数据库管理系统中,实现对数据库的并发访问。上面的解释比较抽象,下面来一点一点分析。转载 2022-10-21 19:51:03 · 390 阅读 · 0 评论 -
Mysql基础篇之一条Sql更新语句的前世今生---02
前面我们系统了解了一个查询语句的执行流程,并介绍了执行过程中涉及的处理模块。相信你还记得,一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。那么,一条更新语句的执行流程又是怎样的呢?之前你可能经常听 DBA 同事说,MySQL 可以恢复到半个月内任意一秒的状态,惊叹的同时,你是不是心中也会不免会好奇,这是怎样做到的呢?前面我有跟你介绍过 SQL 语句基本的执行链路,这里我再把那张图拿过来,你也可以先简单看看这个图回顾下。转载 2022-10-21 15:37:44 · 260 阅读 · 0 评论 -
Mysql基础篇之一条Sql查询语句的前世今生---01
查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。转载 2022-10-21 11:18:56 · 317 阅读 · 0 评论