原文：
www.kdnuggets.com/2023/07/database-optimization-exploring-indexes-sql.html
图片由作者提供
在书中寻找特定主题时，我们会先查看索引页（位于书的开头），找出包含我们感兴趣主题的页码。现在，想象一下没有索引页的情况下查找书中的特定主题是多么不便。为此，我们不得不逐页搜索，这既耗时又令人沮丧。
1. 谷歌网络安全证书 - 快速进入网络安全职业轨道。
2. 谷歌数据分析专业证书 - 提升您的数据分析能力。
3. 谷歌 IT 支持专业证书 - 支持您的组织 IT 工作。
在 SQL Server 检索数据时，也会出现类似的问题。为了解决这个问题，SQL Server 也使用索引来加快数据检索过程，在这篇文章中我们将讨论这一部分。我们将讨论为什么需要索引以及如何有效地创建和删除索引。本教程的前提是对 SQL 命令的基本了解。
索引是一种模式对象，使用指针从行中检索数据，从而减少定位数据的 I/O（输入/输出）时间。索引可以应用于一个或多个我们希望搜索的列。它们将列存储在一个称为B-Tree的单独数据结构中。B-Tree 的主要优点之一是它将数据按排序顺序存储。
如果你想知道为什么排序后的数据可以更快地检索，你必须阅读线性搜索与二分搜索。
索引是提升 SQL 查询性能的最著名方法之一。它们小巧、快速，并且针对关系表进行了显著优化。当我们希望在没有索引的情况下搜索一行时，SQL 会执行全表扫描，逐行扫描每一行以找到匹配条件，这非常耗时。另一方面，如前所述，索引保持数据排序。
但是我们也应该小心，索引创建了一个单独的数据结构，这需要额外的空间，当数据库很大时，这可能会成为一个问题。为了良好的实践，索引仅对经常使用的列有效，而对于很少使用的列可以避免使用。以下是一些索引可能有用的场景，
-
行数必须大于（>10000）。
-
所需的列包含大量值。
-
所需的列不能包含大量的 NULL 值。
-
如果我们经常基于特定列对数据进行排序或分组，索引可以快速检索排序后的数据，而不是进行全表扫描。
并且在以下情况下可以避免使用索引，
-
表很小。
-
或者当列的值很少使用时。
-
或者当列的值经常变化时。
也可能有一种情况，当优化器检测到全表扫描比索引表花费的时间更少时，即使存在索引，索引也可能不会被使用。这种情况可能发生在表很小或者列经常更新时。
在开始之前，你必须在 PC 上设置 MySQL Workbench，以便轻松跟随教程。你可以参考这个 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;输出：
图 1 示例数据库 | 图片来源：作者
为了创建索引，我们可以使用如下的 CREATE 命令，
语法：
CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME);在上述查询中，index_name是索引的名称，table_name是表的名称，column_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;输出：
以下命令在现有表中创建一个新索引。
语法：
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;输出：
在上面的示例中，我们在现有表中创建了一个新索引。但我们无法修改现有索引。为此，我们必须首先删除旧索引，然后创建一个新的修改版索引。
例如：
DROP INDEX name_index ON employee_info;
CREATE INDEX name_index ON employee_info (name, email);
SHOW INDEX
FROM employee_info ;输出：
在这篇文章中，我们涵盖了 SQL 索引的基本理解。还建议将索引保持狭窄，即仅限于少数几列，因为过多的索引可能会对性能产生负面影响。索引可以加快SELECT查询和WHERE子句，但会减慢插入和更新语句。因此，只对经常使用的列应用索引是一个好的做法。
在此之前，请继续阅读和学习。
Aryan Garg 是一名 B.Tech 电气工程专业的学生，目前在本科最后一年。他的兴趣领域包括网页开发和机器学习。他一直在追求这些兴趣，并渴望在这些方向上进一步发展。