求 sql 优化才几万数据查询都破 1s 了

SELECT b.rank FROM
        (
        SELECT t.id, @rownum := @rownum + 1 AS rank
          
        FROM (SELECT @rownum := 0) r,
          
        (SELECT id FROM fa_bankgy_dsxx_user ORDER BY jifen_all DESC) AS t
          
        ) AS b WHERE b.id = 29605;

表

CREATE TABLE `fa_bankgy_dsxx_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `openid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `realname` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
  `province` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `area` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `workunit` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `exchange_count` int(11) DEFAULT '0',
  `dati_count` int(11) DEFAULT '0' COMMENT '答题次数',
  `rank_fen` int(11) DEFAULT '0',
  `jifen_all` int(255) DEFAULT '0', 累计积分数量
  `jifen` int(11) NOT NULL DEFAULT '0',
  `createtime` int(11) NOT NULL,
  `updatetime` int(11) NOT NULL,
  `deletetime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jifen_all` (`jifen_all`),
  KEY `openid` (`openid`),
  KEY `workunit` (`workunit`)
) ENGINE=MyISAM AUTO_INCREMENT=33259 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

jifen_all 加了索引，怎么避全表扫描又能取出排名？

Supplement 1 · Jun 11, 2021

已经解决，感谢大家帮助

```
select count(*) as rank from fa_bankgy_dsxx_user where jifen_all >= (select jifen_all from fa_bankgy_dsxx_user where id = 29605 limit 1) ORDER BY jifen_all DESC;
```

有个缺点就是两个人积分同样，排名也一样了。

比如最高积分的两个人：
a 先生积分 435，排名 2
b 先生积分 435，排名 2

我这边有个前十公开排名那个我单独做处理就好

15 replies • 2021-06-11 16:12:24 +08:00