php - 优化大型 MySQL 数据库的查询

Question

我正在编写一个网站，该网站将存储一些报价(例如工作机会)。最终，它可能包含超过 100 万个优惠。现在我遇到了一些低效 SQL 查询的问题。

场景:

• 每个职位都可以分配到类别(例如 IT 职位)
• 每个类别都有自定义字段(例如，IT 职位可以有“价格”类型的自定义字段，该字段将代表接受数字(价格)的文本框 - 在我们的示例中，假设我们有预期工资的价格输入)
• 每个优惠都存储元数据以及这些类别自定义字段的值
• 用于过滤的数据库字段具有索引

表格类别(我使用嵌套集来存储类别层次结构):

CREATE TABLE `category` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `parent_id` int(11) DEFAULT NULL,
 `lft` int(11) DEFAULT NULL,
 `rgt` int(11) DEFAULT NULL,
 `depth` int(11) DEFAULT NULL,
 `order` int(11) NOT NULL,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `category_parent_id_index` (`parent_id`),
 KEY `category_lft_index` (`lft`),
 KEY `category_rgt_index` (`rgt`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

表格类别_字段:

CREATE TABLE `category_field` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `category_id` int(10) unsigned NOT NULL,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `optional` tinyint(1) NOT NULL DEFAULT '0',
 `type` enum('price','number','date','color') COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`),
 KEY `category_field_category_id_index` (`category_id`),
 CONSTRAINT `category_field_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

桌面报价:

CREATE TABLE `offer` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `text` text COLLATE utf8_unicode_ci NOT NULL,
 `category_id` int(10) unsigned NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `offer_category_id_index` (`category_id`),
 CONSTRAINT `offer_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

表offer_meta:

CREATE TABLE `offer_meta` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `offer_id` int(10) unsigned NOT NULL,
 `category_field_id` int(10) unsigned NOT NULL,
 `price` double NOT NULL,
 `number` int(11) NOT NULL,
 `date` date NOT NULL,
 `color` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `offer_meta_offer_id_index` (`offer_id`),
 KEY `offer_meta_category_field_id_index` (`category_field_id`),
 KEY `offer_meta_price_index` (`price`),
 KEY `offer_meta_number_index` (`number`),
 KEY `offer_meta_date_index` (`date`),
 KEY `offer_meta_color_index` (`color`),
 CONSTRAINT `offer_meta_category_field_id_foreign` FOREIGN KEY (`category_field_id`) REFERENCES `category_field` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `offer_meta_offer_id_foreign` FOREIGN KEY (`offer_id`) REFERENCES `offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=107769 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

当我在页面上设置一些过滤器时(例如，对于我们的工资自定义字段)，我必须从返回可用 Offer_meta 记录中的最低和最高价格的查询开始(我想向前面的用户显示范围 slider ) -结束，所以我需要这个范围的最小/最大值):

select MIN(`price`) AS min, MAX(`price`) AS max from `offer_meta` where `category_field_id` = ? limit 1

我发现这些查询是我所做的所有查询中效率最低的(当 Offer_meta 表有几千条记录时，上述查询需要超过 500 毫秒)。

其他低效查询(offer_meta 有 107k 条记录):

获取 slider 过滤数字的最小值和最大值

select MIN(`number`) AS min, MAX(`number`) AS max from `offer_meta` where `category_field_id` = ? limit 1

获取 slider 的最低和最高价格以按价格过滤

select MIN(`price`) AS min, MAX(`price`) AS max from `offer_meta` where `category_field_id` = ? limit 1

获取日期范围限制的最小和最大日期

select MIN(`date`) AS min, MAX(`date`) AS max from `offer_meta` where `category_field_id` = ? limit 1

获取带有计数的颜色以显示带有数字的颜色列表

select `color`, count(*) as `count` from `offer_meta` where `category_field_id` = ? group by `color`

使用多个过滤条件获取报价计数的完整查询示例(0.5 秒)

select count(*) as count from `offer` where id in (select
                            distinct offer_id
                        from offer_meta om
                        where offer_id in (select
                            distinct offer_id
                        from offer_meta om
                        where offer_id in (select
                            distinct offer_id
                        from offer_meta om
                        where offer_id in (select
                            distinct om.offer_id
                        from offer_meta om
                        join category_field cf on om.category_field_id = cf.id
                        where
                            cf.category_id in (2,3,4,41,43,5,6,7,8,37) and
                            om.category_field_id = 1 and
                            om.number >= 1 and
                            om.number <= 50) and 
                            om.category_field_id = 2 and
                            om.price >= 2 and
                            om.price <= 4545) and 
                            om.category_field_id = 3 and
                            om.date >= '0000-00-00' and
                            om.date <= '2015-04-09') and 
                            category_field_id = 4 and
                            om.color in ('#0000ff'))

没有聚合函数 (COUNT) 的相同查询速度要快几倍(仅获取 ID)。

问题:

是否可以调整这些查询，或者您对如何使用不同的表架构实现我的逻辑(提供类别和在管理中动态添加到每个类别的自定义字段)有什么建议吗？我尝试了更多的方案，但没有成功。

问题2:

你认为这是我的MySQL服务器问题，如果我购买VPS就可以了吗？

帮助更好地理解:

我受到 WordPress 自定义字段架构的强烈启发，因此逻辑是相似的。

最后注释:

此外，我正在开发 Laravel 框架，并且正在使用 Eloquent ORM。抱歉我的英语不好，我希望我能清楚地表达我的问题:-)

预先感谢您，帕特里克

Answer 1

这不是MySql问题。在您的场景中，我们发现了大量的数据收集。当然，关系数据库对于某些查询效率不高。(我遇到了oracle的情况)解决这种情况的做法是使用图数据库。看来你在运动中面临的处境很困难。我听说 Lucene 对大型数据库的索引有某种支持以用于选择目的。我不知道到底该怎么做。 http://en.wikipedia.org/wiki/Lucene