大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

唯一索引和普通索引的区别是什么,什么时候需要建唯一索引。


_腾讯视频

一、背景介绍

索引(INDEX)是寻找资源中特定项目的一种机制,其实质是一种以特定顺序保存的表。索引的作用是便捷化检索表中的行和列的子集,而不需要检查表中的每行。常见有INDEX、UNIQUE INDEX、FULLTXET INDEX和SPATIAL INDEX(仅MyISAM支持后两者)。

引申:MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。

二、知识剖析

UNIQUE INDEX具备INDEX的全部特点,还作为一种机制限制索引列出现重复值,即设置为UNIQUE INDEX的列或者字段,其对应的值必须唯一。唯一索引可以保证数据记录的唯一性。

通常,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。即向表中插入一条记录,DBMS首先检查该记录中被定义为UNIQUE INDEX字段的值是否已经存在;如果是,DBMS将拒绝插入那条新记录。

三、常见问题

每个索引实质上是一个表,需要占用磁盘空间与维护管理。每次对表增或删,表中的所有INDEX必须被修改;更新行时,受影响的的列的任何INDEX也必须被修改。一旦发生较多INDEX更改,服务器的处理性能将被拖累。

四、解决方案

INDEX默认使用策略

1)确保所有主键列被索引;

2)为所有被外键约束引用的列创建索引

3)索引被频繁检索的列。

五、代码实战

1)创建索引

mysql> ALTERTABLE Registration

-> ADD INDEX reg_name_idx(name);

Query OK, 0 rowsaffected (0.03 sec)

Records:0Duplicates: 0Warnings: 0


mysql>CREATE INDEX reg_name_idx

-> ON Registration (name);

Query OK, 0 rowsaffected (0.02 sec)

Records:0Duplicates: 0Warnings: 0

(从MySQL5.0开始,create index命令已经映射到alter table命令,但是前者依旧可用)

2)删除索引

mysql> ALTERTABLE Registration

-> DROP INDEX reg_QQ_idx;

Query OK, 0 rowsaffected (0.02 sec)

Records:0Duplicates: 0Warnings: 0

3)插入一条记录

mysql> INSERTINTO Registration(QQ)

-> VALUES('505034172');

ERROR1062 (23000): Duplicate entry '505034172' for key 'reg_QQ_idx'

4)查看索引

mysql> SHOWINDEX FROM Registration \G

***************************1. row ***************************

Table: registration

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 6

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

***************************2. row ***************************

Table: registration

Non_unique: 0

Key_name: reg_QQ_idx

Seq_in_index: 1

Column_name: QQ

Collation: A

Cardinality: 7

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

***************************3. row ***************************

Table: registration

Non_unique: 1

Key_name: reg_name_idx

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 7

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

3 rows in set (0.00 sec)

六、扩展思考

基于报名帖整理出的学员表,如果对QQ建立了UNIQUE INDEX,在插入新的学员记录时候,是否需要先判断这个QQ是否存在?

七、参考文献

Alan Beaulieu.SQL学习指南(第2版).张伟超 林青松译.北京:人民邮电出版社,2015.

八、更多讨论

详见小视频。

九、鸣谢

今天的分享就到这里啦,欢迎大家拍砖和吐槽!


技能树.IT修真院

“我们相信人人都可以成为一个工程师,现在开始,找个师兄,带你入门,掌控自己学习的节奏,学习的路上不再迷茫”。

这里是技能树.IT修真院,成千上万的师兄在这里找到了自己的学习路线,学习透明化,成长可见化,师兄1对1免费指导。快来与我一起学习吧。