Mysql - 帮我优化这个查询

Question

关于系统：

-系统共有8个表 - 用户 - Tutor_Details（导师是一种用户，Tutor_Details表链接到用户） - Learning_packs，（存储由导师创建的包） -learning_packs_tag_relations，（保存用于搜索的标签关系） - coachs_tag_relations 和标签以及 订单（包含导师包的购买详细信息）， order_details 链接到订单和 coach_details。

要更清楚地了解所涉及的表格，请查看最后的表格部分。

- 遵循基于标签的搜索方法。当新导师注册和导师创建包时创建标签关系（这使得导师和包可搜索）。有关详细信息，请查看下面的标签在该系统中如何工作？部分。

以下是我试图优化的更复杂查询的更简单表示（不是实际的）：-我在查询中使用了诸如部分解释之类的语句

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

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

​=============================================

以上是什么意思查询做什么？

• 是否对搜索关键字进行 AND 逻辑搜索（本例中为 2 - “民主”和“独裁”）。
• 仅返回两个关键字都存在于两个集合的并集中的导师 - 导师详细信息和导师创建的所有包的详细信息。

为了让事情变得清楚 - 假设一个名为“Sandeepan Nath”的导师创建了一个包“我的第一个包”，那么： -

• 搜索“Sandeepan Nath”会返回 Sandeepan Nath。
• 搜索“Sandeepan first”会返回 Sandeepan Nath。
• 搜索“Sandeepan secondary”不会返回 Sandeepan Nath。

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

问题

返回的结果上面的查询是正确的（并且逻辑按照预期工作），但是在重负载的数据库上查询所花费的时间约为 25 秒，而正常查询时间为 0.005 - 0.0002 秒，这使得它完全无法使用。

由于所有可能的字段尚未建立索引，可能会导致一些延迟，但我希望有一个更好的查询作为解决方案，尽可能优化，显示相同的结果

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

标签在这个系统中如何工作？

• 当导师注册、输入标签并根据导师的详细信息（如姓名等）创建标签关系。
• 当导师创建包时，再次输入标签并根据包的详细信息（如包名称、描述等）创建标签关系。
• 标签关系对于存储在tutorials_tag_relations中的导师和存储在learning_packs_tag_relations中的包。所有单独的标签都存储在标签表中。

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

表格

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

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

Answer 1

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

如果只是查询/索引问题，那么解释计划输出的打印可能会揭示一些原因。还需要确切的查询。

Answer 2

1. 您确实应该为查询使用一些更好的格式。
   只需在每行的开头添加至少 4 个空格即可获得良好的代码格式。

   从某个表中选择 *
       INNER JOIN anothertable ON sometable.id = anothertable.sometable_id
   

   或者看看这里：https://stackoverflow.com/editing-help

2. 你能提供mysql的执行计划吗？您需要将“EXPLAIN”添加到查询中并复制结果。

   解释 SELECT * FROM ...complexquery...
   

   会给你一些有用的提示（执行顺序，返回的行，可用/使用的索引）

Answer 3

您的问题是，“我怎样才能找到匹配某些标签的导师？”这不是一个很难的问题，所以回答这个问题的查询也不难。

类似的东西：

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

如果您修改设计以在“导师”表中包含“标签”字段，其中放置适用于该导师的所有标签，那么这将起作用。它将消除连接层和表层。

所有这些连接和表层是否提供了真正的功能，或者只是更多的编程难题？考虑您的应用程序真正需要的功能，然后简化您的数据库设计！

Answer 4

回答我自己的问题。

这种方法的主要问题是在单个查询中连接了太多表。其中一些表，例如 Tags（具有大量记录 -​​ 将来可以容纳词汇表中的所有英语单词），当与如此多的表连接时，会导致这种乘法效应，这种效应在任何情况下都不会发生。方式予以反击。

解决方案基本上是确保在单个查询中不会进行太多连接。将一个大型连接查询分成多个步骤，将一个查询（涉及某些表上的连接）的结果用于下一个连接查询（涉及其他表上的连接）可以减少乘法效应。

稍后我将尝试对此提供更好的解释。