需要关于如何索引和优化特定MySQL数据库的建议

Question

我正在尝试优化我的MySQL数据库，以便我能够尽快查询它。

事情是这样的：

我的DB由一个表组成，该表(目前)大约有1800万行，而且增长很快。该表有以下列- idx、time、tag_id、x、y、z。没有列具有任何空值。

‘'idx’是一个INT(11)索引列，AI和PK。现在按升序排列。

“时间”是一个日期-时间列。它也在上升。表中50%的“时间”值是不同的(其余的值最多可能出现两次或三次)。

'tag_id‘是一个INT(11)列。它没有以任何方式排序，而且在整个DB中有30-100个不同的可能的tag_id值。这也是另一张桌子的外键。

插入-每2-3秒向表插入一个新行.‘'idx’由服务器(AI)计算。因为' time‘列表示插入该行的时间，所以插入的每一个新的“时间”都将高于或等于前一行。所有其他列值都没有任何顺序。

选择-以下是一个典型查询的示例：“选择x，y，z，从表中选择日期(时间)在'2014-08-01‘和'2014-10-01’之间，并且tag_id = 123456”

因此，'time‘和'tag_id’是唯一出现在where部分的列，它们都总是出现在每个查询的where部分。“x”、“y”、“z”和“time”将始终出现在select部分。“tag_id”有时也可能出现在select部分。

查询通常会查找更高(最近的)时间，而不是更旧的时间。意思是-表中稍后的行将被更多地搜索。

索引-现在，作为PK的'idx‘是聚集的ASC索引。“‘time”也有一个非聚集的ASC索引。

就这样。考虑到所有这些数据，一个典型的查询将在30秒内为我返回结果。这次我在试着降低。有什么建议吗？

我正在考虑将一个或两个索引从ASC更改为DESC (因为更高的值在搜索中更受欢迎)。如果我将“idx”更改为DESC，它将从物理上逆转整个表。如果我将“时间”更改为DESC，它将反转“时间”索引树。但是，由于这是一个1800万行表，这样的更改可能需要很长时间才能完成，所以我想确定这是个好主意。问题是，如果我颠倒顺序，插入一个新行，服务器会知道如何快速地将其放在表的开头吗？还是每次都会在桌子上搜索这个地方？在表格的开头放一行是否意味着每次都需要对整个表进行某种类型的数据转换？

或者我只是需要一种不同的索引技巧？任何你有的想法都是非常受欢迎的。谢谢！！

Answer 1

select x, y, z, time from table 
where date(time) between '2014-08-01' and '2014-10-01' and tag_id = 123456

在像date(time)这样的函数调用中放置一个列会破坏为该列使用索引的任何机会。如果要使用索引，则必须仅使用裸列进行比较。

因此，如果要将其与日期进行比较，则应存储一个DATE列。如果您有一个DATETIME列，您可能必须使用如下搜索术语：

WHERE `time` >= '2014-08-01 00:00:00 AND `time` < '2014-10-02 00:00:00' ...

此外，应该在可能的情况下使用多列索引。首先使用相等条件中的列，然后在范围条件中使用一列。有关此规则的更多信息，请参见我的演示文稿如何设计索引，真的。

还可以添加不用于搜索的列，以便查询可以单独从索引项检索列。将这些列放在用于搜索或排序的列后面。这被称为仅索引查询。

因此，对于这个查询，您的索引应该是：

ALTER TABLE `this_table` ADD INDEX (tag_id, `time`, x, y, z);

对于ASC和DESC，语法支持不同方向索引的选项，但是在MySQL、InnoDB和MyISAM中使用的两个最流行的存储引擎中没有区别。排序的两个方向都可以或多或少地使用任意类型的索引。