MySQL面试之数据库索引

快来打我* 2023-06-16 02:20 1阅读 0赞

# 一、啥是索引 #

数据库索引是建立在表的一列或多个列上的辅助对象，用于加快数据的检索，类似于书籍的索引。当数据库表中的数据越来越多的时候，查询效率变慢，这时候我们就需要数据库索引了。

**优点：**

1.  加快数据的查询速度；
2.  唯一索引可以确保数据的唯一性；
3.  可以加速表和表之间的连接，实现表和表之间的参照完整性；
4.  使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

**缺点：**

1.  占用数据表以外的物理存储空间；
2.  需要对索引进行维护；
3.  表进行更新操作时，索引需要被重建，降低了数据的维护速度。

# 二、索引的分类 #

1、**普通索引**（INDEX）

**create index index\_name on table\_name(col\_name)**

普通索引是最基本的索引，没有什么特别限制，主要是为了增加这个字段的查询速度。

2、**唯一索引**（UNIQUE）

**create unique index user\_id\_index on user（id）**

唯一索引是强调表中数据记录的唯一性，允许NULL值。

3、**主键索引**（PRIMAY KEY）

主键索引是一种特殊的唯一索引，将索引建在主键上，不允许NULL值。

4、**组合索引**

组合索引是多列值组成一个索引，专门用于组合搜索，效率大于索引合并。

5、**全文索引**（FULLTEXT）

全文索引对文本的内容进行分词搜索。

# 三、索引的实现方式 #

**1、 B+树**

B+树和红黑树差不多，都是是为了尽量保持树的平衡。但红黑树是二叉树，B+树是多叉树，节点下面可以有多个子节点，数据库会默认设置子节点数的一个最大值，这个值不会太小，所以B+树一般来说比较矮胖，而红黑树就比较瘦高。

如果经常需要同时对两个字段进行and查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

**2、散列索引**

散列索引，又叫哈希索引，是通过散列函数来定位的一种索引。不过很少有单独使用散列索引的，反而是散列文件组织用的比较多。

散列文件组织就是根据一个键通过散列计算把对应的记录都放到同一个槽中，这样的话相同的键值对应的记录就一定是放在同一个文件里了，也就减少了文件读取的次数，提高了效率。

散列索引呢就是根据对应键的散列码来找到最终的索引项的技术，其实和B树就差不多了，也就是一种索引之上的二级辅助索引，我理解散列索引都是二级或更高级的稀疏索引，否则桶就太多了，效率也不会很高。

**3、位图索引**

位图索引是一种针对多个字段的简单查询设计一种特殊的索引，适用范围比较小，只适用于字段值固定并且值的种类很少的情况，比如性别，只能有男和女，或者级别，状态等，并且只有在同时对多个这样的字段查询时才能体现出位图的优势。

**create bitmap index idx\_name on table(col\_name)**

位图的基本思想就是对每一个条件都用0或者1来表示，如有5条记录，性别分别是男，女，男，男，女，那么如果使用位图索引就会建立两个位图，对应男的10110和对应女的01001,这样做有什么好处呢，就是如果同时对多个这种类型的字段进行and或or查询时，可以使用按位与和按位或来直接得到结果了。

# 四、创建索引的原则 #

凡事都有两面，有利必有弊。建立索引也是同样的道理，我们在增加了检索速度的同时，也会损耗相当的更新性能。所以建立索引前，要慎重的讨论清楚，确实需要的时候再加。

**应该创建索引的情况：**

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

**不应该创建索引的情况：**

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