精华内容
下载资源
问答
  • 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
    

    总结:

    1. 在写查询语句时需要避免全表扫描。
    2. 创建索引虽然能够提高查询效率,但与此同时或降低插入修改删除的效率。
    3. 涉及到较复杂的sql操作,我们可是创建存储过程,存储过程在创建时编译,执行效率会更高,而且可以降低程序与数据库的网络流通量因为程序只需要将存储过程的名称和参数传递到数据库。
    展开全文
  • 在写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存储过程时,比如有一张数据量很大的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:13
    1. 怎么加快查询速度,优化查询效率,主要原则就是应尽量避免全表扫描,应该考虑在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

    展开全文
  • 网上分页存储过程的代码很多,这个是我一直在用的经典的三层架构里提取出来的,并做了部分语句优化... 如有更好的欢迎讨论! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO --------------------------...
  • {"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云...提供数据库优化方面专业建议,在业务高峰期与用户共同保障数据库系统平...
  • 转载: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?大致思路如下:根据慢查询日志定位慢查询 SQL;使用 explain 等工具分析 SQL;修改 SQL 或者尽量让 SQL 走索引。获取有性能问题的 SQL 的两种方法:通过慢查日志获取存在性能问题的 SQL...
  • PHP PDO 预处理语句存储过程很多更成熟的数据库都支持预处理语句的概念。什么是预处理语句?可以把它看作是想要运行的 SQL 的一种编译过的模板,它可以使用变量参数进行定制。预处理语句可以带来两大好处:查询仅...
  • 专题描述讲述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数据库是功能性很好的数据库管理系统,至今为止在数据库市场中仍占有主要份额,下文中将为大家讲解Oracle中存储过程和Sql语句优化重点 ,希望对大家能够有所帮助。 1.全表扫描和索引扫描 大数据量表尽量要...
  • 网上分页存储过程的代码很多,这个是我一直在用的经典的三层架构里提取出来的,并做了部分语句优化... 如有更好的欢迎讨论! 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语句优化重点作者:人月神话1.全表扫描和索引扫描 大数据量表尽量要避免全表扫描,全部扫描会按顺序每条记录扫描,对于>100万数据表影响很大。 Oracle中通过RowID访问数据是最...
  • 第三十二天,简要学了索引、存储过程、触发器。列举了SQL语句优化的方法。
  • 存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划,这样,在执行过程时便可节省此开销。 2、降低网络开销 存储过程调用时只需用提供存储过程名和...
  • 存储过程性能优化

    2015-11-26 19:02:00
    1.使用SET NOCOUNT(设置...sql语句 SET NOCOUNT OFF go 2.使用确定的架构 如:select * from dbo.table 比select * from table 性能上要好 如果一张表是my.table,那么直接查询色select * from table 是会报...
  • 调用存储过程,[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、写存储过程、写视图、写触发器等等,其中,“写SQL”应当是最常做的事情了,本文将罗列整理出SQL语句常见的几十种性能优化方式,建议诸位可以收藏!1.SQL查询应尽量避免全...
  • 一,SQL优化 主要解决海量数据操作时的全表搜索,所以减少不必要的全表搜索是SQL优化的主要目的,下面总结一下常用的优化有哪些: 1,避免在where条件中使用!=或者&lt;&gt;,这样会是的查询放弃索引而进行...
  • 存储过程:使用一个名称存储的预编译T-SQL语句和流程控制语句的集合 由数据库开发人员或数据库管理员编写 用来执行管理任务或应用复杂的业务规则 优点:执行速度更快 首次运行时,进行优化和编译得到执行计划并...
  • 存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,529
精华内容 1,011
关键字:

存储过程语句优化