草庐IT

mysql - 为什么从 VARCHAR 列中剥离一些数据后，MySQL MyISAM 表的大小相同？

coder 2023-10-06 原文

我需要减少 MySQL 数据库的大小。我重新编码了一些带有“;”条纹的信息和来自 sources 列的 ':'(字符减少约 10%)。这样做之后，表格的大小与之前完全一样。这怎么可能？我正在使用 MyISAM 引擎。

顺便说一句:不幸的是，我无法使用 myisampack 压缩表格。

mysql> INSERT INTO test SELECT protid1, protid2, CS, REPLACE(REPLACE(sources, ':', ''), ';', '') FROM homologs_9606; 
Query OK, 41917131 rows affected (4 min 11.30 sec)
Records: 41917131  Duplicates: 0  Warnings: 0

mysql> select TABLE_NAME name, ROUND(TABLE_ROWS/1e6, 3) 'million rows', ROUND(DATA_LENGTH/power(2,30), 3) 'data GB', ROUND(INDEX_LENGTH/power(2,30), 3) 'index GB' from information_schema.TABLES WHERE TABLE_NAME IN ('homologs_9606', 'test') ORDER BY TABLE_ROWS DESC LIMIT 10;
+---------------+--------------+---------+----------+
| name          | million rows | data GB | index GB |
+---------------+--------------+---------+----------+
| test          |       41.917 |   0.857 |    1.075 |
| homologs_9606 |       41.917 |   0.887 |    1.075 |
+---------------+--------------+---------+----------+
2 rows in set (0.01 sec)

mysql> select * from homologs_9606 limit 10;
+---------+---------+-------+--------------------------------+
| protid1 | protid2 | CS    | sources                        |
+---------+---------+-------+--------------------------------+
| 5635338 | 1028608 | 0.000 | 10:,1                          |
| 5644385 | 1028611 | 0.947 | 5:1,1;8:0.943,35;10:1,1;11:1,1 |
| 5652325 | 1028611 | 0.947 | 5:1,1;8:0.943,35;10:1,1;11:1,1 |
| 5641128 | 1028612 | 1.000 | 8:1,10                         |
| 5636414 | 1028616 | 0.038 | 8:0.038,104;10:,1              |
| 5636557 | 1028616 | 0.000 | 8:,4                           |
| 5637419 | 1028616 | 0.011 | 5:,1;8:0.011,91;10:,1          |
| 5641196 | 1028616 | 0.080 | 5:1,1;8:0.074,94;10:,1;11:,4   |
| 5642914 | 1028616 | 0.000 | 8:,3                           |
| 5643778 | 1028616 | 0.056 | 8:0.057,70;10:,1               |
+---------+---------+-------+--------------------------------+
10 rows in set (4.55 sec)

mysql> select * from test limit 10;
+---------+---------+-------+-------------------------+
| protid1 | protid2 | CS    | sources                 |
+---------+---------+-------+-------------------------+
| 5635338 | 1028608 | 0.000 | 10,1                    |
| 5644385 | 1028611 | 0.947 | 51,180.943,35101,1111,1 |
| 5652325 | 1028611 | 0.947 | 51,180.943,35101,1111,1 |
| 5641128 | 1028612 | 1.000 | 81,10                   |
| 5636414 | 1028616 | 0.038 | 80.038,10410,1          |
| 5636557 | 1028616 | 0.000 | 8,4                     |
| 5637419 | 1028616 | 0.011 | 5,180.011,9110,1        |
| 5641196 | 1028616 | 0.080 | 51,180.074,9410,111,4   |
| 5642914 | 1028616 | 0.000 | 8,3                     |
| 5643778 | 1028616 | 0.056 | 80.057,7010,1           |
+---------+---------+-------+-------------------------+
10 rows in set (0.00 sec)

mysql> describe test;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| protid1 | int(10) unsigned | YES  | PRI | NULL    |       |
| protid2 | int(10) unsigned | YES  | PRI | NULL    |       |
| CS      | float(4,3)       | YES  |     | NULL    |       |
| sources | varchar(100)     | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> describe homologs_9606;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| protid1 | int(10) unsigned | NO   | PRI | 0       |       |
| protid2 | int(10) unsigned | NO   | PRI | 0       |       |
| CS      | float(4,3)       | YES  |     | NULL    |       |
| sources | varchar(100)     | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

EDIT1:添加平均列长度。 

mysql> select AVG(LENGTH(sources)) from test; 
+----------------------+
| AVG(LENGTH(sources)) |
+----------------------+
|               5.2177 |
+----------------------+
1 row in set (10.04 sec)

mysql> select AVG(LENGTH(sources)) from homologs_9606; 
+----------------------+
| AVG(LENGTH(sources)) |
+----------------------+
|               6.8792 |
+----------------------+
1 row in set (9.95 sec)

EDIT2:通过为所有列设置 NOT NULL，我能够剥离更多 MB。 

mysql> drop table test
Query OK, 0 rows affected (0.42 sec)

mysql> CREATE table test (protid1 INT UNSIGNED NOT NULL DEFAULT '0', protid2 INT UNSIGNED NOT NULL DEFAULT '0', CS FLOAT(4,3) NOT NULL DEFAULT '0', sources VARCHAR(100) NOT NULL DEFAULT '0', PRIMARY KEY (protid1, protid2), KEY `idx_protid2` (protid2)) ENGINE=MyISAM CHARSET=ascii;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test SELECT protid1, protid2, CS, REPLACE(REPLACE(sources, ':', ''), ';', '') FROM homologs_9606; 
Query OK, 41917131 rows affected (2 min 7.84 sec)

mysql> select TABLE_NAME name, ROUND(TABLE_ROWS/1e6, 3) 'million rows', ROUND(DATA_LENGTH/power(2,30), 3) 'data GB', ROUND(INDEX_LENGTH/power(2,30), 3) 'index GB' from information_schema.TABLES WHERE TABLE_NAME IN ('homologs_9606', 'test');
Records: 41917131  Duplicates: 0  Warnings: 0

+---------------+--------------+---------+----------+
| name          | million rows | data GB | index GB |
+---------------+--------------+---------+----------+
| homologs_9606 |       41.917 |   0.887 |    1.075 |
| test          |       41.917 |   0.842 |    1.075 |
+---------------+--------------+---------+----------+
2 rows in set (0.02 sec)

最佳答案

它们并不完全相同。您的查询清楚地表明 test 比 homologs_9606 小大约 30 MB:

+---------------+--------------+---------+
| name          | million rows | data GB |
+---------------+--------------+---------+
| test          |       41.917 |   0.857 | <-- 0.857 < 0.887
| homologs_9606 |       41.917 |   0.887 |
+---------------+--------------+---------+

我们应该为您的 table 准备多少存储空间？让我们检查Data Type Storage Requirements :

INTEGER(10): 4 bytes
FLOAT(4): 4 bytes
VARCHAR(100): L+1

其中 L 是字符字节数，通常每个字符一个字节，但如果您使用 Unicode 字符集，有时会更多。

您的行平均需要:

INTEGER + INTEGER + FLOAT + VARCHAR =
4 + 4 + 4 + (L + 1) = L + 13 bytes

我们可以将您的原始平均 L 推断为 (0.887*1024^3/41917131) - 13 = 9.72。你说你从 sources 中剥离了 10%，这意味着你的新 L 是 9.72*0.9 = 8.75。这给出了预期的新总存储要求 ((8.75 + 13) * 41917131)/1024^3 = 0.849 GB

我怀疑差异(在 0.849 和 0.857 之间)可能是由于 test 有两列设置为 NULLable 而 homologs_9606 没有，但我do not know enough关于 MyISAM 引擎来准确计算这个。不过我能猜到!至少每行每列需要 1 位来存储 NULL 状态，在您的情况下这意味着每行两位或 2*41917131 = 83834262 位 = 10 479 283 字节 = 0.010 GB。总 0.849+0.010 = 0.859 略高于目标(大约多出 2 MB)。但我做了一些四舍五入，你的 10% 的数字也是一个估计值，所以我确信其余部分在翻译中丢失了。

另一个原因可能是如果你在 test 中的 sources 上使用 Unicode 字符集，在这种情况下，某些字符可能每个使用一个以上的字节，但由于 NULLable列似乎说明了我认为您的表不是这种情况的所有内容。

总结

  • 您的两个表大小不同，它们相差 30 MB。
  • 新表的大小与预期大小相近。
  • 通过将 protid1 和 protid2 设置为 NOT NULL 列，您可以在新表中节省更多空间。

关于mysql - 为什么从 VARCHAR 列中剥离一些数据后，MySQL MyISAM 表的大小相同？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34412512/

VARCHARMyISAMcodeprotid39mysqlsize-reduction

有关mysql - 为什么从 VARCHAR 列中剥离一些数据后，MySQL MyISAM 表的大小相同？的更多相关文章

  1. ruby - 为什么我可以在 Ruby 中使用 Object#send 访问私有(private)/ protected 方法？ - 2

    类classAprivatedeffooputs:fooendpublicdefbarputs:barendprivatedefzimputs:zimendprotecteddefdibputs:dibendendA的实例a=A.new测试a.foorescueputs:faila.barrescueputs:faila.zimrescueputs:faila.dibrescueputs:faila.gazrescueputs:fail测试输出failbarfailfailfail.发送测试[:foo,:bar,:zim,:dib,:gaz].each{|m|a.send(m)resc

  2. ruby-on-rails - Rails - 子类化模型的设计模式是什么？ - 2

    我有一个模型:classItem项目有一个属性“商店”基于存储的值，我希望Item对象对特定方法具有不同的行为。Rails中是否有针对此的通用设计模式？如果方法中没有大的if-else语句，这是如何干净利落地完成的？ 最佳答案 通常通过Single-TableInheritance. 关于ruby-on-rails-Rails-子类化模型的设计模式是什么？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.co

  3. ruby - 什么是填充的 Base64 编码字符串以及如何在 ruby​​ 中生成它们？ - 2

    我正在使用的第三方API的文档状态:"[O]urAPIonlyacceptspaddedBase64encodedstrings."什么是“填充的Base64编码字符串”以及如何在Ruby中生成它们。下面的代码是我第一次尝试创建转换为Base64的JSON格式数据。xa=Base64.encode64(a.to_json) 最佳答案 他们说的padding其实就是Base64本身的一部分。它是末尾的“=”和“==”。Base64将3个字节的数据包编码为4个编码字符。所以如果你的输入数据有长度n和n%3=1=>"=="末尾用于填充n%

  4. ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2

    我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i

  5. ruby - 为什么 4.1%2 使用 Ruby 返回 0.0999999999999996？但是 4.2%2==0.2 - 2

    为什么4.1%2返回0.0999999999999996？但是4.2%2==0.2。 最佳答案 参见此处:WhatEveryProgrammerShouldKnowAboutFloating-PointArithmetic实数是无限的。计算机使用的位数有限(今天是32位、64位)。因此计算机进行的浮点运算不能代表所有的实数。0.1是这些数字之一。请注意，这不是与Ruby相关的问题，而是与所有编程语言相关的问题，因为它来自计算机表示实数的方式。 关于ruby-为什么4.1%2使用Ruby返

  6. ruby - ruby 中的 TOPLEVEL_BINDING 是什么？ - 2

    它不等于主线程的binding，这个toplevel作用域是什么？此作用域与主线程中的binding有何不同？>ruby-e'putsTOPLEVEL_BINDING===binding'false 最佳答案 事实是，TOPLEVEL_BINDING始终引用Binding的预定义全局实例，而Kernel#binding创建的新实例>Binding每次封装当前执行上下文。在顶层，它们都包含相同的绑定(bind)，但它们不是同一个对象，您无法使用==或===测试它们的绑定(bind)相等性。putsTOPLEVEL_BINDINGput

  7. ruby - Infinity 和 NaN 的类型是什么？ - 2

    我可以得到Infinity和NaNn=9.0/0#=>Infinityn.class#=>Floatm=0/0.0#=>NaNm.class#=>Float但是当我想直接访问Infinity或NaN时:Infinity#=>uninitializedconstantInfinity(NameError)NaN#=>uninitializedconstantNaN(NameError)什么是Infinity和NaN？它们是对象、关键字还是其他东西？ 最佳答案 您看到打印为Infinity和NaN的只是Float类的两个特殊实例的字符串

  8. ruby-on-rails - 如果 Object::try 被发送到一个 nil 对象，为什么它会起作用？ - 2

    如果您尝试在Ruby中的nil对象上调用方法，则会出现NoMethodError异常并显示消息:"undefinedmethod‘...’fornil:NilClass"然而，有一个tryRails中的方法，如果它被发送到一个nil对象，它只返回nil:require'rubygems'require'active_support/all'nil.try(:nonexisting_method)#noNoMethodErrorexceptionanymore那么try如何在内部工作以防止该异常？ 最佳答案 像Ruby中的所有其他对象

  9. ruby - 为什么 SecureRandom.uuid 创建一个唯一的字符串？ - 2

    关闭。这个问题需要detailsorclarity.它目前不接受答案。想改进这个问题吗？通过editingthispost添加细节并澄清问题.关闭8年前。Improvethisquestion为什么SecureRandom.uuid创建一个唯一的字符串？SecureRandom.uuid#=>"35cb4e30-54e1-49f9-b5ce-4134799eb2c0"SecureRandom.uuid方法创建的字符串从不重复？

  10. ruby - Nokogiri 剥离所有属性 - 2

    我有这个html标记:我想得到这个:我如何使用Nokogiri做到这一点？ 最佳答案 require'nokogiri'doc=Nokogiri::HTML('')您可以通过xpath删除所有属性:doc.xpath('//@*').remove或者，如果您需要做一些更复杂的事情，有时使用以下方法遍历所有元素会更容易:doc.traversedo|node|node.keys.eachdo|attribute|node.deleteattributeendend 关于ruby-Nokog

随机推荐