Jeffrey's questions

在表中查找树结构（父子结构）的递归查询很常见，但大多数问题是 childID 必须高于 parentID。

一个可能的解决方案是使用类似的查询（给定父母 X，找到所有孩子）

SELECT * FROM (
    SELECT @pv:=(
        SELECT GROUP_CONCAT( child_id SEPARATOR "," ) FROM link_parent_child WHERE FIND_IN_SET( parent_id, @pv )
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$parentStartID$) tmp
    ) a
WHERE lv IS NOT NULL

或给定孩子 X，找到所有父母

SELECT * FROM (
    SELECT @pv:=(
        SELECT parent_id FROM link_parent_child WHERE child_id = @pv
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$ChildStartID$) tmp
    ) a
WHERE lv IS NOT NULL

但是，对于超过 50 万条记录的表，这有点慢......（轻描淡写）

CREATE TABLE `link_parent_child` (
    `parent_id` int(11) NOT NULL,
    `child_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `link_parent_child`
    ADD PRIMARY KEY (`child_id`,`parent_id`);
    ADD KEY `child_id` (`child_id`),
    ADD KEY `parent_id` (`parent_id`),
    ADD KEY `parent_id_2` (`parent_id`,`child_id`);

即使设置了索引，并且只有 10k 条记录，查询也需要 80 多秒才能运行。

所以，这个查询（如果我错了，请纠正我）非常慢，因为内部几乎所有行都返回 NULL，（我猜）这些行存储在临时表中并被查询的外部过滤掉。我猜想如果早先过滤掉NULL，查询会加快速度，但这可能吗？

我的树有一个动态长度，但大多不超过 3-10 层。我应该承担损失并以编程方式解决这个问题，还是有办法加快速度？

我一直在尝试优化我的表格，但仍无法获得所需的结果。

CREATE TABLE IF NOT EXISTS `puzzles` (
  `puzzleID` int(7) NOT NULL,
  `puzzleMask` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `puzzleScore` int(7) NOT NULL,
  `time` int(11) NOT NULL DEFAULT '0',
  `hour` int(11) NOT NULL DEFAULT '0',
  `ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Iip` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`puzzleID`, `puzzleMask`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

ID和Mask的组合必须是唯一的，而ID是随机整数，Mask是最大长度为30的数字组合。time是unix时间戳，hour是“time - (time % 3600)”（时间在该行已提交）。ip 是服务器的 IP 地址（大约 20 个服务器），Iip 是 INET_ATON( ip )。

这 20 台服务器向该数据库提交拼图信息。我想显示提交信息的实时统计信息（每秒 5-10 条记录）。

为此，我使用了两个需要尽可能优化的主要查询：

获取总统计

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# GROUP BY hour

获取服务器相关统计信息

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# AND Iip = INET_ATON("#IPAddress#") GROUP BY hour

我的问题是，使用什么索引？我现在已经尝试了很多东西，但是随着数据库的快速增加，试验和错误的时间已经结束了……例如，我是否需要一个单独的 puzzleScore 索引，因为我想要最大值？或者我需要将它合并到另一个索引中吗？

此外，将主键（puzzleID 和 puzzleMask）与 INSERT IGNORE INTO... 结合使用是否比使用具有自动增量的 id 并首先执行 SELECT 以查看记录是否存在以及插入更利于性能？ ？

谢谢

给定一个简单的文本和时间列，我想从文本列中选择 X 个唯一值，而该行包含时间的最高值。

INSERT INTO `test` (`id`, `text`, `time`) 
VALUES
    (1, 'test1', 1),
    (2, 'test1', 3),
    (3, 'test1', 2),
    (4, 'test2', 1),
    (5, 'test2', 100),
    (6, 'test2', 20) ;

满足我大部分要求的查询是：

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    a.`text` ASC
LIMIT 0,50

对于小桌子，这非常有效。尽管在我的表（300k+ 行）中，由于子查询，它使 mysql 崩溃。

是否可以优化此查询？如果无法优化，是否可以选择最后插入的唯一值text？（理论上id和time是不相关的，尽管在 99% 的情况下会发现相关性，而 id 越高，时间越长）

我想自动创建用户，它们都有一个唯一的 ID。

现在问题来了。如果两个用户是在完全相同的纳秒内创建的，则可能不会引起问题。因此，我现在要做的是：

1. 获取下一个自动增量编号
2. 添加一个名为“U”的新用户 + 自动增量数字 + 随机（0-9）

当在完全相同的纳秒内创建超过 10 个用户时，这仍然会导致问题（理论上！）。因此，我正在寻找一种让 MySQL 处理用户名的方法。

考虑下表：

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

我想使用：

INSERT INTO test (id, name) VALUES (null, "U" + id)

其中 id 是自增数。但是，由于 id 没有在 MySQL 中定义，它将包含 0。

如何定义 id，使其包含该行的自增数。这可以通过两个查询轻松完成，但我希望只在一个查询中执行此操作:)

谢谢！