SQL中:索引的建立和删除

目录 实验过程创建索引修改索引删除索引查询索引查看索引信息分析索引待续、更新中

实验过程

1 在STUDENT表的sno列上创建一个非聚簇索引，索引名为“student_sno_idx”。

CREATE INDEX student_sno_idx ON STUDENT (sno);

2.在STUDENT表上按sno的升序，sname的降序创建一个复合索引，索引名为“student_sno_sname_idx”。

Create clustered index student_sno_sname_idx on STUDENT (sno ASC,sname DESC )

3.把STUDENT表上的索引student_sno_sname_idx更名为“IX_sno_sname”。

EXEC sp_rename 'student.student_sno_sname_idx', 'IX_sno_sname', 'INDEX'; -- sql server的重命名命令

4.在COURSE表的cno列上创建唯一聚簇索引，索引名为“course_cno_idx”。

-- 手动删除主键聚集索引 CREATE UNIQUE CLUSTERED INDEX course_cno_idx ON COURSE(cno);

5.使用存储过程sp_helpindex查看STUDENT表中的索引信息。 EXEC sp_helpindex ‘STUDENT’;

6.删除STUDENT表中的索引IX_sname。

7.使用存储过程sp_rename将索引student_sno_idx改名为“student_sno”。

use suoyin EXEC sp_rename 'student.student_sno_idx', 'student_sno', 'INDEX';

六、分析索引 1.指明引用索引 SELECT 目标列表达式 FROM 表名 WITH (INDEX (索引名)) WHERE 查询条件 这个 SQL 命令语法用于指定在查询过程中使用特定的索引。 索引名：要在查询中使用的索引的名称。 WITH (INDEX (索引名)) : 用来指定在执行查询时使用特定的索引。这可以用于强制查询优化器使用特定的索引，而不是根据统计信息和查询条件自行选择索引。 使用这个语法时，需要确保指定的索引名是存在的. 在某些情况下，强制使用特定的索引可能会提高查询性能，但在其他情况下可能会降低性能。因此，在使用这个语法时，需要谨慎评估是否真正需要强制使用特定的索引。

如: select sage from student with(index (IX_sage)) where sage>18;

2.使用SHOWPLAN_ALL分析索引 设置是否显示查询计划的命令为： SET SHOWPLAN_ALL ON|OFF 或 SET SHOWPLAN_TEXT ON|OFF 必须要有相应的权限

SET SHOWPLAN_ALL ON|OFF命令 与SET SHOWPLAN_TEXT ON|OFF命令 ,是返回有关语句执行情况的详细信息，并估计语句对资源的需求。 而错误提示: SET SHOWPLAN 语句必须是批处理中仅有的语句。

解决:

加三个go ,批处理开头以go开始 go SET showplan_all on go SELECT * FROM student go SET showplan_all OFF

8.使用IX_sno_sname查询名叫“刘云云”的学生的信息。

select * from student with (index(IX_sno_sname)) where sname ='刘云云'

9.如果不使用索引IX_sno_sname，此次查询如何完成？

select * from student where sname ='刘云云' 创建索引

语法:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX <索引名> ON <表名> (<列名> [ASC|DESC] [,…n] )

例如: 在STUDENT表上按sno的升序，sname的降序创建一个复合索引，索引名为“student_sno_sname_idx

Create index student_sno_sname_idx on STUDENT (sno ASC,cno DESC ) -- CLUSTERED：可选，表示指定创建的索引为聚集索引 -- NONCLUSTERED：可选，表示指定创建的索引为非聚集索引

作用:

不必全盘扫描全部表内容 类似指针,书页目录

创建索引条件(经验)

创建查询的l列: 主码/外码/经常使用的列 创建索引后会有索引码 / 指针,也会占用物理磁盘空间,需要合理创建索引

分类: 聚簇索引

创建该索引后, 表中数据行的物理存储顺序与索引顺序完全相同, 表中有主码时会自动创建聚簇索引 只能有一个主码,一个聚簇索引

非聚簇索引

不改变表中数据行的物理存储顺序，即表中的数据行不按索引码的次序存储。在非聚簇索引中，每个索引都有指针指向包含该键值的数据行 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

DBMS向导创建索引

命令创建索引

在COURSE表的cno列上创建唯一聚簇索引，索引名为“course_cno_idx” CREATE UNIQUE CLUSTERED INDEX course_cno_idx ON COURSE(cno); -- 必须先删除自动为主键创建的聚集索引, 且也要删除与之关联的外键表的约束 修改索引

例如:

-- sql server的重命名索引命令 --语法 -- EXEC sp_rename 'table_name.old_index_name', 'new_index_name', 'INDEX'; EXEC sp_rename 'student.student_sno_sname_idx', 'IX_sno_sname', 'INDEX'; --oracle ALTER INDEX old_index_name RENAME TO new_index_name; 删除索引

例如: 删除STUDENT表中的索引IX_sname

DROP INDEX IX_sname ON student ; 查询索引

查询过程中使用特定的索引 语法: SELECT 目标列表达式 FROM 表名 WITH (INDEX (索引名)) WHERE 查询条件;

WITH (INDEX (索引名)) : 用来指定在执行查询时使用特定的索引。

例如:

select sage from student with(index (IX_sage)) where sage>18;

结果:

查看索引信息 EXEC sp_helpindex 'STUDENT';

分析索引

SHOWPLAN_ALL 分析索引

设置是否显示查询计划的命令为： SET SHOWPLAN_ALL ON|OFF 或 SET SHOWPLAN_TEXT ON|OFF 如何才能检测查询使用了哪个索引呢？SQL Server提供了多种分析索引和查询性能的方法，其中最常用的是SHOWPLAN_ALL方法。 显示查询计划就是SQL Server将显示在执行查询的过程中连接表时采取的每个步骤，以及是否选择及选择了哪个索引，从而帮助我们分析有哪些索引被系统采用。 通过在查询语句中设置SHOWPLAN_ALL选项，可以选择是否让SQL Server显示查询计划。 待续、更新中

    ————————————————————— 以上就是今日博客的全部内容了 创作不易,若对您有帮助,可否点赞、关注一二呢,感谢支持.