mysql - 百万记录 Sql 排序依据和行数计数

Question

我有一个包含数百万条记录的数据库。该表的结构如下:

<小时/>

表名称:记录

Filed1:名称(varchar)(主键)

字段2:记录(int/bigint)

示例:

姓名 |记录

达林 | 256

亚伦 | 3

达里尔 | 12

...

<小时/>

我需要知道名称为“namex”的用户在排序记录中的位置。

目前我实现了这个解决方案:

...

$name=namex;
$query= mysqli_query($mysqli,"SELECT Name FROM record  ORDER BY Record DESC");

$x=0;
$rank=0;
if ($query->num_rows > 0) {
    // output data of each row
    while($row = $query->fetch_assoc()) {
        if($row["Name"]==$name){
            $rank=$x+1;
            echo "Rank : $rank<br>";

            break;
        }


        $x++;
    }
}

...

有了它和数据库中的 100 万条记录，大约 4 秒就能得到答案。

我尝试在字段 Record 上放置表索引，但性能保持不变。

如何减少执行时间？

Answer 1

由于我不知道您使用的是什么 DBMS(在标签中您同时使用 mysql 和 sql-server...)，您可以创建一个 View (对于 SQL Server 必须是 indexed view )或者为 mysql 实现/模拟一种物化 View (具有更好的性能)。通过一些 DBMS 获得更好的性能是很好的观点。对于MySQL可能没有区别。

在 View 中显示排名位置，如下面的查询(mysql 示例):

CREATE VIEW ranked_record AS
SELECT 
    record.Name,
    @curRank := @curRank + 1 AS rank
FROM 
    record,
    (SELECT @curRank := 0) r
ORDER BY Record DESC;

或 SQL 服务器:

CREATE VIEW ranked_record AS
SELECT 
    record.Name,
    row_number() over(ORDER BY record)
FROM 
    record;

然后运行您的查询:

SELECT name , rank FROM ranked_record WHERE name LIKE 'some name'

更新:

在约翰评论之后，我意识到使用变量的 View 的错误。这是不可能的，因为 "feature/bug" of/from MySQL

因此，您可以选择将其用作 subquery in FROM clause :

SELECT 
    name, 
    rank
FROM (
        SELECT 
            record.Name,
            @curRank := @curRank + 1 AS rank
        FROM 
            record,
            (SELECT @curRank := 0) r
    ) AS ranked_record
WHERE 
    name LIKE 'some name';

或者创建一个函数来计算 View 内的排名 ( like this example ):

CREATE FUNCTION `func_inc_var_session`() RETURNS int(11)
begin
  SET @var := IFNULL(@var,0) + 1;
  return @var;

结束；

然后，像以前一样创建 View ，只需使用函数而不是变量:

CREATE VIEW ranked_record AS
SELECT 
    record.Name,
    func_inc_var_session() as rank
FROM 
    record
ORDER BY Record DESC;