首页 > 编程笔记 > MySQL笔记

MySQL索引简介

索引是 MySQL 数据库中的重要对象之一,用于快速找出某个列中有某一特定值的行。

为什么要使用索引

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础,常用于实现数据的快速检索。

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表,实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中,通常有以下两种方式访问数据库表的行数据:

1) 顺序访问

顺序访问是在表中实行全表扫描,从头到尾逐行遍历,直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单,但是当表中有大量数据的时候,效率非常低下。例如,在几千万条数据中查找少量的数据时,使用顺序访问方式将会遍历所有的数据,花费大量的时间,显然会影响数据库的处理性能。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引,在列上创建了索引之后,查找数据时可以直接根据该列上的索引找到对应记录行的位置,从而快捷地查找到数据。索引存储了指定列数据值的指针,根据指定的排序顺序对这些指针排序。

例如,在学生基本信息表 students 中,如果基于 student_id 建立了索引,系统就建立了一张索引列到实际记录的映射表,当用户需要查找 student_id 为 12022 的数据的时候,系统先在 student_id 索引上找到该记录,然后通过映射表直接找到数据行,并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度,所以采用索引的方式可以大大提高数据库的工作效率。

索引的分类

索引的类型和存储引擎有关,每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同,MySQL 中常用的索引在物理上分为以下两类。

1) B-树索引

B-树索引又称为 BTREE 索引,目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构,其包含的组件主要有以下几个:
基于这种树形数据结构,表中的每一行都会在索引上有一个对应值。因此,在表中进行数据查询时,可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询,也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则,要考虑以下几点约束:

2) 哈希索引

哈希(Hash)一般翻译为“散列”,也有直接音译成“哈希”的,就是把任意长度的输入(又叫作预映射,pre-image)通过散列算法变换成固定长度的输出,该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中,MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引,且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据,而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快,但也存在下面的一些缺点:
根据索引的具体用途,MySQL 中的索引在逻辑上分为以下 5 类:

1) 普通索引

普通索引是最基本的索引类型,唯一任务是加快对数据的访问速度,没有任何限制。创建普通索引时,通常使用的关键字是 INDEX 或 KEY。

2) 唯一性索引

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。

创建唯一性索引的目的往往不是为了提高访问速度,而是为了避免数据出现重复。

3) 主键索引

主键索引是一种唯一性索引,即不允许值重复或者值为空,并且每个表只能有一个主键。主键可以在创建表的时候指定,也可以通过修改表的方式添加,必须指定关键字 PRIMARY KEY。

注意:主键是数据库考察的重点。注意每个表只能有一个主键。

4) 空间索引

空间索引主要用于地理空间数据类型 GEOMETRY。

5) 全文索引

全文索引只能在 VARCHAR 或 TEXT 类型的列上创建,并且只能在 MyISAM 表中创建。

索引在逻辑上分为以上 5 类,但在实际使用中,索引通常被创建成单列索引和组合索引。

提示:一个表可以有多个单列索引,但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引,以此来加快查询速度。比如,在一个表中创建了一个组合索引(c1,c2,c3),在实际查询中,系统用来实际加速的索引有三个:单个索引(c1)、双列索引(c1,c2)和多列索引(c1,c2,c3)。

为了提高索引的应用性能,MySQL中的索引可以根据具体应用采用不同的索引策略。这些索引策略所对应的索引类型有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引等。

索引的使用原则和注意事项

虽然索引可以加快查询速度,提高 MySQL 的处理性能,但是过多地使用索引也会造成以下弊端:

注意:索引可以在一些情况下加速查询,但是在某些情况下,会降低效率。

索引只是提高效率的一个因素,因此在建立索引的时候应该遵循以下原则:
与此对应,在某些应用场合下建立索引不能提高 MySQL 的工作效率,甚至在一定程度上还带来负面效应,降低了数据库的工作效率,一般来说不适合创建索引的环境如下:

所有教程

优秀文章