对大型MySQL数据库查询的优化
对大型MySQL数据库查询的优化
提问于 2015-03-02 04:07:26
我正在编码一个网站，将存储一些优惠(前。(招聘)。最终，它可能包含超过100万份报价。现在，我遇到了一些效率低下的SQL查询的问题。
设想情况：
- 每一种报盘都可以分为类别(例如。资讯科技工作)
- 每个类别都有自定义字段(例如。IT作业可以有类型为" price“的自定义字段，它将表示文本框接受数字(价格)--在我们的示例中，假设我们输入了预期薪资的价格)
- 每个选项都存储具有这些类别自定义字段的值的元数据。
- 将用于筛选的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_ciTable 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表报价：
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_ciTable 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和最大值的查询开始(我想在前端向用户显示一个范围滑块，因此我需要这个范围的MIN/MAX值)：
select MIN(`price`) AS min, MAX(`price`) AS max from `offer_meta` where `category_field_id` = ? limit 1我发现这些查询在我所做的所有查询中效率最高(当offer_meta表有几千条记录时，上面的查询需要超过500 of )。
其他低效查询(offer_meta有107 k记录)：
获取滑块的最小值和最大值以过滤数字
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)。
问题：
是否有可能调整这些查询，或者您对如何用不同的表模式实现我的逻辑(在admin中动态添加了类别和自定义字段)有任何建议吗？我尝试了很少的计划，但没有成功。
问题2:
您认为这是我的MySQL服务器的问题，如果我购买VPS，它会好吗？
有助于更好地理解：
对于自定义字段，我受到WordPress模式的强烈启发，因此逻辑是相似的。
最后注：
另外，我正在研究Laravel框架，我使用的是雄辩的ORM。对不起我的英语，我希望我把我的问题说清楚:-)
预先谢谢你，帕特里克
回答 1
Stack Overflow用户
发布于 2015-03-02 04:46:07
这不是一个MySql问题。在您的场景中，我们发现了大量的数据收集。当然，关系数据库对于某些查询是不有效的。(我在oracle中遇到了一种情况)，赢得这种情况的实践是使用图形数据库。你在运动中所面临的情况似乎很困难。我听说Lucene支持索引大型数据库以供选择用途。我不知道具体是怎么做的。http://en.wikipedia.org/wiki/Lucene
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28802571
复制相关文章
相似问题
腾讯云开发者
