精华内容
下载资源
问答
  • 在《面试官:为啥加了索引查询会变快?》一文,我们介绍了索引的数据结构,正是因为索引使用了B+树,才...聚集索引的这特性决定了索引组织表中数据也是索引的部分 (备注:真实的B+树叶子节点是通过链表相连的,

    《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的

    我们一起来看一下InnoDB存储引擎中的索引

    聚集索引

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

    在这里插入图片描述

    (备注:真实的B+树叶子节点是通过链表相连的,这里只是为了说明聚集索引存储了行数据,凑合着看吧~)

    每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序,聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描

    辅助索引

    辅助索引(Secondary Index,也称非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了指向主键的指针。

    辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过聚集索引来找到一个完整的行记录

    在这里插入图片描述

    联合索引

    联合索引是指对表上的多个列进行索引

    从本质上来说,联合索引也是一棵B+树,不同的是联合索引的键值的数量不是1,而是大于等于2

    联合索引有如下特点:

    最左前缀原则

    创建了(a,b,c)联合索引,如下几种情况都可以走索引:

    • select * from table where a = xxx;
    • select * from table where a = xxx and b = xxx;
    • select * from table where a = xxx and b = xxx and c = xxx

    如下几种情况不走索引

    • select * from table where b = xxx;
    • select * from table where c = xxx;
    • select * from table where b = xxx and c = xxx;

    本质上讲(a,b,c)联合索引等同于(a)单列索引、(a,b)联合索引、(a,b,c)联合索引三种索引的组合,符合最左前缀原则

    覆盖索引

    InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作

    • 非聚集索引上直接可以拿到所需数据,不需要再回表查,比如 select id from table where name = xxx;(id为主键、name为索引列)
    • 在统计操作中也会使用覆盖索引。比如(a,b)联合索引,select * from table where b = xxx语句按最左前缀原则是不会走索引的,但如果是统计语句select count(*) from table where b = xxx;就会使用覆盖索引。

    选择性

    并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般的经验是,在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段,它们可取值的范围很小,称为低选择性

    按性别进行查询时,可取值的范围一般只有’M’、‘F’。因此上述SQL语句得到的结果可能是该表50%的数据(假设男女比例1∶1),这时添加B+树索引是完全没有必要的。相反,如果某个字段的取值范围很广,几乎没有重复,即属于高选择性,则此时使用B+树索引是最适合的

    所以,我们在添加索引的时候,要尽量选择高选择性的字段,反之你在低选择性的字段上加了字段,查询可能也不会走索引

    如果感觉对你有些帮忙,请收藏好,你的关注和点赞是对我最大的鼓励!
    如果想跟我一起学习,坚信技术改变世界,请关注【Java天堂】公众号,我会定期分享自己的学习成果,第一时间推送给您

    在这里插入图片描述

    展开全文
  • 目录 首先介绍这些索引的概念: 接着结合mysql进行讲解: 首先介绍这些索引的概念: 1、聚集索引 ...聚集索引:指索引项的排序方式和...因为数据在物理存放时只能有一种排列方式,所以一个表只能有一个聚集索引。...

    目录

     

    首先介绍这些索引的概念:

    接着结合mysql进行讲解:


    首先介绍这些索引的概念:

    1、聚集索引

    聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引 

    也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据,即对表的数据按索引键的顺序进行排序,然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式,所以一个表只能有一个聚集索引。

    比如字典中,用‘拼音’查汉字,就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字,就是非聚集索引,因为正文中的字并不是按照偏旁部首排序的,我们通过检字表得到正文中的字在索引中的映射,然后通过映射找到所需要的字。

    聚集索引的使用场合为: 

      a.查询命令的回传结果是以该字段为排序依据的; 

      b.查询的结果返回一个区间的值; 

      c.查询的结果返回某值相同的大量结果集。 

    聚集索引会降低 insert,和update操作的性能,所以,是否使用聚集索引要全面衡量。 

    2、非聚集索引: 索引顺序与物理存储顺序不同

    非聚集索引的使用场合为: 

      a.查询所获数据量较少时; 

      b.某字段中的数据的唯一性比较高时;

    非聚集索引必须是稠密索引

    3、聚簇索引

    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语“聚族”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行放在两个不同的地方,所以一个表只能有一个聚族索引。 

    聚族索引的优点 

    可以把相关数据保存在一起。就好像在操场上战队,一个院系一个院系的站在一起,这样要找到一个人,就先找到他的院系,然后在他的院系里找到他就行了,而不是把学校里的所有人都遍历一遍

    数据访问更快。聚族索引将索引和数据保存在同一个B-Tree中,因此从聚族索引中获取数据通常比在非聚族索引中查找更快

    4、稠密索引

    稠密索引:每个索引键值都对应有一个索引项

    稠密索引能够比稀疏索引更快的定位一条记录。但是,稀疏索引相比于稠密索引的优点是:它所占空间更小,且插入和删除时的维护开销也小。


    5、稀疏索引

    稀疏索引:相对于稠密索引,稀疏索引只为某些搜索码值建立索引记录;在搜索时,找到其最大的搜索码值小于或等于所查找记录的搜索码值的索引项,然后从该记录开始向后顺序查询直到找到为止。 

     

    接着结合mysql进行讲解:

    mysql的两种数据存储方式,一种是InnoDB,一种是MyISAM。这两种存储都是基于B+树的存储方式,但是也有点不同。

    MyIsam 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。主索引和辅助索引没有区别都是非聚集索引。索引页正常大小为1024字节,索引页存放在.MYI 文件中。MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。

    InnoDB 也使用B+Tree作为索引结构,索引页大小16,和表数据页共同存放在表空间中。从InnoDB表数据存放方式可看出InnoDB表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    InnoDB默认对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。一般来说,InnoDB 会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础。

    所以mysql innodb引擎的聚集索引、聚簇索引都默认是主键索引,如果没有指定主键,就是一个具有唯一且非空值的索引,如果不存在这样的索引,就是InnoDB自定义的隐藏主键索引,并且该索引是稠密索引。

    展开全文
  • 聚集索引

    千次阅读 2018-12-04 21:35:44
    表只能定义一个聚集索引。 让我们假设您希望在堆上使用数据创建集群索引。作为第一步,如图2-5所示,SQL Server创建数据的另一个副本,然后根据集群键的值对其进行排序。数据页在双链表链接,其中每个页面都包含...

    聚集索引指示表中数据的物理顺序,该顺序是根据聚集索引键排序的。表只能定义一个聚集索引。

    让我们假设您希望在堆表上使用数据创建集群索引。作为第一步,如图2-5所示,SQL Server创建数据的另一个副本,然后根据集群键的值对其进行排序。数据页在双链表中链接,其中每个页面都包含指向链中的下一个和上一个页面的指针。这个列表称为索引的叶级,它包含实际的表数据。

    在这里插入图片描述
    图2 - 5.聚集索引结构:叶级

    ■注意页面上的排序顺序是由槽阵列控制。页面上的实际数据没有排序。

    当叶子层包含多个页面时,SQL Server开始构建索引的中间层,如图2-6所示。

    在这里插入图片描述
    图2 - 6.聚类索引结构:中间层和叶层

    中间层为每个叶级页面存储一行。它存储了两条信息:物理地址和它引用的页面索引键的最小值。唯一的例外是第一页的第一行,其中SQL Server存储NULL,而不是最小索引键值。在这样的优化下,当您插入表中键值最低的行时,SQL Server不需要更新非叶级行。

    中间层上的页面也链接到双链表。SQL Server添加了越来越多的中间级别,直到有一个级别只包含单个页面。这一层称为根层,它为索引的入口点,如图2-7所示。
    在这里插入图片描述

    图2-7.聚集索引结构:根级别

    正如您所看到的,索引总是具有一个叶级、一个根级和零个或多个中间级。唯一的例外是索引数据适合于单个页面。在这种情况下,SQL Server不会创建单独的根级别页面,而索引只包含单个叶级别页面。

    索引中的级别数量主要取决于行和索引键大小。例如,4字节整数列上的索引在中间和根级别上需要每一行13字节。这13个字节由一个2字节的槽数组条目、一个4字节的索引键值、一个6字节的页指针和一个1字节的行开销组成,这已经足够了,因为索引键不包含可变长度和NULL列。

    因此,每行可以容纳8060字节/ 13字节=每页620行。这意味着,使用一个中间层,可以存储最多620 * 620 = 384,400页的信息。如果数据行大小为200字节,那么每个叶级页面可以存储40行,索引中最多可以存储15,376,000行,其中只有三个级别。向索引中添加另一个中间级别将基本上覆盖所有可能的整数值。

    ■注意在现实生活中,索引碎片将减少这些数字。我们将在第六章讨论索引碎片

    SQL Server可以通过三种不同的方式从索引读取数据。第一个是有序扫描。让我们假设希望从dbo运行SELECT名称。客户通过CustomerId查询下单。索引页级别上的数据已经根据CustomerId列值排序。因此,SQL Server可以从第一个页面扫描到最后一个页面的索引叶级别,并按照存储它们的顺序返回行。

    SQL Server从索引的根页面开始,从根页面读取第一行。该行引用中间页,其中包含来自表的最小键值。SQL Server读取该页面并重复该过程,直到找到叶子级别上的第一个页面。然后,SQL Server开始逐个读取行,遍历页面的链表,直到读取了所有行。图2-8说明了这个过程。

    在这里插入图片描述

    图 2 - 8.命令索引扫描

    前面查询的执行计划显示了集群索引扫描操作符,其有序属性设置为true,如图2-9所示。
    在这里插入图片描述
    图 2 - 9.排序索引扫描执行计划

    值得一提的是,order by子句不需要触发有序扫描。有序扫描意味着SQL Server根据索引键的顺序读取数据。

    SQL Server可以在前进和后退两个方向上导航索引。但是,您必须记住一个重要的方面:SQL Server在向后索引扫描期间不使用并行性。

    ■提示 你可以检查扫描方向通过检查索引扫描或索引寻求运营商属性执行计划。但是请记住,Management Studio不会在执行计划的图形表示形式中显示这些属性。您需要打开Properties窗口,通过在执行计划中选择操作符并选择View/Properties窗口菜单项或按F4键来查看它。

    SQL Server的企业版有一个称为旋转木马扫描的优化特性,它允许多个任务共享相同的索引扫描。假设会话S1扫描索引。在扫描过程中的某个时刻,另一个会话S2运行一个查询,该查询需要扫描相同的索引。通过旋转木马扫描,S2在当前扫描位置加入S1。SQL Server只读取每个页面一次,将行传递给两个会话。

    当S1扫描到达索引的末尾时,S2从索引的开头开始扫描数据,直到S2扫描开始的那一点。旋转木马扫描是另一个例子,说明为什么不能依赖索引键的顺序,以及为什么在重要的时候应该始终指定order BY子句。

    排序扫描之后的下一个访问方法称为分配顺序扫描。QL服务器通过IAM页面访问表数据,这与它通过堆表访问表数据的方式类似。从dbo中选择的名称。具有(NOLOCK)查询的客户和图2-10演示了这种方法。图2-11显示了查询执行计划。

    在这里插入图片描述
    图2 - 10.分配顺序扫描

    在这里插入图片描述
    图2 - 11.分配顺序扫描执行计划

    不幸的是,SQL Server在使用分配顺序扫描时不容易检测到。如果执行计划中的Ordered属性为false,则表示SQL Server并不关心是否按照索引键的顺序读取行,也不关心是否使用了分配顺序扫描。

    分配顺序扫描可以更快地扫描大型表,尽管它有较高的启动成本。当表很小时,SQL Server不使用这种访问方法。另一个重要的考虑因素是数据一致性。SQL Server在具有集群索引的表中不使用转发指针,分配顺序扫描可能产生不一致的结果。由于页面分割导致的数据移动,可以多次跳过或读取行。因此,SQL Server通常避免使用分配顺序扫描,除非它在未提交或可序列化事务隔离级别读取数据。

    ■注意 我们将讨论页面分裂和分化在第六章,“索引碎片,”和在第三部分讨论锁定和数据一致性,“锁定、阻塞和并发性”。

    最后一种索引访问方法称为索引寻道。从dbo中选择的名称。其中CustomerId在4到7之间的查询和图2-12说明了操作。

    在这里插入图片描述
    图2 - 12.指数寻求

    为了从表中读取行范围,SQL Server需要从范围中找到键值最小的行,即4。SQL Server从根页面开始,其中第二行引用键值最小为350的页面。它大于我们正在寻找的键值(4),SQL Server读取根页面上第一行引用的中间层数据页(1:170)。

    类似地,中间页面将SQL Server引导到第一个叶级页面(1:176)。SQL Server读取该页面,然后读取定制id为4和5的行,最后从第二个页面读取剩下的两行。
    执行计划如图2-13所示。

    在这里插入图片描述
    图2 - 13.索引查找执行计划

    正如您所猜测的,索引查找比索引扫描更有效,因为SQL Server只处理行和数据页的子集,而不是扫描整个表。

    从技术上讲,有两种索引查找操作。第一个称为单例查找,有时也称为点查找,SQL Server在其中查找并返回一行。您可以以CustomerId = 2谓词为例。另一种类型的索引查找操作称为范围扫描,它要求SQL Server查找键的最低或最高值,并扫描(向前或向后)这组行,直到扫描范围结束。CustomerId在4到7之间的谓词将导致范围扫描。这两种情况都显示为执行计划中的索引查找操作。

    正如您所猜测的,范围扫描完全有可能强制SQL Server处理来自索引的大量甚至所有数据页。例如,如果将查询更改为使用WHERE CustomerId >谓词,SQL Server将读取所有行/页,即使在执行计划中显示了索引查找操作符。您必须记住这种行为,并在查询性能调优期间始终分析范围扫描的效率。

    关系数据库中有一个概念叫SARGable谓词,它代表查找薪资。如果SQL Server可以使用索引查找操作(如果存在索引),则谓词是可SARGable。简而言之,当SQL Server能够隔离要处理的单个值或索引键值范围时,谓词是可SARGable的,从而限制了谓词计算期间的搜索。显然,使用可SARGable谓词编写查询并在任何可能的情况下使用索引搜索是有益的。

    SARGable谓词包括以下操作符:=、>、>=、<、<=、IN、BETWEEN和LIKE(在前缀匹配的情况下)。非sargable操作符包括NOT、<>、LIKE(在非前缀匹配的情况下)和NOT in。

    使谓词不可sargable的另一种情况是对表列使用函数或数学计算。SQL Server必须为它处理的每一行调用函数或执行计算。幸运的是,在某些情况下,您可以重构查询,使这些谓词成为可SARGable。表2-1显示了一些例子。

    在这里插入图片描述
    表2 - 1.重构不可SARGable谓词到可SARGable谓词的示例

    您必须记住的另一个重要因素是类型转换。在某些情况下,您可以使用不正确的数据类型使谓词不可sargable。让我们使用varchar列创建一个表,并用一些数据填充它,如清单2-6所示。
    清单2 - 6。SARG谓词和数据类型:测试表的创建

     create table dbo.Data 
     ( 
         VarcharKey varchar(10) not null, 
         Placeholder char(200) 
     ); 
     
    create unique clustered index IDX_Data_VarcharKey 
    on dbo.Data(VarcharKey); 
    
    ;with N1(C) as (select 0 union all select 0) -- 2 rows 
    ,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows 
    ,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows 
    ,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 row
    ,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows 
    ,IDs(ID) as (select row_number() over (order by (select null)) from N5) 
    insert into dbo.Data(VarcharKey) 
    select convert(varchar(10),ID) from IDs; 
    

    集群索引键列被定义为varchar,尽管它存储整数值。现在,让我们运行两个选择,如清单2-7所示,并查看执行计划。

    清单2 - 7日.SARG谓词和数据类型:使用整型参数进行选择

    declare 
       @IntParam int = '200' 
     
    select * from dbo.Data where VarcharKey = @IntParam; 
    select * from dbo.Data where VarcharKey = convert(varchar(10),@IntParam); 
    

    如图2-14所示,对于integer参数,SQL Server扫描集群索引,将varchar转换为每一行的整数。在第二种情况下,SQL Server在开始时将整型参数转换为varchar,并使用更高效的集群索引查找操作。
    在这里插入图片描述在这里插入图片描述
    图2 - 14.SARG谓词和数据类型:具有整型参数的执行计划

    ■提示注意连接谓词的列数据类型。隐式或显式数据类型转换会显著降低查询的性能。

    在unicode字符串参数的情况下,您将观察到非常类似的行为。让我们运行清单2-8所示的查询。图2-15显示了语句的执行计划。

    清单2 - 8.SARG谓词和数据类型:使用字符串参数进行选择

    select * from dbo.Data where VarcharKey = '200'; 
    select * from dbo.Data where VarcharKey = N'200'; -- unicode parameter 
    

    在这里插入图片描述
    图2-15.S ARG谓词和数据类型:带有String参数的执行计划

    如您所见,对于varchar列,unicode字符串参数是不可sargable的。这是一个比看上去大得多的问题。虽然很少以这种方式编写查询,但如清单2-8所示,现在大多数应用程序开发环境都将字符串视为unicode。结果,SQL Server客户端库为字符串对象生成unicode (nvarchar)参数,除非参数数据类型明确指定为varchar。这使得谓词non-SARGable,而且由于不必要的扫描,甚至在索引varchar列时,也会导致性能下降。

    ■重要总是在客户机应用程序指定参数的数据类型。例如,在ADO中。使用SqlDbType Parameters.Add (“@ParamName”.Varchar、<大小>).Value = stringVariable而不是Parameters.Add (“@ParamName”)…Value = stringVariable重载。在ORM框架中使用映射显式地指定类中的非unicode属性。

    值得一提的是,对于nvarchar unicode数据列,varchar参数是可SARGable的。

    -------------------译至《Pro SQL Server Internals, 2nd edition》。

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

    万次阅读 多人点赞 2019-05-19 01:25:35
    一、深入浅出理解索引结构 实际上,可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引...其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,因为“安”的拼音是...

    欢迎大家关注我的公众号【老周聊架构】,Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。

    一、深入浅出理解索引结构

    实际上,可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

    其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    如果遇到不认识的字,不知道它的发音,这时候,需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

    通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

    二、区别及优缺点

    区别:

    • 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
    • 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续
    • 聚集索引:物理存储按照索引排序;聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序。
      非聚集索引:物理存储不按照索引排序;非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。
    • 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。

    优势与缺点:

    聚集索引插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入),查询数据比非聚集数据的速度快。

    三、需要搞清楚的几个问题

    第一:聚集索引的约束是唯一性,是否要求字段也是唯一的呢?

    分析:如果认为是的朋友,可能是受系统默认设置的影响,一般我们指定一个表的主键,如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引,而主键都是唯一的,所以理所当然的认为创建聚集索引的字段也需要唯一。
      
    结论:聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲,实际情况并不能随便指定,否则在性能上会是恶梦。

    第二:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?
      
    粗一看,这还真是和聚集索引的约束相背,但实际情况真可以创建聚集索引。

    分析其原因是:如果未使用 UNIQUE 属性创建聚集索引,数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。

    第三:是不是聚集索引就一定要比非聚集索引性能优呢?
      
    如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

    答:否。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引在比以学分为聚集索引做查询性能更好。

    第四:在数据库中通过什么描述聚集索引与非聚集索引的?
      
    索引是通过二叉树的形式进行描述的,我们可以这样区分聚集与非聚集索引的区别:聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。

    第五:在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢?

    有了上面第四点的认识,我们分析这个问题就有把握了,在有主键的表中插入数据行,由于有主键唯一性的约束,所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:聚集索引由于索引叶节点就是数据页,所以如果想检查主键的唯一性,需要遍历所有数据节点才行,但非聚集索引不同,由于非聚集索引上已经包含了主键值,所以查找主键唯一性,只需要遍历所有的索引页就行(索引的存储空间比实际数据要少),这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

    四、何时使用聚集索引或非聚集索引

    动作描述 使用聚集索引 使用非聚集索引
    列经常被分组排序
    返回某范围内的数据 不应
    一个或极少不同值 不应 不应
    小数目的不同值 不应
    大数目的不同值 不应
    频繁更新的列 不应
    外键列
    主键列
    频繁修改索引列 不应

    五、结合实际,谈索引使用的误区

    理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。

    1、主键就是聚集索引–错误想法的

    这种想法是极端错误的,是对聚集索引的一种浪费。虽然默认是在主键上建立聚集索引的。

    通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。如果我们将这个列设为主键,mysql会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但这样做意义不大。

    显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。

    从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为 ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次,让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影响。

    如在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。

    通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。

    在这里之所以提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

    1).仅在主键上建立聚集索引,并且不划分时间段:
    Select gid,fariqi,neibuyonghu,title from tgongwen
    用时:128470毫秒(即:128秒)
    2).在主键上建立聚集索引,在fariq上建立非聚集索引:
    select gid,fariqi,neibuyonghu,title from Tgongwen
    where fariqi> dateadd(day,-90,getdate())
    用时:53763毫秒(54秒)
    3).将聚合索引建立在日期列(fariqi)上:
    select gid,fariqi,neibuyonghu,title from Tgongwen
    where fariqi> dateadd(day,-90,getdate())
    用时:2423毫秒(2秒)

    虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000 万容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是:在各个select语句前加:

    declare @d datetime
    set @d=getdate()
    并在select语句后加:select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

    2、只要建立索引就能显著提高查询速度–错误想法的

    事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。

    从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

    3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度–错误想法的

    上面已经谈到:在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。

    很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列):

    1).select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>’‘2004-5-5’’
    查询速度:2513毫秒
    2).select gid,fariqi,neibuyonghu,title from Tgongwen
    where fariqi>’‘2004-5-5’’ and neibuyonghu=’‘办公室’’
    查询速度:2516毫秒
    3).select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=’‘办公室’’
    查询速度:60280毫秒

    从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

    六、其他书上没有的索引使用经验总结

    1、用聚合索引比用不是聚合索引的主键速度快

    下面是实例语句:(都是提取25万条数据)
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’‘2004-9-16’’
    使用时间:3326毫秒
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
    使用时间:4470毫秒
    这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。

    2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
    用时:12936
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
    用时:18843
    这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。

    3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个:

    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’‘2004-1-1’’
    用时:6343毫秒(提取100万条)
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’‘2004-6-6’’
    用时:3170毫秒(提取50万条)
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’‘2004-9-16’’
    用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’‘2004-1-1’’ and fariqi<’‘2004-6-6’’
    用时:3280毫秒

    4、日期列不会因为有分秒的输入而减慢查询速度

    下面的例子中,共有100万条数据,2004年1月1日以后的数据有50万条,但只有两个不同的日期,日期精确到日;之前有数据50万条,有5000个不同的日期,日期精确到秒。
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’‘2004-1-1’’ order by fariqi
    用时:6390毫秒
    select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<’‘2004-1-1’’ order by fariqi
    用时:6453毫秒

    展开全文
  • 一、深入浅出理解索引结构  实际上,可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也... 其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字
  • 今天看SQL方面的书,看到关于 索引方面的讲解不太详细,弄的一头雾水,似懂非懂,特别是聚集索引和非聚集索引。深知索引对提高数据库查询速度的重要性,所以就花了半下午的时间在网上Google了一些这方面的文章看...
  • 数据库中聚集索引和非聚集索引

    千次阅读 2018-06-09 17:17:08
    这也是一个表只能有一个聚集索引的原因。因为这个特点,具体索引应该建在那些经常需要order by,group by,按范围取值的列上。因为数据本身就是按照聚集索引的顺序存储的。不应该建在需要频繁修改的列上,因为聚集索引...
  • 聚集索引的区别  聚集索引:物理存储按照索引排序  非聚集索引:物理存储不按照索引排序 优势与缺点 聚集索引:插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入),...
  • 2、查询时使用联合索引一个字段,如果这个字段在联合索引中所有字段的第一个,那就会用到索引,否则就无法使用到索引。 3、联合索引IDX(字段A,字段B,字段C,字段D),当仅使用字段A查询时,索引IDX就会使用到;如果...
  • 聚集索引与非聚集索引的区别

    千次阅读 2017-08-02 18:10:50
    定义聚集索引中键值的逻辑顺序决定了表中相应行的物理存储位置,因此一个表中只能有一个聚集索引。索引的逻辑顺序与相应行的物理位置一致。2.聚集索引的适用情形 我们可以在针对以下情况建立聚集索引 经常对某些列...
  • 索引是与或视图关联的磁盘上结构,可以加快从或视图检索行的速度。 索引包含由或视图的一列或多列生成的键。 这些键存储在一个结构(B 树),使 SQL Server 可以快速... 每个表只能有一个聚集索引,因为...
  •  最近在一次面试,讨论了一个这样的问题:主键和索引什么区别?当时我的回答是这样的:“主键就是加了唯一性约束的聚集索引。” “你确定你所说的是对的?” 面试官反问到。 “应该是对的。” 我不加思索地回答...
  •  1、聚集索引一个表只能有一个,非聚集索引一个表可以存在多个  聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索 引。但该...
  • mysql 聚集索引聚集索引 概念梳理

    千次阅读 2018-01-07 21:44:24
    由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(联合索引)(不过mysql的innodb只支持主键聚集索引,不支持联合聚集索引),就像电话簿按姓氏和名字进行组织...
  • 官方说法: 聚集索引  一种索引,该索引键值的逻辑顺序决定了表中相应行的物理顺序。...由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组
  • 因为数据在物理存放时只能有一种排列方式,所以一个表只能有一个聚集索引。比如字典,用‘拼音’查汉字,就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字,就是非聚集索引
  • 聚集索引是按B树结构进行组织的,B树的每一页称为一个索引节点。每个索引行包含一个键值和一个指针。指针指向B树上的某一中间级页(比如根节点指向中间级节点的索引页)或叶级索引的某个数据行(比如
  • 聚集索引聚集索引的逻辑顺序决定了...因为真实的物理存储顺序只能有一种,所以一张表只能有一个聚集索引。 主键不一定是聚集索引,可以把其他唯一行作为聚集索引的。主键索引可以直接存储指向数据行的指针。...
  • 主键、聚集索引、非聚集索引区别

    千次阅读 2020-07-14 10:59:36
    一个表最多一个聚集索引 是否允许多个字段来定义 一个主键可以多个字段来定义 一个索引可以多个字段来定义 是否允许 null 数据行出现 如果要创建的数据列数据存在null,无法建立主键。...
  • 聚集索引  一种索引,该索引键值的逻辑顺序决定了表中相应行的物理顺序。...由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像
  • 聚集索引和非聚集索引整理

    千次阅读 2017-04-16 17:14:22
    由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。     聚集索引对于那些经常要搜索范围值的列特别有效...
  • mysql聚集索引和非聚集索引区别

    万次阅读 2020-06-03 11:09:45
    聚集索引每张表只能有一个,非聚集索引可以多个。 mysql的innodb引擎必须要主键,因为数据存放在聚集索引上,即使不设置主键,mysql也会设置一个默认主键,需要去存放数据。其他索引也就是非聚集索引或者叫二级...
  • 每个表中只能有一个聚集索引。又名聚合索引。(如:新华字典的注音) 非聚集索引:通过二叉树的数据结构来描述的,索引里可能会多层关系,最后索引相邻的两条记录可能在物理上是完全不相邻的两条记录(如:...
  • MySQL系列—聚集索引和非聚集索引

    千次阅读 2017-08-12 11:19:29
    聚集索引:非聚集索引表示数据存储在一个地方,索引存储在另一个地方,索引带指针指向数据的存储位置,非聚集索引检索效率比聚集索引低,但对数据更新影响较小。 聚集索引 该索引键值的逻辑顺序决定了表中...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 40,135
精华内容 16,054
关键字:

一个表中只能有一个聚集索引