文章/答案/技术大牛

发布

社区首页 >问答首页 >存在索引的情况下使用变量的MySQL排名

问存在索引的情况下使用变量的MySQL排名
EN

Stack Overflow用户

提问于 2012-06-02 03:54:00

回答 1查看 562关注 0票数 3

使用使用@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

sql

mariadb

回答 1

Stack Overflow用户

发布于 2012-10-28 05:32:59

这个“把戏”是一个等着爆炸的炸弹。一个聪明的优化器会在它认为合适的时候评估查询，优化速度--这就是为什么它被称为优化器。我不认为这种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 ;

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/10856546

复制

相似问题

问存在索引的情况下使用变量的MySQL排名
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问存在索引的情况下使用变量的MySQL排名EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问存在索引的情况下使用变量的MySQL排名
EN