2gb表有1000万行，后期分页选择很慢

我在MySQL中有一个表，其中有1000万行，数据为2GB选择IN LIFO格式的数据是慢速

表引擎=InnoDB

表有一个主键和一个

SELECT * FROM link LIMIT 999999 , 50;

我如何提高桌子的性能？

表格结构

id  int(11) NO  PRI NULL    auto_increment
url varchar(255)    NO  UNI NULL    
website varchar(100)    NO      NULL    
state   varchar(10) NO      NULL    
type    varchar(100)    NO      NULL    
prio    varchar(100)    YES     NULL    
change  varchar(100)    YES     NULL    
last    varchar(100)    YES     NULL

注意：SELECT * FROM link LIMIT 1 , 50;占用.9ms，但当前sql占用1000ms 1000次，占用更多

这很可能是由于"早期行查找"

MySQL可以强制执行"后期行查找"。尝试以下查询

SELECT  l.*
FROM    (
        SELECT  id
        FROM    link
        ORDER BY
                id
        LIMIT 999999 , 50
        ) q
JOIN    link l
ON      l.id = q.id

检查这篇文章

MySQL限制子句和低费率查找

对于Next和Previv按钮，可以使用WHERE子句而不是OFFSET。

示例（使用LIMIT 10-下面解释的示例数据）：您所在的某个页面显示了ID为[2522,2520,2514,2513,2509,2508,2506,2504,2497,2496]的10行。在我的案例中，这是用创建的

select *
from link l
order by l.id desc
limit 10
offset 999000

对于下一页，您将使用

limit 10
offset 999010

获取ID为CCD_ 9的行。

对于上一页，您将使用

limit 10
offset 998990

获取ID为CCD_ 10的行。

以上所有查询在500毫秒内执行。使用Sanj建议的"技巧"，仍然需要250毫秒。

现在，对于具有minId=2496和maxId=2522的给定页面，我们可以使用WHERE子句为Next和Last按钮创建查询。

下一个按钮：

select *
from link l
where l.id < :minId -- =2496
order by l.id desc
limit 10

生成的ids:[2495,2494,2493,2492,2491,2487,2483,2481,2479,2475]。

上一个按钮：

select *
from link l
where l.id > :maxId -- =2522
order by l.id asc
limit 10

生成的ids:[2524,2525,2527,2530,2533,2535,2538,2540,2541,2542]。

要反转顺序，您可以在子选择中使用查询：

select *
from (
    select *
    from link l
    where l.id > 2522
    order by l.id asc
    limit 10
) sub
order by id desc

生成的ids:[2542,2541,2540,2538,2535,2533,2530,2527,2525,2524]。

这些查询在"无时间"（小于1毫秒）内执行，并提供相同的结果。

您不能使用此解决方案创建页码。但我不认为你会输出20万个页码。

测试数据：

用于示例和基准的数据已使用创建

CREATE TABLE `link` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `url` VARCHAR(255) NOT NULL,
    `website` VARCHAR(100) NULL DEFAULT NULL,
    `state` VARCHAR(10) NULL DEFAULT NULL,
    `type` VARCHAR(100) NULL DEFAULT NULL,
    `prio` VARCHAR(100) NULL DEFAULT NULL,
    `change` VARCHAR(100) NULL DEFAULT NULL,
    `last` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `url` (`url`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;
insert into link
    select i.id
        , concat(id, '-', rand()) url
        , rand() website
        , rand() state
        , rand() `type`
        , rand() prio
        , rand() `change`
        , rand() `last`
    from test._dummy_indexes_2p23 i
    where i.id <= 2000000
      and rand() < 0.5

其中CCD_ 17是包含2^23个id（约8M）的表。因此，数据包含每秒随机丢失的大约1M行。表大小：228 MB

由于数据量大，

有几个技巧可以提高查询响应时间：

将存储引擎Innodb更改为myisam
创建表分区(https://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html)
Mysql集群(http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html)
增加硬件容量

感谢

首先，在没有任何顺序的情况下在表上运行并不能保证您的查询在运行两次时会返回相同的数据。最好添加一个ORDER BY子句。将id作为一个好的候选者，因为它是您的主键，并且看起来是唯一的（因为它是auto_increment值）。

你可以用这个作为你的基础：

SELECT * FROM link ORDER BY id LIMIT 50;

这将为您提供表中的前50行。

现在，对于接下来的50行，我们可以保存查询中的最后一个位置，而不是使用OFFSET。

您将保存最后一行的id上一个查询的最后一个id，并在下一个查询中使用它：

SELECT * FROM link WHERE id > last_id ORDER BY id LIMIT 50;

这将在最后一个id之后给你接下来的50行。

查询在OFFSET的高值上运行缓慢的原因是mysql必须在给定的OFFSET中的所有行上运行，并返回最后一个LIMIT行数。这意味着OFFSET越大，查询运行的速度就越慢。

我上面展示的解决方案不依赖于OFFSET，因此查询将以相同的速度运行，与当前页面无关。

另请参阅这篇有用的文章，它解释了您可以选择的其他几个选项：http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

我已经将我的SQL查询更新到了这个版本，这花费的时间更少。

 SELECT * FROM link ORDER BY id LIMIT 999999 , 50  ;

相关内容

最新更新

热门标签：