mysql实战45讲（37-38-39-41-42）

37 内部临时表的使用

1，union使用临时表

union语义：取两个子查询结果的并集。union all语义：取两个子查询的合集，重复的也没关系

union的执行过程中有使用到临时表，因为要去重，union all不需要临时表

2，group by 执行流程

在 Extra 字段里面，我们可以看到三个信息：

Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表；

Using temporary，表示使用了临时表；

Using filesort，表示需要排序。

这个语句的执行流程是这样的：

1）创建内存临时表，表里有两个字段 m 和 c，主键是 m；

2）扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

3）遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

如果你对结果不需要排序的，可以在后面加order by null
select id%10 as m, count(*) as c from t1 group by m order by null;

如果内存不够，无法在内存建临时表，那就会在磁盘上建临时表

group by 因为需要构建临时表，用于记录并且统计结果。如果数据量大的话那样就会很耗性能，

优化方法：

1，使用索引 

group by z(z是索引)，这个排序走索引，就不需要临时表，

2，直接排序

如果碰到排序字段没有索引怎么办，可以在sql中加入SQL_BIG_RESULT，告诉优化器，因为数据量大直接走磁盘临时表，优化器会直接在磁盘建临时表并且用数组的直接排序算法排序。

总结：

1，如果对group by 语句结果没有排序需求，可以在语句后面加order by null

2，尽量让group by走索引，确认方法是explain结果里没有Using temporary和Using filesort

3，如果group by需要统计的数据量不大，尽量只使用内存临时表，调节tmp_table_size参数设置临时表使用内存大小，默认16m

4，如果数据量实在大，使用SQL_BIG_RESULT，直接告诉优化器直接使用排序算法得到结果

 

38，Innodb与Memory引擎，哪个好

1，InnoDB 和 Memory 引擎的数据组织方式的区别

1）Innodb引擎把数据放在主键索引上，其他索引保存的是主键id,这种方式，我们称为索引组织表

2）Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称为堆组织表

2，不同点：

1）当数据文件有空洞的时候，innodb表在插入新数据的时候，为了保证数据的有序性，只能在固定位置写入新值，而内存表找到空位就可以插入新值、

2）innodb表的数据总是有序性存放的，而内存表的数据就是按照写入顺序存放的

3）数据位置发生变化的时候，innodb表只需要修改主键索引，而内存表需要修改所有索引

4）innodb表用主键索引查询需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找，而内存表没这个区别，所有索引的地位都是相同的

5）innodb支持变长数据类型，不同记录的长度是可能不同的，内存表不支持blob和text字段，并且即使定义了varchar，实际也是当作char，也就是固定长度字符串存储。

因此，内存表删了数据之后，空的位置就可以被新的值填入。

建议还是使用innodb表，不要使用内存表，原因：

1）锁粒度问题

内存表是表锁，没有行锁。

2）数据持久性问题

内存表优势是在数据在内存里，快，但是在数据库重启的时候，所有内存表都会被清空

总结：总之还是用innodb表，读写性能还不错，重点是还有专门的redolog数据安全有保障。用于故障恢复，行锁保证锁粒性小，

 

 

39  自增主键

1，自增主键存在哪里

表结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值

MyISAM引擎的自增值保存在数据文件中

InnoDB自增值，其实是保存在了内存中，并且在8版本可以支持持久化。

具体情况是：

1）在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。 举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。 也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。

2）在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

2，自增值修改机制

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

1）如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；

2）如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。

如果 X<Y，那么这个表的自增值不变；

如果 X≥Y，就需要把当前自增值修改为新的自增值。

3，新的自增值生成算法

从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。备注：在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。

当 auto_increment_offset 和 auto_increment_increment 都是 1 的时候，新的自增值生成逻辑很简单，就是：

如果准备插入的值 >= 当前自增值，新的自增值就是“准备插入的值 +1”；否则，自增值不变。

4，自增值为啥会出现不连续

唯一键冲突是导致自增主键 id 不连续的第一种原因。

事务回滚也会产生类似的现象，这就是第二种原因。

自增值回退成本太高，所以放弃回退。

5，优化

自增 id 锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。

1）这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；

2）这个参数的值被设置为 1 时：

普通 insert 语句，自增锁在申请之后就马上释放；

类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

3）这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。

在生产上，尤其是有 insert … select 这种批量插入数据的场景时，从并发插入数据性能的角度考虑，建议设置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 这样做，既能提升并发性，又不会出现数据一致性问题。

批量申请，就是单纯的批量插入时，执行一次插入会申请一个，第二次就会申请2个，第三次就会申请4个，但是实际每次只用了一个id，所以这也是导致不连续的第三种原因。

 

41 快速复制一张表

1,mysqldump方法
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

1）–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

2）–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；

3）–no-create-info 的意思是，不需要导出表结构；

4）–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；

5）–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

（自己试的时候没成功，不知道怎么错了）

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

将临时的t.sql放在db2里去执行

1）打开文件，默认以分号为结尾读取一条条的 SQL 语句；

2）将 SQL 语句发送到服务端执行。

也就是说，服务端执行的并不是这个“source t.sql"语句，而是 INSERT 语句。所以，不论是在慢查询日志（slow log），还是在 binlog，记录的都是这些要被真正执行的 INSERT 语句。

2，导出CSV文件
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

1）这条语句会将结果保存在服务端。如果你执行命令的客户端和 MySQL 服务端不在同一个机器上，客户端机器的临时目录下是不会生成 t.csv 文件的。

2）into outfile 指定了文件的生成位置（/server_tmp/），这个位置必须受参数 secure_file_priv 的限制。参数 secure_file_priv 的可选值和作用分别是：

• 如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；
• 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
• 如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select … into outfile 操作。

3）这条命令不会帮你覆盖文件，因此你需要确保 /server_tmp/t.csv 这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。

4）这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开。

然后得到csv文件后，执行：load data infile '/server_tmp/t.csv' into table db2.t;，将文件复制到db2中

如果 binlog_format=statement，这个 load 语句记录到 binlog 里以后，怎么在备库重放呢？由于 /server_tmp/t.csv 文件只保存在主库所在的主机上，如果只是把这条语句原文写到 binlog 中，在备库执行的时候，备库的本地机器上没有这个文件，就会导致主备同步停止。所以，这条语句执行的完整流程，其实是下面这样的。

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 命令有两种用法：

1）不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；

2）加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程。

3，物理拷贝

假设我们现在的目标是在 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 相同的数据。

这三种方法的优缺点：

1）物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：

• 必须是全表拷贝，不能只拷贝部分数据；
• 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
• 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。

2）用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。

3）用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

 

42 数据库权限

1，创建一个用户：create user 'ua'@'%' identified by 'pa';

这个sql执行动作：

1）磁盘上，往 mysql.user 表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是 N；

2）内存里，往数组 acl_users 里插入一个 acl_user 对象，这个对象的 access 字段值为 0。

2，全局权限

grant all privileges on *.*(库名.表名) to 'ua'@'%' with grant option;

全局权限，作用于整个mysql，保存在mysql.user表中
两个动作：

1）磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；

2）内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。

注意：

1）grant 命令对于全局权限，同时更新了磁盘和内存。命令完成后即时生效，接下来新创建的连接会使用新的权限。

2）对于一个已经存在的连接，它的全局权限不受 grant 命令的影响。

撤销权限：revoke all privileges on *.*(库名.表名) from 'ua'@'%';  也是对表和内存进行修改

3，db权限(库权限)

grant all privileges on db1.*(库名.表名) to 'ua'@'%' with grant option;

两个动作：

1）磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；

2）内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。

注意：修改db权限，是实时生效的，即便是已经连接的线程也会受到影响。

4，表权限和列权限

表权限定义存放在表 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;（列权限）

也是会去修改表和内存

5，flush privileges 

以上权限，可以看出，不管啥粒度的权限，都会去修改表和内存，所以正常情况之后，没有必要跟着执行flush privileges命令

flush privileges 是当数据表的权限数据跟内存中的权限数据不一致的情况下，flush privileges可以用来重建内存数据，使内存与表数据一致。

当然表与内存数据不一致的原因一般就是由于操作不正常的情况才会出现。所以只要规范使用grant是不需要加flush privileges的。