Mysql 空间问题及count(*)慢查询问题

空间问题

在Mysql日常使用中，会出现这样的一个问题，我将数据表的内存删除了一半，但是数据表占用的空间并未发生变化。

数据删除流程

假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

现在，你已经知道了InnoDB的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记
录，会怎么样？
答案是，整个数据页就可以被复用了。

数据页的复用跟记录的复用是不同的。

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

而当整个页从B+树里面摘掉以后，可以复用到任何位置。以图1为例，如果将数据页page A上的所有记录删除以后，page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，page A是可以被复用的。

不止是删除数据会造成空洞，插入数据也会

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

解决空间问题的方式：重建表

你可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A
里读出来再插入到表B中。

由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。

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

我给你简单描述一下引入了Online DDL之后，重建表的流程：

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

DDL之前是要拿MDL写锁的，这样还能叫Online DDL吗？

流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。

为什么要退化呢？为了实现Online，MDL读锁不会阻塞增删改操作。

那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。

count(*)

在不同的MySQL引擎中，count(*)有不同的实现方式。

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
• 而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出
  来，然后累积计数。

为什么InnoDB不跟MyISAM一样，也把数字存起来呢？
因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的。

count（）比较

对于count(主键id)来说：Innodb会遍历整张表，取出所有id，server层拿到所有id，进行累加
对于count（1）来说：Innodb会遍历整张表，不取值，server会返回的每行，放一个数字“1”进去，累加。
对于count（字段）来说：Innodb遍历整张表之后，一行行读取该字段的值，判断不为null，进行累加。
**count（*）**是mysql专门做了优化的，不会去每一行取值
运行效率：
count（字段）<count(id)<count(1)约等于count(*)

在高频率数据更新的情况下，我们如何更快的计算出count(*)

1. 用缓存系统保存计数

   Redis服务来保存这个表的总行数。这个表每被插入一行Redis计数就加1，每被删除一行Redis计数就减1。这种方式下，读和更新操作都很快。

   但你再想一下这种方式存在什么问题吗？没错，缓存系统可能会丢失更新。

   将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使Redis正常工作，这个值还是逻辑上不精确的。

   你可以设想一下有这么一个页面，要显示操作记录的总数，同时还要显示最近操作的100条记录。那么，这个页面的逻辑就需要先到Redis里面取出计数，再到数据表里面取数据记录。
   我们是这么定义不精确的：

   1. 一种是，查到的100行结果里面有最新插入记录，而Redis的计数里还没加1；
   2. 另一种是，查到的100行结果里没有最新插入的记录，而Redis的计数里已经加了1。

   这两种情况，都是逻辑不一致的。

2. 在数据库保存计数
   新建一个表用来存储count(*)的计数，及每次插入、删除的操作，与redis不同的是，我们可以利用数据库的事务特性：
   
   T3时刻，由于A事务并未提交，因此对于C表的修改对于B事务来说是不可见的，因此保持了两者数据的一致性。