精华内容
下载资源
问答
  • 一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理...由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
  • 文章目录1.提问:InnoDB和MyISAM引擎是针对表还是数据库?2.如何存储?2.1 我们看/usr/local/...聚集索引和非聚集索引3.1 区别3.2 用两张图对比一下4.哪个更快?5.总结 1.提问:InnoDB和MyISAM引擎是针对表还是数据库?

    1.提问:InnoDB和MyISAM引擎是针对表还是数据库?

    答:表

    选中一张表右击-》设计表-》选项,即可看到设置引擎的地方

    2.如何存储?

    InnoDb数据引擎用的是聚集索引。
    MyISAM数据引擎用的是非聚集索引。
    那这两种索引结构又是如何存储的呢?

    我用的是自己本地的数据库。
    找到数据库存储的地址。Windows找MySQL安装目录下的data目录 \MySQL Server\data ,我用的是Mac,地址/usr/local/var/mysql。
    或者你也可以用这个命令自己找一下。

    ps -ef|grep mysql
    

    2.1 我们看/usr/local/var/mysql目录下都有什么:

    在这里插入图片描述

    再看下我的Navicat连接,发现了吗!在/usr/local/var/mysql存储的就是自己的数据库

    2.2 存储引擎设置

    a.我将test库的article表的存储引擎设置为MyISAM

    b. 再将test库的book表的存储引擎设置为InnoDb

    c./usr/local/var/mysql/test目录下的这两个文件

    在这里插入图片描述
    .frm文件 存储数据表结构相关的信息
    .MYD文件 即myIsam data 存储表的记录
    .MYI文件 即myisam index 存储索引
    .idb文件 存储的是innoDb引擎下,所有表的记录和索引。

    在上一篇文章MYSQL-为什么索引要使用B+TREE?中,有同学留言问道:索引存放在哪里,就放在这里,你的磁盘上,明白了吧~~~

    3.聚集索引和非聚集索引

    3.1 区别

    聚集索引:数据和索引都在ibd文件中,叶节点包含了完整的数据记录,叶子节点用指针连接,一个表只有一个聚集索引。
    非聚集索引:数据在MYD,索引在MYI文件,索引与数据分开存储

    3.2 用两张图对比一下

    这叫聚集索引,它存在于idb文件中
    在这里插入图片描述

    这叫非聚集索引,索引文件和数据文件是分离的。索引存在于MYI文件中,数据存在于MYD文件中。
    在这里插入图片描述

    4.哪个更快?

    那么,单从索引角度上讲,聚集索引和非聚集索引哪个查询更快呢?
    当然是聚集索引,因为非聚集索引需要跨文件去查询啦!

    5.总结

    好记性不如烂笔头,知道不如做到。

    展开全文
  • 一、聚集索引(聚簇索引)1. 什么是聚集索引?比如要查找'hello',则直接找内容为hello的行,我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。聚集索引的叶子节点就是数据节点,key为主键的值...

    一、聚集索引(聚簇索引)

    1. 什么是聚集索引?

    比如要查找'hello',则直接找内容为hello的行,我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    聚集索引的叶子节点就是数据节点,key为主键的值,value为其余列数据以及rowid、rollback pointer、trx id等信息。

    聚集索引的条件:

    a.首先选择显示定义的主键为聚集索引;

    b.如果没有则选择第一个非NULL的唯一索引;

    c.以上都不满足就选择ROWID。

    聚集索引表现:

    a.索引的键值顺序决定了表中相应行的物理顺序,即表中行的存储顺序由聚集索引的键值顺序决定;

    b.一个表只能有一个聚集索引;

    c.索引列可能是多个(复合索引)。

    2. 适用场景(只针对innodb存储引擎,myisam不存在这说法)

    a.主键列,该列在where子句中使用并且插入是随机的。

    b.按范围存取的列,如pri_order > 100 and pri_order < 200。

    c.在group by或order by中使用的列。

    d.不经常修改的列,不能建立在自增列上。

    e.在连接操作中使用的列。

    二、非聚集索引

    1.比如要寻找一个繁体字,得先找到它对应的前一个和后一个字的页码,通过页码来找到对应内容,把这种通过页码来寻找的方式称为非聚集索引。

    非聚集索引的叶子节点为索引节点,但是有一个指针指向数据节点。

    非聚集索引就是普通索引,仅仅是对表创建索引不会影响表的物理存储顺序,非聚集索引的写入顺序由时间顺序决定。

    2.对更新频繁的表来说,表上的非聚簇索引比聚集索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言,指向该数据的每个非聚簇索引的页级行也必须更新,有时可能还需要索引页的分理。

    3.适用场景

    a.常用于计算函数如sum/count的列;

    b.常用于join/order by/group by的列;

    c.查寻出的数据不超过表中数据量的20%。

    三、总结

    bf7907aad45e61fa2b448a80ec0fad99.png

    四、索引优化

    1、缺省情况下建立的索引是非聚簇索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说:

    a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和order by、group by发生的列,可考

    虑建立聚集索引;

    b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;

    c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。

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

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

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

    5、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。

    展开全文
  • MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类,理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式,第一类是拼音检索(前提是知道该汉字读音),比如拼音为cheng的...

    MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类,理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式,第一类是拼音检索(前提是知道该汉字读音),比如拼音为cheng的汉字排在拼音chang的汉字后面,根据拼音找到对应汉字的页码(因为按拼音排序,二分查找很快就能定位),这就是我们通常所说的字典序;第二类是部首笔画检索,根据笔画找到对应汉字,查到汉字对应的页码。拼音检索就是聚集索引,因为存储的记录(数据库中是行数据、字典中是汉字的详情记录)是按照该索引排序的;笔画索引,虽然笔画相同的字在笔画索引中相邻,但是实际存储页码却不相邻。

    正文内容按照一个特定维度排序存储,这个特定的维度就是聚集索引;

    Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的,Innodb的表也称为索引表;因为行记录只能按照一个维度进行排序,所以一张表只能有一个聚集索引。

    非聚集索引索引项顺序存储,但索引项对应的内容却是随机存储的;

    举个例子说明下:

    create table student (

    `id` INT  UNSIGNED AUTO_INCREMENT,

    `name`  VARCHAR(255),

    PRIMARY KEY(`id`),

    KEY(`name`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    该表中主键id是该表的聚集索引、name为非聚集索引;表中的每行数据都是按照聚集索引id排序存储的;比如要查找name='Arla'和name='Arle'的两个同学,他们在name索引表中位置可能是相邻的,但是实际存储位置可能差的很远。name索引表节点按照name排序,检索的是每一行数据的主键。聚集索引表按照主键id排序,检索的是每一行数据的真实内容。

    也就是说查询name='Arle'的记录时,首相通过name索引表查找到Arle的主键id(可能有多个主键id,因为有重名的同学),再根据主键id的聚集索引找到相应的行记录;

    Mysql聚集索引和非聚集索引最简单的表述:

    聚集索引一般是表中的主键索引,如果表中没有显示指定主键,则会选择表中的第一个不允许为NULL的唯一索引,如果还是没有的话,就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

    每张表只有一个聚集索引,因为聚集索引在精确查找和范围查找方面良好的性能表现(相比于普通索引和全表扫描),聚集索引就显得弥足珍贵,聚集索引选择还是要慎重的(一般不会让没有语义的自增id充当聚集索引)。

    从宏观上分析下聚集索引和普通索引的性能差异,还是针对上述student表:

    (1)select * from student where id >5000 and id <20000;

    (2)select * from student where name > 'Alie' and name < 'John';

    第一条SQL语句根据id进行范围查询,因为(5000, 20000)范围内的记录在磁盘上按顺序存储,通过顺序读取磁盘很快就能读到这批数据。

    第二条SQL语句查询('Alie', 'John')范围内的记录,主键id分布可能是离散的1,100,20001,5000.....;增加了随机读取数据页几率;所以普通索引的范围查询效率被聚集索引甩开几条街都不止;非聚集索引的精确查询效率还是可以的,比聚集索引查询只增加了一次IO开销。

    展开全文
  • 索引这个词在数据库知识中耳熟能详,是一种以空间换取时间的典型,索引结构往往能给数据操作带来十分显著的性能提升,我们都知道大多数的数据库默认的索引结构都是B+树,而聚集索引和非聚集索引(也叫辅助索引)都属于...

    索引这个词在数据库知识中耳熟能详,是一种以空间换取时间的典型,索引结构往往能给数据操作带来十分显著的性能提升,我们都知道大多数的数据库默认的索引结构都是B+树,而聚集索引和非聚集索引(也叫辅助索引)都属于B+树结构;

    所以首先我们简单了解下B+树:

    B+树:是一种高度平衡的为磁盘数据存取而设计的查找树结构,所有的数据节点从左到右都是按顺序存放在同一层的叶子节点上,这就为数据的顺序查找或者范围查找提供了极大的便利,查询效率使用大O表示法为O(logN);

    mysql使用B+树存取数据,相对于所有数据的顺序查找来说,磁盘IO的速度相当低下,而B+树的查找往往只需要几次的磁盘IO,效率是十分惊人的;

    回到问题本身,聚集索引和非聚集索引的区别是什么?最根本的区别在于索引的顺序和表数据的顺序是否一致。。

    ①聚集索引:使用聚集索引的表,记录和索引保持着一致的顺序,这样只要找到索引的值就能直接从叶子节点里面获取到全部列数据;

    de1e5c2983cf101252c50234900de272.png

    ②非聚集索引:记录和索引的顺序往往不同,可理解为索引下面的叶子节点存储的还是索引,想要获得真正的列数据,还需要再一次查询;

    4cbd42f4fad3f1ab28bbc3fd9a2cc171.png

    根据上面的描述,聚集索引常常用在重复值不多(数据库通常会自动加内置的rowID保证列成为唯一索引),长度不大的列(比如主键等),而且聚集索引十分适合于顺序查询(order by)和范围查询(between,>,

    而非聚集索引更常被用于频繁更新的列,列长度比较大的场景;

    有人做过形象的比喻,聚集索引就像是使用拼音查字典(比如b就是100-150页),而非聚集索引是使用部首查字典(比如弓在100页,张在200页)!

    场景适用性截图如下:

    1adf51ae9a941942b037e81d4cf3d56a.png

    在下列场景中,不应该使用索引:

    1,表数据不多:表数据不多的时候,磁盘IO本来就少,加索引只会增加IO开销;

    2,不常使用的列:没必要;

    3,频繁更新的列:插入和删除时需要额外维护一份索引数据,会影响总的效率;

    展开全文
  • 众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两...
  • ALTER TABLE t1 ADD INDEX(or CREATE INDEX)ALTER TABLE t1 ADD FULLTEXT INDEXALTER TABLE t1 ADD COLUMN, ALGORITHM...中间表索引(主要和次要)使用“排序索引构建”构建。算法在 0 级别创建页,还要为此页创建一个...
  • 一、索引的简介 索引是存储引擎快速找到记录的一种数据结构,是数据库中专门用于帮助用户快速查询数据的一种数据结构,可以帮助用户快速寻找到需要的数据行,是数据库性能优化中最重要的工具。 使用索引的主要目的...
  • mysql 聚集索引 非聚集索引

    千次阅读 2019-03-06 18:43:56
    聚集索引 索引是存储了表数据的物理地址 聚集索引既存储了表数据key又存储了行值,物理地址的逻辑顺序和表存储的顺序一致!是唯一的 对于Innodb,主键毫无疑问是一个聚集索引。但是当一个表没有主键,或者没有一...
  • 目录 ...它会根据聚集索引键的顺序来存储表中的数据,即对表的数据按索引键的顺序进行排序,然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式,所以一个表只能有一个聚集索引。...
  • 查看MySQL存储引擎 show variables like '%storage_engine%'; 1、聚集索引 聚集索引特征 索引的数据的物理存放顺序和索引的顺序一致(数据的存放顺序根据索引顺序存放) ... 聚集索引插入数据时速度要慢(时间花...
  • MySQL系列—聚集索引和非聚集索引

    千次阅读 2018-09-12 15:09:24
    非聚集索引非聚集索引表示数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置,非聚集索引检索效率比聚集索引低,但对数据更新影响较小。 聚集索引  该索引中键值的逻辑顺序决定了表...
  • 为什么不建议使用mysql内置函数,而是要放在业务代码中去处理? 为什么明明建了索引,但是SQL执行还是很慢? 为什么有时候测试环境执行SQL正常能用到索引生产环境却不行? 为什么建议用自增ID做主键索引,而不是...
  • 索引: 在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针...1、聚集索引: ...
  • MySQL索引中可以分为聚集索引与非聚集索引两类,在网络上也见过聚簇的说法,这里先简单介绍两种索引的含义与适用场景。懒得画图,全是字。索引的键值逻辑顺序决定了表数据行的物理存储顺序,也就是在数据库上连接的...
  • 关于索引的介绍,以及b+树结构图,两种索引性能比较,索引优化建议
  • 目录 一、MySQL存储引擎 1.1 Innodb引擎 1.2 MyISAM引擎 1.3 InNoDB与MyISAM异同 1.4 两种引擎的选择 ...2.2 MyISAM索引实现(非聚集索引) ...2.4 用一组实例来比较聚集索引和非聚集索引的根本区别 2.4.1...
  • mysql,聚集索引通常就是主键索引,非聚集索引通常就是普通索引,它们有什么区别? 测试表 先建一张表,有id、name和其他列。id建立主键索引,此时就是聚集索引。name建立普通索引,此时就是非聚集索引。 ...
  • MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据...
  • 一、索引介绍 1.什么是索引? 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少...索引MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。可以理解成为排好序的快速查找数...
  • 聚集(clustered)索引,也叫聚簇索引定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用...
  • 一.索引简介众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索...MySQL 索引类型有:唯一索引,主键(聚集)索引,非聚集索引...
  • 聚集索引,非聚集索引,覆盖索引 原理

    万次阅读 多人点赞 2018-08-28 11:37:34
    「数据库」和「数据库索引」这两个东西是在服务器端开发领域应用最为广泛的两个概念,熟练使用数据库和数据库索引是开发人员在行业内生存的必备技能 使用索引很简单,只要能写创建表的语句,就肯定能写创建索引的...
  • 概述:汉语字典的正文本身就是一个聚集索引。比如要查z开头的,我们会自然翻到字典的后半部分,而不是从前往后一页一页翻,避免了全表扫描。而当我们翻到那一页,就能得到内容,而不需要再去查其他目录来找到需要找...
  • 非聚集索引:不是索引类型,也是一种数据存储方式,也称非聚簇索引; 辅助索引:工作中创建及使用的索引,也称二级索引; 存储文件InnoDB:frm是表定义文件,ibd是数据索引文件;MyISAM:frm是表定义文件,myd是...
  • 今天给大家介绍mysql InnoDB 下3种常用的索引:主键索引(聚集索引)、非聚集索引、覆盖索引 下面已一张表具体的表为例:来演示数据查找的过程。 譬如:一张用户表 User表 Id是主键 主键索引,也被称为”聚集...
  •  myisam使用的堆组织表(Heap Organize Table, HOT),没有聚集索引的概念,使用B-tree索引的存储格式,显示都是随机顺序。 innodb表是索引组织表(Index Organized Table, IOT),它的索引则是采用 clustered index ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 29,584
精华内容 11,833
关键字:

mysql非聚集索引

mysql 订阅
友情链接: Lab3-5-1 SD_Card.zip