magic_chao1
这个作者很懒,什么都没留下…
展开
-
37 | 什么时候会使用内部临时表?
但是,如果碰上不适合创建索引的场景,我们还是要老老实实做排序的。Extra 字段,表示在对子查询的结果集做 union 的时候,使用了临时表 (Using temporary)。在 group by 语句中加入 SQL_BIG_RESULT 这个提示(hint),就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。这个语句的逻辑是把表 t1 里的数据,按照 id%10 进行分组统计,并按照 m 的结果排序后输出。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。原创 2024-05-11 15:10:16 · 32 阅读 · 0 评论 -
34 | 到底可不可以使用join?
t2 里插入了 1000 行数据,在表 t1 里插入的是 100 行数据。原创 2024-05-10 17:13:54 · 22 阅读 · 0 评论 -
26 | 备库为什么会延迟好几个小时?
在官方的 5.6 版本之前,MySQL 只支持单线程复制,由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。coordinator 就是原来的 sql_thread, 不过现在它不再直接更新数据了,只负责读取中转日志和分发事务。真正更新日志的,变成了 worker 线程。而 work 线程的个数,就是由参数slave_parallel_workers 决定的。根据我的经验,把这个值设置为 8~16 之间最好(32 核物理机的情况),毕竟备库还有可能要提供读查询,不能把 CPU 都吃光了。原创 2024-05-09 18:03:19 · 52 阅读 · 0 评论 -
25 | MySQL是怎么保证高可用的?
所谓主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是 T3-T1。原创 2024-05-09 16:30:44 · 130 阅读 · 0 评论 -
24 | MySQL是怎么保证主备一致的?
由于 statement 格式下,记录到 binlog 里的是语句原文,因此可能会出现这样一种情况:在主库执行这条 SQL 语句的时候,用的是索引 a;当 binlog_format 使用 row 格式的时候,binlog 里面记录了真实删除行的主键 id,这样 binlog 传到备库去的时候,就肯定会删除 id=4 的行,不会有主备删除不同行的问题。binlog 有两种格式,一种是 statement,一种是 row。时,binlog 里面记录的就是 SQL 语句的原文。双 Master 结构。原创 2024-05-09 16:07:21 · 98 阅读 · 0 评论 -
23 | MySQL是怎么保证数据不丢的?
系统给 binlog cache 分配了一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小,就要暂存到磁盘。binlog 的写入逻辑比较简单:事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。,指的就是指把日志写入到文件系统的 page cache,并没有把数据持久化到磁盘,所以速度比较快。原创 2024-05-09 14:39:47 · 14 阅读 · 0 评论 -
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
创建索引都支持 Online DDL 了,对于那种高峰期数据库已经被这个语句打挂了的情况,最高效的做法就是直接执行 alter table 语句。让数据库跳过权限验证阶段,重启数据库,并使用–skip-grant-tables 参数启动。原创 2024-05-08 18:01:23 · 39 阅读 · 2 评论 -
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。这个语句里 trade_detail 表成了驱动表,但是 explain 结果的第二行显示,这次的查询操作用上了被驱动表 tradelog 里的索引 (tradeid),扫描行数是 1。原创 2024-04-29 17:19:47 · 12 阅读 · 0 评论 -
21 | 为什么我只改一行的语句,锁这么多?
表结构。原创 2024-04-30 15:33:57 · 20 阅读 · 0 评论 -
20 | 幻读是什么,幻读有什么问题?
幻读是什么?在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。上面 session B 的修改结果,被 session A 之后的 select语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”。原创 2024-04-30 10:35:03 · 37 阅读 · 0 评论 -
19 | 为什么我只查一行的语句,也执行这么慢?
带 lock in share mode 的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果,所以速度很快;而 select * from t where id=1 这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回。由于字段 c 上没有索引,这个语句只能走 id 主键顺序扫描,因此需要扫描 5 万行。大概率是表 t 被锁住了。接下来分析原因的时候,一般都是首先执行一下。命令,看看当前语句处于什么状态。原创 2024-04-30 10:00:37 · 15 阅读 · 0 评论 -
17 | 如何正确地显示随机消息?
Extra 字段显示 Using temporary,表示的是需要使用临时表;Using filesort,表示的是需要执行排序操作。需要临时表,并且需要在临时表上排序。order by rand() 使用了内存临时表,内存临时表排序的时候使用了 rowid 排序方法。原创 2024-04-29 14:50:39 · 11 阅读 · 0 评论 -
16 | “order by”是怎么工作的?
但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么 sort_buffer 里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。在这个索引里面,我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录,并且额外确保了,接下来按顺序取“下一条记录”的遍历过程中,只要 city 的值是杭州,name 的值就一定是有序的。从上面分析的执行过程,我们可以看到,MySQL 之所以需要生成临时表,并且在临时表上做排序操作,其原因是原来的数据都是无序的。原创 2024-04-28 17:48:17 · 13 阅读 · 0 评论 -
15 | 答疑文章(一):日志和索引相关问题
如果在图中的地方,也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前,发生了崩溃(crash),由于此时 binlog 还没写,redo log 也还没提交,所以崩溃恢复的时候,这个事务会回滚。这时候,binlog 还没写,所以也不会传到备库。也就是 binlog 写完,redo log 还没 commit 前发生 crash,崩溃恢复时的判断规则。原创 2024-04-28 15:25:50 · 18 阅读 · 0 评论 -
14 | count(*)这么慢,我该怎么办?
server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。原创 2024-04-28 14:43:42 · 14 阅读 · 0 评论 -
13 | 为什么表数据删掉一半,表文件大小不变?
结果就是,所有的数据页都会被标记为可复用。delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。我们要删掉 R4 这个记录,InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时,可能会复用这个位置。现在,你已经知道了 InnoDB 的数据是按页存储的,那么如果我们删掉了一个数据页上的所有记录,会怎么样?答案是,整个数据页就可以被复用了。原创 2024-04-28 11:44:22 · 22 阅读 · 0 评论 -
12 | 为什么我的MySQL会“抖”一下?
平时执行很快的更新操作,其实就是在写内存和日志,而 MySQL 偶尔“抖”一下的那个瞬间,可能就是在。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“原创 2024-04-26 18:20:34 · 13 阅读 · 0 评论 -
11 | 怎么给字符串字段加索引?
优势:用的空间会更小 缺点:索引覆盖失效当然,使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如5%。然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,假设这里 L6、L7 都满足,你就可以选择前缀长度为 6。使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。原创 2024-04-26 13:54:46 · 13 阅读 · 0 评论 -
10 | MySQL为什么有时候会选错索引?
背景: t 中插入 10 万行记录,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。key 这个字段值是’a’,表示优化器选择了索引 a。第一句,是将慢查询日志的阈值设置为 0,表示这个线程接下来的语句都会被记录入慢查询日志中;第二句,Q1 是 session B 原来的查询;第三句,Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。原创 2024-04-24 13:46:59 · 138 阅读 · 0 评论 -
09 | 普通索引和唯一索引,应该怎么选择?
在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。因为引擎是按页读写的,所以说,当找到 k=5 的记录的时候,它所在的数据页就都在内存里了。当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InooDB 会将这些更新操作缓存在。当然,如果 k=5 这个记录刚好是这个数据页的最后一个记录,那么要取下一个记录,必须读取下一个数据页,这个操作会稍微复杂一些。原创 2024-04-19 18:28:28 · 305 阅读 · 0 评论 -
08 | 事务到底是隔离的还是不隔离的?
前提:一致性视图是在执行 start transaction with consistent snapshot 时创建的。原创 2024-04-19 17:26:05 · 290 阅读 · 0 评论 -
07 | 行锁功过:怎么减少行锁对性能的影响?
每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是 O(n) 的操作。你可以想象一下这个过程:每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。用法:如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。原创 2024-04-19 15:51:24 · 137 阅读 · 0 评论 -
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。针对修改表结构和查询表数据,防止个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上。阻塞的命令:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。sessionC启动,加MDL写锁,sessionA未解锁,阻塞;原创 2024-04-19 11:42:41 · 259 阅读 · 0 评论 -
05 | 深入浅出索引(下)
需要执行几次树的操作执行流程回到主键索引树搜索的过程,我们称为。由于查询结果所需要的数据只在主键索引上有,所以不得不回表。那么,有没有可能经过索引优化,避免回表过程呢?原创 2024-04-18 17:43:58 · 134 阅读 · 0 评论 -
04 | 深入浅出索引(上)
哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。重建索引 k 的做法是合理的:索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。非主键索引的叶子节点内容是主键的值。原创 2024-04-18 15:40:24 · 328 阅读 · 0 评论 -
03 | 事务隔离:为什么你改了我还看不见?
当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。如图中看到的,在视图 A、B、C 里面,这一个记录的值分别是 1、2、4,同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。同时你会发现,即使现在有另外一个事务正在将 4 改成 5,这个事务跟 read-view A、B、C 对应的事务是不会冲突的。原创 2024-04-18 15:03:07 · 538 阅读 · 0 评论 -
02 | 日志系统:一条SQL更新语句是如何执行的?
前面我们讲过,MySQL 整体来看,其实就有两块:一块是 Server 层,它主要做的是 MySQL 功能层面的事情;MySQL 有这个问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。,它的关键点就是先写日志,再写磁盘,也就是先写粉板,等不忙的时候再写账本。原创 2024-04-16 12:32:40 · 656 阅读 · 0 评论 -
01 | 基础架构:一条SQL查询语句是如何执行的?
大体来说,MySQL 可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。原创 2024-04-15 17:44:08 · 905 阅读 · 0 评论