MySQL
文章平均质量分 91
mysql 相关学习笔记
Jiangw557
前行者~
展开
-
【实战篇】为什么我只查一行的语句,也执行这么慢?
一般情况下,如果说查询性能优化,首先会想到一些复杂的语句,想到查询需要返回大量的数据。但有些情况下,“查一行”,也会执行得特别慢。今天,我们来聊聊这个有趣的话题,看看什么情况下,会出现这个现象。如果 MySQL 数据库本身就有很大的压力,导致数据库服务器 CPU 占用率很高或 ioutil(IO 利用率)很高,这种情况下所有语句的执行都有可能变慢,不属于我们今天的讨论范围。原创 2024-09-13 09:55:28 · 683 阅读 · 0 评论 -
【实战篇】为什么这些SQL语句逻辑相同,性能却差异巨大?
交易记录表 tradelog 包含交易流水号(tradeid)、交易员 id(operator)、交易时间(t_modified)等字段。假设,现在已经记录了从 2016 年初到 2018 年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中 7 月份的交易记录总数。由于 t_modified 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。原创 2024-09-13 09:55:02 · 973 阅读 · 0 评论 -
【实战篇】如何正确地显示随机消息?
英语学习 App 首页有一个随机显示单词的功能,这个用户每次访问首页的时候,都会随机滚动显示三个单词。发现随着单词表变大,选单词这个逻辑变得越来越慢,甚至影响到了首页的打开速度。现在,如果让你来设计这个 SQL 语句,你会怎么写呢?原创 2024-09-12 19:50:51 · 734 阅读 · 0 评论 -
【实战篇】“order by”是怎么工作的?
但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么 sort_buffer 里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。如果 MySQL 认为内存足够大,会优先选择全字段排序,把需要的字段都放到 sort_buffer 中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。如果 sort_buffer_size 超过了需要排序的数据量的大小,number_of_tmp_files 就是 0,表示排序可以直接在内存中完成。原创 2024-09-12 19:50:25 · 1016 阅读 · 0 评论 -
【答疑篇】日志和索引相关问题
如果是在时刻 A 的地方异常重启也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前,发生了崩溃(crash),由于此时 binlog 还没写,redo log 也还没提交,所以崩溃恢复的时候,这个事务会回滚。这时候,binlog 还没写,所以也不会传到备库。如果是在时刻 B 的地方异常重启也就是 binlog 写完,redo log 还没 commit 前发生 crash,那崩溃恢复的时候 MySQL 会怎么处理?我们先来看一下崩溃恢复时的判断规则。原创 2024-09-11 17:51:05 · 984 阅读 · 0 评论 -
【实战篇】count(*)这么慢,我该怎么办?
我们可能经常需要计算一个表的行数,但是,我们会发现随着系统中记录数越来越多,这条语句执行得也会越来越慢。那么今天,我们就来聊聊 count(*) 语句到底是怎样实现的,以及 MySQL 为什么会这么实现。然后,在讨论下如果应用中有这种频繁变更并需要统计表行数的需求,业务设计上可以怎么做。原创 2024-09-11 17:50:36 · 977 阅读 · 0 评论 -
【实战篇】为什么表数据删掉一半,表文件大小不变?
日常使用中,当数据库占用空间太大,把一个最大的表删掉了一半的数据,但是表文件的大小还是没变,这是为什么呢?针对 InnoDB 引擎,一个 InnoDB 表包含两部分,即:表结构定义和数据。在 MySQL 8.0 版本以前,表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。原创 2024-09-10 10:57:15 · 728 阅读 · 0 评论 -
【实战篇】为什么我的MySQL会“抖”一下?
不知道你有没有遇到过这样的场景,一条 SQL 语句,正常执行的时候特别快,但是有时也不知道怎么回事,它就会变得特别慢,并且这样的场景很难复现,它不只随机,而且持续时间还很短。看上去,这就像是数据库“抖”了一下。今天,我们就一起来看一看这是什么原因。我们知道 WAL 机制 :InnoDB 在处理更新语句的时候,只做了写日志这一个磁盘操作。这个日志叫作 redo log(重做日志),在更新内存写完 redo log 后,就返回给客户端,本次更新成功。原创 2024-09-10 10:56:37 · 1110 阅读 · 0 评论 -
【实战篇】怎么给字符串字段加索引
直接创建完整索引,这样可能比较占用空间;创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。原创 2024-09-10 10:55:29 · 550 阅读 · 0 评论 -
【实战篇】MySQL为什么有时候会选错索引?
然后,我们往表 t 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。这条查询语句的执行也确实符合预期,key 这个字段值是’a’,表示优化器选择了索引 a。接下来,我们进行如下操作:这时候,session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。原创 2024-09-10 10:54:55 · 883 阅读 · 0 评论 -
【实战篇】普通索引和唯一索引,应该怎么选择?
所以,我在图中就没画出这两部分。因为 merge 的时候是真正进行数据更新的时刻,而 change buffer 的主要目的就是将记录的变更动作缓存下来,所以在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。原创 2024-09-02 12:41:08 · 782 阅读 · 0 评论 -
【基础篇】事务到底是隔离的还是不隔离的?
因为之后的更新,生成的版本一定属于上面的 2 或者 3(a) 的情况,而对它来说,这些新的数据版本是不存在的,所以这个事务的快照,就是“静态”的了。图中是同一行数据的 4 个版本,当前最新版本是 V4,k 的值是 22,它是被 transaction id 为 25 的事务更新的,因此它的 row trx_id 也是 25。比如,对于图 2 中的数据来说,如果有一个事务,它的低水位是 18,那么当它访问这一行数据时,就会从 V4 通过 U3 计算出 V3,所以在它看来,这一行的值是 11。原创 2024-09-02 12:40:43 · 599 阅读 · 0 评论 -
【基础篇】行锁功过:怎么减少行锁对性能的影响?
*MySQL 的行锁是在引擎层由各个引擎自己实现的。**但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。我们主要来聊聊 InnoDB 的行锁,以及如何通过减少锁冲突来提升业务并发度。顾名思义,行锁就是针对数据表中行记录的锁。原创 2024-08-29 17:46:33 · 795 阅读 · 0 评论 -
【基础篇】全局锁和表锁 :给表加个字段怎么有这么多阻碍?
全局锁主要用在逻辑备份过程中,对于全部是 InnoDB 引擎的库,建议你选择使用 single-transaction 参数,对应用会更友好;表锁一般是在数据库引擎不支持行锁的时候才会被用到的,如果你发现你的应用程序里面有 lock tables 这样的语句:要么现在还是用 MyISAM 这种不支持事务的引擎,那要安排升级换引擎;要么是你的引擎升级了,但是代码还没升级。可以将 lock tables 和 unlock tables 换成 begin 和 commit 就可以了。原创 2024-08-29 17:46:19 · 745 阅读 · 0 评论 -
【基础篇】深入浅出索引(下)
如果执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?在这个过程中,可以看到,这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。在这个例子中,由于查询结果所需要的数据只在主键索引上有,所以不得不回表。那么,有没有可能经过索引优化,避免回表过程呢?原创 2024-08-20 09:40:32 · 590 阅读 · 0 评论 -
【基础篇】深入浅出索引(上)
一句话简单来说,原创 2024-08-20 09:40:13 · 840 阅读 · 0 评论 -
【基础篇】事务隔离:为什么你改了我还看不见?
简单来说,事务就是要保证一组数据库操作,。在 MySQL 中,。我们知道,MySQL 是一个支持多引擎的系统,但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务,这也是 MyISAM 被 InnoDB 取代的重要原因之一。原创 2024-08-16 11:32:07 · 793 阅读 · 0 评论 -
【基础篇】日志系统:一条SQL更新语句是如何执行的?
前面我们系统了解了一个查询语句的执行流程,并介绍了执行过程中涉及的处理模块。一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。那么,一条更新语句的执行流程又是怎样的呢?首先,可以确定的说,查询语句的那一套流程,更新语句也是同样会走一遍。原创 2024-08-13 16:49:04 · 795 阅读 · 0 评论 -
【基础篇】基础架构:一条 SQL 查询语句时如何执行的?
大多数情况下我会建议你不要使用查询缓存,为什么呢?因为查询缓存往往弊大于利。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接建立完成之后,来到。原创 2024-08-13 11:25:14 · 319 阅读 · 0 评论