金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 语句查询常用的

语句查询常用的

发布时间:2019-10-28 22:04编辑:数据库浏览(165)

    **1、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 

    1、B+树基本概念

    CTE 也叫公用表表达式和派生表非常类似 先定义一个USACusts的CTE  

    2、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 

      B+树的语言定义比较复杂,简单的说是为磁盘存取设计的平衡二叉树

    WITH USACusts AS
    (
      SELECT custid, companyname
      FROM Sales.Customers
      WHERE country = N'USA'
    )
    SELECT * FROM USACusts;
    

    3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: 
    select id from t where num is null 
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: 
    select id from t where num=0 

    图片 1

    with  ()  称为内部查询   与派生表相同,一旦外部查询完成后,CTE就自动释放了

    4、应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: 
    select id from t where num=10 or num=20 
    可以这样查询: 
    select id from t where num=10 
    union all 
    select id from t where num=20 

      网上经典图,黄色p1 p2 p3代表指针,蓝色的代表磁盘,里面包含数据项,第一层17,35,p1就代表小于17的,p2就代表17-35之间的,p3就代表大于35的,可是需要注意的是,第三层才是真实的数据,17、35都不是真实数据,只是用来划分数据的!

    CTE内部方式 就是上面代码所表示的方式  其实还有一种外部方式

    5、下面的查询也将导致全表扫描: 
    select id from t where name like '%abc%' 
    若要提高效率,可以考虑全文检索。 

    2、为什么使用B+树

    WITH C(orderyear, custid) AS
    (
      SELECT YEAR(orderdate), custid
      FROM Sales.Orders
    )
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
    FROM C
    GROUP BY orderyear;
    GO
    
    C(orderyear, custid)  可以理解为 select orderyear, custid from C   指定返回你想要的列  不过个人感觉没什么用!
    
    它和派生表相同 也可以在CTE中查询使用参数
    
    DECLARE @empid AS INT = 3;
    
    WITH C AS
    (
      SELECT YEAR(orderdate) AS orderyear, custid
      FROM Sales.Orders
      WHERE empid = @empid
    )
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
    FROM C
    GROUP BY orderyear;
    GO
    

    6、in 和 not in 也要慎用,否则会导致全表扫描,如: 
    select id from t where num in(1,2,3) 
    对于连续的数值,能用 between 就不要用 in 了: 
    select id from t where num between 1 and 3 

      B+树有什么好处我们非要使用它呢?那就先要来看看mysql的索引

    定义多个CTE

    7、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: 
    select id from t where num=@num 
    可以改为强制查询使用索引: 
    select id from t with(index(索引名)) where num=@num 

     

    WITH C1 AS
    (
      SELECT YEAR(orderdate) AS orderyear, custid
      FROM Sales.Orders
    ),
    C2 AS
    (
      SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
      FROM C1
      GROUP BY orderyear
    )
    SELECT orderyear, numcusts
    FROM C2
    WHERE numcusts > 70;
    

    8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: 
    select id from t where num/2=100 
    应改为: 
    select id from t where num=100*2 

      2.1mysql索引

    多个CTE用 , 隔开 通过with 内存 可以在外查询中多次引用

    9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: 
    select id from t where substring(name,1,3)='abc'--name以abc开头的id 
    select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id 
    应改为: 
    select id from t where name like 'abc%' 
    select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 

        试想一下在mysql中有200万条数据,在没有建立索引的情况下,会全部进行扫描读取,这个时间消耗是非常恐怖的,而对于大型一点的网站来说,达到这个数据量很容易,不可能这样去设计

    WITH YearlyCount AS
    (
      SELECT YEAR(orderdate) AS orderyear,
        COUNT(DISTINCT custid) AS numcusts
      FROM Sales.Orders
      GROUP BY YEAR(orderdate)
    )
    SELECT Cur.orderyear, 
      Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
      Cur.numcusts - Prv.numcusts AS growth
    FROM YearlyCount AS Cur
      LEFT OUTER JOIN YearlyCount AS Prv
        ON Cur.orderyear = Prv.orderyear + 1;
    

    10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。 

        在我们创建数据库表的时候,大家都知道一个东西叫做主键,一般来讲数据库会自动在主键上创建索引,这叫做主键索引,来看看索引的分类吧

    可以需要在多个相同表结果做物理实例化  这样可以节省很多查询时间 或者在临时表和表变量中固化内部查询结果

    11、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。 

        a.主键索引:int优于varchar

    递归CTE

    12、不要写一些没有意义的查询,如需要生成一个空表结构: 
    select col1,col2 into #t from t where 1=0 
    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: 
    create table #t(...) 

        b.普通索引(INDEX):最基本的索引,没有限制,加速查找

    递归CTE至少由两个查询定义,至少一个查询作为定位点成员,一个查询作为递归成员。

    13、很多时候用 exists 代替 in 是一个好的选择: 
    select num from a where num in(select num from b) 
    用下面的语句替换: 
    select num from a where exists(select 1 from b where num=a.num) 

        c.唯一索引(UNUQUE):听名字就知道,要求所有类的值是唯一的,但是允许有空值

    递归成员是一个引用CTE名称的查询 ,在第一次调用递归成员,上一个结果集是由上一次递归成员调用返回的。 其实就和C# 方法写递归一样  返回上一个结果集 依次输出

    14、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。 

        d.组合索引:

       WITH    Emp
     AS ( SELECT  * FROM  dbo.dt_users
                   WHERE  id=2
                    UNION ALL  
                    SELECT d.* FROM  Emp
                             INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
                 )
        SELECT *
         FROM Emp 
    

    15、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。 

    1 CREATE INDEX name_age_address_Index ON `student`(`name`, `age`, `address`);
    

    在前面也写过 sql 语句的执行顺序 其实到  FROM Emp   时 就进行了节点第一次递归  当我们递归到第三次的时候 这个为执行的sql 语句实际是什么样的呢

    16、应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。 

        在这里实际上包含三个索引,说到组合索引,一定要讲最左前缀原则

       WITH    Emp
     AS ( SELECT  * FROM  dbo.dt_users
                   WHERE  id=2
                    UNION ALL  
                    SELECT  * FROM  dbo.dt_users
                   WHERE  id=3
                    UNION ALL  
                    SELECT  * FROM  dbo.dt_users
                   WHERE  id=4
                    UNION ALL  
                    SELECT d.* FROM  Emp
                             INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
                 )
        SELECT *
         FROM Emp 
    

    17、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。 

     

    简单理解可以把它看成两部分

    18、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。 


    SELECT  * FROM  dbo.dt_users
                   WHERE  id=2
    
       SELECT d.* FROM  Emp
                             INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
    

    本文由金沙国际官网发布于数据库,转载请注明出处:语句查询常用的

    关键词: