mysql 非聚集索引_MySQL数据库SQL优化技巧五之非聚集索引

#MYSQL#本篇主要介绍的是数据库SQL优化中索引的一种叫做非族索引的具体用法和创建。和最基本的索引的创建语法。

索弓|的创建与销毁在SQL中，创建索引是由CREATE INDEX关键字实现的。但在不同的数据库管理系统中，CREATE INDEX语句有不同形式的扩展。索引的销毁则是由DROP INDEX关键字实现的。

2.基本创建语法创建素引的基本关键字为CREATE INDEX，在其后要指明创建索引的名称，并需要指明表的名字及创建索引的列。语法如下CREATE INDEX index_ name ON table_ name (column_ name1，[column. name2]

每个索引必须有唯一的名字。ON关键字后面为创建索引的表的名字，在括号内列出索引包含的列(当然，可以为多列)。在不同的数据库管理系统中，创建索引语句有着不同形式的扩展。如在SQL Server中，

CREATE INDEX语句创建索引可以有如下的形式。用到的几个主要关键字含义如下。

UNIQUE ( DISTINCT):唯一性索引，不允许表中不同的行在索引列，上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。

CLUSTERED/ NONCLUSTERED:聚集和，若为CLUSTERED,则为聚集索引，即表中元组按索引项的值排序，并聚集在一起。一个基本表上只能建一个聚集索引。NONCLUSTERED表示创建的索引为非聚集索引。

ASC/DESC: 索引表中索引值的排序次序，缺省为ASC (正序排列)。关于其他关键字和语句的含义，读者可参阅各数据库系统的参考手册。所有这些创建形式有一些共同点，即都包含了基本的创建语句。

所有这些创建形式有一些共同点，即都包含了基本的创建语句： .CREATE INDEX index_ name ON table_ name (column_ name)本篇介绍的索引操作就是围绕这个基本创建语句展开的。

学生表

3.创建简单的非簇索引当一个表的记录数很大时，为查询符合条件的记录，扫描整个表要花费很长时间。如在StudentInfo表中查询学生”xx”的记录，查询语句如下。SELECT * FROM StudentInfo WHERE sname=’xx’，

假如StudentInfo表中有100000条记录，为查询“xx”学生的记录，我们就需要用WHERE条件对100 000条记录逐一进行核对， 显然效率低下，而此时如果对sname字段建立了索引，该索引如同对sname字段内的所有记录进行了某种排序，通过分析姓名“xx”，很快就会定位到它在表中的记录位置，从而提高了检索效率。

下面我们通过-一个实例介绍一下 非簇索引的创建和使用过程。该实例将为StudentInfo 表中的姓名(sname) 字段创建非簇索引Name_ Index.语法如下CREATE INDEX Name_ Index ON StudentInfo (sname )

4.非簇索引的使用由于大多数数据库系统具有使用多个索引的能力，如SQL Server,当在表上创建一个或多个索引后，SQLServer的查询优化器会自动决定在查询执行期间使用哪个索引。为了在SQL Server中使用索引，WHERE子句中的列之一必 须是索引所在列，对于本例来说就是sname列。当然也可以不让SQL Server的查询优化器自动决定索引，而是强制使用某种索引，语法如下。

SELECT columnl, column2, FROM table_ name WITH (INDEX (index_ name) )WHERE condi tion

ORDER BY关键词在每次查询数据时，都要对数据进行排序：而创建索引后，数据库系统实际上创建了一个索引结构体，用户每次使用查询数据时，都使用相同的索引结构，从而节约了时间。当数据库表被删除时，和它相关的所有索引都将被删除。

字段非簇索引的创建SQL允许用户在一个表中，在两个或多个字段上创建多字段索引，这种索引又被称为复合索引。有时，建立这类索引在实际应用中也是必要的。

例如，在学生选课表中，我们经常要查询某某同学(如张三)选修的某门课程(如数学)的成绩。这时，如果只在一个字段上建立索引，则查询效率要低些。比如，只在学生姓名字段上建立索引，执行查询时，系统将利用索引找出张三同学的所有选课记录，然后再对课程逐一扫描，找到课程为数学的记录：而如果只在课程字段上建立索引，执行查询时，系统将利用索引找出所有选修数学课程的学生记录，再对这些记录的姓名字段进行逐-扫描，找到姓名为张三的记录。这时，为了提高查询效率，我们可以同时为姓名和课程两个字段建立-一个索引。

为StudentInfo表中的性别(sex) 和姓名(sname) 字段创建索引SexName_ Index. CREATE INDEX SexName_ Index ON StudentInfo (sex ，sname )强制使用SexName_ Index 索引，查询StudentInfo表中的所有记录。SELECT FROM StudentInfo WITH (INDEX (SexName_ Index) )

我们可以发现，在创建的索引中，sex字段的优先级要高于sname字段。在创建多字段索引时，各字段的排列顺序决定了其优先级，排列越靠前，具有越高的优先级。