代码之家 › 专栏 › 技术社区 › Ronak Patel

mysql-无适当索引的长时间运行查询

query-optimization indexing optimization performance mysql

Ronak Patel · 技术社区 · 7 年前

此查询在生产中运行约15个小时,我正在寻找替代方案来改进此查询,

我认为有些改进可能会有所帮助,请参见以下评论:

SELECT  table1.*
    FROM  table1
    WHERE  UPPER(LEFT(table1.cloumn1, 1)) IN ('A', 'B')
      AND  table1.cloumn2 = 'N' /* add composite index for cloumn2,
        column3 */
      AND  table1.cloumn3 != 'Y'
      AND  table1.id IN (
        SELECT  MAX(id)
            FROM  table1
            GROUP BY  column5,column6
                        ) /* move this clause to 2nd after
    where  */
      AND  table1.column4 IN (
        SELECT  column1
            FROM  table2
            WHERE  column2 IN ('VALUE1', 'VALUE2')
              AND  (SUBSTRING(column3,6,1) = 'Y'
                      OR  SUBSTRING(column3,25,1) = 'Y')
                          ) /* move this clause to 1st after
    where  */
      AND  (table1.column5,table1.column6) NOT IN (
        SELECT  column1, column2
            FROM  table3
            WHERE  table3.column3 IN ('A', 'B')/* add index for this column*/
           )
      AND  DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE,
                INTERVAL - 28 DAY)) /* need index  ON this col? */ ;

欢迎提出任何意见/建议。

使现代化 :只需更新筛选顺序,查询性能提高到28秒左右,将在添加一些索引并替换一些子查询到联接后在此处更新

2 回复 | 直到 7 年前

Kickstart 7 年前

假设您可以添加有用的索引(这将有助于您的某些检查),那么可能会尝试尽早排除行。

我怀疑表1中每列5/列6的组合都有相当多的行。如果您可以尽早获得其中每一行的最新信息(即,使用您加入的子查询),那么您可以在需要检查任何非索引WHERE子句之前从表1中排除大多数行。您还可以通过对表3上的子查询执行进一步的联接来排除其中的一些查询。

未经测试,但如果我对数据库结构的假设是正确的,那么这可能是一种改进:-

SELECT table1.* 
FROM 
(
    SELECT MAX(table1.id) AS max_id
    FROM table1 
    INNER JOIN 
    (
        SELECT DISTINCT column1, column2 
        FROM table3
        WHERE table3.column3 IN ('A', 'B')
        AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE, INTERVAL - 28 DAY)
    ) sub0_0
    ON table1.column5 = sub0_0.column1
    AND  table1.column6 = sub0_0.column2
    WHERE (table1.cloumn1 LIKE 'A%' OR table1.cloumn1 LIKE 'B%')
    AND table1.cloumn2 = 'N'
    AND table1.cloumn3 != 'Y'
    GROUP BY table1.column5,
            table1.column6
) sub0
INNER JOIN table1
ON table1.id = sub0.max_id
INNER JOIN
(
    SELECT DISTINCT column1 
    FROM table2
    WHERE column2 IN ('VALUE1', 'VALUE2') 
    AND (SUBSTRING(column3,6,1) = 'Y' 
    OR SUBSTRING(column3,25,1) = 'Y')
) sub1
ON table1.column4 = sub1.column1

Rick James diyism 7 年前

(这可能有助于了解 SHOW CREATE TABLE .)

AND  DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE,
            INTERVAL - 28 DAY))

无法使用索引;这可能相当于:

AND  timstampColumn > CURRENT_DATE - INTERVAL 28 DAY

请提供 EXPLAIN 。

您使用的是什么版本?

它可能(取决于版本)有助于 IN ( SELECT ... ) “派生”表中的子句:

JOIN ( SELECT ... ) ON ...

WHERE (x,y) IN ... 没有很好地优化。它们是什么类型的值?

使用*\u ci排序规则,

UPPER(LEFT(table1.cloumn1, 1)) IN ('A', 'B')

可以做到:

LEFT(table1.cloumn1, 1) IN ('A', 'B')

这对性能没有明显的帮助。最好不要拆开柱子进行测试。

这可能会使用涉及以下内容的索引 cloumn1 :

    table1.cloumn1 >= 'A'
AND table1.cloumn1 <  'C'

事情的顺序和结合很少有关系。中的顺序 INDEX 会有很大的不同。