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 个月的记录)

同时，我想在where子句中添加publishedon 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

这个查询:

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));

仅用索引很难优化。最好的索引是以 published 开头然后有其他列的索引——不清楚它们的顺序应该是什么。原因是因为除了 published 之外的所有内容都没有使用 =。

因为您的性能问题与排序有关，这表明返回了很多行。通常，索引用于满足 WHERE 子句，然后才能将索引用于 ORDER BY。这使得它很难优化。

建议。 . .都不是那么好:

• 如果您要按月访问数据，则可以考虑按月对数据进行分区。这将使没有 ORDER BY 的查询更快，但对 ORDER BY 没有帮助。
• 在索引中 published 后尝试各种列顺序。您可能会找到最具选择性的列。但是，这再次加快了排序前的查询速度。
• 考虑如何构建查询以在 WHERE 子句中包含更多相等条件或返回较小的数据集。
• (不推荐)在published 和排序列上建立索引。然后使用子查询来获取数据。将不等式条件(IN 等)放在外部查询中。子查询将使用索引进行排序，然后过滤结果。

不推荐最后一个的原因是因为 SQL(和 MySQL)不保证子查询结果的顺序。然而，因为 MySQL 实现了子查询，所以结果确实是有序的。我不喜欢使用未记录的副作用，这些副作用会因版本而异。