在上一篇文章中,我和你介绍了join语句的两种算法,分别是Index Nested-Loop Join(NLJ)和Block Nested-Loop Join(BNL)。

我们发现在使用NLJ算法的时候,其实效果还是不错的,比通过应用层拆分成多个语句然后再拼接查询结果更方便,而且性能也不会差。

但是,BNL算法在大表join的时候性能就差多了,比较次数等于两个表参与join的行数的乘积,很消耗CPU资源。

当然了,这两个算法都还有继续优化的空间,我们今天就来聊聊这个话题。

我经常会被问到这样一个问题:我的主机内存只有100G,现在要对一个200G的大表做全表扫描,会不会把数据库主机的内存用光了?

这个问题确实值得担心,被系统OOM(out of memory)可不是闹着玩的。但是,反过来想想,逻辑备份的时候,可不就是做整库扫描吗?如果这样就会把内存吃光,逻辑备份不是早就挂了?

所以说,对大表做全表扫描,看来应该是没问题的。但是,这个流程到底是怎么样的呢?

在MySQL中有两个kill命令:一个是kill query +线程id,表示终止这个线程中正在执行的语句;一个是kill connection +线程id,这里connection可缺省,表示断开这个线程的连接,当然如果这个线程有语句正在执行,也是要先停止正在执行的语句的。

不知道你在使用MySQL的时候,有没有遇到过这样的现象:使用了kill命令,却没能断开这个连接。再执行show processlist命令,看到这条语句的Command列显示的是Killed。

你一定会奇怪,显示为Killed是什么意思,不是应该直接在show processlist的结果里看不到这个线程了吗?

今天,我们就来讨论一下这个问题。

其实大多数情况下,kill query/connection命令是有效的。比如,执行一个查询的过程中,发现执行时间太久,要放弃继续查询,这时我们就可以用kill query命令,终止这条查询语句。

今天我要和你讨论的是一个沉重的话题:误删数据。

在前面几篇文章中,我们介绍了MySQL的高可用架构。当然,传统的高可用架构是不能预防误删数据的,因为主库的一个drop table命令,会通过binlog传给所有从库和级联从库,进而导致整个集群的实例都会执行这个命令。

虽然我们之前遇到的大多数的数据被删,都是运维同学或者DBA背锅的。但实际上,只要有数据操作权限的同学,都有可能踩到误删数据这条线。

今天我们就来聊聊误删数据前后,我们可以做些什么,减少误删数据的风险,和由误删数据带来的损失。

为了找到解决误删数据的更高效的方法,我们需要先对和MySQL相关的误删数据,做下分类:

  1. 使用delete语句误删数据行;
  2. 使用drop table或者truncate table语句误删数据表;
  3. 使用drop database语句误删数据库;
  4. 使用rm命令误删整个MySQL实例。

之前介绍了主备切换流程。通过这些内容的讲解,你应该已经很清楚了:在一主一备的双M架构里,主备切换只需要把客户端流量切到备库;而在一主多从架构里,主备切换除了要把客户端流量切到备库外,还需要把从库接到新主库上。

主备切换有两种场景,一种是主动切换,一种是被动切换。而其中被动切换,往往是因为主库出问题了,由HA系统发起的。

这也就引出了我们今天要讨论的问题:怎么判断一个主库出问题了?

你一定会说,这很简单啊,连上MySQL,执行个select 1就好了。但是select 1成功返回了,就表示主库没问题吗?

大多数的互联网应用场景都是读多写少,因此你负责的业务,在发展过程中很可能先会遇到读性能的问题。而在数据库层解决读性能问题,就要涉及到接下来两篇文章要讨论的架构:一主多从。

今天这篇文章,我们就先聊聊一主多从的切换正确性。然后,我们在下一篇文章中再聊聊解决一主多从的查询逻辑正确性的方法。

在上一篇文章中,我和你介绍了几种可能导致备库延迟的原因。你会发现,这些场景里,不论是偶发性的查询压力,还是备份,对备库延迟的影响一般是分钟级的,而且在备库恢复正常以后都能够追上来。

但是,如果备库执行日志的速度持续低于主库生成日志的速度,那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说,备库很可能永远都追不上主库的节奏。

这就涉及到今天我要给你介绍的话题:备库并行复制能力。

在上一篇文章中,我和你介绍了binlog的基本内容,在一个主备关系中,每个备库接收主库的binlog并执行。

正常情况下,只要主库执行更新生成的所有binlog,都可以传到备库并被正确地执行,备库就能达到跟主库一致的状态,这就是最终一致性。

但是,MySQL要提供高可用能力,只有最终一致性是不够的。为什么这么说呢?今天我就着重和你分析一下。

这里,我再放一次上一篇文章中讲到的双M结构的主备切换流程图。

在前面的文章中,我不止一次地和你提到了binlog,大家知道binlog可以用来归档,也可以用来做主备同步,但它的内容是什么样的呢?为什么备库执行了binlog就可以跟主库保持一致了呢?今天我就正式地和你介绍一下它。

毫不夸张地说,MySQL能够成为现下最流行的开源数据库,binlog功不可没。

在最开始,MySQL是以容易学习和方便的高可用架构,被开发人员青睐的。而它的几乎所有的高可用架构,都直接依赖于binlog。虽然这些高可用架构已经呈现出越来越复杂的趋势,但都是从最基本的一主一备演化过来的。

今天这篇文章我主要为你介绍主备的基本原理。理解了背后的设计原理,你也可以从业务开发的角度,来借鉴这些设计思想。

不知道你在实际运维过程中有没有碰到这样的情景:业务高峰期,生产环境的MySQL压力太大,没法正常响应,需要短期内、临时性地提升一些性能。

我以前做业务护航的时候,就偶尔会碰上这种场景。用户的开发负责人说,不管你用什么方案,让业务先跑起来再说。

但,如果是无损方案的话,肯定不需要等到这个时候才上场。今天我们就来聊聊这些临时方案,并着重说一说它们可能存在的风险。

我和你介绍了间隙锁和next-key lock的概念,但是并没有说明加锁规则。间隙锁的概念理解起来确实有点儿难,尤其在配合上行锁以后,很容易在判断是否会出现锁等待的问题上犯错。

所以今天,我们就先从这个加锁规则开始吧。

首先说明一下,这些加锁规则我没在别的地方看到过有类似的总结,以前我自己判断的时候都是想着代码里面的实现来脑补的。这次为了总结成不看代码的同学也能理解的规则,是我又重新刷了代码临时总结出来的。所以,这个规则有以下两条前提说明:

  1. MySQL后面的版本可能会改变加锁策略,所以这个规则只限于截止到现在的最新版本,即5.x系列<=5.7.24,8.0系列 <=8.0.13。
  2. 如果大家在验证中有发现bad case的话,请提出来。

因为间隙锁在可重复读隔离级别下才有效,所以本篇文章接下来的描述,若没有特殊说明,默认是可重复读隔离级别

为了便于说明问题,这一篇文章,我们就先使用一个小一点儿的表。建表和初始化语句如下:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

这个表除了主键id外,还有一个索引c,初始化语句在表中插入了6行数据。

一般情况下,如果我跟你说查询性能优化,你首先会想到一些复杂的语句,想到查询需要返回大量的数据。但有些情况下,“查一行”,也会执行得特别慢。今天,我就跟你聊聊这个有趣的话题,看看什么情况下,会出现这个现象。

需要说明的是,如果MySQL数据库本身就有很大的压力,导致数据库服务器CPU占用率很高或ioutil(IO利用率)很高,这种情况下所有语句的执行都有可能变慢,不属于我们今天的讨论范围。

这篇文章,我就从这个性能问题说起,和你说说MySQL中的另外一种排序需求,希望能够加深你对MySQL排序逻辑的理解。

有个英语学习App,首页有一个随机显示单词的功能,也就是根据每个用户的级别有一个单词表,然后这个用户每次访问首页的时候,都会随机滚动显示三个单词。他们发现随着单词表变大,选单词这个逻辑变得越来越慢,甚至影响到了首页的打开速度。

现在,如果让你来设计这个SQL语句,你会怎么写呢?

日志相关问题

之前文章讲到binlog(归档日志)和redo log(重做日志)配合崩溃恢复的时候,用的是反证法,说明了如果没有两阶段提交,会导致MySQL出现主备数据不一致等问题。

很多同学在问,在两阶段提交的不同瞬间,MySQL如果发生异常重启,是怎么保证数据完整性的?

现在,我们就从这个问题开始吧。

我再放一次两阶段提交的图,方便你学习下面的内容。

在开发系统的时候,你可能经常需要计算一个表的行数,比如一个交易系统的所有变更记录总数。这时候你可能会想,一条select count(*) from t 语句不就解决了吗?

但是,你会发现随着系统中记录数越来越多,这条语句执行得也会越来越慢。然后你可能就想了,MySQL怎么这么笨啊,记个总数,每次要查的时候直接读出来,不就好了吗。

那么今天,我们就来聊聊count(*)语句到底是怎样实现的,以及MySQL为什么会这么实现。然后,我会再和你说说,如果应用中有这种频繁变更并需要统计表行数的需求,业务设计上可以怎么做。

经常会有同学来问我,我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?

那么今天,我就和你聊聊数据库表的空间回收,看看如何解决这个问题。

这里,我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论。一个InnoDB表包含两部分,即:表结构定义和数据。在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里。而MySQL 8.0版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。

接下来,我会先和你说明为什么简单地删除表数据达不到表空间回收的效果,然后再和你介绍正确回收空间的方法。

平时的工作中,不知道你有没有遇到过这样的场景,一条SQL语句,正常执行的时候特别快,但是有时也不知道怎么回事,它就会变得特别慢,并且这样的场景很难复现,它不只随机,而且持续时间还很短。

看上去,这就像是数据库“抖”了一下。今天,我们就一起来看一看这是什么原因。

现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。

假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:

1
2
3
4
5
mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

由于要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句:

1
mysql> select f1, f2 from SUser where email='xxx';

前面我们介绍过索引,你已经知道了在MySQL中一张表其实是可以支持多个索引的。但是,你写SQL语句的时候,并没有主动指定使用哪个索引。也就是说,使用哪个索引是由MySQL来确定的。

不知道你有没有碰到过这种情况,一条本来可以执行得很快的语句,却由于MySQL选错了索引,而导致执行速度变得很慢?

在前面的基础篇文章中,我给你介绍过索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就继续来谈谈,在不同的业务场景下,应该选择普通索引,还是唯一索引?

假设你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名,就会执行类似这样的SQL语句:

1
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

所以,你一定会考虑在id_card字段上建索引。

由于身份证号字段比较大,我不建议你把身份证号当做主键,那么现在你有两个选择,要么给id_card字段创建唯一索引,要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。

如果是可重复读隔离级别,事务T启动的时候会创建一个视图read-view,之后事务T执行期间,即使有其他事务修改了数据,事务T看到的仍然跟在启动时看到的一样。也就是说,一个在可重复读隔离级别下执行的事务,好像与世无争,不受外界影响。

但是,我在上一篇文章中,和你分享行锁的时候又提到,一个事务要更新一行,如果刚好有另外一个事务拥有这一行的行锁,它又不能这么超然了,会被锁住,进入等待状态。问题是,既然进入了等待状态,那么等到这个事务自己获取到行锁要更新数据的时候,它读到的值又是什么呢?

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。

我们今天就主要来聊聊InnoDB的行锁,以及如何通过减少锁冲突来提升业务并发度。

顾名思义,行锁就是针对数据表中行记录的锁。这很好理解,比如事务A更新了一行,而这时候事务B也要更新同一行,则必须等事务A的操作完成后才能进行更新。

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。今天这篇文章,我会和你分享全局锁和表级锁。

这里需要说明的是,锁的设计比较复杂,章不会涉及锁的具体实现细节,主要介绍的是碰到锁时的现象和其背后的原理。