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

2024-05-13 01:12
文章标签 mysql 问题 内存 脏页 索引 删除 讨论 前缀 count database 干净

本文主要是介绍MySQL前缀索引、脏页和干净页、COUNT(*)讨论、表删除内存问题，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • 如何加索引
      • 如何给身份证号添加索引
  • SQL语句变慢
      • 脏页 (Dirty Pages)
      • 干净页 (Clean Pages)
      • 为何区分脏页和干净页
      • 处理脏页
      • 管理策略
    • flush
      • 如何控制
  • 为什么删除表数据后表文件大小不变
    • 问题背景
    • 核心原因
      • 数据存储方式
      • 参数影响
    • 解决方案
      • 1. 调整`innodb_file_per_table`设置
      • 2. 使用表重建来回收空间
      • 3. 定期优化表
    • 注意事项
    • online和inplace
      • Inplace DDL
      • Online DDL
  • COUNT(*) 讨论
    • `COUNT(*)` 的实现方式
    • InnoDB 的设计和挑战
    • 解决策略
    • 建议
    • 总结

本文为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> selectcount(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语句的响应时间

InnoDB刷脏页速度策略|500

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

问题背景

在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(*) 变得较慢。
  • 在面对实际业务需求时，特别是在数据量大且更新频繁的情况下，推荐采用外部缓存系统来处理计数，以提高性能。

这篇关于MySQL前缀索引、脏页和干净页、COUNT(*)讨论、表删除内存问题的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/984270。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
NameNode内存生产配置

NameNode内存生产配置

Hadoop2.x 系列，配置 NameNode 内存 NameNode 内存默认 2000m ，如果服务器内存 4G ， NameNode 内存可以配置 3g 。在 hadoop-env.sh 文件中配置如下。 HADOOP_NAMENODE_OPTS=-Xmx3072m Hadoop3.x 系列，配置 Nam
阅读更多...
好题——hdu2522（小数问题：求1/n的第一个循环节）

好题——hdu2522（小数问题：求1/n的第一个循环节）

好喜欢这题，第一次做小数问题，一开始真心没思路，然后参考了网上的一些资料。 知识点***********************************无限不循环小数即无理数，不能写作两整数之比*****************************（一开始没想到，小学没学好） 此题1/n肯定是一个有限循环小数，了解这些后就能做此题了。 按照除法的机制，用一个函数表示出来就可以了，代码如下
阅读更多...
hdu1043（八数码问题，广搜 + hash（实现状态压缩） ）

hdu1043（八数码问题，广搜 + hash（实现状态压缩） ）

利用康拓展开将一个排列映射成一个自然数，然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
电脑桌面文件删除了怎么找回来？别急，快速恢复攻略在此

电脑桌面文件删除了怎么找回来？别急，快速恢复攻略在此

在日常使用电脑的过程中，我们经常会遇到这样的情况：一不小心，桌面上的某个重要文件被删除了。这时，大多数人可能会感到惊慌失措，不知所措。 其实，不必过于担心，因为有很多方法可以帮助我们找回被删除的桌面文件。下面，就让我们一起来了解一下这些恢复桌面文件的方法吧。 一、使用撤销操作 如果我们刚刚删除了桌面上的文件，并且还没有进行其他操作，那么可以尝试使用撤销操作来恢复文件。在键盘上同时按下“C
阅读更多...
购买磨轮平衡机时应该注意什么问题和技巧

购买磨轮平衡机时应该注意什么问题和技巧

在购买磨轮平衡机时，您应该注意以下几个关键点： 平衡精度 平衡精度是衡量平衡机性能的核心指标，直接影响到不平衡量的检测与校准的准确性，从而决定磨轮的振动和噪声水平。高精度的平衡机能显著减少振动和噪声，提高磨削加工的精度。 转速范围 宽广的转速范围意味着平衡机能够处理更多种类的磨轮，适应不同的工作条件和规格要求。 振动监测能力 振动监测能力是评估平衡机性能的重要因素。通过传感器实时监
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2