MySQL查询优化一例——也说说 Using intersect
2024-09-01 01:42:17
生产上面有一条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来选择更优的索引。
最新文章
- c语言完成宽带拨号
- Linux gcc 编译日记
- 【Android】AppCompat V21:将 Materia Design 兼容到5.0之前的设备
- php final static const成员属性用法
- 51nod1421 最大MOD值
- F1
- 基础数据结构 之 栈(python实现)
- 也可以看看GCD(杭州电2504)(gcd)
- 如何获得android手机通讯录的字母显示(两)
- Java学习笔记-枚举类型
- PrintWriter write返回数据显示中文变问号";???";
- 如何引入iconfont图标与Element-UI组件
- kylin cubing algorithm(算法)
- leetcode每日刷题计划-简单篇day4
- postgresql 9源码安装
- eclipse安装properties插件
- RN中关于IOS和Android的相关权限的问题
- Application类
- sql练习(针对Mysql)
- iOS开发总结
热门文章
- 【Spring AOP】AOP介绍(一)
- 洛谷 P2357 守墓人
- c++中#ifndef ... 与#pragma once的区别
- SWA2G422&;485JK2G基础篇: STM32+W5500实现MQTT通信控制,485/422透传通信
- itms-services 方式安装ipa 无法连接到网址(eg. 我用的ip:172.26.167.82)
- .NET Core:中间件
- LeetCode 118:杨辉三角 II Pascal&#39;s Triangle II
- scrapy 使用
- 使用jedis操作redis常用方法
- KVM虚拟机网络配置 Bridge方式,NAT方式