文章目录

• insert语句的锁为什么这么多？
• 怎么最快地复制一张表？
• • mysqldump 方法
  • 导出 CSV 文件
  • 物理拷贝方法
• grant之后要跟着flush privileges吗？
• • 全局权限
  • db 权限
  • 表权限和列权限
  • flush privileges 使用场景
  • 权限的作用范围和修改策略总结
• 要不要使用分区表？
• • 分区表是什么？
  • 分区策略
  • 分区表的 server 层行为
• 自增id用完怎么办？

insert语句的锁为什么这么多？

• 几种特殊情况下的 insert 语句:
  • insert … select 是很常见的在两个表之间拷贝数据的方法。需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
  • 如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
  • insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

怎么最快地复制一张表？

• 先创建一个表 db1.t，并插入 1000 行数据，同时创建一个相同结构的表 db2.t。假设，我们要把 db1.t 里面 a>900 的数据行导出来，插入到 db2.t 中。

mysqldump 方法

• 一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句。你可以使用下面的命令：

mysqldump -h$host -P$port -u$user --add-locks=0 --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 表示生成的文件是在客户端机器上的。
• 如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数–skip-extended-insert。
• 然后，可以通过下面这条命令，将这些 INSERT 语句放到 db2 库里去执行。

	mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

• source 是一个客户端命令。mysql 客户端执行这个命令的流程是这样的：
  • 打开文件，默认以分号为结尾读取一条条的 SQL 语句；
  • 将 SQL 语句发送到服务端执行。

导出 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;

• 这条语句执行的完整流程是下面这样的：
  • 1.主库执行完成后，将 /server_tmp/t.csv 文件的内容直接写到 binlog 文件中。
  • 2.往 binlog 文件中写入语句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE db2.t。
  • 3.把这个 binlog 日志传到备库。
  • 4.备库的 apply 线程在执行这个事务日志时：
    • a. 先将 binlog 中 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中；
    • b. 再执行 load data 语句，往备库的 db2.t 表中插入跟主库相同的数据。
• 执行流程如图所示：
  
• 注意，这里备库执行的 load data 语句里面多了一个“local”。意思是“将执行这条命令的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0 的内容，加载到目标表 db2.t 中”。
• load data 命令有两种用法：
  • 不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；
  • 加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程。

物理拷贝方法

• 在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。
• 假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：
  • 1.执行 create table r like t，创建一个相同表结构的空表；
  • 2.执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
  • 3.执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
  • 4.在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
  • 5.执行 unlock tables，这时候 t.cfg 文件会被删除；
  • 6.执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
• 至此，拷贝表数据的操作就完成了。这个流程的执行过程图如下：
  

三种将一个表的数据导入到另外一个表中的方法。对比一下这三种方法的优缺点：

• 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
  • 必须是全表拷贝，不能只拷贝部分数据；
  • 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
  • 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
• 用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
• 用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。
• 后两种方式都是逻辑备份方式，是可以跨引擎使用的。

grant之后要跟着flush privileges吗？

• 在 MySQL 中，用户权限是有不同的范围的。

全局权限

• 全局权限，作用于整个 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 数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位。

一般在生产环境上要合理控制用户权限的范围。如果一个用户有所有权限，一般就不应该设置为所有 IP 地址都可以访问。

• 如果要回收上面的 grant 语句赋予的权限，可以使用下面这条命令：revoke all privileges on *.* from 'ua'@'%';。 revoke 命令的用法与 grant 类似，做了修改两个动作。

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”。
• grant 操作对于已经存在的连接的影响，在全局权限和基于 db 的权限效果是不同的。
  • 用户 ua 的 super 权限在 T3 时刻已经通过 revoke 语句回收了，但是在 T4 时刻执行 set global 的时候，权限验证还是通过了。这是因为 super 是全局权限，这个权限信息在线程对象中，而 revoke 操作影响不到这个线程对象。
  • 在 T5 时刻去掉 ua 对 db1 库的所有权限后，在 T6 时刻 session B 再操作 db1 库的表，就会报错“权限不足”。这是因为 acl_dbs 是一个全局数组，所有线程判断 db 权限都用这个数组，这样 revoke 操作马上就会影响到 session B。
  • 如果当前会话已经处于某一个 db 里面，之前 use 这个库的时候拿到的库权限会保存在会话变量中。

表权限和列权限

• 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;

• 这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接。

grant之后要跟着flush privileges吗？

• flush privileges 命令会清空 acl_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。
• 如果内存的权限数据和磁盘数据表相同的话，不需要执行 flush privileges。而如果我们都是用 grant/revoke 语句来执行的话，内存和数据表本来就是保持同步更新的。
• 因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。

flush privileges 使用场景

• 当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。这种不一致往往是由不规范的操作导致的，比如直接用 DML 语句操作系统权限表。

权限的作用范围和修改策略总结

要不要使用分区表？

分区表是什么？

• 这个分区表包含了一个.frm 文件和 4 个.ibd 文件，每个分区对应一个.ibd 文件。也就是说：
  • 对于引擎层来说，这是 4 个表；
  • 对于 Server 层来说，这是 1 个表。
• 分区表和手工分表，一个是由 server 层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。

分区策略

• MyISAM 分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由 server 层控制。通用分区策略，是 MySQL 一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。MySQL 从 5.7.17 开始，将 MyISAM 分区表标记为即将弃用 (deprecated)。
• 从 MySQL 5.7.9 开始，InnoDB 引擎引入了本地分区策略（native partitioning）。这个策略是在 InnoDB 内部自己管理打开分区的行为。
• 从 MySQL 8.0 版本开始，就不允许创建 MyISAM 分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。

分区表的 server 层行为

• MySQL 在第一次打开分区表的时候，需要访问所有的分区；
• 在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；
• 在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

自增id用完怎么办？

• 表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。
• Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。
• InnoDB 数据可见性的核心思想是：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。
• 每种自增 id 有各自的应用场景，在达到上限后的表现也不同：
  • 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
  • row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
  • Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
  • InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
  • thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。

你知道的越多，你不知道的越多。