-
2017-09-06 17:25:13
我们在写sql的时候应该尽量避免在一个复杂查询里面使用 LIKE ‘%parm1%’—— 由于parm1前面用到了“%”,因此该查询必然走全表扫描,导致相关列的索引无法使用,除非必要,否则不要在关键词前加%,
如果后台逻辑必须使用’%parm1%’,可以考虑以下解决办法:- 修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了。
- 直接修改后台——根据输入条件,先查出符合条件的供应商,并把相关记录保存在一个临时表里头,然后再用临时表去做复杂关联
例如:
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不可时的处理方案
2020-09-10 08:23:44主要介绍了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 例如报表类数据库 重复率高的数据 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果
缺点:不适合重复率低的字段,还有经常DML操作(insert,update,delete),因为位图索引的锁代价极高,修改一个位图索引段影响整个位图段,例如修改
一个键值,会影响同键值的多行,所以对于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%'
于德晓测试结果:
select * from gt_alarm where alarm_date like '%2016/4/3%'; --11s
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
通过结果,管中窥豹,可见一斑。
-
sql优化常用的几种方法,19种最有效的sql优化技巧
2021-01-21 03:47:09本文我们来谈谈项目中常用的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)我们要重点关注的数据:
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表中的数据:
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语句,扫描的行数成百万级以上的时候就可以使用分段查询:
12、避免在where子句中对字段进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
13、不建议使用%前缀模糊查询
例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。
那如何查询%name%?
如下图所示,虽然给secret字段添加了索引,但在explain结果并没有使用:
那么如何解决这个问题呢,答案:使用全文索引。
在我们查询中经常会用到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优化
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)利用小表去驱动大表:
从原理图能够直观的看出如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 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,否则可能造成查询结果不准确。
这个方式有时能减少3倍的时间。
客服微信:(id1234562011)本文链接:https://www.changchenghao.cn/n/174426.html
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 394062665@qq.com 举报,一经查实,本站将立刻删除。
-
MySql基础知识总结(SQL优化篇)
2021-07-10 12:26:262、添加索引 3、更改索引顺序 4、去掉in 5、小结 四、双表sql优化 1、建表语句 2、左连接查询 3、小结 五、避免索引失效的一些原则 六、一些其他的优化方法 1、exist和in 2、order by 优化 七、sql顺序 -> 慢日志... -
sql优化中关于 属性值 like '%值' 的优化
2018-06-05 09:21:53适合数据大于几百万条的优化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:59SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至某些公司招聘时,在岗位职能上都有明码标注,这也就更证明了掌握SQL优化技能的重要性,借此机会就和大家分享一下我在优化SQL时的一些经验心得。 小弟献丑啦,嘿嘿... -
mysql 模糊查询like优化方案(亲测)
2021-11-18 18:44:24select * 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:13sql优化一般步骤概要: 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:011.EXPLAIN type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到的索引名。...MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好 -
sql语句查询,多字段like模糊查询优化
2020-05-08 09:19:341、多字段like模糊查询优化: 最常见的写法: where a like '%xx%' or b like '%xx%' or c like '%xx%' 这种写法查询效率低,经过调查,下面的方法可以替代,并且效率高: 2、如果like的关键字相同: where ... -
Like SQL语句的优化
2012-07-25 09:12:10Like SQL语句的优化的一个小测试 1.尽量不要使用 like '%..%' 2.对于 like '..%..' (不以 % 开头),Oracle可以应用 colunm上的index 3.对于 like '%...' 的 (不以 % 结尾),可以利用reverse + function ... -
关于sql中like操作符的使用及效率优化问题整理
2019-01-08 10:59:42LIKE 操作符 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:41SQL 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:03Sql优化基础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:32instr(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:04SQL优化: 个人理解:主要是对查询的优化。对于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:17like 经常与where 字句和通配符在一块进行使用,表示像啥啥,模糊查询 通配符 主要是 _ 和 % % 百分号表示零个,一个或多个字符 _ 下划线表示单个字符 **注意:**1、 MS Access使用问号(?)而不是下划线(_) ... -
SQL优化最干货总结 - MySQL(2020最新版)
2020-06-29 16:55:47MySQL - SQL优化干货总结(吐血版),别辜负了自己的梦想,欢迎白嫖、点赞、收藏。 -
SQL中,有效防止like的SQL注入
2019-03-12 17:11:31SQL中有效防止like的SQL注入 #{xxx},使用的是PreparedStatement,会有类型转换,所以比较安全。 ${xxx},使用字符串拼接,可以SQL注入。 like查询不小心会有漏动,正确写法如下: Mysql select * from t_user...