-
SQL 存储过程 与 查询语句优化
2018-03-04 20:54:01存储过程 存储过程(procedure) 类似C语言中的函数,用来执行管理任务或应用复杂的业务规则。 存储过程的优点 1.执行速度更快 2.允许模块化程序设计 3.提高系统安全 4.减少网络流通量 存储过程的分类 1.系统存储...SQL语句造成全部扫描
1:where条件中对字段进行null判断 2:where条件使用 != 或 <> 操作符 3:or 连接条件 4:in 或 not in 5:like '%ll%' 或 like '%ll' 6:where条件中使用参数,SQL只有运行时才会解析局部变量,但是优化程序不能将访问计划的选择 推迟到运行时,他必须在编译时进行选择。然而,在编译时建立访问计划变量的值时未知的,因而无 法作为索引选择的输入项.但是可以强制使用索引 select id from t where num = @num => select id from t with(index(索引名)) where num = @num 7:避免对字段进行表达式操作 8:避免对字段进行函数操作 9:不要在where子句=的左边进行函数,算数或其他表达式操作 10:使用索引字段作为条件时,如果是复合索引必须使用该索引中的第一个字段作为条件时才能保证 系统使用该索引。否则该索引将不会被使用,并且尽可能的让字段顺序与索引顺序一致。 11:用exists 代替 in
什么情况下使用存储过程
- 一个业务同时对多个表进行处理的
- 一个业务逻辑很复杂的时候
优点: 1.执行速度更快,在创建时编译。 2.允许模块化程序设计。 3.提高系统安全,可以指定账号执行 4.减少网络流通量。
--创建proc create proc up_name @id int = 0, --参数 @code varchar(64) = '' output --参数输出参数 as begin --想要执行的SQL语句必须写 end go --调用 exec up_name @id,@code output go -- 查询存储过程的sql脚本 sp_helptext up_name go --修改 alter proc up_name @id int, --参数 @code varchar(64) = '' output --参数输出参数 as begin --想要执行的新的SQL语句 end go --删除 drop proc up_name go
总结:
- 在写查询语句时需要避免全表扫描。
- 创建索引虽然能够提高查询效率,但与此同时或降低插入修改删除的效率。
- 涉及到较复杂的sql操作,我们可是创建存储过程,存储过程在创建时编译,执行效率会更高,而且可以降低程序与数据库的网络流通量因为程序只需要将存储过程的名称和参数传递到数据库。
-
mysql存储过程where条件_mysql存储过程WHERE 条件语句优化
2021-02-08 06:52:21在写mysql存储过程时,比如有一张数据量很大的log表,我们要取符合条件的20条,为了方便我们一般会这样写:SELECT * FROM logs ORDER BY id DESC LIMIT in_page,in_limit;其中in_page,in_limit 是传入的参数,当没有...在写mysql存储过程时,比如有一张数据量很大的log表,我们要取符合条件的20条,为了方便我们一般会这样写:
SELECT * FROM logs ORDER BY id DESC LIMIT in_page,in_limit;
其中in_page,in_limit 是传入的参数,当没有其他条件时,这样查询没有什么问题,假如我们要查询某人,某时的log时,为了方便我们一般会这么写:
SELECT * FROM logs WHERE CASE WHEN in_username = "" THEN true ELSE username LIKE CONCAT("%",in_username,"%") END
AND CASE WHEN time = "" THEN true ELSE time = in_time END
ORDER BY id DESC LIMIT in_page,in_limit;
其中in_username,in_time是传入的参数,这样写的后果是当数据量很大的情况下查询会很慢,原因是WHERE条件语句中case when后面的username和time字段的索引将失效!
为了提高查询效率,提高索引的使用效率where语句后面尽量不要使用case when,需要做判断的条件尽量在查询之前判断,组合成完整sql后在执行。
以上sql可以修改为:
SET kusername = CONCAT("'%",in_username,"%'");
IF in_username = "" THEN
SET continion = true;
ELSE
SET continion = CONCAT(continion," username LIKE ",kusername );
END IF;
IF in_time <> "" THEN
SET continion = CONCAT(continion," AND time = ",in_time);
END IF;
SET sqltxt = "SELECT * FROM logs WHERE";
SET pagetxt = CONCAT(" ORDER BY id DESC LIMIT ",def_page,",",in_pagesize);
SET sqltxt=CONCAT(sqltxt,continion,pagetxt);
SET @sqltxt = sqltxt;
PREPARE stmt FROM @sqltxt;
EXECUTE stmt;
需要注意的是WHERE后面的条件参数尽量不要使用函数方法,如:
SELECT * FROM logs WHERE DATE_SUB(time,INTERVAL 1 DAY)=in_time ORDER BY id DESC LIMIT 20;
这样会导致mysql进行全表操作,失去time索引的作用。
-
mysql存储过程WHERE 条件语句优化
2019-02-15 17:25:16在写mysql存储过程时,比如有一张数据量很大的log表,我们要取符合条件的20条,为了方便我们一般会这样写: SELECT * FROM logs ORDER BY id DESC LIMIT in_page,in_limit; 其中in_page,in_limit 是传入的参数,当...在写mysql存储过程时,比如有一张数据量很大的log表,我们要取符合条件的20条,为了方便我们一般会这样写:
SELECT * FROM logs ORDER BY id DESC LIMIT in_page,in_limit;
其中in_page,in_limit 是传入的参数,当没有其他条件时,这样查询没有什么问题,假如我们要查询某人,某时的log时,为了方便我们一般会这么写:
SELECT * FROM logs WHERE CASE WHEN in_username = "" THEN true ELSE username LIKE CONCAT("%",in_username,"%") END AND CASE WHEN time = "" THEN true ELSE time = in_time END ORDER BY id DESC LIMIT in_page,in_limit;
其中in_username,in_time是传入的参数,这样写的后果是当数据量很大的情况下查询会很慢,原因是WHERE条件语句中case when后面的username和time字段的索引将失效!
为了提高查询效率,提高索引的使用效率where语句后面尽量不要使用case when,需要做判断的条件尽量在查询之前判断,组合成完整sql后在执行。
以上sql可以修改为:
SET kusername = CONCAT("'%",in_username,"%'"); IF in_username = "" THEN SET continion = true; ELSE SET continion = CONCAT(continion," username LIKE ",kusername ); END IF; IF in_time <> "" THEN SET continion = CONCAT(continion," AND time = ",in_time); END IF; SET sqltxt = "SELECT * FROM logs WHERE"; SET pagetxt = CONCAT(" ORDER BY id DESC LIMIT ",def_page,",",in_pagesize); SET sqltxt=CONCAT(sqltxt,continion,pagetxt); SET @sqltxt = sqltxt; PREPARE stmt FROM @sqltxt; EXECUTE stmt;
需要注意的是WHERE后面的条件参数尽量不要使用函数方法,如:
SELECT * FROM logs WHERE DATE_SUB(time,INTERVAL 1 DAY)=in_time ORDER BY id DESC LIMIT 20;
任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
-
Sql语句优化及存储过程
2019-05-15 16:35:131. 怎么加快查询速度,优化查询效率,主要原则就是应尽量避免全表扫描,应该考虑在where及order by 涉及的列上建立索引。 2.:一个表的索引不是越多越好,也没有一个具体的数字,根据以往的经验,一个表的索引最多...1. 怎么加快查询速度,优化查询效率,主要原则就是应尽量避免全表扫描,应该考虑在where及order by 涉及的列上建立索引。
2.:一个表的索引不是越多越好,也没有一个具体的数字,根据以往的经验,一个表的索引最多不能超过6个,因为索引越多,对update和insert操作也会有性能的影响,涉及到索引的新建和重建操作。
3.索引建立
1.多数查询经常使用的列;
2.很少进行修改操作的列;
3.索引需要建立在数据差异化大的列上
4.sql语句模型结构优化指导
1. ORDER BY + LIMIT组合的索引优化
如果一个SQL语句形如:SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];
这个SQL语句优化比较简单,在[sort]这个栏位上建立索引即可。
2.LIKE语句优化
SELECT id FROM A WHERE name like ‘%abc%’
由于abc前面用了“%”,因此该查询必然走全表查询,除非必要,否则不要在关键词前加%
优化成如下
SELECT id FROM A WHERE name like ‘abc%’
3.where子句使用 != 或 <> 操作符优化
SQL:SELECT id FROM A WHERE ID != 5 优化成:SELECT id FROM A WHERE ID>5 OR ID<5
4.LIMIT分页优化
当偏移量特别大时,limit效率会非常低
SELECT id FROM A LIMIT 1000,10 很快SELECT id FROM A LIMIT 90000,10 很慢
方法一:select id from A order by id limit 90000,10; 很快,0.04秒就OK。 因为用了id主键做索引当然快
5.视图操作
重点:视图不能加条件sql加条件判断推荐使用存储过程
1.创建视图 create view 视图名 as sql语句‘
2.删除视图 drop view 视图名
3.查看创建的视图
sqlyog
navicat
4.修改视图sql
create or replace view as sql语句
6.储存过程操作
1.创建储存过程:
1.DELIMITER //
2.CREATE PROCEDURE 过程名字(IN | OUT | INOUT)
3.BEGIN
4. SQL体
5.END//
方法参数
1. IN 向过程里传参
2. OUT:过程向外传参值
3, INOUT:in and out
在sql中写 DELIMITER// 相当于分辨是否是SQL 还是在创建储存过程,最后以END//结束还可以写%%分辨不是必须//
调用储存过程
CALL 过程名(参数赋值)
删除储存过程
DROP PROCEDURE 过程名
将原有表的数据传到其他数据库的表
ss为新表名 hrms为旧数据库名.旧表名
CREATE ss AS SELECT * FROM hrms.management -
SQL:分页存储过程(优化了部分语句,附C#调用接口)
2019-09-23 04:23:54网上分页存储过程的代码很多,这个是我一直在用的经典的三层架构里提取出来的,并做了部分语句的优化... 如有更好的欢迎讨论! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO --------------------------... -
mysql 存储过程 插入语句_mysql 存储过程 插入语句
2021-01-19 12:55:23{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云...提供数据库优化方面专业建议,在业务高峰期与用户共同保障数据库系统平... -
根据 累计 使用 CPU 资源最多的语句 和 最经常 需要编辑的存储过程 来优化 语句 和存储过程...
2012-11-14 08:44:00转载:http://www.cnblogs.com/xunziji/archive/2011/07/22/2114275.html--使用DMV来分析SQL ...例如下面的语句就可以列出前50名。select c.last_execution_time,c.execution_count,c.total_logical_reads,c.tota... -
如何在sql存储过程中打log_SQL语句优化
2020-12-03 04:34:21如何定位并优化慢查询 SQL?大致思路如下:根据慢查询日志定位慢查询 SQL;使用 explain 等工具分析 SQL;修改 SQL 或者尽量让 SQL 走索引。获取有性能问题的 SQL 的两种方法:通过慢查日志获取存在性能问题的 SQL... -
mysql 存储过程 预处理语句_PHP的PDO预处理语句与存储过程
2021-02-01 21:58:48PHP PDO 预处理语句与存储过程很多更成熟的数据库都支持预处理语句的概念。什么是预处理语句?可以把它看作是想要运行的 SQL 的一种编译过的模板,它可以使用变量参数进行定制。预处理语句可以带来两大好处:查询仅... -
MySQL存储过程函数优化步骤
2017-03-03 10:10:47专题描述讲述Ecbil项目典型复杂业务场景的存储过程优化步骤,帮助研发团队提升存储过程的编程能力和相关的优化技术技巧。...存储过程/函数内部的SQL语句优化例如:INSERTINTOtt_express_invoice(EXPRESS_INVOI... -
存储过程 查询优化
2019-03-07 13:39:32排查发现 调用存储过程 分析如下 列表_住院确认( Vi机构序号 Number, Vi终端序号 Number, Vi操作员序号 Number, vd申请时间前 DATE, vd申请时间后 DATE, vi健康序号 NUMBER, vs关键词 NVARCHAR2, rs数据集 out sys_... -
Oracle中存储过程和Sql语句的优化重点
2011-06-01 23:12:39Oracle数据库是功能性很好的数据库管理系统,至今为止在数据库市场中仍占有主要份额,下文中将为大家讲解Oracle中存储过程和Sql语句的优化重点 ,希望对大家能够有所帮助。 1.全表扫描和索引扫描 大数据量表尽量要... -
SQL:分页存储过程(优化了部分语句,附C#调用接口)(转)
2013-11-01 11:54:55网上分页存储过程的代码很多,这个是我一直在用的经典的三层架构里提取出来的,并做了部分语句的优化... 如有更好的欢迎讨论! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ------------... -
存储过程优化
2019-11-21 10:50:40优化存储过程有很多种方法,下面介绍最常用的7种。 1.使用SET NOCOUNT ON选项 我们使用SELECT语句时,除了返回对应的结果集外,还会返回相应的影响行数。使用SET NOCOUNT ON后,除了数据集就不会返回额外的信息了... -
[转载]Oracle中存储过程和Sql语句的优化重点
2011-11-22 11:10:00原文地址:Oracle中存储过程和Sql语句的优化重点作者:人月神话1.全表扫描和索引扫描 大数据量表尽量要避免全表扫描,全部扫描会按顺序每条记录扫描,对于>100万数据表影响很大。 Oracle中通过RowID访问数据是最... -
【第32天】Oracle的索引、存储过程、触发器以及SQL语句的优化
2018-12-17 17:33:04第三十二天,简要学了索引、存储过程、触发器。列举了SQL语句的优化的方法。 -
存储过程与SQL语句对比
2019-04-24 11:09:03存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划,这样,在执行过程时便可节省此开销。 2、降低网络开销 存储过程调用时只需用提供存储过程名和... -
存储过程性能优化
2015-11-26 19:02:001.使用SET NOCOUNT(设置...sql语句 SET NOCOUNT OFF go 2.使用确定的架构 如:select * from dbo.table 比select * from table 性能上要好 如果一张表是my.table,那么直接查询色select * from table 是会报... -
mysql存储过程的优化,
2015-02-11 03:39:30调用存储过程,[code=sqlINSERT INTO tb_test(id,rule,time) VALUES(_id,_rule,_time); SELECT @@identity;][/code] 进行简单的插入语句,在慢查询日志中 95% of Time : 5254.761149 s total, 2.48805 s avg, 1.... -
存储过程批量建表 sql_40种常见的SQL语句性能优化方式
2021-01-15 04:16:53而打交道的方式无非就是建库、建表、写SQL、写存储过程、写视图、写触发器等等,其中,“写SQL”应当是最常做的事情了,本文将罗列整理出SQL语句常见的几十种性能优化方式,建议诸位可以收藏!1.SQL查询应尽量避免全... -
Mysql - SQL语句优化,索引,视图,触发器,存储过程,函数等。
2018-05-08 15:52:00一,SQL优化 主要解决海量数据操作时的全表搜索,所以减少不必要的全表搜索是SQL优化的主要目的,下面总结一下常用的优化有哪些: 1,避免在where条件中使用!=或者<>,这样会是的查询放弃索引而进行... -
什么是存储过程?存储过程与SQL语句对比,使用存储过程的优点?
2017-02-23 13:20:10存储过程:使用一个名称存储的预编译T-SQL语句和流程控制语句的集合 由数据库开发人员或数据库管理员编写 用来执行管理任务或应用复杂的业务规则 优点:执行速度更快 首次运行时,进行优化和编译得到执行计划并... -
Mysql优化存储过程存储函数
2021-01-20 17:33:33存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中...