页首Html代码
1.创建表并插入数据
在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下：
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
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 (6, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
执行完上述sql代码以后我们会发现在Test数据库中多出了一张emp_pay表，数据库表的内容如下图所示：
2.无索引查找
从上图我们可以看出数据库中存储的数据排列顺序与我们插入的先后顺序一致。接下来我们查询employeeID=5的字段，执行如下sql代码：
SELECT * FROM emp_pay where employeeID=5
在SQL SERVER MANAGEMENT STUDIO中我们点击&显示估计的查询计划&，会出现如下图所示的查询计划图：
其中表扫描的内容为：
3.创建索引
接下来我们为上述表添加聚集唯一索引，代码如下：
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
在执行完上述创建索引的代码以后，我们再次查询emp_pay的数据内容，如下图所示：
从上图我们可以发现数据内容已经按照employeeID进行了排序。
我们继续执行前面关于employeeID=5的查询，点击&显示估计的执行计划&，出现如下图所示内容：
聚集索引查找的内容为：
当我们为数据库表中的某一个字段创建索引，并且在查询语句中where子句中用到这样一个字段，那么查询效率会有所提高，我们上述实验因为数据量的关系查询效率提高不明显。
我们上面添加的索引是唯一聚集索引，因此当插入的数据在employeeID字段出现重复时会报错。假如我们在创建索引之前数据字段出现重复，那么就不能创建唯一索引。
创建索引以后的排序（PS:）
执行如下sql语句
update emp_pay set employeeID=7 where employeeID=1;
然后再次执行全表查询，我们发现查询结果如下所示：
只要我们更新了employeeID，那么最后的更新结果都会按照employeeID的值进行升序排序。这是因为我们在employeeID上创建了索引的缘故。
删除索引（PS:）
我们可以通过sql server management studio这个工具删除索引，也可以通过sql语句进行索引的删除，假设我们要求删除在前面创建的索引employeeID_ind，那么sql语句如下代码所示：
DROP INDEX employeeID_ind ON emp_
阅读(...) 评论()
页脚Html代码MySQL 如何创建索引及优化_数据库技术_Linux公社-Linux系统门户网站
你好，游客
MySQL 如何创建索引及优化
来源：Linux社区&
作者：茶轴的青春
索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。
一、导致SQL执行慢的原因
1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2.没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）
3.数据过多（分库分表）
4.服务器调优及各个参数设置（调整my.cnf）
二、分析原因时，一定要找切入点
1.先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢SQL），在生产环境跑上个一天过后，看看哪些SQL比较慢。
2.Explain和慢SQL分析。比如SQL语句写的烂，索引没有或失效，关联查询太多（有时候是设计缺陷或者不得以的需求）等等。
3.Show Profile是比Explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。
4.找DBA或者运维对MySQL进行服务器的参数调优。
三、什么是索引？
MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree索引和Hash索引。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。索引如图所示：
最外层浅蓝色磁盘块1里有数据17、35（深蓝色）和指针P1、P2、P3（黄色）。P1指针表示小于17的磁盘块，P2是在17-35之间，P3指向大于35的磁盘块。真实数据存在于子叶节点也就是最底下的一层3、5、9、10、13&&非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35。
查找过程：例如搜索28数据项，首先加载磁盘块1到内存中，发生一次I/O，用二分查找确定在P2指针。接着发现28在26和30之间，通过P2指针的地址加载磁盘块3到内存，发生第二次I/O。用同样的方式找到磁盘块8，发生第三次I/O。
真实的情况是，上面3层的B+Tree可以表示上百万的数据，上百万的数据只发生了三次I/O而不是上百万次I/O，时间提升是巨大的。
四、Explain 分析
前文铺垫完成，进入实操部分，先来插入测试需要的数据：
CREATE&TABLE&`user_info`&(
& `id`& &BIGINT(20)& NOT&NULL&AUTO_INCREMENT,
& `name`&VARCHAR(50)&NOT&NULL&DEFAULT&'',
& `age`& INT(11)& & & & & & & DEFAULT&NULL,
& PRIMARY KEY&(`id`),
& KEY&`name_index`&(`name`)
)ENGINE&=&InnoDB&DEFAULT&CHARSET&=&utf8;
INSERT INTO user_info&(name,&age)&VALUES&('xys',&20);
INSERT INTO user_info&(name,&age)&VALUES&('a',&21);
INSERT INTO user_info&(name,&age)&VALUES&('b',&23);
INSERT INTO user_info&(name,&age)&VALUES&('c',&50);
INSERT INTO user_info&(name,&age)&VALUES&('d',&15);
INSERT INTO user_info&(name,&age)&VALUES&('e',&20);
INSERT INTO user_info&(name,&age)&VALUES&('f',&21);
INSERT INTO user_info&(name,&age)&VALUES&('g',&23);
INSERT INTO user_info&(name,&age)&VALUES&('h',&50);
INSERT INTO user_info&(name,&age)&VALUES&('i',&15);
CREATE&TABLE&`order_info`&(
& `id`& & & & & &BIGINT(20)& NOT&NULL&AUTO_INCREMENT,
& `user_id`& & & BIGINT(20)& & & & & &DEFAULT&NULL,
& `product_name`&VARCHAR(50)&NOT&NULL&DEFAULT&'',
& `productor`& & VARCHAR(30)& & & & & DEFAULT&NULL,
& PRIMARY KEY&(`id`),
& KEY&`user_product_detail_index`&(`user_id`,&`product_name`,&`productor`)
)ENGINE&=&InnoDB&DEFAULT&CHARSET&=&utf8;
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(1,&'p1',&'WHH');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(1,&'p2',&'WL');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(1,&'p1',&'DX');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(2,&'p1',&'WHH');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(2,&'p5',&'WL');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(3,&'p3',&'MA');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(4,&'p1',&'WHH');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(6,&'p1',&'WHH');
INSERT INTO order_info&(user_id,&product_name,&productor)&VALUES&(9,&'p8',&'TE');
初体验，执行Explain的效果：
索引使用情况在possible_keys、key和key_len三列，接下来我们先从左到右依次讲解。
--id相同,执行顺序由上而下
explain&select&u.*,o.*&from&user_info&u,order_info&o&where&u.id=o.user_
--id不同,值越大越先被执行
explain select *&from& user_info& where&id=(select user_id from order_info where& product_name&='p8');
2.select_type
可以看id的执行实例，总共有以下几种类型：
SIMPLE：&表示此查询不包含 UNION 查询或子查询
PRIMARY：&表示此查询是最外层的查询
SUBQUERY：&子查询中的第一个 SELECT
UNION：&表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION：&UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）
table表示查询涉及的表或衍生的表：
explain select tt.* from (select u.* from user_info u,order_info o where u.id=o.user_id and u.id=1) tt
id为1的&derived2&的表示id为2的u和o表衍生出来的。
type 字段比较重要，它提供了判断查询是否高效的重要依据依据。 通过 type 字段，我们判断此次查询是 全表扫描 还是 索引扫描等。
type 常用的取值有:
system: 表中只有一条数据，&这个类型是特殊的 const 类型。
const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快， 因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的：explain select * from user_info where id = 2；
eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user_info, order_info where user_info.id = order_info.user_
ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, &&, &, &=, &, &=, IS NULL, &=&, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info &where id between 2 and 8；
index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说， 我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询， 那么一般来说可以对相应的字段添加索引来避免。
通常来说, 不同的 type 类型的性能关系如下:
ALL & index & range ~ index_merge & ref & eq_ref & const & system
ALL 类型因为是全表扫描， 因此在相同的查询条件下，它是速度最慢的。而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引，因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据，因此可以过滤部分或大部分数据，因此查询效率就比较高了。
5.possible_keys
它表示 mysql 在查询时，可能使用到的索引。 注意，即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 mysql 使用到。&mysql 在查询时具体使用了哪些索引，由 key 字段决定。
此字段是 mysql 在当前查询时所真正使用到的索引。比如请客吃饭,possible_keys是应到多少人，key是实到多少人。当我们没有建立索引时：
explain&select&o.*&from&order_info&o&where& o.product_name=&'p1'&and& o.productor='whh';
create index idx_name_productor on order_info(productor);
drop index idx_name_productor on&order_
建立复合索引后再查询：
表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。
这个表示显示索引的哪一列被使用了，如果可能的话,是一个常量。前文的type属性里也有ref，注意区别。
rows 也是一个重要的字段，mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好。可以对比key中的例子，一个没建立索引钱，rows是9，建立索引后，rows是4。
explain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:
using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。
using where ：表名使用了where过滤。
五、优化案例
explain select u.*,o.* from user_info u LEFT JOIN& order_info o on u.id=o.user_
执行结果，type有ALL，并且没有索引：
开始优化，在关联列上创建索引，明显看到type列的ALL变成ref，并且用到了索引，rows也从扫描9行变成了1行：
这里面一般有个规律是：左链接索引加在右表上面，右链接索引加在左表上面。
六、是否需要创建索引？
索引虽然能非常高效的提高查询速度，同时却会降低更新表的速度。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
我是个普通的程序猿，水平有限，文章难免有错误，欢迎牺牲自己宝贵时间的读者，就本文内容直抒己见，我的目的仅仅是希望对读者有所帮助。
Linux公社的RSS地址：
本文永久更新链接地址：
相关资讯 & & &
& (05月01日)
& (04月12日)
& (07/25/:03)
& (04月12日)
& (01月05日)
& (04/04/:02)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款sql怎么建立索引？_百度知道
sql怎么建立索引？
进入查询窗口后，输入下面的语句：CREATE INDEX mycolumn_index ON mytable (myclumn)这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。注意：在本书中你执行任何SQL语句，都会收到如下的信息：This command did not return data,and it did not return any rows这说明该语句执行成功了。索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性）如果你需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索引后，你可以用下面的SQL语句删除它：DROP INDEX mytable.mycolumn_index注意在DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表mytable的索引。要建立一个聚簇索引，可以使用关键字CLUSTERED。）记住一个表只能有一个聚簇索引。（这里有一个如何对一个表建立聚簇索引的例子：CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)如果表中有重复的记录，当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索引；你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可：CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)WITH ALLOW_DUP_ROW这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子：CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。最后，要建立一个对多个字段的索引——复合索引——在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引：CREATE INDEX name_index ON username(firstname,lastname)这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对16个字段进行索引。用事务管理器建立索引用事务管理器建立索引比用SQL语句容易的多。使用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。使用事务管理器你可以用两种方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。要用Manage Tables 窗口建立一个新索引，单击按钮Advanced Options(它看起来象一个前面有一加号的表)。这样就打开了Advanced Options对话框。这个对话框有一部分标名为Primary Key（见图11.1）。图11。1要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后，索引会自动被建立。在Manage Tables窗口中的字段名旁边，会出现一把钥匙。你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索引强制一个字段成为唯一值字段。要建立没有这些限制的索引，你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes，打开Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。（见图11.2）。要建立一个新索引，从Index下拉框中选择New Index.，然后就可以选择要对之建立索引的字段。单击按钮Add，把字段加人到索引中。图11。2你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮Build，建立该索引。注意：唯一索引是指该字段不能有重复的值，而不是只能建立这一个索引。SQL核心语句在第十章，你学会了如何用SQL SELECT 语句从一个表中取数据。但是，到现在为止，还没有讨论如何添加，修改或删除表中的数据。在这一节中，你将学习这些内容。插入数据向表中添加一个新记录，你要使用SQL INSERT 语句。这里有一个如何使用这种语句的例子：INSERT mytable (mycolumn) VALUES (‘some data')这个语句把字符串'some data'插入表mytable的mycolumn字段中。将要被插入数据的字段的名字在第一个括号中指定，实际的数据在第二个括号中给出。INSERT 语句的完整句法如下：INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES |Values_list | select_statement}如果一个表有多个字段，通过把字段名和字段值用逗号隔开，你可以向所有的字段中插入数据。假设表mytable有三个字段first_column,second_column,和third_column。下面的INSERT语句添加了一条三个字段都有值的完整记录：INSERT mytable (first_column,second_column,third_column)VALUES (‘some data','some more data','yet more data')注意：你可以使用INSERT语句向文本型字段中插入数据。但是，如果你需要输入很长的字符串，你应该使用WRITETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。如果你在INSERT 语句中只指定两个字段和数据会怎么样呢？换句话说，你向一个表中插入一条新记录，但有一个字段没有提供数据。在这种情况下，有下面的四种可能：如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段third_column提供数据，而这个字段有一个缺省值'some value'。在这种情况下，当新记录建立时会插入值'some value'。如果该字段可以接受空值，而且没有缺省值，则会被插入空值。如果该字段不能接受空值，而且没有缺省值，就会出现错误。你会收到错误信息：The column in table mytable may not be null.最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标识字段的表中插入新记录时，只要忽略该字段，标识字段会给自己赋一个新值。注意：向一个有标识字段的表中插入新记录后，你可以用SQL变量@@identity来访问新记录的标识字段的值。考虑如下的SQL语句：INSERT mytable (first_column) VALUES(‘some value')INSERT anothertable(another_first,another_second)VALUES(@@identity,'some value')如果表mytable有一个标识字段，该字段的值会被插入表anothertable的another_first字段。这是因为变量@@identity总是保存最后一次插入标识字段的值。字段another_first应该与字段first_column有相同的数据类型。但是，字段another_first不能是应该标识字段。Another_first字段用来保存字段first_column的值。删除记录要从表中删除一个或多个记录，需要使用SQL DELETE语句。你可以给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。例如，下面的这个DELETE语句只删除字段first_column的值等于'Delete Me'的记录：DELETE mytable WHERE first_column='Deltet Me'DELETE 语句的完整句法如下：DELETE [FROM] {table_name|view_name} [WHERE clause]在SQL SELECT 语句中可以使用的任何条件都可以在DELECT 语句的WHERE子句中使用。例如，下面的这个DELETE语句只删除那些first_column字段的值为'goodbye'或 second_column字段的值为 'so long'的记录：DELETE mytable WHERE first_column='goodby' OR second_column='so long'如果你不给DELETE 语句提供WHERE 子句，表中的所有记录都将被删除。你不应该有这种想法。如果你想删除应该表中的所有记录，应使用第十章所讲的TRUNCATE TABLE语句。注意：为什么要用TRUNCATE TABLE 语句代替DELETE语句？当你使用TRUNCATE TABLE语句时，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE 要比DELETE快得多
采纳率：20%
为您推荐：
其他类似问题
您可能关注的内容
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。&&&&&&&&&&&
索引的简介：
索引分为聚集索引和非聚集索引，数据库中的索引类似于一本书的目录，在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。
索引主要目的是提高了SQL&Server系统的性能，加快数据的查询速度与减少系统的响应时间&。
但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用 WHERE 子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL&SERVER 除了要更新数据表本身，还要连带立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。
索引的分类：
索引就类似于中文字典前面的目录，按照拼音或部首都可以很快的定位到所要查找的字。
唯一索引（UNIQUE）：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）
主键索引：当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。
聚集索引（CLUSTERED）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。
非聚集索引（NONCLUSTERED）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。
PS：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
什么情况下使用索引：
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON &object& ( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH &backward_compatible_index_option& [ ,...n ] ]
[ ON { filegroup_name | "default" } ]
&object& ::=
[ database_name. [ owner_name ] . | owner_name. ]
table_or_view_name
&backward_compatible_index_option& ::=
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB
| IGNORE_DUP_KEY
| STATISTICS_NORECOMPUTE
| DROP_EXISTING
UNIQUE：为表或视图创建唯一索引。 唯一索引不允许两行具有相同的索引键值。 视图的聚集索引必须唯一。如果要建唯一索引的列有重复值，必须先删除重复值。
CLUSTERED：表示指定创建的索引为聚集索引。创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。 聚集索引的底层（或称叶级别）包含该表的实际数据行。
NONCLUSTERED：表示指定创建的索引为非聚集索引。创建一个指定表的逻辑排序的索引。 对于非聚集索引，数据行的物理排序独立于索引排序。
index_name：表示指定所创建的索引的名称。
database_name：表示指定的数据库的名称。
owner_name：表示指定所有者。
table：表示指定创建索引的表的名称。
view：表示指定创建索引的视图的名称。
column：索引所基于的一列或多列。 指定两个或多个列名，可为指定列的组合值创建组合索引。
[&ASC&| DESC]：表示指定特定索引列的升序或降序排序方向。 默认值为 ASC。
on filegroup_name：为指定文件组创建指定索引。 如果未指定位置且表或视图尚未分区，则索引将与基础表或视图使用相同的文件组。 该文件组必须已存在。
on default：为默认文件组创建指定索引。
PAD_INDEX = {ON |OFF&}：指定是否索引填充。默认为 OFF。
　　ON&通过指定的可用空间的百分比fillfactor应用于索引中间级别页。
　　OFF&或&fillfactor&未指定，考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。
　　PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。
FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor 的值为1到100。
SORT_IN_TEMPDB = {ON |OFF&}：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 默认为 OFF。
　　ON&用于生成索引的中间排序结果存储在tempdb。 这可能会降低仅当创建索引所需的时间tempdb位于不同的与用户数据库的磁盘集。&
　　OFF&中间排序结果与索引存储在同一数据库中。
IGNORE_DUP_KEY = {ON |OFF&}：指定在插入操作尝试向唯一索引插入重复键值时的错误响应。默认为 OFF。
　　ON&向唯一索引插入重复键值时将出现警告消息。 只有违反唯一性约束的行才会失败。
　　OFF&向唯一索引插入重复键值时将出现错误消息。 整个 INSERT 操作将被回滚。
STATISTICS_NORECOMPUTE = {ON |OFF}：用于指定过期的索引统计是否自动重新计算。 默认为 OFF。
　　ON&不会自动重新计算过时的统计信息。
　　OFF&启用统计信息自动更新功能。
DROP_EXISTING = {ON |OFF&}：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 OFF。
　　ON&指定要删除并重新生成现有索引，其必须具有相同名称作为参数&index_name。
　　OFF&指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在，SQL Server 将显示一个错误。
ONLINE = {ON |OFF}：表示建立索引时是否允许正常访问，即是否对表进行锁定。默认为 OFF。
　　ON&它将强制表对于一般的访问保持有效，并且不创建任何阻止用户使用索引和/表的锁。
　　OFF 对索引操作将对表进行表锁，以便对表进行完全和有效的访问。
创建唯一聚集索引：
-- 创建唯一聚集索引
create unique clustered
--表示创建唯一聚集索引
index UQ_Clu_StuNo
--索引名称
on Student(S_StuNo)
--数据表名称（建立索引的列名）
pad_index=on,
--表示使用填充
fillfactor=50,
--表示填充因子为50%
ignore_dup_key=on,
--表示向唯一索引插入重复值会忽略重复值
statistics_norecompute=off
--表示启用统计信息自动更新功能
创建唯一非聚集索引：
-- 创建唯一非聚集索引
create unique nonclustered
--表示创建唯一非聚集索引
index UQ_NonClu_StuNo
--索引名称
on Student(S_StuNo)
--数据表名称（建立索引的列名）
pad_index=on,
--表示使用填充
fillfactor=50,
--表示填充因子为50%
ignore_dup_key=on,
--表示向唯一索引插入重复值会忽略重复值
statistics_norecompute=off
--表示启用统计信息自动更新功能
--创建聚集索引
create clustered index Clu_Index
on Student(S_StuNo)
with (drop_existing=on)
--创建非聚集索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)
--创建唯一索引
create unique index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)
PS：当 create index 时，如果未指定 clustered 和 nonclustered，那么默认为 nonclustered。
创建非聚集复合索引：
--创建非聚集复合索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)
--创建非聚集复合索引，未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)
在 CREATE INDEX 语句中使用 INCLUDE 子句，可以在创建索引时定义包含的非键列（即覆盖索引），其语法结构如下：
CREATE NONCLUSTERED INDEX 索引名
ON { 表名| 视图名 } ( 列名 [ ASC | DESC ] [ ,...n ] )
INCLUDE (&列名1&, &列名2&, [,… n])
--创建非聚集覆盖索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)
--创建非聚集覆盖索引，未指定默认为非聚集索引
create index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)
PS：聚集索引不能创建包含非键列的索引。
创建筛选索引：
--创建非聚集筛选索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo &= 001 and S_StuNo &= 020
with(drop_existing=on)
--创建非聚集筛选索引，未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo &= 001 and S_StuNo &= 020
with(drop_existing=on)
修改索引：
--修改索引语法
ALTER INDEX { 索引名| ALL }
ON &表名|视图名&
| REORGANIZE }[ ; ]
REBUILD：表示指定重新生成索引。
DISABLE：表示指定将索引标记为已禁用。
REORGANIZE：表示指定将重新组织的索引叶级。
--禁用名为 NonClu_Index 的索引
alter index NonClu_Index on Student disable
删除和查看索引：
--查看指定表 Student 中的索引
exec sp_helpindex Student
--删除指定表 Student 中名为 Index_StuNo_SName 的索引
drop index Student.Index_StuNo_SName
--检查表 Student 中索引 UQ_S_StuNo 的碎片信息
dbcc showcontig(Student,UQ_S_StuNo)
--整理 Test 数据库中表 Student 的索引 UQ_S_StuNo 的碎片
dbcc indexdefrag(Test,Student,UQ_S_StuNo)
--更新表 Student 中的全部索引的统计信息
update statistics Student
索引定义原则：
避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。
在频繁进行排序或分组（即进行 GROUP BY 或 ORDER BY 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。
http://www.cnblogs.com/knowledgesea/p/3672099.html
https://msdn.microsoft.com/zh-cn/library/ms188783.aspx
阅读(...) 评论()