聚集索引 订阅
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况,所以,对应的聚集索引只能有一个。如果某索引不是聚集索引,则表中的行物理顺序与索引顺序不匹配,与非聚集索引相比,聚集索引有着更快的检索速度。 展开全文
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况,所以,对应的聚集索引只能有一个。如果某索引不是聚集索引,则表中的行物理顺序与索引顺序不匹配,与非聚集索引相比,聚集索引有着更快的检索速度。
信息
类    型
一种索引
特    点
逻辑顺序决定表中相应的物理顺序
适    用
含有大量非重复值的列
中文名
聚集索引
外文名
Clustered Index
聚集索引基本介绍
聚集索引是一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引也称为聚簇索引(Clustered Index),聚类索引,簇集索引 [1]  。同样,非聚集索引也称为非聚簇索引,非聚类索引,非簇集索引。聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
收起全文
精华内容
下载资源
问答
  • 聚集索引
    2022-02-26 11:36:33

     

    一、索引方法

    Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。

    1. FULLTEXT
    即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。

    全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

    2. HASH
    由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。

    HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。

    3. BTREE
    BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。

    4. RTREE
    RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

    相对于BTREE,RTREE的优势在于范围查找。

    ps. 此段详细内容见此片博文:Mysql几种索引类型的区别及适用情况

    二、索引类型

    普通索引:仅加速查询

    唯一索引:加速查询 + 列值唯一(可以有null)

    主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个

    组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并

    全文索引:对文本的内容进行分词,进行搜索

    ps.索引合并,使用多个单列索引组合搜索

    覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖

    三、使用索引的注意事项

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

    1.索引不会包含有NULL值的列

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

    2.使用短索引

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

    3.索引列排序

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

    4.like语句操作

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

    5.不要在列上进行运算

    复制代码 代码如下:

    select * from users where YEAR(adddate)<2007;
    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:

    复制代码 代码如下:
    select * from users where adddate<‘2007-01-01';
    6.不使用NOT IN和<>操作


    唯一索引、聚集索引和聚集索引本质区别

    1.一个表只能有一个主索引-PRIMARY,且只有是数据库表才有主索引,后缀为.CDX,索引关键字是不可以重复的.哪怕是空记录也只可以有一条.
    2.候选索引可以有很多个,索引关键字同样不可以重复,同样只存在于数据库表.
    3.唯一索引,可以存在于自由表,但索引关键字不可以重复.
    4.普通索引简单的理解就是只起排序作用.索引关键字是可以重复的.可存在于自由表.

    主键与唯一索引的区别

    主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。 唯一性索引列允许空值,而主键列不允许为空值。 主键列在创建时,已经默认为空值 + 唯一索引了。 主键可以被其他表引用为外键,而唯一索引不能。 一个表最多只能创建一个主键,但可以创建多个唯一索引。 主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。 在 RBO 模式下,主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

    CREATE UNIQUE INDEX test_UniqueKey ON test (UniqueKey); 

    聚集索引:


    聚集索引基于数据行的键值,在表内排序和存储这些数据行。每个表只能有一个聚集索引,应为数据行本分只能按一个顺序存储。
    在聚集索引中,表中各行的物理顺序与索引键值的逻辑(索引)顺序相同。聚集索引通常可加快UPDATE和DELETE操作的速度,因为这两个操作需要读取 大量的数据。创建或修改聚集索引可能要花很长时间,因为执行这两个操作时要在磁盘上对表的行进行重组。

    非聚集索引:

    因为一个表中只能有一个聚集索引,如果需要在表中建立多个索引,则可以创建为非聚集索引。表中的数据并不按照非聚集索引列的顺序存储,但非聚集索引的索引行中保存了非聚集键值和行定位器,可以快捷地根据非聚集键的值来定位记录的存储位置。

    无论是聚集索引,还是非聚集索引,都可以是唯一索引。在SQL Server中,当唯一性是.数据本身的特点时,可创建唯一索引,但索引列的组合不同于表的主键。例如,如果要频繁查询表Employees(该表主键为 列Emp_id)的列Emp_name,而且要保证姓名是唯一的,则在列Emp_name上创建唯一索引。如果用户为多个员工输入了相同的姓名,则数据库 显示错误,并且不能保存该表。


    参考

    SQL Server数据库--索引 - cplvfx - 博客园什么是索引 描述: 汉语字典中的汉字按页存放,一般都有汉语拼音目录(索引)、偏旁部首目录等 我们可以根据拼音或偏旁部首,快速查找某个字词 SQL Server中的数据也是按页存放 索引: 是SQL Shttps://www.cnblogs.com/cplvfx/articles/10482236.html索引的类型及分类 - CodingPanda - 博客园一、索引方法Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。 1. FULLTEXT即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLhttps://www.cnblogs.com/LiLiliang/p/9960895.html

    唯一索引、聚集索引和聚集索引 - fthjane - 博客园主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。 唯一性索引列允许空值,而主键列不允许为空值。 主键列在创建时,已经默认为空值https://www.cnblogs.com/fthjane/p/4776737.html

    更多相关内容
  • 聚集索引:物理存储按照索引排序非聚集索引:物理存储不按照索引排序优势与缺点聚集索引:插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入)查询数据比非聚集数据的速度快 ...
  • 我们知道SQLSERVER的数据行的存储有两种数据结构:A: 堆 B :B树...除非表里有聚集索引,如果没有的话那么表里的数据的存储就是堆结构 那么非聚集索引呢?非聚集索引也是堆结构?其实SQLSERVER有几种页面类型
  • 在MySQL里,聚集索引和非聚集索引分别是什么意思,有什么区别? 在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)。 也有人把聚集索引...
  • 聚集索引 一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表...
  • 而为表加上聚集索引后,SQL Server对于数据的查找是按照聚集索引的列作为关键字进行了。因此对于聚集索引的选择对性能的影响变得十分重要了。本文从旨在从性能的角度来谈聚集索引的选择,但这仅仅是从性能方面考虑。...
  • 先创建一个聚集索引表这个表每个行由int(4字节),char(999字节)和varchar(0字节组成),所以每行为1003个字节,则8行占用空间1003*8=8024字节加上一些内部开销,可以容纳在一个页面插入数据看一下所用的数据页数量再...
  • 一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理...由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
  • 关于索引的介绍,以及b+树结构图,两种索引性能比较,索引优化建议
  • 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存储引擎之一的Innodb的索引,可以分为聚集索引与非聚集索引,这两种索引都是使用B+树组织的。 本文不讲解什么是索引,对索引不了解的同学可以先移步到我的另外一篇文章【数据库】mysql索引简谈 在分析这两种...
  • 聚集索引,这个是大家都非常熟悉的一个东西,有时候我们由于业务原因,sql写的非常复杂,需要join很多张表,然后就泪流满面了。。。这时候就有DBA或者资深的开发给你看这个猥琐的sql,通过执行计划一分析。。。...
  • 说到聚集索引,我想每个码农都明白,但是也有很多像我这样的猥程序员,只能用死记硬背来解决这个问题,什么表中只能建一个聚集索引,然后又扯到了目录查找来帮助读者记忆。。。。问题就在这里,我们不是学文科,,,...
  • 主键与聚集索引

    2020-09-11 08:57:38
    表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性。在创建或修改表时,您可以通过定义 PRIMARY KEY 约束来创建主键。
  • MySQL索引 聚集索引

    2017-10-17 14:48:37
    MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引
  • SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。 MySQL 索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引。 二.聚集索引 聚集(clustered)索引,也叫聚簇索引。 定义:数据行...

    一.索引简介

    众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引。

    SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。

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

    二.聚集索引

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

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

    单单从定义来看是不是显得有点抽象,打个比方,一个表就像是我们以前用的新华字典,聚集索引就像是拼音目录,而每个字存放的页码就是我们的数据物理地址,我们如果要查询一个“哇”字,我们只需要查询“哇”字对应在新华字典拼音目录对应的页码,就可以查询到对应的“哇”字所在的位置,而拼音目录对应的A-Z的字顺序,和新华字典实际存储的字的顺序A-Z也是一样的,如果我们中文新出了一个字,拼音开头第一个是B,那么他插入的时候也要按照拼音目录顺序插入到A字的后面,现在用一个简单的示意图来大概说明一下在数据库中的样子:

    地址idusernamescore
    0x011小明90
    0x022小红80
    0x033小华92
    ........
    0xff256小英70

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

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




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

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

    创建聚集索引

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

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

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

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

    SQL Server

    create clustered index clustered_index on table_name(colum_name)
    

    MySQL

    alter table table_name add primary key(colum_name)
    

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

    三.非聚集索引

    非聚集(unclustered)索引。

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

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




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

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

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

    即:因为Innodb二级索引存储的是主键,所以通过索引查找时,第一次查询是通过二级索引找到主键值,第二次查询是通过主键在聚簇索引找到对应的行位置

    如有以下表t1:

    idusernamescore
    1小明90
    2小红80
    3小华92
    ......
    256小英70

    以及聚集索引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%。


     

    在SQL Server里面会对查询自动优化,选择适合的索引,因此如果在数据量不大的情况下,SQL Server很有可能不会使用非聚集索引进行查询,而是使用聚集索引进行查询,即便需要扫描整个聚集索引,效率也比使用非聚集索引效率要高。


     

    本人试过在含有30w行表上建立非聚集索引,查询非聚集索引覆盖以外的列就会变成聚集索引的全索引扫描(index scan)查询来避免二次查询,而在另外一张200w行表才会用到非聚集索引seek对应的列再进行kek lookup,有关于SQL Server的有Index seek,index scan, table scan,key LookUp这几个概念,可以查看这个blog,描写比较详细。

    但在MySQL里面就算表里数据量少且查询了非键列,也不会使用聚集索引去全索引扫描,但如果强制使用聚集索引去查询,性能反而比非聚集索引查询要差,这就是两种SQL的不同之处。

    还有一点要注意的是非聚集索引其实叶子节点除了会存储索引覆盖列的数据,也会存放聚集索引所覆盖的列数据。

    如何解决非聚集索引的二次查询问题

    复合索引(覆盖索引)

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

    select col1, col2 from t1 where col1 = '213';
    

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

    在SQL Server中还有include的用法,可以把非聚集索引里包含的列包含进来,而不一定需要建立复合索引。

    四.总结与使用心得

    1. 使用聚集索引的查询效率要比非聚集索引的效率要高,但是如果需要频繁去改变聚集索引的值,写入性能并不高,因为需要移动对应数据的物理位置。
    2. 非聚集索引在查询的时候可以的话就避免二次查询,这样性能会大幅提升。
    3. 不是所有的表都适合建立索引,只有数据量大表才适合建立索引,且建立在选择性高的列上面性能会更好。

    转自:https://www.cnblogs.com/s-b-b/p/8334593.html 

    展开全文
  • 为什么索引会提高检索速度? 为什么不建议使用SELECT * ? 为什么针对区分度低的字段,例如性别,不要建索引? 为什么不建议使用mysql内置函数,而是要放在业务代码中去处理? 为什么明明建了索引,但是SQL执行...

    目录

    一、概述

    话不多说,先来问几个问题:

    二、聚集索引

    三、非聚集索引

    四、聚集索引和非聚集索引在MySQL的实现

    五、聚集索引与非聚集索引的区别

    六、适用场景

    七、总结


    一、概述

    话不多说,先来问几个问题:

    1. 什么是聚集索引?什么是非聚集索引?
    2. 聚集索引和非聚集索引有什么区别?
    3. 为什么 InnoDB 只在主键索引树的叶子节点存储具体数据,但是其他索引树却不存具体数据,而是存储主键值?
    4. 为什么索引会提高检索速度?
    5. 为什么针对区分度低的字段,例如性别,不建议建立索引?
    6. 为什么不建议使用mysql内置函数,而是要放在业务代码中去处理?
    7. 为什么明明建了索引,但是SQL执行还是很慢?
    8. 为什么MySQL建议用自增ID做主键索引,而不是UUID、雪花算法等?

    接下来,我们带着上述几个问题开始我们今天的主题 -- MySQL聚集索引和非聚集索引的介绍。

    二、聚集索引

    (一)、概念

    聚集(clustered)索引,也叫聚簇索引。它是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。因为一个表的物理顺序只有一种情况,所以每个表的聚集索引只能有一个。

    聚集索引类比我们的新华字典,按字母a-z排序,并且后面的所有字也是按照a-z的顺序排序,这就是聚集索引。聚集索引的叶子结点即存储了真实的数据行,所以通过聚集索引可以直接获取到数据库中的数据。

    (二)、示意图

    下图是聚集索引的存储示例图:

    从上图可以看出,聚集索引的叶子节点就是对应的数据节点,可以直接获取到对应的全部列的数据,而后面我们即将介绍的非聚集索引,查询的时候可能需要二次查询,因此在查询方面,聚集索引的速度往往会比非聚集索引快。

    (三)、如何生成聚集索引

    以MySQL为例,聚集索引通常是表的主键,这也是为什么InnoDB要求表必须有主键,InnoDB会按照如下规则进行处理: 

      1,如果一个主键被定义了,那么这个主键就是作为聚集索引 ;

      2,如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引; 

      3,如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,该列的值会随着数据的插入自增;

    (四)、注意点

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

    三、非聚集索引

    (一)、概念

    非聚集(unclustered)索引,即索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,所以一个表中可以拥有多个非聚集索引。

    与聚集索引不同的是,非聚集索引的叶子节点不存储具体的数据页, 叶子结点包含索引字段值及指向数据行的逻辑指针。所以非聚集索引不能直接获取到数据,需要通过逻辑指针进行二次查找来获取数据。

    (二)、示意图

    下面是非聚集索引的存储示意图:

    从上图可以看到,非聚集索引的叶子结点并没有存储真正的数据,它只是存储了指向具体数据化的地址,第一次查找只能获取到数据行的地址,还需要经过第二次去数据文件中查找对应的数据【也就是回表】。

    (三)、如何解决非聚集索引的二次查询问题

    我们其实可以使用覆盖索引来解决非聚集索引的二次查询问题。建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如表中建立了联合索引index(col1, col2),如下SQL:

    select col1, col2 from t1 where col1 = '213';

     就可以避免二次查询,因为从索引列就已经可以查询到对应的数据返回了。

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

    四、聚集索引和非聚集索引在MySQL的实现

    我们来看一下聚集索引和非聚集索引在MySQL两种储存引擎的底层实现。

    (一)、Innodb 引擎

    InnoDB 是聚集索引方式,数据和索引都存储在同一个文件里。

     【主索引搜索过程】

    首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树,如上图所示,而 B+树的叶子节点存储的是主键 ID 对应的数据,比如在执行 select * from user_info where id=15 这个语句时,InnoDB 就会查询这颗主键 ID 索引 B+树,找到对应的 user_name=‘Bob’。

    【辅助索引搜索过程】

    比如我们要给 user_name 这个字段加索引,那么 InnoDB 就会建立 user_name 索引 B+树,节点里存的是 user_name 这个 KEY,叶子节点存储的数据的是主键 KEY。

    注意,叶子存储的是主键 KEY,拿到主键 KEY 后,InnoDB 才会去主键索引树里根据刚在user_name 索引树找到的主键 KEY 查找到对应的数据。【两次查询,回表操作】

    为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据,但是其他索引树却不存具体数据呢,而要先找到主键,再在主键索引树找到对应的数据呢?

    因为 InnoDB 需要节省存储空间,一个表里可能有很多个索引,InnoDB 都会给每个加了索引的字段生成索引树,如果每个字段的索引树都存储了具体数据,那么这个表的索引数据文件就变得非常巨大(数据极度冗余)。

    (二)、MyISAM 引擎

    MyISAM 用的是非聚集索引方式,数据和索引落在不同的两个文件上。

     

    如上图,MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树,树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后,就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

    当我们为某个字段添加索引时,我们同样会生成对应字段的索引树,该字段的索引树的叶子节点同样是记录了对应数据的物理地址,然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

    五、聚集索引与非聚集索引的区别

    1. 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个;
    2. 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续;
    3. 通过聚集索引一次就可以查到需要查找的数据, 而通过非聚集索引第一次只能查到记录对应的主键值 ,需要回表再使用主键的值通过聚集索引查找到需要的数据;因此与非聚集索引相比,聚集索引有着更快的检索速度;

    六、适用场景

    聚集索引适用情况:

    • 1、含有大量非重复值的列;
    • 2、使用BETWEEN,>,>=,<或<=返回一个范围值的列;
    • 3、被连续访问的列;
    • 4、返回大型结果集的查询;
    • 5、经常被使用连接或GROUP BY子句的查询访问的列;

    下表总结了何时使用聚集索引或非聚集索引: 

    动作描述

    使用聚集索引

    使用非聚集索引

    列经常被分组排序

    返回某范围内的数据

    不应

    一个或极少不同值

    不应

    不应

    小数目的不同值

    不应

    大数目的不同值

    不应

    频繁更新的列

    不应

    外键列

    主键列

    频繁修改索引列

    不应

    七、总结

    本篇文章主要总结了MySQL中聚集索引和非聚集索引,从概念、数据存储、数据查询过程、两者区别、适用场景等方面,总结了两者的不同以及在不同的存储引擎中的实现。众所周知,索引有助于提高检索性能,但过多或不当的索引也会导致系统低效或者索引碎片,因为索引也会占用磁盘空间。因此我们要记住不是所有的表都适合建立索引,只有数据量大表才适合建立索引,且建立在选择性高的列上面性能才会提高。由于笔者水平有限,文中如有不对之处,希望指正,希望这篇文章对大家有所帮助。

    参考资料:

    学习资料分享

    • MySQL高性能书籍_第3版(中文)
    • MySQL技术内幕-InnoDB存储引擎_第2版
    展开全文
  • 一.索引简介众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储...SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。MySQL 索引类型有:唯一索引,主键(聚集)索引,非聚集索引...

    一.索引简介

    众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引。

    SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。

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

    二.聚集索引

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

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

    单单从定义来看是不是显得有点抽象,打个比方,一个表就像是我们以前用的新华字典,聚集索引就像是拼音目录,而每个字存放的页码就是我们的数据物理地址,我们如果要查询一个“哇”字,我们只需要查询“哇”字对应在新华字典拼音目录对应的页码,就可以查询到对应的“哇”字所在的位置,而拼音目录对应的A-Z的字顺序,和新华字典实际存储的字的顺序A-Z也是一样的,如果我们中文新出了一个字,拼音开头第一个是B,那么他插入的时候也要按照拼音目录顺序插入到A字的后面,现在用一个简单的示意图来大概说明一下在数据库中的样子:

    地址

    id

    username

    score

    0x01

    1

    小明

    90

    0x02

    2

    小红

    80

    0x03

    3

    小华

    92

    ..

    ..

    ..

    ..

    0xff

    256

    小英

    70

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

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

    519d8429ba1909e63c701ab1defcb08f.png

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

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

    创建聚集索引

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

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

    create table t1(

    id int primary key,

    name nvarchar(255)

    )

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

    SQL Server

    create clustered index clustered_index on table_name(colum_name)

    MySQL

    alter table table_name add primary key(colum_name)

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

    三.非聚集索引

    非聚集(unclustered)索引。

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

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

    642da34b3bbc25e0b1ae574e49086658.png

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

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

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

    如有以下表t1:

    id

    username

    score

    1

    小明

    90

    2

    小红

    80

    3

    小华

    92

    ..

    ..

    ..

    256

    小英

    70

    以及聚集索引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%。

    4f27fddf616ac016ac803425707f378e.png

    在SQL Server里面会对查询自动优化,选择适合的索引,因此如果在数据量不大的情况下,SQL Server很有可能不会使用非聚集索引进行查询,而是使用聚集索引进行查询,即便需要扫描整个聚集索引,效率也比使用非聚集索引效率要高。

    4266fcfb83dbf57fd8c96fc09b432a74.png

    本人试过在含有30w行表上建立非聚集索引,查询非聚集索引覆盖以外的列就会变成聚集索引的全索引扫描(index scan)查询来避免二次查询,而在另外一张200w行表才会用到非聚集索引seek对应的列再进行kek lookup,有关于SQL Server的有Index seek,index scan, table scan,key LookUp这几个概念,可以查看这个blog,描写比较详细。

    但在MySQL里面就算表里数据量少且查询了非键列,也不会使用聚集索引去全索引扫描,但如果强制使用聚集索引去查询,性能反而比非聚集索引查询要差,这就是两种SQL的不同之处。

    还有一点要注意的是非聚集索引其实叶子节点除了会存储索引覆盖列的数据,也会存放聚集索引所覆盖的列数据。

    如何解决非聚集索引的二次查询问题

    复合索引(覆盖索引)

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

    select col1, col2 from t1 where col1 = '213';

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

    在SQL Server中还有include的用法,可以把非聚集索引里包含的列包含进来,而不一定需要建立复合索引。

    四.总结与使用心得

    使用聚集索引的查询效率要比非聚集索引的效率要高,但是如果需要频繁去改变聚集索引的值,写入性能并不高,因为需要移动对应数据的物理位置。

    非聚集索引在查询的时候可以的话就避免二次查询,这样性能会大幅提升。

    不是所有的表都适合建立索引,只有数据量大表才适合建立索引,且建立在选择性高的列上面性能会更好。

    另附本人博客地址

    展开全文
  • 聚集索引(Non-clustered Index),非聚集索引并不决定数据在磁盘上的物理排序,索引上只包含被建立索引的数据,以及一个行定位符 row-locator,这个行定位符,可以理解为一个聚集索引物理排序的指针,通过这个...
  • 按照物理实现划分2.1 聚集索引2.2 非聚集索引3. 小结 之前松哥在前面的文章中介绍 MySQL 的索引时,有小伙伴表示被概念搞晕了,主键索引、非主键索引、聚簇索引、非聚簇索引、二级索引、辅助索引等等,今天咱们就来...
  • SQL:聚集索引和非聚集索引

    千次阅读 2021-02-08 03:33:45
    聚集(clustered)索引,也叫聚簇索引定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用...
  • 但是你回答的是具体的索引,实际上索引大体可以分成二大类:聚集索引和非聚集索引。下面我们具体介绍。 聚集索引 (又叫聚簇索引) 聚集索引就好比只有正文(没有目录)的汉语字典一样。我们知道汉语字典的排列顺序是...
  • 主键、聚集索引、非聚集索引区别

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

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 92,042
精华内容 36,816
关键字:

聚集索引

友情链接: 雷达.rar