史上最慢SQL - 反面案例
先上效果图
十四万多条记录，效果如图，没错，这条sql就是本帅逼写的，反正就是影响效率的条件都用上了。
SQL正文
下面是Mapper层代码，**手动打码。
<!-- 分页查询 **** 短信下发异常列表 -->
<select id="get***SmsSendingExceptionPage" resultType="com.***.rest.models.SmsSendingExceptionModel">
<if test="exConditionType != 2 and exConditionType != 3">
select p.ID as id,
org.ORG_ID AS orgId,
org.NAME as orgName,
p.** as policeName,
p.*_NAME as department,
p.**_NAME as personInCharge,
p.**_NAME as addressee,
p.PHONE as phone,
p.STATUS as exCondition,
p.**_TIME as processingTime,
(CASE WHEN p.PHONE IS NULL OR p.PHONE = '' THEN '0'
WHEN LENGTH( p.PHONE ) != 11 OR p.PHONE LIKE '%[^0-9]%' AND p.PHONE IS NOT NULL AND p.PHONE NOT LIKE '' THEN '1'
END ) AS exConditionType
from *_process p
LEFT JOIN **_org org ON p.ORG_ID = org.ORG_ID
<where>
p.`STATUS` != '9' AND org.`STATUS` != '9'
and (p.PHONE IS NULL OR p.PHONE = '' OR LENGTH( p.PHONE ) != 11 OR p.PHONE LIKE '%[^0-9]%' )
<if test = "orgIds != null and orgIds.size() > 0">
and org.ID in
<foreach collection="orgIds" item="id" index="index" separator="," open="(" close=")">
#{id}
</foreach>
</if>
<if test = "startTime != null and startTime != ''">
and DATE_FORMAT(p.HANDLE_TIME,'%Y-%m-%d') <![CDATA[ >= ]]> DATE_FORMAT(#{startTime},'%Y-%m-%d')
</if>
<if test="endTime != null and endTime != ''">
and DATE_FORMAT(p.HANDLE_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> DATE_FORMAT(#{endTime},'%Y-%m-%d')
</if>
<if test="exConditionType != null and exConditionType != '' and exConditionType == 0">
and (p.PHONE is null OR p.PHONE = '')
</if>
<if test="exConditionType != null and exConditionType != '' and exConditionType == 1">
AND ( p.PHONE IS NOT NULL and p.PHONE != '') and (LENGTH( p.PHONE ) != 11 OR p.PHONE LIKE '%[^0-9]%' )
</if>
</where></if>
<if test="exConditionType != 0 and exConditionType != 1">
<if test="exConditionType == null or exConditionType == ''" >
UNION ALL
</if>
select p.ID as id,
org.ORG_ID AS orgId,
org.NAME as orgName,
p.** as policeName,
p.*** as department,
p.**_NAME as personInCharge,
p.**_NAME as addressee,
p.PHONE as phone,
tmo.ERR_MSG as exCondition,
p.HANDLE_TIME as processingTime,
(CASE
WHEN tmo.ERR_MSG LIKE '%移动%'
OR tmo.ERR_MSG LIKE '%联通%'
OR tmo.ERR_MSG LIKE '%电信%'
OR tmo.ERR_MSG LIKE '%广电%'
OR tmo.ERR_MSG LIKE '%网%'
OR tmo.ERR_MSG LIKE '%运营商%'
OR tmo.ERR_MSG LIKE '%拦截%'
OR tmo.ERR_MSG LIKE '%黑名单%'
THEN '2'
WHEN LENGTH( p.PHONE ) = 11
and tmo.ERR_MSG NOT LIKE '%移动%'
and tmo.ERR_MSG NOT LIKE '%联通%'
and tmo.ERR_MSG NOT LIKE '%电信%'
and tmo.ERR_MSG NOT LIKE '%广电%'
and tmo.ERR_MSG NOT LIKE '%网%'
and tmo.ERR_MSG NOT LIKE '%运营商%'
and tmo.ERR_MSG NOT LIKE '%拦截%'
and tmo.ERR_MSG NOT LIKE '%黑名单%' THEN '3' END) AS exConditionType
from *_process p
LEFT JOIN t*_**_user tmo on tmo.**_ID = p.ID
LEFT JOIN *_org org ON p.ORG_ID = org.ORG_ID
<where>
p.`STATUS` != '9' and org.`STATUS` != '9' and tmo.`STATUS` = '3' and tmo.**_TYPE = '**'
AND LENGTH( p.PHONE ) = 11
<if test = "orgIds != null and orgIds.size() > 0">
and org.ID in
<foreach collection="orgIds" item="id" index="index" separator="," open="(" close=")">
#{id}
</foreach>
</if>
<if test = "startTime != null and startTime != ''">
and DATE_FORMAT(p.HANDLE_TIME,'%Y-%m-%d') <![CDATA[ >= ]]> DATE_FORMAT(#{startTime},'%Y-%m-%d')
</if>
<if test="endTime != null and endTime != ''">
and DATE_FORMAT(p.HANDLE_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> DATE_FORMAT(#{endTime},'%Y-%m-%d')
</if>
<if test="exConditionType == 2">
and (tmo.ERR_MSG LIKE '%移动%'
OR tmo.ERR_MSG LIKE '%联通%'
OR tmo.ERR_MSG LIKE '%电信%'
OR tmo.ERR_MSG LIKE '%广电%'
OR tmo.ERR_MSG LIKE '%网%'
OR tmo.ERR_MSG LIKE '%运营商%'
OR tmo.ERR_MSG LIKE '%拦截%')
</if>
<if test="exConditionType == 3">
AND LENGTH( p.PHONE ) = 11
and tmo.ERR_MSG NOT LIKE '%移动%'
and tmo.ERR_MSG NOT LIKE '%联通%'
and tmo.ERR_MSG NOT LIKE '%电信%'
and tmo.ERR_MSG NOT LIKE '%广电%'
and tmo.ERR_MSG NOT LIKE '%网%'
and tmo.ERR_MSG NOT LIKE '%运营商%'
and tmo.ERR_MSG NOT LIKE '%拦截%'
</if>
</where>
</if>
ORDER BY processingTime desc
</select>
影响速率的条件
影响这条SQL查询速率的条件有：IS NULL、IS NOT NULL、LIKE ‘%*%’、UNION ALL、OR
解决办法
- 改表结构
-- 添加 **记录表 错误类型字段
ALTER TABLE ***_process
ADD COLUMN ***_type TINYINT(1) COMMENT '异常类型：0->未录入，1->号码格式错误，2->运营商问题，3->其他问题';
- 数据分类
-- 未录入
UPDATE ***_process t
left JOIN ***_org o on o.ORG_ID = t.ORG_ID
SET t.***_type = 0 WHERE (t.PHONE IS NULL OR t.PHONE = '' or t.PHONE like ' ') and t.`STATUS` != '9' and o.`STATUS` != '9';
-- 号码格式错误
UPDATE ***_process t
left JOIN ***_org o on o.ORG_ID = t.ORG_ID
SET t.***_type = 1 WHERE t.STATUS != '9' AND
LENGTH( t.PHONE )!= 11 and PHONE IS not null and PHONE not like '' and PHONE not like ' ' and t.`STATUS` != '9' and o.`STATUS` != '9';
-- 运营商问题
UPDATE ***_process t
left JOIN ***_user a ON t.ID = a.**_ID
SET t.***_type = 2 WHERE a.ERR_MSG IS NOT NULL
AND ( a.ERR_MSG LIKE '%移动%'
OR a.ERR_MSG LIKE '%联通%'
OR a.ERR_MSG LIKE '%电信%'
OR a.ERR_MSG LIKE '%广电%'
OR a.ERR_MSG LIKE '%网%'
OR a.ERR_MSG LIKE '%运营商%'
OR a.ERR_MSG LIKE '%拦截%') and t.`STATUS` != '9' and a.`STATUS` != '9';
-- 其他错误
UPDATE ***_process t
left JOIN ***_user a ON t.ID = a.BUSI_ID
SET t.***_type = 3 WHERE a.ERR_MSG IS NOT NULL
AND a.ERR_MSG NOT LIKE '%移动%'
AND a.ERR_MSG NOT LIKE '%联通%'
AND a.ERR_MSG NOT LIKE '%电信%'
AND a.ERR_MSG NOT LIKE '%广电%'
AND a.ERR_MSG NOT LIKE '%网%'
AND a.ERR_MSG NOT LIKE '%运营商%'
AND a.ERR_MSG NOT LIKE '%拦截%' and t.`STATUS` != '9' and a.`STATUS` != '9';
- 添加索引
-- 添加 **记录表 `***_type` 字段索引
ALTER TABLE ***_process ADD INDEX idx_***_type (`***_type`);
-- 添加 **记录表 `STATUS` 字段索引
ALTER TABLE ***_process ADD INDEX idx_status (`STATUS`);
（。。。省略一部分）
最后改成这样
select p.ID as id,
org.ORG_ID AS orgId,
org.NAME as orgName,
p.*** as policeName,
p.*** as department,
p.****_NAME as personInCharge,
p.****_NAME as addressee,
p.PHONE as phone,
p.STATUS as exCondition,
p.HANDLE_TIME as processingTime,
p.***_type AS exConditionType
from ***_process p
LEFT JOIN **_org org ON p.ORG_ID = org.ORG_ID
<where>
p.`STATUS` != '9' AND org.`STATUS` != '9'
<if test = "orgIds != null and orgIds.size() > 0">
and org.ID in
<foreach collection="orgIds" item="id" index="index" separator="," open="(" close=")">
#{id}
</foreach>
</if>
<if test = "startTime != null and startTime != ''">
and DATE_FORMAT(p.HANDLE_TIME,'%Y-%m-%d') <![CDATA[ >= ]]> DATE_FORMAT(#{startTime},'%Y-%m-%d')
</if>
<if test="endTime != null and endTime != ''">
and DATE_FORMAT(p.HANDLE_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> DATE_FORMAT(#{endTime},'%Y-%m-%d')
</if>
<if test="exConditionType == null or exConditionType == ''">
and p.***_type IN ('0','1','2','3')
</if>
<if test="exConditionType != null and exConditionType != ''">
and p.***_type = #{exConditionType}
</if>
</where>
ORDER BY processingTime desc
优化后效果
Nice~解决完问题神清气爽
340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
