mysql笔记：7. 索引

在关系数据库中，索引是一种可以加快数据检索速度的数据结构，主要用于提高数据库查询数据的性能。在MySQL中，一般在基本表上建立一个或多个索引，从而快速定位数据的存储位置。

索引含义和特点

索引是一个单独地存储在磁盘上的数据库结构，其包含着对数据表里面的所有记录的引用指针。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也没一定支持所有索引类型。
索引的优点主要有：

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

索引也有缺点的，主要有：

• 创建索引和维护索引要耗费时间 ，并且随着数据量的增加所耗费的时间也会增加
• 索引需要占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、修改和删除的时候，索引也要动态维护，这样就降低了数据维护速度。

索引分类

普通索引和唯一索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引是指索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引是一种特殊的唯一索引，不允许有空值。

单列索引和组合索引

单列索引即一个索引只包含单个列。一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时要遵循最左前缀集合。

全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文检索可以在CHAR、VARCHAR或TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

空间索引

空间索引是指对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是:GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使其能够用与创建正规索引类似的语法创建空间索引。相关列必须是非空的。空间索引只能在存储引擎为MyISAM的表中创建。

索引设计原则

索引缺少或设计不合理都会对数据库和应用程序造成障碍。设计时，有以下准则：

• 索引并非越多越好。一个表中如果有大量索引，不仅占用磁盘空间，而且会影响INSERT、UPDATE、DELETE等性能，因为在表中的数据更改时，索引也要进行调整和更新
• 避免对经常更新的表创建过多的索引，并且索引中的列应尽可能少。对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
• 数据量小的表最好不要创建索引。因为数据较少，直接查询花费的时间可能比遍历索引的时间还要短。
• 在条件表达式中经常用到的不同值较多的列上创建索引，在不同值少的列上不要创建索引。如人员信息表中的性能字段只有男女两个不同值，创建索引会严重降低更新速度。
• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需要能确保定义的列的数据完整性，以提高查询速度。
• 在频繁进行排序或分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

创建索引

直接创建索引

最简单的方式是创建表时直接创建索引，基本语法格式如下：

CREATE TABLE table_name [col_name data_type] 
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
1
2

示例如下：

# 普通索引
index(publication_year)

# 唯一索引
unique index uniqId(bookId)

# 全文索引
fulltext index fullindex(bookname)

# 单列索引
index idx(bookname(5))

# 多列索引
index muxIndex(bookid, bookname, author)

# 空间索引
spatial index index_na(bookname)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在已经存在的表上创建索引

基本语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX [index_name] ON table_name ( col_name [(length)] [ASC|DESC]);
1

也可以使用ALTER TABLE语句创建索引
基本语法：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [index_name] ON table_name ( col_name [(length)] [ASC|DESC]);
1

删除索引

基本语法：

ALTER TABLE table_name DROP INDEX index_name;
或
DROP INDEX index_name ON table_name;
1
2
3