Googlebot's questions

我将数百万个 XML 文件（每个 1-100MB）归档到一个表中，其结构为

CREATE TABLE Data
(
    ID int(11) unsigned NOT NULL,
    XML longtext COMPRESSED,
    PRIMARY KEY(ID)
) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO Data (ID,XML) VALUES ($id,LOAD_FILE('file.xml'));

该过程很慢，大约每秒 2-5 次插入。整个数据库对于 SDD 驱动器来说太大了，我在单独的 HDD 上创建数据库，但我将文件批量移动到 SDD 驱动器以使读取速度更快。请注意，磁盘速度不是决定速率的步骤，因为 XML 数据会因压缩而大幅缩小。

我尝试InnoDB获得并发插入，但是InnoDB ibd的大小是ARIA/MyISAM的三倍，并且InnoDB在HDD上慢得多。

我尝试过ROCKSDB，但无法在单独的磁盘上创建它，因为所有表都有一个目录。另外，ROCKSDB 的内存管理对于这种情况来说非常糟糕（或者我找不到正确的配置）。

我没有尝试ARCHIVE引擎性能，因为它需要ID井然有序。

我当前的解决方案是同时 INSERT 到 SSD 上的临时 InnoDB 表，然后INSERT INTO SELECT从 InnoDB 表到 HDD 上的 ARIA 表。问题在于清空 InnoDB 和启动并发 INSERT 进程的完整性和延迟。

我很感激任何可能的解决方案。

对于具有 5 亿行的 InnoDB 表（在单独的 NVMe 驱动器上），SELECT COUNT(*)大约需要 3 分钟。

SHOW ENGINE INNODB STATUS\G显示ROW OPERATIONS大约 2M reads/s，这与查询花费的时间一致。

它还显示FILE I/O大约 3,000 次读取/秒。这与 read from 类似iostat，也显示了大约 50MB/s 的读取速度。

NVMe 具有更多从磁盘读取数据的能力。

我想知道这里的瓶颈是什么？它仍然是I/O，还是 MySQL 处理？

例子

我做了一个基本的可重复测试。

CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
Number int(11) unsigned NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB

INSERT INTO test (Number) SELECT * FROM seq_1_to_500000000;
Query OK, 500000000 rows affected (20 min 2.846 sec)
Records: 500000000  Duplicates: 0  Warnings: 0

SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*)  |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 20.234 sec)

重启后MySQL

innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
query_cache_type=0
query_cache_size=0

在没有任何其他活动的情况下，我得到了

SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*)  |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 13.245 sec)

关键问题：在典型的 NVMe 上运行此查询是最快的吗？

配置：

• 50GB 缓冲池。该ibd文件为 17.7GB。
• CPU 为 16/32 核/线程。
• innodb_io_threads没有效果。我尝试了 4（默认）和 64（最大）。

和

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+-----------------------------------------+-------------+
| Variable_name                           | Value       |
+-----------------------------------------+-------------+
| Innodb_buffer_pool_dump_status          |             |
| Innodb_buffer_pool_load_status          |             |
| Innodb_buffer_pool_resize_status        |             |
| Innodb_buffer_pool_load_incomplete      | OFF         |
| Innodb_buffer_pool_pages_data           | 874527      |
| Innodb_buffer_pool_bytes_data           | 14328250368 |
| Innodb_buffer_pool_pages_dirty          | 0           |
| Innodb_buffer_pool_bytes_dirty          | 0           |
| Innodb_buffer_pool_pages_flushed        | 0           |
| Innodb_buffer_pool_pages_free           | 2351473     |
| Innodb_buffer_pool_pages_made_not_young | 0           |
| Innodb_buffer_pool_pages_made_young     | 0           |
| Innodb_buffer_pool_pages_misc           | 0           |
| Innodb_buffer_pool_pages_old            | 322843      |
| Innodb_buffer_pool_pages_total          | 3226000     |
| Innodb_buffer_pool_pages_lru_flushed    | 0           |
| Innodb_buffer_pool_read_ahead_rnd       | 682843      |
| Innodb_buffer_pool_read_ahead           | 0           |
| Innodb_buffer_pool_read_ahead_evicted   | 0           |
| Innodb_buffer_pool_read_requests        | 56901439    |
| Innodb_buffer_pool_reads                | 874396      |
| Innodb_buffer_pool_wait_free            | 0           |
| Innodb_buffer_pool_write_requests       | 515         |
+-----------------------------------------+-------------+
23 rows in set (0.001 sec)

关于表的附加信息

SHOW TABLE STATUS LIKE 'test';
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| test | InnoDB |      10 | Dynamic    | 499216334 |             25 | 12859736064 |               0 |            0 |   7340032 |      500000001 | 2022-07-29 00:10:49 | 2022-07-29 00:32:52 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)

EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "test",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["id"],
      "rows": 499216334,
      "filtered": 100,
      "using_index": true
    }
  }
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

SHOW VARIABLES LIKE 'innodb_io%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 32000 |
| innodb_io_capacity_max | 64000 |
+------------------------+-------+

我有一个团队成员表，其中PlayerID某些行缺少。

CREATE TABLE TeamPlayers
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
TeamID int(11) unsigned NOT NULL,
PlayerID int(11) unsigned,
Name varchar(255),
INDEX(PlayerID),
INDEX(Name),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

我尝试匹配PlayerID每个名称。然而，由于名字不是唯一的（两个人可以有相同的名字），我必须依赖一些近似值。

第一个尝试是假设合作者的名字是唯一的。例如，

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        NULL        John West
3     16        NULL        Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

假设库克队只与一个名叫约翰·韦斯特的人一起比赛，反之亦然。

预期输出：

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        5           John West
3     16        9           Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

我从

SELECT a.Name,MAX(a.PlayerID),MAX(b.PlayerID),
    GROUP_CONCAT(a.ID SEPARATOR ','),    GROUP_CONCAT(b.ID SEPARATOR ',') 
  FROM TeamPlayers a JOIN TeamPlayers b 
  ON a.TeamID=b.TeamID AND a.Name<>b.Name
  GROUP BY a.Name

但是到UPDATE餐桌的正确方法是什么？

SQL小提琴

我捕获对其他用户的文章发表评论的用户，如

INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq) 
    SELECT AuthorID,CommentatorID,C FROM 
        (SELECT b.UserID AS AuthorID,c.UserID AS CommentatorID,COUNT(*) AS C 
            FROM ArticleComments a  
                JOIN ArticleMap b ON a.ArticleID=b.ArticleID 
                JOIN CommentMap c ON a.CommentID=c.CommentID 
                GROUP BY b.UserID,c.UserID
         ) c 
ON DUPLICATE KEY UPDATE Freq=c.C

但由于ArticleComments表太大，查询速度很慢。

有什么办法可以改进这个查询吗？

桌子很简单，

CREATE TABLE AuthorCommentators
(
AuthorID int(11) unsigned NOT NULL,
CommentatorID int(11) unsigned NOT NULL,
Freq mediumint(7) unsigned,
INDEX(CommentatorID),
INDEX(Freq),
PRIMARY KEY(AuthorID,CommentatorID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE ArticleComments
(
ArticleID int(11) unsigned NOT NULL,
CommentID int(11) unsigned NOT NULL,
INDEX(CommentID),
PRIMARY KEY(ArticleID,CommentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE ArticleMap
(
ArticleID int(11) unsigned NOT NULL,
UserID int(11) unsigned NOT NULL,
INDEX(UserID),
PRIMARY KEY(ArticleID,UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE CommentMap
(
CommentID int(11) unsigned NOT NULL,
UserID int(11) unsigned NOT NULL,
INDEX(UserID),
PRIMARY KEY(CommentID,UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

由于两者都Articles可以Comments有多个作者，我必须规范化架构。

我做INSERT INTO SELECT了一张巨大的桌子（1TB）。该过程完成后，MySQL/InnoDB 仍会进行数小时的后处理（创建 更新的二级索引等）。

由于这些是内部流程，它们不会出现在SHOW PROCESSLISTor中SHOW ENGINE INNODB STATUS \G。

我只能通过 I/O 活动来检查进程。

有什么方法可以正确监控正在进行的流程？

我的桌面上有 MariaDB 10.5，有多个磁盘（SSD 和 HDD）用于写入密集型项目。写入单个表的速度很快，并且脏页的百分比保持接近于零1000-3000 writes/s。

但是，当我同时主动写入多个表时，脏页的百分比迅速上升。问题是刷新到磁盘的水平下降到100 writes/s并保持在该水平。

在重新启动之前，此行为将一直存在。

我认为这个问题与Percona 10 年前确定的问题有某种关系（不完全是）。

有什么技巧可以保持冲水的速度吗？

key_buffer_size     = 20M
max_allowed_packet  = 5G
thread_stack        = 256K
thread_cache_size       = 8
innodb_buffer_pool_size = 70G
innodb_log_buffer_size = 512M
innodb_log_file_size = 20G
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_flush_neighbors=0
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-ft-result-cache-limit=4G
innodb_fatal_semaphore_wait_threshold=7200
innodb_compression_default=ON
innodb_random_read_ahead=1

更新：可能的解决方案

我不发布这个，因为我不确定它是否是真正的解决方案。经过大量实验，我发现问题在于自适应冲洗。我解决了这个问题

innodb_adaptive_flushing=0
innodb_adaptive_flushing_lwm=70

显然，当触发自适应刷新以避免高 I/O 时，它会停留很长时间。

UPDATE2：页面与列压缩

我发现问题是

innodb_compression_default=ON

根据Rick James的建议，我使用列压缩而不是页面压缩创建了类似的表。两种方法的压缩率大约为 300%（页面压缩比页面压缩好 10-20%，适用于整个表而不是选择性列），但在 HDD 上的性能明显不同。

我认为问题在于在 HDD 上同时写入由页面压缩创建的多个稀疏文件（这在 SSD 上应该不是问题）。

我需要重新创建所有表来确定，这个过程非常耗时。

在这个sqlfiddle中，

CREATE TABLE t1
(
id int(11) unsigned NOT NULL,
val int(11) unsigned NOT NULL,
rank mediumint(7) unsigned,
INDEX(id),
INDEX(rank),
PRIMARY KEY(id,val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (id,val,rank) VALUES (1,2,2), (1,1,1), (1,9,1), (1,5,23), 
(1,3,2), (2,2,1), (2,9,8), (2,5,0);

SELECT id,GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') 
FROM t1 GROUP BY id;

如何限制返回的行数GROUP_BY？

我的问题是ORDERwithinGROUP_BY应该与ORDERwithin 相同GROUP_CONCAT。

例如，如果使用LIMIT 3，输出应该是

id  GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',')
1   5|23,3|2,2|2
2   9|8,2|1,5|0

innodb_read_io_threads和的默认值为innodb_write_io_threads4。如何检查服务器负载是否需要更高数量的线程？

正如我经常检查的那样show engine innodb status \G，没有可追踪的活动：

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
...
I/O thread 32 state: waiting for completed aio requests (write thread)
I/O thread 33 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,

此外，将其增加到最大值 64 的缺点是什么？如果不使用，它们是无害的空闲线程。

表格中的每一个都在SELECTn 行中ArticleIDTagMap

SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
HAVING COUNT(b.TagID) < 3
ORDER BY a.ArticleID,a.TagID

我想添加一个条件来限制查询到特定的标签，

SELECT a.ArticleID,a.TagID FROM TagMap a
  LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
    WHERE a.TagID IN(SELECT TagID FROM Tags WHERE Status=1)
    AND b.TagID IN(SELECT TagID FROM Tags WHERE Status=1)
  GROUP BY a.ArticleID,a.TagID
  HAVING COUNT(b.TagID) < 3
  ORDER BY a.ArticleID,a.TagID

如果我使用条件作为子查询，我需要子查询。如果我使用条件 as JOIN，我需要两个JOINs ，因为上面JOIN是LEFT JOIN。

有更简单的方法吗？

我想为每个条目找到最上面的父 ID。例如，

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);

我打算得到

ID     Highest ParentID
1      NULL
2      1
3      1
4      1

我计划创建一个递归查询（MySQL 8 或 MariaDB 10.5），方法是在父 ID 为特定值（例如，在上述情况下为 NULL）时添加一个条件来中断递归。我从

WITH RECURSIVE cte (ID, ParentID) as (
  SELECT ID,ParentID FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT * FROM cte;

但它不像我想要的那样工作，因为它获取下一个 ParentID 而不是递归。

样品小提琴。

我有疑问

INSERT IGNORE INTO t2 (ID,P1,P2,Items) 
    SELECT ID,P1,P2,SUM(Items) 
    FROM t1 GROUP BY ID,P1,P2;

由于我需要经常运行此查询以检查 中的更改t1，因此我需要添加

ON DUPLICATE KEY UPDATE t2.Items=SUM(t1.Items)

但它给出了ERROR 1111 (HY000): Invalid use of group function错误。

我的表是使用 InnoDB 行压缩 ( ENGINE=InnoDB ROW_FORMAT=COMPRESSED) 创建的。现在我将它们更改为页面压缩。根据 MariaDB 的官方文档，启用页面压缩仅影响新创建的表。

因此，我创建了一个副本表并使用INSERT INTO SELECT.

我想知道ALTER表是否可以更改压缩类型（从行到页）？

我们可以ALTER使用普通表来使用页面压缩

ALTER TABLE t1 PAGE_COMPRESSED=1;

我的问题是关于删除ROW_FORMAT=COMPRESSED和添加PAGE_COMPRESSED=1.

我使用SphinxSE而不是FULLTEXT. Sphinx 设置适用于搜索引擎的最终用户。根据官方文档，

限制条款。支持 LIMIT N 和 LIMIT M,N 形式。与常规 SQL 不同（但与 Sphinx API 类似），默认情况下存在隐式 LIMIT 0,20。

因此，任何查询只返回 20 行

MariaDB [sphinx]> SELECT * FROM t1 WHERE query='test;mode=any';
+------+--------+---------------+
| id   | weight | query         |
+------+--------+---------------+
|  556 |      1 | test;mode=any |
|  864 |      1 | test;mode=any |
| 1329 |      1 | test;mode=any |
| 1781 |      1 | test;mode=any |
| 1832 |      1 | test;mode=any |
| 2157 |      1 | test;mode=any |
| 2388 |      1 | test;mode=any |
| 2889 |      1 | test;mode=any |
| 3118 |      1 | test;mode=any |
| 3155 |      1 | test;mode=any |
| 3255 |      1 | test;mode=any |
| 3485 |      1 | test;mode=any |
| 3495 |      1 | test;mode=any |
| 3623 |      1 | test;mode=any |
| 4034 |      1 | test;mode=any |
| 4525 |      1 | test;mode=any |
| 4563 |      1 | test;mode=any |
| 4659 |      1 | test;mode=any |
| 4736 |      1 | test;mode=any |
| 4807 |      1 | test;mode=any |
+------+--------+---------------+
20 rows in set (0.027 sec)

我在 中尝试了各种设置sphinx.conf，但无法更改设置以在 SQL 查询中返回更多结果。

我的服务器默认CHARSET是UTF-8（在 my.cnf 中设置）。我创建像

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
Title varchar(255),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=2

但有些表只有int列。对他们使用latin字符集是否有益？还是让所有表都带有UTF-8字符集以保持一致性更好？

CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

对比

CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_swedish_ci KEY_BLOCK_SIZE=1

我JOIN将自己的表作为

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

如何通过以下方式限制为每个表（t1 和 t2）检索的行

WHERE TagID IN(SELECT TagID FROM Tags WHERE Articles>50)

请注意，该表TagMap很大，行数超过 200M，我希望在JOIN.

其实我想限制行数的原因TagMap是表太大，无法执行JOIN。

我有一个简单的 tag_map 表

CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) REFERENCES Articles(ArticleID) ON DELETE CASCADE,
PRIMARY KEY(TagID,ArticleID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1

我得到了共同标签（标签，与特定标签一起出现）：

SELECT TagID AS TagID2,COUNT(*) FROM TagMap WHERE ArticleID IN(
SELECT ArticleID FROM TagMap WHERE TagID=1 // This is TagID1
) 
GROUP BY TagID

如何对所有标签进行此查询以获取

TagID1,TagID2,COUNT(*)

表格很大（10-50M 行），每篇文章有几十个标签。因此，性能至关重要。

我有一个项目数据库，因为每个项目都有一组键值对，例如

place => london
field => engineering

因为我经常需要找到具有相同两个键值集的项目。id我通过为每个key和存储指定的 s 来创建支持表value。

CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned,
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned,
UNIQUE INDEX(Key1,Value1,Key2,Value2),
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned,
PRIMARY KEY (PairID,ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

（为简单起见，我删除了索引和 FK）

例如，表格的一行Pairs是

PairID    Key1             Value1            Key2              Value2
1         3 (for place)    88 (for London)   10 (for field)    9 (for engineering)

我需要填写最后两张表。

1. 我怎样才能从表中的INSERT所有组合？Key1,Value1,Key2,Value2ItemKeyValues (KeyID,ValueID)
2. 我怎样才能UPDATE从桌子上ItemPairs桌子ItemPairs？

性能非常重要，因为表ItemKeyValues超过 1 亿行（尽管这是一次性操作）。

我尝试通过计算另一个表中的行来更新一个表

UPDATE t1 SET c1 = (
    SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
    )

奇怪的是，这个查询停留在Sending data状态好几个小时。

SELECT COUNT(*) FROM t2

在 2 秒内返回 ~16M。

SELECT id FROM t2 GROUP BY id

在 5 秒内返回 50K 行。

表结构非常简单

CREATE TABLE t2
(
item int(11) unsigned NOT NULL,
id mediumint(7) unsigned NOT NULL,
PRIMARY KEY (item,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

设置时

innodb_fast_shutdown=0

MySQL 在关闭/崩溃之前丢弃所有不完整的事务。默认值为1，这对于保留不完整的事务是可以理解的。

然而，在恢复模式（例如，innodb_force_recovery = 6）中，MySQL 将所有未完成的事务视为已提交。由于事务不完整，这显然会损坏表。

当损坏整个表时保留不完整事务的目的是什么？

如果在某些情况下有必要，为什么不能像保存表innodb_force_recovery一样简单地删除所有未完成的事务（当它们无法提交时） ？innodb_fast_shutdown=0还是我错过了什么？

我尝试通过mysqldump. 该数据库有数百万行，总大小为 1.3TB。

CREATE TABLE PageContents
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
FOREIGN KEY(ID) REFERENCES AllPages(ID) ON DELETE CASCADE,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1;

但我在倾倒 8 小时后收到错误消息：

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PageContents` at row: 20946782

我增加max_allowed_packet了mysql.cnf

max_allowed_packet  = 16G