InnoDB 引擎谈论
一个 InnoDB 表包含两部分，即：表构造定义和数据。在 MySQL 8.0 版本以前，表构造是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经许可把表构造定义放在系统数据表中了。
1.1 表构造和表数据如何存放？
表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 掌握的：
1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也便是跟数据字典放在一起；
2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。表构造和表数据分开存放。
从 MySQL 5.6.6 版本开始，它的默认值便是 ON 了。
将 innodb_file_per_table 设置为 ON，是推举做法，我们接下来的谈论都是基于这个设置展开的
我们在删除全体表的时候，可以利用 drop table 命令回收表空间。但是，我们碰着的更多的删除数据的场景是删除某些行，这时就碰着了问题：表中的数据被删除了，但是表空间却没有被回收。
1.2 数据删除流程
假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。
InnoDB 的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，全体数据页就可以被复用了。
1.2.1 数据页的复用跟记录的复用是不同的
1 记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4 这条记录被删除后，如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了。
2 当全体页从 B+ 树里面摘掉往后，可以复用到任何位置。如果将数据页 page A 上的所有记录删除往后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录须要利用新页的时候，page A 是可以被复用的。
如果我们用 delete 命令把全体表的数据删除呢？结果便是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。
delete 命令实在只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也便是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被利用的空间，看起来就像是“空洞”。
1.2.2 删除，插入，更新索引值都会造成空洞
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。
假设图 1 中 page A 已经满了，这时我要再插入一行数据，会若何呢？
可以看到，由于 page A 满了，再插入一个 ID 是 550 的数据时，就不得不再申请一个新的页面 page B 来保存数据了。页分裂完成后，page A 的末端就留下了空洞（把稳：实际上，可能不止 1 个记录的位置是空洞）。其余，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难明得，这也是会造成空洞的。
造成空洞缘故原由：插入，删除，更新索引上的值。经由大量增编削的表，都是可能是存在空洞的。以是，如果能够把这些空洞去掉，就能达到紧缩表空间的目的。而重修表，就可以达到这样的目的。
1.3 重修表
如果你现在有一个表 A，须要做空间紧缩，为了把表中存在的空洞去掉，你可以怎么做呢？
笨办法：新建一个与表 A 构造相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。由于表 B 是新建的表，以是表 A 主键索引上的空洞，在表 B 中就都不存在了。用表 B 更换 A，从效果上看，就起到了紧缩表 A 空间的浸染
利用 alter table A engine=InnoDB 命令来重修表。在 MySQL 5.5 版本之前，这个命令的实行流程跟我们前面描述的差不多，差异只是这个临时表 B 不须要你自己创建，MySQL 会自动完成转存数据、交流表名、删除旧表的操作。
图3
显然，花韶光最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在全体 DDL 过程中，表 A 中不能有更新。也便是说，这个 DDL 不是 Online 的。
而在MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。
我给你大略描述一下引入了 Online DDL 之后，重修表的流程：
图4 Online DDL
1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录天生 B+ 树，存储到临时文件中；
3. 天生临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4. 临时文件天生后，将日志文件中的操作运用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的便是图中 state3 的状态；
5. 用临时文件更换表 A 的数据文件。
特殊把稳：重修方法都会扫描原表数据和构建临时文件。对付很大的表来说，这个操作是很花费 IO 和 CPU 资源的。因此，如果是线上做事，你要很小心地掌握操作韶光。如果想要比较安全的操作的话，我推举你利用 GitHub 开源的 gh-ost 来做。https://www.cnblogs.com/zhoujinyi/p/9187421.html
1.4 Online 和 inplace
在图 3 中，我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表，是在 server 层创建的。在图 4 中，根据表 A 重修出来的数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在内部创建出来的。全体 DDL 过程都在 InnoDB 内部完成。对付 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这便是“inplace”名称的来源。
如果你有一个 1TB 的表，现在磁盘间是 1.2TB，能不能做一个 inplace 的 DDL 呢？
答案是不能。由于，tmp_file 也是要占用临时空间的。
我们重修表的这个语句 alter table t engine=InnoDB，实在隐含的意思是：
alter table t engine=innodb,ALGORITHM=inplace;
跟 inplace 对应的便是拷贝表的办法了，用法是：
alter table t engine=innodb,ALGORITHM=copy;
当你利用 ALGORITHM=copy 的时候，表示的是逼迫拷贝表，对应的流程便是图 3 的操作过程。
inplace 跟 Online 是不是便是一个意思？实在不是的，只是在重修表这个逻辑中刚好是这样而已。
比如，如果我要给 InnoDB 表的一个字段加全文索引，写法是：alter table t add FULLTEXT(field_name);这个过程是 inplace 的，但会壅塞增编削操作，是非 Online 的。
如果说这两个逻辑之间的关系是什么的话，可以概括为：
1. DDL 过程如果是 Online 的，就一定是 inplace 的；
2. 反过来未必，也便是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情形。
利用 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 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重修表，才能达到表文件变小的目的。我跟你先容了重修表的两种实现办法，Online DDL 的办法是可以考虑在业务低峰期利用的，而 MySQL 5.5 及之前的版本，这个命令是会壅塞 DML 的，这个你须要特殊小心。
Truncate 会开释表空间吗？
Truncate 可以理解为drop+create
如果将 alter 操作显式的放到事务里 ，事务不提交 ， 另一个事务查询的时候会查询到alter 操作后的表构造 ， 比如新增了一个字段。这个是什么缘故原由 ，是否冲破了 mvcc 的定义呢？
好问题 ， 不过alter table 语句会默认提交前面的事务，然后自己独立实行、
optimize table t 即是 recreate+analyze
老师请教个问题recreate出来该当是险些全新的，analyze的必要性？
作者回答: 好问题，这个得是比较极度的情形下才有必要，以是我比较喜好直接用alter
1.5 思考题
假设现在有人碰到了一个“想要紧缩表空间，结果揠苗助长”的情形，看上去是这样的：
1. 一个表 t 文件大小为 1TB；
2. 对这个表实行 alter table t engine=InnoDB；
3. 创造实行完成后，空间不仅没变小，还轻微大了一点儿，比如变成了 1.01TB。
（1）这个表，本身就已经没有空洞的了，比如说刚刚做过一次重修表操作。在 DDL 期间，如果刚好有外部的 DML 在实行，这期间可能会引入一些新的空洞。
（2）在重修表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也便是说，实在重修表之后不是“最”紧凑的。
如果是这么一个过程：
1. 将表 t 重修一次；
2. 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；
3. 这种情形下，再重修一次表 t，就可能会涌现问题中的征象。
2 count()这么慢，我该怎么办？
2.1 count() 的实现办法
在不同的 MySQL 引擎中，count() 有不同的实现办法。
MyISAM 引擎：把一个表的总行数存在了磁盘上，因此实行 count() 的时候会直接返回这个数，效率很高。如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的
InnoDB 引擎：，它实行 count() 的时候，须要把数据一行一行地从引擎里面读出来，然后累积计数。
为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？
由于纵然是在同一个时候的多个查询，由于多版本并发掌握（MVCC）的缘故原由，InnoDB 表“该当返回多少行”也是不愿定的。
假设表 t 中现在有 10000 条记录，我们设计了三个用户并行的会话。
会话 A 先启动事务并查询一次表的总行数；
会话 B 启动事务，插入一行后记录后，查询表的总行数；
会话 C 先启动一个单独的语句，插入一行记录后，查询表的总行数。
我们假设从上到下是按照韶光顺序实行的，同一行语句是在同一时候实行的。
图 1 会话 A、B、C 的实行流程
在末了一个时候，三个会话 A、B、C 会同时查询表 t 的总行数，但拿到的结果却不同。
这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上便是通过多版本并发掌握，也便是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对付 count() 要求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于打算“基于这个查询”的表的总行数。
MySQL，在实行 count() 操作的时候还是做了优化的。InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。以是，普通索引树比主键索引树小很多。因此，MySQL 优化器会找到最小的那棵树来遍历。在担保逻辑精确的条件下，只管即便减少扫描的数据量，是数据库系统设计的通用法则之一。
show table status 这个命令的输出结果里面也有一个 TABLE_ROWS 用于显示这个表当前有多少行，这个命令实行挺快的，那这个 TABLE_ROWS 能代替 count() 吗？
索引统计的值是通过采样来估算的。TABLE_ROWS 便是从这个采样估算得来的，官方文档说偏差可能达到 40% 到 50%。以是，show table status 命令显示的行数也不能直策应用。
小结一下：
MyISAM 表虽然 count() 很快，但是不支持事务；show table status 命令虽然返回很快，但是不准确；InnoDB 表直接 count() 会遍历全表，虽然结果准确，但会导致性能问题。如果你现在有一个页面常常要显示交易系统的操作记录总数，到底该当怎么办呢？答案是，我们只能自己计数。
2.2 用缓存系统保存计数
对付更新很频繁的库来说，你可能会第一韶光想到，用缓存系统来支持。
你可以用一个 Redis 做事来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1。这种办法下，读和更新操作都很快，但你再想一下这种办法存在什么问题吗？
没错，缓存系统可能会丢失更新。但实际上，将计数保存在缓存系统中的办法，还不但是丢失更新的问题。纵然 Redis 正常事情，这个值还是逻辑上禁绝确的。
设想一下有这么一个页面，要显示操作记录的总数，同时还要显示最近操作的 100 条记录。那么，这个页面的逻辑就须要先到 Redis 里面取出计数，再到数据表里面取数据记录。
在 T3 时候会话 B 来查询的时候，会显示出新插入的 R 这个记录，但是 Redis 的计数还没加 1。这时候，就会涌现我们说的数据不一致。
会话 B 在 T3 时候查询的时候，Redis 计数加了 1 了，但还查不到新插入的 R 这一行，也是数据不一致的情形。
并发系统里面，我们是无法精确掌握不同线程的实行时候的，由于存在图中的这种操作序列，以是，我们说纵然 Redis 正常事情，这个计数值还是逻辑上禁绝确的。
2.3 在数据库保存计数
如果我们把这个计数直接放到数据库里单独的一张计数表 C 中，又会怎么样呢？首先，这办理了崩溃丢失的问题，InnoDB 是支持崩溃规复不丢数据的。
现在我们就利用“事务”这个特性，把问题办理掉。
虽然会话 B 的读操作仍旧是在 T3 实行的，但是由于这时候更新事务还没有提交，以是计数值加 1 这个操为难刁难会话 B 还不可见。因此，会话 B 看到的结果里， 查计数值和“最近 100 条记录”看到的结果，逻辑上便是同等的。
2.4 不同的 count 用法
在 select count(?) from t 这样的查询语句里面，count()、count(主键 id)、count(字段) 和 count(1) 平分歧用法的性能，
这count() 是一个聚合函数，对付返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。末了返回累计值。
以是，count()、count(主键 id) 和 count(1) 都表示返回知足条件的结果集的总行数；而 count(字段），则表示返回知足条件的数据行里面，参数“字段”不为 NULL 的总个数。
剖析性能差别的时候，你可以记住这么几个原则：
1. server 层要什么就给什么；
2. InnoDB 只给必要的值；
3. 现在的优化器只优化了 count() 的语义为“取行数”，其他“显而易见”的优化并没有做。
（1）对付 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。count(id)可能会选择最小的索引来遍历
（2）对付 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对付返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
单看这两个用法的差别的话，你能比拟出来，count(1) 实行得要比 count(主键 id) 快。由于从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
（3）对付 count(字段) 来说：
1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
2. 如果这个“字段”定义许可为 null，那么实行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。count(字段)的话，如果字段上没有索引，就只能选主键索引
（4）但是 count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。
以是结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，以是我建议你，只管即便利用 count()。
小结
在不同引擎中 count() 的实现办法是不一样的，也剖析了用缓存系统来存储计数值存在的问题。实在，把计数放在 Redis 里面，不能够担保计数和 MySQL 表里的数据精确同等的缘故原由，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确同等的视图。而把计数值也放在 MySQL 中，就办理了同等性视图的问题。InnoDB 引擎支持事务，我们利用好事务的原子性和隔离性，就可以简化在业务开拓时的逻辑。这也是 InnoDB 引擎备受青睐的缘故原由之一。2.5 思考题
在谈论的方案中，我们用了事务来确保计数准确。由于事务可以担保中间结果不被别的事务读到，因此修正计数值和插入新记录的顺序是不影响逻辑结果的。但是，从并发系统性能的角度考虑，你以为在这个事务序列里，该当先插入操作记录，还是该当先更新计数表呢？
由于更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。
