mysql 如何创建简单索引

mysql索引作用

在索引列上，除了有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

 

对于任何的数据库管理系统，索引都是进行优化的最主要因素。对于少量的数据，即使没有合适的索引对数据库性能的影响并不是很大，但是随着数据量的增加，数据库性能会急剧下降，所以索引目的在于提高检索数据的效率。

 

mysql索引创建规则：

• 表的主键、外键必须有索引
• 数据量超过300的表应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
• 索引应该建在选择性高的字段上
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
• 复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替
• 频繁进行数据操作的表，不要建立太多的索引
• 删除无用的索引，避免对执行计划造成负面影响

以上是一些普遍的建立索引时的判断依据。

一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE table_name ADD UNIQUE (column_list);

ALTER TABLE table_name ADD PRIMARY KEY (column_list);

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名 index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名必填。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

索引类型

在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name DROP PRIMARY KEY;

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

一些关于索引的常用语句

添加PRIMARY KEY(主键索引)

ALTER TABLE 'table_name' ADD PRIMARY KEY ('column');

添加UNIQUE(唯一索引)

ALTER TABLE 'table_name' ADD UNIQUE ('column');

添加INDEX(普通索引)

ALTER TABLE 'table_name' ADD INDEX index_name ('column');

添加FULLTEXT(全文索引)

ALTER TABLE 'table_name' ADD FULLTEXT ('column');

添加多列索引

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

UNIQUE KEY和PRIMARY KEY的区别

• Primary key的1个或多个列必须为NOT NULL，如果列为NULL，在增加PRIMARY KEY时，列自动更改为NOT NULL。而UNIQUE KEY 对列没有此要求
• 一个表只能有一个PRIMARY KEY，但可以有多个UNIQUE KEY
• 主键和唯一键约束是通过参考索引实施的，如果插入的值均为NULL，则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值时，可以有重复的，而其他的则不能插入重复值。

alter table t add constraint uk_t_1 unique (a,b);

insert into t (a ,b ) values (null,1);    # 不能重复

insert into t (a ,b ) values (null,null);#可以重复