Mysql慢sql优化案例

一 问题描述

生产有一个这样的慢sql：

SELECT DISTINCT

  m1.meetingid AS meetingid,

  m1.starttime AS START,

  m1.endtime AS END,

m1.title AS title,

j.userid AS uid,

j.type AS TYPE,

m1.cuserid AS cuserid

FROM

  MEETINGINFO m1

  INNER JOIN JOININFO j

    ON (m1.meetingid = j.meetingid)

    AND (

      m1.cuserid IN (192379)

      OR j.userid IN (192379)

    )

    AND m1.dostatus IN (2, 4)

ORDER BY m1.starttime DESC

LIMIT 0, 10

查询需要2.3秒。

MEETINGINFO有三十万条数据，JOININFO有将近98万条数据。

执行计划如下：

 

二 优化思路

看到查询条件里有m1.cuserid IN (192379)，却选择了starttime这个索引来扫描m1，遂查询下该列上是否有索引：

SHOW INDEXES FROM MEETINGINFO #看到没有相关索引

再看下该列是否适合创建索引：

 

重复率不算高，还可以。

尝试在该列上创建索引：

CREATE INDEX ind_cuserid ON MEETINGINFO(cuserid);

发现执行计划依然没选择走该索引，原因有二：

① cuserid是varchar类型的，而sql里192379是数值类型，发生数据类型转换，会导致索引失效

② OR j.userid IN (192379)里的OR会导致无法走索引，需要用UNION ALL来代替OR。

三 解决办法

改写sql：

SELECT DISTINCT   aa.meetingid,aa.start,aa.end,aa.title,aa.uid,aa.type,aa.cuserid 
FROM
  (SELECT 
    m1.meetingid AS meetingid,
    m1.starttime AS START,
    m1.endtime AS  END,
  m1.title AS title,
  j.userid AS uid,
  j.type AS TYPE,
  m1.cuserid AS cuserid 
  FROM
    MEETINGINFO m1 
    INNER JOIN JOININFO j 
      ON (m1.meetingid = j.meetingid) 
      AND (m1.cuserid IN ('192379')) 
      AND m1.dostatus IN (2, 4) 
  UNION  ALL 
  (SELECT 
    m1.meetingid AS meetingid,
    m1.starttime AS START,
    m1.endtime AS 
  END,
  m1.title AS title,
  j.userid AS uid,
  j.type AS TYPE,
  m1.cuserid AS cuserid 
  FROM
    MEETINGINFO m1 
    INNER JOIN JOININFO j 
      ON (m1.meetingid = j.meetingid) 
      AND j.userid IN ('192379') 
      AND m1.dostatus IN (2, 4)
    )
  ) aa 
ORDER BY aa.start DESC LIMIT 0,10 

查询时间从2秒多降到了0.02秒。

执行计划如下：