关于数据库性能优化:数据库优化探索-SQL-中的索引

5次阅读

共计 2510 个字符，预计需要花费 7 分钟才能阅读完成。

举荐：应用 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;

输入：

创立和删除索引

为了创立索引，咱们能够像这样应用 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;

输入：

更新索引

以下命令在现有表中创立新索引。
语法：

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 子句的速度，但减慢了插入和更新语句的速度。因而，仅对罕用列利用索引是一种很好的做法。在那之前，持续浏览并持续学习。

原文链接：数据库优化：摸索 SQL 中的索引

正文完
数据库性能优化
2023-09-08
 0
关于数据库性能优化:数据库优化探索-SQL-中的索引

站内搜索

-「」