MYSQL 值差优化

Question

大家好，

我正在运行一个非常大的数据库(ATM，例如超过 500 万个数据集)。我的数据库存储自定义生成的数字(这些数字以及它们的组成方式在这里并不重要)以及与该数字相对应的日期。此外，每个产品都会存储一个ID(意味着一个产品可以在我的数据库中具有不同日期的多个条目 ->主键被划分)。现在我想SELECT那些在过去两天内数字差异最大的前 10 个 ID。目前，我尝试使用 JOINS 来实现这一点，但由于我获得了那么多数据集，这种方式速度太慢了。我怎样才能加快整个操作的速度？

SELECT 
  d1.place,d2.place,d1.ID 
FROM 
  daily 
INNER JOIN 
  daily AS d1 ON d1.date = CURDATE() 
INNER JOIN 
  daily as d2 ON d2.date = DATE_ADD(CURDATE(), INTERVAL -1 DAY) 
ORDER BY 
  d2.code-d1.code LIMIT 10

编辑:这就是我的结构的样子

CREATE TABLE IF NOT EXISTS `daily` (
 `ID` bigint(40) NOT NULL,
 `source` char(20) NOT NULL,
 `date` date NOT NULL,
 `code` int(11) NOT NULL,
 `cc` char(2) NOT NULL,
 PRIMARY KEY (`ID`,`source`,`date`,`cc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这就是Explain语句的输出

    id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  d1  ALL PRIMARY NULL    NULL    NULL    5150350 Using where; Using temporary; Using filesort
1   SIMPLE  d2  ref PRIMARY PRIMARY 8   mytable.d1.ID   52  Using where

Answer 1

这个怎么样？

SELECT
  d1.ID, d1.place, d2.place
FROM
  daily AS d1
CROSS JOIN
  daily AS d2
USING (ID)
WHERE
  d1.date = CURDATE()
  AND d2.date = CURDATE() - INTERVAL 1 DAY
ORDER BY
  d2.code - d1.code DESC
LIMIT
  10

<小时/>

关于表结构的一些想法。

`ID` bigint(40) NOT NULL,

为什么选择 BIGINT？您需要一年、每周 7 天、每天 24 小时每秒执行 136 次插入，才能耗尽 INT 的范围。在您完成一半之前，您的应用程序可能需要一位专业的 DBA。请记住，较小的主索引会导致较长的查找 - 这使我们:

PRIMARY KEY (`ID`,`source`,`date`,`cc`)

为什么？ ID 列上的单列 PK 应该足够了。如果您需要其他列上的索引，请创建其他索引(并且明智地创建它)。事实上，您基本上拥有整个表的覆盖索引...这就像索引中包含整个表一样。

最后但并非最不重要的一点:place 列在哪里？你在你的查询中使用过它(然后我在我的查询中使用过它)，但它却无处可见？

建议的表结构:

CREATE TABLE IF NOT EXISTS `daily` (
 `ID` int(10) UNSIGNED NOT NULL,  --usually AUTO_INCREMENT is used as well,
 `source` char(20) NOT NULL,
 `date` date NOT NULL,
 `code` int(11) NOT NULL,
 `cc` char(2) NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `ID_date` (`ID`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;