提高MySql EAV查询性能

Question

当我运行这个查询时，平均花费了1.2421秒，我认为这很慢，我在WHERE子句中为每个可能的列添加了索引。所以我还可以做更多的改进来加速这个查询吗？包含大多数数据的表是eav，它有大约111276行/记录。

SELECT SQL_CALC_FOUND_ROWS eav.entid, 
       ent.entname 
FROM   eav, 
       ent, 
       catatt ca 
WHERE  eav.entid = ent.entid 
       AND ent.status = 'active' 
       AND eav.status = 'active' 
       AND eav.attid = ca.attid 
       AND ca.catid = 1 
       AND eav.catid = 1 
       AND ( ca.canviewby <= 6 
             || ( ent.addedby = 87 
                  AND canviewby <= 6 ) ) 
       AND ( ( eav.attid = 13 
               AND ( `char` = '693fafba093bfa35118995860e340dce' ) ) 
              OR ( eav.attid = 3 
                   AND `double` = 6 ) 
              OR ( eav.attid = 45 
                   AND ( `int` = 191 ) ) ) 
GROUP  BY eav.entid 
HAVING Count(*) >= 3 

解释输出

​

catatt表指数

​

eav表索引

​

ent表索引

​

Answer 1

我简化了您的查询以更好地理解它，从where子句中删除了不必要的情况，进行了查询规划。

因此，检查此查询并将其放到注释结果中，让我们在我的回答下调试它：

SELECT 
  SQL_CALC_FOUND_ROWS 
  eav.entid, 
  ent.entname 
FROM   
  eav
INNER JOIN ent ON (eav.entid = ent.entid AND ent.status = 'active')
INNER JOIN catatt ON (eav.attid = catatt.attid AND catatt.catid = 1)
WHERE 
  eav.catid = 1 AND eav.status = 'active' 
  AND (catatt.canviewby <= 6 OR ent.addedby = 87) 
  AND
  ( 
    (eav.attid = 13 AND eav.`char` = '693fafba093bfa35118995860e340dce') 
    OR 
    (eav.attid = 3 AND eav.`double` = 6) 
    OR 
    (eav.attid = 45 AND eav.`int` = 191) 
  ) 
GROUP BY eav.entid
HAVING COUNT(eav.entid) > 2 

• 此外，我还发现您很少使用UPDATE-ing表(数据大多被插入到这些表中)--因此，尝试将这些表的引擎变成MyISAM
• 从下列组合中创建复合索引：attid, char；attid, double；attid, int
• 查看mysql的配置，并对其进行优化，以获得更好的查询缓存和内存使用