使用 @N=@N + 1 的经典技巧来获取某些有序列上的项目的排名。现在,在订购之前,我需要通过将基表与其他表进行内部连接来过滤掉基表中的一些值。所以查询看起来像这样 -:
SET @N=0;
SELECT
@N := @N + 1 AS rank,
fa.id,
fa.val
FROM
table1 AS fa
INNER JOIN table2 AS em
ON em.id = fa.id
AND em.type = "A"
ORDER BY fa.val ;
问题是,如果我在 em.type 上没有索引,那么一切都会正常工作,但如果我在 em.type 上放置索引,那么地狱就会释放,排名值而不是按 val 列排序这些行存储在 em 表中。
这是示例输出 -:
没有索引-:
rank id val
1 05F8C7 55050.000000
2 05HJDG 51404.733458
3 05TK1Z 46972.008208
4 05F2TR 46900.000000
5 05F349 44433.412847
6 06C2BT 43750.000000
7 0012X3 42000.000000
8 05MMPK 39430.399658
9 05MLW5 39054.046383
10 062D20 35550.000000
带索引-:
rank id val
480 05F8C7 55050.000000
629 05HJDG 51404.733458
1603 05TK1Z 46972.008208
466 05F2TR 46900.000000
467 05F349 44433.412847
3534 06C2BT 43750.000000
15 0012X3 42000.000000
1109 05MMPK 39430.399658
1087 05MLW5 39054.046383
2544 062D20 35550.000000
我认为索引的使用应该完全透明,并且输出不应该受到影响。这是 MySQL 中的错误吗?
这个“诡计”是一颗等待爆炸的炸弹。聪明的优化器会根据它认为合适的方式评估查询,优化速度 - 这就是它被称为优化器的原因。我不认为 MySQL 变量的这种使用被记录为可以像您期望的那样工作,但它确实有效。
一直在工作,直到最近对 MariaDB 优化器进行了改进。它可能也会在主流 MySQL 中出现问题,因为在 5.6 版本(尚未发布,仍为测试版)中,优化器有一些改进。
你能做的(直到MySQL实现窗口函数)是使用自连接和分组。无论优化器未来进行什么改进,结果都将是一致的。缺点是它可能不是很有效:
SELECT
COUNT(*) AS rank,
fa.id,
fa.val
FROM
table1 AS fa
INNER JOIN table2 AS em
ON em.id = fa.id
AND em.type = 'A'
INNER JOIN
table1 AS fa2
INNER JOIN table2 AS em2
ON em2.id = fa2.id
AND em2.type = 'A'
ON fa2.id <= fa.id
--- assuming that `id` is the Primary Key of the table
GROUP BY fa.id
ORDER BY fa.val ;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)