我正在使用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。
我无法理解为什么一组特定的功能依赖关系不是最小的。我们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
在我的情况下我不能说。