mysql如何建立索引

我们可以通过查看索引的属性来判断创建索引的方法。

查看索引的语法格式如下：

SHOW INDEX FROM <表名> [ FROM <数据库名>]

语法说明如下：

<表名>：指定需要查看索引的数据表名。

<数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

示例

使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息，SQL 语句和运行结果如下所示。

mysql> SHOW INDEX FROM tb_stu_info2\G

1 row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (003 sec)

其中各主要参数说明如下：

参数 说明

Table 表示创建索引的数据表名，这里是 tb_stu_info2 数据表。

Non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。

Key_name 表示索引的名称。

Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。

Column_name 表示定义索引的列字段。

Collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。

Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。

Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。

Packed 指示关键字如何被压缩。若没有被压缩，值为 NULL。

Null 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。

Index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 显示评注。

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

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

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

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

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

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

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

编写下面的代码：

create nonclustered index kk on student(姓名)

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

kk：索引的名称。

on student：在表student里。

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

编译执行代码。

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

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

MySQL索引类型包括：

(1)普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

◆创建索引

CREATE

INDEX

indexName

ON

mytable(username(length));

如果是

CHAR，VARCHAR类型，length可以小于字段实际长度;如果是BLOB和TEXT类型，必须指定

length，下同。

◆修改表结构

ALTER

mytable

ADD

INDEX

[indexName]

ON

(username(length))

◆

创建表的时候直接指定

CREATE

TABLE

mytable(

ID

INT

NOT

NULL,

username

VARCHAR(16)

NOT

NULL,

INDEX

[indexName]

(username(length))

);

删除索引的语法：

DROP

INDEX

[indexName]

ON

mytable;

(2)唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE

UNIQUE

INDEX

indexName

ON

mytable(username(length))

◆修改表结构

ALTER

mytable

ADD

UNIQUE

[indexName]

ON

(username(length))

◆创建表的时候直接指定

CREATE

TABLE

mytable(

ID

INT

NOT

NULL,

username

VARCHAR(16)

NOT

NULL,

UNIQUE

[indexName]

(username(length))

);

(3)主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE

TABLE

mytable(

ID

INT

NOT

NULL,

username

VARCHAR(16)

NOT

NULL,

PRIMARY

KEY(ID)

);

当然也可以用

ALTER

命令。记住：一个表只能有一个主键。

(4)组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE

TABLE

mytable(

ID

INT

NOT

NULL,

username

VARCHAR(16)

NOT

NULL,

city

VARCHAR(50)

NOT

NULL,

age

INT

NOT

NULL

);

图索引和表索引是数据库系统中用来加快数据查询和更新的数据结构。

图索引是一种特殊的索引，它通常用于存储图形数据，例如地图或者三维模型。图索引能够快速找到与给定几何图形相交的图形，或者在图形中找到符合特定条件的点。

表索引是指在数据库表中建立的索引。表索引能够加速数据查询和更新的速度，因为它可以快速找到表中的特定行。表索引通常是基于表中的某个字段或者一组字段建立的。

要创建图索引或表索引，你需要使用数据库管理系统（DBMS）或者数据库开发工具。具体的步骤取决于你使用的数据库类型和开发工具。

例如，在 MySQL 中，你可以使用以下语句来创建一个表索引：

CREATE INDEX index_name ON table_name (column_name);

在这个例子中，index_name 是你要创建的索引的名称，table_name 是你要在其中创建索引的表的名称，column_name 是你要建立索引的字段的名称。

对于图索引，具体的创建方式取决于你使用的数据库类

普通索引 添加INDEX

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

下面演示下给user表的name字段添加一个索引

主键索引 添加PRIMARY KEY

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

唯一索引 添加UNIQUE

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

全文索引 添加FULLTEXT

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

如何添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

五 索引分类

直接创建索引和间接创建索引

直接创建索引 CREATE INDEX mycolumn_index ON mytable (myclumn)

间接创建索引 定义主键约束或者唯一性键约束 可以间接创建索引

普通索引和唯一性索引

普通索引 CREATE INDEX mycolumn_index ON mytable (myclumn)

唯一性索引 保证在索引列中的全部数据是唯一的 对聚簇索引和非聚簇索引都可以使用

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

单个索引和复合索引

单个索引 即非复合索引

复合索引 又叫组合索引 在索引建立语句中同时包含多个字段名 最多 个字段

CREATE INDEX name_index ON username(firstname lastname)

聚簇索引和非聚簇索引(聚集索引 群集索引)

聚簇索引 物理索引 与基表的物理顺序相同 数据值的顺序总是按照顺序排列

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH

ALLOW_DUP_ROW(允许有重复记录的聚簇索引)

非聚簇索引 CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

六 索引的使用

当字段数据更新频率较低 查询使用频率较高并且存在大量重复值是建议使用聚簇索引

经常同时存取多列 且每列都含有重复值可考虑建立组合索引

复合索引的前导列一定好控制好 否则无法起到索引的效果 如果查询时前导列不在查询条件中则该复合索引不会被使用 前导列一定是使用最频繁的列

多表 *** 作在被实际执行前 查询优化器会根据连接条件 列出几组可能的连接方案并从中找出系统开销最小的最佳方案 连接条件要充份考虑带有索引的表 行数多的表;内外表的选择可由公式 外层表中的匹配行数内层表中每一次查找的次数确定 乘积最小为最佳方案

where子句中对列的任何 *** 作结果都是在sql运行时逐列计算得到的 因此它不得不进行表搜索 而没有使用该列上面的索引;如果这些结果在查询编译时就能得到 那么就可以被sql优化器优化 使用索引 避免表搜索(例 select from record where substring(card_no )=

&& select from record where card_no like % )任何对列的 *** 作都将导致表扫描 它包括数据库函数 计算表达式等等 查询时要尽可能将 *** 作移至等号右边

where条件中的 in 在逻辑上相当于 or 所以语法分析器会将in ( ′ ′)转化为column= ′ or column= ′来执行 我们期望它会根据每个or子句分别查找 再将结果相加 这样可以利用column上的索引;但实际上它却采用了 or策略 即先取出满足每个or子句的行 存入临时数据库的工作表中 再建立唯一索引以去掉重复行 最后从这个临时表中计算结果 因此 实际过程没有利用column上索引 并且完成时间还要受tempdb数据库性能的影响 in or子句常会使用工作表 使索引失效;如果不产生大量重复值 可以考虑把子句拆开;拆开的子句中应该包含索引

要善于使用存储过程 它使sql变得更加灵活和高效

lishixinzhi/Article/program/MySQL/201311/29603

什么是索引

拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4K 。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），我们可以选择按拼音或笔画查找方式，快速查找到需要的字（词）。

同理，SQL Server允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度。

• SQL Server中的数据也是按页（ 4KB ）存放

• 索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据。

• 索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。

• 索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

索引类型

• 唯一索引：唯一索引不允许两行具有相同的索引值

• 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

• 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

• 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

索引类型：再次用汉语字典打比方，希望大家能够明白聚集索引和非聚集索引这两个概念。

唯一索引：

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。例如，如果在stuInfo表中的学员员身份z号(stuID) 列上创建了唯一索引，则所有学员的身份z号不能重复。

提示：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束或唯一约束。

主键索引：

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

聚集索引（clustered index）

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

非聚集索引(Non-clustered)

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

如何创建索引

使用T-SQL语句创建索引的语法：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]

INDEX index_name

ON table_name (column_name…)

[WITH FILLFACTOR=x]

q UNIQUE表示唯一索引，可选

q CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选

q FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

在stuMarks表的writtenExam列创建索引：

USE stuDB

GO

IF EXISTS (SELECT name FROM sysindexes

WHERE name = 'IX_writtenExam')

DROP INDEX stuMarksIX_writtenExam

/--笔试列创建非聚集索引：填充因子为30％--/

CREATE NONCLUSTERED INDEX IX_writtenExam

ON stuMarks(writtenExam)

WITH FILLFACTOR= 30

GO

/-----指定按索引 IX_writtenExam 查询----/

SELECT FROM stuMarks (INDEX=IX_writtenExam)

WHERE writtenExam BETWEEN 60 AND 90

虽然我们可以指定SQL Server按哪个索引进行数据查询，但一般不需要我们人工指定。SQL Server将会根据我们创建的索引，自动优化查询。

索引的优缺点

• 优点

– 加快访问速度

– 加强行的唯一性

• 缺点

– 带索引的表在数据库中需要更多的存储空间

– *** 纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

创建索引的指导原则

• 请按照下列标准选择建立索引的列。

– 该列用于频繁搜索

– 该列用于对数据进行排序

• 请不要使用下面的列创建索引：

– 列中仅包含几个不同的值。

– 表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

以上就是关于mysql如何建立索引全部的内容，包括:mysql如何建立索引、数据库。写出建立索引的 *** 作步骤、mysql 状态类型字段怎么建索引等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！