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

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;

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,5))

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

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

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

关于为什么您的查询没有得到更快的一个重要的通用注意事项,尽管您尝试的是索引目前不支持MysqL上的DESC.看到这个 SO thread和 the source.

在这种情况下,您最大的问题是您的记录的大小.如果引擎决定使用索引不会更快,那么它不会.

你有几个选择,所有的都是相当不错的,可以帮助你看到显着的改进.

关于sql的注释

首先,我想对sql中的索引进行快速注释.虽然我不认为这是您的困境的解决方案,但这是您的主要问题,可以帮助您.

它通常可以帮助我在三个不同的桶中考虑索引.绝对的,也许,永远不会.你的索引当然没有任何东西在从未列,但有一些我会考虑“可能”的索引.

绝对：这是你的主键和任何外键.您也可以定期参考从您拥有的大量数据中提取一小组数据的任何键.

也许：这些列,虽然你可以定期引用它们,但它们本身并没有被真正引用.事实上,通过分析和使用EXPLAIN作为@Machavity在他的答案中推荐,你可能会发现,在这些列用于删除字段之前,没有那么多字段.对于我来说,这个专栏的一个例子就是公布的专栏.请记住,每个INDEX都会添加您的查询所需的工作.

另外：当您经常搜索基于两个不同列的数据时,复合键是一个不错的选择.稍后再说.

选项,选项,选项…

有很多选择要考虑,每个都有一些缺点.最终我会逐案考虑这些,因为我没有看到这些是一个银弹.理想情况下,您将根据当前设置测试一些不同的解决方案,并使用一个很好的科学测试来查看哪个解决方案最快.

>将sql表拆分成两个或多个单独的表.

这是少数几个之一,尽管表中的列数,我不会急于尝试将您的表分成更小的块.但是,如果您决定将其拆分成较小的块,我会认为您可以轻松地将[action] edon,[action] edby_id和[action]

+-----------+-------------+------+-----+-------------------+----------------+
| 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.有利的是,当您按日期排序时,您无需排列具有尽可能多的索引的列数.此外,它允许您排序不仅可以创建,而且还可以通过所有其他操作进行排序.

编辑：根据请求,这里是一个抽样排序查询

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,5)) 
  AND (listings.source_id  in(1,5)) 
  AND (actions.actiondate between 1441105258 AND 1443614458)
ORDER BY listings.views DESC

理论上,它应该减少你排序的行数,因为它只是拉取相关数据.我没有像你的数据集,所以我现在不能测试！

如果您将复合键放在actiondate和listing.id上,这应该有助于提高速度.

正如我所说,我不认为这是你现在最好的解决方案,因为我不相信它会给你最大的优化.这导致我的下一个建议：

>创建一个月字段

我使用this nifty tool来确认我以为我理解你的问题：你在这里按月排序.您的示例特别在9月1日至9月30日期间.

因此,另一个选项是将您的整数函数拆分为月,日,年字段.您仍然可以拥有您的时间戳,但时间戳并不适合搜索.运行一个EXPLAIN甚至一个简单的查询,你会看到自己.

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

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

在前面拍一个EXPLAIN,你应该看到很大的改进.

因为您计划参考一个月和一天,您可能需要针对月和年添加复合键,而不是单独添加一个复合键,以增加增益.

注意：我想清楚,这不是“正确”的做事方式.方便,但非规范化.如果你想要正确的方法来做事情,你会适应像this link这样的一些事情,但是我认为这将需要你认真地重新考虑你的桌子,而且我没有这样做,没有必要,坦率地说,将会,刷上我的几何.我认为这是一个有点过分的你想做什么.

>在别的地方做你的重排序

这对我来说很难,因为我喜欢尽可能地做“sql”的方式,但这并不总是最好的解决方案.例如,重型计算最好使用您的编程语言,让sql处理关系.

Digg的前CTO使用PHP而不是MysqL进行排序,并获得了4,000% performance increase.当然,您可能不会扩展到此级别,因此除非您自己测试,否则性能权衡将不会被清除.然而,这个概念是健全的：数据库是瓶颈,电脑内存比较便宜.

无疑可以做更多的调整.这些都有一个缺点,需要一些投资.最好的答案是测试两个或更多的这些,看看哪一个可以帮助你获得最大的改进.