mysql - 对索引键与主键的 sql 查询的性能

Question

我有一个 mysql innodb 表 -

create table data (
    `sha256` CHAR(64) NOT NULL,
    'created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    <some other fields>
    PRIMARY KEY (`sha256`),
)

mysqld_slow_query 中最慢的查询之一是

select * from data where created between "2013-02-01" and "2013-03-01";

为了改进这个查询的执行，我有两个选择:

选项 1:在创建时添加索引

选项 2:将 ('created', 'sha256') 设为主键，并在 sha256 上添加索引。

这里的思路是，当我们选择的行数很多的时候，比如一个月采集的数据，我想减少访问的B-tree blocks的个数。如果我们通过索引访问这些记录(选项 1)，我们仍然可能最终访问每条记录的不同 block 。相反，如果我们将按时间戳排序的记录存储为主键/聚簇键(选项 2)，我们将在同一个 B 树 block 中找到大量记录，这将减少磁盘读取。

但出于某种原因，虽然选项 1 提高了性能，但选项 2 并没有提高多少。任何想法为什么？还有其他建议吗？提前致谢。

Answer 1

InnoDB 对大主键特别敏感，因为它使用聚簇主索引并且 CHAR(64) 会产生非常大的主键。我建议您添加一个 AUTOINCREMENT id 列作为主键，并为 sha256 提供一个唯一索引。那些与 created 上的索引一起应该有助于提高性能。在 sha256 上查找会稍微慢一些，但其他一切都会更快。插入也会更快，因为数据永远不需要通过 sha256 的随机值来回移动。

我不完全确定为什么单个索引会更快，但这可能与复合索引如此之大有关，即使它是聚集索引也是如此。