HTML5技术

sql的那些事(一) - Akon_Coder

字号+ 作者:H5之家 来源:H5之家 2017-01-01 12:00 我要评论( )

一.概述 书写sql是我们程序猿在开发中必不可少的技能,优秀的sql语句,执行起来吊炸天,性能杠杠的。差劲的sql,不仅使查询效率降低,维护起来也十分不便。一切都是为了性能,一切都是为了业务,你觉得你的sql技能如何?所有的伟大来自于点滴的积累,不积跬

一.概述

书写sql是我们程序猿在开发中必不可少的技能,优秀的sql语句,执行起来吊炸天,性能杠杠的。差劲的sql,不仅使查询效率降低,维护起来也十分不便。一切都是为了性能,一切都是为了业务,你觉得你的sql技能如何?所有的伟大来自于点滴的积累,不积跬步无以至千里,让sql性能飞起来吧!

二.sql初探 1.常见sql写法注意点

(1)字符类型建议采用varchar/nvarchar数据类型

  • char
    char是定长的,也就是当你输入的字符小于你指定的数目时,char(8),你输入的字符小于8时,它会再后面补空值。当你输入的字符大于指定的数时,它会截取超出的字符。
    nvarchar(n)
    包含 n 个字符的可变长度 Unicode 字符数据。n 的值必须介于 1 与 4,000 之间。字节的存储大小是所输入字符个数的两倍。所输入的数据字符长度可以为零。
    varchar[(n)]
    长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度,而不是 n 个字节。所输入的数据字符长度可以为零。
  • [1]—CHAR。CHAR存储定长数据很方便,CHAR字段上的索引效率级高,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间。

    [2]—VARCHAR。存储变长数据,但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢?这一个字节用于保存实际使用了多大的长度。 从空间上考虑,用varchar合适;从效率上考虑,用char合适,关键是根据实际情况找到权衡点。

    [3]—TEXT。text存储可变长度的非Unicode数据,最大长度为2^31-1(2,147,483,647)个字符。

    [4]—NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来,nchar、nvarchar则最多存储4000个字符,不论是英文还是汉字;而char、varchar最多能存储8000个英文,4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。

    所以一般来说,如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar。

    举例说明:
    两字段分别有字段值:我和coffee
    那么varchar字段占2×2+6=10个字节的存储空间,而nvarchar字段占8×2=16个字节的存储空间。
    如字段值只是英文可选择varchar,而字段值存在较多的双字节(中文、韩文等)字符时用nvarchar

    (2)金额货币建议采用money数据类型 (一般常用,最大四位小数)

    (3)科学计数建议采用numeric数据类型-- (建议巨额资金交易用numeric)

    (4)自增长标识建议采用bigint数据类型 (数据量一大,用int类型就装不下,那以后改造就麻烦了)

    (5)时间类型建议采用为datetime数据类型

    (6)禁止使用text、ntext、image老的数据类型(已过时)

    (7)禁止使用xml数据类型、varchar(max)、nvarchar(max)

    (8)禁止在数据库做复杂运算 (业务处理逻辑最好在代码层实现,不要让所有的代码逻辑存在于sql中,不便于后期的问题定位)

    (9)禁止使用SELECT * (按需所取,查找自己所需要的列)

    (10)禁止在索引列上使用函数或计算

    例如:
    我们查询注册时间在2015-11-11的店铺账号,找出它们进行活动奖励,我们如果不加注意,很可能写成这样:

    select * from T_Account where Convert(varchar(10,Regtime,121)='2015-11-11'

    这样写的话,我们就无法命中索引字段Regtime,如果T_Account的数据量超大的时候,数据库查询分析器走表扫描,查询效率就降低了;要实现上面的查询结果,其实我们可以换一种写法:

    select * from T_Account where Regtime>='2015-11-11 00:00:00' and Regtime<'2015-11-12 00:00:00'

    (11)禁止使用游标

    由于游标在处理大数据量的时候,占有的内存较大,效率低。可能造成其他的数据库查询堵塞的现象,除非是当你使用while循环,子查询,临时表,表变量,自建函数或其他方式都无法处理某种操作的时候,再考虑使用游标。
    举例说明一下在实际运用中的一个游标处理:

    --定义店铺ID declare @accId int set @accId=218424 --1.创建临时表并插入数据 select gsid,gid into #gidlist from T_Goods_Sku where accid=@accId and gid in (select gid from T_GoodsInfo where accid=@accId and isService=0 and IsExtend=1) select gsId,gaVName into #gsidlist from T_Goods_Relation where gsid in (select gsid from T_Goods_Sku where accid=@accId and gid in (select gid from T_GoodsInfo where accid=@accId and isService=0 and IsExtend=1)) order by gsId select a.gid gid,a.gsId gsId,b.gaVName gaVName into #tempgid from #gidlist a left join #gsidlist b on a.gsId=b.gsId drop table #gidlist drop table #gsidlist --2.开始事务 BEGIN TRANSACTION --3.定义变量,累积事务执行过程中的错误 DECLARE @error INT SET @error = 0 --4.声明游标 DECLARE goodsCursor CURSOR SCROLL FOR SELECT gid ,gsId ,gaVName FROM #tempgid --5.打开游标 OPEN goodsCursor --6.声明游标提取数据所要存放的变量 DECLARE @gid INT ,@gsId INT ,@gaVName NVARCHAR(400) ,@gUnionKey NVARCHAR(400) --7.定位游标到哪一行 FETCH First FROM goodsCursor INTO @gid ,@gsId ,@gaVName --8.提取成功,对数据操作,进行下一条数据的提取操作 WHILE @@fetch_status = 0 BEGIN SET @gUnionKey = '' SELECT @gUnionKey = gUnionKey from T_GoodsInfo where accid=@accId and isService=0 and IsExtend=1 and gid=@gid SELECT @gUnionKey=@gUnionKey+'|'+@gaVName PRINT '-----start-------' PRINT @gid PRINT @gsId PRINT @gaVName PRINT @gUnionKey --更新gUnionKey update T_GoodsInfo set gUnionKey=@gUnionKey where accid=@accId and isService=0 and IsExtend=1 and gid=@gid PRINT '-----end--------' --移动游标 FETCH NEXT FROM goodsCursor INTO @gid ,@gsId ,@gaVName END --9.判读事务错误数,提交或回滚事务 IF @error <> 0 --有误 BEGIN PRINT '回滚事务' ROLLBACK TRANSACTION END ELSE BEGIN PRINT '提交事务' COMMIT TRANSACTION END --10.关闭并删除游标,删除临时表 CLOSE goodsCursor DEALLOCATE goodsCursor drop table #tempgid

    (12)禁止使用触发器

     

    1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

    相关文章
    • 救火队员的那些事(2) - CC

      救火队员的那些事(2) - CC

      2016-09-07 17:00

    • 回首一年的骚动岁月 - Akon_Coder

      回首一年的骚动岁月 - Akon_Coder

      2016-09-04 16:00

    • 那些年搞不懂的术语、概念:协变、逆变、不变体 - 农码一生

      那些年搞不懂的术语、概念:协变、逆变、不变体 - 农码一生

      2016-08-30 17:00

    • 开源的那些事儿 (一)- 如何看待开源 - LeftNotEasy

      开源的那些事儿 (一)- 如何看待开源 - LeftNotEasy

      2016-08-24 10:00

    网友点评