Sql优化-多like模糊查询及根据时间排序

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是很有帮助，正确优化还是能极大提升效率的。

1、主键就是聚集索引

2、只要建立索引就能显著提高查询速度

3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

　(四)其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

4 、日期列不会因为有分秒的输入而减慢查询速度

(五)其他注意事项

1. 不要索引常用的小型表

2. 不要把社会保障号码(SSN)或身份z号码(ID)选作键

3. 不要用用户的键

4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)

5. 使用系统生成的主键

　二、改善SQL语句

1、Like语句是否属于SARG取决于所使用的通配符的类型

2、or 会引起全表扫描

3、非 *** 作符、函数引起的不满足SARG形式的语句

4、IN 的作用相当与OR

5、尽量少用NOT

6、exists 和 in 的执行效率是一样的

7、用函数charindex()和前面加通配符%的LIKE执行效率一样

8、union并不绝对比or的执行效率高

9、字段提取要按照“需多少、提多少”的原则，避免“select *”

10、count(*)不比count(字段)慢

11、order by按聚集索引列排序效率最高

12、高效的TOP

进行SQL性能优化的方法：

1、SQL语句不要写的太复杂。一个SQL语句要尽量简单，不要嵌套太多层。

2、使用『临时表』缓存中间结果。简化SQL语句的重要方法就是采用临时表暂存中间结果，这样可以避免程序中多次扫描主表，也大大减少了阻塞，提高了并发性能。

3、使用like的时候要注意是否会导致全表扫，有的时候会需要进行一些模糊查询例如：select id from table where username like ‘%hollis%’关键词%hollis%，由于hollis前面用到了“%”，因此该查询会使用全表扫描，除非必要，否则不要在关键词前加%。

4、尽量避免使用!=或<> *** 作符。在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描。

5、尽量避免使用 or 来连接条件；在 where 子句中使用 or 来连接条件，引擎将放弃使用索引而进行全表扫描。可以使用

select id from t where num=10

union all

select id from t where num=20

替代

select id from t where num=10 or num=20

6、尽量避免使用in和not in：在 where 子句中使用 in和not in，引擎将放弃使用索引而进行全表扫描。可以使用

select id from t where num between 10 and 20

替代

select id from t where num in (10,20)

7、可以考虑强制查询使用索引

select * from table force index(PRI) limit 2(强制使用主键)

select * from table force index(hollis_index) limit 2(强制使用索引"hollis_index")

select * from table force index(PRI,hollis_index) limit 2(强制使用索引"PRI和hollis_index")

8、尽量避免使用表达式、函数等 *** 作作为查询条件；尽量避免大事务 *** 作，提高系统并发能力。尽量避免使用游标；任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

9、尽可能的使用 varchar/nvarchar 代替 char/nchar。尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

10、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。

---