精华内容
参与话题
问答
  • 聚簇索引和非聚簇索引

    千次阅读 多人点赞 2018-08-07 15:34:30
    聚簇索引并不是一种单独的索引类型,而是...MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustere...

    参考博客:http://www.admin10000.com/document/5372.html

    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体细节依赖于其实现方式。

    MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。

    这两种索引内部都是B+树,聚集索引的叶子节点存放着一整行的数据。

    Innobd中的主键索引是一种聚簇索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。

    Innodb使用的是聚簇索引,MyISam使用的是非聚簇索引

    聚簇索引(聚集索引)

    聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。

    Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。

    聚簇索引的优缺点

    优点:

    1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快

    1. 聚簇索引对于主键的排序查找和范围查找速度非常快

    缺点:

    1. 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
    2. 更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。

    3.二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

    辅助索引(非聚簇索引)

    在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。

    Innodb辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。

    辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

    Innodb聚簇索引和MyIsam非聚簇索引的比较说明

    参考博客:https://www.cnblogs.com/zlcxbb/p/5757245.html

    InnoDB索引实现

    InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同.

    1)主键索引:

    MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    (图inndb主键索引)是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    2)InnoDB的辅助索引

       InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:

    InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引(Secondary Index, 也就是非主键索引)也会包含主键列,所以,如果主键定义的比较大,其他索引也将很大。如果想在表上定义 、很多索引,则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

    文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

    不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白1、为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,2、用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

    MyISAM索引实现

    MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

    1)主键索引:

    MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图

    这里设表一共有三列,假设我们以Col1为主键,图myisam1是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

    2)辅助索引(Secondary key)

    在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

    同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

    MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

     

     

    MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

    为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

    问题:主键索引是聚集索引还是非聚集索引?

    在Innodb下主键索引是聚集索引,在Myisam下主键索引是非聚集索引

    聚簇索引和非聚簇索引的区别

    聚簇索引的叶子节点存放的是主键值和数据行,支持覆盖索引;二级索引的叶子节点存放的是主键值或指向数据行的指针。

    由于节子节点(数据页)只能按照一颗B+树排序,故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织,所以一张表可以有多个辅助索引

    展开全文
  • MySQL聚簇索引和非聚簇索引的理解

    千次阅读 2019-07-10 00:41:45
    关于聚簇索引和非聚簇索引的概念很多同学找了很多教程但是仍然很迷糊。 这里给出一篇翻译,并给出我的配图,希望对大家理解有帮助。 英文原文:...

    关于聚簇索引和非聚簇索引的概念很多同学找了很多教程但是仍然很迷糊。

    这里给出一篇翻译,并给出我的配图,希望对大家理解有帮助。

     

    英文原文:http://www.mysqltutorial.org/mysql-index/mysql-clustered-index/

    一、聚簇索引的概念

    一般来说索引就是如B-树这类可以来存储键值方便快速查找的数据结构。

    聚簇索引是物理索引,数据表就是按顺序存储的,物理上是连续的。

    一旦创建了聚簇索引,表中的所有列都根据构造聚簇索引的关键列来存储。

    (我的理解,所有的记录行都根据聚簇索引顺序存储,如按照主键Id递增方式依次物理顺序存储)

    因为聚簇索引是按该列的排序存储的,因此一个表只能有一个聚簇索引。

    二、MySQL中InnoDB表的聚簇索引

    每个InnoDB表都需要一个聚簇索引。该聚簇索引可以帮助表优化增删改查操作。

    如果你为表定义了一个主键,MySQL将使用主键作为聚簇索引。

    如果你不为表指定一个主键,MySQL讲索第一个组成列都not null的唯一索引作为聚簇索引。

    如果InnoBD表没有主键且没有适合的唯一索引(没有构成该唯一索引的所有列都NOT NULL),MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。

    因此每个InnoDB表都有且仅有一个聚簇索引。

     

    所有不是聚簇索引的索引都叫非聚簇索引或者辅助索引。

    在InnDB存储引擎中,每个辅助索引的每条记录都包含主键,也包含非聚簇索引指定的列。

    MySQL使用这个主键值来检索局促索引。

    因此应该尽可能将主键缩短,否则辅助索引占用空间会更大。

    一般来说用自增的整数型列作为主键列。

    -----------------------华丽分隔符-------------------

    简单解释

    聚簇索引和非聚簇索引

    下面举例聚簇索引和非聚簇索引的区别。

     

    注意:这里的主键是非自增的。普通索引K表示普通的索引非唯一索引。

     

     

     

    主键是采用B+Tree的数据结构(请看左图),根据上文可以知主键为聚簇索引,物理存储是根据ID的增加排序递增连续存储的。

    普通索引K也是B+Tree的数据结构(请看右图),但是它不是聚簇索引,因此为非聚簇索引或者辅助索引聚簇索引只可能是主键,或者所有组成唯一键的所有列都为NOT NULL的第一个唯一索引,或者隐式创建的聚簇索引这三种情况)。

    他的叶子节点存储的是索引列的值,它的数据域是聚簇索引即ID。

     

     

    假如普通索引k为非唯一索引,要查询k=3的数据。

    需要在k索引查找k=3得到id=30。

    然后在左侧的ID索引树查找ID=30对应的记录R3。

    然后K索引树继续向右查找,发现下一个是k=5不满足(非唯一索引后面有可能有相等的值,因此向右查找到第一个不等于3的地方),停止。

    整个过程从K索引树到主键索引树的过程叫做“回表”。

     

     

    更多进阶内容参考极客时间《MySQL45讲》

     

    创作不易,如果觉得本文对你有帮助,欢迎点赞,欢迎关注我,如果有补充欢迎评论交流,我将努力创作更多更好的文章。

    展开全文
  • 聚簇索引和非聚簇索引的区别

    万次阅读 多人点赞 2018-07-25 10:39:04
    参考链接:和刚入门的菜鸟们聊聊--什么是聚簇索引与非聚簇索引 MYSQL索引:对聚簇索引和非聚簇索引的认识 一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。 在《数据库原理》一书中是这么...

    参考链接:和刚入门的菜鸟们聊聊--什么是聚簇索引与非聚簇索引       MYSQL索引:对聚簇索引和非聚簇索引的认识

    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:
    聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    因此,MYSQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。
    下面,我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构。

    如原始数据为:

    MyISAM引擎的数据存储方式如图:

    MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。
    从MYISAM存储的物理文件我们能看出,MYISAM引擎的索引文件(.MYI)和数据文件(.MYD)是相互独立的。

    而InnoDB按聚簇索引的形式存储数据,所以它的数据布局有着很大的不同。它存储数据的结构大致如下:

    注:聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC)和余下的列(如col2)。

    INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值,而不是行指针(row pointers),这减小了移动数据或者数据页面分裂时维护二级索引的开销,因为InnoDB不需要更新索引的行指针。其结构大致如下:

    INNODB和MYISAM的主键索引与二级索引的对比:

    InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此,通过二级索引查询首先查到是主键值,然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合,叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别,主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引,且MYISAM引擎中可以不设主键。

     

    【聚簇索引】
    平时习惯逛图书馆的童鞋可能比较清楚,如果你要去图书馆借一本书,最开始是去电脑里面查书名然后根据书名来定位藏书在那个区,哪个书柜,哪一行,第多少本。。。清晰明确,一目了然,因为藏书的结构与图书室的位置,书架的顺序,书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的,所以很容易找到。比如,你的目标藏书在C区2柜3排5仓,那么你走到B区你就很快知道前面就快到了C区了,你直接奔着2柜区就能找到了。 这就是雷同于聚簇索引的功效了,聚簇索引,实际存储的循序结构与数据存储的物理机构是一致的,所以通常来说物理顺序结构只有一种,那么一个表的聚簇索引也只能有一个,通常默认都是主键,设置了主键,系统默认就为你加上了聚簇索引,当然有人说我不想拿主键作为聚簇索引,我需要用其他字段作为索引,当然这也是可以的,这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引,然后再设置主键,这样就木有问题啦。
    总而言之,聚簇索引是顺序结构与数据存储物理结构一致的一种索引,并且一个表的聚簇索引只能有唯一的一条;

     

    【非聚簇索引】
    同样的,如果你去的不是图书馆,而是某城市的商业性质的图书城,那么你想找的书就摆放比较随意了,由于商业图书城空间比较紧正,藏书通常按照藏书上架的先后顺序来摆放的,所以如果查询到某书籍放在C区2柜3排5仓,但你可能要绕过F区,而不是A.B.C.D...连贯一致的,也可能同在C区的2柜,书柜上第一排是计算机类的书记,也可能最后一排就是医学类书籍;

    那么对照着来看非聚簇索引的概念就比较好理解了,非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;

     

     

    动手试试:看看代码怎么敲的

    建立索引之前选好表对象,假设表明为IndexTestTable此表中包含三个字段Id,Name,UniqueCode

    为了更快的进行姓名查询,我们可以在Name字段上添加非聚簇索引;

    创建索引的格式如下:

    CREATE NONCLUSTERED INDEX [index_name【索引名称】] ON [table_name【表名称】]([column_name1【列名称】],[column_name2【列名称】],...);

    我们给IndexTestTable表的Name字段添加一个非聚簇索引

    CREATE NONCLUSTERED INDEX IndexTestTable_index_name ON IndexTestTable(Name);

    给IndexTestTable表的UniqueCode字段添加一个聚簇索引

    CREATE CLUSTERED INDEX IndexTestTable_index_uniquecode ON IndexTestTable(UniqueCode)

    以上的代码是最简单最直接设置索引的方式,而通常实际应用中,会有多字段联合添加索引的情况,这个就需要你根据实际的应用查询场景,以及在where条件下最常用的查询字段,例如:在 TableX中你最经常查询的条件为:

    SELECT Name,Message 
    FROM TableX 
    WHERE 1=1 
    AND DeptId='003523' 
    AND LimitedCondition='SomeValue' 
    

    这个时候你就可以 添加一个基于 DeptId 和 LimitedCondition 两个字段的非聚簇索引,以便于加速查询速度;

    CREATE NONCLUSTERED INDEX TableX_index_departid_limitedcondition 
    ON TableX(DeptId,LimitedCondition);
    

    简言之,就是需要根据你的实际应用场景,添加有用并且高效的索引;

    展开全文
  • 索引的重要性 数据库性能优化中索引绝对是一个重量级的因素,可以说,索引使用不当,其它优化措施将毫无意义。...最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引

    本文转自https://my.oschina.net/u/1866821/blog/297673

    索引的重要性
    数据库性能优化中索引绝对是一个重量级的因素,可以说,索引使用不当,其它优化措施将毫无意义。

    聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
    最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一页就是P开头的部分,这就是物理存储顺序(聚簇索引);而不用你到目录,找到汉字“爬”所在的页码,然后根据页码找到这个字(非聚簇索引)。

    下表给出了何时使用聚簇索引与非聚簇索引:

    动作
    使用聚簇索引
    使用非聚簇索引
    列经常被分组排序


    返回某范围内的数据

    不应
    一个或极少不同值
    不应
    不应
    小数目的不同值

    不应
    大数目的不同值
    不应

    频繁更新的列
    不应

    外键列


    主键列


    频繁修改索引列
    不应



    聚簇索引的唯一性
    正式聚簇索引的顺序就是数据的物理存储顺序,所以一个表最多只能有一个聚簇索引,因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引,所以它显得更为珍贵,一个表设置什么为聚簇索引对性能很关键。

    初学者最大的误区:把主键自动设为聚簇索引
    因为这是SQLServer的默认主键行为,你设置了主键,它就把主键设为聚簇索引,而一个表最多只能有一个聚簇索引,所以很多人就把其他索引设置为非聚簇索引。这个是最大的误区。甚至有的主键又是无意义的自动增量字段,那样的话Clustered index对效率的帮助,完全被浪费了。

    刚才说到了,聚簇索引性能最好而且具有唯一性,所以非常珍贵,必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引,这个要看实际情况。

    事实上,建表的时候,先需要设置主键,然后添加我们想要的聚簇索引,最后设置主键,SQLServer就会自动把主键设置为非聚簇索引(会自动根据情况选择)。如果你已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。

    记住我们的最终目的就是在相同结果集情况下,尽可能减少逻辑IO。
    我们先从一个实际使用的简单例子开始。

    一个简单的表:

    1. CREATE TABLE [dbo].[Table1](

    2.   [ID] [int] IDENTITY(1,1) NOT NULL,

    3.   [Da

      ta1] [int] NOT NULL DEFAULT ((0)),

    4.   [Da

      ta2] [int] NOT NULL DEFAULT ((0)),

    5.   [Da

      ta3] [int] NOT NULL DEFAULT ((0)),

    6.   [Name1] [nvarchar](50) NOT NULL DEFAULT (''),

    7.   [Name2] [nvarchar](50) NOT NULL DEFAULT (''),

    8.   [Name3] [nvarchar](50) DEFAULT (''),

    9.   [DTAt] [datetime] NOT NULL DEFAULT (getdate())

    复制代码

     

    来点测试数据(10w条):


    1. declare @i int

    2. set @i = 1 

    3. while @i < 100000 

    4. begin 

    5. insert into Table1 ([Da

      ta1] ,[Da

      ta2] ,[Da

      ta3] ,[Name1],[Name2] ,[Name3]) 

    6. values(@i , 2* @i ,3*@i, CAST(@i AS NVARCHAR(50)), CAST(2*@i AS NVARCHAR(50)), CAST(3*@i AS NVARCHAR(50)))

    7. set @i = @i + 1

    8. end

    9. update table1 set dtat= DateAdd (s, da

      ta1, dtat)

    复制代码

    打开查询分析器的IO统计和时间统计:

    1. SET STATISTICS IO ON;

    2. SET STATISTICS TIME ON;

    复制代码

    显示实际的“执行计划”:


    我们最常用的SQL查询是这样的:

    1. SELECT * FROM Table1 WHERE Da

      ta1 = 2 ORDER BY DTAt DESC;

    复制代码

    先在Table1设主键ID,系统自动为该主键建立了聚簇索引。
    然后执行该语句,结果是:

    1. Table 'Table1'. Scan count 1, logical reads 911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 16 ms, elapsed time = 7 ms.

    复制代码

     

    然后我们在Data1和DTat字段分别建立非聚簇索引:

    1. CREATE NONCLUSTERED INDEX [N_Da

      ta1] ON [dbo].[Table1] 

    2. (

    3. [Da

      ta1] ASC

    4. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

      LINE = OFF) ON [PRIMARY]

    5. CREATE NONCLUSTERED INDEX [N_DTat] ON [dbo].[Table1] 

    6. (

    7. [DTAt] ASC

    8. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

      LINE = OFF) ON [PRIMARY]

    复制代码

    再次执行该语句,结果是:

    1. Table 'Table1'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 0 ms, elapsed time = 39 ms.

    复制代码

     

    可以看到设立了索引反而没有任何性能的提升而且消耗的时间更多了,继续调整。

    然后我们删除所有非聚簇索引,并删除主键,这样所有索引都删除了。建立组合索引Data1和DTAt,最后加上主键

    1. CREATE CLUSTERED INDEX [C_Da

      ta1_DTat] ON [dbo].[Table1] 

    2. (

    3. [Da

      ta1] ASC,

    4. [DTAt] ASC

    5. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

      LINE = OFF) ON [PRIMARY]

    复制代码

    再次执行语句:

    1. Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 0 ms, elapsed time = 1 ms.

    复制代码

     

    可以看到只有聚簇索引seek了,消除了index scan和nested loop,而且执行时间也只有1ms,达到了最初优化的目的。

    组合索引小结
    小结以上的调优实践,要注意聚簇索引的选择。首先我们要找到我们最多用到的SQL查询,像本例就是那句类似的组合条件查询的情况,这种情况最好使用组合聚簇索引,而且最多用到的字段要放在组合聚簇索引的前面,否则的话就索引就不会有好的效果,看下例:

     

    查询条件落在组合索引的第二个字段上,引起了index scan,效果很不好,执行时间是:

    1. Table 'Table1'. Scan count 1, logical reads 238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 16 ms, elapsed time = 22 ms.

    复制代码

    而如果仅查询条件是第一个字段也没有问题,因为组合索引最左前缀原则,实践如下:

    1. Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    2. SQL Server Execution Times:

    3. CPU time = 0 ms, elapsed time = 1 ms.

    复制代码

    从中可以看出,最多用到的字段要放在组合聚簇索引的前面。

    Index seek 为什么比 Index scan好?
    索引扫描也就是遍历B树,而seek是B树查找直接定位。
    Index scan多半是出现在索引列在表达式中。数据库引擎无法直接确定你要的列的值,所以只能扫描整个整个索引进行计算。index seek就要好很多.数据库引擎只需要扫描几个分支节点就可以定位到你要的记录。回过来,如果聚集索引的叶子节点就是记录,那么Clustered Index Scan就基本等同于full table scan。

    一些优化原则
    1、缺省情况下建立的索引是非聚簇索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说: 
    a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和order by、group by发生的列,可考 
    虑建立群集索引; 
    b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引; 
    c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。 

    2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语,任何一种索引都有助于SELECT的性能提高。 

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

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

    5、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
    Sql的优化原则2: 
    1、只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT 
    2、尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值。 
    3、尽量少用VARCHAR、TEXT、BLOB类型 
    4、如果你的数据只有你所知的少量的几个。最好使用ENUM类型  

    使用SQLServer Profiler找出数据库中性能最差的SQL
    首先打开SQLServer Profiler:

     


    然后点击工具栏“New Trace”,使用默认的模板,点击RUN。

    也许会有报错:"only TrueType fonts are supported. There id not a TrueType font"。不用怕,点击Tools菜单->Options,重新选择一个字体例如Vendana 即可。(这个是微软的一个bug)

    运行起来以后,SQLServer Profiler会监控数据库的活动,所以最好在你需要监控的数据库上多做些操作。等觉得差不多了,点击停止。然后保存trace结果到文件或者table。
    这里保存到Table:在菜单“File”-“Save as ”-“Trace table”,例如输入一个master数据库的新的table名:profileTrace,保存即可。

    找到最耗时的SQL:

    1. use master

    2. select * from profiletrace order by duration desc;

    复制代码

    找到了性能瓶颈,接下来就可以有针对性的一个个进行调优了。

    对使用SQLServer Profiler的更多信息可以参考:
    http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx

    使用SQLServer Database Engine Tuning Advisor数据库引擎优化顾问
    使用上述的SQLServer Profiler得到了trace还有一个好处就是可以用到这个优化顾问。用它可以偷点懒,得到SQLServer给您的优化顾问,例如这个表需要加个索引什么的…
    首先打开数据库引擎优化顾问:
     

    然后打开刚才profiler的结果(我们存到了master数据库的profileTrace表):

     

    点击“start analysis”,运行完成后查看优化建议(图中最后是建议建立的索引,性能提升72%)

     
    这个方法可以偷点懒,得到SQLServer给您的优化顾问。


    展开全文
  • MySQL数据库面试题(2020最新版)

    万次阅读 多人点赞 2020-03-10 17:20:40
    数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别?InnoDB引擎的4大特性存储引擎选择索引什么是索引索引有哪些优缺点?索引使用场景(重点)...
  • 聚簇索引

    2019-08-23 08:55:04
    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于其实现方式,但InnoDB的聚族索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚族索引时,它的数据行存放在索引的叶子页中。...
  • 聚簇索引聚簇索引介绍

    千次阅读 2018-08-19 09:52:14
    转载自 聚簇索引聚簇索引介绍 一. 什么是索引和建立索引的好处 什么是索引  在数据库中,索引的含义与日常意义上的“索引”一词并无多大区别,与书中的索引一样,数据库中的索引使您可以快速找到表中的...
  • Innodb中聚簇索引

    千次阅读 2018-10-17 20:59:05
    在一次面试过程中别问道Innodb中聚簇索引,让我介绍一下,当时表现很差,回答的条理不清。于是回来后对Innodb的聚簇索引进行进一步的理解和学习。 理解聚簇索引前先明确一下索引的本质是什么?  MySQL官方对索引...
  • 理解聚簇索引

    2020-02-07 13:21:55
    聚簇索引(Cluster Index)也叫索引组织表(Index-organized Table),MySQL用户经常称呼为前者。聚簇索引并不是数据库索引的某个种类,其描述的是数据的一种存储方式。我们知道,在InnoDB中,数据是以B+Tree的形式...
  • 一篇聚簇索引数据结构的文章:聚簇索引数据结构 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB存储引擎的聚簇索引的背后数据结构就是 B-Tree或者B-Tree的变种B+Tree。 当表有聚簇索引时,它的...
  • 聚集索引简介

    千次阅读 多人点赞 2019-03-01 17:34:18
    本文为转载文章,主要介绍聚集索引的概念,以便于更清楚的理解。 转载地址:https://www.cnblogs.com/aspwebchh/p/6652855.html 前段时间,公司一个新上线的网站出现页面响应速度缓慢的问题, 一位负责这个项目的...
  • MYSQL索引:对聚簇索引和非聚簇索引的认识

    万次阅读 多人点赞 2016-07-17 22:03:41
    聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。 一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。 在《数据库原理》一书中...
  • MySQL中Innodb的聚簇索引和非聚簇索引

    万次阅读 热门讨论 2018-08-29 22:22:15
    聚簇索引 数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页...
  • 但是书中又把InnoDB作为聚簇索引的代表、MyISAM作为非聚簇索引的代表,两者进行比较,这就导致了矛盾点,如果将二级索引等价于非聚簇索引,那InnoDB就不能完全代表聚簇索引;但是单从二级索引的存储形式来看,又属于...
  • 聚簇索引与非聚簇索引学习总结

    千次阅读 2016-06-29 19:32:04
    聚簇索引与非聚簇索引的区别  通常情况下,建立索引是加快查询速度的有效手段。但索引不是万能的,靠索引并不能实现对所有 数据的快速存取。事实上,如果索引策略和数据检索需求严重不符的话,建立索引...
  • 聚簇索引聚簇索引 为什么要建索引 怎么创建索引 总结 什么是索引. 我们来看看比较大众的定义,OK,那就直接百度百科吧:"索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中...
  • 索引分为聚簇索引和非聚簇索引。以一本英文课本为例,要找第8课,直接翻书,若先翻到第5课,则往后翻,再翻到第10课,则又往前翻。这本书本身就是一个索引,即“聚簇索引”。如果要找"fire”这个单词,会翻到书...
  • 这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的. 聚簇索引和非聚簇索引的概念 我们先引用官网上的一段话来看看它们是干嘛的 Every InnoDB ...
  • Mysql聚簇索引和非聚簇索引原理(数据库)

    万次阅读 多人点赞 2016-11-02 11:29:35
    一、‘页’和操作系统的关系 ①为什么要有内存管理? 我们知道,一个进程完成他的功能,需要访问磁盘加载数据到内存然后等待进入cpu运算,因为数据量大小远远大于内存大小。因此提出虚拟内存概念。...
  • 通常情况下,建立索引是加快查询速度的有效手段。但索引不是万能的,靠索 引并不能实现对所有数据的快速存取。事实上,如果索引策略和数据检索... 本文简要讨论一下聚簇索引的特点及其与非聚簇索引的区别。 建立...

空空如也

1 2 3 4 5 ... 20
收藏数 27,012
精华内容 10,804
关键字:

聚簇索引