mysql通过函数对两张表进行操作_Mysql 实战笔记 (三) 实践（2）

五、为什么表数据删除后，表文件大小不变？

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。

表数据

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table 控制的：这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

数据删除流程

InnoDB 引擎只会把要删除的记录标记为删除。如果之后要在这个位置插入一个记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就可以被复用了。但是，数据页的复用跟记录的复用是不同的。

记录的复用，只限于符合范围条件的数据。比如上面的这个例子，ID=400 这条记录被删除后，如果插入一个 ID 是 400 的行，可以直接复用这个空间。

而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

重建表

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

alter table A engine=InnoDB，用来重建表，原理是 Online DDL

重建表的流程：建立一个临时文件，扫描表 A 主键的所有数据页；

用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；

生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件(row log)中，对应的是图中 state2 的状态；

临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；

用临时文件替换表 A 的数据文件。

alter 语句在启动的时候需要获取 MDL(元数据) 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。

Online 和 inplace

在上图中，根据表 A 重建出来的数据是放在“tmp_file”里的，这个临时文件是 InnoDB在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

以，我现在问你，如果你有一个 1TB 的表，现在磁盘间是 1.2TB，能不能做一个 inplace的 DDL 呢？答案是不能。因为，tmp_file 也是要占用临时空间的。

optimize table、analyze table 和 alter table区别

从 MySQL 5.6 版本开始，alter table t engine = InnoDB(也就是 recreate)默认的就是上面图 4 的流程了；

analyze table t其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

optimize table t等于 recreate+analyze。

delete truncate drop 的区别

truncate 删除内容、释放空间但不删除表的结构，可以理解为 delete + create。

drop 删除内容和表的结构，释放空间。

六、count 为什么这么慢？

count(*) 的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高(如果加了where 条件的话，MyISAM 表也是不能返回得这么快的)；

而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？这是因为即使是在同一个时刻的多个查询，由于多版本并发控制(MVCC)的原因，InnoDB 表“应该返回多少行”也是不确定的。 这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

show table status 命令显示的行数row不能直接使用。

自己实现一个count(*)计数

用缓存保存计数: redis

在数据库保存计数

count(*)、count(主键 id)、count(字段) 和 count(1) 区别count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(字段) ：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(*)，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是 null，按行累加。

七、orderby 是怎么工作的？

全字段排序

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

select city,name,age from t where city='杭州' order by name limit 1000

按 name 排序，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size，就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。

但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。外部排序一般使用归并排序算法。将需要排序的数据分成 若干 份，每一份单独排序后存在这些临时文件中。然后把这些个有序文件再合并成一个有序的大文件。

sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

rowid 排序

如果 MySQL 认为排序的单行长度太大会怎么做呢？

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法， 这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序， 把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

可以利用覆盖索引，防止使用排序和临时表

alter table t add index city_user_age(city, name, age);

可以看到，Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。

八、如何正确地显示随机消息？

内存临时表

select word from words order by rand() limit 3;

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。

上面sql的执行流程：创建一个临时表。这个临时表使用的是 memory 引擎， 表里有两个字段，第一个字段是double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。

从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand()函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的R 和 W 字段中，到此，扫描行数是 10000。

现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R 排序。

初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。

从内存临时表中一行一行地取出 R 值和位置信息(rowid)，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。

在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。

排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

慢查询日志为：# Query_time: 0.900376 Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003

SET timestamp=1541402277;

select word from words order by rand() limit 3;

图中的 pos 就是位置信息(rowid)，它表示的是：每个引擎用来唯一标识数据行的信息。对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID。

到这里，稍微小结一下：order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。 如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。磁盘临时表使用的引擎默认是 InnoDB， 是由参数 internal_tmp_disk_storage_engine 控制的。

当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

九、为什么有些SQL语句逻辑相同，性能却差异巨大？

条件字段函数操作

统计发生在所有年份中 7 月份的交易记录总数。

select count(*) from tradelog where month(t_modified)=7;

t_modified 字段上有索引，但是对字段做了函数计算，就用不上索引了。为什么条件是 where t_modified='2018-7-1'的时候可以用上索引，而改成 where month(t_modified)=7 的时候就不行了？对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。改为：select count(*) from tradelog

where (t_modified >= '2016-7-1' and t_modified

or (t_modified >= '2017-7-1' and t_modified

or (t_modified >= '2018-7-1' and t_modified

隐式类型转换

select * from tradelog where tradeid=110717;

tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。对于优化器来说，这个语句相当于:

select * from tradelog where CAST(tradid AS signed int) = 110717;

隐式字符编码转换

字符集不同造成的问题：tradeid不同

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;