我在MySQL中有一个包含1000万行和2 GB数据的表
选择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 它的100倍时间
为了 下一个 和 上一页 您可以使用的按钮 WHERE 条款而不是 OFFSET。
例 （使用 LIMIT 10 - 下面解释的示例数据）：您在某个页面上显示10行带有ID [2522,2520,2514,2513,2509,2508,2506,2504,2497,2496]。在我的情况下这是创建的
select *
from link l
order by l.id desc
limit 10
offset 999000
对于下一页，您将使用
limit 10
offset 999010
获取带有ID的行 [2495,2494,2493,2492,2491,2487,2483,2481,2479,2475]。
对于您将使用的上一页
limit 10
offset 998990
获取带有ID的行 [2542,2541,2540,2538,2535,2533,2530,2527,2525,2524]。
以上所有查询都在执行 500毫秒。使用Sanj建议的“技巧”仍然需要 250毫秒。
现在用给定的页面 minId=2496 和 maxId=2522 我们可以为。创建查询 下一个 和 持续 按钮使用 WHERE 条款。
下一步按钮：
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毫秒）内执行并提供相同的结果。
您无法使用此解决方案来创建页码。但我认为你不会输出200K的页码。
测试数据：
用于示例和基准测试的数据已创建
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
哪里 test._dummy_indexes_2p23 是一个包含2 ^ 23个ID（约8M）的表。因此，数据包含每隔一个id随机丢失的大约1M行。表大小：228 MB
这很可能是由于“早期行查找”
可以强制MySQL进行“后期行查找”。请尝试以下查询
SELECT l.*
FROM (
SELECT id
FROM link
ORDER BY
id
LIMIT 999999 , 50
) q
JOIN link l
ON l.id = q.id
看看这篇文章
MySQL限制子句和速率低查找
首先在没有任何订单的情况下在您的桌面上运行不保证您的查询将返回相同的数据，如果运行两次。
最好添加一个 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 ;