SQL优化之索引篇

项目背景

临近下班时间，领导突然反馈了一个问题，测试环境接口查询的结果为null，当时以为是依赖的服务在重启，决定去看一下日志，因为日志不太规范，找了很久很久没找到地方，结果打开了依赖服务的日志，发现有请求结果，并且有返回结果的，然后试了几次请求之后，发现有时有数据，有时没有。最后排查是因为网关层限制了超时时间为5秒，接口查询超过了5秒，果断嘲讽一下 ，这什么sql居然要5秒，然后把sql抓出来，准备去叼那个人时，领导突然说，这个接口是我们团队负责的，果然在心理暗骂前同事，然后只能无奈的去吧sql优化一下，记一下SQL优化过程

使用EXPLAIN关键字

EXPLAIN SELECT
	sn.id AS id,
	sn.title AS title,
	sn.content AS content,
	sn.content_txt AS contentTxt,
	sn.product_id AS productId,
	sn.des_identitys AS desIdentitys,
	sn.first_type_code AS firstTypeCode,
	sn.second_type_code AS secondTypeCode,
	sn.is_push AS isPush,
	sn.des_terminal_code AS desTerminalCode,
	sn.source_terminal_code AS sourceTerminalCode,
	sn.pub_date AS pubDate,
	sn.custom_msg AS customMsg,
	sn.create_date AS createDate,
	sn.link AS link,
	sn.trigger_pub_date AS triggerPubDate,
	sin.read_flag AS readFlag,
	sin.read_date AS readDate,
	sin.receive_code AS receiveCode,
	son.send_code AS sendCode,
	son.identity AS identity,
	son.identity_name AS identityName,
	son.org_code AS orgCode 
FROM
	notice sn
	INNER JOIN notice_out son ON son.notice_id = sn.id
	LEFT JOIN notice_in sin ON sin.notice_id = son.notice_id 
WHERE
	sn.STATUS != 2 
	AND sin.read_flag IS NOT NULL 
	AND sin.STATUS != - 1 
	AND sn.first_type_code IN ( 6 ) 
	AND sin.receive_code =  '634687356619853824' 
	AND sn.des_terminal_code LIKE concat( concat( '%','A02'), '%' ) 
ORDER BY
	sn.create_date DESC 
	LIMIT 0,12

已经优化了一下，不好去删除了，然后发现 这个索引的的key为空的，然后明显没有走索引，添加join字段的索引

优化结果

结果果断的变慢了，真好，慢慢来，又可以去吹我优化了sql了