sanjihan's questions

我正遭受 mysqldump/mariadb-dump 速度缓慢的困扰。

我发现禁用autocommit, unique_checks and foreign_key_checks提升导入性能。

我通过在前面dump.sql添加

SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;

并附加

COMMIT;SET unique_checks=1;SET foreign_key_checks=1;

直到它的结束。

附加很简单，但是在添加时，我需要在所消耗的时间和空间之间做出妥协。

前面加上

sed -i '0,/^/s//SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;/' dump.sql

进行大型转储可能需要花费大量时间，但它会创建一个文件。

cat <(echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;") dump.sql <(echo "COMMIT;SET unique_checks=1;SET foreign_key_checks=1;") > new-dump.sql"

速度很快，但它会创建另一个转储文件，因此占用大量空间（即使它只是临时的）

在创建转储时，是否可以指示mysqldump/maridb-dump在转储前面添加和附加自定义文本？

我正在使用VARBINARY(16)数据类型存储 ips。我想只选择 ipv4 ips。是否可以按VARBINARY（或VARCHAR）中使用的长度过滤列？像这样的东西

SELECT INET6_NTOA(`ip`) from TABLE where BYTESLENGHT(`ip`) = 4

我想监控某些查询执行了多少次。看看下面的 3 个查询，第二个和第三个是写成prepared statement。

SELECT COUNT(*) FROM TABLE1

UPDATE TABLE2 SET `last_parsed` = NOW() WHERE `id` = ?

SELECT 1 FROM TABLE2 WHERE (`last_parsed` IS NULL OR `last_parsed` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)) AND `id` = ?

mariadb/mysql dbms 是否跟踪每个查询的执行次数？它是否将准备好的语句分组到一个列表中，以便如果id是 10 或 20，它们都显示在同一类别下？

我想获取包含超过 10M 行的表（名为 CONTENT）的最后（或最后几行）行。该查询包含其他 2 个表的连接，而且速度非常慢。这些是表定义和我的查询：

CREATE TABLE `USER` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `GUID` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `value` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `CONTENT` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `guid_id` int(11) unsigned DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `guid` (`guid_id`),
  KEY `user_id` (`user_id`),
  KEY `timestamp` (`timestamp`),

  CONSTRAINT `CONTENT_ibfk_4` FOREIGN KEY (`guid_id`) REFERENCES `GUID` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `CONTENT_ibfk_5` FOREIGN KEY (`user_id`) REFERENCES `USER` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

询问：

SELECT 
`CONTENT`.`id`,
`GUID`.`value` AS `guid_value`, 
`USER`.`value` AS `user_value` 
FROM `CONTENT`, `USER`, `GUID` 
WHERE `CONTENT`.`user_id` = `USER`.`id` 
AND `CONTENT`.`guid_id` = `GUID`.`id` 
ORDER BY `CONTENT`.`timestamp` DESC LIMIT 1
  # even without ORDER BY the query is slow as seen by explain command

这些是复制为 INSERT 的解释命令的结果：

+------+-------------+---------+--------+---------------+---------+---------+-----------------------------+-------+----------------------------------------------+
| id   | select_type | table   | type   | possible_keys | key     | key_len | ref                         | rows  | Extra                                        |
+------+-------------+---------+--------+---------------+---------+---------+-----------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | GUID    | index  | PRIMARY       | value   | 37      | NULL                        | 16329 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | CONTENT | ref    | guid,user_id  | guid    | 5       | MANAGER.GUID.id             | 293   | Using where                                  |
|    1 | SIMPLE      | USER    | eq_ref | PRIMARY       | PRIMARY | 4       | MANAGER.CONTENT.user_id     | 1     |                                              |
+------+-------------+---------+--------+---------------+---------+---------+-----------------------------+-------+----------------------------------------------+

该查询无法使用，因此我将其拆分为 2 个查询。首先，我检索感兴趣的 CONTENT.id，其次，我WHERE CONTENT.id = x在 SELECT 语句中插入一个附加子句。似乎在原始查询中 MariaDB 优化器不理解我只需要 1 行，因此它使用 GUID 表中的每一行生成笛卡尔积。是否将查询拆分为 2 个子查询？有人可以确认笛卡尔积运算确实是导致问题的运算吗？（解释命令结果的第一行）

解释 Ricks 查询：

+------+-------------+------------+--------+----------------------+-------------------+---------+-----------------------------+---------+-------------+
| id   | select_type | table      | type   | possible_keys        | key               | key_len | ref                         | rows    | Extra       |
+------+-------------+------------+--------+----------------------+-------------------+---------+-----------------------------+---------+-------------+
|    1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL              | NULL    | NULL                        | 2       |             |
|    1 | PRIMARY     | CONTENT    | eq_ref | PRIMARY,guid,user_id | PRIMARY           | 4       | c.id                        | 1       | Using where |
|    1 | PRIMARY     | USER       | eq_ref | PRIMARY              | PRIMARY           | 4       | MANAGER.CONTENT.user_id     | 1       |             |
|    1 | PRIMARY     | GUID       | eq_ref | PRIMARY              | PRIMARY           | 4       | MANAGER.CONTENT.guid_id     | 1       |             |
|    2 | DERIVED     | CONTENT    | index  | NULL                 | timestamp         | 6       | NULL                        | 9474301 | Using index |
+------+-------------+------------+--------+----------------------+-------------------+---------+-----------------------------+---------+-------------+

优化搜索查询的一种可能方法是（a）将保留与不同关系/表对应的数据的记录存储在（b）相同文件→相同页面上。这样，可以更快地执行连接。

我用谷歌搜索了“共同聚类”，结果出人意料地很少。例如，我在 MySQL 上一无所获。有迹象表明甲骨文在 10 年前就提供了它。联合集群仍然是优化的有效选择吗？

例如，您有两个关系/表：

• Employee (id, name, age, did)
• Department (did, location)

您优化的典型查询可能如下所示：

SELECT E.name, 
       E.age 
  FROM Employee E, 
       Department D 
 WHERE E.age = 25 
   AND E.did = D.did;

如果您有 1,000,000 名员工并且他们都在 25 到 27 之间，那么最好的连接方法可能是排序合并连接或哈希连接 - 两者都需要多次扫描。

现在，如果您将多个关系/表的元组/行存储在同一页面上，您可以使用一种物理结构，将具有某个特定的部门与did具有相同的员工一起存储did。请注意，这种连接需要更少的 IO。

我正在尝试按比例增加冗余属性和性能增益。尽管存储冗余信息会导致许多异常，但我只关注冗余属性的大小与在不同表中存储指向另一个元组的指针（或引用）。

那么InnoDB Mysql系统中元组指针的大小是多少呢？我发现 MyISAM 使用 6 个字节。InnoDB 也一样吗？作为参考，6 个字节使得在单独的表中存储 3 个 SMALLINT（每个 2 个字节）在存储方面毫无意义。也许一个等价的问题是：记录地址的大小是多少？

我无法理解为什么一组特定的功能依赖关系不是最小的。我们R(A,B,C,D,E,F,G)与以下依赖项 F 有关系：

1. A->CDE
2. B->FG
3. AB->CDEFG

F 的最小覆盖只是依赖 1 和 2。很直观，属性 CDEFG 已经分别由 A 和 B 确定。因此，没有新的属性是由 AB 的并集决定的。是否有确定这种依赖关系（AB->CDEFG 的联合）是多余的确切规则？

到目前为止，本书中列出的阿姆斯壮规则是：

    IR1 (reflexive rule): If X ⊇ Y, then X →Y.
    IR2 (augmentation rule): {X → Y} |=XZ → YZ. 
    IR3 (transitive rule): {X → Y, Y → Z} |=X → Z.

和

    IR4 (decomposition, or projective, rule): {X → YZ} |=X → Y. 
    IR5 (union, or additive, rule): {X → Y, X → Z} |=X → YZ. 
    IR6 (pseudotransitive rule): {X → Y, WY → Z} |=WX → Z.

我不确定哪个规则负责AB->CDEFG. 联合规则似乎很接近，但左侧属性被列为相同（均为 X），FD1 U FD2在我的情况下我不能说。

我正在阅读一本关于数据库的书，其中的一章讨论了缓冲区管理器及其替代策略。

我注意到 DBMS 直接与内存交互，而不是要求操作系统与虚拟内存交互。那是对的吗？这种直接的沟通究竟是如何发生的？DBMS 允许分配多少内存？我从来没有接触过如此低级的函数调用，我很想看到一些解释和例子。

如果您不知道要搜索的正确关键字，那么在 google 上查找此类信息有点麻烦。