目录
怎么最快地复制一张表
-
物理拷贝的方式速度最快,尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况,用备份恢复出误删之前的临时库,然后再把临时库中的表拷贝到生产库上,是恢复数据最快的方法。但是,这种方法的使用也有一定的局限性:
- 必须是全表拷贝,不能只拷贝部分数据;
- 需要到服务器上拷贝数据,在用户无法登录数据库主机的场景下无法使用;
- 由于是通过拷贝物理文件实现的,源表和目标表都是使用InnoDB引擎时才能使用。
-
用mysqldump生成包含INSERT语句文件的方法,可以在where参数增加过滤条件,来实现只导出部分数据。这个方式的不足之一是,不能使用join这种比较复杂的where条件写法。
-
用select … into outfile的方法是最灵活的,支持所有的SQL写法。但,这个方法的缺点之一就是,每次只能导出一张表的数据,而且表结构也需要另外的语句单独备份。
后两种方式都是逻辑备份方式,是可以跨引擎使用的。
如果可以控制对源表的扫描行数和加锁范围很小的话,简单地使用insert … select 语句即可实现。当然,为了避免对源表加读锁,更稳妥的方案是先将数据写到外部文本文件,然后再写回目标表。
先创建一个表db1.t,并插入1000行数据,同时创建一个相同结构的表db2.t。
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create database db2;
create table db2.t like db1.t
假设,我们要把db1.t里面a>900的数据行导出来,插入到db2.t中。
mysqldump方法
一种方法是,使用mysqldump命令将数据导出成一组INSERT语句。你可以使用下面的命令:
mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
把结果输出到临时文件。
这条命令中,主要参数含义如下:
–single-transaction的作用是,在导出数据的时候不需要对表db1.t加表锁,而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法;
–add-locks设置为0,表示在输出的文件结果里,不增加" LOCK TABLES
t
WRITE;" ;–no-create-info的意思是,不需要导出表结构;
–set-gtid-purged=off表示的是,不输出跟GTID相关的信息;
–result-file指定了输出文件的路径,其中client表示生成的文件是在客户端机器上的。
通过这条mysqldump命令生成的t.sql文件中就包含了如图1所示的INSERT语句。
可以看到,一条INSERT语句里面会包含多个value对,这是为了后续用这个文件来写入数据的时候,执行速度可以更快。
如果你希望生成的文件中一条INSERT语句只插入一行数据的话,可以在执行mysqldump命令时,加上参数–skip-extended-insert。
然后,你可以通过下面这条命令,将这些INSERT语句放到db2库里去执行。
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
需要说明的是,source并不是一条SQL语句,而是一个客户端命令。mysql客户端执行这个命令的流程是这样的:
打开文件,默认以分号为结尾读取一条条的SQL语句;
将SQL语句发送到服务端执行。
也就是说,服务端执行的并不是这个“source t.sql"语句,而是INSERT语句。所以,不论是在慢查询日志(slow log),还是在binlog,记录的都是这些要被真正执行的INSERT语句。
导出CSV文件
另一种方法是直接将结果导出成.csv文件。MySQL提供了下面的语法,用来将查询结果导出到服务端本地目录:
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
我们在使用这条语句时,需要注意如下几点。
-
这条语句会将结果保存在服务端。如果你执行命令的客户端和MySQL服务端不在同一个机器上,客户端机器的临时目录下是不会生成t.csv文件的。
-
into outfile指定了文件的生成位置(/server_tmp/),这个位置必须受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用分别是:
- 如果设置为empty,表示不限制文件生成的位置,这是不安全的设置;
- 如果设置为一个表示路径的字符串,就要求生成的文件只能放在这个指定的目录,或者它的子目录;
- 如果设置为NULL,就表示禁止在这个MySQL实例上执行select … into outfile 操作。
-
这条命令不会帮你覆盖文件,因此你需要确保/server_tmp/t.csv这个文件不存在,否则执行语句时就会因为有同名文件的存在而报错。
-
这条命令生成的文本文件中,原则上一个数据行对应文本文件的一行。但是,如果字段中包含换行符,在生成的文本中也会有换行符。不过类似换行符、制表符这类符号,前面都会跟上“\”这个转义符,这样就可以跟字段之间、数据行之间的分隔符区分开。
得到.csv导出文件后,你就可以用下面的load data命令将数据导入到目标表db2.t中。
load data infile '/server_tmp/t.csv' into table db2.t;
这条语句的执行流程如下所示。
打开文件/server_tmp/t.csv,以制表符(\t)作为字段间的分隔符,以换行符(\n)作为记录之间的分隔符,进行数据读取;
启动事务。
判断每一行的字段数与表db2.t是否相同:
- 若不相同,则直接报错,事务回滚;
- 若相同,则构造成一行,调用InnoDB引擎接口,写入到表中。
重复步骤3,直到/server_tmp/t.csv整个文件读入完成,提交事务。
如果binlog_format=statement,这个load语句记录到binlog里以后,怎么在备库重放呢?
由于/server_tmp/t.csv文件只保存在主库所在的主机上,如果只是把这条语句原文写到binlog中,在备库执行的时候,备库的本地机器上没有这个文件,就会导致主备同步停止。
所以,这条语句执行的完整流程,其实是下面这样的。
主库执行完成后,将/server_tmp/t.csv文件的内容直接写到binlog文件中。
往binlog文件中写入语句load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`。
把这个binlog日志传到备库。
备库的apply线程在执行这个事务日志时:
a. 先将binlog中t.csv文件的内容读出来,写入到本地临时目录/tmp/SQL_LOAD_MB-1-0 中;
b. 再执行load data语句,往备库的db2.t表中插入跟主库相同的数据。
执行流程如图2所示:
注意,这里备库执行的load data语句里面,多了一个“local”。它的意思是“将执行这条命令的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0的内容,加载到目标表db2.t中”。
也就是说,load data命令有两种用法:
不加“local”,是读取服务端的文件,这个文件必须在secure_file_priv指定的目录或子目录下;
加上“local”,读取的是客户端的文件,只要mysql客户端有访问这个文件的权限即可。这时候,MySQL客户端会先把本地文件传给服务端,然后执行上述的load data流程。
另外需要注意的是,select …into outfile方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。mysqldump提供了一个–tab参数,可以同时导出表结构定义文件和csv数据文件。这条命令的使用方法如下:
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
这条命令会在$secure_file_priv定义的目录下,创建一个t.sql文件保存建表语句,同时创建一个t.txt文件保存CSV数据。
物理拷贝方法
前面我们提到的mysqldump方法和导出CSV文件的方法,都是逻辑导数据的方法,也就是将数据从表db1.t中读出来,生成文本,然后再写入目标表db2.t中。
你可能会问,有物理导数据的方法吗?比如,直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下,是否可行呢?
答案是不行的。
因为,一个InnoDB表,除了包含这两个物理文件外,还需要在数据字典中注册。直接拷贝这两个文件的话,因为数据字典中没有db2.t这个表,系统是不会识别和接受它们的。
不过,在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法,可以通过导出+导入表空间的方式,实现物理拷贝表的功能。
假设我们现在的目标是在db1库下,复制一个跟表t相同的表r,具体的执行步骤如下:
-
执行 create table r like t,创建一个相同表结构的空表;
-
执行alter table r discard tablespace,这时候r.ibd文件会被删除;
-
执行flush table t for export,这时候db1目录下会生成一个t.cfg文件;
-
在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd;这两个命令;
-
执行unlock tables,这时候t.cfg文件会被删除;
-
执行alter table r import tablespace,将这个r.ibd文件作为表r的新的表空间,由于这个文件的数据内容和t.ibd是相同的,所以表r中就有了和表t相同的数据。
至此,拷贝表数据的操作就完成了。这个流程的执行过程图如下:
关于拷贝表的这个流程,有以下几个注意点:
-
在第3步执行完flsuh table命令之后,db1.t整个表处于只读状态,直到执行unlock tables命令后才释放读锁;
-
在执行import tablespace的时候,为了让文件里的表空间id和数据字典中的一致,会修改t.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此,如果是一个很大的文件(比如TB级别),每个数据页都需要修改,所以你会看到这个import语句的执行是需要一些时间的。当然,如果是相比于逻辑导入的方法,import语句的耗时是非常短的。
binlog_format=statement的时候,binlog记录的load data命令是带local的。既然这条命令是发送到备库去执行的,那么备库执行的时候也是本地执行,为什么需要这个local呢?如果写到binlog中的命令不带local,又会出现什么问题呢?
这样做的一个原因是,为了确保备库应用binlog正常。因为备库可能配置了secure_file_priv=null,所以如果不用local的话,可能会导入失败,造成主备同步延迟。
另一种应用场景是使用mysqlbinlog工具解析binlog文件,并应用到目标库的情况。你可以使用下面这条命令 :
mysqlbinlog $binlog_file | mysql -h$host -P$port -u$user -p$pwd
把日志直接解析出来发给目标库执行。增加local,就能让这个方法支持非本地的$host。
grant语句和flush privileges语句
先创建一个用户:
create user 'ua'@'%' identified by 'pa';
这条语句的逻辑是创建一个用户’ua’@’%’,密码是pa。注意,在MySQL里面,用户名(user)+地址(host)才表示一个用户,因此 ua@ip1 和 ua@ip2代表的是两个不同的用户。
这条命令做了两个动作:
-
磁盘上,往mysql.user表里插入一行,由于没有指定权限,所以这行数据上所有表示权限的字段的值都是N;
-
内存里,往数组acl_users里插入一个acl_user对象,这个对象的access字段值为0。
图1就是这个时刻用户ua在user表中的状态。
全局权限
全局权限,作用于整个MySQL实例,这些权限信息保存在mysql库的user表里。如果我要给用户ua赋一个最高权限的话,语句是这么写的:
grant all privileges on *.* to 'ua'@'%' with grant option;
这个grant命令做了两个动作:
-
磁盘上,将mysql.user表里,用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’;
-
内存里,从数组acl_users中找到这个用户对应的对象,将access值(权限位)修改为二进制的“全1”。
在这个grant命令执行完成后,如果有新的客户端使用用户名ua登录成功,MySQL会为新连接维护一个线程对象,然后从acl_users数组里查到这个用户的权限,并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句,所有关于全局权限的判断,都直接使用线程对象内部保存的权限位。
基于上面的分析我们可以知道:
-
grant 命令对于全局权限,同时更新了磁盘和内存。命令完成后即时生效,接下来新创建的连接会使用新的权限。
-
对于一个已经存在的连接,它的全局权限不受grant命令的影响。
需要说明的是,一般在生产环境上要合理控制用户权限的范围。我们上面用到的这个grant语句就是一个典型的错误示范。如果一个用户有所有权限,一般就不应该设置为所有IP地址都可以访问。
如果要回收上面的grant语句赋予的权限,你可以使用下面这条命令:
revoke all privileges on *.* from 'ua'@'%';
这条revoke命令的用法与grant类似,做了如下两个动作:
-
磁盘上,将mysql.user表里,用户’ua’@’%'这一行的所有表示权限的字段的值都修改为“N”;
-
内存里,从数组acl_users中找到这个用户对应的对象,将access的值修改为0。
db权限
除了全局权限,MySQL也支持库级别的权限定义。如果要让用户ua拥有库db1的所有权限,可以执行下面这条命令:
grant all privileges on db1.* to 'ua'@'%' with grant option;
基于库的权限记录保存在mysql.db表中,在内存里则保存在数组acl_dbs中。这条grant命令做了如下两个动作:
-
磁盘上,往mysql.db表中插入了一行记录,所有权限位字段设置为“Y”;
-
内存里,增加一个对象到数组acl_dbs中,这个对象的权限位为“全1”。
图2就是这个时刻用户ua在db表中的状态。
每次需要判断一个用户对一个数据库读写权限的时候,都需要遍历一次acl_dbs数组,根据user、host和db找到匹配的对象,然后根据对象的权限位来判断。
也就是说,grant修改db权限的时候,是同时对磁盘和内存生效的。
grant操作对于已经存在的连接的影响,在全局权限和基于db的权限效果是不同的。接下来,我们做一个对照试验来分别看一下。
需要说明的是,图中set global sync_binlog这个操作是需要super权限的。
可以看到,虽然用户ua的super权限在T3时刻已经通过revoke语句回收了,但是在T4时刻执行set global的时候,权限验证还是通过了。这是因为super是全局权限,这个权限信息在线程对象中,而revoke操作影响不到这个线程对象。
而在T5时刻去掉ua对db1库的所有权限后,在T6时刻session B再操作db1库的表,就会报错“权限不足”。这是因为acl_dbs是一个全局数组,所有线程判断db权限都用这个数组,这样revoke操作马上就会影响到session B。
这里在代码实现上有一个特别的逻辑,如果当前会话已经处于某一个db里面,之前use这个库的时候拿到的库权限会保存在会话变量中。
你可以看到在T6时刻,session C和session B对表t的操作逻辑是一样的。但是session B报错,而session C可以执行成功。这是因为session C在T2 时刻执行的use db1,拿到了这个库的权限,在切换出db1库之前,session C对这个库就一直有权限。
表权限和列权限
除了db级别的权限外,MySQL支持更细粒度的表权限和列权限。其中,表权限定义存放在表mysql.tables_priv中,列权限定义存放在表mysql.columns_priv中。这两类权限,组合起来存放在内存的hash结构column_priv_hash中。
这两类权限的赋权命令如下:
create table db1.t1(id int, a int);
grant all privileges on db1.t1 to 'ua'@'%' with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;
跟db权限类似,这两个权限每次grant的时候都会修改数据表,也会同步修改内存中的hash结构。因此,对这两类权限的操作,也会马上影响到已经存在的连接。
grant语句都是即时生效的,那这么看应该就不需要执行flush privileges语句了呀。
答案也确实是这样的。
flush privileges命令会清空acl_users数组,然后从mysql.user表中读取数据重新加载,重新构造一个acl_users数组。也就是说,以数据表中的数据为准,会将全局权限内存数组重新加载一遍。
同样地,对于db权限、表权限和列权限,MySQL也做了这样的处理。
也就是说,如果内存的权限数据和磁盘数据表相同的话,不需要执行flush privileges。而如果我们都是用grant/revoke语句来执行的话,内存和数据表本来就是保持同步更新的。
因此,正常情况下,grant命令之后,没有必要跟着执行flush privileges命令。
flush privileges使用场景
那么,flush privileges是在什么时候使用呢?显然,当数据表中的权限数据跟内存中的权限数据不一致的时候,flush privileges语句可以用来重建内存数据,达到一致状态。
这种不一致往往是由不规范的操作导致的,比如直接用DML语句操作系统权限表。我们来看一下下面这个场景:
可以看到,T3时刻虽然已经用delete语句删除了用户ua,但是在T4时刻,仍然可以用ua连接成功。原因就是,这时候内存中acl_users数组中还有这个用户,因此系统判断时认为用户还正常存在。
在T5时刻执行过flush命令后,内存更新,T6时刻再要用ua来登录的话,就会报错“无法访问”了。
直接操作系统表是不规范的操作,这个不一致状态也会导致一些更“诡异”的现象发生。比如,前面这个通过delete语句删除用户的例子,就会出现下面的情况:
可以看到,由于在T3时刻直接删除了数据表的记录,而内存的数据还存在。这就导致了:
-
T4时刻给用户ua赋权限失败,因为mysql.user表中找不到这行记录;
-
而T5时刻要重新创建这个用户也不行,因为在做内存判断的时候,会认为这个用户还存在。
grant语句会同时修改数据表和内存,判断权限的时候使用的是内存数据。因此,规范地使用grant和revoke语句,是不需要随后加上flush privileges语句的。
flush privileges语句本身会用数据表的数据重建一份内存权限数据,所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用DML语句操作系统权限表导致的,所以我们尽量不要使用这类语句。
另外,在使用grant语句赋权时,你可能还会看到这样的写法:
grant super on *.* to 'ua'@'%' identified by 'pa';
这条命令加了identified by ‘密码’, 语句的逻辑里面除了赋权外,还包含了:
-
如果用户’ua’@’%'不存在,就创建这个用户,密码是pa;
-
如果用户ua已经存在,就将密码修改成pa。
这也是一种不建议的写法,因为这种写法很容易就会不慎把密码给改了。
\G 行转列并发送给 mysql server
\g 等同于 ;
\! 执行系统命令
\q exit
\c 清除当前SQL(不执行)
\s mysql status 信息
其他参考 \h
分区表
分区表是什么?
为了说明分区表的组织形式,我先创建一个表t:
CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);
我在表t中初始化插入了两行记录,按照定义的分区规则,这两行记录分别落在p_2018和p_2019这两个分区上。
可以看到,这个表包含了一个.frm文件和4个.ibd文件,每个分区对应一个.ibd文件。也就是说:
- 对于引擎层来说,这是4个表;
- 对于Server层来说,这是1个表。
分区表的引擎层行为
举个在分区表加间隙锁的例子,目的是说明对于InnoDB来说,这是4个表。
我们初始化表t的时候,只插入了两行数据, ftime的值分别是,‘2017-4-1’ 和’2018-4-1’ 。session A的select语句对索引ftime上这两个记录之间的间隙加了锁。如果是一个普通表的话,那么T1时刻,在表t的ftime索引上,间隙和加锁状态应该是图3这样的。
也就是说,‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。那么,sesion B的两条插入语句应该都要进入锁等待状态。
但是,从上面的实验效果可以看出,session B的第一个insert语句是可以执行成功的。这是因为,对于引擎来说,p_2018和p_2019是两个不同的表,也就是说2017-4-1的下一个记录并不是2018-4-1,而是p_2018分区的supremum。所以T1时刻,在表t的ftime索引上,间隙和加锁的状态其实是图4这样的:
由于分区表的规则,session A的select语句其实只操作了分区p_2018,因此加锁范围就是图4中深绿色的部分。
所以,session B要写入一行ftime是2018-2-1的时候是可以成功的,而要写入2017-12-1这个记录,就要等session A的间隙锁。
图5就是这时候的show engine innodb status的部分结果。
MyISAM分区表的例子。
我首先用alter table t engine=myisam,把表t改成MyISAM表;然后,我再用下面这个例子说明,对于MyISAM引擎来说,这是4个表。
在session A里面,我用sleep(100)将这条语句的执行时间设置为100秒。由于MyISAM引擎只支持表锁,所以这条update语句会锁住整个表t上的读。
但我们看到的结果是,session B的第一条查询语句是可以正常执行的,第二条语句才进入锁等待状态。
这正是因为MyISAM的表锁是在引擎层实现的,session A加的表锁,其实是锁在分区p_2018上。因此,只会堵住在这个分区上执行的查询,落到其他分区的查询是不受影响的。
分区表看来还不错嘛,为什么不让用呢?我们使用分区表的一个重要原因就是单表过大。那么,如果不使用分区表的话,我们就是要使用手动分表的方式。
手动分表和分区表有什么区别。
比如,按照年份来划分,我们就分别创建普通表t_2017、t_2018、t_2019等等。手工分表的逻辑,也是找到需要更新的所有分表,然后依次执行更新。在性能上,这和分区表并没有实质的差别。
分区表和手工分表,一个是由server层来决定使用哪个分区,一个是由应用层代码来决定使用哪个分表。因此,从引擎层看,这两种方式也是没有差别的。
其实这两个方案的区别,主要是在server层上。从server层看,我们就不得不提到分区表一个被广为诟病的问题:打开表的行为。
分区策略
每当第一次访问一个分区表的时候,MySQL需要把所有的分区都访问一遍。
一个典型的报错情况:如果一个分区表的分区很多,比如超过了1000个,而MySQL启动的时候,open_files_limit参数使用的是默认值1024,那么就会在访问这个表的时候,由于需要打开所有的文件,导致打开表文件的个数超过了上限而报错。
下图创建的一个包含了很多分区的表t_myisam,执行一条插入语句后报错的情况。
可以看到,这条insert语句,明显只需要访问一个分区,但语句却无法执行。
这个表我用的是MyISAM引擎。是的,因为使用InnoDB引擎的话,并不会出现这个问题。
MyISAM分区表使用的分区策略,我们称为通用分区策略(generic partitioning),每次访问分区都由server层控制。通用分区策略,是MySQL一开始支持分区表的时候就存在的代码,在文件管理、表管理的实现上很粗糙,因此有比较严重的性能问题。
从MySQL 5.7.9开始,InnoDB引擎引入了本地分区策略(native partitioning)。这个策略是在InnoDB内部自己管理打开分区的行为。
MySQL从5.7.17开始,将MyISAM分区表标记为即将弃用(deprecated),意思是“从这个版本开始不建议这么使用,请使用替代方案。在将来的版本中会废弃这个功能”。
从MySQL 8.0版本开始,就不允许创建MyISAM分区表了,只允许创建已经实现了本地分区策略的引擎。目前来看,只有InnoDB和NDB这两个引擎支持了本地分区策略。
接下来,我们再看一下分区表在server层的行为。
分区表的server层行为
如果从server层看的话,一个分区表就只是一个表。
如图8和图9所示,分别是这个例子的操作序列和执行结果图。
可以看到,虽然session B只需要操作p_2107这个分区,但是由于session A持有整个表t的MDL锁,就导致了session B的alter语句被堵住。
分区表,在做DDL的时候,影响会更大。如果你使用的是普通分表,那么当你在truncate一个分表的时候,肯定不会跟另外一个分表上的查询语句,出现MDL锁冲突。
到这里我们小结一下:
-
MySQL在第一次打开分区表的时候,需要访问所有的分区;
-
在server层,认为这是同一张表,因此所有分区共用同一个MDL锁;
-
在引擎层,认为这是不同的表,因此MDL锁之后的执行过程,会根据分区表规则,只访问必要的分区。
而关于“必要的分区”的判断,就是根据SQL语句中的where条件,结合分区规则来实现的。比如我们上面的例子中,where ftime=‘2018-4-1’,根据分区规则year函数算出来的值是2018,那么就会落在p_2019这个分区。
但是,如果这个where 条件改成 where ftime>=‘2018-4-1’,虽然查询结果相同,但是这时候根据where条件,就要访问p_2019和p_others这两个分区。
如果查询语句的where条件中没有分区key,那就只能访问所有分区了。当然,这并不是分区表的问题。即使是使用业务分表的方式,where条件中没有使用分表的key,也必须访问所有的分表。
分区表的应用场景
分区表的一个显而易见的优势是对业务透明,相对于用户分表来说,使用分区表的业务代码更简洁。还有,分区表可以很方便的清理历史数据。
如果一项业务跑的时间足够长,往往就会有根据时间删除历史数据的需求。这时候,按照时间分区的分区表,就可以直接通过alter table t drop partition …这个语法删掉分区,从而删掉过期的历史数据。
这个alter table t drop partition …操作是直接删除分区文件,效果跟drop普通表类似。与使用delete语句删除数据相比,优势是速度快、对系统影响小。
需要注意的是,文中是以范围分区(range)为例和你介绍的。实际上,MySQL还支持hash分区、list分区等分区方法。你可以在需要用到的时候,再翻翻手册。
实际使用时,分区表跟用户分表比起来,有两个绕不开的问题:一个是第一次访问的时候需要访问所有分区,另一个是共用MDL锁。
因此,如果要使用分区表,就不要创建太多的分区。我见过一个用户做了按天分区策略,然后预先创建了10年的分区。这种情况下,访问分区表的性能自然是不好的。这里有两个问题需要注意:
-
分区并不是越细越好。实际上,单表或者单分区的数据一千万行,只要没有特别大的索引,对于现在的硬件能力来说都已经是小表了。
-
分区也不要提前预留太多,在使用之前预先创建即可。比如,如果是按月分区,每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区,要及时的drop掉。
至于分区表的其他问题,比如查询需要跨多个分区取数据,查询性能就会比较慢,基本上就不是分区表本身的问题,而是数据量的问题或者说是使用方式的问题了。
举例的表中没有用到自增主键,假设现在要创建一个自增字段id。MySQL要求分区表中的主键必须包含分区字段。如果要在表t的基础上做修改,你会怎么定义这个表的主键呢?为什么这么定义呢?
这时候就有两种可选:一种是(ftime, id),另一种是(id, ftime)。
如果从利用率上来看,应该使用(ftime, id)这种模式。因为用ftime做分区key,说明大多数语句都是包含ftime的,使用这种模式,可以利用前缀索引的规则,减少一个索引。
这时的建表语句是:
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`ftime`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = MyISAM,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = MyISAM,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = MyISAM,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = MyISAM);
当然,我的建议是你要尽量使用InnoDB引擎。InnoDB表要求至少有一个索引,以自增字段作为第一个字段,所以需要加一个id的单独索引。
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`ftime`,`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
当然把字段反过来,创建成:
PRIMARY KEY (`id`,`ftime`),
KEY `id` (`ftime`)
也是可以的。
问题
join的写法
问题:
-
如果用left join的话,左边的表一定是驱动表吗?
-
如果两个表的join包含多个条件的等值匹配,是都要写到on里面呢,还是只把一个条件写到on里面,其他条件写到where部分?
为了同时回答这两个问题,我来构造两个表a和b:
create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
表a和b都有两个字段f1和f2,不同的是表a的字段f1上有索引。然后,我往两个表中都插入了6条记录,其中在表a和b中同时存在的数据有4行。
其实就是下面这两种写法的区别:
select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/
我把这两条语句分别记为Q1和Q2。
首先,需要说明的是,这两个left join语句的语义逻辑并不相同。我们先来看一下它们的执行结果。
可以看到:
- 语句Q1返回的数据集是6行,表a中即使没有满足匹配条件的记录,查询结果中也会返回一行,并将表b的各个字段值填成NULL。
- 语句Q2返回的是4行。从逻辑上可以这么理解,最后的两行,由于表b中没有匹配的字段,结果集里面b.f2的值是空,不满足where 部分的条件判断,因此不能作为结果集的一部分。
接下来,我们看看实际执行这两条语句时,MySQL是怎么做的。
我们先一起看看语句Q1的explain结果:
可以看到,这个结果符合我们的预期:
- 驱动表是表a,被驱动表是表b;
- 由于表b的f1字段上没有索引,所以使用的是Block Nexted Loop Join(简称BNL) 算法。
看到BNL算法,你就应该知道这条语句的执行流程其实是这样的:
-
把表a的内容读入join_buffer 中。因为是select * ,所以字段f1和f2都被放入join_buffer了。
-
顺序扫描表b,对于每一行数据,判断join条件(也就是a.f1=b.f1 and a.f2=b.f2)是否满足,满足条件的记录, 作为结果集的一行返回。如果语句中有where子句,需要先判断where部分满足条件后,再返回。
-
表b扫描完成后,对于没有被匹配的表a的行(在这个例子中就是(1,1)、(2,2)这两行),把剩余字段补上NULL,再放入结果集中。
对应的流程图如下:
可以看到,这条语句确实是以表a为驱动表,而且从执行效果看,也和使用straight_join是一样的。
你可能会想,语句Q2的查询结果里面少了最后两行数据,是不是就是把上面流程中的步骤3去掉呢?我们还是先看一下语句Q2的expain结果吧。
可以看到,这条语句是以表b为驱动表的。而如果一条join语句的Extra字段什么都没写的话,就表示使用的是Index Nested-Loop Join(简称NLJ)算法。
因此,语句Q2的执行流程是这样的:顺序扫描表b,每一行用b.f1到表a中去查,匹配到记录后判断a.f2=b.f2是否满足,满足条件的话就作为结果集的一部分返回。
为什么语句Q1和Q2这两个查询的执行流程会差距这么大呢?其实,这是因为优化器基于Q2这个查询的语义做了优化。
在MySQL里,NULL跟任何值执行等值判断和不等值判断的结果,都是NULL。这里包括, select NULL = NULL 的结果,也是返回NULL。
因此,语句Q2里面where a.f2=b.f2就表示,查询结果里面不会包含b.f2是NULL的行,这样这个left join的语义就是“找到这两个表里面,f1、f2对应相同的行。对于表a中存在,而表b中匹配不到的行,就放弃”。
这样,这条语句虽然用的是left join,但是语义跟join是一致的。
因此,优化器就把这条语句的left join改写成了join,然后因为表a的f1上有索引,就把表b作为驱动表,这样就可以用上NLJ 算法。在执行explain之后,你再执行show warnings,就能看到这个改写的结果,如图5所示。
这个例子说明,即使我们在SQL语句中写成left join,执行过程还是有可能不是从左到右连接的。
也就是说,使用left join时,左边的表不一定是驱动表。
这样看来,如果需要left join的语义,就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断,必须都写在on里面。那如果是join语句呢?
这时候,我们再看看这两条语句:
select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/
我们再使用一次看explain 和 show warnings的方法,看看优化器是怎么做的。
可以看到,这两条语句都被改写成:
select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);
执行计划自然也是一模一样的。
也就是说,在这种情况下,join将判断条件是否全部放在on部分就没有区别了。
Simple Nested Loop Join 的性能问题
虽然BNL算法和Simple Nested Loop Join 算法都是要判断M*N次(M和N分别是join的两个表的行数),但是Simple Nested Loop Join 算法的每轮判断都要走全表扫描,因此性能上BNL算法执行起来会快很多。
BNL算法的执行逻辑是:
-
首先,将驱动表的数据全部读入内存join_buffer中,这里join_buffer是无序数组;
-
然后,顺序遍历被驱动表的所有行,每一行数据都跟join_buffer中的数据进行匹配,匹配成功则作为结果集的一部分返回。
Simple Nested Loop Join算法的执行逻辑是:顺序取出驱动表中的每一行数据,到被驱动表去做全表扫描匹配,匹配成功则作为结果集的一部分返回。
Simple Nested Loop Join算法,其实也是把数据读到内存里,然后按照匹配条件进行判断,为什么性能差距会这么大呢?
解释这个问题,需要用到MySQL中索引结构和Buffer Pool的相关知识点:
在对被驱动表做全表扫描的时候,如果数据没有在Buffer Pool中,就需要等待这部分数据从磁盘读入;
从磁盘读入数据到内存中,会影响正常业务的Buffer Pool命中率,而且这个算法天然会对被驱动表的数据做多次访问,更容易将这些数据页放到Buffer Pool的头部。即使被驱动表数据都在内存中,每次查找“下一个记录的操作”,都是类似指针操作。而join_buffer中是数组,遍历的成本更低。
所以说,BNL算法的性能会更好。
distinct 和 group by的性能
问题:如果只需要去重,不需要执行聚合函数,distinct 和group by哪种效率高一些呢?
我来展开一下他的问题:如果表t的字段a上没有索引,那么下面这两条语句:
select a from t group by a order by null;
select distinct a from t;
的性能是不是相同的?
首先需要说明的是,这种group by的写法,并不是SQL标准的写法。标准的group by语句,是需要在select部分加一个聚合函数,比如:
select a,count(*) from t group by a order by null;
这条语句的逻辑是:按照字段a分组,计算每组的a出现的次数。在这个结果里,由于做的是聚合计算,相同的a只出现一次。
没有了count(*)以后,也就是不再需要执行“计算总数”的逻辑时,第一条语句的逻辑就变成是:按照字段a做分组,相同的a的值只返回一行。而这就是distinct的语义,所以不需要执行聚合函数时,distinct 和group by这两条语句的语义和执行流程是相同的,因此执行性能也相同。
这两条语句的执行流程是下面这样的。
创建一个临时表,临时表有一个字段a,并且在这个字段a上创建一个唯一索引;
遍历表t,依次取数据插入临时表中:
- 如果发现唯一键冲突,就跳过;
- 否则插入成功;
遍历完成后,将临时表作为结果集返回给客户端。
备库自增主键问题
在binlog_format=statement时,语句A先获取id=1,然后语句B获取id=2;接着语句B提交,写binlog,然后语句A再写binlog。这时候,如果binlog重放,是不是会发生语句B的id为1,而语句A的id为2的不一致情况呢?
首先,这个问题默认了“自增id的生成顺序,和binlog的写入顺序可能是不同的”,这个理解是正确的。
其次,这个问题限定在statement格式下,也是对的。因为row格式的binlog就没有这个问题了,Write row event里面直接写了每一行的所有字段的值。
而至于为什么不会发生不一致的情况,我们来看一下下面的这个例子。
create table t(id int auto_increment primary key);
insert into t values(null);
可以看到,在insert语句之前,还有一句SET INSERT_ID=1。这条命令的意思是,这个线程里下一次需要用到自增值的时候,不论当前表的自增值是多少,固定用1这个值。
这个SET INSERT_ID语句是固定跟在insert语句之前的,比如@帽子掉了同学提到的场景,主库上语句A的id是1,语句B的id是2,但是写入binlog的顺序先B后A,那么binlog就变成:
SET INSERT_ID=2;
语句B;
SET INSERT_ID=1;
语句A;
你看,在备库上语句B用到的INSERT_ID依然是2,跟主库相同。
因此,即使两个INSERT语句在主备库的执行顺序不同,自增主键字段的值也不会不一致