MySQL5种索引类型

2023-10-28 15:50
文章标签 类型 索引 mysql5

本文主要是介绍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中起着重要的作用，可以提供对列值的倒序查询支持，提高查询性能和优化特定类型的查询。在设计表结构时，如果需要按照倒序进行排序或查询，可以考虑创建倒序索引来提高数据库的性能和查询效率。

这篇关于MySQL5种索引类型的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/294267。

相关文章

性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
零基础学习Redis(10) -- zset类型命令使用

零基础学习Redis(10) -- zset类型命令使用

zset是有序集合，内部除了存储元素外，还会存储一个score，存储在zset中的元素会按照score的大小升序排列，不同元素的score可以重复，score相同的元素会按照元素的字典序排列。 1. zset常用命令 1.1 zadd  zadd key [NX | XX] [GT | LT]   [CH] [INCR] score member [score member ...]
阅读更多...
自定义类型：结构体(续)

自定义类型：结构体(续)

目录 一. 结构体的内存对齐 1.1 为什么存在内存对齐？ 1.2 修改默认对齐数 二. 结构体传参 三. 结构体实现位段 一. 结构体的内存对齐 在前面的文章里我们已经讲过一部分的内存对齐的知识，并举出了两个例子，我们再举出两个例子继续说明： struct S3{double a;int b;char c;};int mian(){printf("%zd\n",s
阅读更多...
【编程底层思考】垃圾收集机制，GC算法，垃圾收集器类型概述

【编程底层思考】垃圾收集机制，GC算法，垃圾收集器类型概述

Java的垃圾收集（Garbage Collection，GC）机制是Java语言的一大特色，它负责自动管理内存的回收，释放不再使用的对象所占用的内存。以下是对Java垃圾收集机制的详细介绍： 一、垃圾收集机制概述： 对象存活判断：垃圾收集器定期检查堆内存中的对象，判断哪些对象是“垃圾”，即不再被任何引用链直接或间接引用的对象。内存回收：将判断为垃圾的对象占用的内存进行回收，以便重新使用。
阅读更多...
flume系列之：查看flume系统日志、查看统计flume日志类型、查看flume日志

flume系列之：查看flume系统日志、查看统计flume日志类型、查看flume日志

遍历指定目录下多个文件查找指定内容 服务器系统日志会记录flume相关日志 cat /var/log/messages |grep -i oom 查找系统日志中关于flume的指定日志 import osdef search_string_in_files(directory, search_string):count = 0
阅读更多...
两个月冲刺软考——访问位与修改位的题型(淘汰哪一页)；内聚的类型；关于码制的知识点；地址映射的相关内容

两个月冲刺软考——访问位与修改位的题型(淘汰哪一页)；内聚的类型；关于码制的知识点；地址映射的相关内容

1.访问位与修改位的题型(淘汰哪一页) 访问位：为1时表示在内存期间被访问过，为0时表示未被访问；修改位：为1时表示该页面自从被装入内存后被修改过，为0时表示未修改过。 置换页面时，最先置换访问位和修改位为00的，其次是01(没被访问但被修改过)的，之后是10(被访问了但没被修改过)，最后是11。 2.内聚的类型 功能内聚：完成一个单一功能，各个部分协同工作，缺一不可。 顺序内聚：
阅读更多...
Mysql BLOB类型介绍

Mysql BLOB类型介绍

BLOB类型的字段用于存储二进制数据 在MySQL中，BLOB类型，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储的大小不同。 TinyBlob 最大 255 Blob 最大 65K MediumBlob 最大 16M LongBlob 最大 4G
阅读更多...
Oracle type （自定义类型的使用）

Oracle type （自定义类型的使用）

oracle - type   type定义： oracle中自定义数据类型 oracle中有基本的数据类型，如number，varchar2，date，numeric，float....但有时候我们需要特殊的格式， 如将name定义为（firstname,lastname）的形式，我们想把这个作为一个表的一列看待，这时候就要我们自己定义一个数据类型 格式 ：create or repla
阅读更多...
MyBatis 切换不同的类型数据库方案

MyBatis 切换不同的类型数据库方案

下属案例例当前结合SpringBoot 配置进行讲解。 背景： 实现一个工程里面在部署阶段支持切换不同类型数据库支持。 方案一 数据源配置 关键代码(是什么数据库，该怎么配就怎么配） spring:datasource:name: test# 使用druid数据源type: com.alibaba.druid.pool.DruidDataSource# @需要修改 数据库连接及驱动u
阅读更多...
按揭贷款类型

按揭贷款类型

按揭贷款可以根据不同的分类标准分为多种类型。以下是按揭贷款的一些常见分类： 按贷款利率分类： 固定利率按揭（Fixed Rate Mortgage, FRM）：在整个贷款期间，利率保持不变，这意味着每月还款额也是固定的。浮动利率按揭（Adjustable Rate Mortgage, ARM）：贷款利率随市场利率的变化而调整，通常有一个基准利率加上一定的浮动点数。 按还款方式分类： 等额本息（
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2