MySQL中索引全详解

第一部分：什么是索引

        索引在数据库中就像书的目录，能够快速定位数据位置，从而提升查询效率。没有索引时，数据库查询需要从头到尾扫描整个表（称为全表扫描），这在数据量大时非常耗时。有了索引后，查询就像查找字典中某个字母开头的单词一样，可以直接跳转到相关数据，大大加快了响应速度。

索引的原理可以简单理解为：

  • 数据库通过创建额外的“数据结构”（类似目录）存储索引字段的信息。
  • 查询时，数据库优先查找索引，再根据索引定位到实际数据。
  • 索引对查询速度有提升，但同时也会增加写入和更新的时间，因为每次修改表数据时，索引也需要维护和更新。

        简单总结，索引的核心作用是提高查询速度，但这是一种以额外存储和维护成本换取查询效率的优化方式。

第二部分：索引的分类

        我们按照三个角度来详细分析索引的分类：数据结构、字段特性、字段个数。

1.按数据结构分类

        这是基于索引底层的存储和组织方式来划分的，常见的有以下三种：

1.1 B+Tree 索引

  1. 原理：

    • B+Tree 索引是 MySQL 中最常用的索引结构，底层采用的是平衡多路搜索树。
    • 数据以“节点”的形式存储，叶子节点存储了表中所有的索引字段值，并以链表形式连接；非叶子节点作为目录，用于快速查找。
    • 查找过程：从根节点开始，依次向下遍历，直到叶子节点。

  2. 特点：

    • 有序存储，支持范围查询。
    • 查询效率稳定，查找时间复杂度为 O(log⁡n)O(logn)。
    • 适用于大多数场景，包括等值查询、范围查询、排序等。

  3. 适用场景：

    • 查询条件中包含范围操作（如 BETWEEN、> <）。
    • 常见于主键索引和普通索引。

  4. 局限性：

    • 如果字段值分布不均匀，可能导致某些查询的效率下降。
    • 对频繁插入或更新的大表性能有一定影响，因为需要维护树的平衡。

1.2 Hash 索引

  1. 原理：

    • 使用哈希函数将字段值映射为哈希值，哈希值对应实际数据的位置。
    • 适合等值查询，类似于通过钥匙直接打开锁。

  2. 特点：

    • 查询速度极快，时间复杂度接近 O(1)O(1)。
    • 不支持范围查询，因为哈希值无法保持顺序性。
    • 哈希冲突可能影响性能。

  3. 适用场景：

    • 等值查询场景，例如 WHERE id = 10。
    • 用于对性能要求极高、数据分布均匀的表。

  4. 局限性：

    • 不支持范围查询（如 BETWEEN、> <）。
    • 不适合排序或分组操作。

1.3 Full-Text 索引

  1. 原理：

    • 专门为全文搜索设计的一种索引，类似搜索引擎的倒排索引。
    • 将文本内容切分为关键词，并建立关键词到文档的映射关系。

  2. 特点：

    • 支持模糊查询、多关键字匹配。
    • 效率远高于使用 LIKE '%...%' 的查询方式。

  3. 适用场景：

    • 对大文本字段（如文章、评论）进行搜索，例如实现类似搜索引擎的功能。

  4. 局限性：

    • 配置复杂，只支持特定存储引擎（如 MyISAM、InnoDB 的部分版本）。
    • 对于频繁更新的大文本字段性能较差。

2. 按字段特性分类

        这是基于字段在表中的角色和限制条件来划分的，主要有以下几种：

2.1 主键索引

  1. 定义：

    • 主键是表中的唯一标识，每张表只能有一个主键。主键索引是数据库默认为主键字段生成的索引。

  2. 特点：

    • 唯一性，保证每行数据的主键值不同。
    • 主键索引一般使用 B+Tree 实现，叶子节点存储完整的行数据。

  3. 适用场景：

    • 必须保证数据唯一性，例如用户 ID、订单号等。

2.2 唯一索引

  1. 定义：

    • 唯一索引与主键类似，但表中可以有多个唯一索引。
    • 保证字段值唯一，但允许存在空值（NULL）。

  2. 特点：

    • 避免重复值，确保数据完整性。
    • 唯一索引一般用于非主键字段。

  3. 适用场景：

    • 确保特定字段的值不重复，例如邮箱、用户名等。

2.3 普通索引

  1. 定义：

    • 没有唯一性限制，仅用于提升查询速度。

  2. 特点：

    • 普通索引可以在任意字段上添加。
    • 支持多种查询操作（等值、范围等）。

  3. 适用场景：

    • 提高查询效率，但对唯一性无严格要求的字段。

2.4 前缀索引

  1. 定义：

    • 针对字符串字段，只索引前面几位字符，而不是整列。

  2. 特点：

    • 节省存储空间，提升索引效率。
    • 对于区分度较高的字符串字段适用。

  3. 适用场景：

    • 长字符串字段，如 URL、电子邮件地址等。

3. 按字段个数分类

这是根据字段数量来划分的，主要有以下两种：

3.1 单列索引

  1. 定义：

    • 索引只包含一个字段。

  2. 特点：

    • 适合简单的查询条件（单字段查询）。
    • 无法直接优化多字段组合查询。

  3. 适用场景：

    • 单一字段的等值或范围查询。

3.2 联合索引（复合索引）

  1. 定义：

    • 索引包含多个字段，以字段顺序为准。

  2. 特点：

    • 支持最左前缀原则，即查询条件必须包含从左到右的字段顺序。
    • 高效优化多字段查询，但顺序很重要。

  3. 适用场景：

    • 多字段组合查询，例如 WHERE col1 = ? AND col2 = ?。

第三部分：何时需要/无需索引

1. 什么时候需要创建索引？

        创建索引的核心目的是优化查询性能，因此以下场景适合创建索引：

  1. 查询条件中频繁使用的字段

    • 如果某个字段经常出现在 WHERE、JOIN、GROUP BY 或 ORDER BY 中，应该为该字段创建索引。例如：
SELECT * FROM orders WHERE customer_id = 1001;
    • 为 customer_id 创建索引可以显著提升查询速度。

  2. 数据量大的表

    • 对于大表，如果没有索引，查询时需要进行全表扫描，这会严重拖慢性能。
    • 例如，有一张订单表有上百万条数据，为订单号字段 order_id 创建索引，可以极大提高查找效率。

  3. 高频查询的字段

    • 如果一个字段经常用于查询，即便表数据量不大，也可以考虑创建索引，因为优化高频操作的效率尤为重要。

  4. 排序和分组操作的字段

    • 如果查询中包含排序（ORDER BY）或分组（GROUP BY），为相关字段创建索引可以加速操作。
SELECT product_id, COUNT(*) FROM sales GROUP BY product_id;

2. 什么时候不需要创建索引？

索引虽然有助于查询，但也有成本，以下情况不适合创建索引：

  1. 小表或结果集较大的查询

    • 如果表的数据量很小（例如几十条数据），索引的优化效果微乎其微，反而会增加维护成本。
    • 类似地，如果查询结果总是返回表的大部分数据（如 80% 以上），索引优化的意义不大。

  2. 更新频繁的字段

    • 索引需要在数据修改时同步更新，因此对于频繁更新的字段，创建索引可能导致写入性能下降。
    • 例如日志表中的 last_updated_time 字段频繁更新，此时不建议为其创建索引。

  3. 重复性高的字段

    • 如果字段的值高度重复，索引会失去意义。例如：性别字段 gender（只有男或女）在查询中不建议单独建立索引。

  4. 临时表或测试表

    • 临时数据表通常存活时间短，查询次数少，因此无需索引。

第四部分：优化索引的方法

        索引的设计和使用需要考虑性能和实际需求，以下是几种常见的优化索引的方法：

1. 前缀索引优化

场景和原理

  • 适用场景：当字段值较长（如字符串类型），且前缀部分可以区分大多数记录时，使用前缀索引既能节省存储空间，又能加速查询。
  • 原理：前缀索引只记录字段值的前 N 个字符，降低了索引的大小，但仍能起到查询加速的作用。

优化步骤

  1. 选择合适的前缀长度
  2. 选择长度时，需要保证前缀的区分度（即前缀的唯一性较高）。可以通过以下查询评估： 
    SELECT COUNT(DISTINCT LEFT(column_name, N)) / COUNT(*) AS prefix_selectivity FROM table_name;
    • 如果区分度接近 1，说明前缀长度合适。
  3. 创建前缀索引
    • 使用 CREATE INDEX 指定前缀长度： 
      CREATE INDEX idx_prefix ON users (email(10));

适用场景的示例
        假设有一个邮件用户表，每个用户的邮箱 email 字段长度不一，且查询通常只匹配前缀部分：

SELECT * FROM users WHERE email LIKE 'john%';

使用前缀索引可显著提升效率。

2. 覆盖索引优化

定义与优点

  • 覆盖索引：当索引本身包含了查询所需的全部字段，无需回表查询，即称为覆盖索引。
  • 优点：减少磁盘 I/O 和查询时间。

如何实现覆盖索引

  1. 设计包含所有查询字段的索引

    • 例如，针对以下查询： 
      SELECT id, name FROM employees WHERE department_id = 10;
      可以创建覆盖索引： 
      CREATE INDEX idx_covering ON employees (department_id, id, name);
    • 索引中的字段顺序很重要，应优先按查询条件出现的字段排列。

  2. 使用查询分析工具

    • 使用 EXPLAIN 分析查询是否使用了覆盖索引： 
      EXPLAIN SELECT id, name FROM employees WHERE department_id = 10;

3. 主键索引最好是自增的

自增主键的优势

  1. 避免页分裂：
    • 自增主键的值是递增的，因此每次插入数据时，新记录会追加到索引的最后一个叶子节点，避免了频繁的页分裂。
  2. 提升插入效率：
    • 自增主键的插入是顺序的，减少了磁盘 I/O。

非自增主键的问题

  • 随机插入导致性能下降：
    • 如果主键是随机值（如 UUID），新数据可能插入到索引的任意位置，导致频繁的页分裂和性能下降。

4. 防止索引失效

什么是索引失效？

  • 当查询无法使用已有索引时，称为索引失效。这会导致查询退化为全表扫描，性能显著下降。

常见导致索引失效的场景

  1. 使用函数或计算操作

    • 如以下查询会导致索引失效： 
      SELECT * FROM orders WHERE YEAR(order_date) = 2023;
      • 解决方法：将计算移到索引之外： 
        SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

  2. 类型不匹配

    • 如果索引字段和查询条件的类型不一致，会导致索引失效。 
      SELECT * FROM users WHERE phone_number = 12345; -- phone_number 是字符串类型
      • 解决方法：确保类型一致： 
        SELECT * FROM users WHERE phone_number = '12345';

  3. 查询条件中使用 OR

    • 如果 OR 中的字段未全部使用索引，会导致索引失效。 
      SELECT * FROM employees WHERE department_id = 10 OR name = 'Alice';
      • 解决方法：改为使用 UNION，确保每个查询条件单独使用索引： 
        SELECT * FROM employees WHERE department_id = 10 UNION SELECT * FROM employees WHERE name = 'Alice';

  4. 模糊查询中通配符的位置

    • 以下查询会导致索引失效： 
      SELECT * FROM products WHERE name LIKE '%phone';
      • 解决方法：避免通配符在前，或者考虑全文索引： 
        SELECT * FROM products WHERE name LIKE 'iPhone%';

总结
        索引的优化不仅是设计阶段的任务，在实际使用中还需定期监控和调整，避免失效或过度索引，确保系统性能最佳。

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

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

相关文章

ROS机器视觉入门：从基础到人脸识别与目标检测

ROS机器视觉入门：从基础到人脸识别与目标检测

前言 从本文开始&#xff0c;我们将开始学习ROS机器视觉处理&#xff0c;刚开始先学习一部分外围的知识&#xff0c;为后续的人脸识别、目标跟踪和YOLOV5目标检测做准备工作。我采用的笔记本是联想拯救者游戏本&#xff0c;系统采用Ubuntu20.04&#xff0c;ROS采用noetic。 颜…
阅读更多...
电子电气架构 ---漫谈车载网关

电子电气架构 ---漫谈车载网关

我是穿拖鞋的汉子，魔都中坚持长期主义的汽车电子工程师。 老规矩，分享一段喜欢的文字，避免自己成为高知识低文化的工程师： 所有人的看法和评价都是暂时的，只有自己的经历是伴随一生的，几乎所有的担忧和畏惧，都是来源于自己的想象，只有你真的去做了，才会发现有多快乐。…
阅读更多...
@Autowired与构造器注入区别，为什么spring推荐使用构造注入而不是Autowired？

@Autowired与构造器注入区别，为什么spring推荐使用构造注入而不是Autowired？

目录 1.简介 2.了解两种注入方式的全过程 2.1 Autowired字段注入 2.2 构造函数注入 3.使用autowired注解注入有以下问题 3.1空指针异常 3.2测试不友好 4.使用Lombok去简化构造函数注入的臃肿代码 5.小结 5.1注解注入 5.2构造函数注入 1.简介 使用Spring开发时&#…
阅读更多...
优化注意力层提升 Transformer 模型效率：通过改进注意力机制降低机器学习成本

优化注意力层提升 Transformer 模型效率：通过改进注意力机制降低机器学习成本

Transformer 架构由 Vaswani 等人在 2017 年发表的里程碑式论文《Attention Is All You Need》中首次提出&#xff0c;如今已被广泛认为是过去十年间最具开创性的科学突破之一。注意力机制是 Transformer 的核心创新&#xff0c;它为人工智能模型提供了一种全新的方法&#xff…
阅读更多...
在Excel中处理不规范的日期格式数据并判断格式是否正确

在Excel中处理不规范的日期格式数据并判断格式是否正确

有一个Excel表&#xff0c;录入的日期格式很混乱&#xff0c;有些看着差不多&#xff0c;但实际多一个空格少一个字符很难发现&#xff0c;希望的理想格式是 1980-01-01&#xff0c;10位&#xff0c;即&#xff1a;“YYYY-mm-dd”&#xff0c;实际上数据表中这样的格式都有 19…
阅读更多...
医工交叉入门书籍分享：Transformer模型在机器学习领域的应用｜个人观点·24-11-22

医工交叉入门书籍分享：Transformer模型在机器学习领域的应用｜个人观点·24-11-22

小罗碎碎念 今天给大家推荐一本入门书籍。 这本书由Uday Kamath、Kenneth L. Graham和Wael Emara撰写&#xff0c;深入探讨了Transformer模型在机器学习领域的应用&#xff0c;特别是自然语言处理&#xff08;NLP&#xff09;。 原文pdf已经上传至知识星球的【入门书籍】专栏&…
阅读更多...
SpringCloud Gateway转发请求到同一个服务的不同端口

SpringCloud Gateway转发请求到同一个服务的不同端口

SpringCloud Gateway默认不支持将请求路由到一个服务的多个端口 本文将结合Gateway的处理流程&#xff0c;提供一些解决思路 需求背景 公司有一个IM项目&#xff0c;对外暴露了两个端口8081和8082&#xff0c;8081是springboot启动使用的端口&#xff0c;对外提供一些http接口…
阅读更多...
Parker派克防爆电机在实际应用中的安全性能如何保证？

Parker派克防爆电机在实际应用中的安全性能如何保证？

Parker防爆电机确保在实际应用中的安全性能主要通过以下几个方面来保证&#xff1a; 1.防爆外壳设计&#xff1a;EX系列电机采用强大的防爆外壳&#xff0c;设计遵循严格的防爆标准&#xff0c;能够承受内部可能发生的爆炸而不破损&#xff0c;利用间隙切断原理&#xff0c;防…
阅读更多...
虚拟形象+动作捕捉：解锁品牌N种营销玩法

虚拟形象+动作捕捉：解锁品牌N种营销玩法

近年来&#xff0c;随着Z世代年轻人对于二次元文化的热爱&#xff0c;各种二次元内容频频出圈。为了吸引年轻观众的注意&#xff0c;虚拟IP形象成为了品牌营销的“新宠”与“利器”为品牌踏入元宇宙蓝海提供了关键的切入点。在此背景下虚拟形象动作捕捉技术的组合应用方式正成为…
阅读更多...
空间计算、物理计算、实时仿真与创造拥有「自主行为」的小狗 | 播客《编码人声》

空间计算、物理计算、实时仿真与创造拥有「自主行为」的小狗 | 播客《编码人声》

「编码人声」是由「RTE开发者社区」策划的一档播客节目&#xff0c;关注行业发展变革、开发者职涯发展、技术突破以及创业创新&#xff0c;由开发者来分享开发者眼中的工作与生活。 虚拟世界与现实世界的界限逐渐模糊&#xff0c;已然成为不争的事实。但究竟哪些曾经的幻想已然…
阅读更多...
影响电阻可靠性的因素

影响电阻可靠性的因素

一、影响电阻可靠性的因素&#xff1a; 影响电阻可靠性的因素有温度系数、额定功率&#xff0c;最大工作电压、固有噪声和电压系数 &#xff08;一&#xff09;温度系数 电阻的温度系数表示当温度改变1摄氏度时&#xff0c;电阻阻值的相对变化&#xff0c;单位为ppm/C.电阻温度…
阅读更多...
JAVA后端如何调用百度的身份证识别API

JAVA后端如何调用百度的身份证识别API

大家好&#xff0c;我是 程序员码递夫 。 今天给大家分享的是 JAVA后台如何调用百度的身份证识别API。 1、前言 我们做APP开发时常遇到 身份证认证或资质认证的 需求&#xff0c; 通过上传身份证照片是个常用的操作&#xff0c; 后台对上传的身份证照信息进行识别&#xff0…
阅读更多...
Go语言进阶依赖管理

Go语言进阶依赖管理

1. Go语言进阶 1.1 Goroutine package mainimport ("fmt""time" )func hello(i int) {println("hello goroutine : " fmt.Sprint(i)) }func main() {for i : 0; i < 5; i {go func(j int) { hello(j) }(i) // 启动一个新的 goroutine&…
阅读更多...
基于Java Springboot高考志愿填报辅助系统

基于Java Springboot高考志愿填报辅助系统

一、作品包含 源码数据库全套环境和工具资源部署教程 二、项目技术 前端技术&#xff1a;Html、Css、Js、Vue、Element-ui 数据库&#xff1a;MySQL 后端技术&#xff1a;Java、Spring Boot、MyBatis 三、运行环境 开发工具&#xff1a;IDEA/eclipse 数据库&#xff1a;…
阅读更多...
autoware（2）运行自己的数据集

autoware（2）运行自己的数据集

上一节完成了autoware.ai的安装和编译跑通了demo数据集&#xff0c;本将自己录制的数据包用于测试 1.修改点云地图 将加载点云地图的my_map.launch文件复制并命名为my_map_test.launch&#xff0c; &#xff08;1&#xff09;point cloud处替代原来的点云地图为自己的&#…
阅读更多...
el-select 和el-tree二次封装

el-select 和el-tree二次封装

前言 本文章是本人在开发过程中&#xff0c;遇到使用树形数据&#xff0c;动态单选或多选的需求&#xff0c;element中没有这种组件&#xff0c;故自己封装一个&#xff0c;欢迎多多指教 开发环境&#xff1a;element-UI、vue2 组件效果 单选 多选 组件引用 <treeselec…
阅读更多...
【LeetCode热题100】栈

【LeetCode热题100】栈

这道题一共记录了关于栈的5道题目&#xff1a;删除字符串中所有相邻重复项、比较含退格的字符串、基本计算器II、字符串解码、验证栈序列。 class Solution { public:string removeDuplicates(string s) {string ret;for(auto c : s){if(ret.size() 0 || c ! ret.back()) ret …
阅读更多...
《Python基础》之pip换国内镜像源

《Python基础》之pip换国内镜像源

目录 推荐镜像源网址&#xff1a; 方法一&#xff1a;手动换源 方法二&#xff1a;命令提示符指令换源 临时换源 推荐镜像源网址&#xff1a; 阿里云&#xff1a;Simple Indexhttp://mirrors.aliyun.com/pypi/simple/ 华为云&#xff1a;Index of python-local https://m…
阅读更多...
全面击破工程级复杂缓存难题

全面击破工程级复杂缓存难题

目录 一、走进业务中的缓存 &#xff08;一&#xff09;本地缓存 &#xff08;二&#xff09;分布式缓存 二、缓存更新模式分析 &#xff08;一&#xff09;Cache Aside Pattern&#xff08;旁路缓存模式&#xff09; 读操作流程 写操作流程 流程问题思考 问题1&#…
阅读更多...
Kafka 分区分配及再平衡策略深度解析与消费者事务和数据积压的简单介绍

Kafka 分区分配及再平衡策略深度解析与消费者事务和数据积压的简单介绍

Kafka&#xff1a;分布式消息系统的核心原理与安装部署-CSDN博客 自定义 Kafka 脚本 kf-use.sh 的解析与功能与应用示例-CSDN博客 Kafka 生产者全面解析&#xff1a;从基础原理到高级实践-CSDN博客 Kafka 生产者优化与数据处理经验-CSDN博客 Kafka 工作流程解析&#xff1a…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023