优化大型MySQL数据库上的查询

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

场景：

• 每个职位都可以划分为不同的类别（例如IT工作）
• 每个类别都有自定义字段（例如，IT工作可以有"价格"类型的自定义字段，它将表示接受数字（价格）的文本框-在我们的示例中，假设我们有预期工资的价格输入）
• 每个优惠都存储具有这些类别自定义字段值的元数据
• 将用于筛选的DB字段具有索引

表类别（我使用嵌套集来存储类别层次结构）：

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记录中的MIN和MAX价格（我想在前端向用户显示范围滑块，所以我需要该范围的MIN/MAX值）：

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

我发现这些查询是我所做的所有查询中效率最高的（当offer_meta表有几千条记录时，上面的查询需要超过500毫秒）。

其他低效查询（offer_meta有107k条记录）：

获取滑块的MIN和MAX值以过滤号

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

获取滑块的最小和最大价格以按价格进行过滤

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。对不起我的英语，我希望我把问题说清楚：-）

提前感谢您，Patrik