代码之家 › 专栏 › 技术社区 › Sandeepan Nath

mysql-帮助我优化这个查询

multiple-tables join mysql search optimization

Sandeepan Nath · 技术社区 · 15 年前

关于系统:

-系统共有8个表 -用户 -导师详细信息(导师是一种用户类型,导师详细信息表链接到用户) -学习包(存储导师创建的包) -学习打包标签关系(保留用于搜索的标签关系) -导师标记关系和标记订单(包含导师包的购买详情) 订单详情链接到订单和导师详情。

如需更清楚地了解所涉及的表格,请查看桌子最后的部分。

-采用基于标签的搜索方法。标签关系是在新导师注册和导师创建包时创建的(这使得导师和包可搜索)。有关详细信息,请查看该部分 标签在这个系统中是如何工作的? 下面。

下面是我试图优化的更复杂查询的简单表示形式(而不是实际表示形式):- 我用过这样的陈述 explanation of parts 在查询中

=============================================================

select 

SUM(DISTINCT( t.tag LIKE "%Dictatorship%" )) as key_1_total_matches, 
SUM(DISTINCT( t.tag LIKE "%democracy%" )) as key_2_total_matches,
td.*, u.*, count(distinct(od.id_od)), `if (lp.id_lp > 0) then some conditional logic on lp fields else 0 as tutor_popularity`

from Tutor_Details AS td JOIN Users as u on u.id_user = td.id_user 

LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON td.id_tutor = lptagrels.id_tutor 
LEFT JOIN Learning_Packs AS lp ON lptagrels.id_lp = lp.id_lp 
LEFT JOIN `some other tables on lp.id_lp - let's call learning pack tables set (including 

Learning_Packs table)`

LEFT JOIN Order_Details as od on td.id_tutor = od.id_author LEFT JOIN Orders as o on 

od.id_order = o.id_order 

LEFT JOIN Tutors_Tag_Relations as ttagrels ON td.id_tutor = ttagrels.id_tutor 

JOIN Tags as t on (t.id_tag = ttagrels.id_tag) OR (t.id_tag = lptagrels.id_tag) 

where `some condition on Users table's fields`

AND CASE WHEN ((t.id_tag = lptagrels.id_tag) AND (lp.id_lp > 0)) THEN `some 

conditions on learning pack tables set` ELSE 1 END

 AND CASE WHEN ((t.id_tag = wtagrels.id_tag) AND (wc.id_wc > 0)) THEN `some 

conditions on webclasses tables set` ELSE 1 END

 AND CASE WHEN (od.id_od>0) THEN od.id_author = td.id_tutor and `some conditions on Orders table's fields` ELSE 1 END

 AND ( t.tag LIKE "%Dictatorship%" OR t.tag LIKE "%democracy%")

group by td.id_tutor HAVING key_1_total_matches = 1 AND key_2_total_matches = 1
order by tutor_popularity desc, u.surname asc, u.name asc limit 
0,20

=========================================================

上面的查询是做什么的?

在搜索关键字上搜索does和logic(本例中为2-“民主”和“独裁”)。
仅返回两个关键字同时出现在两个集合中的那些导师-导师详细信息和导师创建的所有包的详细信息。

为了让事情清楚-假设一个名为“sandepan nath”的导师创建了一个包“my first pack”,那么:

搜索“sandepan nath”返回sandepan nath。
搜索“sandepan first”返回sandepan nath。
搜索“sandepan second”不会返回sandepan nath。

===================================================================

问题

上述查询返回的结果是正确的(逻辑按预期工作),但查询在重负载数据库上花费的时间与正常查询时间(0.005-0.0002秒)相比,大约为25秒,这使得它完全不可用。

可能是由于所有可能的字段尚未被索引而导致了一些延迟,但我希望将更好的查询作为解决方案,尽可能优化,显示相同的结果。

===================================================================

标签在这个系统中是如何工作的?

当导师注册时,将输入标签,并根据导师的详细信息(如姓名等)创建标签关系。
当导师创建包时,再次输入标记,并根据包的详细信息(如包名称、描述等)创建标记关系。
存储在导师关系中的导师的标记关系,以及存储在学习关系中的导师的标记关系。所有单个标记都存储在标记表中。

========================================================

桌子

下面的大多数表包含许多其他字段,我在这里省略了这些字段。

CREATE TABLE IF NOT EXISTS `users` (
  `id_user` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `surname` varchar(155) NOT NULL DEFAULT '',
  PRIMARY KEY (`id_user`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=636 ;

CREATE TABLE IF NOT EXISTS `tutor_details` (
  `id_tutor` int(10) NOT NULL AUTO_INCREMENT,
  `id_user` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_tutor`),
  KEY `Users_FKIndex1` (`id_user`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;



CREATE TABLE IF NOT EXISTS `orders` (
  `id_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_order`),
  KEY `Orders_FKIndex1` (`id_user`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=275 ;

ALTER TABLE `orders`
  ADD CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `users` 

(`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION;



CREATE TABLE IF NOT EXISTS `order_details` (
  `id_od` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_order` int(10) unsigned NOT NULL DEFAULT '0',
  `id_author` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_od`),
  KEY `Order_Details_FKIndex1` (`id_order`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=284 ;

ALTER TABLE `order_details`
  ADD CONSTRAINT `Order_Details_ibfk_1` FOREIGN KEY (`id_order`) REFERENCES `orders` 

(`id_order`) ON DELETE NO ACTION ON UPDATE NO ACTION;



CREATE TABLE IF NOT EXISTS `learning_packs` (
  `id_lp` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_author` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_lp`),
  KEY `Learning_Packs_FKIndex2` (`id_author`),
  KEY `id_lp` (`id_lp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;


CREATE TABLE IF NOT EXISTS `tags` (
  `id_tag` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_tag`),
  UNIQUE KEY `tag` (`tag`),
  KEY `id_tag` (`id_tag`),
  KEY `tag_2` (`tag`),
  KEY `tag_3` (`tag`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3419 ;



CREATE TABLE IF NOT EXISTS `tutors_tag_relations` (
  `id_tag` int(10) unsigned NOT NULL DEFAULT '0',
  `id_tutor` int(10) DEFAULT NULL,
  KEY `Tutors_Tag_Relations` (`id_tag`),
  KEY `id_tutor` (`id_tutor`),
  KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tutors_tag_relations`
  ADD CONSTRAINT `Tutors_Tag_Relations_ibfk_1` FOREIGN KEY (`id_tag`) REFERENCES 

`tags` (`id_tag`) ON DELETE NO ACTION ON UPDATE NO ACTION;


CREATE TABLE IF NOT EXISTS `learning_packs_tag_relations` (
  `id_tag` int(10) unsigned NOT NULL DEFAULT '0',
  `id_tutor` int(10) DEFAULT NULL,
  `id_lp` int(10) unsigned DEFAULT NULL,
  KEY `Learning_Packs_Tag_Relations_FKIndex1` (`id_tag`),
  KEY `id_lp` (`id_lp`),
  KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `learning_packs_tag_relations`
  ADD CONSTRAINT `Learning_Packs_Tag_Relations_ibfk_1` FOREIGN KEY (`id_tag`) 

REFERENCES `tags` (`id_tag`) ON DELETE NO ACTION ON UPDATE NO ACTION;

==============================================================

以下是确切的查询(这也包括课程-导师可以创建课程,搜索词与导师创建的课程相匹配):-

SELECT SUM(DISTINCT( t.tag LIKE "%Dictatorship%" )) AS key_1_total_matches,
       SUM(DISTINCT( t.tag LIKE "%democracy%" ))    AS key_2_total_matches,
       COUNT(DISTINCT( od.id_od ))                  AS tutor_popularity,
       CASE
         WHEN ( IF(( wc.id_wc > 0 ), ( wc.wc_api_status = 1
                                       AND wc.wc_type = 0
                                       AND wc.class_date > '2010-06-01 22:00:56'
                                       AND wccp.status = 1
                                       AND ( wccp.country_code = 'IE'
                                              OR wccp.country_code IN ( 'INT' )
                                           ) ), 0)
              ) THEN 1
         ELSE 0
       END                                          AS 'classes_published',
       CASE
         WHEN ( IF(( lp.id_lp > 0 ), ( lp.id_status = 1
                                       AND lp.published = 1
                                       AND lpcp.status = 1
                                       AND ( lpcp.country_code = 'IE'
                                              OR lpcp.country_code IN ( 'INT' )
                                           ) ), 0)
              ) THEN 1
         ELSE 0
       END                                          AS 'packs_published',
       td . *,
       u . *
FROM   tutor_details AS td
       JOIN users AS u
         ON u.id_user = td.id_user
       LEFT JOIN learning_packs_tag_relations AS lptagrels
         ON td.id_tutor = lptagrels.id_tutor
       LEFT JOIN learning_packs AS lp
         ON lptagrels.id_lp = lp.id_lp
       LEFT JOIN learning_packs_categories AS lpc
         ON lpc.id_lp_cat = lp.id_lp_cat
       LEFT JOIN learning_packs_categories AS lpcp
         ON lpcp.id_lp_cat = lpc.id_parent
       LEFT JOIN learning_pack_content AS lpct
         ON ( lp.id_lp = lpct.id_lp )
       LEFT JOIN webclasses_tag_relations AS wtagrels
         ON td.id_tutor = wtagrels.id_tutor
       LEFT JOIN webclasses AS wc
         ON wtagrels.id_wc = wc.id_wc
       LEFT JOIN learning_packs_categories AS wcc
         ON wcc.id_lp_cat = wc.id_wp_cat
       LEFT JOIN learning_packs_categories AS wccp
         ON wccp.id_lp_cat = wcc.id_parent
       LEFT JOIN order_details AS od
         ON td.id_tutor = od.id_author
       LEFT JOIN orders AS o
         ON od.id_order = o.id_order
       LEFT JOIN tutors_tag_relations AS ttagrels
         ON td.id_tutor = ttagrels.id_tutor
       JOIN tags AS t
         ON ( t.id_tag = ttagrels.id_tag )
             OR ( t.id_tag = lptagrels.id_tag )
             OR ( t.id_tag = wtagrels.id_tag )
WHERE  ( u.country = 'IE'
          OR u.country IN ( 'INT' ) )
       AND CASE
             WHEN ( ( t.id_tag = lptagrels.id_tag )
                    AND ( lp.id_lp > 0 ) ) THEN lp.id_status = 1
                                                AND lp.published = 1
                                                AND lpcp.status = 1
                                                AND ( lpcp.country_code = 'IE'
                                                       OR lpcp.country_code IN (
                                                          'INT'
                                                          ) )
             ELSE 1
           END
       AND CASE
             WHEN ( ( t.id_tag = wtagrels.id_tag )
                    AND ( wc.id_wc > 0 ) ) THEN wc.wc_api_status = 1
                                                AND wc.wc_type = 0
                                                AND
             wc.class_date > '2010-06-01 22:00:56'
                                                AND wccp.status = 1
                                                AND ( wccp.country_code = 'IE'
                                                       OR wccp.country_code IN (
                                                          'INT'
                                                          ) )
             ELSE 1
           END
       AND CASE
             WHEN ( od.id_od > 0 ) THEN od.id_author = td.id_tutor
                                        AND o.order_status = 'paid'
                                        AND CASE
             WHEN ( od.id_wc > 0 ) THEN od.can_attend_class = 1
             ELSE 1
                                            END
             ELSE 1
           END
GROUP  BY td.id_tutor
HAVING key_1_total_matches = 1
       AND key_2_total_matches = 1
ORDER  BY tutor_popularity DESC,
          u.surname ASC,
          u.name ASC
LIMIT  0, 20

请注意-提供的数据库结构没有显示此查询中的所有字段和表。

================================================================

解释查询输出: 请看这个屏幕截图 http://www.test.examvillage.com/Explain_query.jpg

4 回复 | 直到 14 年前

Khorkrak 15 年前

有关行计数、值分布、索引、数据库大小、内存大小、磁盘布局(RAID 0、5等)的信息-当查询速度较慢时有多少用户访问您的数据库-其他查询正在运行。所有这些都是性能的因素。

此外,如果只是查询/索引问题,则打印出的“解释计划”输出可能会揭示原因。也需要准确的查询。

Community CDub 8 年前

您真的应该为查询使用一些更好的格式。只需在每行的开头添加至少4个空格,就可以获得这种良好的代码格式。
```
SELECT * FROM sometable
    INNER JOIN anothertable ON sometable.id = anothertable.sometable_id
```
或者看看这里: https://stackoverflow.com/editing-help
你能提供MySQL的执行计划吗?您需要向查询添加“explain”并复制结果。
```
EXPLAIN SELECT * FROM ...complexquery...
```
会给您一些有用的提示(执行顺序、返回行、可用/已用索引)

Summer 15 年前

你的问题是,“我怎样才能找到符合特定标签的导师?”这不是一个困难的问题,所以回答这个问题也不难。

类似:

SELECT *
FROM tutors
WHERE tags LIKE '%Dictator%' AND tags LIKE '%Democracy%'

如果你修改你的设计,在你的“导师”表中有一个“标签”字段,在这个字段中你可以放置所有适用于该导师的标签。它将消除连接层和表。

所有这些连接层和表是否都提供了真正的功能,或者只是更多的编程难题?想想你的应用真正需要的功能,然后简化你的数据库设计!!

Sandeepan Nath 14 年前

回答我自己的问题。

这种方法的主要问题是在一个查询中联接了太多的表。有些桌子 Tags (有大量的记录——将来可以容纳词汇表中所有英语单词的记录)当与如此多的表格结合时,会产生这种无法抵消的乘法效应。

解决方案基本上是确保在一个查询中不进行太多的联接。将一个大型联接查询拆分为多个步骤,使用一个查询(涉及某些表上的联接)的结果进行下一个联接查询(涉及其他表上的联接),可以减少乘法效果。

我稍后会尽力对此作出更好的解释。