13-17

13 | 为什么表数据删掉一半，表文件大小不变

mysql8.0 之前，表结构以.frm为后缀的文件里。而8.0版本允许表结构定义放在系统数据表中，因为该部分占用空间很小。参数 innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。

OFF，表示表的数据放在系统共享表空间，也就是跟数据字典放在一起。drop table及时表删掉了，空间也不会回收。
ON（5.6.6版本后默认值），表示每个innodb表数据存储在以.ibd为后缀的文件中。drop table系统会直接删除这个文件。

以下内容基于innodb_file_per_table on展开。

假设要删除R4，innodb只会标记R4删除。如果之后插入一个ID在300和600之间的记录时，可能会复用该位置。如果删掉整页，整个数据页可以被复用。所以磁盘文件大小不会缩小。

但记录复用，只能插入符合范围的数据。不能插入300~600范围外的数据。

页的复用，可以插入任何新数据。如pageA数据删除后，可以插入ID=50的数据。

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

如果使用delete命令，那么所有数据页标记为可复用。

插入数据也会产生空洞，如果按索引递增插入，那么索引是紧凑的。如果数据插入随机，可能造成索引数据页分裂。

当某页满时，再插入数据，就会申请一个新页，将旧页的部分数据保存到新页中。所以旧页中可能有空洞。

更新索引，可能理解为删除旧值，插入新值。也会造成空洞。

重建表

重建表，可以新建一个表，将旧表中的数据一行一行读出来插入到新表中。然后以新表替换旧表。可以使用 alter table A engine=InnoDB 命令来重建表。在mysql 5.5版本前，该命令流程与上述流程类似。在此过程中，不能更新旧表数据。

MySQL 5.6 版本开始引入的 Online DDL，对该操作流程做了优化。

建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中state3 的状态；
用临时文件替换表 A 的数据文件。

重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。如果是线上服务，要控制操作时间。如果想要比较安全的操作，推荐使用github开源的gh-ost。

optimize table、analyze table和 alter table 这三种方式重建表的区别。

从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认是上图的流程；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。

14 | count(*)这么慢，我该怎么办？

count(*) 的实现方式

MyISAM 引擎保存总行数，所以count很快。但如果加了where不能很快返回。
Innodb需要一行一行读出来累积计数。

innodb由于多版本并发控制（MVCC）的原因，多个事务count的行数不同，所以不能保存总行数。但count(*)做了优化，引擎会选择最小的普通索引树，来计数。而不是直接统计聚集索引树。

show table status 命令输出TABLE_ROWS 显示这个表当前有多少行，但它也是采样估算来的。官方文档说误差可能达到 40% 到 50%。

用缓存系统保存计数

两个问题：

缓存会丢失
缓存不准确，因为缓存计数和插入数据不是原子操作，有可能在中间过程，其他事务读取了数据。

在数据库保存计数

使用一张表保存计数，由于事务可以解决使用缓存问题。

不同的 count 用法

下面的讨论还是基于 InnoDB 引擎的

count(主键 id) ，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
count(1)，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
count(字段) a. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加； b. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
count()，并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以建议尽量使用 count()。

15 | 答疑文章（一）：日志和索引相关问题

日志相关问题

第2篇文章《日志系统：一条 SQL 更新语句是如何执行的？》，两阶段提交的不同瞬间MySQL 如果发生异常重启，是怎么保证数据完整性的？

redo log

如果redo处理perpare阶段，写binlog之前崩溃（crash），恢复时事务回滚。如果binlog写完了，redo未commit前崩溃（crash）：

如果redo log事务完整，有了commit标识，直接提交；
如果redo log里事务只有完整的perpare，则判断对应事务binlog是否完整： a. 如果是，则提交事务； b. 否则回滚。

追问 1：MySQL 怎么知道 binlog 是完整的?

回答：一个事务的binlog是有完整格式的：

statement 格式的 binlog，最后会有 COMMIT； row 格式的 binlog，最后会有一个 XID event。

mysql 5.6.2版本以后，引入binlog-checksum验证binlog内容是否正确。

追问 2：redo log 和 binlog 是怎么关联起来的？

回答：它们有个共同的数据字段：XID。

追问 3：处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?

回答：因为写入binlog后，会被从库使用，为了保证主备一致性。

追问 4：如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

回答：两阶段提交是经典分布式系统问题，并不是mysql独有的。 innodb，如果redo log提交完成，事务就不能回滚（如果还允许回滚，可能覆盖掉别的事务的更新）。但如果redo log直接提交，binlog写失败时，innodb回滚不了，数据和binlog日志会不一致。两阶段提交就是为了每个“人”都ok，在一起提交。

追问 5：不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？

回答：不可以，历史原因，innodb不是mysql原生引擎，binlog不支持崩溃恢复，所以innodb实现了redo log。

追问 6：那能不能反过来，只用 redo log，不要 binlog

回答：如果从崩溃恢复角度来讲是可以的。但redo log是循环写，历史日志没法保留，而binlog有归档功能。binlog还有可以实现复制主从同步。

追问 7：redo log 一般设置多大？

回答：redo log太小会导致很快写满，然后就会强行刷redo log。如果几个TB硬盘，直接将redo log设置为4个文件，每个文件1G。

追问 8：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

回答：这个问题就是“redo log 里面到底是什么”的问题。 redo log没有记录数据页完整数据，所以它没有能力自己去更新磁盘数据页。

如果再次运行的实例，数据页被修改，跟磁盘数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这过程和redo log毫无关系。在崩溃恢复场景，Innodb如果判断一个数据页可能在崩溃恢复时丢失更新，就会将它读到内存，然后让redo log更新内存内容。更新完成内存也变成脏页，就回到第一种情况。 ps：老师说这个问题很好，我之前学习的时候也想不明白刷盘流程，搜索了好久。

追问 9：redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

回答：在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务往两个表中插记录过程中，生成的日志都要先保存起来，但不能在未commit的时候写到redo log里。所以redo log buffer就是一块内存，用来先存redo日志。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正写redo log文件（文件名是ib_logfile+数字），是在执行commit时做的。单独执行一个更新语句，innodb会自己启动一个事务，过程和上述内容一致。

16 | “order by”是怎么工作的

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;


1. SELECT * FROM t WHERE city='上海' ORDER BY age DESC;
2. SELECT * FROM t WHERE city='上海' ORDER BY age DESC LIMIT 10;
3. SELECT * FROM t WHERE city='上海' ORDER BY age DESC LIMIT 10, 20;
4. SELECT * FROM t WHERE city='上海' ORDER BY age DESC LIMIT 1000000000;
5. SELECT * FROM t WHERE city='上海' ORDER BY age DESC LIMIT 1000000000, 1;

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

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

- id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
- 1,SIMPLE,t,NULL,ALL,city,city,3,const,1000,1000,Using Index condition;Using filesort;

Extra中"Using filesort"表示排序，mysql会给每个线程分配一个块内存（sort_buffer）用来排序。

sql执行过程：

初始化sort_buffer，确定放入name、city、age 这三个字段；
从city索引找到第一个city='杭州’的主键id，图中的ID_X；
根据id去聚集索引取这三个字段，放到sort_buffer；
在从city索引取下一个；
重复3、4查询所有的值；
在sort_buffer按name快速排序；
按照排序结果取前1000行返回给客户端。

如果sort_buffer太小，内存放不下排序的数据，则需要使用外部排序，利用磁盘临时文件辅助排序。这取决于排序所需内存和参数 sort_buffer_size。

下面方法可以确定排序是否使用临时文件：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

通过查看 OPTIMIZER_TRACE，number_of_tmp_files表示排序使用的临时文件数，外部排序一般使用归并排序算法。 rows表示满足city='杭州’有4000条，examined_rows=4000表示4000行参与排序。 sort_mode packed_additional_fields表示排序过程字符串做了“紧凑”处理。name字段定义varchar(16)，排序过程中按照实际长度分配空间。最后一个查询语句 select @b-@a返回结果是 4000，表示只扫描了4000行。

这边老师把internal_tmp_disk_storage_engine 设置成MyISAM，否则，select @b-@a结果为 4001。因为innodb把数据从临时表取出来时，会让Innodb_rows_read 的值加 1。

rowid 排序

如果排序的单行长度太大mysql会使用另一种算法。

SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是 36 > max_length_for_sort_data，所以会使用别的算法。该算法和全字段排序的差别：

sort_buffer只会确定放入name 和 id字段，所以只会取这两个字段。
最后根据name排完序，会根据id字段去原表取city、name 和 age 三个字段返回给客户端。需要注意，不做合并操作，而是直接将原表查到的字段返回给客户端。

和上述过程对比：

examined_rows和rows没有变化，但select @b-@a会变成5000。因为排完序需要去原表再取1000行。

全字段排序 VS rowid 排序

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。假设从city索引上取出来的行天然按照name递增排序，就不需要再进行排序了。所以可以建一个city和name的联合索引：

alter table t add index city_user(city, name);

整个查询流程就变成了：

从索引(city, name)找到第一个city='杭州’的主键id；
到聚集索引取name、city、age三个字段，作为结果集一部分直接返回；
从索引(city, name)取下一个。
重复2、3，直到查到1000条记录，或不满足city='杭州’时结束。

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

- id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
- 1,SIMPLE,t,NULL,ALL,city_user,3,const,1000,1000,Using index condition;

没有"Using filesort"。
使用覆盖索引：

select * from t where city in ('杭州'," 苏州 ") order by name limit 100;

上述sql需要排序，因为name不是递增的。可以将sql拆分成两条，最后通过程序内存取前100条。进一步，如果需要分页，“limit 10000,100”，则可以使用下面的思想：


1. SELECT * FROM t WHERE city='上海' ORDER BY age DESC LIMIT 10000, 100;           // 取第10001-10100行
2. SELECT * FROM t WHERE city='上海' AND age > (SELECT age FROM t WHERE city='上海' ORDER BY age DESC LIMIT 10000, 1) ORDER BY age DESC LIMIT 100; // 取第10001-10100行

选取出 10100 数据，在取出 10000 数据，然后求 10000 - 10100 的差值，作为 age 的上界，再取出 100 条数据。

问题一：这种无条件查列表页除了全表扫还有其他建立索引的办法么

1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。因为优化器认为走二级索引再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序 2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的. 因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。

问题二 : 如果加入 group by ，数据该如何走

如果是group by a,a上不能使用索引的情况,是走rowid排序。如果是group by limit,不能使用索引的情况,是走堆排序如果是只有group by a,a上有索引的情况,又根据选取值不同,索引的扫描方式又有不同 select * from t group by a --走的是索引全扫描,至于这里为什么选择走索引全扫描 select a from t group by a --走的是索引松散扫描,也就说只需要扫描每组的第一行数据即可,不用扫描每一行的值

问题三：老师之后的文章会有讲解 bigInt(20) 、 tinyint(2) 、varchar(32) 这种后面带数字与不带数字有何区别的文章么。每次建字段都会考虑长度，但实际却不知道他有何作用

bigint和int加数字都不影响能存储的值。 bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是 2^32-1。

只是有些前端会根据括号里来截取显示而已。建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。

假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。

17 | 如何正确地显示随机消息

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

内存临时表

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

磁盘临时表

tmp_table_size限制了内存临时表的大小，默认16M。如果内存大于tmp_table_size，则会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB，由参数 internal_tmp_disk_storage_engine 控制。

create temporary table tmp_words select * from words order by rand() limit 3;
select * from tmp_words;

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G