数据库优化:探索 SQL 中的索引

了解 SQL 中的索引以及如何提高 SELECT 查询和 WHERE 子句的检索速度

数据库优化:探索 SQL 中的索引
推荐:使用NSDT场景编辑器助你快速搭建可编辑的3D应用场景

在一本书中搜索特定主题时,我们将首先访问索引页面(该页面位于该书的开头),并找到包含我们感兴趣的主题的页码。现在,想象一下在没有索引页的书中找到特定主题是多么不方便。为此,我们必须搜索书中的每一页,这非常耗时且令人沮丧。

当 SQL Server 从数据库中检索数据时,它也会出现类似的问题。为了克服这个问题,SQL Server还使用索引来加快数据检索过程,在本文中,我们将介绍该部分。我们将介绍为什么需要索引以及如何有效地创建和删除索引。本教程的先决条件是 SQL 命令的基本知识。

什么是索引?

索引是一个架构对象,它使用指针从行中检索数据,从而减少查找数据的 I/O(输入/输出)时间。索引可以应用于我们要搜索的一个或多个列。它们将列存储在称为 B 树的单独数据结构中。B-Tree的主要优点之一是它以排序顺序存储数据。

如果您想知道为什么如果对数据进行排序可以更快地检索数据,那么您必须阅读线性搜索与二分搜索。

索引是提高 SQL 查询性能的最著名的方法之一。它们体积小、速度快,并且针对关系表进行了显著优化。当我们想要搜索没有索引的行时,SQL 会线性执行全表扫描。换句话说,SQL必须扫描每一行才能找到匹配条件,这是非常耗时的。另一方面,如上所述,索引使数据保持排序。

但是我们也应该小心,索引会创建一个单独的数据结构,这需要额外的空间,当数据库很大时,这可能会成为问题。出于良好做法,索引仅对常用列有效,可以避免对很少使用的列使用。以下是索引编制可能有用的一些情况,

  1. 行数必须为 (>10000)。
  2. 必需列包含大量值。
  3. 必需的列不得包含大量 NULL 值。
  4. 如果我们经常根据特定列对数据进行排序或分组,这将很有帮助。索引快速检索排序后的数据,而不是执行完全扫描。

在以下情况下可以避免索引,

  1. 桌子很小。
  2. 或者当列的值很少使用时。
  3. 或者当列的值频繁更改时。

当优化程序检测到全表扫描花费的时间少于索引表时,也可能不使用索引,即使它存在也是如此。当表较小或列频繁更新时,可能会发生这种情况。

创建示例数据库

在开始之前,您必须在PC上设置MySQL工作台才能轻松遵循本教程。您可以参考此YouTube视频来设置工作台。

设置工作台后,我们将创建一些随机数据,从中可以执行查询。

创建表:

-- Create a table to hold the random data

CREATE TABLE employee_info (id INT PRIMARY KEY AUTO_INCREMENT,
                                               name VARCHAR(100),
                                                    age INT, email VARCHAR(100));

插入数据:

-- Insert random data into the table

INSERT INTO employee_info (name, age, email)
SELECT CONCAT('User', LPAD(ROW_NUMBER() OVER (), 5, '0')),
       FLOOR(RAND() * 50) + 20,
       CONCAT('user', LPAD(ROW_NUMBER() OVER (), 5, '0'), '@xyz.com')
FROM information_schema.tables
LIMIT 100;

它将创建一个名为具有名称、年龄和电子邮件等属性的表。employee_info

显示数据:

SELECT *
FROM employee_info;

输出:

数据库优化:探索 SQL 中的索引

创建和删除索引

为了创建索引,我们可以像这样使用 CREATE 命令,

语法:

CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME);

在上面的查询中,是索引的名称,是表的名称,是我们要应用索引的列的名称。index_nametable_namecolumn_name

前任-

CREATE INDEX age_index ON employee_info (age);

我们还可以为同一表中的多列创建索引,

CREATE INDEX index_name ON TABLE_NAME (col1,
                                       col2,
                                       col3, ....);

唯一索引: 我们还可以为特定列创建一个唯一索引,该索引不允许在该列中存储重复值。这样可以保持数据的完整性,并进一步提高性能。

CREATE UNIQUE INDEX index_name ON TABLE_NAME (COLUMN_NAME);

注意: 可以为PRIMARY_KEY和 UNIQUE 列自动创建索引。我们不必手动创建它们。

删除索引:

我们可以使用 DROP 命令从表中删除特定索引。

DROP INDEX index_name ON TABLE_NAME;

我们需要指定索引和表名来删除索引。

显示索引:

您还可以查看表中存在的所有索引。

语法:

SHOW INDEX
FROM TABLE_NAME;

前任-

SHOW INDEX
FROM employee_info;

输出:

数据库优化:探索 SQL 中的索引

更新索引

以下命令在现有表中创建新索引。

语法:

ALTER TABLE TABLE_NAME ADD INDEX index_name (col1, col2, col3, ...);

注意: ALTER 不是 ANSI SQL 的标准命令。因此,它可能因其他数据库而异。

例如

ALTER TABLE employee_info ADD INDEX name_index (name);

SHOW INDEX
FROM employee_info;

输出:

数据库优化:探索 SQL 中的索引

在上面的示例中,我们在现有表中创建了一个新索引。但是我们不能修改现有的索引。为此,我们必须首先删除旧索引,然后创建一个新的修改索引。

例如

DROP INDEX name_index ON employee_info;


CREATE INDEX name_index ON employee_info (name, email);

SHOW INDEX
FROM employee_info ;

输出:

数据库优化:探索 SQL 中的索引

总结

在本文中,我们介绍了对 SQL 索引的基本了解。还建议保持索引范围较窄,即限制为几列,因为更多的索引可能会对性能产生负面影响。索引加快了 SELECT 查询和 WHERE 子句的速度,但减慢了插入和更新语句的速度。因此,仅对常用列应用索引是一种很好的做法。

在那之前,继续阅读并继续学习。

3D建模学习工作室 整理翻译,转载请注明出处!

NSDT场景编辑器 | NSDT 数字孪生 | GLTF在线编辑器 | 3D模型在线转换 | UnrealSynth虚幻合成数据生成器 | 3D模型自动纹理化工具
2023 power by nsdt©鄂ICP备2023000829号