SQL中的索引是什么

在 SQL 中，索引（Index） 是一种用于加速数据检索的数据库对象，通过建立特定的数据结构（如 B+树、哈希表等），帮助数据库系统快速定位目标数据。以下是关于索引的详细分类、工作原理、使用场景和最佳实践：

1. 索引的核心作用

  • 加速查询：减少全表扫描，快速定位数据。
  • 保证唯一性：唯一索引确保列值的唯一性（如主键）。
  • 优化排序和分组：加速 ORDER BY 和 GROUP BY 操作。
  • 实现约束：如外键约束需要索引支持。

2. 索引的数据结构

(1) B+树索引
  • 适用场景：范围查询、排序、等值查询。
  • 特点：
    • 所有数据存储在叶子节点，非叶子节点仅存键值和指针。
    • 叶子节点形成有序链表，支持高效范围查询。
    • MySQL 的 InnoDB 引擎默认使用 B+树索引。
(2) 哈希索引
  • 适用场景：精确等值查询（如 =、IN）。
  • 特点：
    • 基于哈希表实现，查询时间复杂度 O(1)。
    • 不支持范围查询和排序。
    • MySQL 的 Memory 引擎支持哈希索引。
(3) 全文索引
  • 适用场景：文本内容的模糊匹配（如 MATCH AGAINST）。
  • 特点：
    • 基于倒排索引实现，支持自然语言搜索。
    • MySQL 的 MyISAM 和 InnoDB 引擎支持全文索引。
(4) 空间索引（R-Tree）
  • 适用场景：地理空间数据查询（如 GIS）。
  • 特点：
    • 用于高效处理多维数据（如经纬度）。
    • MySQL 的 MyISAM 引擎支持空间索引。

3. 索引的分类

(1) 主键索引（Primary Key Index）

  • 特点：

    • 唯一且非空，每个表只能有一个主键索引。
    • InnoDB 中，主键索引的叶子节点存储整行数据（聚集索引）。

  • 创建语法：

    CREATE TABLE users (id INT PRIMARY KEY,  -- 隐式创建主键索引name VARCHAR(50)
);
(2) 唯一索引（Unique Index）

  • 特点：

    • 确保列值的唯一性，允许 NULL 值。
    • 可用于加速等值查询。

  • 创建语法：

    CREATE UNIQUE INDEX idx_email ON users(email);
(3) 普通索引（Secondary Index）

  • 特点：

    • 仅加速查询，不强制唯一性。
    • InnoDB 中，普通索引的叶子节点存储主键值（回表查询）。

  • 创建语法：

    CREATE INDEX idx_name ON users(name);
(4) 组合索引（Composite Index）

  • 特点：

    • 基于多个列创建的索引，遵循 最左前缀原则。
    • 优化多列条件查询和排序。

  • 创建语法：

    CREATE INDEX idx_name_age ON users(name, age);
(5) 覆盖索引（Covering Index）

  • 特点：

    • 索引包含查询所需的所有字段，无需回表查询。
    • 显著提升查询性能。

  • 示例：

    -- 索引 (name, age)
SELECT name, age FROM users WHERE name = 'Alice';

4. 索引的工作原理（以 B+树为例）

(1) 数据存储
  • 叶子节点：存储数据记录（聚集索引）或主键值（非聚集索引）。
  • 非叶子节点：存储键值和指向子节点的指针。
(2) 查询流程
  1. 从根节点开始，按键值比较确定下一层节点。
  2. 逐层向下查找，直到叶子节点。
  3. 在叶子节点遍历链表找到目标数据。
(3) 示例：查询 name = 'Alice'

  • 若存在索引

    idx_name
    1. 从根节点开始，找到 Alice 所在的叶子节点。
    2. 直接返回记录（覆盖索引）或根据主键回表查询。

  • 若无索引：全表扫描所有记录。

5. 索引的最佳实践

(1) 选择合适的索引列
  • 高选择性列：列值唯一性高（如用户ID、手机号）。
  • 频繁查询的列：常用于 WHERE、JOIN、ORDER BY 的列。
(2) 避免过度索引
  • 缺点：索引占用磁盘空间，降低写操作性能（增删改需维护索引）。
  • 建议：单表索引数不超过 5 个，单个组合索引字段不超过 3 个。
(3) 使用组合索引

  • 最左前缀原则：组合索引

    (A,B,C)

    可优化以下查询：

    • WHERE A = 1
    • WHERE A = 1 AND B = 2
    • WHERE A = 1 AND B = 2 AND C = 3

  • 排序优化：组合索引 (A,B) 可优化 ORDER BY A, B。

(4) 避免索引失效场景
  • 隐式类型转换：WHERE varchar_col = 123（应使用字符串 '123'）。
  • 对索引列运算：WHERE YEAR(date_col) = 2023（应使用范围查询）。
  • 模糊查询前缀：WHERE name LIKE '%Alice'（无法使用索引）。
  • OR 条件非全索引：WHERE a = 1 OR b = 2（若 b 无索引，全表扫描）。
(5) 监控和维护索引

  • 分析慢查询日志：定位低效 SQL 并优化索引。

  • 使用 EXPLAIN：查看查询执行计划，确认索引使用情况。

    EXPLAIN SELECT * FROM users WHERE name = 'Alice';

  • 定期重建索引：优化索引碎片（如 OPTIMIZE TABLE）。

6. 索引的优缺点

(1) 优点
  • 显著加速数据检索。
  • 保证数据唯一性（唯一索引）。
  • 优化排序和分组操作。
(2) 缺点
  • 占用额外磁盘空间。
  • 降低写操作（INSERT/UPDATE/DELETE）性能。
  • 维护成本高，需定期优化。

7. 示例：索引优化实战

(1) 创建表并插入数据
CREATE TABLE orders (id INT PRIMARY KEY,user_id INT,amount DECIMAL(10,2),created_at DATETIME
);-- 插入 100 万条测试数据
(2) 无索引查询（全表扫描）
SELECT * FROM orders WHERE user_id = 100;
-- 执行时间：约 500 ms
(3) 创建索引
CREATE INDEX idx_user_id ON orders(user_id);
(4) 再次查询（索引加速）
SELECT * FROM orders WHERE user_id = 100;
-- 执行时间：约 5 ms
(5) 覆盖索引优化
-- 创建组合索引 (user_id, amount)
CREATE INDEX idx_user_amount ON orders(user_id, amount);-- 覆盖索引查询
SELECT user_id, amount FROM orders WHERE user_id = 100;
-- 无需回表，执行时间：约 2 ms

8. 总结

场景推荐索引类型优化效果
主键查询主键索引直接定位数据，速度最快
多列条件查询组合索引减少回表，提升过滤效率
排序和分组组合索引（按顺序）避免额外排序操作
全文搜索全文索引加速文本模糊查询
精确等值查询（无范围）哈希索引（如 Memory）O(1) 时间复杂度

合理设计和使用索引是数据库性能优化的核心手段。通过分析查询模式、选择合适的数据结构、避免索引失效场景，可以显著提升系统性能。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/38813.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

KMP算法

KMP算法

KMP算法 为什么叫做KMP呢。 因为是由这三位学者发明的：Knuth，Morris和Pratt，所以取了三位学者名字的首字母。所以叫做KMP next数组就是一个前缀表（prefix table）。 前缀表是用来回退的，它记录了模式串与…
阅读更多...
3D点云数据处理中的聚类算法总结

3D点云数据处理中的聚类算法总结

1.欧式聚类： 基于点的空间距离（欧几里得距离）来分割点云，将距离较近的点归为同一簇。 欧式聚类需要的参数：邻域半径R,簇的最小点阈值minPts，最大点数阈值maxPts。 实现效率： O(n * log n) 实现…
阅读更多...
WRC世界机器人大会-2024年展商汇总

WRC世界机器人大会-2024年展商汇总

2024世界机器人大会 时间：2024年8月21日至25日 地点：北京经济技术开发区北人亦创国际会展中心 大会主题：共育新质生产力，共享智能新未来 2024世界机器人博览会亮点纷呈，20余款人形机器人整机将亮相博览会&#xff…
阅读更多...
拉取镜像，推送到阿里云镜像仓库

拉取镜像，推送到阿里云镜像仓库

需求背景：在学习k8s，虚拟机无法正常拉取 wangyanglinux/tools:busybox 镜像。 解决办法：将墙外镜像拉到国内（阿里云）再使用 准备工作需要创建对应的镜像仓库，然后再进行推送 1. 拉取镜像 docker pull …
阅读更多...
DeepSeek和Kimi在Neo4j中的表现

DeepSeek和Kimi在Neo4j中的表现

以下是2个最近爆火的人工智能工具， DeepSeek:DeepSeek Kimi: Kimi - 会推理解析，能深度思考的AI助手 1、提示词： 你能帮我生成一个知识图谱吗，等一下我会给你一篇文章，帮我从内容中提取关键要素，然后以N…
阅读更多...
哈尔滨工业大学DeepSeek公开课人工智能：大模型原理 技术与应用-从GPT到DeepSeek｜附视频下载方法

哈尔滨工业大学DeepSeek公开课人工智能：大模型原理 技术与应用-从GPT到DeepSeek｜附视频下载方法

导 读INTRODUCTION 今天继续哈尔滨工业大学车万翔教授带来了一场主题为“DeepSeek 技术前沿与应用”的报告。 本报告深入探讨了大语言模型在自然语言处理（NLP）领域的核心地位及其发展历程，从基础概念出发，延伸至语言模型在机器翻…
阅读更多...
redis解决缓存穿透/击穿/雪崩

redis解决缓存穿透/击穿/雪崩

文章目录 1.缓存穿透1.1 概念1.2 解决方案1.2.1 缓存空对象1.2.2 布隆过滤 1.2 店铺查询使用缓存穿透解决方案1.2.1 流程 2.缓存雪崩2.1 什么是缓存雪崩？2.2 雪崩解决方案 3.缓存击穿3.1 什么是缓存击穿？3.2解决方案3.2.1 基于互斥锁解决缓存击穿问题&am…
阅读更多...
不连续平面提取

不连续平面提取

不连续平面提取 提取流程 #mermaid-svg-Y87uP8WsVRmPYriG {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Y87uP8WsVRmPYriG .error-icon{fill:#552222;}#mermaid-svg-Y87uP8WsVRmPYriG .error-text{fill:#552222;s…
阅读更多...
大语言模型-2.2/3-主流模型架构与新型架构

大语言模型-2.2/3-主流模型架构与新型架构

简介 本博客内容是《大语言模型》一书的读书笔记，该书是中国人民大学高瓴人工智能学院赵鑫教授团队出品，覆盖大语言模型训练与使用的全流程，从预训练到微调与对齐，从使用技术到评测应用，帮助学员全面掌握大语言模型的…
阅读更多...
数据库操作练习

数据库操作练习

一.向heros表中新增一列信息，添加一些约束，并尝试查询一些信息 //向表中添加一列age信息 alter table heros add column age int;//id列添加主键约束，设置自增 alter table heros modify column id int auto_increment primary key;//name列…
阅读更多...
CTF【WEB】学习笔记1号刊

CTF【WEB】学习笔记1号刊

Kali的小工具箱 curl www.xxx.com：查看服务器响应返回的信息 curl -I www.xxx.com:查看响应的文件头 一、cmd执行命令 ipconfig：ip地址配置等； 二、 Kali操作 1.sudo su； 2.msfconsole 3.search ms17_010 永恒之蓝&#xff…
阅读更多...
在 SaaS 应用上构建 BI 能力的实战之路

在 SaaS 应用上构建 BI 能力的实战之路

SaaS 产品在持续运营过程中积累了大量数据，这些数据不仅是数字的记录，更是洞察市场趋势、优化产品功能、提升用户体验的宝贵资源。 因此，大部分的 SaaS 产品在发展到一定阶段后，都会开始构建自己的报表模块或分析模块，…
阅读更多...
gonet开源游戏服务器环境配置

gonet开源游戏服务器环境配置

1.mysql搭建 搜索mysql-server apt安装包名 sudo apt search mysql-server 安装mysql-server sudo apt-get install mysql-server 安装完成后会，启动mysql服务及创建系统服务 查看服务状态 systemctl status mysql.service 使用超级权限登陆mysql sudo mysql 授…
阅读更多...
STM32基础篇(五)------TIM定时器比较输出

STM32基础篇(五)------TIM定时器比较输出

简介 定时器的类型 在《STM32F10xxx参考手册（中文）.pdf》中可以看到下面三个章节 因此可以得到 高级定时器含有通用定时器的所有功能，通用定时器含有基本定时器的所有功能！！！！！…
阅读更多...
基于STM32的两路电压测量仿真设计Proteus仿真+程序设计+设计报告+讲解视频

基于STM32的两路电压测量仿真设计Proteus仿真+程序设计+设计报告+讲解视频

基于STM32两路电压测量仿真设计(Proteus仿真程序设计设计报告讲解视频） 仿真图Proteus 8.9 程序编译器：keil 5 编程语言：C语言 设计编号：C0106 1.主要功能 基于STM32单片机设计一个双路电压检测器 1.系统可以测量两路输入电…
阅读更多...
210、【图论】课程表（Python）

210、【图论】课程表（Python）

题目 思路 这道题本质上是一个拓扑排序。每次先统计每个点的入度个数、然后再统计点与点之间的邻接关系，找到入度为0的点作为起始遍历点。之后每遍历到这个点之后，就把这个点后续的邻接关系边的点入度减去一。当某个点入度为0时，继续被加入其…
阅读更多...
react 杂记2 优化hook

react 杂记2 优化hook

useEffect 每个Fiber节点都会为该组件的所有effec对象​维护一个链表, 场景​类组件方法函数组件等效写法差异说明挂载时执行componentDidMount()useEffect(fn, [])useEffect 副作用在浏览器绘制后异步执行；componentDidMount 是同步的。更新时执行componentDidUp…
阅读更多...
Java内存泄漏、CPU飙升排查

Java内存泄漏、CPU飙升排查

在Java应用开发中&#xff0c;内存泄漏和CPU飙升是两类高频出现的生产问题&#xff0c;也是常见的面试问题。这里通过一些demo进行实践。 内存泄漏 private static List<byte[]> leakList new ArrayList<>();GetMapping("/memory/leak") public void …
阅读更多...
【搜索】dfs（回溯、剪枝、记忆化）

【搜索】dfs（回溯、剪枝、记忆化）

个人主页&#xff1a;Guiat 归属专栏&#xff1a;我讲你听 文章目录 1. dfs 回溯1.1 回溯介绍1.2 回溯模板1.3 回溯经典题目 2. dfs 剪枝2.1 剪枝介绍2. 2 剪枝模板2.3 经典题目 3. dfs 记忆化3.1 记忆化介绍3.2 记忆化示例 正文 1. dfs 回溯 1.1 回溯介绍 核心思想&#xff…
阅读更多...
emWin自定义键盘布局

emWin自定义键盘布局

emWin V6.46提供了自带的键盘控件&#xff0c;用起来功能还是比较齐全的。但是有些时候自带的布局不能满足要求&#xff0c;此时可用键盘的结构体来自定义布局。 KEYDEF_KEYBOARD MyNumPad;static KEYDEF_AREA NumPadKeyArea[4] {{10, 0, 720, 250}, //每行按钮的坐标和占用…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023