聚集索引 订阅
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况,所以,对应的聚集索引只能有一个。如果某索引不是聚集索引,则表中的行物理顺序与索引顺序不匹配,与非聚集索引相比,聚集索引有着更快的检索速度。 展开全文
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况,所以,对应的聚集索引只能有一个。如果某索引不是聚集索引,则表中的行物理顺序与索引顺序不匹配,与非聚集索引相比,聚集索引有着更快的检索速度。
信息
类    型
一种索引
特    点
逻辑顺序决定表中相应的物理顺序
适    用
含有大量非重复值的列
中文名
聚集索引
外文名
Clustered Index
聚集索引基本介绍
聚集索引是一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引也称为聚簇索引(Clustered Index),聚类索引,簇集索引 [1]  。同样,非聚集索引也称为非聚簇索引,非聚类索引,非簇集索引。聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
收起全文
精华内容
下载资源
问答
  • 聚集索引:物理存储按照索引排序非聚集索引:物理存储不按照索引排序优势与缺点聚集索引:插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入)查询数据比非聚集数据的速度快 ...
  • 主要介绍了聚集索引和非聚集索引的相关知识,比较详细整理了一些知识点,需要的朋友可以参考下
  • 一、 创建主键(主键=主键索引=聚集索引) 主键是什么? 答:拿主键可以唯一确定一条数据,它和物理存储排序一致,不能为空,一个表只能有一个。 原本没有创建的主键的表在磁盘上存储为: Id=0;username=username0;sex...
  • 一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理...由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
  • 很多人会把Primary Key和聚集索引搞混起来,或者认为这是同一个东西。这个概念是非常错误的。 主键是一个约束(constraint),他依附在一个索引上,这个索引可以是聚集索引,也可以是非聚集索引。 所以在一个(或一...
  • 关于索引的介绍,以及b+树结构图,两种索引性能比较,索引优化建议
  • 而为表加上聚集索引后,SQL Server对于数据的查找是按照聚集索引的列作为关键字进行了。因此对于聚集索引的选择对性能的影响变得十分重要了。本文从旨在从性能的角度来谈聚集索引的选择,但这仅仅是从性能方面考虑。...
  • 我们知道SQLSERVER的数据行的存储有两种数据结构:A: 堆 B :B树...除非表里有聚集索引,如果没有的话那么表里的数据的存储就是堆结构 那么非聚集索引呢?非聚集索引也是堆结构?其实SQLSERVER有几种页面类型
  • create database myIndexDemo go use myIndexDemo go create table ABC ( A ...’C’ union select 9,’B’,’C’ go select * from ABC –在ABC表上创建聚集索引 create clustered index CLU_ABC on ABC(A) GO –查看
  • MySQL索引 聚集索引

    2017-10-17 14:48:37
    MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引
  • 本篇文章对SQL Server的聚焦索引和非聚集索引进行简单分析,从而总结出聚焦索引对非聚集索引的影响。有兴趣的朋友可以看下
  • 在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)
  • 聚集索引

    千次阅读 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》。

    展开全文
  • 主要介绍了Sql Server中的非聚集索引详细介,本文讲解了非聚集索引为什么能提速这么快、什么是RID、非聚集索引的运行原理等内容,需要的朋友可以参考下
  • 目录 一、MySQL存储引擎 1.1 Innodb引擎 1.2 MyISAM引擎 1.3 InNoDB与MyISAM异同 1.4 两种引擎的选择 ...2.2 MyISAM索引实现(非聚集索引) ...2.4 用一组实例来比较聚集索引和非聚集索引的根本区别 2.4.1...

    目录

    一、MySQL存储引擎

    1.1  Innodb引擎

    1.2 MyISAM引擎

    1.3 InNoDB与MyISAM异同

    1.4 两种引擎的选择

    二、索引(Index)

    2.1 InnoDB存储引擎索引的实现

    2.2 MyISAM索引实现(非聚集索引)

    2.3 InnoDB的索引与InnoDB的索引的区别

    2.4 用一组实例来比较聚集索引和非聚集索引的根本区别

    2.4.1 根本区别

    2.4.2 聚集索引

    2.4.3 非聚集索引

    2.4.4 例子对比两种索引

    聚集索引和非聚集索引的区别总结

    三、如何选择聚集索引和非聚集索引

    四、总结


    一、MySQL存储引擎

    注意,MySQL的存储引擎是表级的,同一个数据库的不同表可以使用不同的引擎

     

    1.1  Innodb引擎

    Innodb引擎现在是MySQL的默认引擎。Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表

     

    1.2 MyISAM引擎

    在MySQL5.1之前,MyISAM是MySQL默认的引擎,它没有提供对数据库事务的支持,也不支持行级锁外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。不过和Innodb不同,MyISAM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。

     

    1.3 InNoDBMyISAM异同

    1. InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text (InNoDB从1.2.X版本开始支持全文搜索的技术)等索引,不支持 Hash 索引,但是给了又有一个特殊的解释:InnoDB存储引擎 是支持hash索引的,不过,我们必须启用,hash索引的创建由InnoDB存储引擎引擎自动优化创建,是数据库自身创建并使用,DBA(数据库管理员)无法干预;
    2. MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
    3. Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
    4. MyISAM引擎不支持外键,InnoDB支持外键
    5. MyISAM引擎的表在大量高并发的读写下会经常出现表损坏的情况
    6. 对于count()查询来说MyISAM更有优势,MyISAM直接通过计数器获取,MyISAM会有一个空间转专门又来存储行数。InnoDB需要通过扫描全部数据,虽然InNoDB存储引擎是支持行级别锁,InNoDB是行级别锁,是where对他主键是有效,非主键的都会锁全表的
    7. MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高,如果你的数据量是百万级别的,并且没有任何的事务处理,那么用MyISAM是性能最好的选择。并且MyISAM可以节省很多内存,因为MyISAM索引文件是与数据文件分开放置,并且索引是有压缩,内存使用率提高不少
    8. 平台承载的大部分项目是读多写少的项目,MyISAM读性能比InNoDB强很多

     

    MyISAM和innoDB的区别总结如下:

    MyISAM和innoDB引擎对比

    MyISAM

    innoDB

    索引类型

    非聚簇

    聚簇

    支持事务

    支持表锁

    支持行锁

    是(默认)

    支持外键

    支持全文索引

    是(5.6以后支持)

    适用操作类型

    大量select下使用

    大量insert、delete和update下使用

     

    1.4 两种引擎的选择

    • 大尺寸的数据集趋向于选择InnoDB引擎,因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。
    • 主键查询在InnoDB引擎下也会相当快,不过需要注意的是如果主键太长也会导致性能问题,因为在检索索引树的时候不管是主键索引还是辅助键索引最终都是会通过比较主键来进行检索进而取得行数据的,如果逐渐太长,那么比较主键的操作也会变复杂。
    • 大批的INSERT语句(在每个INSERT语句中写入多行,批量插入)在MyISAM下会快一些
    • 但是UPDATE语句在InnoDB下则会更快一些,尤其是在并发量大的时候。

     

    二、索引(Index)

    索引(Index)是帮助MySQL高效获取数据的排好序数据结构。MyISAM和Innodb都使用了B+树这种数据结构做为索引。每建一个索引就会将索引数据按照B+树的数据结构创建,将相关数据冗余的存储一份,但是这样能加快搜索速度,很明显的一个用空间换时间的例子。

     

    数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引非聚簇索引两种,在一个表中只能有一个聚集索引,在InnoDB引擎中以主键作为聚集索引,而非聚集索引可以有多个,除了聚集索引其他都是非聚集索引。

     

    可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引,只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时,一棵B+树就只能存储主键(指结点中存储存储主键,使用主键来进行检索),如果检索的是非主键字段,则主键索引失去作用,又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引(使这一课B+树的结点中存储辅助键,使用辅助键来进行检索),这个就是二级索引或者叫辅助键索引,除了主键索引之外其他的所有索引都是二级索引。  这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式

     

    不同的存储引擎对聚集索引和非聚集索引的实现方式是不同的:

    • 对于InnoDB来说,使用聚集索引的主键索引行数据和主键B+树存储在一起,使用非聚集索引的辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。辅助键索引也称为非主键索引或二级索引或次级索引
    • 对于MyISAM来说,它不支持聚集索引,所以主键索引和辅助键索引都是非聚集索引。主键B+树辅助键B+在叶子节点存储指向真正数据行的指针,通过主键索引树或者辅助键索引树都可以直接找到相应数据行的全部数据。

     

    通过上面的对聚集索引和非聚集索引的简单介绍我们就可以发现聚集索引的主键索引和辅助键索引叶子结点树存储内容是有区别的。而非聚集索引的主键索引和辅助键索引的存储结构其实是没有区别,叶子节点中数据区存储的都是指向数据行数据的指针,唯一的区别就是索引树节点中的索引区存储的索引字段不同了。

     

     

    2.1 InnoDB存储引擎索引的实现

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

    以上数字id是主键,姓名是辅助键

    以下为InnoDB存储引擎的表文件:

    1. .frm表的定义,就是描述表结构的文件
    2. .ibd:表的数据文件和索引文件  i表示的是InnoDBIndex  d表示的是Data

    由表文件结构就可以看出,InnoDB是支持聚集索引的,它的表数据文件和索引文件放在一起。

     

    • 为什么InnoDB非聚集索引的辅助键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)

    因为如果InnoDB的辅助键也将行数据全部存到叶子节点,虽然能避免使用辅助键索引进行检索的时候需要检索两次才找到行数据,但是这样会造成冗余的存储数据(因为同一份数据在物理存储器中只能存放在同一个位置,如果像上面这样在非聚集索引中也将表数据存在叶子节点,就只能在存储器中冗余的存两份相同的表数据),同一份数据在硬盘中存储两次,就会造成数据浪费,还有一点就是同一份数据存在两个索引上,一旦对数据库的数据进行修改,还需要保证两颗索引树的数据一致性,这又是一个很麻烦的事情,所以辅助键索引树的叶子节点存储主键值,可以节约空间和保证数据一致性,修改行数据只需要修改主键索引中的行数据就可以了,不需要再对辅助索引进行修改。

     

    • 为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?不用UUID

    由上面的辅助键索引的检索过程可以看出,辅助键索引必须依赖于主键索引才能查找到完整的行数据,表数据就存在主键索引树中,所以InnoDB引擎必须要有主键索引,如果自己不设置主键索引InnoDB也会自己选一列作为索引,如果自己建的表中没有合适的列作为索引,InnoDB也会自动创建一个隐藏列来作为主键。使用整型自增的主键是为了方便检索,很显然整型数据的比较效率要高于随机ASCII码字符串的比较。

    如果InnoDB引擎的表自己不主动定义主键,那么MySQL会自己想办法创建主键,过程如下:

    1. 如果没有为表定义PRIMARY KEY,MySQL将找到第一个UNIQUE索引,其中所有键列都是NOT NULL,而InnoDB将它用作聚集索引。
    2. 如果表没有PRIMARY KEY或合适的UNIQUE索引,InnoDB会在包含行ID值的合成列内部生成名为GEN_CLUST_INDEX的隐藏聚集索引 。这些行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段,在插入新行时会单调增加。因此,由行ID排序的行在物理上处于插入顺序。

     

    • 之前一直有一个误区,认为主键索引就是聚集索引

    纠正一下这个误区,因为MySQL的默认存储引擎是InnoDB,所以创建的主键索引就是聚集索引。其实主键索引和聚集索引并没有必然联系,非聚集索引也有主键索引。聚集索引只是一种数据存储的方式,不同的存储引擎有不同的实现。只是因为MySQL默认是InnoDB引擎,所以创建的主键也就默认是聚集索引。即主键是聚集索引还是非聚集索引取决于这个表的存储引擎是InnoDB还是MyISAM。

     

    2.2 MyISAM索引实现(非聚集索引)

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

    以上Col1列数字id是主键,Col2列的分数是辅助键

     

    以下为MyISAM存储引擎下的表文件:

    1. .frm:表的定义,就是描述表结构的文件
    2. .MYD:数据存储文件  D表示的是Data   MY表示的是MyISAM
    3. .MYI:索引存储文件  I表示的是Index  

    由表文件组成可以看出,MyISAM存储引擎是使用的非聚集索引,它的数据文件和索引文件是分开的。

     

    2.3 InnoDB索引InnoDB索引的区别

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

    我们重点关注聚簇索引,看上去InnoDB的效率明显要低于MyISAM,因为每次使用辅助索引检索都要经过两次B+树查找,而MyISAM的非聚集索引使用辅助键查询只需要一次就能找到一整行的元组数据。这不是多此一举吗?聚簇索引的优势在哪?

            1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,而不用再通过存储的地址再去硬盘中查询一次数据行,如果按照主键Id来组织数据,获得数据更快。

            2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,详细可以看计算机操作系统分页管理相关章节)会随着数据库里数据的修改而发生变化(B+树节点分裂以及Page的分裂),使用InnoDB就可以保证不管这个主键B+树(聚集索引)的节点如何变化,辅助索引树(非聚集索引)都不受影响。

            3 聚集索引的数据都是按顺序存放的,所以如果查询条件是主键,使用主键索引,那么聚集索引会非常快,因为相同范围段的数据都是连续存放在一起的。即聚集索引表记录的物理排列顺序与索引的逻辑排列顺序一致,优点是查询速度快,一旦符合条件的第一个索引值的纪录被找到,具有连续索引值的记录也一定物理的紧跟其后。聚集索引的主键索引的叶子节点中直接存储行数据,又因为B+树的叶子节点之间都会用过指针相连,所以直接就能很快将这个范围内的数据全部获取。但是非聚集索引的主键索引虽然在逻辑上相同范围的叶子节点是顺序存储在一起的,但是真实的行数据是在硬盘中散列存储的,要想获取数据还需要将存储在叶子节点中的地址取出,根据地址再去硬盘中获取数据,效率就慢了很多。这个是聚集索引的主键索引的优势,也是第一条优势的具体体现。根据局部性原理,这也会提高检索效率。

    局部性原理是指CPU访问存储器时,无论是存取指令还是存取数据,所访问的存储单元都趋于聚集在一个较小的连续区域中。

     

    聚集索引的劣势有哪些?

           1 聚集索引的缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据重排,降低了执行速度。插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入)。而非聚集索引指定了表中记录的逻辑顺序,但记录的物理顺序和索引的顺序不一致,聚集索引和非聚集索引都采用了B+树的结构,但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式(这个指针可能是真实的物理地址,也可能是对应的主键值,这根据不同的存储引擎对它实现是不同的)。非聚集索引比聚集索引层次多,添加记录不会引起数据顺序的重组。

     

    总的来说,聚集索引查询数据速度快,插入数据速度慢;非聚集索引反之。他们各自优缺点就是相反的。所以非聚集索引的优缺点看上面聚集索引的优缺点就够了。

     

    2.4 下面用一组实例来比较聚集索引和非聚集索引的根本区别

    2.4.1 根本区别

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

     

    2.4.2 聚集索引

        聚集索引表记录的排列顺序和索引的排列顺序一致(以InnoDB聚集索引的主键索引来说,叶子节点中存储的就是行数据,行数据在物理储器中的真实地址就是按照主键索引树形成的顺序进行排列的),所以查询效率快,只要找到第一个索引值记录,其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢,因为为了保证表中记录的物理和索引顺序一致,在记录插入的时候,会对数据页重新排序(因为在真实物理存储器的存储顺序只能有一种,而插入新数据必然会导致主键索引树的变化,主键索引树的顺序发生了改变,叶子节点中存储的行数据也要随之进行改变,就会发生大量的数据移动操作,所以效率会慢)。因为在物理内存中的顺序只能有一种,所以聚集索引在一个表中只能有一个

     

    2.4.3 非聚集索引

        非聚集索引制定了表中记录的逻辑顺序,但是记录的物理和索引不一定一致(在逻辑上数据是按顺序排存放的,但是物理上在真实的存储器中是散列存放的),两种索引都采用B+树结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多,不会造成数据重排。所以如果表的读操作远远多于写操作,那么就可以使用非聚集索引。

     

    2.4.4 例子对比两种索引

        聚集索引就类似新华字典中的拼音排序索引,都是按顺序进行,例如找到字典中的“爱”,就里面顺序执行找到“癌”。而非聚集索引则类似于笔画排序,索引顺序和物理顺序并不是按顺序存放的。总的来说,聚集索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块

     

    索引创建Demo

    create database IndexDemo 
    go 
    use IndexDemo 
    go 
    create table ABC 
    ( 
    A int not null, 
    B char(10), 
    C varchar(10) 
    ) 
    go 
    insert into ABC select 1,'B','C' 
    union select 5,'B','C' 
    union select 7,'B','C' 
    union select 9,'B','C' 
    go select * from abc

    这个时候查看表记录,如图一显示

    这个时候插入一条数据,

    insert into abc values('6','B','C')

    此时的查询记录为图二展示

     

    添加聚集索引,再查询数据显示为图三,此时发现表的顺序发生了变化,此时的排序按A字段的递增排序。这就说明了使用聚集索引如果插入新数据会进行重新排序

    create clustered index CLU_ABC on abc(A) 

     

    删除聚集索引,会发现表的顺序不会发生改变。

    create nonclustered index NONCLU_ABC on abc(A) 

     

    聚集索引和非聚集索引的区别总结:

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

     

    三、如何选择聚集索引和非聚集索引

    动作描述

    使用聚集索引

    使用非聚集索引

    列经常被分组排序

    返回某范围内的数据

    一个或极少不同值

    小数目的不同值

    大数目的不同值

    频繁更新的列

    外键列

    主键列

    频繁修改索引列

     

    四、总结

    我们需要搞清楚以下几个问题:

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

      分析:如果认为是的朋友,可能是受系统默认设置的影响,一般我们指定一个表的主键,如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引,而主键都是唯一的,所以理所当然的认为创建聚集索引的字段也需要唯一。

      结论:聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲,实际情况并不能随便指定,否则在性能上会是恶梦。

     

      第二:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?

      粗一看,这还真是和聚集索引的约束相背,但实际情况真可以创建聚集索引。

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

     

      第三:是不是聚集索引就一定要比非聚集索引性能优呢?

      如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

      答:否。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引在比以学分为聚集索引做查询性能更好。就是说我们用学分去建立非聚集索引,那么搜索出来之后结点中的索引数据区只存有学分的数据,还需要根据叶子节点中数据区中的地址去查询,但是如果直接将要查询的学分字段和姓名字段创建一个联合索引(也是非聚集索引),这样在索引树中查找到数据之后直接就能在节点的索引数据区取得两个索引值,就不用再通过叶子节点中数据区里面的地址再去查询一次了。

     

      第四:在MySQL数据库中通过什么描述聚集索引与非聚集索引的?

      索引是通过B+树的形式进行描述的,我们可以这样区分聚集与非聚集索引的区别:InnoDB中的聚集索引的叶节点就是最终的数据节点,InnoDB中的非聚集索引叶子节点指向的是相应的主键值。而MyISAM中非聚集索引的主键索引树和二级索引树的叶节仍然是索引节点,但它有一个指向最终数据的指针。

     

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

      聚集索引的缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据重排,降低了执行速度。插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入)。非聚集索引指定了表中记录的逻辑顺序,但记录的物理顺序和索引的顺序不一致,聚集索引和非聚集索引都采用了B+树的结构,但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多,添加记录不会引起数据顺序的重组。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。


    其他相关文章:【MySQL】InnoDB存储引擎,MyISAM存储引擎,聚集索引,非聚集索引,主键索引,二级索引他们之间的关系梳理
                            【MySQL】InnoDB行格式、数据页结构以及索引底层原理分析
                            【MySQL】主从复制实现原理详解
                            【MySQL】MySQL分库分表详解
                            【MySQL】MySQL的锁与事务隔离级别详解


    参考资料:高性能MySQL(第3版)

    展开全文
  • 主键、聚集索引、非聚集索引区别

    千次阅读 2020-07-14 10:59:36
    聚集索引 用途 强制表的实体完整性 对数据行的排序,方便查询用 一个表多少个 一个表最多一个主键 一个表最多一个聚集索引 是否允许多个字段来定义 一个主键可以多个字段来定义 一个索引...

    下面是一个简单的比较表

     主键聚集索引
    用途强制表的实体完整性对数据行的排序,方便查询用
    一个表多少个一个表最多一个主键一个表最多一个聚集索引
    是否允许多个字段来定义一个主键可以多个字段来定义一个索引可以多个字段来定义
       
    是否允许 null 数据行出现如果要创建的数据列中数据存在null,无法建立主键。
    创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。
    没有限制建立聚集索引的列一定必须 not null .
    也就是可以列的数据是 null
    参看最后一项比较
    是否要求数据必须唯一要求数据必须唯一数据即可以唯一,也可以不唯一。看你定义这个索引的 UNIQUE 设置。
    (这一点需要看后面的一个比较,虽然你的数据列可能不唯一,但是系统会替你产生一个你看不到的唯一列)
       
    创建的逻辑数据库在创建主键同时,会自动建立一个唯一索引。
    如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,则建立主键时候,同时建立一个唯一的聚集索引
    如果未使用 UNIQUE 属性创建聚集索引,数据库引擎 将向表自动添加一个四字节 uniqueifier 列。
    必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。

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


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

    注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用的表里的列,其中id是主键,建立了聚集索引。

        结合上面的表格就可以理解这句话了吧:数据行的物理顺序与列值的顺序相同,如果我们查询id比较靠后的数据,那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同,所以也就只能建立一个聚集索引了。

                                                                                           聚集索引实际存放的示意图

        从上图可以看出聚集索引的好处了,索引的叶子节点就是对应的数据节点(MySQL的MyISAM除外,此存储引擎的聚集索引和非聚集索引只多了个唯一约束,其他没什么区别),可以直接获取到对应的全部列的数据,而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询,后面会详细讲。因此在查询方面,聚集索引的速度往往会更占优势。

    创建聚集索引

    如果不创建索引,系统会自动创建一个隐含列作为表的聚集索引。

    1.创建表的时候指定主键(注意:SQL Sever默认主键为聚集索引,也可以指定为非聚集索引,而MySQL里主键就是聚集索引)

    create table t1(
        id int primary key,
        name nvarchar(255)
     
    )


    2.创建表后添加聚集索引

    MySQL

    alter table table_name add primary key(colum_name)


        值得注意的是,最好还是在创建表的时候添加聚集索引,由于聚集索引的物理顺序上的特殊性,因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序,会非常地耗费时间以及性能。
    ————————————————
     

    非聚集(unclustered)索引


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

     

        其实按照定义,除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。如果非要把非聚集索引类比成现实生活中的东西,那么非聚集索引就像新华字典的偏旁字典,他结构顺序与实际存放顺序不一定一致。

    非聚集索引的二次查询问题

    非聚集索引叶节点仍然是索引节点,只是有一个指针指向对应的数据块,此如果使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,那么他还要进行第二次的查询,查询节点上对应的数据行的数据。

    有表t1:

    其中有 聚集索引clustered index(id), 非聚集索引index(username)。

     

    使用以下语句进行查询,不需要进行二次查询,直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

    select id, username from t1 where username = '小明'
     
    select username from t1 where username = '小明'


    但是使用以下语句进行查询,就需要二次的查询去获取原数据行的score:

    select username, score from t1 where username = '小明'


    在SQL Server里面查询效率如下所示,Index Seek就是索引所花费的时间,Key Lookup就是二次查询所花费的时间。可以看的出二次查询所花费的查询开销占比很大,达到50%。

    这篇博客有一个简单示例:https://blog.csdn.net/jiadajing267/article/details/54581262
     

    总结如下:

    ____________________________________________________________________________

    我们需要搞清楚以下几个问题:

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

      分析:如果认为是的朋友,可能是受系统默认设置的影响,一般我们指定一个表的主键,如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引,而主键都是唯一的,所以理所当然的认为创建聚集索引的字段也需要唯一。

      结论:聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲,实际情况并不能随便指定,否则在性能上会是恶梦。

      第二:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?

      粗一看,这还真是和聚集索引的约束相背,但实际情况真可以创建聚集索引。

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

      第三:是不是聚集索引就一定要比非聚集索引性能优呢?

      如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

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

      第四:在数据库中通过什么描述聚集索引与非聚集索引的?

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

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

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

    展开全文
  • 但是你回答的是具体的索引,实际上索引大体可以分成二大类:聚集索引和非聚集索引。下面我们具体介绍。 聚集索引 (又叫聚簇索引) 聚集索引就好比只有正文(没有目录)的汉语字典一样。我们知道汉语字典的排列顺序是...

    前言

    面试的时候,都会被问到索引有哪几种,一般我们都会回答:主键索引,唯一索引,全文索引等等。但是你回答的是具体的索引,实际上索引大体可以分成二大类:聚集索引和非聚集索引。下面我们具体介绍。

    聚集索引 (又叫聚簇索引)

    聚集索引就好比只有正文(没有目录)的汉语字典一样。我们知道汉语字典的排列顺序是从字母“a”开头并以“z”结尾的。如果我们要找“安”这个字。那就需要找以字母‘a’开头的那部分内容,如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字。相反,如果您找到了这个字,那您就可以直接找到关于“安”字的所有信息。

    通过上面的例子我们总结一下聚集索引的特点:

    1.索引内容本身就是目录,您不需要再去查其他目录来找到您需要找的内容。
    2.叶子节点上包含着该行的所有信息。当您找到该叶子节点的时候,不需要再回表,直接可以取出该行数据的所有信息。
    3.每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。
    

    用图形化表示如下:
    在这里插入图片描述
    在上图中我们可以看到,叶子节点中包含着该数据行下的所有信息。

    因此,像这种正文内容本身就是一种按照一定规则排列并且所有信息都在叶子节点上的目录称为“聚集索引”

    表中数据的排列顺序一定是按照聚集索引来排列的,这也就是为什么大家在建表的时候,一定要建主键。如果不建主键,数据库会以自己的方式来排列数据。

    非聚集索引

    非聚集索引我们同样用字典来打比方:当遇到你不知道怎么读的字,那么你就需要利用偏旁部首来查询字典,当您在目录上找到这个字后,根据目录后面显示的页码再去字典中找字。比如:我们要找“张字”,根据偏旁部首,我们在检字表中找到“张”的页码是672页,然后,我们再翻到672页查找“张”字的相关信息。

    但是我们要知道:结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。

    我们总结一下:通过这种方式来找到您所需要的字,但它需要两个过程:

    1.先找到目录中的结果,
    2.再翻到您所需要的页码。
    

    我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。
    用图形化表示如下:
    在这里插入图片描述

    在图中我们可以看到,叶子节点中存的并不是信息,而是地址。既然是地址,那我们就需要拿着地址再回表查询数据信息的过程。

    非聚集索引的特点:

    1.一张表的聚集索引个数可能有多个,最多可以创建249个非聚集索引。
    2.先建聚集索引才能创建非聚集索引。
    3.非聚集索引数据与索引不同序。
    4.非聚集索引在叶节点上有一个“指针”直接指向要查询的数据区域
    

    聚集索引和非聚集索引优缺点:

    1.查询速度上来说:聚集索引优于非聚集索引。
    2.插入数据的速度上来说:非聚集索引要比聚集索引要快。
    

    聚集索引和非聚集索引的比较

    下面我们再总结一下二者的不同,让大家加深理解:

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

    非聚集索引的缺陷弥补

    我们知道,非聚集索引需要再回表查询的,那如何避免回表查询,或者减少回表次数呢?那我们就需要了解联合索引、覆盖索引和索引下推的概念。

    由于篇幅问题,这一块内容我放在下一章了。

    结尾

    好了,本章就讲到这里吧希望对大家有所帮助。

    另外,大家帮我关注一下我的微信公众号,可领取很多资料。

    在这里插入图片描述
    扫二维码关注公众号【Java程序员的奋斗路】可领取如下学习资料:
    1.1T视频教程(大约有100多个视频):涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程,android.等
    2.项目源码:20个JavaWeb项目源码。

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

    万次阅读 多人点赞 2019-05-19 01:25:35
    微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别: ...
  • 问题:关于索引搜索问题,聚集索引可以直接找到数据,对于非聚集索引需要回表查询,那么select count(*) from table 是否需要回表查询呢?why?(文章最后解答) 数据库两大神器:索引+锁,上篇中我们将了mysql的...
  • 聚集索引(clustered index)就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分 ...
  • 聚集索引简介

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

    万次阅读 多人点赞 2018-08-28 11:37:34
    「数据库」和「数据库索引」这两个东西是在服务器端开发领域应用最为广泛的两个概念,熟练使用数据库和数据库索引是开发人员在行业内生存的必备技能 使用索引很简单,只要能写创建表的语句,就肯定能写创建索引的...
  • Mysql存储引擎之一的Innodb的索引,可以分为聚集索引与非聚集索引,这两种索引都是使用B+树组织的。 本文不讲解什么是索引,对索引不了解的同学可以先移步到我的另外一篇文章【数据库】mysql索引简谈 在分析这两种...
  •  myisam使用的堆组织表(Heap Organize Table, HOT),没有聚集索引的概念,使用B-tree索引的存储格式,显示都是随机顺序。 innodb表是索引组织表(Index Organized Table, IOT),它的索引则是采用 clustered index ...
  • 聚集索引 我们先建如下的一张表 CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(10) NOT NULL COMMENT '学生姓名', `age` int(11) NOT NULL COMMENT '学生年龄', ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 81,101
精华内容 32,440
关键字:

聚集索引