sql模糊查询优化

❶ sql之模糊查询的四种用法

MySql 使用 like关键字 进行模糊查询，like关键字 通常用在 where条件查询，like关键字 通常配合 %、_、[ ]、[^ ] 使用。

%表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

注意：只能查询连续的文字。【比如“深圳市福田区”可以查询，“深圳市区”查询不出来】

注意：此语句没有那么灵活，比较适用于固定的某个文字的查询。

注意：此语句比较灵活，可以查询相隔几个文字，比较实用。【比如“深圳市福田区小学”可以查询，“深圳市福区小”也可以查询】

表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

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

❸ SQL数据库怎么实现模糊查询

1、首先，我们按下Ctrl+N创建一个SQL查询。

❹ sql优化的N种方法

1.SQL语句中IN包含的值不应过多:
例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了； 实测速度差距不是很大.

2.SELECT语句务必指明字段名称:
禁止用 * 来查询 ,禁止用 * 来查询 ,禁止用 * 来查询 , 查找哪个字段,就写具体的字段.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

3.只查询一条数据的时候，使用limit 1
【这个很有用】

4.避免在where子句中对字段进行null值判断:
【实测：null值的判断依然走了索引】
explain select uid from user_test WHERE phone is null;

5.避免在where子句中对字段进行表达式操作:

6.对于联合索引来说，要遵守最左前缀法则:
例如组合索引(id,name,sex) 使用的时候,可以id 或者id,name . 禁止直接name,或者sex.会导致联合索引失败

注意: id, name,sex 这三个字段填写顺序不会有影响, mysql会自动优化成最左匹配的顺序.

前三条sql都能命中索引,中间两条由于不符合最左匹配原则,索引失效.

最后一条sql 由于有最左索引id 所以索引部分成功,部分失效. id字段索引使用成功.

7.尽量使用inner join，避免left join：
如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
【实测：不是很准确，具体用explain测试】

8.注意范围查询语句:
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

解决办法: 业务允许的情况下,使用 >= 或者<= 这样不影响索引的使用.

explain select * from user_test where uid=10 and name='张三' and phone='13527748096';
explain select * from user_test where uid between( 1 and 10) and name ='张三' and phone='13527748096';

9.不建议使用%前缀模糊查询:
例如 : LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

explain select * from user_test where uid=10 and uid like "%1" ;
explain select * from user_test where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 来连接条件，如果or连接的条件有一方没有索引,将导致引擎放弃使用索引而进行全表扫描
解决办法: 将or连接的双方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='张三';

11.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。(此处存在疑点,我本人测试的时候,发现索引还是能使用到)

12.字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效

❺ sql语句优化有什么方法

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引。

2. 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
   selectidfromtwherenumisnull
   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
   selectidfromtwherenum=0

3. 应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4. 应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
   selectidfromtwherenum=10ornum=20
   可以这样查询：
   selectidfromtwherenum=10
   unionall
   selectidfromtwherenum=20
   

5. in和notin也要慎用，否则会导致全表扫描，如：
   selectidfromtwherenumin(1,2,3)
   对于连续的数值，能用between就不要用in了：
   
   

6. 下面的查询也将导致全表扫描：
   selectidfromtwherenamelike'%abc%'
   

7. 应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   selectidfromtwherenum/2=100
   应改为:
   selectidfromtwherenum=100*2

8. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   selectidfromtwheresubstring(name,1,3)='abc'--name以abc开头的id
   应改为:
   selectidfromtwherenamelike'abc%'
   

9. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
   

10. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
    否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
    

11. 不要写一些没有意义的查询，如需要生成一个空表结构：
    selectcol1,col2into#tfromtwhere1=0
    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
    createtable#t(...)
    

12. 很多时候用exists代替in是一个好的选择：
    selectnumfromawherenumin(selectnumfromb)
    用下面的语句替换：
    selectnumfromawhereexists(select1frombwherenum=a.num)
    

13. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，
    如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。