MySQL unique index vs. index speed

在MySQL中索引是非常重要的，可以大幅度提升查询效率。但不同的索引类型会对速度有所影响。本文将主要介绍MySQL中unique index和普通index的区别以及对查询速度的影响。

阅读更多：MySQL 教程

索引的基础概念

索引是数据结构，帮助数据库系统高效的检索数据。MySQL中常用的索引类型有primary key、unique index、index、fulltext，其中primary key和unique index可以唯一标识每个记录，而index和fulltext则不可以。

Unique index vs. index

UNIQUE索引和INDEX的区别，大家一般都理解，因为名字都不一样。但是细节的差别和优势，就需要细心了解了。下面将从以下几个方面详细解释它们之间的不同。

唯一值

UNIQUE索引中，列值必须唯一，但是Null可以重复出现。而在INDEX中，值可以重复出现。

性能

因为UNIQUE的唯一性，它的查询性能要略高于INDEX。但三个内部数据结构B-tree，Hash和R-Tree，都支持索引键值的哈希访问方式，在内存小而数据量巨大的时候，使用HASH较快而且存储使用倍数小于BTREE，因为HASH 内部信息比较少。

在执行查询时，如果可以使用上UNIQUE索引就能更快地执行查询了。但是，如果查询的条件中某些列并不是UNIQUE的，使用INDEX也可以提高查询效率，这与查询中的where子句有关。

SELECT * FROM student WHERE name='tom' AND age=20;

假设有两个索引：(name, age)和name。如果name=’tom’的记录很少，那么使用(name, age)索引的效率会更高，因为它可以过滤掉一部分数据。而对于name索引，必须扫描所有数据才能找到对应的记录，效率会低一些。

索引长度

UNIQUE索引比INDEX索引的长度长，以VARCHAR类型为例：

假设有2条记录：

AAA，1111111111
AAA，1111111112

如果使用UNIQUE(name, num)索引，则整个索引长度是20，而如果使用(name)和(num)索引，则总共只有11个字符数，效率会高一些。

索引类型的选择

在实际开发中，应该根据实际情况选择不同的索引方式。

如果某一列存在重复值，或者该列在查询中的where子句中，那么使用INDEX索引。

如果某一列的值都是不同的，可以使用UNIQUE索引或PRIMARY KEY索引。

如果某一列需要进行全文索引，可以使用FULLTEXT索引。

索引的创建

在MySQL中索引使用create index命令创建。下面分别对unique index和index进行示例说明。

创建unique index

创建student表，包含name和num两列：

CREATE TABLE student (
    name VARCHAR(20),
    num VARCHAR(20)
);

创建UNIQUE(name, num)索引：

CREATE UNIQUE INDEX index_name ON student(name, num);

如果在插入或更新数据时违反了唯一性限制，MySQL会在执行插入或更新操作时报错。

创建index

创建score表，包含name、subject和score三列：

CREATE TABLE score (
    name VARCHAR(20),
    subject VARCHAR(20),
    score INT(3)
);

创建(name, subject)和(score)索引：

CREATEINDEX index_name ON score(name, subject);
CREATE INDEX index_score ON score(score);

总结

在MySQL中，unique index和普通index对查询速度有所影响。唯一值、性能、索引长度等方面都有所不同。开发者应该根据实际情况选择不同的索引方式，在创建索引时也需要注意唯一性限制。索引的正确创建和使用，可以大幅度提升查询效率，提高系统的整体性能。