MySQL5种索引类型

摘要

索引在数据库中起着至关重要的作用，可以提高查询性能和数据的访问效率。MySQL作为一种常用的关系型数据库管理系统，提供了多种索引类型来满足不同的需求。本文将介绍MySQL的五种索引类型及其特点，帮助读者选择适合的索引类型。

MySQL的类型主要有五种：主键索引、唯一索引、普通索引、空间索引、全文索引

有表：

CREATE TABLE `t1` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`u1` int unsigned NOT NULL DEFAULT '0',
`u2` int unsigned NOT NULL DEFAULT '0',
`u3` varchar(20) NOT NULL DEFAULT '',
`u4` varchar(35) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB

主键索引：

主键索引是MySQL中的一种特殊索引类型，用于唯一标识表中的每一行数据。下面是对主键索引的总结：

1. 主键索引是一种唯一性索引，用于保证表中每一行数据的唯一性。每个表只能有一个主键索引，可以由一个或多个字段组成。

2. 主键索引可以提高查询性能，因为它会将数据按照主键的顺序进行排序存储，使得范围查询和排序查询更加高效。

3. 主键索引可以加速等值查询，因为它使用B树或哈希表来存储索引数据，可以通过快速的查找算法找到对应的数据行。

4. 主键索引可以作为其他索引的前缀索引，提高其他索引的查询性能。如果其他索引的前缀是主键索引的一部分，那么可以直接使用主键索引进行查询，避免了对其他索引的访问。

5. 主键索引的选择需要考虑字段的唯一性、查询频率和数据更新的成本。如果字段具有较高的唯一性，且经常被用于查询条件，那么可以考虑将其作为主键索引。

总之，主键索引在MySQL中起着非常重要的作用，能够保证数据的唯一性，提高查询性能，并且可以作为其他索引的前缀索引。在设计表结构时，应该合理选择主键字段，并为其创建主键索引，以提高数据库的性能和可靠性。

# 查看创建的索引关键词
PRIMARY KEY (`id`)

唯一索引;

唯一索引是MySQL中的一种索引类型，用于确保表中某个字段或字段组合的唯一性。下面是对唯一索引的总结：

1. 唯一索引可以保证表中某个字段或字段组合的唯一性，即该字段的值在表中不能重复。每个表可以有多个唯一索引。

2. 唯一索引可以提高查询性能，因为它使用B树或哈希表来存储索引数据，可以通过快速的查找算法找到对应的数据行。

3. 唯一索引可以加速等值查询，因为它可以直接通过索引找到对应的数据行，而不需要进行全表扫描。

4. 唯一索引可以作为其他索引的前缀索引，提高其他索引的查询性能。如果其他索引的前缀是唯一索引的一部分，那么可以直接使用唯一索引进行查询，避免了对其他索引的访问。

5. 唯一索引的选择需要考虑字段的唯一性、查询频率和数据更新的成本。如果字段具有较高的唯一性，且经常被用于查询条件，那么可以考虑将其作为唯一索引。

总之，唯一索引在MySQL中起着重要的作用，能够保证数据的唯一性，提高查询性能，并且可以作为其他索引的前缀索引。在设计表结构时，应该合理选择需要保证唯一性的字段，并为其创建唯一索引，以提高数据库的性能和数据的完整性。

# 创建唯一索引
mysql> alter table t1 add unique idx_u1(`u1`);

# 查看创建的索引关键词
UNIQUE KEY `idx_u1` (`u1`)

单列索引

单列索引是MySQL中最基本的索引类型，用于加速对单个列的查询操作。下面是对单列索引的总结：

1. 单列索引是针对表中的单个列创建的索引，可以加速对该列的等值查询、范围查询和排序操作。

2. 单列索引可以提高查询性能，因为它使用B树或哈希表来存储索引数据，可以通过快速的查找算法找到对应的数据行。

3. 单列索引可以减少查询的数据量，因为它可以根据索引的顺序进行范围查询，避免了对整个表的全表扫描。

4. 单列索引可以加速排序操作，因为它会将数据按照索引的顺序进行排序存储，使得排序操作更加高效。

5. 单列索引的选择需要考虑字段的查询频率和数据更新的成本。如果某个字段经常被用于查询条件，那么可以考虑为该字段创建单列索引。

总之，单列索引在MySQL中起着重要的作用，能够提高查询性能和排序性能，并且减少查询的数据量。在设计表结构时，应该根据查询需求合理选择需要加速的字段，并为其创建单列索引，以提高数据库的性能和查询效率。

• 即索引创建在单个列上

• # 创建单列索引
  mysql> alter table t1 add index idx_u1(`u1`);
  
  # 查看创建的索引关键词
  KEY `idx_u1` (`u1`)

  多列索引

• 创建多列索引时记得遵循最左匹配原则，把区分度较高的字段放前面

• # 创建多列索引
  mysql> alter table t1 add index idx_u1_u2(`u1`,`u2`);
  
  # 查看创建的多列索引关键词
  KEY `idx_u1_u2` (`u1`,`u2`)

  多列索引（也称为复合索引或联合索引）是MySQL中的一种索引类型，用于加速对多个列的查询操作。下面是对多列索引的总结：

  1. 多列索引是针对表中多个列创建的索引，可以加速对这些列的等值查询、范围查询和排序操作。

  2. 多列索引可以提高查询性能，因为它使用B树或哈希表来存储索引数据，可以通过快速的查找算法找到对应的数据行。

  3. 多列索引可以减少查询的数据量，因为它可以根据索引的顺序进行范围查询，避免了对整个表的全表扫描。

  4. 多列索引可以支持多个列的组合查询，可以同时使用多个列的值进行查询，提高查询的灵活性。

  5. 多列索引的选择需要考虑多个列的查询频率和数据更新的成本。如果多个列经常被用于查询条件，那么可以考虑为这些列创建多列索引。

  6. 多列索引的顺序很重要，因为它决定了索引的效果。在创建多列索引时，应该根据查询的频率和范围选择合适的列顺序。

  总之，多列索引在MySQL中起着重要的作用，能够提高查询性能和灵活性，并且减少查询的数据量。在设计表结构时，应该根据多个列的查询需求合理选择需要加速的列，并为其创建多列索引，以提高数据库的性能和查询效率。

• 前缀索引

• 即当字段的长度超过索引限制，可以为字段的部分前缀创建索引

• # 创建前缀索引，取字符串的前4个字符
  mysql> alter table t1 add index idx_u3(`u3`(4));
  
  # 查看创建的索引关键词
  KEY `idx_u3` (`u3`(4))

  前缀索引是MySQL中的一种索引类型，它只对列值的前缀进行索引，而不是对整个列值进行索引。下面是对前缀索引的总结：

  1. 前缀索引可以减少索引的存储空间，因为它只对列值的前缀进行索引，而不是对整个列值进行索引。这可以节省磁盘空间，并提高索引的效率。

  2. 前缀索引可以提高查询性能，因为它可以减少索引的大小，从而减少了I/O操作的次数。这对于大型表和长文本字段特别有用。

  3. 前缀索引可以加快索引的建立和维护速度，因为它需要处理的数据量更小。这对于频繁更新的表和大量插入数据的场景特别有用。

  4. 前缀索引的选择需要考虑索引的选择性和查询的需求。如果列值的前缀具有较高的选择性，并且查询只需要使用前缀进行匹配，那么可以考虑为该列创建前缀索引。

  5. 前缀索引的长度需要根据实际情况进行选择。较短的前缀长度可以减少索引的存储空间和维护成本，但可能会降低索引的选择性和查询效率。

  总之，前缀索引在MySQL中起着重要的作用，可以减少存储空间、提高查询性能和加快索引的建立和维护速度。在设计表结构时，应该根据列值的选择性和查询需求合理选择需要创建前缀索引的列，并选择合适的前缀长度，以提高数据库的性能和查询效率。

• 倒序索引

• 8.0 版本出的新功能，以往创建倒叙索引可以创建成功，但实际上仍然是顺序的

# 5.7 创建倒序索引
mysql> select u1 from t1 limit 10;
+----+
| u1 |
+----+
| 12 |
| 23 |
| 12 |
| 34 |
+----+
10 rows in set (0.00 sec)
# 创建倒序索引
mysql> alter table t1 add index idx_u3(u3 desc);
# 实际查出来还是顺序
mysql> select u1 from t1 limit 10;
+----+
| u1 |
+----+
| 12 |
| 12 |
| 12 |
| 12 |
+----+

# 8.0 创建倒序索引
mysql> select u1 from t1 limit 5;
+----+
| u1 |
+----+
| 12 |
| 23 |
| 12 |
| 34 |
+----+
5 rows in set (0.00 sec)

# 添加索引
mysql> alter table t1 add index idx_u1(u1 desc);

# 查询倒序索引成功
mysql> select u1 from t1 limit 5;
+----+
| u1 |
+----+
| 74 |
| 74 |
| 74 |
| 74 |
+----+
5 rows in set (0.00 sec)

表格展示

索引类型	特点
B-Tree索引	最常用的索引类型，适用于所有类型的查询，支持多列索引和范围查询，适合处理大数据量和高并发的情况。
Hash索引	适用于等值查询，不支持范围查询和排序操作，适合处理查询速度要求非常高的场景。
Full-Text索引	适用于全文搜索，支持关键词的匹配和排序，适合处理大量文本数据的搜索。
R-Tree索引	适用于地理空间数据的查询，支持范围查询和空间关系查询，适合处理地理位置相关的应用。
InnoDB全文索引	适用于InnoDB存储引擎，支持全文搜索和关键词匹配，适合处理大量文本数据的搜索。

倒序索引是一种特殊的索引类型，用于对列值进行倒序存储和查询。下面是对倒序索引的总结：

1. 倒序索引可以提供对列值的倒序查询支持，即按照列值的倒序进行索引和查询。这对于需要按照倒序进行排序或查询的场景特别有用。

2. 倒序索引可以提高查询性能，因为它可以减少排序操作的开销。当需要按照倒序进行排序时，使用倒序索引可以避免对整个表的全表扫描和排序操作。

3. 倒序索引可以用于优化特定类型的查询，如按时间倒序查询最新的记录。通过使用倒序索引，可以快速定位到最新的记录，而不需要扫描整个表。

4. 倒序索引的创建和使用与正常索引类似，只是在创建索引时需要指定倒序排序。在查询时，可以使用倒序索引来加速倒序查询操作。

5. 倒序索引可以在单列上创建，也可以在多列上创建。在多列上创建倒序索引时，需要注意各列的排序顺序和倒序索引的创建顺序。

总之，倒序索引在MySQL中起着重要的作用，可以提供对列值的倒序查询支持，提高查询性能和优化特定类型的查询。在设计表结构时，如果需要按照倒序进行排序或查询，可以考虑创建倒序索引来提高数据库的性能和查询效率。