精华内容
下载资源
问答
  • 索引的理解: 索引是对数据库表中的一列或多列的值进行排序的一种数据结构。 索引的作用就类似于书本的目录,新华字典的拼音,偏旁部首的首查字,可以快速的检索到需要 的内容,mysql在300万条记录性能就下降了,...

    索引的理解:

      索引是对数据库表中的一列或多列的值进行排序的一种数据结构。
      索引的作用就类似于书本的目录,新华字典的拼音,偏旁部首的首查字,可以快速的检索到需要的内容,mysql在300万条记录性能就下降了,虽然mysql官方文档说达500万~800万,所以当数据达到几百万的时候,那么索引就很有必要了。
      当表中有大量记录的时候,若要对表进行查询,第一种就是就需要把表中的记录全部取出来,在和查询条件一一对比,然后返回满足条件的记录、这样做就会大大消耗数据库系统的时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中ROWID(相当于页码)快速找到表中的记录。

    语句:

    CREATE TABLE table_name[filed_name data type]
    [unique|fulltext][index|key][index_name](filed_name[length])[asc|desc]

    1. unique|fulltext为可选参数,分别表示唯一索引、全文索引
    2. index和key为同义词,两者作用相同,用来指定创建索引
    3. filed_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择
    4. index_name指定索引的名称,为可选参数,如果不指定,默认filed_name为索引值
    5. length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
    6. asc或desc指定升序或降序的索引值存储

    索引类型:

    一、唯一索引(unique):列的值必须唯一,但允许有空值。如果是组合索引则列值必须唯一

    1)、创建唯一索引

    CREATE UNIQUE INDEX index_name ON table(column(length))

    2)、修改表结构

    ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

    3)、创建表时直接指定

    CREATE TABLE `table_name`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` INT(8) NOT NULL,
    `title` VARCHAR(255) NOTNULL,
    `content` TEXT,
    `time` INT(10) NULL DEFAULT NULL,
    UNIQUE index_name(code)
    );

    二、主键索引(primary key):是一特殊的唯一索引,一个表允许有一个主键,主键要求建表时指定.

    CREATE TABLE `table_name`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` INT(8) NOT NULL,
    `title` VARCHAR(255) NOTNULL,
    `content` TEXT,
    `time` INT(10) NULL DEFAULT NULL,
    PRIMARY KEY(`id`)
    );

    三、普通索引(index):最基本的索引,没有任何限制。如果是char,varchar类型,length可以小于字段长度、如果是BLOB和TEXT类型,必须指定长度。

    1)、直接创建

    CREATE INDEX index_name ON table(column(length))

    2)、修改表结构的方式添加

    ALTER TABLE table_name ADD INDEX index_name ON (column(length))

    3)、创建表时直接指定

    CREATE TABLE `table_name`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOTNULL,
    `content` TEXT,
    `time` INT(10) NULL DEFAULT NULL,
    PRIMARY KEY(`id`),
    INDEX index_name(title(length))
    );

    4)、删除索引

    DROP INDEX index_name ON table

    四、多列索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段索引才会使用。使用组合索引遵循最左前缀原则
    五、全文索引(fulltext):主要是用来查找文本的关键字,而不是直接与索引中的值比较。fulltext索引跟其他索引大不相同,它更像一个搜索引擎,而不是简单的where语句的参数匹配。fulltext要配合match against操作使用,而不是一般的where语句加like。它可以在create tablealter tablecreate index使用,不过目前只有char varchar text列上可以创建全文索引。

    tip:全文索引只有MyISAM支持,不过在mysql5.6后Innodb也支持了

    1)、创建表的时候添加全文索引


    CREATE TABLE `table_name`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOTNULL,
    `content` TEXT,
    `time` INT(10) NULL DEFAULT NULL,
    FULLTEXT (content)
    );

    2)、修改表结构添加全文索引

    ALTER TABLE table_name ADD FULLTEXT index_name(content)

    3)、直接创建索引

    CREATE FULLTEXT INDEX index_namet ON table_name(content)

    缺点:

    1. 虽然索引大大的提升了查询的速度,同时也降低了对表的增删改的速度,因为更新表,不仅要保存数据,还要保存索引文件
    2. 建立索引会占用磁盘空间。一般不太严重,如果你在一个大表上创建多种组合索引,索引文件会增长很快索引只是提高效率的一个因素,如果有大数据量的表,就要花时间研究建立最优秀的索引,或优化查询语句

    索引方式:

        对于BTREE和HASH索引,当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件。Hash索引还有一些其它特征:它们只用于使用=或<=>操作符的等式比较(但很快)。优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目)。MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表,会影响一些查询。只能使用整个关键字来搜索一行。(用B-树索引,任何关键字的最左面的前缀可用来找到行)。

        对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE ‘pattern’(其中 ‘pattern’不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。“常量值”系指:查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式。

    下面是一些WHERE子句中有范围条件的查询的例子。
    下列范围查询适用于 btree索引和hash索引:

    SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

    下列范围查询适用于btree索引

    SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
    SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';

    创建索引原则与使用索引注意事项:

    1. 选择唯一索引

      唯一索引的值是唯一的,可以更快速的通过该索引确定某一条记录

    2. 为经常需要排序、分组和联合操作的字段建立索引

      经常需要order by、 group by、distinct和union等操作的字段,排序会浪费很多时间,如果为其建立索引,可以有效避免排序操作

    3. 经常作为查询条件的字段建立索引

      如果某个字段经常作为查询条件,name该字段的查询速度就会影响整个表的速度。因此,为这样的字段建立索引,可以提高整个表的查询速度

    4. 限制索引的数目

      索引的数目不是越多越好。每个索引都是要占用磁盘空间的,索引越多,需要的磁盘空间就越大。修改表时,和更新会很麻烦。越多的索引,会使更新表变得很浪费时间

    5. 尽量使用数据量少的索引

      如果索引的值很长,那么查询的速度就会受到影响,例如对一个char(100)类型的字段进行全文检索需要的时间肯定要比char(10)类型的字段需要的时间多得多,并且char(10)全文检索而且可以节省磁盘空间和I/O操作。

    6. 尽量使用前缀来索引

      如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。

    7. 删除不再使用或者很少使用的索引

      表中的数据被大量更新,或者数据使用方式被改变后,原有的一些索引可能不在需要。数据库管理员应当定期找出这些索引,将它们删除。从而减少索引对更新操作的影响

    8. 最左前缀原则,非常重要的一个原则

      mysql会一直向右匹配知道遇到范围查询(>、<、between、like)就停止匹配,比如a=1 and b=2 and c>3 and d=4 如果建立(a,b,c,d)顺序索引,d是用不到索引的,如果是建立(a,b,d,c)则可以都用到

    9. =和in可以乱序

      比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

    10. 尽量选择区分度高的列作为索引。

      区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就 是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条 记录

    11. 索引列不能参与计算,保持列“干净”。

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

    12. 尽量的扩展索引,不要新建索引。

      比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

    13. 不使用NOT IN和<>操作

    14. like语句操作

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

    15. 索引列排序

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

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

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


    注意:选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则,但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断,选择最合适的索引方式。

    索引优化:

    一、导致SQL执行慢的原因

    1. 硬件问题。如网络速度慢,内存不足,I/O吞吐量少,磁盘空间满了等。
    2. 没有索引或索引失效。
    3. 数据过多(分库分表)。
    4. 服务器调优及各个参数设置(调整my.cnf)

    二、分析原因时,一定要找切入点

    1. 先观察,开启慢查询日志,设置相应的阈值(比如超过2秒就是慢SQL),在生产环境上跑一天以后,看看那些SQL比较慢
    2. Explain和慢SQL分析。比如写的烂的SQL语句,索引没有或失效,关联查询太多(有时候是设计缺陷或者不得已的需求)等等
    3. Show Profile是比Explain更进一步的执行细节,可以查询到每一个SQL都干了些什么事,这些事分别花了多少时间
    4. 找DBA或运维对MYsql进行服务器参数调优

    Explain 与 Show Profile详情下两章介绍

    参考:https://blog.csdn.net/GV7lZB0y87u7C/article/details/79969293
       https://blog.csdn.net/u013412790/article/details/51612304

    展开全文
  • SQL创建索引

    千次阅读 2012-07-10 16:53:29
    为给定表或视图创建索引。 只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引,无论表中是否有数据。可以通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。 语法 CREATE ...

    为给定表或视图创建索引。
    只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引,无论表中是否有数据。可以通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。

    语法
    CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
        ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
    [ WITH < index_option > [ ,...n] ]
    [ ON filegroup ]

    < index_option > ::=
        { PAD_INDEX |
            FILLFACTOR = fillfactor |
            IGNORE_DUP_KEY |
            DROP_EXISTING |
        STATISTICS_NORECOMPUTE |
        SORT_IN_TEMPDB 
    }

    参数
    UNIQUE

    为表或视图创建唯一索引(不允许存在索引值相同的两行)。视图上的聚集索引必须是 UNIQUE 索引。

    在创建索引时,如果数据已存在,Microsoft? SQL Server? 会检查是否有重复值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行这种检查。如果存在重复的键值,将取消 CREATE INDEX 语句,并返回错误信息,给出第一个重复值。当创建 UNIQUE 索引时,有多个 NULL 值被看作副本。

    如果存在唯一索引,那么会产生重复键值的 UPDATE 或 INSERT 语句将回滚,SQL Server 将显示错误信息。即使 UPDATE 或 INSERT 语句更改了许多行但只产生了一个重复值,也会出现这种情况。如果在有唯一索引并且指定了 IGNORE_DUP_KEY 子句情况下输入数据,则只有违反 UNIQUE 索引的行才会失败。在处理 UPDATE 语句时,IGNORE_DUP_KEY 不起作用。

    SQL Server 不允许为已经包含重复值的列创建唯一索引,无论是否设置了 IGNORE_DUP_KEY。如果尝试这样做,SQL Server 会显示错误信息;重复值必须先删除,才能为这些列创建唯一索引。

    CLUSTERED

    创建一个对象,其中行的物理排序与索引排序相同,并且聚集索引的最低一级(叶级)包含实际的数据行。一个表或视图只允许同时有一个聚集索引。

    具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引,然后才能为该视图定义其它索引。

    在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。

    如果没有指定 CLUSTERED,则创建非聚集索引。

     

    说明  因为按照定义,聚集索引的叶级与其数据页相同,所以创建聚集索引时使用 ON filegroup 子句实际上会将表从创建该表时所用的文件移到新的文件组中。在特定的文件组上创建表或索引之前,应确认哪些文件组可用并且有足够的空间供索引使用。文件组的大小必须至少是整个表所需空间的 1.2 倍,这一点很重要。


    NONCLUSTERED

    创建一个指定表的逻辑排序的对象。对于非聚集索引,行的物理排序独立于索引排序。非聚集索引的叶级包含索引行。每个索引行均包含非聚集键值和一个或多个行定位器(指向包含该值的行)。如果表没有聚集索引,行定位器就是行的磁盘地址。如果表有聚集索引,行定位器就是该行的聚集索引键。

    每个表最多可以有 249 个非聚集索引(无论这些非聚集索引的创建方式如何:是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建,还是使用 CREATE INDEX 显式创建)。每个索引均可以提供对数据的不同排序次序的访问。

    对于索引视图,只能为已经定义了聚集索引的视图创建非聚集索引。因此,索引视图中非聚集索引的行定位器一定是行的聚集键。

    index_name

    是索引名。索引名在表或视图中必须唯一,但在数据库中不必唯一。索引名必须遵循标识符规则。

    table

    包含要创建索引的列的表。可以选择指定数据库和表所有者。

    view

    要建立索引的视图的名称。必须使用 SCHEMABINDING 定义视图才能在视图上创建索引。视图定义也必须具有确定性。如果选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性,则视图也具有确定性。而且,所有键列必须是精确的。只有视图的非键列可能包含浮点表达式(使用 float 数据类型的表达式),而且 float 表达式不能在视图定义的其它任何位置使用。

    若要在确定性视图中查找列,请使用 COLUMNPROPERTY 函数(IsDeterministic 属性)。该函数的 IsPrecise 属性可用来确定键列是否精确。

    必须先为视图创建唯一的聚集索引,才能为该视图创建非聚集索引。

    在 SQL Server 企业版或开发版中,查询优化器可使用索引视图加快查询的执行速度。要使优化程序考虑将该视图作为替换,并不需要在查询中引用该视图。

    在创建索引视图或对参与索引视图的表中的行进行操作时,有 7 个 SET 选项必须指派特定的值。SET 选项 ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、 ANSI_PADDING 和 ANSI_WARNING 必须为 ON。SET 选项 NUMERIC_ROUNDABORT 必须为 OFF。

    如果与上述设置有所不同,对索引视图所引用的任何表执行的数据修改语句 (INSERT、UPDATE、DELETE) 都将失败,SQL Server 会显示一条错误信息,列出所有违反设置要求的 SET 选项。此外,对于涉及索引视图的 SELECT 语句,如果任何 SET 选项的值不是所需的值,则 SQL Server 在处理该 SELECT 语句时不考虑索引视图替换。在受上述 SET 选项影响的情况中,这将确保查询结果的正确性。

    如果应用程序使用 DB-Library 连接,则必须为服务器上的所有 7 个 SET 选项指派所需的值。(默认情况下,OLE DB 和 ODBC 连接已经正确设置了除 ARITHABORT 外所有需要的 SET 选项。)

    如果并非所有上述 SET 选项均有所需的值,则某些操作(例如 BCP、复制或分布式查询)可能无法对参与索引视图的表执行更新。在大多数情况下,将 ARITHABORT 设置为 ON(通过服务器配置选项中的 user options)可以避免这一问题。

    强烈建议在服务器的任一数据库中创建计算列上的第一个索引视图或索引后,尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。

    有关索引视图注意事项和限制的更多信息,请参见注释部分。

    column

    应用索引的列。指定两个或多个列名,可为指定列的组合值创建组合索引。在 table 后的圆括号中列出组合索引中要包括的列(按排序优先级排列)。

     

    说明  由 ntext、text 或 image 数据类型组成的列不能指定为索引列。另外,视图不能包括任何 text、ntext 或 image 列,即使在 CREATE INDEX 语句中没有引用这些列。


    当两列或多列作为一个单位搜索最好,或者许多查询只引用索引中指定的列时,应使用组合索引。最多可以有 16 个列组合到一个组合索引中。组合索引中的所有列必须在同一个表中。组合索引值允许的最大大小为 900 字节。也就是说,组成组合索引的固定大小列的总长度不得超过 900 字节。有关组合索引中可变类型列的更多信息,请参见注释部分。

    [ASC | DESC]

    确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。

    n

    表示可以为特定索引指定多个 columns 的占位符。

    PAD_INDEX

    指定索引中间级中每个页(节点)上保持开放的空间。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用,因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。默认情况下,给定中间级页上的键集,SQL Server 将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果为 FILLFACTOR 指定的百分比不够大,无法容纳一行,SQL Server 将在内部使用允许的最小值替代该百分比。

     

    说明  中间级索引页上的行数永远都不会小于两行,无论 FILLFACTOR 的值有多小。


    FILLFACTOR = fillfactor

    指定在 SQL Server 创建索引的过程中,各索引页叶级的填满程度。如果某个索引页填满,SQL Server 就必须花时间拆分该索引页,以便为新行腾出空间,这需要很大的开销。对于更新频繁的表,选择合适的 FILLFACTOR 值将比选择不合适的 FILLFACTOR 值获得更好的更新性能。FILLFACTOR 的原始值将在 sysindexes 中与索引一起存储。

    如果指定了 FILLFACTOR,SQL Server 会向上舍入每页要放置的行数。例如,发出 CREATE CLUSTERED INDEX ...FILLFACTOR = 33 将创建一个 FILLFACTOR 为 33% 的聚集索引。假设 SQL Server 计算出每页空间的 33% 为 5.2 行。SQL Server 将其向上舍入,这样,每页就放置 6 行。

     

    说明  显式的 FILLFACTOR 设置只是在索引首次创建时应用。SQL Server 并不会动态保持页上可用空间的指定百分比。


    用户指定的 FILLFACTOR 值可以从 1 到 100。如果没有指定值,默认值为 0。如果 FILLFACTOR 设置为 0,则只填满叶级页。可以通过执行 sp_configure 更改默认的 FILLFACTOR 设置。

    只有不会出现 INSERT 或 UPDATE 语句时(例如对只读表),才可以使用 FILLFACTOR 100。如果 FILLFACTOR 为 100,SQL Server 将创建叶级页 100% 填满的索引。如果在创建 FILLFACTOR 为 100% 的索引之后执行 INSERT 或 UPDATE,会对每次 INSERT 操作以及有可能每次 UPDATE 操作进行页拆分。

    如果 FILLFACTOR 值较小(0 除外),就会使 SQL Server 创建叶级页不完全填充的新索引。例如,如果已知某个表包含的数据只是该表最终要包含的数据的一小部分,那么为该表创建索引时,FILLFACTOR 为 10 会是合理的选择。FILLFACTOR 值较小还会使索引占用较多的存储空间。

    下表说明如何在已指定 FILLFACTOR 的情况下填充索引页。

    FILLFACTOR 中间级页 叶级页
    0 一个可用项 100% 填满
    1% -99 一个可用项 <= FILLFACTOR% 填满
    100% 一个可用项 100% 填满


    一个可用项是指页上可以容纳另一个索引项的空间。

     

    重要  用某个 FILLFACTOR 值创建聚集索引会影响数据占用存储空间的数量,因为 SQL Server 在创建聚集索引时会重新分布数据。


    IGNORE_DUP_KEY

    控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了 IGNORE_DUP_KEY,并且执行了创建重复键的 INSERT 语句,SQL Server 将发出警告消息并忽略重复的行。

    如果没有为索引指定 IGNORE_DUP_KEY,SQL Server 会发出一条警告消息,并回滚整个 INSERT 语句。

    下表显示何时可使用 IGNORE_DUP_KEY。

    索引类型 选项
    聚集 不允许
    唯一聚集 允许使用 IGNORE_DUP_KEY
    非聚集 不允许
    唯一非聚集 允许使用 IGNORE_DUP_KEY


    DROP_EXISTING

    指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。因为非聚集索引包含聚集键,所以在除去聚集索引时,必须重建非聚集索引。如果重建聚集索引,则必须重建非聚集索引,以便使用新的键集。

    为已经具有非聚集索引的表重建聚集索引时(使用相同或不同的键集), DROP_EXISTING 子句可以提高性能。DROP_EXISTING 子句代替了先对旧的聚集索引执行 DROP INDEX 语句,然后再对新的聚集索引执行 CREATE INDEX 语句的过程。非聚集索引只需重建一次,而且还只是在键不同的情况下才需要。

    如果键没有改变(提供的索引名和列与原索引相同),则 DROP_EXISTING 子句不会重新对数据进行排序。在必须压缩索引时,这样做会很有用。

    无法使用 DROP_EXISTING 子句将聚集索引转换成非聚集索引;不过,可以将唯一聚集索引更改为非唯一索引,反之亦然。

     

    说明  当执行带 DROP_EXISTING 子句的 CREATE INDEX 语句时,SQL Server 假定索引是一致的(即索引没有损坏)。指定索引中的行应按 CREATE INDEX 语句中引用的指定键排序。


    STATISTICS_NORECOMPUTE

    指定过期的索引统计不会自动重新计算。若要恢复自动更新统计,可执行没有 NORECOMPUTE 子句的 UPDATE STATISTICS。

     

    重要  如果禁用分布统计的自动重新计算,可能会妨碍 SQL Server 查询优化器为涉及该表的查询选取最佳执行计划。


    SORT_IN_TEMPDB

    指定用于生成索引的中间排序结果将存储在 tempdb 数据库中。如果 tempdb 与用户数据库不在同一磁盘集,则此选项可能减少创建索引所需的时间,但会增加创建索引时使用的磁盘空间。

    有关更多信息,请参见 tempdb 和索引创建。

    ON filegroup

    在给定的 filegroup 上创建指定的索引。该文件组必须已经通过执行 CREATE DATABASE 或 ALTER DATABASE 创建。

    注释
    为表或索引分配空间时,每次递增一个扩展盘区(8 个 8 KB 的页)。每填满一个扩展盘区,就会再分配一个。如果表非常小或是空表,其索引将使用单页分配,直到向索引添加了 8 页后,再转而进行扩展盘区分配。若要获得有关索引已分配和占用的空间数量的报表,请使用 sp_spaceused。

    创建聚集索引要求数据库中的可用空间大约为数据大小的 1.2 倍。该空间不包括现有表占用的空间;将对数据进行复制以创建聚集索引,旧的无索引数据将在索引创建完成后删除。使用 DROP_EXISTING 子句时,聚集索引所需的空间数量与现有索引的空间要求相同。所需的额外空间可能还受指定的 FILLFACTOR 的影响。

    在 SQL Server 2000 中创建索引时,可以使用 SORT_IN_TEMPDB 选项指示数据库引擎在 tempdb 中存储中间索引排序结果。如果 tempdb 在不同于用户数据库所在的磁盘集上,则此选项可能减少创建索引所需的时间,但会增加用于创建索引的磁盘空间。除在用户数据库中创建索引所需的空间外, tempdb 还必须有大约相同的额外空间来存储中间排序结果。有关更多信息,请参见 tempdb 和索引创建。

    CREATE INDEX 语句同其它查询一样优化。SQL Server 查询处理器可以选择扫描另一个索引,而不是执行表扫描,以节省 I/O 操作。在某些情况下,可以不必排序。

    在运行 SQL Server 企业管理器和程序员版的多处理器计算机上,CREATE INDEX 自动使用多个处理器执行扫描和排序,与其它查询的操作方式相同。执行一条 CREATE INDEX 语句所使用的处理器数由配置选项 max degree of parallelism 和当前的工作负荷决定。如果 SQL Server 检测到系统正忙,则在开始执行语句之前,CREATE INDEX 操作的并发程度会自动降低。

    自上一次文件组备份以来受 CREATE INDEX 语句影响的全部文件组必须作为一个单位备份。有关文件和文件组备份的更多信息,请参见 BACKUP。

    备份和 CREATE INDEX 操作不相互防碍。如果正在进行备份,则在完整日志记录模式中创建索引,而这可能需要额外的日志空间。

    若要显示有关对象索引的报表,请执行 sp_helpindex。

    可以为临时表创建索引。在除去表或终止会话时,所有索引和触发器都将被除去。

    索引中的可变类型列
    索引键允许的最大大小为 900 字节,不过 SQL Server 2000 允许在可能包含大量可变类型列的列上创建索引,而这些列的最大大小超过 900 字节。

    在创建索引时,SQL Server 检查下列条件:

    所有参与索引定义的固定数据列的总长度必须小于或等于 900 字节。当所要创建的索引只由固定数据列构成时,固定数据列的总计大小必须小于或等于 900 字节。否则将不能创建索引,且 SQL Server 将返回错误。


    如果索引定义由固定类型列和可变类型列组成,且固定数据列满足前面的条件(小于或等于 900 字节),则 SQL Server 仍要检查可变类型列的总大小。如果可变类型列的最大大小与固定数据列大小的和大于 900 字节,则 SQL Server 将创建索引,不过将给用户返回警告消息以提醒用户:如果随后在可变类型列上的插入或更新操作导致总大小超过 900 字节,则操作将失败且用户将收到运行时错误。同样,如果索引定义只由可变类型列组成,且这些列的最大总大小大于 900 字节,则 SQL Server 将创建索引,不过将返回警告消息。
    有关更多信息,请参见索引键的最大值。

    在计算列和视图上创建索引时的考虑
    在 SQL Server 2000 中,还可以在计算列和视图上创建索引。在视图上创建唯一聚集索引可以提高查询性能,因为视图存储在数据库中的方式与具有聚集索引的表的存储方式相同。

    UNIQUE 或 PRIMARY KEY 只要满足所有索引条件,就可以包含计算列。具体说来就是,计算列必须具有确定性、必须精确,且不能包含 text、ntext 或 image 列。有关确定性的更多信息,请参见确定性函数和非确定性函数。

    在计算列或视图上创建索引可能导致前面产生的 INSERT 或 UPDATE 操作失败。当计算列导致算术错误时可能产生这样的失败。例如,虽然下表中的计算列 c 将导致算术错误,但是 INSERT 语句仍有效:

    CREATE TABLE t1 (a int, b int, c AS a/b)
    GO
    INSERT INTO t1 VALUES ('1', '0')
    GO

    相反,如果创建表之后在计算列 c 上创建索引,则上述 INSERT 语句将失败。

    CREATE TABLE t1 (a int, b int, c AS a/b)
    GO
    CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
    GO
    INSERT INTO t1 VALUES ('1', '0')
    GO

    在通过数字或 float 表达式定义的视图上使用索引所得到的查询结果,可能不同于不在视图上使用索引的类似查询所得到的结果。这种差异可能是由对基础表进行 INSERT、DELETE 或 UPDATE 操作时的舍入错误引起的。

    若要防止 SQL Server 使用索引视图,请在查询中包含 OPTION (EXPAND VIEWS) 提示。此外,任何所列选项设置不正确均会阻止优化程序使用视图上的索引。有关 OPTION (EXPAND VIEWS) 提示的更多信息,请参见 SELECT。

    对索引视图的限制
    定义索引视图的 SELECT 语句不得包含 TOP、DISTINCT、COMPUTE、HAVING 和 UNION 关键字。也不能包含子查询。

    SELECT 列表中不得包含星号 (*)、'table.*' 通配符列表、DISTINCT、COUNT(*)、COUNT(<expression>)、基表中的计算列和标量聚合。

    非聚合 SELECT 列表中不能包含表达式。聚合 SELECT 列表(包含 GROUP BY 的查询)中可能包含 SUM 和 COUNT_BIG(<expression>);它一定包含 COUNT_BIG(*)。不允许有其它聚合函数(MIN、MAX、STDEV,...)。

    使用 AVG 的复杂聚合无法参与索引视图的 SELECT 列表。不过,如果查询使用这样的聚合,则优化程序将能使用该索引视图,用 SUM 和 COUNT_BIG 的简单聚合组合代替 AVG。

    若某列是从取值为 float 数据类型或使用 float 表达式进行取值的表达式得到的,则不能作为索引视图或表中计算列的索引键。这样的列被视为是不精确的。使用 COLUMNPROPERTY 函数决定特定计算列或视图中的列是否精确。

    索引视图受限于以下的附加限制:

    索引的创建者必须拥有表。所有表、视图和索引必须在同一数据库中创建。


    定义索引视图的 SELECT 语句不得包含视图、行集函数、行内函数或派生表。同一物理表在该语句中只能出现一次。


    在任何联接表中,均不允许进行 OUTER JOIN 操作。


    搜索条件中不允许使用子查询或者 CONTAINS 或 FREETEXT 谓词。


    如果视图定义包含 GROUP BY 子句,则视图的 SELECT 列表中必须包含所有分组依据列及 COUNT_BIG(*) 表达式。此外,CREATE UNIQUE CLUSTERED INDEX 子句中必须只包含这些列。
    可以创建索引的视图的定义主体必须具有确定性且必须精确,这类似于计算列上的索引要求。请参见在计算列上创建索引。

    权限
    CREATE INDEX 的权限默认授予 sysadmin 固定服务器角色、db_ddladmin 和 db_owner 固定数据库角色和表所有者且不能转让。

    示例
    A. 使用简单索引
    下面的示例为 authors 表的 au_id 列创建索引。

    SET NOCOUNT OFF
    USE pubs
    IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'au_id_ind')
       DROP INDEX authors.au_id_ind
    GO
    USE pubs
    CREATE INDEX au_id_ind
       ON authors (au_id)
    GO

    B. 使用唯一聚集索引
    下面的示例为 emp_pay 表的 employeeID 列创建索引,并且强制唯一性。因为指定了 CLUSTERED 子句,所以该索引将对磁盘上的数据进行物理排序。

    SET NOCOUNT ON
    USE pubs
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'emp_pay')
       DROP TABLE emp_pay
    GO
    USE pubs
    IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'employeeID_ind')
       DROP INDEX emp_pay.employeeID_ind
    GO
    USE pubs
    GO
    CREATE TABLE emp_pay
    (
     employeeID int NOT NULL,
     base_pay money NOT NULL,
     commission decimal(2, 2) NOT NULL
    )
    INSERT emp_pay
       VALUES (1, 500, .10)
    INSERT emp_pay
       VALUES (2, 1000, .05)
    INSERT emp_pay
       VALUES (3, 800, .07)
    INSERT emp_pay
       VALUES (5, 1500, .03)
    INSERT emp_pay
       VALUES (9, 750, .06)
    GO
    SET NOCOUNT OFF
    CREATE UNIQUE CLUSTERED INDEX employeeID_ind
       ON emp_pay (employeeID)
    GO

    C. 使用简单组合索引
    下面的示例为 order_emp 表的 orderID 列和 employeeID 列创建索引。

    SET NOCOUNT ON
    USE pubs
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'order_emp')
       DROP TABLE order_emp
    GO
    USE pubs
    IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'emp_order_ind')
       DROP INDEX order_emp.emp_order_ind
    GO
    USE pubs
    GO
    CREATE TABLE order_emp
    (
     orderID int IDENTITY(1000, 1),
     employeeID int NOT NULL,
     orderdate datetime NOT NULL DEFAULT GETDATE(),
     orderamount money NOT NULL
    )

    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (5, '4/12/98', 315.19)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (5, '5/30/98', 1929.04)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (1, '1/03/98', 2039.82)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (1, '1/22/98', 445.29)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (4, '4/05/98', 689.39)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (7, '3/21/98', 1598.23)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (7, '3/21/98', 445.77)
    INSERT order_emp (employeeID, orderdate, orderamount)
       VALUES (7, '3/22/98', 2178.98)
    GO
    SET NOCOUNT OFF
    CREATE INDEX emp_order_ind
       ON order_emp (orderID, employeeID)

    D. 使用 FILLFACTOR 选项
    下面的示例使用 FILLFACTOR 子句,将其设置为 100。FILLFACTOR 为 100 将完全填满每一页,只有确定表中的索引值永远不会更改时,该选项才有用。

    SET NOCOUNT OFF
    USE pubs
    IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'zip_ind')
       DROP INDEX authors.zip_ind
    GO
    USE pubs
    GO
    CREATE NONCLUSTERED INDEX zip_ind
       ON authors (zip)
       WITH FILLFACTOR = 100

    E. 使用 IGNORE_DUP_KEY
    下面的示例为 emp_pay 表创建唯一聚集索引。如果输入了重复的键,将忽略该 INSERT 或 UPDATE 语句。

    SET NOCOUNT ON
    USE pubs
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'emp_pay')
       DROP TABLE emp_pay
    GO
    USE pubs
    IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'employeeID_ind')
       DROP INDEX emp_pay.employeeID_ind
    GO
    USE pubs
    GO
    CREATE TABLE emp_pay
    (
     employeeID int NOT NULL,
     base_pay money NOT NULL,
     commission decimal(2, 2) NOT NULL
    )
    INSERT emp_pay
       VALUES (1, 500, .10)
    INSERT emp_pay
       VALUES (2, 1000, .05)
    INSERT emp_pay
       VALUES (3, 800, .07)
    INSERT emp_pay
       VALUES (5, 1500, .03)
    INSERT emp_pay
       VALUES (9, 750, .06)
    GO
    SET NOCOUNT OFF
    GO
    CREATE UNIQUE CLUSTERED INDEX employeeID_ind
       ON emp_pay(employeeID)
       WITH IGNORE_DUP_KEY

    F. 使用 PAD_INDEX 创建索引
    下面的示例为 authors 表中的作者标识号创建索引。没有 PAD_INDEX 子句,SQL Server 将创建填充 10% 的叶级页,但是叶级之上的页几乎被完全填满。使用 PAD_INDEX 时,中间级页也填满 10%。

     

    说明  如果没有指定 PAD_INDEX,唯一聚集索引的索引页上至少会出现两项。


    SET NOCOUNT OFF
    USE pubs
    IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'au_id_ind')
       DROP INDEX authors.au_id_ind
    GO
    USE pubs
    CREATE INDEX au_id_ind
       ON authors (au_id)
       WITH PAD_INDEX, FILLFACTOR = 10

    G. 为视图创建索引
    下面的示例将创建一个视图,并为该视图创建索引。然后,引入两个使用该索引视图的查询。

    USE Northwind
    GO

    --Set the options to support indexed views.
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
    GO

    --Create view.
    CREATE   VIEW V1
    WITH   SCHEMABINDING
    AS
       SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
       FROM   dbo.[Order Details] od, dbo.Orders o
       WHERE   od.OrderID=o.OrderID
       GROUP BY   OrderDate, ProductID
    GO

    --Create index on the view.
    CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
    GO

    --This query will use the above indexed view.
    SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
    FROM   dbo.[Order Details] od, dbo.Orders o
    WHERE   od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
       AND OrderDate >= '05/01/1998'
    GROUP BY OrderDate, ProductID
    ORDER BY Rev DESC

    --This query will use the above indexed view.
    SELECT  OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
    FROM   dbo.[Order Details] od, dbo.Orders o
    WHERE   od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
       AND DATEPART(yy,OrderDate) = 1998
    GROUP BY OrderDate
    ORDER BY OrderDate ASC

    <h3>
       心静似高山流水不动,心清若巫峰雾气不沾。
    </h3>
    展开全文
  • MySQL索引创建与使用

    万次阅读 多人点赞 2018-08-06 18:49:59
    声明:本人主要简单示例MySQL中的单列索引、组合索引创建与使用。 索引创建: 建表时创建: CREATE TABLE 表名( 字段名 数据类型 [完整性约束条件], ……, [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY ...

    索引有很多,且按不同的分类方式,又有很多种分类。不同的数据库,对索引的支持情况也不尽相同。

    声明:本人主要简单示例MySQL中的单列索引组合索引的创建与使用。


    索引的创建

    建表时创建

    CREATE TABLE 表名(

    字段名 数据类型 [完整性约束条件],

           ……,

    [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

    [索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

    );

    说明:

    • UNIQUE:可选。表示索引为唯一性索引。
    • FULLTEXT:可选。表示索引为全文索引。
    • SPATIAL:可选。表示索引为空间索引。
    • INDEX和KEY:用于指定字段为索引,两者选择其中之一就可以了,作用是    一样的。
    • 索引名:可选。给创建的索引取一个新名称。
    • 字段名1:指定索引对应的字段的名称,该字段必须是前面定义好的字段。
    • 长度:可选。指索引的长度,必须是字符串类型才可以使用。
    • ASC:可选。表示升序排列。
    • DESC:可选。表示降序排列。

    注:索引方法默认使用B+TREE。

    单列索引(示例):

    CREATE TABLE projectfile (
    	id INT AUTO_INCREMENT COMMENT '附件id',
    	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    	projectid INT COMMENT '项目id;此列受project表中的id列约束',
    	filename VARCHAR (512) COMMENT '附件名',
    	fileurl VARCHAR (512) COMMENT '附件下载地址',
    	filesize BIGINT COMMENT '附件大小,单位Byte',
    	-- 主键本身也是一种索引(注:也可以在上面的创建字段时使该字段主键自增)
            PRIMARY KEY (id),
    	-- 主外键约束(注:project表中的id字段约束了此表中的projectid字段)
    	FOREIGN KEY (projectid) REFERENCES project (id),
    	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
    	UNIQUE INDEX (projectid),
    	-- 给fileuploadercode字段创建普通索引
    	INDEX (fileuploadercode)
    	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

    注:这里只为示例如何创建索引,其他的合理性之类的先放一边。

    组合索引(示例):

    CREATE TABLE projectfile (
    	id INT AUTO_INCREMENT COMMENT '附件id',
    	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    	projectid INT COMMENT '项目id;此列受project表中的id列约束',
    	filename VARCHAR (512) COMMENT '附件名',
    	fileurl VARCHAR (512) COMMENT '附件下载地址',
    	filesize BIGINT COMMENT '附件大小,单位Byte',
    	-- 主键本身也是一种索引(注:也可以在上面的创建字段时使该字段主键自增)
            PRIMARY KEY (id),
            -- 创建组合索引
    	INDEX (fileuploadercode,projectid)
    	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

    建表后创建

    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

    CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]

    示例一:

    -- 假设建表时fileuploadercode字段没创建索引(注:同一个字段可以创建多个索引,但一般情况下意义不大)
    -- 给projectfile表中的fileuploadercode创建索引
    ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);

    示例二:

    ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

    示例三:

    -- 将id列设置为主键
    ALTER TABLE index_demo ADD PRIMARY KEY(id) ;
    -- 将id列设置为自增
    ALTER TABLE index_demo MODIFY id INT auto_increment;  

    查看已创建的索引

    show index from 表名;

    提示:我们也可以直接使用工具查看

    示例:


    索引的删除

    DROP INDEX 索引名 ON 表名

    ALTER TABLE 表名 DROP INDEX 索引名

    示例一:

    drop index fileuploadercode1 on projectfile;

    示例二:

    alter table projectfile drop index s2123;

    查看SQL语句对索引的使用情况(即:查询SQL的查询执行计划QEP)

    在select语句前加上EXPLAIN即可。

    示例:

    EXPLAIN SELECT * FROM `index_demo` ii WHERE ii.e_name = 'Jane';

    分析该SQL的性能为:

    提示:我们也可以使用SQL工具查看,如:navicat中的“解释”选项即可查看。

    说明:

    id:SELECT识别符。这是SELECT的查询序列号。

    select_type:SELECT类型。

    1. SIMPLE: 简单SELECT(不使用UNION或子查询)
    2. PRIMARY: 最外面的SELECT
    3. UNION:UNION中的第二个或后面的SELECT语句
    4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
    5. UNION RESULT:UNION的结果
    6. SUBQUERY:子查询中的第一个SELECT
    7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
    8. DERIVED:导出表的SELECT(FROM子句的子查询)

    table:表名

    type:联接类型。是SQL性能的非常重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref
                > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
                一般来说,得保证查询至少达到range级别。

    1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    6. index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    7. unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    9. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
    10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    11. all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    Extra:该列包含MySQL解决查询的详细信息。

    1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    7. Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    8. Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
    9. Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    单列索引的使用

    准备工作

    给id加主键索引:

    再分别给name、city、country、address加上普通索引:

    注:以上五个索引都是单列索引。

    使用情况

    只涉及到其中的一个字段时,都能使用到索引(以e_name为例):

     

    注:模糊查询时,%如果在前面,那么不会使用索引。

    涉及到多个索引字段时,如果这些索引字段中,存在主键索引,那么只会使用该索引(即:MYSQL优化器会选出并先执行最“严”的索引):

    提示:possible_key中,只是SQL语句里涉及到的索引;key中才是实际上执行查询操作时使用到了的索引。

    涉及到多个索引字段时,如果这些索引字段中,不存在主键索引的话,那么就会使用该使用的索引(注:如果通过其中的部分索引就能准确定位的话,那么其余的索引就不再被使用):

     

    注:多个索引时,先使用哪个索引后使用哪个索引,是由MySQL的优化器经过一些列计算后作出的抉择

    当对索引字段进行 >, <>= <=not inbetween …… and ……函数(索引字段)like模糊查询%在字段前时不会使用该索引

    注:这里对e_age字段进行了 “<” ,所以实际查询时,并没有使用e_age的索引。

    提示:在实际使用时,如果涉及到多列,我们一般都不会将这些列一 一创建为单列索引,而是将这些列创建为组合索引


    组合索引的使用

    最左原则

           假设组合索引为:a,b,c的话;那么当SQL中对应有:aa,ba,b,c的时候,可称为完全满足最左原则;当SQL中查询条件对应只有a,c的时候,可称为部分满足最左原则;当SQL中没有a的时候,可称为不满足最左原则

    注:MySQL5.7开始,会自动优化,如:会把c,b,a优化为a,b,c使之完全遵循最左原则;会把c,a优化为a,c使之部
           分遵循最左原则
    。即:SQL语句中的对应条件的先后顺序无关。

    准备工作

    创建了组合索引:e_name,e_age,e_country,e_city

    使用情况

    完全满足最左原则

    注:与条件的先后无关(这是因为MYSQL5.7开始,对索引全排列有优化,会自动优化为按组合索引的顺序进行查询)
           即:下面这样的话,也是会完整的走组合索引的:

    部分满足最左原则

    注:此SQL中,只有e_name和e_country满足部分最左原则(e_name满足),所以到e_name字段时会走组合所以,但是
           只会走到e_name那里,到e_country时就不会使用组合索引了。

    不满足最左原则

    满足(部分满足)最左原则的字段里,有字段不满足“索引”自身的使用规范

    说明:如果SQL语句里的字段里,满足了最左原则,但是不满足“索引”自身的使用规范,那么组合索引走到这里之后,
               不会再往下走了。

    如图所示:由于e_age字段使用了“>”符号,不符合“索引”自身的使用规范,那么当“e_name”走完组合索引后,
                      走到“e_age”时,该字段及其后面的字段不会再走组合索引了。


    【补充】使用组合索引时,不遵循最左原则仍然会走索引的特殊种情况

    相关概念

    聚集索引与非聚集索引

           每个InnoDB表具有一个特殊的索引称为聚簇索引(也叫聚集索引,聚类索引,簇集索引)。如果表上定义有主键,该主键索引就是聚簇索引。如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

           表中的聚簇索引(clustered index )就是一级索引,除此之外,表上的其他非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。

    回表

            当二级索引无法直接查询到(SQL中select需要的所有)列的数据时,会通过二级索引查询到聚簇索引(即:一级索引)后,再根据(聚集索引)查询到(二级索引中无法提供)的数据,这种通过二级索引查询出一级索引,再通过一级索引查询(二级索引中无法提供的)数据的过程,就叫做回表。

    当无需回表时,不遵循最左原则也是会走组合索引

    如,现有表:

    id是主键,其余三个字段组成联合索引:

    当不需要进行回表时,即便我们的SQL不满足组合索引最左原则,也会走组合索引的,如:

            这里where后直接是gender时, 是不遵循组合索引的最左原则的,但是查询计划显示使用了索引的。这是因为: 对这张表进行select *,相当于进行select id,name,age,gender,其中,id是主键(一级索引),name、age、gender是组合索引(二级索引),这里查询时,能直接从索引中拿到想要查询的所有列的数据,是不需要回表查询的,所以这里哪怕sql写法上不遵循最左原则,但是仍然是会走索引的。

    如果这个时候,我们加一个普通的motto字段:

     

    使用相同的SQL进行查询,可看到:

     

            此时进行select *,相当于进行select id,name,age,gender,motto,其中motto字段是从索引(一级索引、二级索引)里面获取不到数据的,是肯定需要回表的。而查询条件又不遵循最左原则,所以不会走组合索引。

    注:其它情况下,只有(完全或部分)遵循了最左原则,才会走组合索引


    ^_^ 如有不当之处,欢迎指正

    ^_^ 参考链接
                  https://www.cnblogs.com/DreamDrive/p/7752960.html
                  https://www.cnblogs.com/tommy-huang/p/4317305.html
                  https://blog.csdn.net/linjpg/article/details/56054994
                  https://www.jb51.net/article/118371.html

                  https://www.csdn.net/gather_2a/MtTaMgwsNzY0OS1ibG9n.html

    ^_^ 如涉及侵权问题,请及时联系我

    ^_^ 本文已经被收录进《程序员成长笔记(二)》,笔者JustryDeng

    展开全文
  • 创建索引的优点和缺点

    千次阅读 2016-12-28 10:44:26
    为什么要创建索引呢? 这是因为,创建索引可以大大提高系统的性能。 第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。 ...

    为什么要创建索引呢?

    这是因为,创建索引可以大大提高系统的性能

    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。 

    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?

    这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点,但是,为表中的每一个列都增加索引,是非常不明智的。这是因为,增加索引也有许多不利的一个方面。

    第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。 

    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引,例如:在经常需要搜索的列上,可以加快搜索的速度;在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。 

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点

    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

    第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。 

    创建索引的方法和索引的特征

    创建索引的方法 
    创建索引有多种方法,这些方法包括直接创建索引的方法和间接创建索引的方法。直接创建索引,例如使用CREATE INDEX语句或者使用创建索引向导,间接创建索引,例如在表中定义主键约束或者唯一性键约束时,同时也创建了索引。虽然,这两种方法都可以创建索引,但是,它们创建索引的具体内容是有区别的。 
    使用CREATE INDEX语句或者使用创建索引向导来创建索引,这是最基本的索引创建方式,并且这种方法最具有柔性,可以定制创建出符合自己需要的索引。在使用这种方式创建索引时,可以使用许多选项,例如指定数据页的充满度、进行排序、整理统计信息等,这样可以优化索引。使用这种方法,可以指定索引的类型、唯一性和复合性,也就是说,既可以创建聚簇索引,也可以创建非聚簇索引,既可以在一个列上创建索引,也可以在两个或者两个以上的列上创建索引。 
    通过定义主键约束或者唯一性键约束,也可以间接创建索引。主键约束是一种保持数据完整性的逻辑,它限制表中的记录有相同的主键记录。在创建主键约束时,系统自动创建了一个唯一性的聚簇索引。虽然,在逻辑上,主键约束是一种重要的结构,但是,在物理结构上,与主键约束相对应的结构是唯一性的聚簇索引。换句话说,在物理实现上,不存在主键约束,而只存在唯一性的聚簇索引。同样,在创建唯一性键约束时,也同时创建了索引,这种索引则是唯一性的非聚簇索引。因此,当使用约束创建索引时,索引的类型和特征基本上都已经确定了,由用户定制的余地比较小。 
    当在表上定义主键或者唯一性键约束时,如果表中已经有了使用CREATE INDEX语句创建的标准索引时,那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说,主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。 
    索引的特征 
        索引有两个特征,即唯一性索引和复合索引。 
    唯一性索引保证在索引列中的全部数据是唯一的,不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束,那么当创建表或者修改表时,SQL Server自动创建一个唯一性索引。然而,如果必须保证唯一性,那么应该创建主键约束或者唯一性键约束,而不是创建一个唯一性索引。当创建唯一性索引时,应该认真考虑这些规则:当在表中创建主键约束或者唯一性键约束时,SQL Server自动创建一个唯一性索引;如果表中已经包含有数据,那么当创建索引时,SQL Server检查表中已有数据的冗余性;每当使用插入语句插入数据或者使用修改语句修改数据时,SQL Server检查数据的冗余性:如果有冗余值,那么SQL Server取消该语句的执行,并且返回一个错误消息;确保表中的每一行数据都有一个唯一值,这样可以确保每一个实体都可以唯一确认;只能在可以保证实体完整性的列上创建唯一性索引,例如,不能在人事表中的姓名列上创建唯一性索引,因为人们可以有相同的姓名。 
    复合索引就是一个索引创建在两个列或者多个列上。在搜索时,当两个或者多个列作为一个关键值时,最好在这些列上创建复合索引。当创建复合索引时,应该考虑这些规则:最多可以把16个列合并成一个单独的复合索引,构成复合索引的列的总长度不能超过900字节,也就是说复合列的长度不能太长;在复合索引中,所有的列必须来自同一个表中,不能跨表建立复合列;在复合索引中,列的排列顺序是非常重要的,因此要认真排列列的顺序,原则上,应该首先定义最唯一的列,例如在(COL1,COL2)上的索引与在(COL2,COL1)上的索引是不相同的,因为两个索引的列的顺序不同;为了使查询优化器使用复合索引,查询语句中的WHERE子句必须参考复合索引中第一个列;当表中有多个关键列时,复合索引是非常有用的;使用复合索引可以提高查询性能,减少在一个表中所创建的索引数量。 
    索引的类型 
    根据索引的顺序与数据表的物理顺序是否相同,可以把索引分成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引,另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。 
    聚簇索引的体系结构 
    索引的结构类似于树状结构,树的顶部称为叶级,树的其它部分称为非叶级,树的根部在非叶级中。同样,在聚簇索引中,聚簇索引的叶级和非叶级构成了一个树状结构,索引的最低级是叶级。在聚簇索引中,表中的数据所在的数据页是叶级,在叶级之上的索引页是非叶级,索引数据所在的索引页是非叶级。在聚簇索引中,数据值的顺序总是按照升序排列。 
    应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时,应该考虑这些因素:每一个表只能有一个聚簇索引,因为表中数据的物理顺序只能有一个;表中行的物理顺序和索引中行的物理顺序是相同的,在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护这个顺序;关键值的唯一性要么使用UNIQUE关键字明确维护,要么由一个内部的唯一标识符明确维护,这些唯一性标识符是系统自己使用的,用户不能访问;聚簇索引的平均大小大约是数据表的百分之五,但是,实际的聚簇索引的大小常常根据索引列的大小变化而变化;在索引的创建过程中,SQL Server临时使用当前数据库的磁盘空间,当创建聚簇索引时,需要1.2倍的表空间的大小,因此,一定要保证有足够的空间来创建聚簇索引。 
    当系统访问表中的数据时,首先确定在相应的列上是否存在有索引和该索引是否对要检索的数据有意义。如果索引存在并且该索引非常有意义,那么系统使用该索引访问表中的记录。系统从索引开始浏览到数据,索引浏览则从树状索引的根部开始。从根部开始,搜索值与每一个关键值相比较,确定搜索值是否大于或者等于关键值。这一步重复进行,直到碰上一个比搜索值大的关键值,或者该搜索值大于或者等于索引页上所有的关键值为止。 
    非聚簇索引的体系结构 
    非聚簇索引的结构也是树状结构,与聚簇索引的结构非常类似,但是也有明显的不同。 
    在非聚簇索引中,叶级仅包含关键值,而没有包含数据行。非聚簇索引表示行的逻辑顺序。    非聚簇索引有两种体系结构:一种体系结构是在没有聚簇索引的表上创建非聚簇索引,另一种体系结构是在有聚簇索引的表上创建非聚簇索引。 
    如果一个数据表中没有聚簇索引,那么这个数据表也称为数据堆。当非聚簇索引在数据堆的顶部创建时,系统使用索引页中的行标识符指向数据页中的记录。行标识符存储了数据所在位置的信息。数据堆是通过使用索引分配图(IAM)页来维护的。IAM页包含了数据堆所在簇的存储信息。在系统表sysindexes中,有一个指针指向了与数据堆相关的第一个IAM页。系统使用IAM页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页和在这些数据页中的记录没有任何的顺序并且也没有链接在一起。在这些数据页之间的唯一的连接是IAM中记录的顺序。当在数据堆上创建了非聚簇索引时,叶级中包含了指向数据页的行标识符。行标识符指定记录行的逻辑顺序,由文件ID、页号和行ID组成。这些行的标识符维持唯一性。非聚簇索引的叶级页的顺序不同于表中数据的物理顺序。这些关键值在叶级中以升序维持。 
    当非聚簇索引创建在有聚簇索引的表上的时候,系统使用索引页中的指向聚簇索引的聚簇键。聚簇键存储了数据的位置信息。如果某一个表有聚簇索引,那么非聚簇索引的叶级包含了映射到聚簇键的聚簇键值,而不是映射到物理的行标识符。当系统访问有非聚簇索引的表中数据时,并且这种非聚簇索引创建在聚簇索引上,那么它首先从非聚簇索引来找到指向聚簇索引的指针,然后通过使用聚簇索引来找到数据。 
        当需要以多种方式检索数据时,非聚簇索引是非常有用的。当创建非聚簇索引时,要考虑这些情况:在缺省情况下,所创建的索引是非聚簇索引;在每一个表上面,可以创建不多于249个非聚簇索引,而聚簇索引最多只能有一个。
    系统如何访问表中的数据 
    一般地,系统访问数据库中的数据,可以使用两种方法:表扫描和索引查找。第一种方法是表扫描,就是指系统将指针放置在该表的表头数据所在的数据页上,然后按照数据页的排列顺序,一页一页地从前向后扫描该表数据所占有的全部数据页,直至扫描完表中的全部记录。在扫描时,如果找到符合查询条件的记录,那么就将这条记录挑选出来。最后,将全部挑选出来符合查询语句条件的记录显示出来。第二种方法是使用索引查找。索引是一种树状结构,其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时,系统沿着索引的树状结构,根据索引中关键字和指针,找到符合查询条件的的记录。最后,将全部查找到的符合查询语句条件的记录显示出来。 
        在SQL Server中,当访问数据库中的数据时,由SQL Server确定该表中是否有索引存在。如果没有索引,那么SQL Server使用表扫描的方法访问数据库中的数据。查询处理器根据分布的统计信息生成该查询语句的优化执行规划,以提高访问数据的效率为目标,确定是使用表扫描还是使用索引。 
    索引的选项 
        在创建索引时,可以指定一些选项,通过使用这些选项,可以优化索引的性能。这些选项包括FILLFACTOR选项、PAD_INDEX选项和SORTED_DATA_REORG选项。 
    使用FILLFACTOR选项,可以优化插入语句和修改语句的性能。当某个索引页变满时,SQL Server必须花费时间分解该页,以便为新的记录行腾出空间。使用FILLFACTOR选项,就是在叶级索引页上分配一定百分比的自由空间,以便减少页的分解时间。当在有数据的表中创建索引时,可以使用FILLFACTOR选项指定每一个叶级索引节点的填充的百分比。缺省值是0,该数值等价于100。在创建索引的时候,内部索引节点总是留有了一定的空间,这个空间足够容纳一个或者两个表中的记录。在没有数据的表中,当创建索引的时候,不要使用该选项,因为这时该选项是没有实际意义的。另外,该选项的数值在创建时指定以后,不能动态地得到维护,因此,只应该在有数据的表中创建索引时才使用。 
    PAD_INDEX选项将FILLFACTOR选项的数值同样也用于内部的索引节点,使内部的索引节点的填充度与叶级索引的节点中的填充度相同。如果没有指定FILLFACTOR选项,那么单独指定PAD_INDEX选项是没有实际意义的,这是因为PAD_INDEX选项的取值是由FILLFACTOR选项的取值确定的。 
    当创建聚簇索引时,SORTED_DATA_REORG选项清除排序,因此可以减少建立聚簇索引所需要的时间。当在一个已经变成碎块的表上创建或者重建聚簇索引时,使用SORTED_DATA_REORG选项可以压缩数据页。当重新需要在索引上应用填充度时,也使用该选项。当使用SORTED_DATA_REORG选项时,应该考虑这些因素:SQL Server确认每一个关键值是否比前一个关键值高,如果都不高,那么不能创建索引;SQL Server要求1.2倍的表空间来物理地重新组织数据;使用SORTED_DATA_REORG选项,通过清除排序进程而加快索引创建进程;从表中物理地拷贝数据;当某一个行被删除时,其所占的空间可以重新利用;创建全部非聚簇索引;如果希望把叶级页填充到一定的百分比,可以同时使用FILLFACTOR选项和SORTED_DATA_REORG选项。 
    索引的维护 
    为了维护系统性能,索引在创建之后,由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块,因此,必须对索引进行维护。 
    使用DBCC SHOWCONTIG语句,可以显示表的数据和索引的碎块信息。当执行DBCC SHOWCONTIG语句时,SQL Server浏览叶级上的整个索引页,来确定表或者指定的索引是否严重碎块。DBCC SHOWCONTIG语句还能确定数据页和索引页是否已经满了。当对表进行大量的修改或者增加大量的数据之后,或者表的查询非常慢时,应该在这些表上执行DBCC SHOWCONTIG语句。当执行DBCC SHOWCONTIG语句时,应该考虑这些因素:当执行DBCC SHOWCONTIG语句时,SQL Server要求指定表的ID号或者索引的ID号,表的ID号或者索引的ID号可以从系统表sysindexes中得到;应该确定多长时间使用一次DBCC SHOWCONTIG语句,这个时间长度要根据表的活动情况来定,每天、每周或者每月都可以。 
    使用DBCC DBREINDEX语句重建表的一个或者多个索引。当希望重建索引和当表上有主键约束或者唯一性键约束时,执行DBCC DBREINDEX语句。除此之外,执行DBCC DBREINDEX语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。当使用执行DBCC DBREINDEX语句时,应该考虑这些因素:根据指定的填充度,系统重新填充每一个叶级页;使用DBCC DBREINDEX语句重建主键约束或者唯一性键约束的索引;使用SORTED_DATA_REORG选项可以更快地创建聚簇索引,如果没有排列关键值,那么不能使用DBCC DBREINDEX语句;DBCC DBREINDEX语句不支持系统表。另外,还可以使用数据库维护规划向导自动地进行重建索引的进程。
    统计信息是存储在SQL Server中的列数据的样本。这些数据一般地用于索引列,但是还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息,并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。当表中数据发生变化时,SQL Server周期性地自动修改统计信息。索引统计被自动地修改,索引中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如,如果表中有10000行数据,1000行数据修改了,那么统计信息可能需要修改。然而,如果只有50行记录修改了,那么仍然保持当前的统计信息。除了系统自动修改之外,用户还可以通过执行UPDATE STATISTICS语句或者sp_updatestats系统存储过程来手工修改统计信息。使用UPDATE STATISTICS语句既可以修改表中的全部索引,也可以修改指定的索引。 
    使用SHOWPLAN和STATISTICS IO语句可以分析索引和查询性能。使用这些语句可以更好地调整查询和索引。SHOWPLAN语句显示在连接表中使用的查询优化器的每一步以及表明使用哪一个索引访问数据。使用SHOWPLAN语句可以查看指定查询的查询规划。当使用SHOWPLAN语句时,应该考虑这些因素。SET SHOWPLAN_ALL语句返回的输出结果比SET SHOWPLAN_TEXT语句返回的输出结果详细。然而,应用程序必须能够处理SET SHOWPLAN_ALL语句返回的输出结果。SHOWPLAN语句生成的信息只能针对一个会话。如果重新连接SQL Server,那么必须重新执行SHOWPLAN语句。STATISTICS IO语句表明输入输出的数量,这些输入输出用来返回结果集和显示指定查询的逻辑的和物理的I/O的信息。可以使用这些信息来确定是否应该重写查询语句或者重新设计索引。使用STATISTICS IO语句可以查看用来处理指定查询的I/O信息。 
    就象SHOWPLAN语句一样,优化器隐藏也用来调整查询性能。优化器隐藏可以对查询性能提供较小的改进,并且如果索引策略发生了改变,那么这种优化器隐藏就毫无用处了。因此,限制使用优化器隐藏,这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏时,考虑这些规则:指定索引名称、当index_id为0时为使用表扫描、当index_id为1时为使用聚簇索引;优化器隐藏覆盖查询优化器,如果数据或者环境发生了变化,那么必须修改优化器隐藏。 
    索引调整向导 
    索引调整向导是一种工具,可以分析一系列数据库的查询语句,提供使用一系列数据库索引的建议,优化整个查询语句的性能。对于查询语句,需要指定下列内容: 
    查询语句,这是将要优化的工作量  
    包含了这些表的数据库,在这些表中,可以创建索引,提高查询性能  
    在分析中使用的表  
    在分析中,考虑的约束条件,例如索引可以使用的最大磁盘空间  
    这里指的工作量,可以来自两个方面:使用SQL Server捕捉的轨迹和包含了SQL语句的文件。索引调整向导总是基于一个已经定义好的工作量。如果一个工作量不能反映正常的操作,那么它建议使用的索引不是实际的工作量上性能最好的索引。索引调整向导调用查询分析器,使用所有可能的组合评定在这个工作量中每一个查询语句的性能。然后,建议在整个工作量上可以提高整个查询语句的性能的索引。如果没有供索引调整向导来分析的工作量,那么可以使用图解器立即创建它。一旦决定跟踪一条正常数据库活动的描述样本,向导能够分析这种工作量和推荐能够提高数据库工作性能的索引配置。 
    索引调整向导对工作量进行分析之后,可以查看到一系列的报告,还可以使该向导立即创建所建议的最佳索引,或者使这项工作成为一种可以调度的作业,或者生成一个包含创建这些索引的SQL语句的文件。 
    索引调整向导允许为SQL Server数据库选择和创建一种理想的索引组合和统计,而不要求对数据库结构、工作量或者SQL Server内部达到专家的理解程度。总之,索引调整向导能够作到以下几个方面的工作: 
    通过使用查询优化器来分析工作量中的查询任务,向有大量工作量的数据库推荐一种最佳的索引混合方式  
    分析按照建议作出改变之后的效果,包括索引的用法、表间查询的分布和大量工作中查询的工作效果  
    为少量查询任务推荐调整数据库的方法  
    通过设定高级选项如磁盘空间约束、最大的查询语句数量和每个索引的最多列的数量等,允许定制推荐方式  
    图解器 
    图解器能够实时抓取在服务器中运行的连续图片,可以选取希望监测的项目和事件,包括Transact-SQL语句和批命令、对象的用法、锁定、安全事件和错误。图解器能够过滤这些事件,仅仅显示用户关心的问题。可以使用同一台服务器或者其他服务器重复已经记录的跟踪事件,重新执行那些已经作了记录的命令。通过集中处理这些事件,就能够很容易监测和调试SQL Server中出现的问题。通过对特定事件的研究,监测和调试SQL Server问题变得简单多了。 
    查询处理器 
        查询处理器是一种可以完成许多工作的多用途的工具。在查询处理器中,可以交互式地输入和执行各种Transact-SQL语句,并且在一个窗口中可以同时查看Transact-SQL语句和其结果集;可以在查询处理器中同时执行多个Transact-SQL语句,也可以执行脚本文件中的部分语句;提供了一种图形化分析查询语句执行规划的方法,可以报告由查询处理器选择的数据检索方法,并且可以根据查询规划调整查询语句的执行,提出执行可以提高性能的优化索引建议,这种建议只是针对一条查询语句的索引建议,只能提高这一条查询语句的查询性能。 
    系统为每一个索引创建一个分布页,统计信息就是指存储在分布页上的某一个表中的一个或者多个索引的关键值的分布信息。当执行查询语句时,为了提高查询速度和性能,系统可以使用这些分布信息来确定使用表的哪一个索引。查询处理器就是依赖于这些分布的统计信息,来生成查询语句的执行规划。执行规划的优化程度依赖于这些分布统计信息的准确步骤的高低程度。如果这些分布的统计信息与索引的物理信息非常一致,那么查询处理器可以生成优化程度很高的执行规划。相反,如果这些统计信息与索引的实际存储的信息相差比较大,那么查询处理器生成的执行规划的优化程度则比较低。 
        查询处理器从统计信息中提取索引关键字的分布信息,除了用户可以手工执行UPDATE STATISTICS之外,查询处理器还可以自动收集统计这些分布信息。这样,就能够充分保证查询处理器使用最新的统计信息,保证执行规划具有很高的优化程度,减少了维护的需要。当然,使用查询处理器生成的执行规划,也有一些限制。例如,使用执行规划只能提高单个查询语句的性能,但是可能对整个系统的性能产生正面的或者付面的影响,因此,要想提高整个系统的查询性能,应该使用索引调整向导这样的工具。 
    结论 
    在以前的SQL Server版本中,在一个查询语句中,一个表上最多使用一个索引。而在SQL Server 7.0中,索引操作得到了增强。SQL Server现在使用索引插入和索引联合算法来实现在一个查询语句中的可以使用多个索引。共享的行标识符用于连接同一个表上的两个索引。如果某个表中有一个聚簇索引,因此有一个聚簇键,那么该表上的全部非聚簇索引的叶节点使用该聚簇键作为行定位器,而不是使用物理记录标识符。如果表中没有聚簇索引,那么非聚簇索引继续使用物理记录标识符指向数据页。在上面的两种情况中,行定位器是非常稳定的。当聚簇索引的叶节点分开时,由于行定位器是有效的,所以非聚簇索引不需要被修改。如果表中没有聚簇索引,那么页的分开就不会发生。而在以前的版本中,非聚簇索引使用物理记录标识符如页号和行号,作为行的定位器。例如,如果聚簇索引(数据页)发生分解时,许多记录行被移动到了一个新的数据页,因此有了多个新的物理记录标识符。那么,所有的非聚簇索引都必须使用这些新的物理记录标识符进行修改,这样就需要耗费大量的时间和资源。 
        索引调整向导无论对熟练用户还是新用户,都是一个很好的工具。熟练用户可以使用该向导创建一个基本的索引配置,然后在基本的索引配置上面进行调整和定制。新用户可以使用该向导快速地创建优化的索引
    展开全文
  • SQL CREATE INDEX 语句CREATE INDEX 语句用于在表中创建索引。在读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。索引您可以在表中创建索引,以便更加快速高效地查询数据。用户无法看到索引,它们...
  • SqlServer在视图上创建索引

    千次阅读 2015-03-22 10:47:36
    在视图上创建索引需要三个条件: 一、视图必须绑定到架构。 要做到这点,在 CREATE VIEW 语句中,必须加上 WITH SCHEMABINDING,如果是使用企业管理器,则在设计界面的空白处点击右键,属性,选中“绑定到架构”。...
  • sql2000 为给定表或视图创建索引

    千次阅读 2012-02-12 15:01:44
    Transact-SQL 参考 ...为给定表或视图创建索引。...只有表或视图的所有者...可以通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。 语法 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] IND
  • 关于对视图创建索引的一些问题

    千次阅读 2007-08-07 17:14:00
    今天晚上在网上找了一些关于对视图创建索引的文章,比较不错,发上来:第一篇 聚集索引与非聚集索引索引是在数据库表或者视图上创建的对象,目的是为了加快对表或视图的查询的速度按照存储方式分为:聚集与非聚集...
  • 创建高性能的索引

    千次阅读 2019-11-17 17:41:34
    创建一个包含两列的索引,和创建两个只包含一列的索引是大相同的。 索引的类型 B-Tree索引 可以使用B-Tree索引的查询类型: 全值匹配 匹配最左前缀 匹配列前缀 匹配范围值 精确匹配某一列并范围匹配另外一列 只...
  • Logstash 创建 es 索引

    千次阅读 2019-01-08 11:27:03
    Logstash的工作是从MySQL中读取数据,向ES中创建索引,这里需要提前创建mapping的模板文件以便logstash使用。 在logstach的config目录创建xc_course_template.json,内容如下: 下列内容对应的是 跟 es 的映射...
  • oracle索引创建

    千次阅读 2009-08-21 19:10:00
    author:skatetime :2009/08/21 为什么要创建索引呢?这是因为,创建索引可以大大提高系统的性能,他都有哪些优点: 第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 第二,可以大大加快 ...
  • T-Sql 索引创建

    千次阅读 2011-12-04 11:26:41
    为给定表或视图创建索引。 只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引,无论表中是否有数据。可以通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。 语法 CREATE ...
  • SQLServer之创建非聚集索引

    千次阅读 2018-09-17 16:49:37
    创建 UNIQUE 约束时,默认情况下将创建唯一非聚集索引,以便强制 UNIQUE 约束。 如果存在该表的聚集索引,则可以指定唯一聚集索引。 有关详细信息,请参阅 Unique Constraints and Check Constraints。 ...
  • 数据库索引怎么建,什么时候用到 索引优缺点:为主键外建where子句建立索引可以加速数据库查询,但是...)如果一张表的数据量很大而符合条件的结果又很少,那么索引会引起致命性的结果下降,每次查找一条特定的数据...
  • SQL Server 索引创建和使用

    千次阅读 2015-09-21 10:12:54
    什么是索引 拿汉语字典的目录页(索引)打比方:正如汉语字典中的汉字按页存放一样,SQL Server中的数据记录也是按页存放...同理,SQL Server允许用户在表中创建索引,指定按某列预先排序,从而大大提高查询速度。 •
  • SQLServer之创建唯一非聚集索引

    千次阅读 2018-09-19 16:16:16
    创建 PRIMARY KEY 约束时,如果存在该表的聚集索引且未指定唯一非聚集索引,则将自动对一列或多列创建唯一聚集索引。 主键列允许空值。 在创建 UNIQUE 约束时,默认情况下将创建唯一非聚集索引,以便强制 ...
  • mysql索引详解

    万次阅读 多人点赞 2021-07-07 21:40:09
    分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB:所有的表都...
  • SQL SERVER 索引(3)——聚集索引

    千次阅读 2018-11-08 14:43:02
    聚集索引基于数据行的键值在表内排序和存储这些数据行,对磁盘上实际数据重新组织以按指定的一列或多列值排序,聚集索引的顺序和数据表中数据存储的顺序是一样的。每个表只能有一个聚集索引,因为数据行本身只能按...
  • CREATE TABLE 语句用于创建数据库中的表。 SQL CREATE TABLE 语法 CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, .... ) 数据类型(data_type)规定了列可容纳何种数据类型。...
  • 为mysql数据库建立索引;mysql索引总结----mysql 索引类型以及创建;mysql_建立索引的优缺点
  • 索引创建及使用(sqlserver 2000)

    千次阅读 2009-05-10 00:57:00
    为给定表或视图创建索引。只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引,无论表中是否有数据。可以通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。语法CREATE [ ...
  • 索引

    千次阅读 2011-04-17 22:51:00
    <br />索引 在oracle中,索引是除表之外另一个重要的模式对象,索引主要用于提高表查询速度。索引与表一样,有独立的数据段存储,并且可以通过设置储存的参数,控制索引段的盘区分配方式。索引可以由用户...
  • 索引的应用场景(哪些情况需要,...5.Where条件里用到的字段不创建索引 6.单间/组合索引的选择问题,who?(在高并发下倾向创建组合索引) 7.查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度 ...
  • 索引创建及使用(sqlserver 2005)

    千次阅读 2009-11-10 13:47:00
    可在向表中填入数据前创建索引。可通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。 Transact-SQL 语法约定语法 Create Relational Index CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERE
  • 如果某列不能含有空值,同时该表没有主键,则一个唯一性索引列可以显示为PRI, 3、如果多列构成了一个唯一性复合索引,那么一个唯一性索引列可以显示为MUL。(因为虽然索引的多列组合是唯一的,比如ID+NAME是...
  • PostgreSQL索引相关

    千次阅读 2015-08-19 13:32:13
    索引的好处是加快对表中记录的查找或排序,但创建索引也是有代价的,比如增加了存储空间,如果某个表创建了很多索引,那么索引占用的空间有可能比数据表本身占用的空间还要大。另外,索引的创建也会对数据的插入与...
  • 以下情况适合创建索引 1、在经常需要搜索的列上,可以加快搜索的速度。 2、在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构。 3、在经常用于连接两张表的列上,这些列主要是一些外键,可以加快连接的...
  • 创建、删除、索引和更改表

    千次阅读 2015-12-08 21:27:41
    可利用create table、drop table 和alter table 语句创建表,然后,对它们进行删除,更改它们的结构。对于它们中的每一条语句,存在mysql专有的扩充,这些扩充使各语句更为有用。create index 和drop index 语句使您...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 63,124
精华内容 25,249
关键字:

下列不能用于创建索引的是