精华内容
参与话题
问答
  • SQL优化:索引优化

    万次阅读 2017-08-22 08:18:08
     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


展开全文
  • Mysql索引优化

    2015-03-11 23:39:07
    (1)InnoDB存储引擎的索引为主键索引; (2)从多个索引选择最优的执行计划时需要花费时间; (3)如果存在大量的更新,插入或者删除,那么索引需要实时的维护; (4)在数据库内核实现索引是非常复杂的,如何最大...


    (1)InnoDB存储引擎的索引为主键索引;

    (2)从多个索引选择最优的执行计划时需要花费时间;

    (3)如果存在大量的更新,插入或者删除,那么索引需要实时的维护;

    (4)在数据库内核实现索引是非常复杂的,如何最大程度的满足并发,以及如何

    需要慎重使用索引,而不是盲目的使用索引。



    (1)单表扫描的花费为C1,使用索引扫描的花费为C2,进一步判断C1和C2的关系,如果C2小,那么使用索引扫描,如果C1小,

       那么使用单表扫描;

     (2)根据启发式规则来判断基于索引的扫描方式通常由于单表扫描的方式,可以强制优化器来使用索引。









      type:All:表示是全表扫描;

     type:const,常量表,有确定的固定值,主键索引;

     


     

           key1=10; 得到的是一个常量表的扫描,执行效率高;

          而key1 != 10;不等值操作,type:All,表示Mysq执行的是全表扫描,查询优化器觉得全表扫描的执行效率比索引扫描高,所以选择了

       全表扫描;


      key2>10,使用全表扫描,因为索引的选择率高于10%,此时选择全表扫描;

     key2<10;数据的选择率低于10%,此时选择索引扫描;


     

      key_part1为复合索引的前缀部分,而key_part2为复合索引的非前缀部分,所以无法利用到索引;

      

        Using index condition:表示使用了索引下推技术,但是无法避免读取数据文件;

        Using index:表示使用了索引,并且避免读取了数据文件;如果我们要获取的列在索引列中,那么可以使用只读索引技术快速的获取数据信息;


        

       id列参与了表达式计算,导致无法利用到id列上的索引,所以使用了全表扫描;

     

    in和between ... and ...是可以利用到索引的;






    eq_ref:等值的引用方式,引用了tk1表的id,



       如果使用了<操作,两个表的扫描方式都为全表扫描,虽然使用了索引关键字,但是没有使用到索引;





    第二个语句真正做的还是一个内连接的操作,因为等值满足空值拒绝;



    等值连接满足空值拒绝,所以查询优化器把左外连接优化为内连接;


    Cause1和Cause2表明数据的值对于消除左外连接是有影响的;




    group by 的列是一个索引列,那么可以使用索引扫描来完成分组操作;

    groub by abs(id),如果在列上执行了函数操作,此时是不能利用索引进行分组操作优化的;



    复合索引的前缀部分出现在group 的列中,那么可以使用只读索引来完成分组操作的优化;

     


         第二个语句:group by 分组语句部分没有使用复合索引的前缀部分,而是使用了中间部分,MySql不能利用索引来完成分组操作的优化,

         会使用临时表和文件排序操作;



     如果在id列上加一个函数,那么就不能利用索引了。


     

        在复合索引上进行order by的操作,那么可以利用索引来优化,Using index表示使用到了只读索引;

       


    如果把复合索引的前缀去掉,order by语句中只出现复合索引的后半部分,那么此时Mysql不支持利用索引对order by进行优化;



       在主键列上添加了-号,此时也不能利用到索引对order by进行优化;



    distinct后面跟主键列的话可以使用到索引,此时可以把distinct关键字去掉;



          第一条语句说明如果distinct后跟一个唯一索引列的话,也可以使用到索引;

         第二条语句说明如果distinct后跟一个索引列的话,也可以使用到索引;

      


       图:如果distinct后跟一个复合索引的一部分的话,可以使用到索引,但是Using temporary表明使用了临时表来进行了distinct的操作;

      第二个语句说明如果在一个普通列上执行distinct操作的话,那么不会使用到索引,使用临时表来进行去重的操作;

     


           如果在主键前面加上-号的话,从Using temporary来看的话,尽管使用到了索引,但是还是使用了临时表来完成去重的操作;



    Select tables optimized away: 表示使用了索引直接求得最大值和最小值;




        count(*)是可以利用索引来完成计数工作的;

        


         在一个普通列上求sum操作,那么会利用全表扫描,在一个复合索引的一个列上求sum操作,会使用到索引;

        


        






    第一个语句的group by 后面使用的是一个复合索引的非前缀部分,在一个前缀列上求的最值,那么mysql不支持优化;

    第二个语句表明如果group by后面是复合索引的前缀部分,那么mysql使用只读索引来优化;

     


       如果group by后面的列是复合索引的非前缀列,那么会使用Using temporary和Using filesort





    如果order by 后面是非主键索引或者是复合索引的非前缀部分,mysql也是支持使用索引来进行优化的;



           Mysql认为空值是不等于空值的;

          





    展开全文
  • MySQL索引优化案例

    千次阅读 2019-03-20 00:19:28
    开发同学或多或少会遇到系统响应慢的问题,除了业务系统本身的问题外,常常会遇到SQL查询慢的问题,这篇文章结合实际案例分析MySQL InnoDB存储引擎的索引优化,这篇文章不会介绍B+树的知识点,如果需要了解聚集索引和辅助...

    开发同学或多或少会遇到系统响应慢的问题,除了业务系统本身的问题外,常常会遇到SQL查询慢的问题,这篇文章结合实际案例分析MySQL InnoDB存储引擎的索引优化,这篇文章不会介绍B+树的知识点,如果需要了解聚集索引和辅助索引特点的同学可以参考这篇文章,这篇文章主要会介绍三星索引ICP优化.

    如何分析SQL性能

    首先是查看MySQL的状态,系统是否正常,常用的几个命令如下:

    #显示状态信息(扩展show status like ‘XXX’)
    Mysql> show status;
    #显示系统变量(扩展show variables like ‘XXX’)
    Mysql> show variables\G;
    #显示InnoDB存储引擎的状态
    Mysql> show engine innodb status\G;
    #查看当前SQL执行,包括执行状态、是否锁表等
    Mysql> show processlist ;
    

    第二步是找出系统有哪些慢查询SQL,这个要通过slowLog来查询,首先开启慢查询日志,然后在对应日志路径找到mysql-slow.log,相关命令如下所示:

    # 检查是否开启慢查询日志
    show variables like '%slow%';
    # 如果没有开启,也可以在运行时动态开启这个参数
    set global slow_query_log=ON;
    # 设置慢查询记录查询耗时多长的SQL,这里设置成100毫秒
    set long_query_time = 0.1;
    # 这里休眠500毫秒试一下慢查询日志是否会记录
    select sleep(0.5)
    

    找到了慢SQL后比较常见的做法就是用explain命令分析SQL执行计划,查看SQL语句是否命中了索引,explain的用法可以参考MySQL 性能优化神器 Explain 使用分析,在优化过程中我们可能需要看到优化前后的查询时间对比,这时候可以打开profiling开关,查看某条SQL语句的执行耗时情况,分析是哪个步骤耗时较长,相关设置如下:

    # 查看是否开启profiling
    select @@profiling;
    # 开profiling,注意测试完关闭该特性,否则耗费资源
    set profiling=1;
    # 查看所有记录profile的SQL
    show profiles;
    # 查看指定ID的SQL的详情
    show profile for query 1;
    # 测试完,关闭该特性
    set profiling=0;
    

    一个profiling例子:
    profiling

    三星索引优化策略

    三星索引的策略是根据查询语句来建立联合索引,比如有这样一条SQL,SELECT GroupId,AddTime,Status from Order WHERE GroupId = 10010 ORDER BY AddTime,如何用三星索引来优化这条查询呢?

    • 一星索引的核心就是利用索引来尽可能的过滤不必要的数据,减少数据处理的规模,对于RDBMS来说是极为关键的,比如说Order表有100万行数据,GroupId的过滤度(cardinality)是90%,Status的过滤度是0.1%,如果没有建立索引,那要扫描100万行数据去找到GroupId等于10010的那一条数据,因此这里需要把GroupId作为联合索引的第一列,你是不是加了GroupId的索引就解决问题了呢,答案是否定的,虽然GroupId索引过滤了大部分数据,但由于select 后面有order by语句,而且这条SQL查询的不止GroupId这一个字段,不可避免要二次IO,而且还要在内存里进行一次filesort,explain的执行结果如下所示:
      explain
      从上图可以看到这条SQL使用了GroupId这个索引,但是Extra里面并不是直接Using Index,而是Using Index Condition,这意味着通过索引无法完成这条查询,存储引擎还是得读取对应的记录来完成查询,不过这里使用了ICP技术把GroupId这个索引下推到存储引擎层进行过滤,而且在内存中进行了一次filesort排序.

    • 这里就要用到二星索引来优化filesort了,filesort一般出现在要排序的字段没有添加索引或者即使添加了索引但索引不是有顺序的情况下,需要在内存中进行一次排序然后再返回给客户端,如果数据量比较大的情况下排序的开销也挺大,二星索引基本的想法就是利用索引的有序性,消除orderby或者group by等需要排序的操作(备注:group by语句默认会对该字段排序),排序是非常消耗CPU资源的,大量的排序操作会把user cpu搞得很高,即使CPU吃得消,如果数据量比较大,需要排序的数据放不下内存的sort buffer,只能悲剧的和外存换进换出,性能下降的就不是一点两点了,这时候利用索引避免排序的优势就明显的体现出来了,这时候就需要建立GroupId和AddTime的联合索引了,当GroupId相等的时候联合索引的第二列AddTime就默认是有顺序的,这样就可以避免filesort,修改索引后的效果如下所示:
      explain

    • 从上面的图片可以看到filesort已经没有了,但是还是使用ICP而不是Using Index,这是因为我们Select的字段还包含Status这一列,而这一列不在联合索引中,因此存储引擎还是需要读取该行记录来获取Status的值,这时候三星索引就派上用场了, 在索引中额外添加要查询的列Status,这就是所谓的索引覆盖,即在索引的叶子节点就能够读到查询SQL所需要的所有信息,而不需要回原表去查询,如下图所示:
      explain
      这里的Extra列显示是Using Where,Using Index,意思是数据是从索引里面取,不需要回表,Using where表示需要根据其他列来过滤数据,从key_len列也看出索引的长度为5,表示只用了GroupId这个索引,索引长度的计算可以参考这篇文章

    • 除了order by,group by的字段适合作为二星索引外,范围查询(包括between and)也适合,但是当order by和范围查询同时存在时,就需要根据实际情况作出取舍了,看是filesort的开销比较大还是范围查询的开销大

    • 关于三星索引,并不是说要把select语句里所有的查询字段都加到索引列,索引字段太多会给数据库带来很大的开销,特别是会影响DML语句的性能,而且还要结合cardinality字段来分析,有些字段的取值范围很小,选择度很低,不适合作为索引.

    ICP技术介绍

    前面的章节多次提到了ICP技术,那到底什么是ICP呢,在介绍ICP之前我们先来看一下SQL语句的where条件提取规则:所有SQL的where条件,都可以归纳为3大类:Index KeyIndex Filter和Table Filter,下面的介绍以CREATE TABLE Order (GroupId int(11) DEFAULT NULL, AddTime datetime DEFAULT NULL, Status int(11) DEFAULT NULL,KEY IX_GroupId (GroupId), KEY IX_AddTime (AddTime)) ENGINE=InnoDB这个表结构为例,假设我们分别单独建立了groupIdAddTime的索引,假设我们要执行这样一条SQLselect groupid,addtime,status from Order where groupid < 10010 and addTime > '1970' and status = 0;,那SQL执行计划会是怎样呢?

    Index Key

    Index Key只是用来定位索引的起止范围,因此只在索引第一次搜索时使用,一次判断即可;

    从起始范围之后读到的每一条索引记录,均需要判断是否在Index Key的范围内,以上面的SQL为例,Index Key就是GroupId,它的范围是GroupId < 10010.

    Index Filter

    用于过滤索引查询范围中不满足查询条件的记录,因此对于索引范围中的每一条记录,均需要与Index Filter进行对比,若不满足Index Filter则直接丢弃,继续读取索引下一条记录,在前面的SQL中,Index Filter就是AddTime > '1970',MySQL会把这个过滤条件下推到存储引擎层,ICP就是Index Condition Push Down,不管是Index Key还是Index Filter对应的列必须添加了索引.

    Table Filter

    Table FilterWhere条件最后一道防线,用于过滤通过前面索引的层层考验的记录,此时的记录已经满足了Index Key的范围,并且符合Index Filter的条件,存储引擎通过回表读取了完整的记录,判断整条记录是否满足Table Filter中的查询条件,同样的,若不满足,跳过当前记录,继续读取索引的下一条记录,若满足,则返回记录,此记录满足了where的所有条件,可以返回给前端用户.还是回到前面那条SQL,Where条件中的Status=0就是Table Filter,因为Status列并没有添加索引,所以只能通过回表读取该字段来过滤.

    我们来总结下ICP的过程,MySQLIndex Filter条件AddTime>'1970'下推到存储引擎,存储引擎根据AddTime索引来过滤掉不符合where条件AddTime>'1970'的记录,然后把最终数据返回给MySQL Server,MySQL Server再根据where条件status=0来做最后的过滤,最后把数据返回给用户,这个过程减少了二次回表的次数,有效减少了磁盘IO.最后我们来看下explain语句的结果:

    Table Filter

    其他优化建议

    • 整型数据比起字符,处理开销更小,在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;
    • 尽量指定列为NOT NULL,除非你想存储NULL,在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂,你应该用0、一个特殊的值或者一个空串代替空值;
    • 当结果集只需要一行数据时使用LIMIT 1
    • 避免SELECT *,始终指定你需要的列
    • 使用连接(JOIN)来代替子查询(Sub-Queries),使用Join来代理大的分页语句,比如select … Limit 1000000,10 这条语句MySQL要根据查询条件找到符合的100万条记录,然后删掉再读取后10条记录,应该根据Where条件把主键查出来,然后根据主键去访问数据,比如select * from Order o inner join (select Id from Order where …) t where o.id=t.id limit 1000000,10;
    • where子句的查询条件里有!=,MySQL将无法使用索引
    • 使用LIKE进行搜索匹配的时候,这样索引是有效的:select * from Order where name like ‘xxx%’,而like ‘%xxx%’` 时索引无效
    展开全文
  • MySQL 数据库性能优化之索引优化

    千次阅读 2015-05-29 22:01:20
    数据库性能优化之索引优化 大家都知道索引对于数据访问的性能有非常关键的作用,都知道索引可以提高数据访问效率。 为什么索引能提高数据访问性能?他会不会有“副作用”?是不是索引创建越多,性能就越好?...

    接着上一篇 MySQL 数据库性能优化之表结构,这是 MySQL数据库性能优化专题 系列的第三篇文章:MySQL 数据库性能优化之索引优化

    大家都知道索引对于数据访问的性能有非常关键的作用,都知道索引可以提高数据访问效率。

    为什么索引能提高数据访问性能?他会不会有“副作用”?是不是索引创建越多,性能就越好?到底该如何设计索引,才能最大限度的发挥其效能?

    这篇文章主要是带着上面这几个问题来做一个简要的分析,同时排除了业务场景所带来的特殊性,请不要纠结业务场景的影响。

    • 索引为什么能提高数据访问性能?
      很多人只知道索引能够提高数据库的性能,但并不是特别了解其原理,其实我们可以用一个生活中的示例来理解。

      我们让一位不太懂计算机的朋友去图书馆确认一本叫做《MySQL性能调优与架构设计》的书是否在藏,这样对他说:“请帮我借一本计算机类的数据库书籍,是属于 MySQL 数据库范畴的,叫做《MySQL性能调优与架构设计》”。朋友会根据所属类别,前往存放“计算机”书籍区域的书架,然后再寻找“数据库”类存放位置,再找到一堆讲述“MySQL”的书籍,最后可能发现目标在藏(也可能已经借出不在书架上)。

      在这个过程中: “计算机”->“数据库”->“MySQL”->“在藏”->《MySQL性能调优与架构设计》其实就是一个“根据索引查找数据”的典型案例,“计算机”->“数据库”->“MySQL”->“在藏” 就是朋友查找书籍的索引。

      假设没有这个索引,那查找这本书的过程会变成怎样呢?朋友只能从图书馆入口一个书架一个书架的“遍历”,直到找到《MySQL性能调优与架构设计》这本书为止。如果幸运,可能在第一个书架就找到。但如果不幸呢,那就惨了,可能要将整个图书馆所有的书架都找一遍才能找到我们想要的这本书。

      注:这个例子中的“索引”是记录在朋友大脑中的,实际上,每个图书馆都会有一个非常全的实际存在的索引系统(大多位于入口显眼处),由很多个贴上了明显标签的小抽屉构成。这个索引系统中存放这非常齐全详尽的索引数据,标识出我们需要查找的“目标”在某个区域的某个书架上。而且每当有新的书籍入库,旧的书籍销毁以及书记信息修改,都需要对索引系统进行及时的修正。

    下面我们通过上面这个生活中的小示例,来分析一下索引,看看能的出哪些结论?

    • 索引有哪些“副作用”?
      1. 图书的变更(增,删,改)都需要修订索引,索引存在额外的维护成本
      2. 查找翻阅索引系统需要消耗时间,索引存在额外的访问成本
      3. 这个索引系统需要一个地方来存放,索引存在额外的空间成本
    • 索引是不是越多越好?
      1. 如果我们的这个图书馆只是一个进出中转站,里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”,那我们的索引就只会不断的修改,而很少会被用来查找图书
        所以,对于类似于这样的存在非常大更新量的数据,索引的维护成本会非常高,如果其检索需求很少,而且对检索效率并没有非常高的要求的时候,我们并不建议创建索引,或者是尽量减少索引。
      2. 如果我们的书籍量少到只有几本或者就只有一个书架,索引并不会带来什么作用,甚至可能还会浪费一些查找索引所花费的时间。
        所以,对于数据量极小到通过索引检索还不如直接遍历来得快的数据,也并不适合使用索引。
      3. 如果我们的图书馆只有一个10平方的面积,现在连放书架都已经非常拥挤,而且馆藏还在不断增加,我们还能考虑创建索引吗?
        所以,当我们连存储基础数据的空间都捉襟见肘的时候,我们也应该尽量减少低效或者是去除索引。
    • 索引该如何设计才高效?
      1. 如果我们仅仅只是这样告诉对方的:“帮我确认一本数据库类别的讲述 MySQL 的叫做《MySQL性能调优与架构设计》的书是否在藏”,结果又会如何呢?朋友只能一个大类区域一个大类区域的去寻找“数据库”类别,然后再找到 “MySQL”范畴,再看到我们所需是否在藏。由于我们少说了一个“计算机类”,朋友就必须到每一个大类去寻找。
        所以,我们应该尽量让查找条件尽可能多的在索引中,尽可能通过索引完成所有过滤,回表只是取出额外的数据字段。
      2. 如果我们是这样说的:“帮我确认一本讲述 MySQL 的数据库范畴的计算机丛书,叫做《MySQL性能调优与架构设计》,看是否在藏”。如果这位朋友并不知道计算机是一个大类,也不知道数据库属于计算机大类,那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中,然后从包含 “MySQL” 书籍中再看有哪些是“数据库”范畴的(有可能部分是讲述PHP或者其他开发语言的),然后再排除非计算机类的(虽然可能并没有必要),然后才能确认。
        所以,字段的顺序对组合索引效率有至关重要的作用,过滤效果越好的字段需要更靠前。
      3. 如果我们还有这样一个需求(虽然基本不可能):“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找,每次都到索引柜找到计算机书籍所在的区域,然后从书架上搬下一格(假设只能以一格为单位从书架上取下,类比数据库中以block/page为单位读取),取出第一本,然后再从索引柜找到计算机图书所在区域,再搬下一格,取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢?他需要从地一个书架一直往后找,当找到计算机的书,搬下一格,取出所有计算机的书,再往后,直至所有书架全部看一遍。在这个过程中,如果计算机类书籍较多,通过索引来取所花费的时间很可能要大于直接遍历,因为不断往复的索引翻阅所消耗的时间会非常长。(延伸阅读:这里有一篇以前写的关于Oracle的文章,索引扫描还是全表扫描(Index Scan Or Full Table Scan)
        所以,当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候,使用索引并不一定优于全表扫描。
      4. 如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类,抑或者图书馆的索引系统中这两个类别属性并没有关联关系,又会怎样呢?也就是说,朋友得到的是2个独立的索引,一个是告知“计算机”这个大类所在的区域,一个是“数据库”这个小类所在的区域(很可能是多个区域),那么他只能二者选其一来搜索我的需求。即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找,那这样的效率实际过程中也会比较低下。
        所以,在实际使用过程中,一次数据访问一般只能利用到1个索引,这一点在索引创建过程中一定要注意,不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。

    看完这些分析,我想大家应该了解索引优化的一些基本思路了吧 :)

    展开全文
  • 1、创建索引对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据...
  • 索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些...
  • MySQL索引与索引优化

    千次阅读 2020-09-04 16:48:00
    MySQL索引就是用于优化器上。 索引: MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构。 索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速...
  • 理解索引:索引优化

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

    千次阅读 2012-09-18 13:55:23
    MYSQL描述: 一个文章库,里面有两个表:category和article。category里面有10条分类数据。article里面有20万条。...article表里面已经把 article_category字义为了索引。数据库大小为1.3G。 问题描述: 执行一
  • sql查询优化 索引优化

    千次阅读 2013-07-08 16:16:58
    性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。 为了获得稳定的执行性能,SQL语句越简单越好。对复杂的SQL语句,要设法对之...
  • MySQL支持的索引类型 B-tree索引 Hash索引 B-tree索引 特点 加快数据的查询速度 更适合进行范围查找 原理 B+树的结构存储数据 适应范围 全值匹配的查询 匹配最左前缀的查询 匹配列前缀查询 匹配...
  • sql优化、索引优化

    2018-10-20 10:31:42
    sql优化: https://blog.csdn.net/wuseyukui/article/details/71512793     索引优化
  • mysql索引优化面试题

    千次阅读 2016-11-06 20:51:16
    本文转自:http://www.cnblogs.com/hephec/p/4557053.html建设数据库的优化大概主要就是索引优化了吧,因为我们不可能修改数据结构的情况下,提高数据库的查询效率似乎也只能用索引了。当然这也是建立在你sql语句...
  • mysql索引优化

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

    2019-01-28 18:05:04
    MySQL索引优化 一、MySQL索引基础 首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨一下如何创建索引以及索引设计的基本原则。 此部分用于测试索引创建的user表的结构如下: 什么是...
  • ES索引优化

    千次阅读 2016-07-06 14:33:43
    所以从上我可以通过索引的settings进行第一优化:  “index.translog.flush_threshold_ops”: “100000″  “index.refresh_interval”: “-1″,  这两个参数第一是到tranlog数据达到多少条进行平衡
  • SQL索引优化

    千次阅读 2016-06-14 12:05:19
    序言数据库的优化方法有很多种,在应用层来说,主要是基于索引优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的SQL语句优化的降龙十八掌,希望有一天你能用...
  • MySQL 数据库索引优化项目实战

    千次阅读 2018-07-03 02:45:54
    如今 MySQL 数据库在互联网份额日益壮大,各种项目量级的公司,都在项目中会遇到 MySQL 优化,而且站在研发层面,大多是索引优化,而且在面试中,很喜欢问索引。 本场 Chat 首先会带领大家畅聊 MySQL索引,深入理解...
  • db2 索引优化

    千次阅读 2014-06-23 20:09:35
    在数据库应用程序开发期间,开发人员倾向于在表上定义大量索引,以保证每个查询能够良好地执行。当应用程序开发完成并且数据库投入到生产环境中之后,存在过多索引将导致数据库性能下降。大量的索引意味着数据库系统...
  • 索引优化原则

    千次阅读 2014-04-01 14:05:57
    索引优化原则   1 数据类型的选择   数据类型越小越好,越简单越好,避免null值     2 索引的列顺序   索引的列顺序很重要,例如index(a,b),当where中有a或a、b时,索引会被使用,但只有b时则不会被使用。...
  • 索引优化建议

    2007-09-26 13:53:00
    优化数据库性能索引优化建议 Microsoft® SQL Server® 2000 查询优化器在多数情况下可靠地选择最高效的索引。总体索引设计策略应为查询优化器提供更多的索引选择机会,并支持其做出正确的决定。这在各种情形下可...
  • 对于任何系统来说,监控都是重要的组成部分。数据库是一切系统的核心组件,数据库的稳定性从一定程度上决定了系统的稳定性,所以,对于数据库的监控,就显得尤为重要了。常见的开源监控软件有 Nagios、Zabbix。...
  • MySql 索引优化原则

    千次阅读 2019-03-18 19:23:19
    索引优化有很作最佳实践原则,下面对常用原则进行分析。 MySql索引底层数据结构和算法:https://blog.csdn.net/yhl_jxy/article/details/88392411 MySql explan执行计划详解:...
  • MySQL 索引优化 btree hash rtree

    万次阅读 2011-07-07 10:08:31
    一:mysql里目前只支持4种索引分别是:b-tree,full-text,hash以及r-tree索引b-tree索引应该是mysql里最广泛的索引的了,除了archive,基本所有的存储引擎都支持它.1.b-tree在myisam里的形式和innodb稍有不同在 innodb
  • 关于MySQL中复合索引优化

    千次阅读 2012-08-18 11:45:29
    于是上网查了下相关的资料:(关于复合索引优化的) 两个或更多个列上的索引被称作复合索引。 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与...
  • mysql索引优化须知索引基数

    千次阅读 2019-01-31 09:33:21
    索引基数:索引该字段的数据不重复值 索引基数越大,当然数据不重复值越多,自然性能越好 其他信息,可自行了解
  • 空空如也

    1 2 3 4 5 ... 20
    收藏数 45,650
    精华内容 18,260
    热门标签
    关键字:

    索引优化

    Python学习交流群

    Python学习交流群

    CSDN官方群,等你加入

    CSDN官方系统学习渠道

    CSDN官方系统学习渠道

    扫码免费领取职业规划!