MySQL中索引全详解

news/2024/11/24 3:35:23/

第一部分：什么是索引

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

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

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

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

第二部分：索引的分类

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

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.ppmy.cn/news/1549435.html

相关文章

如何更改手机GPS定位

如何更改手机GPS定位

你是否曾想过更改手机GPS位置以保护隐私、玩游戏或访问受地理限制的内容&#xff1f;接下来我将向你展示如何使用 MagFone Location Changer 更改手机GPS 位置&#xff01;无论是在玩Pokmon GO游戏、发布社媒贴子&#xff0c;这种方法都快速、简单且有效。 第一步&#xff1a;下…
阅读更多...
Python爬虫 | Scrapy 爬虫框架学习

Python爬虫 | Scrapy 爬虫框架学习

Scrapy 爬虫框架学习 Scrapy是一个快速的、高层次的web爬取和web抓取框架&#xff0c;用于抓取web站点并从页面中提取结构化的数据。 安装Scrapy 首先&#xff0c;需要安装Scrapy。可以通过pip安装&#xff1a; pip install scrapy创建Scrapy项目 创建一个新的Scrapy项目&…
阅读更多...
oracle19c RAC+ADG+OGG全流程安装部署

oracle19c RAC+ADG+OGG全流程安装部署

oracle19c RACADGOGG部署 RACADGOGGIP192.168.40.30/31/32/33/34192.168.40.40192.168.40.50数据库版本Oracle 19.3.0Oracle 19.3.0Oracle 19.3.0主机名hfdb1/hfdb2hfdb40hfogg操作系统REHL7.6REHL7.6REHL7.6数据库类型RACFSFSDB_UNIQUE_NAMEhfdbdghfdbhfoggDB_NAMEhfdbhfdbhf…
阅读更多...
从0开始学习Linux——Shell编程详解【03】

从0开始学习Linux——Shell编程详解【03】

期目录&#xff1a; 从0开始学习Linux——简介&安装 从0开始学习Linux——搭建属于自己的Linux虚拟机 从0开始学习Linux——文本编辑器 从0开始学习Linux——Yum工具 从0开始学习Linux——远程连接工具 从0开始学习Linux——文件目录 从0开始学习Linux——网络配置 从0开始…
阅读更多...
Unity Shader常见函数 内置Built-in/URP等效函数

Unity Shader常见函数 内置Built-in/URP等效函数

简介&#xff1a; Unity Shader的URP中的函数与Built-in中的是不一样的&#xff0c;升级URP之后&#xff0c;基本都提供了平替的函数 Built-in 内置渲染管线函数URP 通用渲染函数TRANSFORM_TEX(uv, textureName)TRANSFORM_TEX(uv, textureName)tex2D, tex2Dlod, 等SAMPLE_TEXT…
阅读更多...
flume-将日志采集到hdfs

flume-将日志采集到hdfs

看到hdfs大家应该做什么&#xff1f; 是的你应该去把集群打开&#xff0c; cd /export/servers/hadoop/sbin 启动集群 ./start-all.sh 在虚拟机hadoop02和hadoop03上的conf目录下配置相同的日志采集方案&#xff0c;‘ cd /export/servers/flume/conf 切换完成之后&#…
阅读更多...
Github工作流

Github工作流

GitHub 工作流 是一种专门为 GitHub 上的代码协作和版本控制而设计的工作流&#xff0c;它强调通过 **拉取请求&#xff08;Pull Request&#xff0c;PR&#xff09;** 来管理代码的合并和审查。GitHub 工作流通常涉及到使用 **分支** 来进行功能开发和修复&#xff0c;并通过 …
阅读更多...
缓冲区的奥秘：解析数据交错的魔法

缓冲区的奥秘：解析数据交错的魔法

目录 一、理解缓存区的好处 &#xff08;一&#xff09;直观性的理解 &#xff08;二&#xff09;缓存区的好处 二、经典案例分析体会 &#xff08;一&#xff09;文件读写流&#xff08;File I/O Buffering&#xff09; BufferedOutputStream 和 BufferedWriter 可以加快…
阅读更多...
最新文章

Copyright @ 2022~2023