mysql数据库索引的优缺点以及创建方法

朴灿烈づ我的快乐病毒、 2022-05-12 08:08 178阅读 0赞

# 为什么创建索引? #

这是因为创建索引可以大大提高系统的性能。

> 1.  通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
> 2.  可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
> 3.  可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
> 4.  在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

# 创建索引的缺点 #

为什么不单一的对每个表的每一列创建索引呢？

> 1.  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
> 2.  索引需要占物理空间，除了数据表站数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大。
> 3.  当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这就降低了数据的维护速度。

# 哪些列适合建立索引？ #

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：

> 1.  在经常需要搜索的列上，可以加快搜索的速度；
> 2.  在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
> 3.  在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
> 4.  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
> 5.  在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
> 6.  在经常使用在WHERE字句中的列上面创建索引，加快条件的判断速度。

# 哪些列不适合创建索引？ #

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下列特点：

> 1.  对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
> 2.  对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大，增加索引，并不能明显加快检索速度。
> 3.  对于那些定义为text和bit数据类型的列不应该增加索引。这是因为这些列的数据量要么相当大，要么取值很少。
> 4.  当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

# 创建索引的方法 #

（1）普通索引

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

创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VCHAR类型，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 INTNOT NULL, username VARCHAR(16) NOT NULL , city VARCHAR(50) NOT NULL, age INT NOT NULL);

为了进一步榨取mysql的效率，就要考虑建立组合索引。

就是将name，city，age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，username长度为16，这里用10.

这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSER的更新速度。

如果分别在username，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的

组合索引效率也会大不一样，远远低于我们的组合索引。

虽然此时有了三个索引，但MySQL只能用到其中的那个他认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立下面三组组合索引：username，city，age，username，city username 为什么没有city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左边的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHERE username="admin" AND city="郑州" ;
    SELECT * FROM mytable WHREE username="admin" ;

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州";
     SELECT * FROM mytable WHREE city="郑州"