我的问题:想象一个有数百万行的表,比如
CREATE TABLE a {
id INT PRIMARY KEY,
column2..,
column3..,
many other columns..
..
INDEX (column2);
以及这样的查询:
SELECT id FROM a WHERE column2 > 10000 LIMIT 1000 OFFSET 5000;
我的问题:mysql 是否只使用索引"column2"(因此主键 id 隐式存储为其他索引中的引用(,还是必须获取所有行才能获取选择输出的 id?在这种情况下,使用声明为的键,查询应该快得多:
INDEX column2(column2, id)
简短回答:不。
长答案:
与InnoDB不同,MyISAM具有指向每个索引的叶节点中数据的"指针",包括该PRIMARY KEY
。
所以,INDEX(col2)
本质上是INDEX(col2, ptr)
. 同上,INDEX(id)
正在INDEX(id, ptr)
.
"指针"是 .MYD 文件(用于DYNAMIC
(或记录编号(用于FIXED
(。 无论哪种情况,指针都会指向 .MYD 文件。
指针默认为 6 字节数字,允许大量行。 它可以通过设置进行更改,以节省空间或允许更多的行数。
对于您的特定查询,INDEX(col2, id)
是最佳且"覆盖"。 它比MyISAM的INDEX(col2)
更好,但它们与InnoDB等效,因为InnoDB在每个二级索引中隐式具有PK。
查询必须至少扫描 5000+1000 行,至少在索引的 BTree 中。
请注意,InnoDB的PRIMARY KEY
与数据聚类,但MyISAM的PRIMARY KEY
是一个单独的BTree,就像其他二级索引一样。
你真的应该考虑迁移到InnoDB;今天几乎没有理由使用MyISAM。
需要column2
索引。您在索引中使用 id 的建议将阻止表扫描,并且应该非常有效。
此外,假设column2
是一个连续序列,这样做会更快:
SELECT id FROM a WHERE column2 > 15000 LIMIT 1000;
这是因为要使用偏移量,它只需要扫描接下来的 5000 条记录(MySQL 没有意识到您实际上是在偏移column2
(。