MySQL前缀索引、脏页和干净页、COUNT(*)讨论、表删除内存问题

本文为mysql11-15讲总结

如何加索引

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一个语句代表着以整个字符串作为索引，第二个则是只取前6个字节

第二种由于只取前6个字节所以占用空间更少，但是会增加额外的记录扫描次数

如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；

2. 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com，这行记录丢弃；

3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；

4. 重复上一步，直到在index2上取到的值不是’zhangs’时，循环结束。

使用前缀索引时要定义好长度才能做到节省空间的同时尽可能减少增加的额外查询成本

首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

mysql> select
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如5%。然后，在返回的L4~L7中，找出不小于 L * 95%的值，假设这里L6、L7都满足，你就可以选择前缀长度为6。

使用前缀索引比不上覆盖索引对查询性能的优化，就算将前缀索引的长度设置为字段长度，innodb也是会回到索引再查一遍

如何给身份证号添加索引

1. 很多网页比如说校园卡，会跟你说默认密码为身份证后六位，所以我们也可以把身份证倒过来存储，取六位为前缀索引
2. 使用hash字段：在表上创建一个整数字段，保存身份证的校验码，同时创建索引

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了4个字节，比原来小了很多。

接下来，我们再一起看看 使用倒序存储和使用hash字段这两种方法的异同点。

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样地，hash字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。

2. 在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。

3. 从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

SQL语句变慢

脏页 (Dirty Pages)

• 定义：内存中已被修改但尚未写回磁盘的数据页。
• 重要性：允许数据库延迟磁盘写操作，提高性能。
• 风险：系统崩溃时可能丢失未写回的数据。

干净页 (Clean Pages)

• 定义：内存中的数据页内容与磁盘上的内容完全一致。
• 优点：可以随时从内存中移除而不影响数据完整性。
• 场景：系统无需担心数据丢失即可释放这些数据页。

为何区分脏页和干净页

1. 性能优化：允许数据页在内存中保持脏状态可以显著提高数据库的性能。这是因为磁盘I/O操作通常比内存操作要慢得多。通过减少必须执行的磁盘写操作的次数，系统的整体响应时间和吞吐量可以得到提升。
2. 事务的一致性和恢复：在发生系统崩溃或其他故障时，脏页的存在是关键的，因为它们包含了重要的未持久化的数据。数据库恢复机制（如日志重做）依赖于这些脏页的信息来确保数据的一致性和完整性。
3. 缓冲管理：数据库系统通常包含一个缓冲池管理器，它负责决定何时读取或写入磁盘数据页。通过智能地管理脏页和干净页，缓冲池管理器可以优化数据访问和写回策略，例如使用LRU（最近最少使用）算法来决定哪些页应当被保留在缓存中，哪些应当被写回磁盘。

处理脏页

数据库通常通过一种称为“检查点”（checkpoint）的机制定期将所有脏页写回磁盘。这不仅有助于减少系统崩溃后恢复所需的时间，还可以确保数据的持久性和一致性。

总之，脏页和干净页的概念是数据库缓存管理的核心部分，对于理解和优化数据库的性能和可靠性至关重要。

管理策略

• 检查点：定期将所有脏页数据同步到磁盘，帮助减少数据恢复时间，确保数据一致性。
• 缓冲池管理：有效管理脏页和干净页，优化数据访问和持久化策略。

flush

平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

flush：将脏页写入磁盘，确保持久性和一致性

引发flush的情况：

1. 检查点 (Checkpoint)：数据库定期执行检查点操作，这是一个系统过程，旨在将所有当前的脏页写入磁盘。这样做可以在系统崩溃后加快恢复速度，因为只需重新处理从最后一个检查点之后的事务日志。
2. 事务提交：当事务提交时，为保证数据的持久性，会触发脏页的写入。这确保了一旦事务被标记为成功，它的更改就永久保存了。
3. 脏页的限制：如果脏页的数量达到了数据库系统设定的阈值，系统可能会自动触发flush操作，以防内存中积累过多未写入磁盘的更改。
4. 内存压力：当系统内存不足时，数据库可能需要清空缓冲池中的页，以为新的数据页腾出空间。这种情况下，脏页需要先被flush到磁盘，才能被清出内存。
5. 系统关闭：在数据库正常关闭过程中，为了确保所有暂存于内存中的更改都被持久化，会进行一次全面的flush操作。
6. 手动触发：数据库管理员可以手动触发flush操作，例如通过特定的SQL命令或数据库管理工具，来管理数据库的物理存储情况。

如何控制

需要明确告诉innodb所在主机的io能力：

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

关注脏页比例，不要让他经常接近75%，脏页比例则是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，具体的命令参考下面的代码：

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

innodb_flush_neighbors在MySQL8.0中已经默认为0，这样可以在如果邻数据页同为脏页的情况下不被刷掉，也可以减少sql语句的响应时间

为什么删除表数据后表文件大小不变

问题背景

在MySQL数据库中，尤其是使用InnoDB存储引擎的情况下，用户经常遇到删除表中大量数据后，表文件的物理大小没有相应减小的问题。

核心原因

数据存储方式

• InnoDB存储结构： InnoDB使用B+树索引结构存储数据，数据按页（通常大小为16KB）组织。
• 删除操作的实际效果： 在InnoDB中，删除操作仅标记数据行为删除状态，实际数据并未从磁盘中移除。这意味着物理空间不会立即释放，而是留待将来重用。

参数影响

• innodb_file_per_table:
  • 设置为OFF: 所有表数据存放在共享表空间（ibdata1文件）中，删除数据后空间不会回收到操作系统，而是留在共享空间中待后续使用。
  • 设置为ON: 每个表数据存储在单独的.ibd文件中。虽然便于管理，但删除表内数据或整表后，空间仍然不会自动缩减。

解决方案

1. 调整innodb_file_per_table设置

推荐将innodb_file_per_table设置为ON，以便每个表使用独立的文件，便于管理和优化。

2. 使用表重建来回收空间

• 重建单个表：

  ALTER TABLE your_table_name ENGINE=InnoDB;
  

  这个命令会重建表，期间移除了所有标记为删除的记录的空间，压缩表中未使用的空间。

• 创建新表并复制数据：

  CREATE TABLE new_table LIKE original_table;
  INSERT INTO new_table SELECT * FROM original_table;
  DROP TABLE original_table;
  RENAME TABLE new_table TO original_table;
  

  这种方法通过完全重建数据来优化存储布局和页的利用率。

3. 定期优化表

对于频繁更新的表，定期执行表优化可以提高性能和空间利用率。

OPTIMIZE TABLE your_table_name;

注意事项

• 操作风险： 重建表或优化表是资源密集型操作，可能会导致服务性能下降，特别是在高峰期进行这些操作时。建议在低峰时段进行，并确保有完整的备份。
• 数据安全： 在进行任何结构修改前，确保备份所有重要数据，防止操作失误导致数据丢失。

online和inplace

Inplace DDL

• 定义： Inplace操作指的是在进行表结构变更时，数据不需要被移动到外部结构，而是在原有的表结构上直接进行修改。
• 实现细节： 在InnoDB中，例如执行 ALTER TABLE 操作时，可能会创建一个临时文件（tmp_file），这个文件用于处理数据重组过程。尽管操作在物理上看起来像是在原地进行（即不需要额外的临时表），但实际上仍然需要额外的磁盘空间来存储临时数据。
• 空间要求： 如果服务器的可用磁盘空间不足以创建所需的临时文件，即使是inplace操作也无法完成。

Online DDL

• 定义： Online DDL 允许在表结构变更过程中，表继续对外提供服务，即允许对表进行读写操作。

• 关系与区别： 所有Online DDL操作均是Inplace的，但并非所有Inplace操作都是Online的。例如，添加全文索引的过程虽然是inplace的，但它会阻塞表的写操作，因此不是Online的。

• Inplace but not Online: 例如，添加全文索引（FULLTEXT）或空间索引（SPATIAL），这些操作虽然在物理上不需要移动数据到新表，但会阻塞表的某些操作。

• Online and Inplace: 某些表结构变更如增加新列或者修改某些不影响表数据排列的属性，可以在线进行，同时在物理存储上也是在原地完成。

以下是对您提供的文档内容的总结和笔记：

COUNT(*) 讨论

• 问题讨论：为什么在 MySQL 中使用 SELECT COUNT(*) FROM t 查询表行数会随着数据增多而变慢。
• 焦点：分析 COUNT(*) 的执行机制，特别是在不同的存储引擎（MyISAM 和 InnoDB）中的表现，并探讨应对策略。

COUNT(*) 的实现方式

• MyISAM：存储行数在磁盘上，使得 COUNT(*) 很快，因为直接返回已存储的数值。
• InnoDB：需要逐行读取数据并判断行的可见性（MVCC），因此随着数据量的增加，COUNT(*) 的性能会显著下降。

InnoDB 的设计和挑战

• 多版本并发控制（MVCC）：InnoDB 使用 MVCC 支持高并发，但这使得即使是简单的 COUNT(*) 查询也需要逐行检视，以确定每行是否对查询事务可见。
• 索引优化：虽然 InnoDB 会尝试通过遍历最小的索引树来优化 COUNT(*) 查询，但这仍可能涉及大量数据处理。

解决策略

1. 缓存系统（如 Redis）:
   • 优点：快速读写。
   • 缺点：可能丢失更新，需要定期从数据库同步来确保准确性。
2. 定期同步:
   • 方法：定期从数据库执行 COUNT(*) 并更新缓存。
   • 考量：平衡更新频率和性能需求。

建议

• 对于需要频繁访问数据行数的应用，推荐使用缓存系统维护计数，并结合定期同步策略以防数据丢失。
• 需要权衡实时性和性能，选择适合自己业务场景的数据同步频率。

总结

• 虽然 COUNT(*) 在 MyISAM 中非常快速，但不支持事务和并发控制，而 InnoDB 虽然支持高级功能，却因为其数据一致性和并发控制设计，使得 COUNT(*) 变得较慢。
• 在面对实际业务需求时，特别是在数据量大且更新频繁的情况下，推荐采用外部缓存系统来处理计数，以提高性能。