优化大型MySQL数据库的查询

我正在编写一个网站，用于存储一些优惠（例如工作机会） . 最后，它可能包含超过1M的报价 . 现在我遇到了一些低效的SQL查询问题 .

场景：

• 每个报价都可以分配到类别中（例如IT工作）

• 每个类别都有自定义字段（例如，IT作业可以包含"price"类型的自定义字段，它将代表文本框接受编号（价格） - 在我们的示例中，假设我们有预期工资的价格输入）

• 每个商品都会使用这些类别自定义字段的值存储元数据

• 将用于过滤的DB字段具有索引

Table category (I'm using nested sets to store categories hierarchy):

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

Table category_field:

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

Table offer:

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

Table 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毫秒） .

Other inefficient queries (offer_meta has 107k records):

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

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

获取滑块的MIN和MAX价格以按价格过滤

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

获取日期范围限制的MIN和MAX日期

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`

Example of full query to get offers count with multiple filter criteria (0.5 sec)

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） .

Question:

是否可以调整这些查询，或者您是否有任何关于如何使用不同的表模式实现我的逻辑（带有在每个类别的admin中动态添加的类别和自定义字段的提供）的建议？我尝试了更多的方案，但没有成功 .

Question 2:

你认为这是我的MySQL服务器问题，如果我购买VPS，它会好吗？

Help to understand even better:

我对自定义字段的WordPress架构有很强的启发，因此逻辑类似 .

Last notes:

此外，我正在使用Laravel框架，我正在使用Eloquent ORM . 对不起，我的英文，我希望我明白我的问题:-)

帕特里克，提前谢谢你