索引优化_老是说索引优化,到底如何创建索引 - CSDN
  • Mysql索引优化 一:索引介绍 索引是关系型数据库中给数据库表中一列或者多列的值排序后的储存结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引. MySql索引类型有:唯一索引,主键(聚集)...

    Mysql索引优化

    一:索引介绍

    索引是关系型数据库中给数据库表中一列或者多列的值排序后的储存结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引.

    MySql索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引.

    1.1:聚集索引

    聚集(clustered)索引,也叫做聚簇索引.

    定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引.

    注意:聚集索引做查询可以直接获取对应的全部列的数据.所以聚集查询较快.

    1.2非聚集索引

    定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引.

    除了聚集索引以外的索引都是非聚集索引,分成普通索引,唯一索引和全文索引.

    注意:非聚集索引查询在索引没覆盖到对应列的时候需要进行二次查询,索引非聚集查询较慢.

    1.2.1如何解决非聚集索引的二次查询问题
    复合索引(覆盖索引)

    建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1,col2),执行下面的语句

    select col1,col2 from 表名 where col1=‘xxx’;

    要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用.

    二:索引的存储机制

    无索引的表,查询时,是按照顺序存序的方法扫描每个记录来查询符合条件的记录,这样效率很低.

    聚集索引和非聚集索引的根本区别在于表记录的排列顺序和索引的排列顺序是否一致.

    聚集索引就是在数据库被开辟一个物理空间放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个物理空间,而非聚集索引其实可以看做是一个含有聚集索引的表,它只仅包含原表中非聚集索引的列和指向实际物理表的指针,它只记录一个指针,其实就有点和堆栈差不多的感觉.

    三:建立索引的原则

    1. 定义主键的数据列一定要建立索引。

    2. 定义有外键的数据列一定要建立索引。

    3. 对于经常查询的数据列最好建立索引。

    4. 对于需要在指定范围内的快速或频繁查询的数据列;

    5. 经常用在WHERE子句中的数据列。

    6. 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7. 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8. 对于定义为text、image和bit的数据类型的列不要建立索引。

    9. 对于经常存取的列避免建立索引

    10. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    11. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

    四:索引优化面试题

    select * from student s where s.stuName in(“张三”,“李四”,“王五”)and s.age>18 and s.sex=‘男’;

    思路:

    1.肯定要建立二级联合索引:index(stuName,age,sex)

    2.这里要优化一下sql语句中的in,改用union all

    优化后的sql:

    select * from student s where s.stuName=“张三” and s.age>18 and s.sex=‘男’ union all select * from student s where s.stuName=“李四” and s.age>18 and s.sex=‘男’ union all select * from student s where s.stuName=“王五” and s.age>18 and s.sex=‘男’ ;

    展开全文
  • SQL优化:索引优化

    2017-08-22 11:23:55
     SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引,...

    SQL索引

       SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

      1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

            图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

            字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:

    1. 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。
    2. 就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。
    3. 还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

       1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。

            非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引

          原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

     

      1.3 什么情况下设置索引 

    动作描述

    使用聚集索引 

     使用非聚集索引

     外键列

     应

     应

     主键列

     应

     应

     列经常被分组排序(order by)

     应

     应

     返回某范围内的数据

     应

     不应

     小数目的不同值

     应

     不应

     大数目的不同值

     不应

     应

     频繁更新的列

    不应 

     应

     频繁修改索引列

     不应

     应

     一个或极少不同值

     不应

     不应

     

    • 建立索引的原则:

    1) 定义主键的数据列一定要建立索引。

    2) 定义有外键的数据列一定要建立索引。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为textimagebit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个索引虽说提高了访问速度,但太多索引会影响数据的更新操作

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

     

    • 索引的不足之处

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    1. 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
    2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。


    使用索引时,有以下一些技巧和注意事项:

    • 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    • 使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    • 索引列排序

    MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引

    • like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引

    • 不要在列上进行运算

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    1
    select * from users where adddate<‘2007-01-01’;  


    • 不使用NOT IN和<>操作


    1.4 如何创建索引

      1.41 创建索引的语法:

    1.42 删除索引语法:

     
    1
    DROP INDEX table_name.index_name[,table_name.index_name]
    2
    3
    说明:table_name: 索引所在的表名称。
    4
    5
    index_name : 要删除的索引名称。

    1.43 显示索引信息:

    使用系统存储过程:sp_helpindex 查看指定表的索引信息。

    执行代码如下:

     1.44查询索引(均可) 

     
    1
    show index from table_name;
    2
    show keys from table_name;
    3
    desc table_Name;

      1.44组合索引

    很多时候,我们在mysql中创建了索引,但是某些查询还是很慢,根本就没有使用到索引!一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。

    看下面的例子:
    假设有一张订单表(orders),包含order_id和product_id二个字段。
    一共有31条数据。符合下面语句的数据有5条。执行下面的sql语句:

    这条语句要mysql去根据order_id进行搜索,然后返回匹配记录中的product_id。所以组合索引应该按照以下的顺序创建:

     
    1
    create index orderid_productid on orders(order_id, product_id)
    2
    mysql> explain select product_id from orders where order_id in (123, 312, 223, 132, 224) \G
    3
    *************************** 1. row ***************************
    4
               id: 1
    5
      select_type: SIMPLE
    6
            table: orders
    7
             type: range
    8
    possible_keys: orderid_productid
    9
              key: orderid_productid
    10
          key_len: 5
    11
              ref: NULL
    12
             rows: 5
    13
            Extra: Using where; Using index
    14
    1 row in set (0.00 sec)
    可以看到,这个组合索引被用到了,扫描的范围也很小,只有5行。如果把组合索引的顺序换成product_id, order_id的话,mysql就会去索引中搜索 *123 *312 *223 *132 *224,必然会有些慢了。
    这次索引搜索的性能显然不能和上次相比了。rows:31,我的表中一共就31条数据。索引被使用部分的长度:key_len:10,比上一次的key_len:5多了一倍。不知道是这样在索引里面查找速度快,还是直接去全表扫描更快呢?
     
    1
    mysql> alter table orders add modify_a char(255) default 'aaa';
    2
    Query OK, 31 rows affected (0.01 sec)
    3
    Records: 31  Duplicates: 0  Warnings: 0
    4
     
    5
    mysql>
    6
    mysql>
    7
    mysql> explain select modify_a from orders where order_id in (123, 312, 223, 132, 224) \G         
    8
    *************************** 1. row ***************************
    9
               id: 1
    10
      select_type: SIMPLE
    11
            table: orders
    12
             type: ALL
    13
    possible_keys: NULL
    14
              key: NULL
    15
          key_len: NULL
    16
              ref: NULL
    17
             rows: 31
    18
            Extra: Using where
    19
    1 row in set (0.00 sec)
    这样就不会用到索引了。 刚才是因为select的product_id与where中的order_id都在索引里面的。


    为什么要创建组合索引呢?这么简单的情况直接创建一个order_id的索引不就行了吗?果只有一个order_id索引,没什么问题,会用到这个索引,然后mysql要去磁盘上的表里面取到product_id。如果有组合索引的话,mysql可以完全从索引中取到product_id,速度自然会快。再多说几句组合索引的最左优先原则:
    组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:

    对于最后一条语句,mysql会自动优化成第三条的样子~~。下面的情况就不会用到索引:
     
    1
    col_b = "aaaaaa";
    2
    col_b = "aaaa" and col_c = "cccccc";

    通过实例理解单列索引、多列索引以及最左前缀原则。实例:现在我们想查出满足以下条件的用户id:


    因为我们不想扫描整表,故考虑用索引。


    1.单列索引:

     
    1
    ALTER TABLE people ADD INDEX lname (lname);


    将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

    由 于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

    2.多列索引:


    为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

    注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引

    3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

    注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边

    建立索引的时机

    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

     
    1
    SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

    此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

    下句就不会使用:

     
    1
    SELECT * FROM mytable WHEREt Name like'%admin'

    因此,在使用LIKE时应注意以上的区别。



    1.5 索引实战(摘抄)

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,

    这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计不充份的连接条件不可优化的where子句

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。

     

    一、不合理的索引设计----

    例:表record620000行,试看在不同的索引下,下面几个 SQL的运行情况:

    ---- 1.date上建有一非个群集索引

    ---- 2.date上的一个群集索引

     
    1
    select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 (14秒)
    2
    3
    select date,sum(amount) from record group by date(28秒)
    4
    5
    select count(*) from record where date >'19990901' and place in ('BJ','SH')(14秒)
    6
    7
    ---- 分析:---- 在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。

    ---- 3.placedateamount上的组合索引

    ---- 4.dateplaceamount上的组合索引

     
    1
    select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1秒)
    2
    3
    select date,sum(amount) from record group by date(11秒)
    4
    5
    select count(*) from record where date >'19990901' and place in ('BJ','SH')(< 1秒)
    6
    7
    ---- 分析:---- 这是一个合理的组合索引。它将date作为前导列,使每个SQL都可以利用索引,并且在第一和第三个SQL中形成了索引覆盖,因而性能达到了最优。

    ---- 5.总结:----

    缺省情况下建立的索引是非群集索引,但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。

    一般来说:

    .有大量重复值、且经常有范围查询(between, >,< >=,< =)和order bygroup by发生的列,可考虑建立群集索引;

    .经常同时存取多列,且每列都含有重复值可考虑建立组合索引;

    .组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。

     

    二、不充份的连接条件:

    例:表card7896行,在card_no上有一个非聚集索引,表account191122行,在account_no上有一个非聚集索引,试看在不同的表连接条件下,两个SQL的执行情况:

    ---- 分析:---- 

    • 在第一个连接条件下,最佳查询方案是将account作外层表,card作内层表,利用card上的索引,其I/O次数可由以下公式估算为:外层表account上的22541+(外层表account191122*内层表card上对应外层表第一行所要查找的3页)=595907I/O
    • 在第二个连接条件下,最佳查询方案是将card作外层表,account作内层表,利用account上的索引,其I/O次数可由以下公式估算为:外层表card上的1944+(外层表card7896*内层表account上对应外层表每一行所要查找的4页)= 33528I/O

    可见,只有充份的连接条件,真正的最佳方案才会被执行。

    总结:

    1.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。

    2.查看执行方案的方法-- set showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)

     

    三、不可优化的where子句

    1.例:下列SQL条件语句中的列都建有恰当的索引,但执行速度却非常慢:

     
    1
    select * from record where substring(card_no,1,4)='5378'(13秒)
    2
    3
    select * from record where amount/30< 1000(11秒)
    4
    5
    select * from record where convert(char(10),date,112)='19991201'(10秒)
    6
    7
    分析:
    8
    9
    where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;
    10
    11
    如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:
    12
    13
    select * from record where card_no like'5378%'(< 1秒)
    14
    15
    select * from record where amount< 1000*30(< 1秒)
    16
    17
    select * from record where date= '1999/12/01'(< 1秒)

    你会发现SQL明显快起来!

    2.例:表stuff200000行,id_no上有非群集索引,请看下面这个SQL

     
    1
    select count(*) from stuff where id_no in('0','1')(23秒)
    2
    3
    分析:----
    4
    5
    where条件中的'in'在逻辑上相当于'or',所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。
    6
    7
    我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用id_no上的索引;
    8
    9
    但实际上(根据showplan),它却采用了"OR策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用id_no上索引,并且完成时间还要受tempdb数据库性能的影响。
    10
    11
    实践证明,表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时间竟达到220秒!还不如将or子句分开:
    12
    13
    select count(*) from stuff where id_no='0' select count(*) from stuff where id_no='1'
    14
    15
    得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3秒,在620000行下,时间也只有4秒。
    16
    17
    或者,用更好的方法,写一个简单的存储过程:
    18
    19
    create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d
    20
    21
    直接算出结果,执行时间同上面一样快!

     

    ---- 总结:---- 

    可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。

    1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边

    2.inor子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。

    3.善于使用存储过程,它使SQL变得更加灵活和高效

    从以上这些例子可以看出,SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。


    部分引用地址:http://blog.csdn.net/gprime/article/details/1687930


展开全文
SQL优化
  • MySQL索引优化总结

    2019-05-06 19:56:37
    文章目录MySQL索引优化总结select_typeExtra与type详细说明索引的CURD**索引的创建**索引的删除索引的查看索引的类型具体例子 MySQL索引优化总结 mysql> explain select * from t_student_course where stu_id =...

    MySQL索引优化总结

    mysql> explain select * from t_student_course where stu_id = 2;
    – 结果:
    id: 1
    select_type: SIMPLE – 查询类型(简单查询,联合查询,子查询)
    table: user – 显示这一行的数据是关于哪张表的
    type: range – 区间索引(在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。
    possible_keys: birthday – 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
    key: birthday – 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。
    key_len: 4 – 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好
    ref: const – 显示哪个字段或常数与key一起被使用。
    rows: 1 – 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
    Extra: Using where; Using index – 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

    select_type


    • simple 简单select(不使用union或子查询)
    • primary 最外面的select
    • union union中的第二个或后面的select语句
    • dependent union union中的第二个或后面的select语句,取决于外面的查询
    • union result union的结果。
    • subquery 子查询中的第一个select
    • dependent subquery 子查询中的第一个select,取决于外面的查询
    • derived 导出表的select(from子句的子查询)

    Extra与type详细说明

    • Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

    • Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了

    • Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

    • Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

    • Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

    • Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上

    • Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序

    • system 表只有一行:system表。这是const连接类型的特殊情况

    • const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待

    • eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用

    • ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好+

    • range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况+

    • index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)+

    • ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

    其中type:
    如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
    如果是where used,就是使用上了where限制。
    如果是impossible where 表示用不着where,一般就是没查出来啥。
    如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。
    测试的sql

    索引的CURD

    索引的创建

    • ALTER TABLE
      适用于表创建完毕之后再添加

    ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

    ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,当前的索引名就是该字段名;
    ALTER TABLE `table_name` ADD UNIQUE (`column_list`)
    ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`)
    ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)
    
    • CREATE INDEX
      CREATE INDEX可对表增加普通索引或UNIQUE索引
    --例,只能添加这两种索引;
    CREATE INDEX index_name ON table_name (column_list)
    CREATE UNIQUE INDEX index_name ON table_name (column_list)
    
    • 另外,还可以在建表时添加
     PRIMARY KEY (`id`), 
      UNIQUE KEY `unique1` (`tel`), -- 索引名称,可要可不要,不要就是和列名一样
      KEY `index1` (`name`),
      FULLTEXT KEY `intro` (`describe`)
    

    索引的删除

    DROP INDEX `index_name` ON `talbe_name` 
    ALTER TABLE `table_name` DROP INDEX `index_name`
    -- 这两句都是等价的,都是删除掉table_name中的索引index_name;
    
    ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引,注意主键索引只能用这种方式删除
    

    索引的查看

    	show index from tablename 
    

    索引的类型

    UNIQUE唯一索引

    不可以出现相同的值,可以有NULL值

    INDEX普通索引

    允许出现相同的索引内容

    PRIMARY KEY主键索引

    不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引

    fulltext index 全文索引

    上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求

    create table t_student_info
    (
      id              bigint auto_increment comment 'id'
        primary key,
      stu_name        varchar(50)                        null comment '姓名',
      gender          tinyint  default 0                 null comment '性别',
      birthday        date                               null comment '出生日期',
      tel             varchar(20)                        null comment '电话号码',
      is_delete       tinyint  default 0                 null comment '删除状态 0:正常:1:已删除',
      create_time     datetime default CURRENT_TIMESTAMP null comment '创建时间',
      update_datetime datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改时间'
    )
      comment '学生信息表';
    
    
    create table t_course
    (
      id          bigint auto_increment comment '课程id'
        primary key,
      course_name varchar(100)      null comment '课程名',
      is_delete   tinyint default 0 null comment '删除状态 0:正常;1:已删除
    '
    )
      comment '课程表';
    
    
    create table t_student_course
    (
      stu_id    bigint not null comment '学生id',
      course_id bigint not null comment '课程id'
    )
      comment '学生课程关联表';
    
    
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (1, '语文', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (2, '数学', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (3, '英语', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (4, '物理', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (5, '生物', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (6, '化学', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (7, '历史', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (8, '地理', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (9, '政治', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (10, '计算机', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (11, '体育', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (12, '美术', 0);
    INSERT INTO index_demo.t_course (id, course_name, is_delete) VALUES (13, '音乐', 0);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (1, 1);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (1, 2);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (1, 3);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (3, 3);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (3, 4);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (4, 3);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (4, 5);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (5, 6);
    INSERT INTO index_demo.t_student_course (stu_id, course_id) VALUES (5, 7);
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (1, '王老大', 0, '2019-04-21', '133', 0, '2019-04-21 04:30:38', '2019-04-21 04:30:43');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (2, '李老二', 0, '2019-04-22', '135', 1, '2019-04-21 12:32:50', '2019-04-21 17:19:40');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (3, '张三', 0, '2019-04-14', '145', 0, '2019-04-21 12:32:50', '2019-04-21 12:32:50');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (4, '李四', 0, '2019-04-07', '167', 0, '2019-04-21 12:32:50', '2019-04-21 12:32:50');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (5, '王五', 0, '2019-04-07', '7654', 1, '2019-04-21 12:32:50', '2019-04-21 17:19:40');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (6, '马六', 0, '2019-04-28', '987', 0, '2019-04-21 12:45:05', '2019-04-21 12:45:05');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (7, '陈七
    ', 0, '2019-04-01', '789', 1, '2019-04-21 12:45:50', '2019-04-21 17:19:40');
    INSERT INTO index_demo.t_student_info (id, stu_name, gender, birthday, tel, is_delete, create_time, update_datetime) VALUES (8, '赵八', 0, '2019-04-25', '098', 0, '2019-04-21 12:46:50', '2019-04-21 12:46:50');
    
    

    具体例子

    mysql> show index from t_student_info \G;
    *************************** 1. row ***************************
            Table: t_student_info
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 7
         Sub_part: NULL
           Packed: NULL
             Null: 
       Index_type: BTREE
          Comment: 
    Index_comment: 
          Visible: YES
       Expression: NULL
    

    可以看出t_student_info表只有一个PRIMARY索引,

    mysql> explain select * from t_student_info where  id = 5 \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t_student_info
       partitions: NULL
             type: const    --注意这里 
    possible_keys: PRIMARY
              key: PRIMARY  --注意这里 
          key_len: 8
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t_student_info
       partitions: NULL
             type: ALL   --注意这里 
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 8
         filtered: 12.50
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    

    添加is_delete列索引后的变化

    mysql> alter table t_student_info add index `is_delete` (`is_delete`);
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t_student_info
       partitions: NULL
             type: ref
    possible_keys: is_delete
              key: is_delete
          key_len: 2
              ref: const
             rows: 5
         filtered: 12.50
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    

    如果再添加索引 student_name

    alter table t_student_info add index `student_name` (`stu_name`);
    

    结果为

    mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t_student_info
       partitions: NULL
             type: ref
    possible_keys: is_delete,student_name
              key: student_name
          key_len: 203
              ref: const
             rows: 1
         filtered: 62.50
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    

    下面看联合索引

    联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。

    mysql> explain select * from t_student_info where is_delete=0 and stu_name = '张三' \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t_student_info
       partitions: NULL
             type: ref
    possible_keys: is_delete_stu_name
              key: is_delete_stu_name
          key_len: 205
              ref: const,const
             rows: 1
         filtered: 100.00
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain
        -> select s.stu_name, c.course_name
        -> from t_student_info s
        ->        left join t_student_course sc on s.id = sc.stu_id
        ->        left join t_course c on c.id = sc.course_id \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: s
       partitions: NULL
             type: index
    possible_keys: NULL
              key: is_delete_stu_name
          key_len: 205
              ref: NULL
             rows: 8
         filtered: 100.00
            Extra: Using index
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: sc
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 9
         filtered: 100.00
            Extra: Using where; Using join buffer (Block Nested Loop)
    *************************** 3. row ***************************
               id: 1
      select_type: SIMPLE
            table: c
       partitions: NULL
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 8
              ref: index_demo.sc.course_id
             rows: 1
         filtered: 100.00
            Extra: NULL
    3 rows in set, 1 warning (0.00 sec)
    

    先写这些,自己火候不够,等我再读一遍《高性能mysql》再来补充

    展开全文
  • 浅谈索引优化

    2017-11-25 14:22:48
    索引优化 一:应用场景(千万条数据) 说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍,这可真有诱惑...
    索引的优化
    一:应用场景(千万条数据)
    说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍,这可真有诱惑力。可是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。
    二:概述
    1:索引(在MySQL中也叫做 'key' 键)是存储引擎用来快速找出记录的一种数据结构
    2:索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要,在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显
    3:MySQL的建立对于MySQL的高效运行很重要,索引可以大大提高MySQL的检索速度
    4:索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列(alter table dept add index myind (dname,loc);)。
    5:实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录
    6:因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件,建立索引会占用磁盘空间的索引文件

    三:索引数据类型的选择
    1:越小的数据类型通常更好
    小的数据类型通常在磁盘,内存和cup缓存中都需要更小的空间,处理起来更快
    2:简单的数据类型更好
    整型数据比起字符,处理开销更小,因为字符串的比较复杂,在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间,以及用整型数据存储ip地址
    3:尽量避免null
    应该在指定的列设定为NOT NULL,除非你想要存储NULL,在MySQL中,含有控制的列很难进行查询优化,因为他们使得索引,索引的统计信息以及比较运算更加复杂,你可以用0,应该特殊的值或者应该空串代替空值.
    2:选择合适的标识符
    1:选择合适的标识符不仅应该考虑到存储类型,而且应该考虑MySQL是怎样运算和比较的
    2:整型:
    通常作为标识符的最好选择,因为可以更快的处理,而且可以设置为AUTO-INCREMENT
    3:字符串
    尽量避免使用字符串作为标识符,它们消耗更多的空间,处理起来比较慢,而且通常来说,字符串是随机的,索引它们在索引中的位置也是随机的,这会导致页面分裂,随机访问磁盘,聚簇索引分裂(对于使用聚簇索引的存储引擎)。
    展开全文
  • 索引优化

    2017-08-17 15:28:07
    索引优化

    序言
    数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的SQL语句优化的降龙十八掌,希望有一天你能用其中一掌来驯服客服业务中横行的‘恶龙’
    总纲
    建立必要的索引
    这次传授的降龙十八掌,总纲只有一句话:建立必要的索引,这就是后面降龙十八掌的内功基础。这一点看似容易实际却很难。难就难在如何判断哪些索引是必要的,哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上,就必须熟悉数据库应用程序中的所有SQL语句,从中统计出常用的可能对性能有影响的部分SQL,分析、归纳出作为Where条件子句的字段及其组合方式;在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次,必须熟悉应用程序。必须了解哪些表是数据操作频繁的表;哪些表经常与其他表进行连接;哪些表中的数据量可能很大;对于数据量大的表,其中各个字段的数据分布情况如何;等等。对于满足以上条件的这些表,必须重点关注,因为在这些表上的索引,将对SQL语句的性能产生举足轻重的影响。不过下面还是总结了一下降龙十八掌内功的入门基础,建立索引常用的规则如下:

     

    1、表的主键、外键必须有索引;

    2、数据量超过300的表应该有索引;

    3、经常与其他表进行连接的表,在连接字段上应该建立索引;

    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;

    5、索引应该建在选择性高的字段上;

    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:

    A、正确选择复合索引中的主列字段,一般是选择性较好的字段;

    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

    C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;

    D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;

    E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

    8、频繁进行数据操作的表,不要建立太多的索引;

    9、删除无用的索引,避免对执行计划造成负面影响;

    以上是一些普遍的建立索引时的判断依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。

    第一掌 避免对列的操作


    任何对列的操作都可能导致全表扫描,这里所谓的操作包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等式的右边,甚至去掉函数。


    例1:下列SQL条件语句中的列都建有恰当的索引,但30万行数据情况下执行速度却非常慢:


    select * from record where substrb(CardNo,1,4)='5378'(13秒)


    select * from record where amount/30< 1000(11秒)


    select * from record where to_char(ActionTime,'yyyymmdd')='19991201'(10秒)


    由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的,因此它不得不进行表扫描,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表扫描,因此将SQL重写如下:


    select * from record where CardNo like '5378%'(< 1秒)


    select * from record where amount < 1000*30(< 1秒)


    select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')(< 1秒)


    差别是很明显的!


    第二掌 避免不必要的类型转换


    需要注意的是,尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致全表扫描。


    例2:表tab1中的列col1是字符型(char),则以下语句存在类型转换:


    select col1,col2 from tab1 where col1>10,


    应该写为: select col1,col2 from tab1 where col1>'10'。


    第三掌 增加查询的范围限制


    增加查询的范围限制,避免全范围的搜索。


    例3:以下查询表record 中时间ActionTime小于2001年3月1日的数据:


    select * from record where ActionTime < to_date ('20010301' ,'yyyymm')


    查询计划表明,上面的查询对表进行全表扫描,如果我们知道表中的最早的数据为2001年1月1日,那么,可以增加一个最小时间,使查询在一个完整的范围之内。修改如下: select * from record where


    ActionTime < to_date ('20010301' ,'yyyymm')


    and ActionTime > to_date ('20010101' ,'yyyymm')


    后一种SQL语句将利用上ActionTime字段上的索引,从而提高查询效率。把'20010301'换成一个变量,根据取值的机率,可以有一半以上的 机会提高效率。同理,对于大于某个值的查询,如果知道当前可能的最大值,也可以在Where子句中加上 “AND 列名<MAX(最大值)”。


    第四掌 尽量去掉"IN"、"OR"


    含有"IN"、"OR"的Where子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。


    例4:select count(*) from stuff where id_no in('0','1')(23秒)


    可以考虑将or子句分开:


    select count(*) from stuff where id_no='0'


    select count(*) from stuff where id_no='1'


    然后再做一个简单的加法,与原来的SQL语句相比,查询速度更快。

    第五掌 尽量去掉 "<>"

    尽量去掉 "<>",避免全表扫描,如果数据是枚举值,且取值范围固定,则修改为"OR"方式。

    例5:

    UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;

    以 上语句由于其中包含了"<>",执行计划中用了全表扫描(TABLE ACCESSFULL),没有用到state字段上的索引。实际应用中,由于业务逻辑的限制,字段state为枚举值,只能等于0,1或2,而且,值等于=1,2的很少,因此可以去掉"<>",利用索引来提高效率。

    修改为:UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第4种方法。

    第六掌 去掉Where子句中的IS NULL和IS NOT NULL

    Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索,因此需要通过改变查询方式,分情况讨论等方法,去掉Where子句中的IS NULL和IS NOT NULL。

    第七掌 索引提高数据分布不均匀时查询效率

    索引的选择性低,但数据的值分布差异很大时,仍然可以利用索引提高效率。A、数据分布不均匀的特殊情况下,选择性不高的索引也要创建。

    表 ServiceInfo中数据量很大,假设有一百万行,其中有一个字段DisposalCourseFlag,取值范围为枚举值:[0,1,2,3,4,5,6,7]。按照前面说的索引建立的规则,“选择性不高的字段不应该建立索引,该字段只有8种取值,索引值的重复率很高,索引选择性明显很低,因此不建索引。然而,由于该字段上数据值的分布情况非常特殊,具体如下表:


    取值范围 1~5 6 7

    占总数据量的百分比 1% 98% 1%


    而且,常用的查询中,查询DisposalCourseFlag<6 的情况既多又频繁,毫无疑问,如果能够建立索引,并且被应用,那么将大大提高这种情况的查询效率。因此,我们需要在该字段上建立索引。

    第八掌 利用HINT强制指定索引

    在ORACLE优化器无法用上合理索引的情况下,利用HINT强制指定索引。

    继续上面7的例子,ORACLE缺省认定,表中列的值是在所有数据行中均匀分布的,也就是说,在一百万数据量下,每种DisposalCourseFlag值各有12.5万数据行与之对应。假设SQL搜索条件DisposalCourseFlag=2,利用DisposalCourseFlag列上的索引进行数据搜索效率,往往不比全表扫描的高,ORACLE因此对索引“视而不见”,从而在查询路径的选择中,用其他字段上的索引甚至全表扫描。根据我们上面的分析,数据值的分布很特殊,严重的不均匀。为了利用索引提高效率,此时,一方面可以单独对该字段或该表用analyze语句进行分析,对该列搜集足够的统计数据,使ORACLE在查询选择性较高的值时能用上索引;另一方面,可以利用HINT提示,在SELECT关键字后面,加上“/*+INDEX(表名称,索引名称)*/”的方式,强制ORACLE优化器用上该索引。

    比如: select * from serviceinfo where DisposalCourseFlag=1 ;

    上面的语句,实际执行中ORACLE用了全表扫描,加上蓝色提示部分后,用到索引查询。如下:

    select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *

    from serviceinfo where DisposalCourseFlag=1;

    请注意,这种方法会加大代码维护的难度,而且该字段上索引的名称被改变之后,必须要同步所有指定索引的HINT代码,否则HINT提示将被ORACLE忽略掉。

    第九掌 屏蔽无用索引

    继续上面8的例子,由于实际查询中,还有涉及到DisposalCourseFlag=6的查询,而此时如果用上该字段上的索引,将是非常不明智的,效率也极低。因此这种情况下,我们需要用特殊的方法屏蔽该索引,以便ORACLE选择其他字段上的索引。比如,如果字段为数值型的就在表达式的字段名后,添加“+ 0”,为字符型的就并上空串:“||""”

    如: select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 。

    不过,不要把该用的索引屏蔽掉了,否则同样会产生低效率的全表扫描。
    第十掌 分解复杂查询,用常量代替变量

    对于复杂的Where条件组合,Where中含有多个带索引的字段,考虑用IF语句分情况进行讨论;同时,去掉不必要的外来参数条件,减低复杂度,以便在不同情况下用不同字段上的索引。

    继续上面9的例子,对于包含

    Where (DisposalCourseFlag < v_DisPosalCourseFlag) or(v_DisPosalCourseFlag is null) and....的查询,(这里v_DisPosalCourseFlag为一个输入变量,取值范围可能为[NULL,0,1,2,3,4,5,6,7]),可以考虑分情况用IF语句进行讨论,类似:

    IF v_DisPosalCourseFlag =1 THEN

    Where DisposalCourseFlag = 1 and ....

    ELSIF v_DisPosalCourseFlag =2 THEN

    Where DisposalCourseFlag = 2 and ....

    。。。。。。

    第十一掌 like子句尽量前端匹配

    因为like参数使用的非常频繁,因此如果能够对like子句使用索引,将很高的提高查询的效率。

    例6:select * from city where name like ‘%S%’

    以上查询的执行计划用了全表扫描(TABLE ACCESS FULL),如果能够修改为:

    select * from city where name like ‘S%’

    那 么查询的执行计划将会变成(INDEX RANGE SCAN),成功的利用了name字段的索引。这意味着OracleSQL优化器会识别出用于索引的like子句,只要该查询的匹配端是具体值。因此我们在做like查询时,应该尽量使查询的匹配端是具体值,即使用like ‘S%’。

    第十二掌 用Case语句合并多重扫描

    我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询:

    例8:1)select count(*) from emp where sal<1000;

    2)select count(*) from emp where sal between 1000 and 5000;

    3)select count(*) from emp where sal>5000;

    这样我们需要进行三次全表查询,但是如果我们使用case语句:

    select

    count (sale when sal <1000 then 1 else null end) count_poor,

    count (sale when between 1000 and 5000 then 1 else null end) count_blue,

    count (sale when sal >5000 then 1 else null end) count_poor

    from emp;

    这样查询的结果一样,但是执行计划只进行了一次全表查询。

    第十三掌 使用nls_date_format

    例9:

    select * from record where to_char(ActionTime,'mm')='12'

    这个查询的执行计划将是全表查询,如果我们改变nls_date_format,

    SQL>alert session set nls_date_formate=’MM’;

    现在重新修改上面的查询:

    select * from record where ActionTime='12'

    这样就能使用actiontime上的索引了,它的执行计划将是(INDEX RANGE SCAN)。
    第十四掌 使用基于函数的索引

    前面谈到任何对列的操作都可能导致全表扫描,例如:

    select * from emp where substr(ename,1,2)=’SM’;

    但是这种查询在客服系统又经常使用,我们可以创建一个带有substr函数的基于函数的索引,

    create index emp_ename_substr on eemp ( substr(ename,1,2) );

    这样在执行上面的查询语句时,这个基于函数的索引将排上用场,执行计划将是(INDEX RANGE SCAN)。

    第十五掌 基于函数的索引要求等式匹配

    上面的例子中,我们创建了基于函数的索引,但是如果执行下面的查询:

    select * from emp where substr(ename,1,1)=’S’

    得 到的执行计划将还是(TABLE ACCESSFULL),因为只有当数据列能够等式匹配时,基于函数的索引才能生效,这样对于这种索引的计划和维护的要求都很高。请注意,向表中添加索引是非常危险的操作,因为这将导致许多查询执行计划的变更。然而,如果我们使用基于函数的索引就不会产生这样的问题,因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

    第十六掌 使用分区索引

    在用分析命令对分区索引进行分析时,每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。

    例如,假设你已经定义了一个分区索引,并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区,而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引,所以提高了查询的速度。

    第十七掌 使用位图索引

    位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度,因为在位图索引中进行的检索是在RAM中完成的,而且也总是比传统的B树索引的速度要快。对于那些少于1000个唯一数据值的数据列建立位图索引,可以使执行效率更快。

    第十八掌 决定使用全表扫描还是使用索引

    和所有的秘笈一样,最后一招都会又回到起点,最后我们来讨论一下是否需要建立索引,也许进行全表扫描更快。在大多数情况下,全表扫描可能会导致更多的物理磁盘输入输出,但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序,那么一个要返回记录数小于10%的查询可能会读取表中大部分的数据块,这样使用索引会使查询效率提高很多。但是如果表非常有顺序,那么如果查询的记录数大于40%时,可能使用全表扫描更快。因此,有一个索引范围扫描的总体原则是:

    1)对于原始排序的表 仅读取少于表记录数40%的查询应该使用索引范围扫描。反之,读取记录数目多于表记录数的40%的查询应该使用全表扫描。

    2)对于未排序的表 仅读取少于表记录数7%的查询应该使用索引范围扫描。反之,读取记录数目多于表记录数的7%的查询应该使用全表扫描。

    总结

    以上的招式,是完全可以相互结合同时运用的。而且各种方法之间相互影响,紧密联系。这种联系既存在一致性,也可能带来冲突,当冲突发生时,需要根据实际情况进行选择,没有固定的模式。最后决定SQL优化功力的因素就是对ORACLE内功的掌握程度了。

    另外,值得注意的是:随着时间的推移和数据的累计与变化,ORACLE对SQL语句的执行计划也会改变,比如:基于代价的优化方法,随着数据量的增大,优化器可能错误的不选择索引而采用全表扫描。这种情况可能是因为统计信息已经过时,在数据量变化很大后没有及时分析表;但如果对表进行分析之后,仍然没有用上合理的索引,那么就有必要对SQL语句用HINT提示,强制用合理的索引。但这种HINT提示也不能滥用,因为这种方法过于复杂,缺乏通用性和应变能力,同时也增加了维护上的代价;相对来说,基于函数右移、去掉“IN ,OR ,<> ,IS NOT NULL”、分解复杂的SQL语句等等方法,却是“放之四海皆准”的,可以放心大胆的使用。

    同时,优化也不是“一劳永逸”的,必须随着情况的改变进行相应的调整。当数据库设计发生变化,包括更改表结构:字段和索引的增加、删除或改名等;业务逻辑发生变化:如查询方式、取值范围发生改变等等。在这种情况下,也必须对原有的优化进行调整,以适应效率上的需求。


    参考文章:点击打开链接

    展开全文
  • 连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件; 1、连接层:主要完成一些类似于连接处理,授权认证及相关的方案; 2、服务层:主要完成大多数...
  • 理解索引:索引优化

    2018-06-11 12:50:17
    最近有个需求,要修改现有存储结构,涉及查询条件和查询效率的考量,看了几篇索引和...上一篇详细介绍了explain命令,通过该命令,可以定位出在哪一步出现了性能问题,下一步就是通过优化索引来解决它。 部分内...
  • mysql索引优化

    2017-05-29 01:14:34
    十分地简单认识下与索引有关的数据结构 ...优化器不使用索引优化 索引的类型普通索引 唯一索引 主键索引 联合索引 覆盖索引 全文索引 创建索引的几大原则 简单例子体验下联合索引 1.十分简单地认识下与索
  • SQL的索引优化

    2018-08-04 14:58:23
    ,在应用层来说,主要是基于索引优化。 总纲 建立必要的索引 看似容易实际却很难,难在如何判断哪些索引是必要的,哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。建立索引常用的...
  • 本文主要讲基于Mysql的索引优化。 首先我们需要了解执行一条查询SQL时Mysql的处理过程: 其次我们需要知道,我们写的SQL在Mysql的执行顺序是怎么样的?sql的执行顺序对sql的性能优化很有帮助,很重要。在建立复合...
  • MySql 索引优化原则

    2019-07-31 16:23:26
    索引优化有很作最佳实践原则,下面对常用原则进行分析。 MySql索引底层数据结构和算法:https://blog.csdn.net/yhl_jxy/article/details/88392411 MySql explan执行计划详解:...
  • MySQL高级开发(六)–索引优化总结(优化口诀) 标签(空格分隔): MySQL MySQL高级开发六索引优化总结优化口诀 口诀 建索引注意事项 索引容易失效的几个注意点 在根据执行计划对SQL进行分析之后,...
  • 索引优化策略 关于什么是索引,如何建立索引,索引的优缺点等,请移步我的另外一篇文章mysql索引简谈 一、为什么要建立索引? 一句话,为了加快查询效率。注意这里的“查询”,而不是增删改。建立索引的列,一旦...
  • SQLServer索引优化 ——无用索引和索引缺失(三) SQL Server 索引优化——无用索引和索引缺失中,我们根据动态视图sys.dm_db_index_usage_stats探测无用索引;SQL Server 索引优化——无用索引和索引缺失(二)...
  • 学习索引优化之前安装演示数据库以供练习 使用mysql提供的sakila数据库 文件下载: http://downloads.mysql.com/docs/sakila-db.tar.gz 解压后导入数据库 shell&gt; mysql -uroot -p &lt; salila-schema....
  • Mysql索引优化之字符串索引string-to-int 前面提到通过设置字符串前缀索引来优化字符串索引的方式,这里讲一个通过将字符串转换成int的方式做一个替代 索引,这也是一种不错的方式,显然int的排序及检索更高效。 ...
  • mysql 索引 优化 面试

    2017-05-18 17:37:57
    mysql 索引 优化 面试题目: 问如何优化下面的Mysql SQL语句? select * from employee where employee.deptName in ( "departA", "departB", "departC") and tbl.locationID = 3 and tbl.level > 5; 思路: 1: ...
  • db2advis DB2索引优化建议用途:db2advis用于自动根据表、索引、物化视图统计值,自动分析sql执行效率,并给出优化建议; 此命令可以在服务器上执行,也可以在客户端执行(需要先进行编目)。1、db2advis优化建议...
  • Sql server索引优化

    2017-04-01 15:15:28
    Sql server索引优化
  • 前面几篇博文谈到索引使用场景和explain命令帮助我们分析索引的执行情况,今天进入正题,来谈谈索引优化的原则。 1、全值匹配 查询语句尽量使用全值匹配。 2、左前缀原则 如果一个索引是组合索引,索引了多列,要...
  • 1 2 3 4 5 ... 20
    收藏数 404,752
    精华内容 161,900
    热门标签
    关键字:

    索引优化