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

关于为什么您的查询没有得到更快的一个重要的通用注意事项,尽管您尝试的是索引目前不支持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,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和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,2,3,4,5))

AND (source_id in(1,2,3,4,5))

ORDER BY views DESC

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

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

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

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

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

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

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