当前位置： 首页 > article >正文

MySQL之索引基本知识

article 2024/9/29 19:50:03

什么是索引？

索引是数据库中用于加速查询操作的结构，是一种对象 。它通过为表中的一列或多列创建一个排序结构，使数据库可以更快地找到目标数据，而不必遍历整个表的所有行。索引类似于书的目录，通过目录你可以迅速定位到某个主题或章节，而不需要一页一页地翻阅整本书。也就是表TABLE中某个列对应的索引

索引的作用

  • 提高查询速度：索引极大地提高了 SELECT 查询的性能。没有索引时，数据库需要遍历表中的所有行（称为全表扫描）才能找到目标数据。而有了索引，数据库可以快速定位到数据的存储位置。
  • 加速排序和分组：索引也有助于加快排序（ORDER BY）、分组（GROUP BY）等操作，因为索引通常会以排序好的方式存储数据。
  • 提高 JOIN 操作性能：当进行表之间的连接（JOIN）时，索引可以加速连接的过程。

 

 索引的分类：

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT

主键索引：

  • 每张表只能有一个主键索引，它强制列的值必须唯一且不为空。
  • 主键索引是自动创建的，通常在你为某列设置主键（PRIMARY KEY）时就会创建。
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,  -- 主键
    name VARCHAR(100),
    age INT
);

唯一索引（Unique Index）：

  • 强制列的值唯一，但允许有空值。
  • 创建语法示例：
  •  自动创建：当你在创建表时，指定某一列为 UNIQUE，MySQL 会自动为该列创建一个唯一索引
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,  -- 这里会为 email 列自动创建唯一索引
    PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE UNIQUE INDEX index_name ON table_name(column_name);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(100),
    UNIQUE (email)  -- 唯一索引
);

普通索引（Normal Index）：

  • 普通索引用于加速数据访问，但没有唯一性要求。它可以在一列或多列上创建。
  • 创建语法示例：
CREATE INDEX index_name ON table_name(column_name);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX (customer_id)  -- 常规索引
);

全文索引（Full-Text Index）：

  • 全文索引用于搜索大型文本字段。它主要用于处理诸如文章、博客内容等大文本数据的关键词查找。
  • MySQL 从 5.6 版本开始支持 InnoDB 的全文索引。
  • 创建语法示例：

创建表之后：

CREATE FULLTEXT INDEX index_name ON table_name(column_name);

创建表时： 

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)  -- 全文索引
);

 索引的数据结构

索引结构描述
B+Tree 索引（默认）最常见的索引类型，大部分引擎都支持 B+ 树索引。
Hash 索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
R-tree（空间索引）空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
Full-text（全文索引）一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene、Solr、ES。

BTree：

B-Tree（B树）的介绍

B-Tree（Balance Tree，平衡树）是一种自平衡的搜索树数据结构，广泛应用于数据库和文件系统中来组织和管理数据。B+Tree 是 B-Tree 的一种变体，是数据库索引系统中最常见的实现。

B-Tree 的特点

  1. 多路平衡搜索树：与二叉树不同，B-Tree 是一棵多路搜索树，即每个节点可以有多个子节点和多个键值。
  2. 平衡树：B-Tree 始终保持平衡，每个叶子节点到根节点的距离相同，从而避免了性能下降。
  3. 节点存储多个元素：每个节点可以存储多个键值，因此能够减少磁盘 I/O 操作，适合大规模数据的索引。

演示网站B-Tree Visualization 

详细演示过程B-Tree以及创建过程演示_b树的建立过程-CSDN博客

概括一下：度为5表示value元素可以有4个元素，指针有4个。当value元素有5个时，向上分裂 。

B+Tree：

B+Tree 的特点

  1. 非叶子节点只作为索引使用：

    • B+Tree 中的非叶子节点仅包含索引信息（键值），并不存储实际数据。它们的作用是引导查找过程。
    • 非叶子节点中存储的是用于路由到下层节点的键值。每个键值指向下一级节点，帮助缩小查找范围。

  2. 叶子节点存储所有数据：

    • 所有数据记录都存储在叶子节点中，叶子节点构成了一条有序的链表。每个叶子节点包含真实的数据或指向真实数据的指针，所有叶子节点之间通过链表连接，便于范围查询。
    • 这意味着从根节点到叶子节点的所有路径长度相同，保证了查询的稳定性和效率。

  3. 叶子节点按顺序排列：

    • B+Tree 的叶子节点通过指针连接成一个双向链表。由于数据存储在叶子节点中且按顺序排列，所以 B+Tree 支持高效的范围查询，可以通过遍历叶子节点快速获取范围内的所有数据。

  4. 所有数据都在叶子节点中：

    • 由于数据仅存储在叶子节点，非叶子节点并不存储数据，只存储索引值。因此 B+Tree 的树高比 B-Tree 更小，数据检索时经过的节点层数更少，效率更高。

B+Tree 的结构

  • 根节点：位于树的最上层，包含多个索引键值。根据这些键值，决定进入哪个子节点继续查找。
  • 非叶子节点：包含多个索引值（用于路由），每个值指向一个子节点。索引值不会重复出现在不同节点中。
  • 叶子节点：存储实际数据或数据的指针，并按键值有序排列。所有叶子节点通过指针形成链表结构，方便顺序查找。

 相对于B-Tree区别:

1. 所有的数据都会出现在叶子节点

2. 叶子节点形成一个单向链表

过程：

 

 在MySQL中：

非叶子结点只发挥了索引的作用而叶子结点是存储的数据 

Hash：

Hash索引特点

1. 哈希索引只能用于对等比较(=，in)，不支持范围查询(between，>，<，..)

2. 无法利用索引完成排序操作

3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+树索引 

为什么InnoDB存储引擎选择使用B+tree索引结构? 

相对于二叉树，层级更少，搜索效率高

对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低; 

相对Hash索引，B+tree支持范围匹配及排序操作 

 聚集索引和非聚集索引

1. 聚集索引（Clustered Index）

  • 定义：在聚集索引中，索引结构的叶子节点包含了实际的数据行，这意味着数据行根据索引列的顺序存储在同一块物理存储空间中。因此，聚集索引与数据存储在一起。聚集索引决定了数据在表中的物理存储顺序，因此表中的数据行按索引列的顺序进行存储。每个表只能有一个聚集索引，因为物理顺序只能按照一种方式存储。

  • 特性：

    • 数据行的物理顺序与索引顺序相同。
    • 索引键的值唯一时，聚集索引可以提高查询的效率，特别是在范围查询或排序的情况下。
    • 聚集索引通常会被创建在主键列上（如 PRIMARY KEY 会自动创建聚集索引）。
    • 示例：假设有一个聚集索引创建在用户表的 id 列上，表中的数据会按 id 列的值顺序进行物理存储。

  • 缺点：

    • 每个表只能有一个聚集索引。
    • 插入、删除或更新会影响表的物理排序，因此在有大量频繁的插入或更新时，可能会影响性能。

  • 创建聚集索引的语法：

    CREATE CLUSTERED INDEX idx_name ON table_name (column_name);

2. 非聚集索引（Non-clustered Index）

  • 定义：非聚集索引不会影响表中数据的物理存储顺序，而是创建一个独立的索引结构，该索引结构包含列的索引值和指向实际数据行的指针。一个表可以有多个非聚集索引。

  • 特性：

    • 数据的物理存储顺序与非聚集索引无关。
    • 非聚集索引是一个单独的结构，它存储索引列的值，并通过指针（通常是行标识符或聚集索引键）指向实际的数据行。
    • 示例：如果在 name 列上创建了非聚集索引，数据并不会按照 name 列进行物理存储，但索引中会存储 name 列的值，并通过指针指向该行的实际数据位置。

  • 缺点：

    • 非聚集索引需要额外的存储空间，因为它们是独立于数据表的结构。
    • 当索引列和实际查询的列不完全一致时，可能需要通过索引查找到指针，然后再去实际表中查找数据（称为回表操作），这会影响查询性能。

  • 创建非聚集索引的语法：

    CREATE INDEX idx_name ON table_name (column_name);

存储方式和索引的联系 

InnoDB 的存储方式：

当创建一张表（如 user 表），并使用 InnoDB 存储引擎时，会在磁盘上生成以下文件：

  • t_user.ibd：用于存储 InnoDB data 表数据和索引。
  • t_user.frm：用于存储 表的结构信息。

MyISAM 的存储方式：

当创建一张表（如 user 表），并使用 MyISAM 存储引擎时，会在磁盘上生成以下文件：

  • t_user.MYD：用于存储 表数据。
  • t_user.MYI：用于存储 表索引。
  • t_user.frm：用于存储 表的结构信息。（MySQL8.0后，不在生成）

二级索引（非聚集索引） 

二级索引（Secondary Index），也称为非聚集索引（Non-clustered Index），是在数据库表中除了主键或聚集索引外，针对其他列创建的索引。 

二级索引的概念和作用

  • 概念：二级索引是建立在表中非主键或非聚集索引列上的索引。在查询涉及到这些列时，二级索引可以帮助数据库更快地定位数据行，而不必遍历整个表。

  • 作用：当我们对非主键列执行频繁的查询、过滤、排序时，二级索引能够大大提高查询性能，因为它提供了快速查找的路径，而无需扫描整个表。

InnoDB 中的二级索引

在 InnoDB 中，表默认使用 聚集索引，聚集索引的叶子节点存储的是实际的数据行。对于非主键列，InnoDB 会创建 二级索引，即 非聚集索引，其叶子节点存储的是指向聚集索引键（主键值）的指针。

  • 存储结构：InnoDB 中的二级索引并不直接存储数据行，而是存储主键的值。通过二级索引找到数据时，InnoDB 首先会通过二级索引定位到对应的主键值，然后再通过这个主键去聚集索引中查找实际数据。

    • 二级索引指向主键：如果查询是基于非主键列的，那么数据库会先查找二级索引，通过二级索引找到主键值，然后再通过主键值定位到实际的行数据。这一过程称为 回表 操作（因为需要先查找索引再回到聚集索引查找数据）。

MyISAM 中的二级索引

在 MyISAM 中，由于没有聚集索引的概念，所有索引（包括主键和二级索引）都被实现为 非聚集索引。

  • 存储结构：MyISAM 中的所有索引（包括主键和二级索引）都是非聚集的，也就是说，索引的叶子节点存储的是指向数据行的指针。二级索引的叶子节点直接存储的是指向数据存储位置的指针。

    • 因此，当查询 MyISAM 中的非主键列时，数据库引擎通过二级索引可以直接定位到数据行，而无需回表。

覆盖索引：

覆盖索引，是指在查询时，所需的数据完全可以从索引中获取，而不需要回表查询数据行。换句话说，如果查询的所有列都包含在一个索引中，那么数据库可以直接从索引中返回结果，而无需从表中读取实际数据行。这种情况被称为索引覆盖。

索引覆盖的概念

在使用索引覆盖的查询中，索引不仅仅存储索引列本身的信息，还包含了查询所需的所有数据，因此无需访问表中的实际数据行，可以直接从索引中获取查询结果。这种优化方式可以显著提高查询效率，因为减少了 I/O 操作，即不需要进行回表操作。

索引覆盖的工作原理

当我们创建一个索引时，索引的叶子节点存储索引列的值，可能还存储主键值或行指针（取决于存储引擎）。在进行查询时，如果查询中涉及的所有列都包含在该索引中，那么数据库只需要遍历索引，而不需要再回到表中查找其他数据。

索引覆盖的示例

假设我们有一张表 users：

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    PRIMARY KEY (id)
);

如果我们为 name 和 age 列创建一个联合索引：

CREATE INDEX idx_name_age ON users(name, age);

现在如果我们执行以下查询：

SELECT name, age FROM users WHERE name = 'Alice';

在这个查询中，所有的查询列（name 和 age）都包含在 idx_name_age 索引中，因此数据库可以直接通过索引获取 name 和 age 的值，而不需要回表查找其他列的数据。这就是索引覆盖的情况。

但是，如果查询如下：

SELECT name, age, city FROM users WHERE name = 'Alice';

在这个查询中，name 和 age 列可以从 idx_name_age 索引中获取，但 city 列不在索引中，因此数据库需要先通过索引找到符合条件的行，然后再回到表中查找 city 列的值。这种情况不是索引覆盖。

索引下推：

索引下推（Index Condition Pushdown，简称 ICP）是 MySQL 在 5.6 版本中引入的一项优化技术，用于提高查询性能。它的核心思想是将部分查询的过滤操作尽量推到索引扫描的过程中，而不是等到回表后再进行过滤，从而减少回表的次数和范围。

背景问题

在没有索引下推之前，当 MySQL 使用索引进行查询时，即使索引只涉及一部分查询条件，数据库也会先通过索引找到可能符合条件的记录，然后回表获取完整的行数据，再在这些行数据中应用所有的 WHERE 条件进行过滤。这样会导致多余的回表操作，尤其是当大量数据不符合条件时，效率较低。

索引下推的工作原理

通过索引下推，MySQL 可以在索引扫描阶段就应用部分 WHERE 条件，减少不必要的回表操作。具体来说，当一个查询条件中的某些字段在索引中已经包含时，MySQL 会先利用索引直接筛选这些条件，而不是直接回表。这会显著减少不必要的回表操作，提高查询效率。

索引下推的示例

假设有一张表 users：

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    PRIMARY KEY (id),
    INDEX idx_name_age_city (name, age, city)
);

有一个组合索引 idx_name_age_city 包含 name, age, city 列。现在我们执行以下查询：

SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'New York';

索引下推的注意事项

  • 索引下推并不是对所有查询都有效。它的效果依赖于查询条件与索引的相关性，尤其是在联合索引中，只有索引中包含的字段才能应用索引下推。
  • 索引下推并不会改变索引的创建方式，而是 MySQL 的一种内部优化策略。因此，创建索引时仍然需要考虑常规的优化原则，如选择合适的列顺序。

索引下推生效的前提条件

使用复合索引（联合索引）

索引下推的优化通常是在复合索引（联合索引）上发生的。在创建复合索引时，MySQL 会根据索引中的列顺序逐个匹配查询条件。当查询条件涉及多个列，且这些列构成了复合索引时，索引下推能够在扫描索引的过程中逐步过滤不符合条件的记录。

  • 例如，如果你创建了以下复合索引： 
    CREATE INDEX idx_name_age_city ON users(name, age, city);

    查询条件包含 name, age, city 时，MySQL 可以在索引扫描过程中应用索引下推优化。

索引列顺序匹配查询条件

对于复合索引，索引下推生效的一个关键条件是查询条件要按照索引列的顺序匹配。如果查询条件中的列顺序和索引列顺序不一致，索引下推可能无法完全生效。

  • 例如，对于复合索引 idx_name_age_city (name, age, city)，以下查询可以使用索引下推：

    SELECT * FROM users WHERE name = 'Alice' AND age = 25;

    因为查询条件按照索引的顺序使用了 name 和 age 列。

  • 但如果查询只涉及 city 列：

    SELECT * FROM users WHERE city = 'New York';

    索引下推无法生效，因为 city 列在索引中的顺序较后，不能单独使用。

 单列索引和复合索引：

单列索引（Single-Column Index）

  • 定义：单列索引是对表中的某个单独列创建的索引。这种索引只对该列的查询、排序、过滤等操作进行加速优化。

示例： 假设有一张表 users：

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    PRIMARY KEY (id)
);

如果我们对 name 列创建单列索引：

CREATE INDEX idx_name ON users(name);

这个索引只会对 name 列进行优化，因此查询如下内容时索引会生效：

SELECT * FROM users WHERE name = 'Alice';

但是，如果查询如下内容：

SELECT * FROM users WHERE name = 'Alice' AND city = 'New York';

复合索引（Composite Index / Multi-Column Index）

  • 定义：复合索引是在多个列上创建的索引，用于优化涉及多个列的查询。复合索引将多个列的组合当作一个整体进行索引，能够加速多个列的组合查询。

示例： 如果我们为 name 和 city 列创建一个复合索引：

CREATE INDEX idx_name_city ON users(name, city);

这个索引可以优化如下查询：

SELECT * FROM users WHERE name = 'Alice' AND city = 'New York';

同时，这个索引也可以优化以下查询，因为 name 是复合索引的最左列：

SELECT * FROM users WHERE name = 'Alice';

但如果查询条件只包含 city 列，复合索引将无法使用，因为 city 不是复合索引的最左列：

SELECT * FROM users WHERE city = 'New York';

面试：


http://www.kler.cn/news/325026.html

相关文章：

  • 大数据是不是需要用很多ip
  • js逆向——webpack实战案例(一)
  • Java-数据类型与变量
  • Cholesky分解
  • Java 常用的一些Collection的实现类
  • 服务器数据恢复—raid磁盘故障导致数据库文件损坏的数据恢复案例
  • JNI动态注册
  • 【AI大模型】Function Calling
  • 17年数据结构考研真题解析
  • prompt攻击与防范
  • Arrays常用API
  • Java（基本数据类型）（￣︶￣）↗
  • Python中的“锁”艺术：解锁Lock与RLock的秘密
  • Python酷玩之旅_如何连接MySQL（mysql-connector-python）
  • 【Power Compiler手册】13.UPF多电压设计实现（5）
  • 图像处理基础知识点简记
  • HTML5实现好看的唐朝服饰网站模板源码2
  • [Excel VBA]如何使用VBA自动生成图表
  • [论文翻译]基于多模态特征融合的Android恶意软件检测方法
  • 初识Linux以及Linux的基本命令
  • 栏目二：Echart绘制动态折线图+柱状图
  • HCIP——HCIA回顾
  • 华为OD机试 - 对称美学（Python/JS/C/C++ 2024 E卷 100分）
  • MySQL实现按分秒统计数据量
  • android 身份证取景框
  • Python Web 与区块链集成的最佳实践：智能合约、DApp与安全
  • 前端工程记录：Vue2 typescript项目升级Vue3
  • ppt压缩有什么简单方法？压缩PPT文件的几种方法
  • Qt_对话框QDialog的介绍
  • Docker搭建 RabbitMQ 最新版