mysql select 按主键排序。表现

Question

我有一个类似这样的表“tbl”:ID bigint(20) - 主键，自增字段1字段2字段3

该表有 60 万多行。

1. 查询:SELECT * from tbl ORDER by ID LIMIT 600000, 1 需要 1.68 秒
2. 查询:SELECT ID, field1 from tbl ORDER by ID LIMIT 600000, 1 需要 1.69 秒
3. 查询:SELECT ID from tbl ORDER by ID LIMIT 600000, 1 需要 0.16 秒
4. 查询:SELECT * from tbl WHERE ID = xxx 需要 0.005 秒

这些查询在 phpmyadmin 中进行了测试。

结果是查询 3 和查询 4 ​​一起返回必然数据。查询 1 执行相同的工作，但速度要慢得多......

这看起来不适合我。谁能给点建议？

附言很抱歉格式化..我是这个网站的新手。

新测试:

Q5 : CREATE TEMPORARY TABLE tmptable AS (SELECT ID FROM tbl WHERE ID LIMIT 600030, 30);SELECT * FROM tbl WHERE ID IN (SELECT ID FROM tmptable);耗时 0.38 秒

我还是不明白这怎么可能。我重新创建了所有索引。我还能用那个表做什么？手动删除并重新填充？ :)

Answer 1

查询 1 查看表的主键索引，找到正确的 600,000 个 ID 及其在表中的相应位置，然后转到表并从这 600k 个位置获取所有内容。

查询 2 查看表的主键索引，找到正确的 600k id 及其在表中的相应位置，然后转到表并从这 600k 行中获取所需的字段子集。

查询 3 查看表的主键索引，找到正确的 600k id，并返回它们。它根本不需要看表。

查询 4 ​​查看表的主键索引，找到请求的单个条目，转到表，读取该单个条目并将其返回。

就时间而言，让我们向后构建:

(Q4) 表索引允许在 O(log n) 时间内查找键 (id)，这意味着每次表的大小增加一倍时，只需要一个额外的步骤就可以在索引中找到键*。如果您有 100 万行，则只需约 20 步即可找到它。十亿行？ 30 个步骤。索引条目包含有关在表中何处查找该行数据的数据，因此 MySQL 会跳转到表中的那个位置并读取该行。为此报告的时间几乎完全是开销。

(Q3) 正如我提到的，表索引非常快；此查询找到第一个条目并遍历树，直到它具有请求的行数。我确信我可以计算出它需要的精确步数，但作为最大值，我们会说 20 步 x 600k 行 = 12M 步；因为它正在遍历一棵树，所以它可能更像是 1M 步，但精确的数字在很大程度上是无关紧要的。这里最重要的一点是，一旦 MySQL 遍历索引以提取它需要的 id，它就会拥有你所要求的一切。没必要去看表。这个报告的时间实质上是 MySQL 遍历索引所花费的时间。

(Q2) 这从与查询 3 讨论的相同的树遍历开始，但是在提取它需要的 ID 时，MySQL 也提取它们在表文件中的位置。然后它必须转到表文件(可能已经在内存中缓存/mmapped)，对于它提取的每个条目，查找表中的正确位置并从中获取请求的字段行。此查询报告的时间是遍历索引(如第 3 季度)所花费的时间加上访问索引中指定的每一行的时间。

(Q1) 当指定所有字段时，这与 Q2 相同。由于时间与第 2 季度基本相同，我们可以看到从数据库中提取更多字段并没有真正花费更多的时间，任何时候爬行索引和查找行都相形见绌。

*:大多数数据库使用索引数据结构(B-trees 用于 MySQL)，其对数基数远高于 2，这意味着每次表翻倍时都不是额外的步骤，而是每次都更像是额外的步骤表的大小增加了数百到数千倍。这意味着我在示例中陈述的不是 20-30 步，而是更像是 2-5 步。