MySQL索引-根据此表和查询的最佳做法是什么

Question

我有这张桌子（500,000行）

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `hash` char(32) NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `link` varchar(255) NOT NULL,
  `short_link` varchar(255) NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `description` mediumtext,
  `content` mediumtext,
  `images` mediumtext,
  `videos` mediumtext,
  `views` int(10) unsigned NOT NULL,
  `comments` int(11) DEFAULT '0',
  `comments_update` int(11) NOT NULL DEFAULT '0',
  `editor_id` int(11) NOT NULL DEFAULT '0',
  `auther_name` varchar(255) DEFAULT NULL,
  `createdby_id` int(10) NOT NULL,
  `createdon` int(20) NOT NULL,
  `editedby_id` int(10) NOT NULL,
  `editedon` int(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `deletedon` int(20) NOT NULL,
  `deletedby_id` int(10) NOT NULL,
  `deletedfor` varchar(255) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `publishedon` int(11) unsigned NOT NULL,
  `publishedby_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `hash` (`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

我正在考虑通过publishedon between x and y（在所有网站中显示仅1个月的记录）进行每个查询

同时，我想publishedon在where子句中添加published, cat_id , source_id

像这样的事情：

SELECT * FROM listings 
WHERE (publishedon BETWEEN 1441105258 AND 1443614458) 
  AND (published = 1) 
  AND (cat_id in(1,2,3,4,5)) 
  AND (source_id  in(1,2,3,4,5))

该查询现在还可以并且没有索引就可以快速进行，但是在尝试使用order by publishedon它时却变得太慢了，所以我使用了该索引

CREATE INDEX `listings_pcs` ON listings(
    `publishedon` DESC,
    `published` ,
    `cat_id` ,
    `source_id`
)

它的工作和order by publishedon变得很快，现在我想order by views喜欢这个

SELECT * FROM listings 
WHERE (publishedon BETWEEN 1441105258 AND 1443614458) 
  AND (published = 1) 
  AND (cat_id in(1,2,3,4,5)) 
  AND (source_id  in(1,2,3,4,5)) 
ORDER BY views DESC

这是

该查询速度太慢的原因，因为ORDER BY views DESC

然后我试图删除旧索引并添加此

CREATE INDEX `listings_pcs` ON listings(
    `publishedon` DESC,
    `published` ,
    `cat_id` ,
    `source_id`,
    `views` DESC
)

它也太慢了

如果我仅使用单一索引publishedon怎么办？在cat_id，source_id，views，publishedon上使用单个索引怎么办？

如果我发现其他索引方法依赖于任何其他列，则可以在一个月内更改像publishedon这样的查询依赖项

有关使指数是什么（cat_id，source_id，publishedon，published）？但在某些情况下，我只会使用source_id？

该表的最佳索引架构是什么

Answer 1

关于为何尽管尝试仍然无法使查询速度更快的一个重要的一般性注意事项DESC是MySQL当前不支持索引。看到这个SO线程，和源从中而来。

在这种情况下，最大的问题是记录的绝对大小。如果引擎决定使用索引实际上并不会更快，那么它就不会。

您有一些选择，并且实际上所有选择都还不错，并且可以帮助您看到显着的进步。

关于SQL的注释

首先，我想简要介绍一下SQL中的索引编制。虽然我认为这不是解决您的难题的方法，但这是您的主要问题，可以为您提供帮助。

通常，这可以帮助我考虑在三个不同的存储桶中建立索引。在 _ 绝对 的 可能 ，而 _从来没有 。您的索引中肯定没有“ 永不” 列中的
任何内容 ，但是有些索引我会考虑“ 也许 ”索引。

绝对 ：这是您的主键和任何外键。这也是您定期引用的任何键，可从您拥有的海量数据中提取一小部分数据。

也许
：虽然您可能会定期引用这些列，但它们本身并没有真正被引用。实际上，通过分析并EXPLAIN按照@Machavity在他的建议中的建议使用，您可能会发现，当这些列用于去除字段时，反正没有那么多字段。对我来说，扎实扎根的那一列就是published列。请记住，每一项都INDEX增加了查询需要完成的工作。

另外： 当您根据两个不同的列定期搜索数据时，复合键是一个不错的选择。以后再说。

选项，选项，选项…

有许多选项可供考虑，每个选项都有一些缺点。最终，我会视具体情况考虑这些因素，因为我认为这些都不是万能的。理想情况下，您将针对当前设置测试一些不同的解决方案，并通过良好的科学测试来查看运行速度最快的解决方案。

1. 将您的SQL表拆分为两个或多个单独的表。

尽管表中有许多列，这是为数不多的几次，我不会急于尝试将表拆分成较小的块。如果你决定把它分割成更小的块，但是，我要说的却是你的[action]edon，[action]edby_id和[action]ed可以很容易地放进另一个表actions：

+-----------+-------------+------+-----+-------------------+----------------+
| Field     | Type        | Null | Key | Default           | Extra          |
+-----------+-------------+------+-----+-------------------+----------------+
| id        | int(11)     | NO   | PRI | NULL              | auto_increment |
| action_id | int(11)     | NO   |     | NULL              |                |
| action    | varchar(45) | NO   |     | NULL              |                |
| date      | datetime    | NO   |     | CURRENT_TIMESTAMP |                |
| user_id   | int(11)     | NO   |     | NULL              |                |
+-----------+-------------+------+-----+-------------------+----------------+

不利的一面是，它不允许您确保只有一个创建日期而没有TRIGGER。好处是，当您按日期排序时，不必对具有那么多索引的列进行排序时。而且，它不仅使您可以对进行排序created，还可以对所有其他操作进行排序。

编辑：根据要求，这是一个示例排序查询

SELECT * FROM listings 
INNER JOIN actions ON actions.listing_id = listings.id
WHERE (actions.action = 'published') 
  AND (listings.published = 1) 
  AND (listings.cat_id in(1,2,3,4,5)) 
  AND (listings.source_id  in(1,2,3,4,5)) 
  AND (actions.actiondate between 1441105258 AND 1443614458)
ORDER BY listings.views DESC

从理论上讲，它应该减少您要进行排序的行数，因为它仅提取相关数据。 我没有像您这样的数据集，所以现在无法测试！

如果在actiondate和上放置组合键listings.id，这将有助于提高速度。

正如我所说，我认为这不是您现在的最佳解决方案，因为我不相信这会为您提供最大的优化。这引出了我的下一个建议：

1. 创建一个月字段

我使用了这个漂亮的工具来确认我对您的问题的理解：您在这里按月排序。您的示例专门针对9月1日至9月30日（含9月30日）。

因此，另一种选择是你的整数功能分成month，day和year领域。您仍然可以拥有时间戳记，但是时间戳记对于搜索而言并不是那么好。EXPLAIN即使在一个简单的查询上运行，您也会自己看到。

这样，您就可以索引月份和年份字段并进行如下查询：

SELECT * FROM listings 
WHERE (publishedmonth = 9)
  AND (publishedyear = 2015) 
  AND (published = 1) 
  AND (cat_id in(1,2,3,4,5)) 
  AND (source_id  in(1,2,3,4,5)) 
ORDER BY views DESC

一拳打EXPLAIN在前面，您应该会看到大量的改进。

因为您打算参考一个月和一天，所以您可能想针对月份和年份添加一个组合键，而不是分别针对这两个键来增加收益。

注意
：我想清楚一点，这不是做事的“正确”方法。这很方便，但是没有规范化。如果您想以正确的方式来做事情，可以改用类似此链接的内容，但是我认为这需要您认真地重新考虑表，并且由于缺乏需要，我还没有尝试过这样的事情，并且坦率地说，会重新梳理我的几何图形。我认为这对您尝试执行的操作有些过大。

1. 在其他地方进行繁重的分拣

这让我难以接受，因为我喜欢尽可能以“ SQL”方式进行操作，但这并不总是最好的解决方案。例如，重型计算最好使用您的编程语言来完成，而让SQL处理关系。

Digg的前CTO使用PHP而不是MySQL进行了排序，性能提高了4,000％。当然，您可能没有扩展到这个级别，因此除非您自己进行测试，否则性能折衷就不会很明显。尽管如此，这个概念还是很合理的：相比之下，数据库是瓶颈，而计算机内存则便宜得多。

毫无疑问，还有更多的调整可以做。这些中的每一个都有缺点，并且需要一些投资。最好的答案是测试其中的两个或多个，看看哪一个可以帮助您获得最大的改善。