精华内容
下载资源
问答
  • 如果将id和name设置为索引,id是主键索引,name为普通索引,也叫做辅助索引。他们之间有什么区别呢? InnoDB 在InnoDB存储引擎的B+树的数据结构下,主键索引下跟着的是数据信息,而辅助索引下跟着的是主键的id。 ...

    如上图User表,id为主键,name为普通字段。

    如果将id和name设置为索引,id是主键索引,name为普通索引,也叫做辅助索引。他们之间有什么区别呢?

    InnoDB 存储引擎情况下

    在InnoDB存储引擎的B+树的数据结构下,主键索引下跟着的是数据信息,而辅助索引下跟着的是主键的id。

    如图所示:

    使用主键索引查询的时候,可直接取到数据。

    使用主键索引查询的时候,要先得到主键索引值,再根据索引值去匹配数据。

    MYISAM 存储引擎情况下

    MYISAM 的索引是非聚集索引,索引信息和数据信息在不同的文件下,无论是主键索引还是辅助索引下跟着的都是数据的指引地址,根据数据的指引地址去数据信息文件搜索所需数据。

     

    展开全文
  • 首先,主索引就是主键索引辅助索引就是别的索引 MyISAM索引实现: 概述: ​ MyISAM索引文件和数据文件是分离的,MyISAM的索引文件仅仅保存数据记录的地址。 在MyISAM中,主索引和辅助索引在结构上没有任何区别,...

    首先,主索引就是主键索引,辅助索引就是别的索引

    MyISAM索引实现:

    ​ MyISAM索引文件和数据文件是分离的,MyISAM的索引文件仅仅保存数据记录的地址。
    在MyISAM中,主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。

    1)主键索引:
    MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
    下图是MyISAM主键索引的原理图:
    在这里插入图片描述
    2)辅助索引(Secondary key)
    在MyISAM中,主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。 如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:
    在这里插入图片描述
    同样也是一颗B+Tree,data域保存数据记录的地址。
    因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
    MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

    InnoDB索引实现

    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
    第一个重大区别是InnoDB的数据文件本身就是索引文件。
    (主索引和辅助索引放在一个文件里吗?如果不在,那这句话应该说InnoDB的数据文件本身就是索引文件。)
    InnoDB的主索引data域存储的是数据记录。
    InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

    1)主键索引:
    InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
    在这里插入图片描述
    以上是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
    2)InnoDB的辅助索引
    ​ InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:
    在这里插入图片描述
    InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引(Secondary Index, 也就是非主键索引)也会包含主键列, 所以,如果主键定义的比较大,其他索引也将很大。如果想在表上定义很多索引,则争取尽量把主键定义得小一些。 InnoDB 不会压缩索引。

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

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

    InnoDB索引和MyISAM索引的区别:

    一是主索引的区别,InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

    二是辅助索引的区别:InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

    看了上面的你得知道
    数据文件和索引文件怎么个在一起法?索引文件中叶节点data域存储的是完整的数据记录

    展开全文
  • MySQL索引之主键索引

    2020-09-10 05:33:39
    在MySQL里,主键索引辅助索引分别是什么意思,有什么区别?上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引辅助索引的区别
  • 作者 | 吴海存责编| 徐威龙封图|CSDN下载于视觉中国导读:本文主要针对limit分页时,是优先基于主键索引还是辅助索引等层面展开分析,对limit及offset的用法以及是否...

    作者 | 吴海存

    责编 | 徐威龙

    封图| CSDN下载于视觉中国

    导读:

    本文主要针对limit分页时,是优先基于主键索引还是辅助索引等层面展开分析,对limit及offset的用法以及是否该用索引不会过多赘述。

    我们知道,在Mysql中可以通过limit实现快速分页,但是如果表中数据量较大,则分页后期可能会十分缓慢,这是由limit的工作机制决定的,比如limit 500000,5的意思扫描满足条件的500005行,扔掉前面的500000行,返回最后的5行,所以我们在分页的时候,需要通过相应的索引来快速定位到第500000行,然后将后面的5行数据输出即可。

    那我们在选则索引的时候,是使用主键索引还是使用辅助索引呢?若使用辅助索引,那对该辅助索引有没有什么限制呢?比如该索引所基于的列是不是应该有not null约束呢?因为我们知道,在分页的时候,要求数据是连续的,而索引里面是不记录null值的,所以若索引没有not null约束,则有可能不满足分页条件。

    对于以上几个问题,我们通过如下的示例来一一验证说明,如有疏漏之处,望指正。


    实验环境

    MySQL版本:8.0.18

    OS版本:CentOS 8.0

    【实验步骤】

    1.确认测试表emp中的数据量(若数据量太少,则对比效果不明显)

    2.确认表上索引和not null约束信息

    说明: id 上索引为主键索引

         empno上的索引ind2为唯一性非空索引

         ename 上的索引ind3为非空的普通索引

         salary  上的索引为可以为空的普通索引

    3.收集最新的统计信息

    为了测试的公平性,我们执行一次全表查询,模拟将表emp的page尽量多地缓存到buffer pool,否则可能会出现后执行的sql直接逻辑读取了先执行的sql通过物理IO读到buffer pool的page,会使得对比结果缺乏不可靠性。

    为了体现测试效果,我们选择扫描700W+5行,然后舍弃前面的700W行,返回后面满足条件的5行。

    4.1在分页的时候通过ID列选择使用主键索引

    select * from emp where id >= (select id from emp order by id limit 7000000,1) limit 5;

    相应的执行计划使用了主键索引PRIMARY:

    可以看到,使用ID主键索引的时候,用时为2.87秒。 

    4.2使用非空唯一性索引

    select * from emp where empno >= (select empno from emp order by empno limit 7000000,1) limit 5; 

    相应执行计划使用了非空唯一性索引ind2:

    当使用非主键以外的非空唯一性索引时,用时0.85秒。 

    4.3使用非空非唯一的索引 

    select * from emp where ename >= (select ename from emp order by ename limit 7000000,1) limit 5;

    相应执行计划使用了非空非唯一性索引ind3:当使用非空非唯一性索引时,用时1.95秒。

    4.4使用可为null的普通索引

    先随机设置20行salary为null

    查5条数据验证一下: 

    更新统计信息:

    使用可为null的普通索引进行分页:

    执行计划使用了可为null非唯一性索引:

    当使用可为null非唯一性索引时,用时1.18秒,使用的时间比非唯一性索引ind3时间较短的原因,是因为ind4的key_len是5,而ind3的key_len是82,ind3需要读取更多的page。

    4.5验证使用可为null的普通索引是否会丢失数据

    这里有一个问题,就是ind4里会有null值,使用该列进行分页的话,是不是由可能会丢失数据呢?使用如下步骤进行验证:

    增到表中salary为null的行数到100

    对salary进行排序,然后选择扫描7999910行,然后舍弃前面的7999890行,返回后面满足条件的20行,看数据库返回得行数:

    可以发现,当使用可为null的非唯一性索引时,会将null值当作最小值参加排序,不会丢失数据。 

    通过实验验证,我们可以得出如下结论:

    1.当使用非主键的唯一性非空索引时,用作分页效率最高。

    2.若使用primary key, 因为innodb时IOT表,所有全索引访问等同于全表扫描,效率低。

    3.若同为普通索引,则和该索引的key长度,选择率等有关系,若该索引的选择率较高,则效率会高于primary key,原因同第二条。

    4.使用可为null的非唯一性索引时,会将null值当作最小值参加排序,不会丢失数据。

    对于本文,你有什么想法?欢迎在评论区和我讨论。

    作者介绍:

    吴海存,10g/11g/12c OCM, Oracle Exadata/Golden Gate 专家, 曾于Amazon和Oracle公司担任全球业务资深DBA,目前供职于中国农业银行,担任资深数据库专家。

    同时,欢迎所有开发者扫描下方二维码填写《开发者与AI大调研》,只需2分钟,便可收获价值299元的「AI开发者万人大会」在线直播门票!

    推荐阅读:如何成功构建大规模 Web 搜索引擎架构?
    “出道” 5 年采用率达 78%,Kubernetes 的成功秘诀是什么?
    一群阿里人如何用 10 年自研洛神云网络平台?技术架构演进全揭秘!拿下 Gartner 容器产品第一,阿里云打赢云原生关键一战!
    大话卷积神经网络CNN,小白也能看懂的深度学习算法教程,全程干货建议收藏!
    朱广权李佳琦直播掉线,1.2 亿人在线等
    “抗疫”新战术:世卫组织联合IBM、甲骨文、微软构建了一个开放数据的区块链项目!
    真香,朕在看了!
    
    展开全文
  • B+树索引并不能找到一个给定键的具体行。B+树索引能找到的只是被查找数据行所在的页。 然后数据库通过把页读入内存,再在内存中进行查找,最后得到要查找的数据。 B+树(平衡多路查找树): B+树是为了磁盘或...

    B+树索引并不能找到一个给定键的具体行。B+树索引能找到的只是被查找数据行所在的页。

    然后数据库通过把页读入内存,再在内存中进行查找,最后得到要查找的数据。

     

    B+树(平衡多路查找树):

    B+树是为了磁盘或其它直接存取设备设计的一种平衡多路查找树。在B+树里是,所以记录节点都是键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。

    B+树索引在数据库中有高扇出性的特点,因此在数据库中,B+树的高度一般在2~4层,也就是说查找某一键值的行记录时最多只需要2~4次IO.

    数据库中的B+树索引可以分为聚集索引和辅助索引,其内部都是B+树的,高度平衡,叶子节点存放着数据。

    聚集索引和辅助索引不同的是,叶子节点存放的是否是一整行的信息。

     

    聚集索引:

    InnoDB存储引擎表是索引组织表,即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树,同时叶子结点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。

    由于实际的数据页只能按照一棵B+树进行排序,因此每张表只能拥有一个聚集索引。

    聚集索引的存储并不是物理上连续的,而是逻辑上连续的。
    一:页通过双向链表链接,页按照主键的顺序排序;

    二:每个页中的记录也是通过双向链表进行维护的,物理存储上可以同样不按照主键存储。

     

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

     

    辅助索引:

    对于辅助索引,叶子结点并不包含行记录的全部数据。叶子结点除了包含键值以外,每个叶子节点中索引行中还包含了一个相应行数据的聚集索引键(InnoDB引擎下)。

    当辅助索引寻找数据时,InnoDB存储引擎会遍历辅助索引并通过页级别的指针获取只指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。

     

    ALTER TABLE tablename ADD INDEX indexname (index_col,....)

    ALTER TABLE tablename DROP INDEX indexname (index_col,....)

    CREATE INDEX indexname ON tablename 

    DROP INDEX indexname ON tablename 

     

    展开全文
  • 上面两个查询,前者是用到了主键索引,后两者用到了辅助索引,但是为什么用到主键索引会更慢呢?上述三个查询的type=index,这个方式跟全表扫描一样,只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免...
  • 导读在MySQL里,主键索引辅助索引分别是什么意思,有什么区别?上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引辅助索引的区别。1、主键索引主键索引,简称主键,原文是PRIMARY KEY...
  • 1.在表上定义主键PRIMARY KEY,InnoDB将主键索引用作聚簇索引; 2.如果表没有定义主键,InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引; 3.如果以上两个都没有,InnoDB 会使用一个6 字节长整型的隐式字段 ROWID...
  • 聚集索引: 按照每张表的主键构造B+树,中间节点用来存放索引,叶子节点(数据页)用来存放行的全部数据,按照主键的顺序排序,每个数据页都通过一个双向链表来连接,因此,聚集索引能够在B+树索引的叶子节点上直接...
  • 主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。 全文索引(FULLTEXT ):可用于 MyISAM 表,mysql5.6之后也可用于innodb表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时和...
  • 文章目录前言基于 B+Tree 的索引InnoDB 的主键索引MyISAM 的主键索引附一个小图InnoDB 和 MyISAM 二级索引的区别参考图InnoDB的主键索引和二级索引MyISAM的主键索引和二级索引 前言 体能状态先于精神状态,习惯先于...
  • 索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。 索引分为聚簇索引和非聚簇索引两种,聚簇索引 是按照数据存放的物理位置为顺序的,而非聚簇索引就不...
  • MYSQL的全表扫描,主键索引(聚集索引、第一索引),非主键索引(非聚集索引、第二索引),覆盖索引四种不同查询的分析   1.前置条件: 本次是基于小数据量,且数据块在一个页中的最理想情况进行分析,可能无具体的...
  • 唯一索引/非唯一索引主键索引(主索引)聚集索引/非聚集索引组合索引唯一索引/非唯一索引唯一索引1.唯一索引是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中不可以重复。非唯一索引2....
  • 让人非常遗憾的是,网络上至今仍然有非常多的文章是这样的描述:“一张表中必须有聚集索引,但不一定需要主键”。前半句是正确的,后半句是大错特错! 对于 InnoDB 存储引擎来说,表采用的存储方式称为索引组织表...
  • 1、主键索引和二级索引 2、InnoDB存储引擎 2.1 总结 3、MyISAM存储引擎 3.1 总结 4、不同存储引擎对聚集索引和非聚集索引的实现方式不同 5、二次查询问题 在最初学习MySQL底层原理的时候,对InnoDB和MyISAM...
  • 1、主键索引:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符 2、联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,...
  • MyISAM存储引擎 MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图: ...在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只...
  • MyISAM存储引擎 MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图: ...在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只...
  • MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图: ...在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是
  • 主键索引(主索引) 聚集索引/非聚集索引 组合索引 唯一索引/非唯一索引 唯一索引 1.唯一索引是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中不可以重复。 非唯一索引 2.非...
  • 测试表USER_INFO的数据和结构如下 EXPLAIN SELECT count(*) from USER_INFO的结果是 ...接下来给USER_INFO表加上一个辅助索引idx_user_name 再次运行EXPLAIN SELECT count(*) from USER_INFO的结果是.

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 21,664
精华内容 8,665
关键字:

主键索引辅助索引