第1个回答 2022-07-28
2020-04-21
记录一次sql优化记录：
环境：用的mysql版本 select Version();
优化过程：
用的是两张表联查，四个条件like查询 ，根据时间排序降序
其中A，B表没有大字段，A表20万多数据，B表50万多条数据。语句如下：
EXPLAIN
SELECT A.bondId,A.sname,A.cname,A.secuCode,A. ISSUER,A.guarantor,B.underwriter AS infoSource
FROM A
LEFT JOIN B ON B.bondId = A.bondId
WHERE B.agentType = 1
AND B.underwriter = '有限公司'
AND A.startDate <= '2020-04-21 18:02:10'
AND A.endDate >= '2020-04-21 18:02:10'
AND (
A.cname LIKE '%%' OR A.sname LIKE '%%' OR A.secuCode LIKE '%%'
OR A. ISSUER LIKE '%%'OR A.guarantor LIKE '%%')
AND A.isValid = 1
ORDER BY A.startDate DESC
LIMIT 0, 20
这是2个表都没有加索引的情况，从explain来看结果非常糟糕，都是全表扫描，并且产生临时表同时有文件排序，效率肯定非常低。
首先尝试在B表上建立一个联合索引
可以考虑从关联字段及where条件字段考虑（bondId, underwriter, agentType）
建一个联合索引，试试。
ALTER TABLE B ADD INDEX bua_index(bondId, underwriter, agentType)
再explain看：
可以看到B表用到了我们刚刚建的联合索引，并且额外信息是Using index ，type是ref级别的，效果比较理想，再来看A表。
Where条件中有多个like，这种情况下一般索引都是不可用的，所以必须用覆盖索引解决，
由于又根据startDate排序，所以尝试根据如下字段建立联合索引，同时查询的字段就是索引中的字段（startDate, endDate,cname, sname, secuCode, issuer, guarantor）
ALTER TABLE A ADD INDEX index_scssig(startDate, endDate,cname, sname, secuCode, issuer, guarantor)
再次explain看看效果：
这样乍看上去A表也用到了刚刚建的联合索引，并且type是range级别虽然比ref差点，按理说应该也还可以，但是我执行sql语句，效率还是非常差，查询耗时达到8s，并且偶尔还不止这个时间
究其原因，虽然使用了索引，但是extra里面是Using index condition&Using where
回表操作了，我在想如果将extra优化成Using index效率肯定没问题
故再进一步优化，还是从索引入手
在联合索引上添加2个字段isValid, bondId 再试试
ALTER TABLE A ADD INDEX index_scssig(isvalid,startDate, endDate,cname, sname, secuCode, issuer, guarantor,bondId)
再次explain:
这个结果就是我想要的，然后执行sql看看效率：
已经提升了很多了，但是我试了别的查询条件偶尔时间会到3，4s,怀疑和自己的机器有关
在这这种多个like的or查询mysql本身并不擅长，无奈坑爹的需要需要这样，可能效率并不是非常的高，优化成这样可以接受了。
最近对以前项目的慢查询进行sql调优，感觉性能的下降往往还是sql语句及索引的建立的问题，explain是很有帮助，正确优化还是能极大提升效率的。