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

Mysql(4)—数据库索引

article 2024/10/19 8:12:36

一、关于索引

1.1 简介

数据库索引是数据库管理系统中用于提高数据检索效率的数据结构。索引类似于书籍中的索引，它允许用户快速找到数据，而不需要扫描整个表。

1223​

‍

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

‍

1.2 发展

  1. 早期阶段
  • 无索引：最初，数据库系统没有索引，所有数据检索都依赖全表扫描，效率低下。
  • 简单索引：随着数据量的增加，简单的索引机制（如线性列表或链表）被引入，用于加速基本的查找操作。

‍

  1. B-树的引入
  • B-树索引：1970年代，B-树被提出并成为主流的索引结构。它支持高效的查找、插入和删除操作，适合磁盘存储结构，广泛应用于关系数据库。

‍

  1. 多种索引结构的出现
  • 哈希索引：用于快速等值查询，提供了比B-树更快的查找速度，但不支持范围查询。
  • 位图索引：在数据仓库等场景中被提出，适用于低基数字段的快速检索。

‍

  1. 全文索引的发展
  • 全文检索：随着文本数据的激增，数据库系统开始支持全文索引，以便高效处理大规模文本数据的查询。

‍

  1. 新型数据库和索引
  • NoSQL数据库：在大数据和分布式系统的需求推动下，新的数据存储方案（如文档存储、键值存储）出现，带来了不同的索引策略。
  • 列式数据库：在数据分析和OLAP（联机分析处理）场景中，列式存储和相关的索引机制（如列簇索引）得到应用。

‍

  1. 智能索引和自适应索引
  • 自适应索引技术：现代数据库系统开始采用机器学习和自适应技术来优化索引策略，根据查询模式动态调整索引。

‍

  1. 未来趋势
  • 云数据库和大数据处理：随着云计算和大数据技术的发展，数据库索引的管理和优化也在不断演进，关注分布式索引和性能调优。

‍

1.3 背景

数据库索引的出现主要是为了解决随着数据量增长而导致查询性能下降的问题。在没有索引的情况下，数据库系统需要进行全表扫描来查找满足条件的数据行，这意味着它必须检查表中的每一行来确定是否符合查询条件。当表中只有少量数据时，这种操作可能不会对性能产生太大影响；但随着数据量的增长，全表扫描将变得非常耗时。

以下是促使索引技术发展的几个关键背景因素：

  1. 数据量的增长:

    • 随着信息技术的发展和数字化进程的加快，企业、组织乃至个人都产生了大量的数据。这些数据通常存储在关系型数据库或其他类型的数据库管理系统中。面对海量数据，如何高效地检索信息成为了一个亟待解决的问题。

  2. 提高查询效率的需求:

    • 业务需求不断推动了对于快速响应时间的要求。无论是在线交易处理(OLTP)还是数据分析(OLAP)，用户都期望能够迅速获得结果。因此，优化查询速度成为了数据库设计中的一个重要方面。

  3. 硬件资源的限制:

    • 尽管计算机硬件能力持续提升，但是相对于指数级增长的数据规模而言，硬件资源始终是有限的。通过使用索引来减少磁盘I/O操作次数，可以有效减轻CPU和内存的压力，从而更有效地利用现有硬件资源。

  4. 数据库理论的发展:

    • 数据库领域内的理论研究促进了各种新算法和技术的发明与应用。例如B树及其变种结构被广泛应用于构建高效的索引机制。同时，像哈希表这样的数据结构也被引入到某些特定场景下的索引实现中。

  5. 实际应用场景的需求:

    • 在很多具体的应用场景下，比如电子商务网站、社交网络平台等，存在着大量且频繁的读写请求。为了保证良好的用户体验，必须采取措施降低延迟，而合理地设置索引就是一种常见且有效的手段之一。

‍

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

‍

1.3 特点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
  • 可以给所有的 MySQL 列类型设置索引。
  • 可以大大加快数据的查询速度，这是使用索引最主要的原因。
  • 在实现数据的参考完整性方面可以加速表与表之间的连接。
  • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

‍

缺点

增加索引也有许多不利的方面，主要如下：

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

‍

1.4 使用建议

  • 选择合适的列：为经常出现在 WHERE 子句、JOIN 条件和 ORDER BY 子句中的列创建索引。
  • 避免过度索引：过多的索引会增加维护成本，影响性能。
  • 定期检查和优化：根据实际查询情况定期评估和调整索引策略。

‍

‍

二、索引分类

2.1 按唯一性分类

2.1.1 普通索引（INDEX）

简介

普通索引（也称为非唯一索引或简单索引）是 MySQL 中最基本的索引类型。它允许索引列中存在重复值，这意味着可以在同一列中有多个相同的值。普通索引的主要目的是加速对表中数据的检索，提高查询性能。

  • 定义：普通索引是最基本的索引类型，没有唯一性约束，可以包含重复值。
  • 优点：适用于任何查询，可以加速数据检索。
  • 使用场景：适合不需要强制唯一性的列，例如日志记录中的时间戳。

‍

特点
  1. 没有唯一性约束：普通索引允许列中的重复值。
  2. 查询加速：通过索引提高数据检索速度，尤其是在大表中。
  3. 适用范围广：可以应用于几乎所有的查询场景。
  4. 创建简单：普通索引的创建和管理相对简单。

‍

使用场景

普通索引适用于以下场景：

  • 当某个字段被频繁用于查询（如 WHERE​ 子句）时。
  • 当字段的值不需要唯一时，例如，用户的国家、城市等字段。

‍

创建普通索引的语法
CREATE INDEX index_name ON table_name (column_name);
  • index_name​：指定索引的名称。
  • table_name​：指定要创建索引的表名。
  • column_name​：指定要创建索引的列名。

‍

示例

以下是一个具体示例，演示如何在 MySQL 中创建和使用普通索引。

1. 创建示例表

首先，创建一个名为 employees​ 的表，该表包含一些员工信息：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    hire_date DATE
);

2. 插入示例数据

接下来，插入一些示例数据：

INSERT INTO employees (name, department, hire_date) VALUES
('Alice', 'HR', '2023-01-15'),
('Bob', 'IT', '2023-02-10'),
('Charlie', 'IT', '2023-03-05'),
('David', 'Finance', '2023-01-20'),
('Eve', 'HR', '2023-02-25');

3. 创建普通索引

现在，假设我们希望根据 department​ 列快速检索员工信息，我们可以在 department​ 列上创建一个普通索引：

CREATE INDEX idx_department ON employees (department);

4. 查询使用索引

创建索引后，我们可以使用以下查询来检索 IT​ 部门的所有员工：

SELECT * FROM employees WHERE department = 'IT';

由于我们在 department​ 列上创建了普通索引，MySQL 将利用该索引加速查询，快速找到符合条件的记录。

5. 查看索引信息

可以通过以下查询查看 employees​ 表中的索引信息：

SHOW INDEX FROM employees;

这将返回表中所有索引的详细信息，包括索引名称、列名、索引类型等。

‍

‍

2.1.2 唯一索引（UNIQUE）

简介

唯一索引（Unique Index）是一种特殊的索引类型，它保证了索引列中的所有值都是唯一的，即不允许出现重复值。唯一索引在保证数据完整性的同时，也能够提高查询性能。

  • 定义：唯一索引要求索引列中的每个值都是唯一的，不能有重复值。
  • 优点：除了加速查询外，确保数据的唯一性，维护数据完整性。
  • 使用场景：适合用户电子邮件、用户名等需要唯一性的字段。

‍

特点
  1. 唯一性约束：唯一索引不允许在索引列中插入重复值，这有助于维护数据的唯一性。
  2. 加速查询：与普通索引一样，唯一索引也能加速数据检索。
  3. 允许 NULL 值：在唯一索引的列中，可以有多个 NULL 值（在某些情况下，具体行为依赖于数据库的配置）。
  4. 适用于主键：主键本身就是一种唯一索引。

‍

使用场景

唯一索引适用于以下场景：

  • 当某个字段需要保证唯一性时，例如用户邮箱、用户名等。
  • 在多个字段组合的情况下，也可以使用唯一索引来确保组合值的唯一性。

‍

创建唯一索引的语法
CREATE UNIQUE INDEX index_name ON table_name (column_name);
  • index_name​：指定索引的名称。
  • table_name​：指定要创建索引的表名。
  • column_name​：指定要创建索引的列名。

‍

示例

以下是一个具体示例，演示如何在 MySQL 中创建和使用唯一索引。

1. 创建示例表

首先，创建一个名为 users​ 的表，该表包含一些用户信息：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

2. 创建唯一索引

现在，我们希望保证 username​ 列和 email​ 列中的值是唯一的，因此可以在这两列上创建唯一索引：

CREATE UNIQUE INDEX idx_username ON users (username);
CREATE UNIQUE INDEX idx_email ON users (email);

3. 插入示例数据

插入一些示例数据，注意遵循唯一性约束：

INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com');

4. 尝试插入重复值

如果尝试插入一个具有重复用户名或电子邮件地址的记录，MySQL 将返回错误：

-- 尝试插入重复的用户名
INSERT INTO users (username, email) VALUES
('alice', 'anotheralice@example.com');  -- 这将导致错误

-- 尝试插入重复的电子邮件
INSERT INTO users (username, email) VALUES
('charlie', 'alice@example.com');  -- 这也将导致错误

5. 查询使用索引

可以通过以下查询快速检索用户信息：

SELECT * FROM users WHERE username = 'bob';

由于在 username​ 列上创建了唯一索引，MySQL 会利用该索引加速查询。

6. 查看索引信息

可以通过以下查询查看 users​ 表中的索引信息：

SHOW INDEX FROM users;

这将返回表中所有索引的详细信息，包括索引名称、列名、索引类型等。

‍

2.1.3 主键索引（PRIMARY KEY）

简介

主键索引是一种特殊类型的唯一索引，它用于唯一标识表中的每一行数据。每个表只能有一个主键，而主键可以由一个或多个列组成。主键的主要作用是确保数据的唯一性和完整性，同时加速数据的检索。

  • 定义：主键索引是唯一索引的一种特殊形式，它要求索引列的值唯一且不允许NULL值。
  • 优点：每个表只能有一个主键，通常用于唯一标识表中的每一行数据。
  • 使用场景：适合表的标识列，如用户ID、订单号等。

‍

特点
  1. 唯一性：主键约束确保表中的每一行都是唯一的，不能有重复值。
  2. 非空：主键列不能包含 NULL 值。
  3. 快速检索：主键索引通过优化查询性能，允许快速查找特定行。
  4. 默认聚集索引：在 MySQL 中，主键通常被实现为聚集索引（Clustered Index），这意味着数据行的物理存储顺序与主键的逻辑顺序相同。

‍

使用场景

主键索引适用于任何需要唯一标识记录的场景，例如：

  • 数据库表中的每一条记录。
  • 实体关系模型中的主实体（如用户、订单等）。

‍

创建主键索引的语法
CREATE TABLE table_name (
    column_name data_type PRIMARY KEY
);

或者在表创建后添加主键：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

‍

示例

以下是一个具体示例，演示如何在 MySQL 中创建和使用主键索引。

1. 创建示例表

首先，创建一个名为 products​ 的表，该表包含一些产品信息：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL
);

在这个示例中，product_id​ 列被定义为主键，并设置为自动递增（AUTO_INCREMENT​），这意味着它将为每个新插入的记录自动生成唯一的 ID。

2. 插入示例数据

接下来，可以插入一些示例数据：

INSERT INTO products (product_name, price, quantity) VALUES
('Laptop', 999.99, 10),
('Smartphone', 499.99, 25),
('Tablet', 299.99, 15);

由于 product_id​ 是主键，因此每个插入的记录都会自动生成唯一的 product_id​。

3. 查询数据

可以通过以下查询快速检索产品信息：

SELECT * FROM products WHERE product_id = 1;

由于 product_id​ 是主键，MySQL 会利用主键索引快速找到对应的记录。

4. 查看索引信息

可以通过以下查询查看 products​ 表中的索引信息：

SHOW INDEX FROM products;

这将返回表中所有索引的详细信息，包括索引名称、列名、索引类型等。

‍

组合主键

如果需要在多个列上创建主键，可以使用组合主键，例如：

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

在这个示例中，order_id​ 和 product_id​ 列共同构成了一个主键，确保每一对 (order_id, product_id)​ 的组合都是唯一的。

‍

‍

2.2 按索引的列数分类

2.2.1 单列索引（Single-column Index）

简介

单列索引是指在数据库表中为单个列创建的索引。它是最基本的索引类型，主要用于提高对该列的查询性能。单列索引通过快速定位表中的数据行来加速检索过程。

  • 定义：在单一列上创建的索引。
  • 优点：简单，易于创建和管理。
  • 使用场景：适用于只查询单个列的场景，例如搜索用户的用户名。

‍

特点
  1. 提高查询性能：通过为列创建索引，MySQL 可以快速查找、排序和筛选数据，从而提高查询效率。
  2. 唯一性（可选）：单列索引可以是唯一索引（确保列中的值唯一）或非唯一索引（允许重复值）。
  3. 可支持多种操作：单列索引可以提高使用 WHERE​、ORDER BY​ 和 GROUP BY​ 子句的查询性能。
  4. 不影响数据完整性：单列索引不会强制约束列的值，但可以提高访问效率。

‍

使用场景
  • 查询某个列的值时经常使用该列作为条件。
  • 需要对某个列进行排序或分组操作。
  • 数据表中的某一列是经常被查询并且可以有重复值的列。

‍

创建单列索引的语法
CREATE INDEX index_name ON table_name (column_name);

或者在创建表时直接定义索引：

CREATE TABLE table_name (
    column_name data_type,
    INDEX index_name (column_name)
);

‍

示例

以下是一个具体的示例，演示如何在 MySQL 中创建和使用单列索引。

1. 创建示例表

首先，创建一个名为 employees​ 的表，包含员工的基本信息：

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50)
);

在这个示例中，employee_id​ 列被定义为主键，确保每个员工的唯一性。

2. 创建单列索引

接下来，为 last_name​ 列创建单列索引，以提高对员工姓氏的查询效率：

CREATE INDEX idx_last_name ON employees (last_name);

这样做后，MySQL 会为 last_name​ 列创建索引，查询该列时将更快速。

3. 插入示例数据

可以插入一些示例数据到 employees​ 表中：

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', 'john.doe@example.com', 'Sales'),
('Jane', 'Smith', 'jane.smith@example.com', 'Marketing'),
('Emily', 'Davis', 'emily.davis@example.com', 'Sales'),
('Michael', 'Brown', 'michael.brown@example.com', 'HR');

4. 查询数据

现在，可以使用 last_name​ 列进行查询：

SELECT * FROM employees WHERE last_name = 'Doe';

由于 last_name​ 列有索引，MySQL 将能够更快速地查找匹配的记录。

5. 查看索引信息

可以通过以下查询查看 employees​ 表中的索引信息：

SHOW INDEX FROM employees;

这将返回表中所有索引的详细信息，包括索引名称、列名、索引类型等。

‍

删除单列索引

如果不再需要索引，可以使用以下语句删除单列索引：

DROP INDEX idx_last_name ON employees;

‍

‍

2.2.2 复合索引（Composite Index）

简介

复合索引（Composite Index）是指在一个表的多个列上创建的索引。这种索引可以提高多列组合查询的效率。当查询条件中涉及到多个列时，复合索引能够加速这些查询的执行。复合索引不仅能提高查询性能，还可以用于对多个列进行排序和分组操作。

‍

  • 定义：在多个列上创建的索引。

  • 优点：

    • 可以提高涉及多个列的查询性能。
    • 查询时可以使用多个列进行筛选。

  • 缺点：

    • 索引的顺序非常重要，应该根据查询的条件顺序来设计。

  • 使用场景：适合复杂查询，涉及多个字段的场合，如联合查询和过滤。

‍

特点
  1. 多个列的组合：复合索引可以由两个或多个列组成，适用于需要同时查询这些列的场景。
  2. 最左前缀原则：复合索引遵循最左前缀原则，只有当查询条件中包含索引的最左边列时，索引才能被使用。也就是说，如果索引是 (column1, column2)​，那么查询条件必须包含 column1​ 才能利用该索引。
  3. 可以提高多列查询效率：对于涉及多个列的查询，复合索引比单列索引更高效。
  4. 支持多种操作：复合索引可提高 WHERE​、ORDER BY​ 和 GROUP BY​ 子句的查询性能。

‍

使用场景
  • 当查询经常涉及多个列作为条件时。
  • 需要对多个列进行排序或分组操作。
  • 适合用于大数据量的表，以减少查询时间。

‍

创建复合索引的语法
CREATE INDEX index_name ON table_name (column1, column2, ...);

‍

示例

以下是一个具体的示例，演示如何在 MySQL 中创建和使用复合索引。

1. 创建示例表

首先，创建一个名为 orders​ 的表，包含订单信息：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20)
);

在这个示例中，order_id​ 列被定义为主键。

2. 创建复合索引

接下来，为 customer_id​ 和 order_date​ 列创建复合索引，以提高按客户 ID 和订单日期查询的效率：

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

这样，MySQL 将在 customer_id​ 和 order_date​ 列上创建一个复合索引。

3. 插入示例数据

插入一些示例数据到 orders​ 表中：

INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(1, '2024-01-10', 100.00, 'Completed'),
(1, '2024-01-15', 150.00, 'Pending'),
(2, '2024-02-20', 200.00, 'Completed'),
(1, '2024-01-25', 80.00, 'Completed'),
(2, '2024-02-22', 90.00, 'Pending');

4. 查询数据

可以使用 customer_id​ 和 order_date​ 列进行查询：

SELECT * FROM orders WHERE customer_id = 1 AND order_date >= '2024-01-10';

由于 customer_id​ 和 order_date​ 列有复合索引，MySQL 将能够更快速地查找匹配的记录。

5. 查看索引信息

通过以下查询可以查看 orders​ 表中的索引信息：

SHOW INDEX FROM orders;

这将返回表中所有索引的详细信息，包括索引名称、列名、索引类型等。

删除复合索引

如果不再需要复合索引，可以使用以下语句删除：

DROP INDEX idx_customer_order ON orders;

总结

复合索引是提高多列查询性能的重要工具。在设计数据库时，应根据实际的查询需求合理选择和创建复合索引，以确保数据库性能的最佳化。合理利用复合索引能够显著提高复杂查询的执行效率，尤其是在处理大量数据时。

‍

2.3 按存储结构分类

2.3.1 B树索引（B-Tree Index）

  • 定义：B树索引是MySQL默认的索引类型，使用B树或B+树结构。

  • 优点：

    • 支持快速查找、插入和删除操作。
    • 可以进行范围查询和排序操作。

  • 使用场景：适用于大多数普通查询。

‍

2.3.2 哈希索引（Hash Index）

  • 定义：哈希索引使用哈希表存储索引数据。

  • 优点：

    • 在等值查询时非常快速，查询效率高。

  • 缺点：

    • 只支持等值查询，不支持范围查询。
    • 一旦哈希冲突发生，可能导致性能下降。

  • 使用场景：适合对性能要求较高的简单等值查询。MySQL中的Memory存储引擎支持哈希索引。

‍

2.3.3 全文索引（FULLTEXT）

  • 定义：全文索引用于对大文本字段进行搜索。

  • 优点：

    • 允许对文本进行复杂搜索，例如匹配单词、短语和自然语言处理。
    • 可以进行布尔搜索，支持关键词和短语的匹配。

  • 缺点：

    • 只能用于CHAR、VARCHAR和TEXT类型的列。

  • 使用场景：适用于需要对大文本字段进行搜索的应用，如博客、评论系统等。

‍

2.4 按使用方式分类

2.4.1 覆盖索引（Covering Index）

  • 定义：查询所需的所有数据都在索引中，不需要回表查询数据表。

  • 优点：

    • 减少了对数据表的访问，提高了查询性能。
    • 提升了查询效率，尤其是在大表上。

  • 使用场景：适用于查询较为复杂但不需要回表的情况。

‍

2.4.2 临时索引（Temporary Index）

  • 定义：在执行某些特定查询时，数据库自动创建的索引，查询完成后会被删除。
  • 优点：提升了复杂查询的性能，优化了查询速度。
  • 使用场景：通常在复杂的JOIN查询和子查询中使用。

‍

  • 在执行某些特定查询时，数据库会自动创建的索引，查询完成后会被删除。
  • 通常用于优化复杂的查询操作。

‍

2.5 按索引类型分类

2.5.1 聚集索引（Clustered Index）

  • 定义：聚集索引将数据表的物理存储顺序与索引的顺序一致。

  • 优点：

    • 查找速度快，因为数据存储和索引顺序一致。
    • 只能在一个列上创建一个聚集索引。

  • 缺点：插入和删除操作可能会导致数据移动，影响性能。

  • 使用场景：通常是主键索引，用于快速查找表中记录。

‍

2.5.2 非聚集索引（Non-clustered Index）

  • 定义：非聚集索引的索引顺序与数据表的物理存储顺序无关，可以在多个列上创建。

  • 优点：

    • 可以创建多个非聚集索引。
    • 数据表和索引的物理存储顺序可以不同。

  • 缺点：查询时可能需要回表，导致性能下降。

  • 使用场景：适用于需要对多个列进行快速查找的情况。

‍

2.6 特殊类型索引

2.6.1 空间索引（SPATIAL Index）

  • 定义：专为地理空间数据设计的索引类型，支持空间查询。

  • 优点：

    • 适合存储地理位置等数据，可以加速空间数据的查询。

  • 使用场景：适用于GIS（地理信息系统）应用程序。

‍

2.6.2 位图索引（Bitmap Index）

  • 定义：使用位图压缩存储适用于分类数据的索引类型。

  • 优点：

    • 在分类数据中高效存储和快速查询。

  • 缺点：在MySQL中并不直接支持，主要在其他数据库系统中使用。

  • 使用场景：适合数据量小且分类多的情况。

‍

‍

三、创建和管理索引

3.1 创建索引

创建索引的基本语法如下：

CREATE INDEX index_name ON table_name (column1, column2, ...);

‍

3.2 查看索引

查看表中的索引：

SHOW INDEX FROM table_name;

‍

3.3 删除索引

DROP INDEX index_name ON table_name;

‍

3.4 Dbeaver创建索引

单列索引

image-20230724164858280​

‍

image-20230724174805141​

‍

image-20230724174913482​

‍

创建联合索引

image-20230724175151200​

image-20230724175256934​

‍

image​

‍

‍

‍


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

相关文章：

  • 数据结构实验十二 图的遍历及应用
  • 在FastAPI网站学python：虚拟环境创建和使用
  • 特斯拉智驾路线影响国内OEM组织架构变革，Robotaxi重塑汽车定位搅动风云
  • 【云从】五、负载均衡CLB
  • 使用 Docker compose 部署 Nacos（达梦数据库）
  • MongoDB如何查找数据以及条件运算符使用的详细说明
  • 比肩vercel的nuxt自动化部署，nuxthub+github+cloudflare
  • web网页---QQ注册页面的实现
  • 4.计算机网络_TCP
  • 【2024CANN训练营第二季】Ascend C概述
  • 游戏盾在防御DDoS与CC攻击中的作用与实现
  • 【DNF mysql8.0安装】DNF安装MySQL服务器教程
  • docker-harbor
  • 基于SpringBoot+Vue+uniapp微信小程序的垃圾分类系统的详细设计和实现(源码+lw+部署文档+讲解等)
  • 代码随想录 -- 贪心 -- 分发饼干
  • 树莓派--AI视觉小车智能机器人--1.树莓派系统烧入及WiFi设置并进入jupyterlab
  • 滚雪球学Redis[8.1讲]：Redis插件与扩展模块：模块化架构的强大扩展能力
  • 六、设置弹窗显示队列
  • 从nas硬盘大量解压缩zip文件的性能问题
  • COSCon'24 志愿者招募令：共创开源新生活！