mysql创建索引的原则

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

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

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等 *** 作的字段，排序 *** 作会浪费很多时间。如果为其建立索引，可以有效地避免排序 *** 作。

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

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

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

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

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引

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

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

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

8 最左前缀匹配原则，非常重要的原则。

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

9 =和in可以乱序。

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

。

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

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

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

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

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

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

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

一、SQL创建索引的目的如下：

1、通过唯一性索引（unique）可确保数据的唯一性；

2、加快数据的检索速度；

3、加快表之间的连接；

4、减少分组和排序时间；

5、使用优化隐藏器提高系统性能。

二、创建SQL索引的语法：

CREATE[UNIQUE][CLUSTERED|]（索引类型）INDEX

扩展资料：

索引的类别介绍：

1、唯一索引：

唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。

2、主键索引：

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

Create Relational Index

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON <object> ( column [ ASC | DESC ] [ ,n ] )

[ INCLUDE ( column_name [ ,n ] ) ]

[ WHERE <filter_predicate> ]

[ WITH ( <relational_index_option> [ ,n ] ) ]

[ ON { partition_scheme_name ( column_name )

| filegroup_name

| default

}

]

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

参数

UNIQUE

为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。

无论 IGNORE_DUP_KEY 是否设置为 ON，数据库引擎都不允许为已包含重复值的列创建唯一索引。否则，数据库引擎会显示错误消息。必须先删除重复值，然后才能为一列或多列创建唯一索引。唯一索引中使用的列应设置为 NOT NULL，因为在创建唯一索引时，会将多个 Null 值视为重复值。

CLUSTERED

创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。聚集索引的底层（或称叶级别）包含该表的实际数据行。一个表或视图只允许同时有一个聚集索引。

具有唯一聚集索引的视图称为索引视图。为一个视图创建唯一聚集索引会在物理上具体化该视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其他索引。

在创建任何非聚集索引之前创建聚集索引。创建聚集索引时会重新生成表中现有的非聚集索引。

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

注意：

因为按照定义，聚集索引的叶级别与其数据页相同，所以创建聚集索引和使用 ON partition_scheme_name 或 ON filegroup_name 子句实际上会将表从创建该表时所在的文件组移到新的分区方案或文件组中。对特定的文件组创建表或索引之前，应确认哪些文件组可用并且有足够的空间供索引使用。

NONCLUSTERED

创建一个指定表的逻辑排序的索引。对于非聚集索引，数据行的物理排序独立于索引排序。

无论是使用 PRIMARY KEY 和 UNIQUE 约束隐式创建索引，还是使用 CREATE INDEX 显式创建索引。每个表都最多可包含 999 个非聚集索引。

对于索引视图，只能为已定义唯一聚集索引的视图创建非聚集索引。

默认值为 NONCLUSTERED。

index_name

索引的名称。索引名称在表或视图中必须唯一，但在数据库中不必唯一。索引名称必须符合标识符的规则。

column

索引所基于的一列或多列。指定两个或多个列名，可为指定列的组合值创建组合索引。在 table_or_view_name 后的括号中，按排序优先级列出组合索引中要包括的列。

一个组合索引键中最多可组合 16 列。组合索引键中的所有列必须在同一个表或视图中。组合索引值允许的最大大小为 900 字节。

不能将大型对象 (LOB) 数据类型 ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml 或 image 的列指定为索引的键列。另外，即使 CREATE INDEX 语句中并未引用 ntext、text 或 image 列，视图定义中也不能包含这些列。

如果 CLR 用户定义类型支持二进制排序，则可以为该类型的列创建索引。另外，对于已定义为用户定义类型列的方法调用的计算列，只要这些方法标记为确定性方法且不执行数据访问 *** 作，便可为该计算列创建索引。

[ ASC | DESC ]

确定特定索引列的升序或降序排序方向。默认值为 ASC。

INCLUDE ( column [ ,n ] )

指定要添加到非聚集索引的叶级别的非键列。非聚集索引可以唯一，也可以不唯一。

在 INCLUDE 列表中列名不能重复，且不能同时用于键列和非键列。

除 text、ntext 和 image 之外，允许所有数据类型。如果指定的任一非键列属于 varchar(max)、nvarchar(max) 或 varbinary(max) 数据类型，则必须脱机 (ONLINE = OFF) 创建或重新生成该索引。

精确或不精确的确定性计算列都可以是包含列。从 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型派生的计算列可以包含在非键列中，前提是允许将这些计算列数据类型作为包含列。

WHERE <filter_predicate>

通过指定索引中要包含哪些行来创建筛选索引。筛选索引必须是对表的非聚集索引。为筛选索引中的数据行创建筛选统计信息。

筛选谓词使用简单比较逻辑且不能引用计算列、UDT 列、空间数据类型列或 hierarchyID 数据类型列。比较运算符不允许使用 NULL 文本的比较。请改用 IS NULL 和 IS NOT NULL 运算符。

下面是 ProductionBillOfMaterials 表的筛选谓词的一些示例：

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

筛选索引不适用于 XML 索引和全文索引。对于 UNIQUE 索引，仅选定的行必须具有唯一的索引值。筛选索引不允许有 IGNORE_DUP_KEY 选项。

ON partition_scheme_name ( column_name )

指定分区方案，该方案定义要将分区索引的分区映射到的文件组。必须通过执行 CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME，使数据库中存在该分区方案。column_name 指定将作为分区索引的分区依据的列。该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配。column_name 不限于索引定义中的列。除了在对 UNIQUE 索引分区时，必须从用作唯一键的列中选择 column_name 外，还可以指定基表中的任何列。通过此限制，数据库引擎可验证单个分区中的键值唯一性。

注意：

在对非唯一的聚集索引进行分区时，如果尚未指定分区依据列，则默认情况下数据库引擎将在聚集索引键列表中添加分区依据列。在对非唯一的非聚集索引进行分区时，如果尚未指定分区依据列，则数据库引擎会添加分区依据列作为索引的非键（包含）列。

如果未指定 partition_scheme_name 或 filegroup 且该表已分区，则索引会与基础表使用相同分区依据列并被放入同一分区方案中。

有关将索引分区的详细信息，请参阅已分区索引的特殊指导原则。

ON filegroup_name

为指定文件组创建指定索引。如果未指定位置且表或视图尚未分区，则索引将与基础表或视图使用相同的文件组。该文件组必须已存在。

ON "default"

为默认文件组创建指定索引。

在此上下文中，“default”不是关键字。它是默认文件组的标识符，并且必须进行分隔（类似于 ON "default" 或 ON[default]）。如果指定了 "default"，则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。这是默认设置。

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

在创建聚集索引时，指定表的 FILESTREAM 数据的位置。FILESTREAM_ON 子句用于将 FILESTREAM 数据移动到不同的 FILESTREAM 文件组或分区方案。

filestream_filegroup_name 是 FILESTREAM 文件组的名称。该文件组必须包含一个使用 CREATE DATABASE 或 ALTER DATABASE 语句为该文件组定义的文件；否则，将引发错误。

如果表已分区，则必须包含 FILESTREAM_ON 子句并且必须指定 FILESTREAM 文件组的分区方案，且此分区方案需使用与该表分区方案相同的分区函数和分区列。否则将引发错误。

如果该表未分区，则无法对 FILESTREAM 列分区。该表的 FILESTREAM 数据必须存储在一个由 FILESTREAM_ON 子句指定的文件组中。

如果创建的是聚集索引且该表不包含 FILESTREAM 列，则可在 CREATE INDEX 语句中指定 FILESTREAM_ON NULL。

首先打开SQL2005 管理平台。连接好数据库。

打开目录：数据库-->系统数据库-->master-- >表-->系统表-->dbostudent-->索引。。这里我只是拿student来举例。你可以选择其他表方法一样的。右击索引选择新建索引。d出新建索引的窗口。

首先要填好索引的名称。可以选择聚集，非聚集，主XML三种索引类型。这里就选择非聚集。

还有添加列，不添加就建立不了的。我们就添加《学号》这一列吧。选择确定

我们刷新一下目录，索引 kk 已经建立好了。ok

第二种方法是通过代码来实现：

首先单击新建查询进入编写代码的窗口。

编写下面的代码：

create nonclustered index kk on student(姓名)

语句 nonclustered index：就是索引的类型为非聚集索引。

kk：索引的名称。

on student：在表student里。

括号里的：姓名是字段的意思。

编译执行代码。

刷新查看索引，已经建立好了。

1、数据库索引是什么，有什么用

数据库索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

2、数据库索引的用法

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O *** 作；

第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识值的数据页的逻辑指针清单。

扩展资料：

一、索引的原理：

对要查询的字段建立索引其实就是把该字段按照一定的方式排序；建立的索引只对该字段有用，如果查询的字段改变，那么这个索引也就无效了，比如图书馆的书是按照书名的第一个字母排序的，那么你想要找作者叫张三的就不能用改索引了；还有就是如果索引太多会降低查询的速度。

二、数据库索引的特点：

1、避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据。

2、聚集索引可以避免数据插入 *** 作，集中于表的最后一个数据页面。

3、在某些情况下，索引可以避免排序 *** 作。

参考资料：

语法：

CREATE [索引类型] INDEX 索引名称

ON 表名(列名)

WITH FILLFACTOR = 填充因子值0~100

GO

/实例/

USE 库名

GO

IF EXISTS (SELECT FROM SYSINDEXES WHERE NAME='IX_TEST_TNAME')--检测是否已经存在IX_TEST_TNAME索引

DROP INDEX TESTIX_TEST_TNAME--如果存在则删除

--创建索引

CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引

ON TEST(TNAME) --为TEST表的TNAME字段创建索引

WITH FILLFACTOR = 30 --填充因子为30%

GO

附录：

1什么是索引：数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

2分类：

唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）

主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）

聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引

非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引

3创建索引的标准：用语频繁搜索的列；用语对数据进行排序的列

以上就是关于mysql创建索引的原则全部的内容，包括:mysql创建索引的原则、SQL创建索引的目的是什么(sql建立索引有什么用)、索引怎么建立使用等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！