MySQL入门(五) MySQL中的索引详讲

MySQL入门(五) MySQL中的索引详讲

索引概述

索引（Index）是在数据库表中建立起的一种数据结构，可以用来大大提高数据库表的检索效率。与数据检索方式不同，通过建立索引可以使用类似二分法的算法来快速定位到目标数据，避免了全表扫描，大大提高了查询效率。

当我们需要从一张表中查询数据行时，如果没有使用索引，MySQL将会做全表扫描（Full Table Scan），也就是将表中的每一行都读取一遍，效率非常低下。而使用索引后，MySQL只需要查找索引表，根据索引表中存储的指针快速找到所需要的数据行，因此查询速度可以大大提升。

索引的作用

1. 加速查询：通过索引定位到所需数据行的过程，比遍历整张表要快得多。
2. 保障数据唯一性：可以对建立索引的列添加唯一性约束，保障表中的数据不会重复。
3. 优化排序：索引也可用于优化某些排序操作，特别是针对大表时，作用尤为显著。

索引的分类

1. 普通索引：最基本的索引类型，没有任何约束条件。

   CREATE INDEX index_name ON table_name(column_name)

2. 唯一索引：建立后要求索引列的值必须是唯一的，可用于保障数据的唯一性。

   CREATE UNIQUE INDEX index_name ON table_name(column_name)

3. 主键索引：唯一索引的一种特殊形式，用于保障数据的唯一性，并将该列设置为数据表的主键。

   ALTER TABLE table_name ADD PRIMARY KEY(column_name)

4. 全文索引：只能用于MyISAM和InnoDB存储引擎，用于对表中的文字进行全文检索。

   CREATE FULLTEXT INDEX index_name ON table_name(column_name)

索引的优点与缺点

优点

1. 大大减少了查询全表时所需的扫描量，缩短了查询时间。
2. 数据与索引分离，便于快速重建索引。
3. 利用索引组织数据，可以方便的实现数据的排序。
4. 可以通过唯一索引添加约束条件，保持数据的完整性和正确性。

缺点

1. 索引需要占据额外的磁盘空间。
2. 索引在插入、更新、删除数据的时候会产生额外的开销。
3. 创建索引的过多会消耗大量的时间和资源，降低数据库性能。
4. 不同类型的查询需要建立不同的索引，索引的维护成本较高。

创建索引的注意事项

1. 需要根据实际需求创建适合的索引类型，不同场景下需要创建的索引类型可能也不同。
2. 只有大表中的字段才需要创建索引，小表反而会因为索引的创建带来的额外开销。
3. 索引列的值越大，索引的效率越低，需要尽可能的缩小索引列的数据范围。
4. 不要创建重复的索引，除非需要在同一列上建立不同类型的索引。
5. 索引所涉及的列尽量不要使用函数、表达式或者计算复杂的SQL语句，这会降低索引的利用效果。
6. 减少字符串类型索引的长度可以有效地提高索引查询效率。
7. 在MySQL中，索引也需要进行优化，在应用中需要进行适当的调整和维护。

示例说明

假设有一张数据表student，需要从表中查询年龄为20岁的学生信息。需要创建一条针对年龄(age)列的索引，创建索引的SQL语句如下：

CREATE INDEX idx_age ON student(age)

然后，使用如下SQL语句查询年龄为20岁的学生信息：

SELECT * FROM student WHERE age = 20

查询结果会直接使用刚刚创建的索引定位到包含年龄为20岁的数据行，而不需要扫描整张表，大大提高了查询效率。

假设学生表中还有一个height列，需要对身高(height)进行排序。需要创建一条针对身高列(height)的索引，创建索引的SQL语句如下：

CREATE INDEX idx_height ON student(height)

然后，使用如下SQL查询语句查询学生表中身高最高的前五名学生：

SELECT * FROM student ORDER BY height DESC LIMIT 5

查询结果会使用刚刚创建的索引按照身高进行排序，而不需要扫描整张表，大大提高了排序操作的效率。