如何实现物理排序的表文件组织？

Question

我只是在模式中的一个列上声明一个索引吗？

ALTER TABLE `table` ADD INDEX `index_name` (`colDate`)

我希望不仅在执行ORDER BY查询时对表记录进行物理排序。

我相信最好的做法是在一个日期索引，如果我将持续地查询一个日期范围或关于一个顺序范围的任何查询。我相信这是对的吗？

Answer 1

根据第7章，第148,149页，在Storing Tables in Column-Sorted ORDER书中的

通过使用ALTER TABLE ... ORDER BY语句，您可以控制MySQL如何存储表的物理数据，而不管索引是否到位。

您在理论上是正确的，但是存储引擎将对组织有最终的决定权。我引用的上述声明适用于MyISAM。那InnoDB呢？第149页以

如果表是用InnoDB而不是MyISAM定义的，那么这不会有任何效果: InnoDB总是通过簇键存储数据。

聚集索引本质上是一个rowid索引，正如MySQL文档所说的

如果表没有主键或合适的唯一索引，则InnoDB在包含行ID值的合成列上生成隐藏的聚集索引。行由InnoDB分配给这样一个表中的行的ID排序。行ID是一个6字节的字段，在插入新行时单调地增加。因此，行ID排序的行实际上是按插入顺序排列的。

这就是为什么执行ALTER TABLE ... ORDER BY对InnoDB表没有影响的原因。即使行在物理上被重新排序，行也将被rowid通过集群键访问。

你原来的问题

回顾一下您所做的索引，可以实现以下几点

• 如果您总是输入日期值增加的数据，则索引简化了进行日期范围扫描。
• 如果从较早的日期加载数据，则只有索引才能帮助您进行范围扫描。

物理上重新排序群集键的唯一方法是运行以下命令

CREATE TABLE mytable_new LIKE mytable;
INSERT INTO  mytable_new SELECT * FROM mytable ORDER BY colDate;
DROP TABLE mytable;
ALTER TABLE mytable_new RENAME mytable;

这样做并不符合您的最佳利益，除非这是一次重新排序。我的建议是坚持创建索引，让MySQL做所有必要的繁重工作。此外，您还需要每周运行一次ANALYZE TABLE mytable;，以确保在运行大日期扫描之前获得最新的索引统计信息。

Answer 2

InnoDB表由主键进行物理排序。但是，对于任何引擎，要保证订单，您必须使用ORDER BY子句。