精华内容
下载资源
问答
  • Sql性能优化LIKE模糊查询
    千次阅读
    2017-09-06 17:25:13

    我们在写sql的时候应该尽量避免在一个复杂查询里面使用 LIKE ‘%parm1%’—— 由于parm1前面用到了“%”,因此该查询必然走全表扫描,导致相关列的索引无法使用,除非必要,否则不要在关键词前加%,
    如果后台逻辑必须使用’%parm1%’,可以考虑以下解决办法:

    1. 修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了。
    2. 直接修改后台——根据输入条件,先查出符合条件的供应商,并把相关记录保存在一个临时表里头,然后再用临时表去做复杂关联
      例如:
      select * from 商品表 where name like 'Java%'
      这是个模糊查询,会用到索引,请大家记住,用like进行模糊查询时,如果第一个就是模糊的匹配符,比如wherenamelike‘%java’,那么在查询时不会走索引。在其他情况下,不论用了多少个%,也不论%的位置,只要不出现在第一个位置,那么都能用到索引。
    更多相关内容
  • SQL语句优化like优化+性能优化

    千次阅读 2020-06-16 13:22:09
    开头和结尾都是%,对不起,很难优化 SQL> select * from test_like where object_name like '%%'; 以常量结束,直接写的时候是不能应用index的 SQL> select * from test_like where object_name like '.

    常量开头的like , 会利用index ,没问题

    SQL> select * from test_like where object_name like 'AS%';


    开头和结尾都是%,对不起,很难优化

    SQL> select * from test_like where object_name like '%%';

     

    以常量结束,直接写的时候是不能应用index的


    SQL> select * from test_like where object_name like '%S';
     

    以常量结束的,加个reverse 函数,又可以用上index了


    SQL> select * from test_like where reverse(object_name)like reverse('%AS');

     

    展开全文
  • 主要介绍了SQL大量数据查询的优化及非用like不可时的处理方案,需要的朋友可以参考下
  • sql优化like模糊查询【亲测】

    万次阅读 2018-02-22 20:21:09
    一、工作心得:优化也好,升级也罢,做web开发,安全重于泰山。只有数据安全了,才可以谈优化。 二、关于索引: Oracle B-tree、位图、全文索引三大索引性能比较及优缺点罗列一下 1、B-Tree索引 场合:非常适合...

    一、工作心得:优化也好,升级也罢,做web开发,安全重于泰山。只有数据安全了,才可以谈优化。

    二、关于索引

    Oracle B-tree、位图、全文索引三大索引性能比较及优缺点罗列一下

    1、B-Tree索引

    场合:非常适合数据重复度低的字段 例如 身份证号码  手机号码  QQ号等字段,常用于主键 唯一约束,一般在在线交易的项目中用到的多些。

    原理:一个键值对应一行(rowid)  格式: 【索引头|键值|rowid】

    优点:当没有索引的时候,oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的,当数据量很大的时候简直会让人崩溃,那么有个B-tree索引我们就像翻书目录一样,直接定位rowid立刻就找到了我们想要的数据,实质减少了I/O操作就提高速度,它有一个显著特点查询性能与表中数据量无关,例如 查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。

    当我们的字段中使用了主键or唯一约束时,不用想直接可以用B-tree索引

    缺点:不适合键值重复率较高的字段上使用

    2、位图索引   Bitmap index
    场合:列的基数很少,可枚举,重复值很多,数据不会被经常更新
    原理:一个键值对应很多行(rowid), 格式:键值  start_rowid   end_rowid  位图
    优点:OLAP 例如报表类数据库 重复率高的数据 特定类型的查询例如countorand等逻辑操作因为只需要进行位运算即可得到我们需要的结果
    缺点:不适合重复率低的字段,还有经常DML操作(insertupdatedelete),因为位图索引的锁代价极高,修改一个位图索引段影响整个位图段,例如修改
    一个键值,会影响同键值的多行,所以对于OLTP 系统位图索引基本上是不适用的

    3、全文索引 Text index
    定义:全文索引就是通过将文字按照某种语言进行词汇拆分,重新将数据组合存储,来达到快速检索的目的
    场合:当字段里存储的都是文本时适合用全文索引,常用于搜索文字
    优点:全文索引不是按照键值存储的,而是按照分词重组数据,常用于模糊查询Where name like '%leonarding%'效率比全表扫描高很多,适用OLAP系统,
    OLTP系统里面用到的并不多。
    缺点:全文索引会占用大量空间有时比原表本身占的空间还多,bug较多,维护困难。

    4、之前整理的笔记:

    sql语句中用来实现模糊查询,可以在where了句中使用like来达到模糊查询的效果。
    可以使用两个通配符: % 零个或多个字符 _ 单一任何字符(下划线) ------------------------------where a.upload_path like '%电子版档案_';筛选含有电子版档案的数据

    SQL> select * from emp2 where job like '%RE%' and ename like '%A%' and mgr like '%3%';

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

    Executed in 1.859 seconds

    如上所示,LIKE查询一次,就走一次全表扫描,效率非常慢

    同样的效果,现在来换做INSTR函数来执行

    SQL> select * from emp where instr(job,'RE')>0 and instr(ename,'A')>0 and instr(mgr,'3')>0;

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

    Executed in 0.063 seconds

    看到了吧,,时间上的差异很明显,INSTR在一瞬间执行完成,因为这个是查找的字段,而非走全表扫描

    看来,oracle 内部函数效率还是高些。

    因此,大家以后碰到同样的问题,除了全文检索外,这个也是个好方式

    注意:

        select   id, name from users where instr(id, '101') > 0;  

             等价于

              select   id, name from users where id like '%101%'

     

    于德晓测试结果:

     

     
    1. select * from gt_alarm where alarm_date like '%2016/4/3%'; --11s
    2. select * from gt_alarm where instr(alarm_date,'2016/4/3')>0; --9s

            select * from gt_alarm where alarm_person like '%xiuxianxi%';--11s

           select * from gt_alarm where instr(alarm_person,'xiuxianxi')>0;--8s

    通过结果,管中窥豹,可见一斑。

    展开全文
  • 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:1、EXPLAIN做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:type列,连接类型。一个...

    本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:

    1、EXPLAIN

    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。

    下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

    key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。

    key_len列,索引长度。

    rows列,扫描行数。该值是个预估值。

    extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。

    2、SQL语句中IN包含的值不应过多

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。

    3、SELECT语句务必指明字段名称

    SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。

    4、当只需要一条数据的时候,使用limit 1

    这是为了使EXPLAIN中type列达到const类型

    5、如果排序字段没有用到索引,就尽量少排序

    6、如果限制条件中其他字段没有索引,尽量少用or

    or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

    7、尽量用union all代替union

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。

    8、不使用ORDER BY RAND()

    select id from `dynamic` order by rand() limit 1000;

    上面的SQL语句,可优化为:

    select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

    9、区分in和exists、not in和not exists

    select * from 表A where id in (select id from 表B)

    上面SQL语句相当于

    select * from 表A where exists(select * from 表B where 表B.id=表A.id)

    区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

    关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句?

    原SQL语句:

    select colname … from A表 where a.id not in (select b.id from B表)

    高效的SQL语句:

    select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

    取出的结果集如下图表示,A表不在B表中的数据:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    10、使用合理的分页方式以提高分页的效率

    select id,name from product limit 866613, 20

    使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

    优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法:

    select id,name from product where id> 866612 limit 20

    11、分段查询

    在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。

    如下图这个SQL语句,扫描的行数成百万级以上的时候就可以使用分段查询:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    12、避免在where子句中对字段进行null值判断

    对于null的判断会导致引擎放弃使用索引而进行全表扫描。

    13、不建议使用%前缀模糊查询

    例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

    那如何查询%name%?

    如下图所示,虽然给secret字段添加了索引,但在explain结果并没有使用:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    那么如何解决这个问题呢,答案:使用全文索引。

    在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。

    创建全文索引的SQL语法是:

    ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

    使用全文索引的SQL语句是:

    select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);

    注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

    14、避免在where子句中对字段进行表达式操作

    比如:

    select user_id,user_project from user_base where age*2=36;

    中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:

    select user_id,user_project from user_base where age=36/2;

    15、避免隐式类型转换

    where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。

    16、对于联合索引来说,要遵守最左前缀法则

    举列来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。

    17、必要时可以使用force index来强制查询走某个索引

    有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

    18、注意范围查询语句

    对于联合索引来说,如果存在范围查询,比如between、>、

    19、关于JOIN优化

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    LEFT JOIN A表为驱动表,INNER JOIN MySQL会自动找出那个数据少的表作用驱动表,RIGHT JOIN B表为驱动表。

    注意:

    1)MySQL中没有full join,可以用以下方式来解决:

    select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

    2)尽量使用inner join,避免left join:

    参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

    3)合理利用索引:

    被驱动表的索引字段作为on的限制字段。

    4)利用小表去驱动大表:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    从原理图能够直观的看出如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数。

    5)巧用STRAIGHT_JOIN:

    inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确。

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    这个方式有时能减少3倍的时间。

    客服微信:(id1234562011)本文链接:https://www.changchenghao.cn/n/174426.html

    版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 394062665@qq.com 举报,一经查实,本站将立刻删除。

    展开全文
  • MySql基础知识总结(SQL优化篇)

    万次阅读 多人点赞 2021-07-10 12:26:26
    2、添加索引 3、更改索引顺序 4、去掉in 5、小结 四、双表sql优化 1、建表语句 2、左连接查询 3、小结 五、避免索引失效的一些原则 六、一些其他的优化方法 1、exist和in 2、order by 优化 七、sql顺序 -> 慢日志...
  • 适合数据大于几百万条的优化1:首先要在该属性上面建立索引(此时需要考虑索引碎片的处理,否则后期会很卡)2:sql语句的写法:select * form 表 where REVERSE(属性值) like '值%'(注意:传过来的值要先反转,比如...
  • like查询中的SQL注入

    千次阅读 2021-01-19 17:10:45
    看《ibatis in action》,里面提到了使用like进行模糊查询的时候,会有注入漏洞。举例说明如下:Xml代码select*from tbl_schoolwhere school_name like '%$name$%'Java代码public List getSchoolByName(String name)...
  • 大聪明教你学Java | 谈谈SQL优化

    万次阅读 2021-11-25 09:25:59
    SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至某些公司招聘时,在岗位职能上都有明码标注,这也就更证明了掌握SQL优化技能的重要性,借此机会就和大家分享一下我在优化SQL时的一些经验心得。 小弟献丑啦,嘿嘿...
  • mysql 模糊查询like优化方案(亲测)

    千次阅读 2021-11-18 18:44:24
    select * from message_1 where content like "今天不开心%”; 查询包含“今天不开心”的聊天记录,是不能走索引的。 select * from message_1 where content like "%今天不开心%"; 咱们主要优化的是第二种情况,...
  • sql优化的几种方式

    万次阅读 多人点赞 2018-11-05 10:20:46
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也...二、SQL优化的一些方法 1.对查询进行优化,应尽量避免全表扫描...
  • SQL优化(一)、sql优化一般步骤

    万次阅读 多人点赞 2018-10-20 16:17:13
    sql优化一般步骤概要: 1 通过 show status 命令了解各种sql的执行频率 2 定位执行效率较低的sql语句 3 通过explain分析低效sql的执行计划 4 通过 show profile 分析sql 5 通过trace分析 优化器 如何选择执行...
  • mysql like性能优化

    万次阅读 2018-08-08 16:33:51
    优化sql过程中,发现like的性能非常低,打算优化一下;网上很多优化方法,无非下面几种,抄来抄去的,我用213万条数据,每条数据50个字段左右(用的真实的生产环境的mysql数据库,和真实的成产环境的数据),做了...
  • sql优化常用的几种方法

    千次阅读 2022-03-06 05:45:01
    1.EXPLAIN type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到的索引名。...MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好
  • 1、多字段like模糊查询优化: 最常见的写法: where a like '%xx%' or b like '%xx%' or c like '%xx%' 这种写法查询效率低,经过调查,下面的方法可以替代,并且效率高: 2、如果like的关键字相同: where ...
  • Like SQL语句的优化

    万次阅读 2012-07-25 09:12:10
    Like SQL语句的优化的一个小测试 1.尽量不要使用 like '%..%' 2.对于 like '..%..' (不以 % 开头),Oracle可以应用 colunm上的index 3.对于 like '%...' 的 (不以 % 结尾),可以利用reverse + function ...
  • LIKE 操作符 LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。 基本语法 一:% 表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示 1.从上面的 “Persons...
  • SQL优化--SQL优化语句的一般步骤

    万次阅读 2018-07-25 15:55:59
    --SQL优化语句的一般步骤 --1.通过show status命令了解各种SQL的执行频率 --2.定位执行效率较低的SQL语句 --3.通过EXPLAIN分析较低SQL的执行计划 --4.通过show profile分析SQL --5.通过trace分析优化器如何选择...
  • oracle中慢sql优化思路

    千次阅读 2020-07-04 09:59:18
    参考资料:官方文档SQL Tuning Guide https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-performance-fundamentals.html#GUID-DD9CAA74-3E0B-48C9-8770-AADB614BC992 Oracle Database 2...
  • SQL 高级教程:SQL LIKE 操作符

    千次阅读 2021-06-09 09:29:41
    SQL LIKE 操作符 LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。 LIKE 操作符 LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。 SQL LIKE 操作符语法 SELECT column_name(s) FROM table_name WHERE column...
  • SQL优化(面试题)

    万次阅读 多人点赞 2019-06-13 22:21:12
    最近在面试的时候经常被问到SQL优化,总结如下: 1、在表中建立索引,优先考虑where、group by使用到的字段。 2、尽量避免使用select *,返回无用的字段会降低查询效率。如下: SELECT * FROM t 优化方式:...
  • Sql优化总结!详细!(2021最新面试必问)

    万次阅读 多人点赞 2021-04-09 16:20:03
    Sql优化基础Sql优化查询SQL尽量不要使用select *,而是具体字段避免在where子句中使用or来连接条件使用varchar代替char尽量使用数值替代字符串类型查询尽量避免返回大量数据使用explain分析你SQL执行计划是否使用了...
  • sql语句 模糊查找like

    千次阅读 2021-08-02 19:49:26
    模糊查找:like 语法形式:字段like '要查找字符' 说明: 1、like模糊查找用于对字符类型的字段进行字符匹配查找。 2、要查找的字符中,有两个特殊含义的字符:% , _: 2.1: %含义是:代表0或多个的任意字符 ...
  • like not like 优化

    千次阅读 2019-09-28 02:27:32
    instr(title,’手册’)>0 相当于like instr(title,’手册’)=0 相当于not like ...对于LIKE语句,我们可以使用instr函数来进行SQL调优 转载于:https://www.cnblogs.com/hyde114/p/11526450.html...
  • 常见的SQL优化

    万次阅读 多人点赞 2018-07-16 22:47:04
    SQL优化: 个人理解:主要是对查询的优化。对于sql的优化主要就是下面几个方面的优化, 1.避免全表扫描 2.避免索引失效 3.避免排序,不能避免,尽量选择索引排序 4.避免查询不必要的字段 5.避免临时表的创建,...
  • sqlserver的like '%xxx%'优化,全文索引

    千次阅读 2018-02-07 11:49:00
    查询耗时0秒,降到了毫秒级别,从这点可以看出,普通的非聚集索引对于直接匹配(=)查询的支持是最好的,然后是like 'xxxx%',而like '%xxx%'不支持     然后我们在Address字段上建立一个全文索引   ...
  • sql模糊查询 like

    千次阅读 2019-10-09 18:14:17
    like 经常与where 字句和通配符在一块进行使用,表示像啥啥,模糊查询 通配符 主要是 _ 和 %  % 百分号表示零个,一个或多个字符  _ 下划线表示单个字符 **注意:**1、 MS Access使用问号(?)而不是下划线(_) ...
  • SQL优化最干货总结 - MySQL(2020最新版)

    万次阅读 多人点赞 2020-06-29 16:55:47
    MySQL - SQL优化干货总结(吐血版),别辜负了自己的梦想,欢迎白嫖、点赞、收藏。
  • SQL中,有效防止likeSQL注入

    千次阅读 2019-03-12 17:11:31
    SQL中有效防止likeSQL注入 #{xxx},使用的是PreparedStatement,会有类型转换,所以比较安全。 ${xxx},使用字符串拼接,可以SQL注入。 like查询不小心会有漏动,正确写法如下: Mysql select * from t_user...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 513,534
精华内容 205,413
关键字:

like sql 如何优化