我目前正在遇到
MySQL的一些性能问题,并尝试提出解决方案.我已经在各种表中添加了一些索引,它似乎已经从查询长度中删除了几百毫秒,但我想知道是否可以优化以下内容：
负责这个的代码非常适合在下面发布,但总的来说：
>简历中有许多期望职业(=职业模型)
>简历有很多过去的职业(=职业模式)
>简历有很多职业技能(=技能模型)
>简历有很多education_skills(=技能模型)
>职业有很多技能
>职业属于概念
>技能属于概念
>概念有很多,属于概念
我知道没有模型这样做有点棘手,但帖子对字符数量有限制.日志中的大多数查询如下所示：
Language Load (0.0ms)
SELECT `languages`.* FROM `languages` WHERE `languages`.`code` = 'en' LIMIT 1 Skill Load (1.0ms) SELECT `skills`.* FROM `skills` INNER JOIN `occupation_skills` ON `skills`.id = `occupation_skills`.skill_id WHERE ((`occupation_skills`.occupation_id = 156))
Concept Load (1.0ms)
SELECT `concepts`.* FROM `concepts` WHERE `concepts`.`id` = 10 LIMIT 1 ConceptLabel Load (1.0ms) SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1
Label Load (1.0ms)
SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1
其中一些直接来自缓存,如下所示：
CACHE (0.0ms)
SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1
CACHE (0.0ms)
SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1
CACHE (0.0ms)
SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1 CACHE (0.0ms) SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1
CACHE (0.0ms)
SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1
CACHE (0.0ms)
SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1
其中最重要的问题是
Label Load (56.0ms)
SELECT `labels`.* FROM `labels` WHERE (`labels`.`id` IN (9909,9888,9855,9822,9900,9867,9834,9912,9891,9879,9846,9813,9870,9858,9825,9903,9882,9837,9804,9894,9861,9849,9816,9873,9840,9828,9796,9906,9885,9852,9807,9897,9864,9831,9819,9876,9843,9810))
然而输出仍然需要很长时间才能满足我的需求：
Rendered static/categorize.html.haml within layouts/application (515.1ms)
Completed 200 OK in 1651ms (Views: 424.0ms | ActiveRecord: 188.0ms)
还有其他的东西不见了,因为,上次我查了188 424ms！= 1651ms …
在运行性能测试时,JMeter需要8秒才能收到正确的响应…
