大型数据库的Mysql查询优化

Question

我们有很大的数据库，我们有近两条湖泊记录，而我们尝试使用下面的查询进行搜索需要很长时间才能得到结果

我的查询

SELECT 
      count( DISTINCT e.guid ) AS total
   FROM 
      elgg_entities e
         JOIN elgg_users_entity u 
            ON e.guid = u.guid
         JOIN ( SELECT 
                      subm1.*, 
                      s1.string
                   FROM 
                      elgg_metadata subm1
                         JOIN elgg_metastrings s1 
                            ON subm1.value_id = s1.id ) AS m1 
            ON e.guid = m1.entity_guid
   WHERE 
          m1.name_id = '332'
      AND m1.string LIKE '%96059%'
      AND ( (     e.access_id = -2
              AND e.owner_guid IN ( SELECT guid_one
                                        FROM elgg_entity_relationships
                                        WHERE relationship = 'friend'
                                           AND guid_two =130 )
            )
            OR 
            (     e.access_id IN ( 2, 1, 3, 4, 6, 7 )
              OR ( e.owner_guid = 130 )
              OR (     e.access_id = 0
                   AND e.owner_guid = 130 )
            )
            AND e.enabled = 'yes'
          )

my query explain

编辑，我们在循环中有更多派生查询，因此我需要对@DRapp进行更多优化

回答

SELECT count( DISTINCT e.guid ) AS total
FROM elgg_entities e
JOIN elgg_users_entity u ON e.guid = u.guid
JOIN (

SELECT subm1 . * , s1.string
FROM elgg_metadata subm1
JOIN elgg_metastrings s1 ON subm1.value_id = s1.id
) AS m1 ON e.guid = m1.entity_guid
JOIN (

SELECT subm2 . * , s2.string
FROM elgg_metadata subm2
JOIN elgg_metastrings s2 ON subm2.value_id = s2.id
) AS m2 ON e.guid = m2.entity_guid
WHERE (
(
subm1.name_id = '332'
AND s1.string LIKE '%10001%'
)
AND (
subm2.name_id = '328'
AND s2.string LIKE '%New York%'
)
)
AND (
(
e.access_id = -2
AND e.owner_guid
IN (

SELECT guid_one
FROM elgg_entity_relationships
WHERE relationship = 'friend'
AND guid_two =2336
)
)
OR (
e.access_id
IN ( 2, 1 )
OR (
e.owner_guid =2336
)
OR (
e.access_id =0
AND e.owner_guid =2336
)
)
AND e.enabled = 'yes'
)
AND (
(
subm1.access_id = -2
AND subm1.owner_guid
IN (

SELECT guid_one
FROM elgg_entity_relationships
WHERE relationship = 'friend'
AND guid_two =2336
)
)
OR (
subm1.access_id
IN ( 2, 1 )
OR (
subm1.owner_guid =2336
)
OR (
subm1.access_id =0
AND subm1.owner_guid =2336
)
)
AND subm1.enabled = 'yes'
)
AND (
(
subm2.access_id = -2
AND subm2.owner_guid
IN (

SELECT guid_one
FROM elgg_entity_relationships
WHERE relationship = 'friend'
AND guid_two =2336
)
)
OR (
subm2.access_id
IN ( 2, 1 )
OR (
subm2.owner_guid =2336
)
OR (
subm2.access_id =0
AND subm2.owner_guid =2336
)
)
AND subm2.enabled = 'yes'
)

谢谢

Answer 1

我已经重组了您的查询。一些 where 子句是多余的(相对于 e.owner_guid = 130)，因此删除了多余的元素。

我添加了 MySQL 子句“STRAIGHT_JOIN”来告诉引擎按照表和相应联接提供的顺序执行。我从您的“m1”开始作为第一个预查询，还包括您的“name_id”和“String”限定符的标准。确保您的 elgg_metadata 表在 name_id 列上有索引。另外，由于您没有对元数据或元字符串表中的任何其他列执行任何操作(除了限定条件)，因此我只返回不同的“entity_id”。这应该会为您返回一个快速的小子集。

从该结果中，仅将那些预先限定的实体、用户和关系表连接起来(关系上的左连接，因为这是稍后的“OR”条件)。如果无法在实体 ID 上找到匹配项，请不要再继续下去。

然后，可以应用其余的 OR 标准...如果owner_guid = 130 OR 通过左连接与 IN(子选择)在 eer(关系)中找到，这将是性能 killer ，并且您的最终 OR为 Access_ID。

SELECT STRAIGHT_JOIN
      count( DISTINCT e.guid ) AS total
   FROM
      ( SELECT DISTINCT
              subm1.entity_id
           FROM 
              elgg_metadata subm1
                 JOIN elgg_metastrings s1 
                    ON subm1.value_id = s1.id 
           WHERE
                  subm1.name_id = '332'
              AND s1.string LIKE '%96059%' ) AS m1 

         JOIN elgg_entities e
            ON m1.entity_id = e.guid
            AND e.enabled = 'yes'

            JOIN elgg_users_entity u 
               ON e.guid = u.guid

            LEFT JOIN elgg_entity_relationships eer
               ON e.owner_guid = eer.guid_one
               AND eer.relationship = 'friend'
               AND eer.guid_two = 130
               AND e.access_id = -2
   WHERE
         e.owner_guid = 130
      OR eer.guid_one = e.owner_guid
      OR e.access_id IN ( 2, 1, 3, 4, 6, 7 )