精华内容
下载资源
问答
  • 1、聚集索引 表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外...从物理文件中也可以看出 MyISAM(聚集索引)的索引文件.MYI
  • 聚簇索引与非聚簇索引的区别

    千次阅读 2021-01-28 06:37:24
    通常情况下,建立索引是加快查询速度的有效手段。但索引不是万能的,靠索 引并不能实现对所有数据的快速存取。事实上,如果索引策略和...本文简要讨论一下聚簇索引的特点及其与非聚簇索引的区别。建立索引:在SQL语...

    通常情况下,建立索引是加快查询速度的有效手段。但索引不是万能的,靠索 引并不能实现对所有数据的快速存取。事实上,如果索引策略和数据检索需求严重不符的话,建立索引反而会降低查询性能。因此在实际使用当中,应该充分考虑到 索引的开销,包括磁盘空间的开销及处理开销(如资源竞争和加锁)。例如,如果数据频繁的更新或删加,就不宜建立索引。

    本文简要讨论一下聚簇索引的特点及其与非聚簇索引的区别。

    建立索引:

    在SQL语言中,建立聚簇索引使用CREATE INDEX语句,格式为:CREATE CLUSTER INDEX index_name ON table_name(column_name1,column_name2,...);

    存储特点:

    聚集索引。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。

    非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。

    总结一下:聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。

    更新表数据

    1、向表中插入新数据行

    如果一张表没有聚集索引,那么它被称为“堆集”(Heap)。这样的表中的数据行没有特定的顺序,所有的新行将被添加到表的末尾位置。而建立了聚簇索引的数据表则不同:最简单的情况下,插入操作根据索引找到对应的数据页,然后通过挪动已有的记录为新数据腾出空间,最后插入数据。如果数据页已满,则需要拆分数据页,调整索引指针(且如果表还有非聚集索引,还需要更新这些索引指向新的数据页)。而类似于自增列为聚集索引的,数据库系统可能并不拆分数据页,而只是简单的新添数据页。

    2、从表中删除数据行

    对删除数据行来说:删除行将导致其下方的数据行向上移动以填充删除记录造成的空白。如果删除的行是该数据页中的最后一行,那么该数据页将被回收,相应的索

    引页中的记录将被删除。对于数据的删除操作,可能导致索引页中仅有一条记录,这时,该记录可能会被移至邻近的索引页中,原索引页将被回收,即所谓的“索引

    合并”。

    聚簇索引确定表中数据的物理顺序。聚簇索引类似于电话簿,后者按姓氏排列数据。由于聚簇索引规定数据在表中的物理存

    储顺序,因此一个表只能包含一个聚簇索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用,

    在汉语字典里,索引项是字母+声调,字典正文也是按照先字母再声调的顺序排列。

    聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚簇(物理排序),避免每次查询该列时都进行排序,从而节省成本。

    建立聚簇索引的思想

    1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争,在一个高事务的环境中,对最后一页的封锁严重影响系统的吞吐量。

    2、在聚簇索引下,数据在物理上按顺序排在数据页上,重复值也排在一起,因而在那些包含范围检查 (between、、>=)或使用group by或orderby的查询时,一旦找到具有范围中第一个键值的行,具有后续索引值的行保证物理上毗连在一起而不必进一步搜索,避免了大范围扫描,可以大 大提高查询速度。

    3、在一个频繁发生插入操作的表上建立聚簇索引时,不要建在具有单调上升值的列(如IDENTITY)上,否则会经常引起封锁冲突。

    4、在聚簇索引中不要包含经常修改的列,因为码值修改后,数据行必须移动到新的位置。

    5、选择聚簇索引应基于where子句和连接操作的类型。

    不知从什么角度来对比,只能说说各自的特点,希望对你有用。

    1、聚簇索引

    a) 一个索引项直接对应实际数据记录的存储页,可谓“直达”

    b) 主键缺省使用它

    c) 索引项的排序和数据行的存储排序完全一致,利用这一点,想修改数据的存储顺序,可以通过改变主键的方法(撤销原有主键,另找也能满足主键要求的一个字段或一组字段,重建主键)

    d) 一个表只能有一个聚簇索引(理由:数据一旦存储,顺序只能有一种)

    2、非聚簇索引

    a) 不能“直达”,可能链式地访问多级页表后,才能定位到数据页

    b) 一个表可以有多个非聚簇索引

    第二种理解:

    聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。

    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:

    聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    因此,MYSQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。

    下面,我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构。

    如原始数据为:

    823bdffd1801154adcc6002cac920d73.png

    MyISAM引擎的数据存储方式如图:

    cdf92883521850ee95bbbcf467a9a601.png

    MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。

    从MYISAM存储的物理文件我们能看出,MYISAM引擎的索引文件(.MYI)和数据文件(.MYD)是相互独立的。

    而InnoDB按聚簇索引的形式存储数据,所以它的数据布局有着很大的不同。它存储数据的结构大致如下:

    e619998da5e6562ee22abef65ff93a05.png

    注:聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC)和余下的列(如col2)。

    INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值,而不是行指针(row pointers),这减小了移动数据或者数据页面分裂时维护二级索引的开销,因为InnoDB不需要更新索引的行指针。其结构大致如下:

    3659bf3b46effd5431bd0deeedcae9a9.png

    INNODB和MYISAM的主键索引与二级索引的对比:

    f6d4cc7b0616b534f1701ea8f2c06a9a.png

    InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此,通过二级索引查询首先查到是主键值,然后InnoDB再根据查到的主键值通过主键

    索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合,叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主

    键索引和二级索引没有任何区别,主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引,且MYISAM引擎中可以不设主键

    展开全文
  • 1.聚簇索引和非聚簇索引索引是用来提高数据库性能的,用于快速找出某个列中有一特定值的行,如果不使用索引,MySQL必须从第1条记录开始读完整个表直到找出相关的行,表越大,花费的时间越多,如果表中查询的列有一个...

    1.聚簇索引和非聚簇索引

    索引是用来提高数据库性能的,用于快速找出某个列中有一特定值的行,如果不使用索引,MySQL必须从第1条记录开始读完整个表直到找出相关的行,表越大,花费的时间越多,如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜索数据文件,没有必要看全部数据,这对性能将有极大的提升。

    再用一道数据题来理解:如果表中的一条记录在磁盘上占用 1000字节的话,我们对其中10字节的一个字段建立索引,那么该记录对应的索引块的大小只有10字节。我们知道,MySQL的最小空间分配单元是“页(Page)”,一个页在磁盘上默认占用16K空间,那么这一个页可以存储上述记录16条,但可以存储索引1600条。现在我们要从一个有16000条记录的表中检索符合某个条件的记录,如果没有索引的话,我们可能需要遍历16000条×1000字节/16K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话,那么我们可以在16000条×10字节/16K字节=10个页面中就检索到满足条件的索引块,然后根据索引块上的指针逐一找到结果数据块,这样IO访问量要少的多,可见索引对性能的提升作用。

    索引可分为聚簇索引和非聚簇索引。

    聚簇索引中键值的逻辑顺序决定了表数据行的物理顺序,只要索引是相邻的,对应的数据一定也是相邻地存放在磁盘上,因此每张表只能建立一个聚簇索引。比如英语课本中查找某个单词在第8页,翻到第6页再往后翻,翻到第10页再往前翻,这就类似于聚簇索引,表存储数据的顺序就是跟着聚簇索引来存储的。

    InnoDB表中聚簇索引首选主键,其次选择不含null值的唯一索引,如果两者都无,则会内置rowid作为隐含的聚集索引。

    官方文档关于这个知识点描述如下:

    • When you define a PRIMARY KEY on your table, InnoDB uses it as theclustered index.Define a primary key for each table that you create. If there is no logicalunique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically. 首选主键

    • If you do not define a PRIMARY KEY for your table, MySQL locates thefirst UNIQUE index where all the key columns are NOT NULL and InnoDB uses it asthe clustered index. 没有主键则选非空唯一索引

    • If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDBinternally generates a hidden clustered index on a synthetic column containingrow ID values. The rows are ordered by the ID that InnoDB assigns to the rowsin such a table. The row ID is a 6-byte field that increases monotonically asnew rows are inserted. Thus, the rows ordered by the row ID are physically ininsertion order.都没有的话就内置rowid生成隐含的聚集索引

    非聚簇索引中键值的逻辑顺序与数据行的物理存储顺序不同,假设要找某个单词,需要翻到英语书后面的单词目录,找到对应的字母位置,看其所在第几课,然后再找到对应课中的单词。这个附录就类似非聚簇索引。一个表可以有不止一个非聚簇索引,非聚簇索引需要大量的硬盘空间和内存,虽然非聚簇索引可以提高从表中读取数据的速度,但也会降低向表中插入和更新数据的速度,因为只要你更新一个建立了非聚簇索引的表中的数据时,必须要同时更新索引。

    一般缺省情况下建立的索引是非聚簇索引,但并一定是最佳的,因为很多情况下,聚簇索引比非聚簇索引查询效率高很多,聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有一个指针指向对应的数据块。而且如果需要频繁地更新表数据就不要建太多非聚簇索引,另外,如果硬盘和内存空间有限,也应该限制非聚簇索引的数量。总之,真正合理的索引设计要建立在对各种查询的分析和预测上。

    官方文档说明如下:Every InnoDB table has aspecial index called the clustered index where the data for the rows is stored.

    Typically, the clustered indexis synonymous with the primary key.

    To get the best performance from queries, inserts, and other databaseoperations, you must understand how InnoDB uses the clustered

    index to optimizethe most common lookup and DML operations for each table.

    2.如何查询表的索引基本情况?

    表的索引情况记录在information_schema库中的statistics表中,该表专提供表索引的信息,查询某库某表索引信息语句如下:SELECT * FROM INFORMATION_SCHEMA.STATISTICS

    WHERE table_name = 'tbl_name'

    AND table_schema = 'db_name'

    或者SHOW INDEX FROM tbl_name FROM db_name

    指定查询某库某表索引情况的语句:

    mysql> select * from statistics whereTABLE_SCHEMA='cloudXXX' and TABLE_NAME='opmXXXX'\G

    查询第一行数据进行逐个字段解析:

    mysql> select * fromstatistics limit 0,1\G

    *************************** 1. row ***************************

    TABLE_CATALOG: def 数据表登记目录

    TABLE_SCHEMA: monitor_XXXX 索引所属表的数据库名

    TABLE_NAME: alarm_XXXX 索引所属的数据表名

    NON_UNIQUE: 0 字段不唯一的标识,索引不能包括重复词则为0,否则为1

    INDEX_SCHEMA: monitor_XXXX 索引所属的数据库名

    INDEX_NAME: PRIMARY 索引名称,primary一般是聚簇索引

    SEQ_IN_INDEX: 1 索引中的序列号,从1开始

    COLUMN_NAME: id 索引列的列名

    COLLATION: A 校对,列值全显示为A

    CARDINALITY: 58 基数,同该表的数据行数

    SUB_PART: NULL 列只是部分编入索引,则显示编入索引的字符的数据,如整列被编入索引,则为NULL

    PACKED: NULL 关键字是否包装过,没有压缩包装则默认为NULL

    NULLABLE: 是否为空

    INDEX_TYPE: BTREE 索引的类型,列值全显示为BTREE,InnoDB的存储引擎默认创建的是BTREE(举例:BTREE,FULLTEXT, HASH, RTREE)

    COMMENT: 索引注释、备注

    INDEX_COMMENT:

    如果查询某库某表索引信息没有任何输出,则说明该表未建索引。

    3.如何查询表的索引类型情况?

    InnoDB表的索引类型情况在information_schema库中的INNODB_SYS_TABLES和INNODB_SYS_INDEXES里。

    先在INNODB_SYS_TABLES表里找到自己需要查询的表对应的TABLE_ID:mysql> select TABLE_ID,NAME from INNODB_SYS_TABLESwhere NAME like 'monitor %';

    输出举例:| 76 |monitor_XXXXXXXX/os_perf_biz_vpn |

    | 77 |monitor_ XXXXXXXX /os_perf_biz_vpn_day |

    | 78 |monitor_ XXXXXXXX /os_perf_biz_vpn_hour |

    | 79 |monitor_ XXXXXXXX /os_perf_biz_vpn_month |

    再用这个TABLE_ID去INNODB_SYS_INDEXES表里去查该表的索引类型:mysql> select * from INNODB_SYS_INDEXES whereTABLE_ID between '76' and '199';

    输出举例:+----------+-------------------+----------+------+----------+---------+-------+

    | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS |PAGE_NO | SPACE |

    +----------+-------------------+----------+------+----------+---------+-------+

    | 92 | GEN_CLUST_INDEX | 76 | 1 | 0 | 3 | 65 |

    | 351 | create_time_index | 76 | 0 | 1 | 4 | 65 |

    | 93 | GEN_CLUST_INDEX | 77 | 1| 0 | 3 | 66 |

    | 352 | create_time_index | 77 | 0| 1 | 4 | 66

    这个输出关键看TYPE字段,TYPE字段官网解析如下:A numeric identifiersignifying the kind of index.

    0 = Secondary Index,

    1 =Clustered Index,

    2 = Unique Index,

    3 = PrimaryIndex,

    32 = Full-text Index,

    64 = Spatial Index,

    128 = A secondary indexthat includes a generated virtual column

    TYPE为0便是辅助索引,也是非聚簇索引,1是聚簇索引,3是主键索引。上文输出可以看出这些表建的就是非聚簇索引,索引名字为create_time_index。

    除此之外,还有个GEN_CLUST_INDEX,其官网解析如下:

    If the name isGEN_CLUST_INDEX, the index is the clustered index thatis created automatically if the table definition doesn't include a primary keyor non-NULL unique index.

    即使一个既无主键也无索引的表,在statistics中没有任何记录,在这里也可以查询如下,这也应证了上文所述的InnoDB表隐含的聚集索引:mysql>select TABLE_ID,NAME from INNODB_SYS_TABLES where NAME='monitor_XXXXXX/os_XXXXXX';

    +----------+---------------------------------------------------+

    |TABLE_ID | NAME |

    +----------+---------------------------------------------------+

    | 203 | monitor_XXXXX/os_XXXXXXXXXXX |

    +----------+---------------------------------------------------+

    1row in set (0.00 sec)

    mysql>select * from INNODB_SYS_INDEXES where TABLE_ID='203';

    +----------+-----------------+----------+------+----------+---------+-------+

    |INDEX_ID | NAME | TABLE_ID | TYPE| N_FIELDS | PAGE_NO | SPACE |

    +----------+-----------------+----------+------+----------+---------+-------+

    | 218 | GEN_CLUST_INDEX| 203 | 1 | 0 | 3 | 192 |

    +----------+-----------------+----------+------+----------+---------+-------+

    1row in set (0.00 sec)

    以上就是MySQL的聚簇索引、非聚簇索引及其查询解析方法的内容,更多相关内容请关注PHP中文网(www.gxlcms.com)!

    本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

    本文系统来源:php中文网

    展开全文
  • 聚簇索引(Clustered Index)和非聚簇索引(Non- Clustered Index)最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一...

    索引的重要性

    数据库性能优化中索引绝对是一个重量级的因素,可以说,索引使用不当,其它优化措施将毫无意义。

    聚簇索引(Clustered Index)和非聚簇索引(Non- Clustered Index)

    最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一页就是P开头的部分,这就是物理存储顺序(聚簇索引);而不用你到目录,找到汉字“爬”所在的页码,然后根据页码找到这个字(非聚簇索引)。

    下表给出了何时使用聚簇索引与非聚簇索引:

    动作

    使用聚簇索引

    使用非聚簇索引

    列经常被分组排序

    返回某范围内的数据

    不应

    一个或极少不同值

    不应

    不应

    小数目的不同值

    不应

    大数目的不同值

    不应

    频繁更新的列

    不应

    外键列

    主键列

    频繁修改索引列

    不应

    聚簇索引的唯一性

    正式聚簇索引的顺序就是数据的物理存储顺序,所以一个表最多只能有一个聚簇索引,因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引,所以它显得更为珍贵,一个表设置什么为聚簇索引对性能很关键。

    初学者最大的误区:把主键自动设为聚簇索引

    因为这是SQLServer的默认主键行为,你设置了主键,它就把主键设为聚簇索引,而一个表最多只能有一个聚簇索引,所以很多人就把其他索引设置为非聚簇索引。这个是最大的误区。甚至有的主键又是无意义的自动增量字段,那样的话Clustered index对效率的帮助,完全被浪费了。

    刚才说到了,聚簇索引性能最好而且具有唯一性,所以非常珍贵,必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引,这个要看实际情况。

    事实上,建表的时候,先需要设置主键,然后添加我们想要的聚簇索引,最后设置主键,SQLServer就会自动把主键设置为非聚簇索引(会自动根据情况选择)。如果你已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。

    记住我们的最终目的就是在相同结果集情况下,尽可能减少逻辑IO。

    我们先从一个实际使用的简单例子开始。

    一个简单的表:

    CREATE TABLE [dbo].[Table1](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Da

    ta1] [int] NOT NULL DEFAULT ((0)),

    [Da

    ta2] [int] NOT NULL DEFAULT ((0)),

    [Da

    ta3] [int] NOT NULL DEFAULT ((0)),

    [Name1] [nvarchar](50) NOT NULL DEFAULT (''),

    [Name2] [nvarchar](50) NOT NULL DEFAULT (''),

    [Name3] [nvarchar](50) DEFAULT (''),

    [DTAt] [datetime] NOT NULL DEFAULT (getdate())

    复制代码

    2d76f38c1aa9e19575d5ebda0eb9fe5a.png

    来点测试数据(10w条):

    declare @i int

    set @i = 1

    while @i < 100000

    begin

    insert into Table1 ([Da

    ta1] ,[Da

    ta2] ,[Da

    ta3] ,[Name1],[Name2] ,[Name3])

    values(@i , 2* @i ,3*@i, CAST(@i AS NVARCHAR(50)), CAST(2*@i AS NVARCHAR(50)), CAST(3*@i AS NVARCHAR(50)))

    set @i = @i + 1

    end

    update table1 set dtat= DateAdd (s, da

    ta1, dtat)

    复制代码

    打开查询分析器的IO统计和时间统计:

    SET STATISTICS IO ON;

    SET STATISTICS TIME ON;

    复制代码

    显示实际的“执行计划”:

    c79979e4214556dc5ad39742cdd9962e.png

    我们最常用的SQL查询是这样的:

    SELECT * FROM Table1 WHERE Da

    ta1 = 2 ORDER BY DTAt DESC;

    复制代码

    先在Table1设主键ID,系统自动为该主键建立了聚簇索引。

    然后执行该语句,结果是:

    Table 'Table1'. Scan count 1, logical reads 911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 16 ms, elapsed time = 7 ms.

    复制代码

    ba919fa170335008ca61fee872628b3e.png

    然后我们在Data1和DTat字段分别建立非聚簇索引:

    CREATE NONCLUSTERED INDEX [N_Da

    ta1] ON [dbo].[Table1]

    (

    [Da

    ta1] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

    LINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [N_DTat] ON [dbo].[Table1]

    (

    [DTAt] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

    LINE = OFF) ON [PRIMARY]

    复制代码

    再次执行该语句,结果是:

    Table 'Table1'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 39 ms.

    复制代码

    5f90c276c2915dd937a8af49cf4904d7.png

    可以看到设立了索引反而没有任何性能的提升而且消耗的时间更多了,继续调整。

    然后我们删除所有非聚簇索引,并删除主键,这样所有索引都删除了。建立组合索引Data1和DTAt,最后加上主键:

    CREATE CLUSTERED INDEX [C_Da

    ta1_DTat] ON [dbo].[Table1]

    (

    [Da

    ta1] ASC,

    [DTAt] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ON

    LINE = OFF) ON [PRIMARY]

    复制代码

    再次执行语句:

    Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    复制代码

    97a99c7e51a8f0987b9931f3a2e7ca5c.png

    可以看到只有聚簇索引seek了,消除了index scan和nested loop,而且执行时间也只有1ms,达到了最初优化的目的。

    组合索引小结

    小结以上的调优实践,要注意聚簇索引的选择。首先我们要找到我们最多用到的SQL查询,像本例就是那句类似的组合条件查询的情况,这种情况最好使用组合聚簇索引,而且最多用到的字段要放在组合聚簇索引的前面,否则的话就索引就不会有好的效果,看下例:

    8d6164e83314361bd24268988d70e0b5.png

    查询条件落在组合索引的第二个字段上,引起了index scan,效果很不好,执行时间是:

    Table 'Table1'. Scan count 1, logical reads 238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 16 ms, elapsed time = 22 ms.

    复制代码

    而如果仅查询条件是第一个字段也没有问题,因为组合索引最左前缀原则,实践如下:

    f93ac0ecf0b15692bbdebd8d78d6ef4a.png

    Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    复制代码

    从中可以看出,最多用到的字段要放在组合聚簇索引的前面。

    Index seek 为什么比 Index scan好?

    索引扫描也就是遍历B树,而seek是B树查找直接定位。

    Index scan多半是出现在索引列在表达式中。数据库引擎无法直接确定你要的列的值,所以只能扫描整个整个索引进行计算。index seek就要好很多.数据库引擎只需要扫描几个分支节点就可以定位到你要的记录。回过来,如果聚集索引的叶子节点就是记录,那么Clustered Index Scan就基本等同于full table scan。

    一些优化原则

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

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

    虑建立群集索引;

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

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

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

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

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

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

    Sql的优化原则2:

    1、只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT

    2、尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值。

    3、尽量少用VARCHAR、TEXT、BLOB类型

    4、如果你的数据只有你所知的少量的几个。最好使用ENUM类型

    使用SQLServer Profiler找出数据库中性能最差的SQL

    首先打开SQLServer Profiler:

    2a0f2b73e4a5f262d87a771dcdb61a69.png

    然后点击工具栏“New Trace”,使用默认的模板,点击RUN。

    也许会有报错:"only TrueType fonts are supported. There id not a TrueType font"。不用怕,点击Tools菜单->Options,重新选择一个字体例如Vendana 即可。(这个是微软的一个bug)

    运行起来以后,SQLServer Profiler会监控数据库的活动,所以最好在你需要监控的数据库上多做些操作。等觉得差不多了,点击停止。然后保存trace结果到文件或者table。

    这里保存到Table:在菜单“File”-“Save as ”-“Trace table”,例如输入一个master数据库的新的table名:profileTrace,保存即可。

    找到最耗时的SQL:

    use master

    select * from profiletrace order by duration desc;

    复制代码

    找到了性能瓶颈,接下来就可以有针对性的一个个进行调优了。

    对使用SQLServer Profiler的更多信息可以参考:

    http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx

    使用SQLServer Database Engine Tuning Advisor数据库引擎优化顾问

    使用上述的SQLServer Profiler得到了trace还有一个好处就是可以用到这个优化顾问。用它可以偷点懒,得到SQLServer给您的优化顾问,例如这个表需要加个索引什么的…

    首先打开数据库引擎优化顾问:

    3d51ef72c97addef1c8051f5b4411ec6.png

    然后打开刚才profiler的结果(我们存到了master数据库的profileTrace表):

    d362d3eedfdc61af21f352797636dc51.png

    点击“start analysis”,运行完成后查看优化建议(图中最后是建议建立的索引,性能提升72%)

    7aabada49736cdccc581da1f61ba2e5e.png

    这个方法可以偷点懒,得到SQLServer给您的优化顾问。

    展开全文
  • mysql聚簇索引和非聚簇索引

    千次阅读 2020-03-15 20:17:12
    这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的. 聚簇索引和非聚簇索引的概念 我们先引用官网上的一段话来看看它们是干嘛的 Every InnoDB ...

    引语

    这篇文章主要介绍mysql中innodb的聚簇索引和非聚簇索引,那首先我们要先看下聚簇索引和非聚簇索引的概念是什么,是干什么用的.

    聚簇索引和非聚簇索引的概念

    我们先引用官网上的一段话来看看它们是干嘛的

    Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.
    
    When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
    
    If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
    
    If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
    

    有耐性的朋友可以自己翻译看看,这里咱们大概翻译了一下,总结出上面这段话的意思:
    每个InnoDB表都有一个特殊的索引,称为聚簇索引,用于存储行数据。
    1.如果创建了一个主键,InnoDB会将其用作聚簇索引(如果主键没有逻辑唯一且非空的列或列集,最好是设置成自动递增的)
    2.如果没有为表创建主键,则MySQL会在所有键列都不为NULL的情况下找到第一个UNIQUE索引,InnoDB会将其用作聚集索引
    3.如果表没有PRIMARY KEY或合适的UNIQUE索引,则InnoDB在包含行ID值的合成列上内部生成一个名为GEN_CLUST_INDEX的隐藏的聚集索引(隐藏的是看不到的,也就是说不会出现在desc table中,行ID是一个6字节的字段,随着插入新行而单调增加)
    从这三种情况来看的话,就是说不管你有没有创建主键,mysql都会给你弄一个聚簇索引给安排上,你创建了就用你设置的主键为聚簇索引,没有创建就给你来个隐藏的.

    聚簇索引(也称为主键索引)就是携带了行数据的索引,非聚簇索引就是除了聚簇索引之外的索引.这样说起来可能有点干巴巴的,咱们画个图来理解一下.
    假设有一张表test

    create table test(
    id int primary key,
    age int not null,
    name varchar(16),
    PRIMARY KEY (`id`),
    KEY `idx_age` (`age`) USING BTREE,
    KEY `idx_name` (`name`) USING BTREE,
    )engine=InnoDB;
    

    主键是id,然后有两个普通索引idx_age,idx_name(btree类型的索引),使用的是innodb引擎.
    我们知道id就是聚簇索引,idx_age,idx_name是非聚簇索引.
    现在有三条数据(1,11,‘甲’),(2,12,‘乙’),(2,13,‘丙’).那么他们在数据库中存储的形式是,如下:
    聚簇索引:

    非聚簇索引:

    可以看到聚簇索引后面是直接跟着的数据,而非聚簇索引指向的是聚簇索引的key值.
    因此非聚簇索引查询数据需要先查到聚簇索引的key,然后用这个key去查询真正的数据(这个过程称为回表).
    也就是说非聚簇索引是需要查询两次
    如图:

    所以能走聚簇索引的尽量走聚簇索引(也可以说是尽量走主键),看起来都是走索引,实际上主键要更快.
    而且主键索引如果是自增的int类型,因为长度比较小,占用的空间也比较小.

    覆盖索引

    我们上面说到如果是非聚簇索引的话会需要回表,查询两次,但是如果要查询得字段,数据直接就在索引上是可以不需要回表的.这种索引称为覆盖索引.
    比如我们要查询上面的test表中的age和name两个字段.

    select id,age,name from test where age = 13;
    

    直接查询的话,会根据age的索引找到id的key,然后再用id去查询出数据.
    但是如果我们创建一个(age,name)的联合索引,情况就不一样了.

    因为要返回的值,id在联合索引指向的key上,age和name共同组成了联合索引,
    因此数据都在(age,name)的联合索引上,并不需要回表在去查询一次,可以大大提高查询得效率.
    当然这个查询要比较频繁,使用率比较高,毕竟创建索引也是要消耗资源的,实际情况要根据查询频率和索引大小来做出判断.
    有联合索引存在的情况下能走覆盖索引当然是最好的,提高了查询效率.
    注:还有在某些count聚合函数使用的时候可以使用覆盖索引来优化count,比如说select count(age) from test.
    因为age是有索引了,直接使用到的也是age,所以覆盖索引了,无需回表.

    总结:

    1.聚簇索引和非聚簇索引,查询得时候使用聚簇索引能提高查询效率,尽量避免走非聚簇索引回表的耗时操作
    2.覆盖索引能提高查询效率,主要是避免了回表的操作,查询得时候根据具体情况建立合适的索引走覆盖索引提高查询速度

    参考资料:

    1.https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html
    2.https://mp.weixin.qq.com/s/y0pjtNUZhOW2ZBOy4m-xsA

    展开全文
  • 1.聚簇索引和非聚簇索引的概念 2.两者详细介绍 3. 两者的区别 3.1 数据存储方式 3.2二级索引查询 1.聚簇索引和非聚簇索引的概念 数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种。“聚簇”的...
  • 在《数据库原理》里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此,所以一个表最多只能有一个聚簇索引。 不过这个定义太...
  • 目录(1)聚簇索引(2)辅助索引(非聚簇索引)(3)InnoDB索引的实现(3.1)主键索引(3.2)InnoDB的辅助索引(4)MyISAM索引的实现(4.1)主键索引(4.2)辅助索引(Secondary key)(5)总结 (1)聚簇索引 聚簇...
  • Myisam的数据存储有三种后缀类型的文件,.MYD,.MYI,.frm,而.MYI就是Myisam存储的索引文件,.MYD是数据存储文件,所以说Myisam数据和索引是分开的。在MYI文件里,存储的是一棵树,查找的方式是顺着文件找,找到后,...
  • sql聚簇索引和非聚簇索引 The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A ...
  • 聚簇索引和非聚簇索引

    万次阅读 多人点赞 2018-08-07 15:34:30
    聚簇索引并不是一种单独的索引类型,而是...MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustere...
  • 所以聚簇索引和非聚簇索引本质的区别就是B+树的叶子节点上存储的是行数据还是行数据的地址(行号)。 InnoDB中每张表有且仅有一个聚簇索引(就是主键索引),InnoDB中的二级索引是非聚簇索引,那二级索引是怎么组织...
  • 非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方, 这两颗B+树的叶子节点都使用一个地址...
  • 1 数据结构及算法基础1.1 索引的本质官方定义:索引(Index)是帮助MySQL高效获取数据的数据结构本质:索引是数据结构查询是数据库的最主要功能之一。我们都希望查询速度能尽可能快,因此数据库系统的设计者会从查询...
  • MySQL常见的存储引擎有InnoDB和MyISAM。 InnoDB中,(一级索引)数据和索引是...每张表都有且仅有一个聚簇索引,会按照主键、唯一索引、生成rowid的顺序,采纳当做聚簇索引聚簇索引是B+树结构,数据都是有序存储...
  • 索引如何找到数据 比如现在有一张表有name、age、sex三个属性 如果没有建立索引,我们查询name=‘张三’的人 需要从表的第一行开始遍历到最后一行 但是如果我们创建了索引在name字段上,根据name...非聚簇索引很明.
  • 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘...
  • 一、聚簇索引 VS 非聚簇索引 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到...
  • 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘...
  • 聚簇索引和非聚簇索引(通俗易懂 言简意赅)

    千次阅读 多人点赞 2020-06-23 19:09:36
    总结:InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引...
  • 聚簇索引的特点是叶子节点包含了完整的记录行,而非聚簇索引的叶子节点只有所以字段和主键ID。 根据聚簇索引和非聚簇索引还能继续下分还能分为普通索引、覆盖索引、唯一索引以及联合索引等。 二、聚簇索引和非聚...
  • 聚簇索引和非聚簇索引区别 MySQL的聚簇索引是指Innodb引擎的特性,MySIAM并没有,如果需要该索引,只要将索引指定为主键(primary key)就可以了。 聚集(clustered)索引,也称聚簇索引。聚簇索引并不是一种单独的...
  • 每个 InnoDB 表都有一个称为 「 聚簇索引 」 的特殊索引,通常情况下,这个聚簇索引就是 「 主键 」( primary key ) 。Innodb 使用它存储表中每一行的数据。如果想要从 查询,插入 和其它数据库操作中获得最佳性能,...
  • 聚集索引:表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的...聚簇:innodb的主键索引关键字,与 记录 是存储在一起的导致的结果:记录依据主键顺序排序记录的真实位置会改变。随着主键索引关键字的...
  • 但是书中又把InnoDB作为聚簇索引的代表、MyISAM作为非聚簇索引的代表,两者进行比较,这就导致了矛盾点,如果将二级索引等价于非聚簇索引,那InnoDB就不能完全代表聚簇索引;但是单从二级索引的存储形式来看,又属于...
  • 聚簇索引和非聚簇索引的区别

    万次阅读 多人点赞 2020-07-31 16:26:24
    一、聚簇索引和非聚簇索引  1、聚簇索引和非聚簇索引:  我拿查字典做一个比喻,字典的页面就好比是物理排列顺序,物理排列顺序是固定的,查询的方式就好比是索引,区别是聚簇索引就好比是拼音查询,每一个字母...

空空如也

空空如也

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

非聚簇索引

友情链接: yolo.rar