索引介绍

你可以利用索引【index】来预防你的资料出现问题，尤其是表格存储非常大量的记录时，简历适当的索引，可以增加查询与资料维护的效率

以 MyISAM 存储引擎来说，表中存储的数据是存储在电脑中的一个档案

当你执行如下查询的时候

数据库要找到你需要查询或维护的纪录，如果没有索引帮助的话，就会从头开始一边读取，一边判断是否有符合条件的资料

你可以为表格建立索引来改善这种比较没有效率的方式

建立城市名称的索引档以后，同样执行下列的查询叙述，MySQL会自动使用索引来快速找到你需要的资料：

索引可以增加删除或修改不的效率

索引有三种

索引名字	说明	适用条件
主索引键(primary key)	一个表格通常会有一个，而且只能有一个。在一个表格中，设定为主索引键的字段值不可重复，而且不可以储存“NULL”值	编码、代号或身份证号
唯一索引(unique index)	也称不可重复索引，唯一索引的字段值不可重复，但是可以储存“NULL”值	可存储email，因为email可能不是所有人都有，但是又不可能重复
非唯一索引(non-unique index)	只是用来增加查询与维护资料效率的索引。设定为非唯一索引的字段值可以重复，也可以储存“NULL”值

建立索引

创建表格简历索引

在建立表格的叙述中，你会定义出许多表格所需要的字段，在字段的定义中，除了名称、型态与属性，还可以加入“唯一索引”与“主索引键”的定义

你可以使用这样的语法在“id”字段后面加入“PRIMARY KEY”，指定“id”字段为主索引键，这表示“id”字段的值不可以重复，而且不可以储存“NULL”值；另外在“email”字段加入“UNIQUE KEY”，指定“email”字段为唯一索引，这表示“email”字段的值不可以重复

第二种创建表格简历索引

同样是建立数据库表，下面两种建立索引语法的效果是一样的

如果你要建立一般索引(可以重复的索引)，或是要建立包含多个字段的索引时，就一定要把建立索引的定义加在所有字段定义后面：

在建立索引的时候，你可以指定某一个字段为建立索引的字段，不过有时候你只想要为一个字串型态字段的部份资料建立索引，或是指定建立的索引资料，是要依照由小到大，还是由大到小排列。有这样的需求时，你可以依照下列的语法来指定：

以建立联络簿的表格来说，为地址资料“address”字段建立索引的时候，如果你希望建立地址前五个字符的索引资料，而且依照由大到小的顺序。下列的叙述就可以建立这样的索引：

注：

只有“CHAR”、“VARCHAR”、“BINARY”与“VARBINARY”型态的字段可以指定制作索引的长度。“ASC”或“DESC”可以使用在任何型态的字段。

在修改表格的时候建立索引

如果你想要为一个已经存在的表格建立索引的话，你可以在修改表格“ALTER TABLE”中建立索引：

你可以使用“ALTER TABLE”叙述建立需要的索引，不过一个“ALTER TABLE”叙述只能建立一个索引：

使用“CREATE INDEX”建立索引

需要为一个已经存在的表格建立索引，除了使用“ALTER TABLE”叙述建立索引外，还可以使用“CREATE INDEX”叙述建立唯一索引与一般索引：

使用“CREATE INDEX”叙述只能建立唯一索引与一般索引，你还是要使用“ALTER TABLE”叙述建立主索引键：

为一个已经存在的表格建立索引时，要特别注意主索引键与唯一索引这两种索引。如果这个表格没有任何纪录资料的话，那就不会有问题；可是如果表格中已经有纪录了，而且你想要建立一个主索引键时，有可能会发生下列的错误：

也就是说一个表中值能有一个主键索引

为一个已经存在、而且已经有纪录的表格建立唯一索引时，也有可能会发生下列的错误：

索引的名称

在“CREATE TABLE”或是“ALTER TABLE”叙述中建立索引的话，你可以为建立的索引取一个名称：

如果你在使用上列的语法建立索引的时候没有指定索引名称，MySQL会帮你取一个，索引的名称就是字段名称，如果是多个字段的索引，就会使用第一个字段当作索引名称。

使用“CREATE INDEX”建立索引的时候，就一定要指定一个索引名称：

注：

在一般的操作中，你并不会用到索引名称；不过在删除索引的时候就会用到。

7 删除索引

如果一个已经建立好的索引已经不需要了，为了节省储存的空间，你可以使用下列的语法删除索引：

下列的叙述使用修改表格“ALTER TABLE”叙述删除不需要的索引：

使用“ALTER TABLE”叙述可以一次删除多个索引，“DROP INDEX”叙述一次只能删除一个索引：

数值字段型态与AUTO_INCREMENT

在数据库的应用中，很常会遇到为纪录“编流水号”的需求，如果资料表中的每一笔纪录都需要一个递增的数值编号，你可以选择整数型态的字段后，再使用“AUTO_INCREMENT”字段属性：

通俗点说就是设置数据自增

如果一个公司想要储存员工开会的资料，你可以在建立开会资料表格的时候，为这个表格定义一个储存开会编号的字段，这个字段需要自动递增，而且会为它建立主索引键：

建立开会资料表格以后，另外建立一个储存参加会议的员工资料表格：

设定为“AUTO_INCREMENT”的整数字段，在新增资料的时候可以不用指定数值，MySQL会为你自动编制一个流水号并储存在纪录中；而接着要新增参加这次开会的员工资料到“participate”表格时，你需要用到MySQL刚才会为你在“meeting”表格中自动编制的流水号，这样的需求可以使用“LAST_INSERT_ID()”函式来取得：

在新增资料时，要让MySQL为你自动编制一个流水号，并储存到纪录中的方式有下列几种：

MySQL是一个可以让多人同时使用的数据库，使用“LAST_INSERT_ID()”函式来取得自动编制的流水号数值，并不会因为不同的用户端同时使用而造成混乱：

“AUTO_INCREMENT”字段的一般用法通常是用来储存从“1”开始的流水号，每一笔新增的纪录都会自动加一成为新的编号。可是如果在新增纪录的时候，自己指定“AUTO_INCREMENT”字段一个数值，就会造成下列的情况：

“AUTO_INCREMENT”字段在你删除纪录以后，也不会帮你重新使用已经用过的编号：

注：

使用“TRUNCATE TABLE”叙述删除包含“AUTO_INCREMENT”字段表格的所有纪录，编号会重新从头开始。

不要指定值，或是指定“NULL”值给“AUTO_INCREMENT”字段，都可以让MySQL为你自动编制一个流水号，并储存到纪录中，这两种也是比较好的方式；

使用“AUTO_INCREMENT”字段属性有下列几个重点：

一个表格只能有一个“AUTO_INCREMENT”字段，而且要为它建立一个索引，而且通常是建立主索引键或唯一索引，这样可以防止重复的编号；不过MySQL也允许你建立可重复的索引

只有整数型态才可以使用“AUTO_INCREMENT”字段属性，你可以根据编号大小的需求，选择使用“TINYINT”、“SMALLINT”、“MEDIUMINT”、“INT”或“BIGINT”，而且因为只会使用到正数，所以你可以加入“UNSIGNED”来增加编号的范围

如果编号已经到字段型态的最大范围，例如一个“SMALLINT”型态，而且是指定为“UNSIGNED”的“AUTO_INCREMENT”字段，编号已经到“65535”了，如果再执行新增的叙述，就会造成“Duplicate entry ’65535′ for key ‘字段名称’”的错误

查询表格与索引资讯

表格相关资讯

想要知道一个数据库中有哪一些表格

MySQL数据库在启动以后，会有一个很特别的数据库，名称是“information_schema”，这个数据库通常会称为“系统资讯数据库”。这个数据库中有一个表格叫作“TABLES”，它储存所有MySQL数据库中的表格相关资讯，“TABLES”表格有下列主要的字段：

字段名称	型态	说明
TABLE_SCHEMA	varchar(64)	数据库名称
TABLE_NAME	varchar(64)	表格名称
ENGINE	varchar(64)	使用的储存引擎名称
TABLE_ROWS	bigint(21) unsigned	纪录数量
AUTO_INCREMENT	bigint(21) unsigned	如果包含“AUTO_INCREMENT”字段的话，这个字段会储存下一个编号
TABLE_COLLATION	varchar(32)	表格使用的collation

你也可以

下列的叙述可以查询建立表格的“CREATE TABLE”叙述：

9.2 索引相关资讯

MySQL提供“SHOW INDEX”叙述查询一个表格的索引详细资讯，下列是执行这个叙述以后，传回的主要字段资料：

字段名称	说明
Table	表格名称
Non_unique	“0”表示不可重复；“1”可以重复
Key_name	索引名称
Seq_in_index	单一字段的索引为“1”；多个字段的索引表示建立索引的字段顺序
Column_name	索引字段名称
Sub_part	如果是指定长度的索引，这里会显示长度；不是的话显示“NULL”
Null	是否允许“NULL”值
Index_type	索引种类，“BTREE”或“HASH”

你可以在“SHOW INDEX FROM”后面指定一个表格名称，执行以后就可以查询这个表格所有的索引资讯：

表格和索引