MySQL数据库的索引

水深无声 2021-11-10 15:31 271阅读 0赞

### 1 索引 ###

*  索引是MySQL数据库中的重要对象之一，用于快速找出某个列中有某一特定值的行。
 *  创建索引时，需要确保该索引是应用在SQL查询语句的条件。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
 *  索引分为单列索引和组合索引。
    
     *  单例索引：一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
     *  组合索引：一个索引包含多个列。
 *  不足：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert 、update 、delete因为更新表的收，MySQL不仅要保存数据，还有保存一下索引文件。建立索引会占用磁盘空间的索引文件。

### 2 为什么要使用索引？ ###

*  它是数据库性能调优技术的基础，常用于实现数据的快速检索。
 *  索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

### 3 访问方式 ###

*  顺序访问  
    顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据，这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时， 使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
 *  索引访问  
    索引访问时通过遍历索引来直接访问表中记录行的方式，使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。
 *  例如，在学生基本信息表 students 中，如果基于 student\_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student\_id 为 12022 的数据的时候，系统先在 student\_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

### 4 索引的分类 ###

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。

#### 4.1 物理分类 ####

根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。

1.  B-树索引

*  B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，其包含的组件主要有以下几个
    
     *  叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
     *  分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
     *  根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点
     *  基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。
 *  B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：
    
     *  查询必须从索引的最左边的列开始。
     *  查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
     *  存储引擎不能使用索引中范围条件右边的列。

1.  哈希索引

*  哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
 *  哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。
 *  HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：
    
     *  MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。
     *  不能使用 HASH 索引排序。
     *  HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
     *  HASH 索引不支持键的部分匹配，因为在计算HASH 值的时候是通过整个索引值来计算的。

#### 4.2 逻辑分类 ####

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

*  创建索引
    
    > 语法：CREATE INDEX ON MYTABLE(username(length));  
    > 如果是CHAR，VARCHAR 类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。
 *  添加索引
    
    > 语法：ALTER table tablename add index indexName(columnName)
 *  创建表的时候直接指定
    
    > CREATE TABLE mytable(  
    > ID INT NOT NULL,  
    > username VARCHAR(16) NOT NULL,  
    > INDEX \[indexName\] (username(length))  
    > );
 *  删除索引
    
    > DROP INDEX \[indexName\] ON mytable;

1.  唯一性索引  
    唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。  
    创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。索引列的值必须唯一，但是允许有控制。如果是组合索引，则列值的组合必须唯一。
2.  主键索引  
    主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。

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

1.  空间索引  
    空间索引主要用于地理空间数据类型 GEOMETRY。
2.  全文索引  
    全文索引只能在 VARCHAR 或 TEXT 类型的列上创建，并且只能在 MyISAM 表中创建。

*  索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。
    
     *  单列索引就是索引只包含原表的一个列。
     *  组合索引也称为复合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。

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

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

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

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

*  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
 *  除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
 *  当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

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

索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则：

*  在经常需要搜索的列上建立索引，可以加快搜索的速度。
 *  在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
 *  在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
 *  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
 *  在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
 *  在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。

与此对应，在某些应用场合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上还带来负面效应，降低了数据库的工作效率，一般来说不适合创建索引的环境如下：

*  对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
 *  对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
 *  对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
 *  当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
 *  转载自：[http://c.biancheng.net/view/2604.html][http_c.biancheng.net_view_2604.html]

[http_c.biancheng.net_view_2604.html]: http://c.biancheng.net/view/2604.html