MySQL基础笔记（四）索引

川长思鸟来 2022-08-01 18:21 146阅读 0赞

# 一、什么是索引 #

**索引（Index）**，可以看作一个指针，指向表里的数据。当数据库没有索引时，查找信息通常是**全表扫描**；使用了索引，它就会直接引导到数据在表里的准确物理位置。

*  **优点**：索引的主要目的是提高数据检索的性能，在数据量比较大时尤其明显。
 *  **缺点**：
    
    1.  索引通常与相应的表是分开保存的，索引也会占据物理存储空间，而且可能比表本身还大。
    2.  索引的维护（索引项的添加和删除）有时间消耗，会影响数据检索的速度。

当为字段添加了索引后，索引里记录了与被索引字段相关联的位置值。当表里添加新数据时，索引里也会添加新项。如下图所示：

![20150702154624025][]

假设执行

SELECT * FROM table_name WHERE name='SMITH';

由于 where 条件里的 name 字段已经设置了索引，所以会首先在索引里搜索`'SMITH'`，找到以后直接返回被搜索数据在表里的实际位置。

# 二、创建索引 #

创建索引使用`CREATE INDEX`命令或者`ALTER TABLE...ADD...`命令。数据库里的表可以创建多种类型的索引。

## 1. 单字段索引 ##

单字段索引是基于一个字段创建的：

CREATE INDEX index_name ON table_name(column_name);

如果某个字段经常在 where 子句作为单独的查询条件，那么它的单字段索引是最有效的。

## 2. 唯一索引 ##

唯一索引不允许`被索引字段`具有重复值，除此之外，它与普通索引的功能一样。

CREATE UNIQUE INDEX index_name ON table_name(column_name);

唯一索引用于改善性能和保证数据完整性。

## 3. 组合索引 ##

组合索引是基于两个或多个字段创建的：

CREATE INDEX index_name ON table_name(column1, column2);

如果经常在where子句里联合使用两个或多个字段进行查询，可以考虑创建组合索引。

## 4. 隐含索引 ##

隐含索引是数据库服务程序在创建对象时自动创建的。比如，数据库会为**主键约束**和**唯一性约束**自动创建索引。

为什么给主键约束和唯一性约束自动创建索引？

主键约束和唯一性约束都要求字段的每个值都唯一。当用户向表中添加新记录时，为了有效地检查新值在成百上千的记录里是否唯一，对应的字段必须被索引。

# 三、删除索引 #

MySQL中删除索引使用`ALTER TABLE`或者`DROP INDEX`命令。两者的功能是一样的，事实上，`DROP INDEX`语句在内部被映射到一个`ALTER TABLE`语句中。

方法一：

ALTER TABLE table_name DROP INDEX index_name;

方法二：

DROP INDEX index_name ON table_name;

# 四、何时（不）应该使用索引 #

考虑使用索引的情况：

1.  一般来说，大多数用于表结合的字段都应该设置索引。比如，外键经常用于与父表的结合，适合设置索引。
2.  经常在`order by`和`group by`里引用的字段应该考虑设置索引，因为索引会自动进行排序，简化了实际的排序操作，提高了输出结果的速度。
3.  具有大量唯一值得字段，或是在where子句里会返回很小部分记录的字段，都可以考虑设置索引。

索引的初衷是提高数据检索的性能，但不要认为使用索引就能解决所有的问题，在一些情况下索引可能根本不会改善性能而只是占据磁盘空间。

1.  索引不应该用于小规模的表。
2.  当字段在where子句里作为条件会返回表里的大部分记录时，该字段不适合设置索引。
3.  包含大量`NULL`值的字段不应该设置索引。索引对在不同记录中包含不同数据的字段特别有效，字段中过多的NULL值会严重影响索引的运行效率。
4.  经常被更新的字段不应该设置索引。因为对索引的维护会变得很繁重。

个人站点：[http://songlee24.github.com][http_songlee24.github.com]

[20150702154624025]: /images/20220731/49eadcb0c32b45228a4d7c7dfa265395.png
[http_songlee24.github.com]: http://songlee24.github.io/