mysql useing查询_MySQL查询优化一例——也说说 Using intersect

生产上面有一条sql查询很慢，需要7到8秒左右，简化之后的sql如下所示：

SELECT

mingxi.*

FROM

(

SELECT

btjc01.id,

department.`name` AS btjc01011,

btjc01011 AS obj,

btjc01.inibeginmonth AS beginYearMonth,

lsbtsj.btje AS btjc01030,

CASE

WHEN btjc01.inibeginmonth ='2019-10' THEN

'是'

ELSE

'否'

END AS isadd

FROM

btjc01

INNER JOIN department ON department.Id = btjc01.btjc01038

INNER JOIN lsbtsj ON lsbtsj.btrid = btjc01.Id

WHERE

btjc01.btjc01037 = '3'

AND btjc01046 ='江西省'

AND btjc01047 ='九江市'

AND btjc01048 ='修水县'

AND lsbtsj.btqsyf <='2019-10'

AND lsbtsj.btjzyf >='2019-10'

AND beginYearMonth != inibeginmonth

UNION ALL

SELECT

btjc01.id,

department.`name` AS btjc01011,

btjc01011 AS obj,

btjc01.beginYearMonth,

btjc01.btjc01030,

CASE

WHEN btjc01.inibeginmonth ='2019-10' THEN

'是'

ELSE

'否'

END AS isadd

FROM

btjc01

INNER JOIN department ON department.Id = btjc01.btjc01038

INNER JOIN btgg03 ON btgg03.Id = btjc01.btjc01040

WHERE

btjc01.btjc01037 = '3'

AND btjc01046 ='江西省'

AND btjc01047 ='九江市'

AND btjc01048 ='修水县'

AND (beginYearMonth <='2019-10')

) AS mingxi

LIMIT 100,200;

执行计划如下所示：

可以看到 执行计划里面出现了 Using intersect，btjc01046 btjc01048 btjc01037 btjc01047 多个索引来进行数据的过滤。

另外发现一个问题，里面的 union all 子查询 却只需要 0.4秒就可以执行完成：

SELECT

btjc01.id,

department.`name` AS btjc01011,

btjc01011 AS obj,

btjc01.inibeginmonth AS beginYearMonth,

lsbtsj.btje AS btjc01030,

CASE

WHEN btjc01.inibeginmonth ='2019-10' THEN

'是'

ELSE

'否'

END AS isadd

FROM

btjc01

INNER JOIN department ON department.Id = btjc01.btjc01038

INNER JOIN lsbtsj ON lsbtsj.btrid = btjc01.Id

WHERE

btjc01.btjc01037 = '3'

AND btjc01046 ='江西省'

AND btjc01047 ='九江市'

AND btjc01048 ='修水县'

AND lsbtsj.btqsyf <='2019-10'

AND lsbtsj.btjzyf >='2019-10'

AND beginYearMonth != inibeginmonth

UNION ALL

SELECT

btjc01.id,

department.`name` AS btjc01011,

btjc01011 AS obj,

btjc01.beginYearMonth,

btjc01.btjc01030,

CASE

WHEN btjc01.inibeginmonth ='2019-10' THEN

'是'

ELSE

'否'

END AS isadd

FROM

btjc01

INNER JOIN department ON department.Id = btjc01.btjc01038

INNER JOIN btgg03 ON btgg03.Id = btjc01.btjc01040

WHERE

btjc01.btjc01037 = '3'

AND btjc01046 ='江西省'

AND btjc01047 ='九江市'

AND btjc01048 ='修水县'

AND (beginYearMonth <='2019-10')

执行计划如下：

比较一下前面和后面两个的执行计划，可以发现 都是有了 Using intersect 但是呢后面一个却只需要0.4秒，而在外面加上一层 select * from t，时间却需要7到8秒。一直没有明白什么原因。

最后的优化是去掉 Using interset ，使用 force index 手动指定使用索引 btjc01048:

SELECT

mingxi.*

FROM

(

SELECT

btjc01.id,

department.`name` AS btjc01011,

btjc01011 AS obj,

btjc01.inibeginmonth AS beginYearMonth,

lsbtsj.btje AS btjc01030,

CASE

WHEN btjc01.inibeginmonth ='2019-10' THEN

'是'

ELSE

'否'

END AS isadd

FROM

btjc01 force index(btjc01048)

INNER JOIN department ON department.Id = btjc01.btjc01038

INNER JOIN lsbtsj ON lsbtsj.btrid = btjc01.Id

WHERE

btjc01.btjc01037 = '3'

AND btjc01046 ='江西省'

AND btjc01047 ='九江市'

AND btjc01048 ='修水县'

AND lsbtsj.btqsyf <='2019-10'

AND lsbtsj.btjzyf >='2019-10'

AND beginYearMonth != inibeginmonth

UNION ALL

SELECT

btjc01.id,

department.`name` AS btjc01011,

btjc01011 AS obj,

btjc01.beginYearMonth,

btjc01.btjc01030,

CASE

WHEN btjc01.inibeginmonth ='2019-10' THEN

'是'

ELSE

'否'

END AS isadd

FROM

btjc01 force index(btjc01048)

INNER JOIN department ON department.Id = btjc01.btjc01038

INNER JOIN btgg03 ON btgg03.Id = btjc01.btjc01040

WHERE

btjc01.btjc01037 = '3'

AND btjc01046 ='江西省'

AND btjc01047 ='九江市'

AND btjc01048 ='修水县'

AND (beginYearMonth <='2019-10')

) AS mingxi

LIMIT 100,200;

执行时间需要1.2秒左右，和0.4秒相比，还是有差距。执行计划如下；

可以看到没有了 Using interset. 我们强制使用索引 btjc01048.

总结：

如果出现 Using interset 需要注意是否mysql优化器选择的是否是最佳的索引方案。是否可以通过force index来选择更优的索引。