MySQL数据库索引的类型与使用规则

MySQL数据库索引的类型与使用规则

        转载自：http://database.51cto.com/art/201005/202796.htm

        以下的文章主要介绍的是MySQL数据库索引类型，其中包括普通索引，唯一索引，主键索引与组合索引，以及对这些索引的实际应用，以下就是文章的主要内容描述。

1.普通索引

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

1）创建索引

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

        如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length，下同。

2）修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length)) 

3）创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length))); 

4）删除索引：

DROP INDEX [indexName] ON mytable;   

2.唯一索引
        它与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
1）创建索引

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

2）修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 

3）创建表的时候直接指定

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 INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );  

        为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 username，city，age建到一个索引里：

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

        建表时，username长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。如果分别在 username，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
        建立这样的组合索引，其实是相当于分别建立了下面三组组合MySQL数据库索引：
        username,city,age/username,city/username 为什么没有 city,age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合MySQL数据库索引：

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

        而下面几个则不会用到：

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

        转载自：http://book.51cto.com/art/201012/240955.htm

索引的设计原则
        为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
1）选择唯一性索引
        唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
2）为经常需要排序、分组和联合操作的字段建立索引
        经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3）为常作为查询条件的字段建立索引
        如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
4）限制索引的数目
        索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
5）尽量使用数据量少的索引
        如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6）尽量使用前缀来索引
        如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。如上面组合索引例子中：使用username的前10个字符来代表整个username字段建立索引。
7）删除不再使用或者很少使用的索引
        表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
        注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。