数据库优化：探索SQL中的索引
数据库优化：SQL中的索引
在搜索书籍中的特定主题时，我们首先会访问索引页（该页位于书籍开头），找出包含我们感兴趣主题的页码。现在，想象一下如果没有索引页，在书籍中找到特定主题是多么不方便。为此，我们必须搜索书中的每一页，这非常耗时和令人沮丧。
当从数据库中检索数据时，SQL Server也会遇到类似的问题。为了克服这个问题，SQL Server还使用索引来加速数据检索过程，在本文中我们将涵盖这部分内容。我们将介绍索引的必要性以及如何有效地创建和删除索引。本教程的先决条件是对SQL命令有基本的了解。
- LAION AI推出Video2Dataset：一款开源工具，旨在高效且规模化地整理视频和音频数据集
- Jasper AI评论（2023年7月）：最佳AI写作生成器？
- 如何在Windows上使用Anaconda解决Python依赖问题
什么是索引？
索引是一种模式对象，它使用指针来检索行中的数据，从而减少了查找数据的I/O（输入/输出）时间。索引可以应用于我们想要搜索的一个或多个列。它们将列存储在一个称为B-Tree的单独数据结构中。B-Tree的主要优点之一是它按排序顺序存储数据。
如果你想知道为什么如果数据排序了就可以更快地检索数据，那么你必须阅读关于线性搜索与二分搜索的文章。
索引是改善SQL查询性能的最著名方法之一。它们小巧、快速，并且在关系表中经过了优化。当我们想要在没有索引的情况下搜索一行时，SQL将线性地执行全表扫描。换句话说，SQL必须扫描每一行以找到匹配条件的行，这非常耗时。另一方面，索引保持数据排序，如上所述。
但是我们也应该小心，索引会创建一个单独的数据结构，需要额外的空间，当数据库很大时可能会成为问题。出于良好的实践，索引仅对经常使用的列有效，并且可以在很少使用的列上避免使用索引。以下是一些可能有帮助的索引场景：
- 行数必须大于10000。
- 所需列包含大量值。
- 所需列不应包含大量NULL值。
- 如果我们经常根据特定列对数据进行排序或分组，那么索引是有帮助的。索引可以快速检索排序后的数据，而不是执行全表扫描。
在以下情况下可以避免使用索引：
- 表很小。
- 或者列的值很少使用。
- 或者列的值经常更改。
还有可能出现这样一种情况，即优化器检测到全表扫描所需的时间比使用索引的表少，即使索引存在也不使用。这可能发生在表很小或者列经常更新的情况下。
创建示例数据库
在开始之前，您必须在您的计算机上安装MySQL Workbench以便轻松地按照教程进行操作。您可以参考这个YouTube视频来设置您的Workbench。
在设置好您的Workbench之后，我们将创建一些随机数据，以便执行我们的查询。
创建表：
-- 创建一个表来保存随机数据
CREATE TABLE employee_info (id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT, email VARCHAR(100));插入数据：
-- 向表中插入随机数据
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;
输出：
创建和删除索引
要创建索引，我们可以使用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是电气工程学士学位的学生，目前正在本科的最后一年。他对Web开发和机器学习领域感兴趣。他追求这个兴趣，并渴望在这些方向上做更多的工作。
