如何通过对大型表建立索引来提高数据库的性能

什么是数据库索引？

数据库索引是一种可以更快地从数据库中搜索和检索数据的技术。这就像创建一个在一本大书中查找信息的快速指南。它有助于加快搜索速度并使查找内容变得更容易。

索引可加快 SELECT 查询和 WHERE 子句的速度。另一方面会减慢 INSERT 和 UPDATE 查询的速度。

图：数据库索引数据结构

为什么要建立索引？

想象一下，您有一个书籍数据库，并且您想要查找标题中包含“编程”一词的所有书籍。如果没有索引，数据库将必须扫描表中的每一行才能找到与搜索条件匹配的书籍。这可能需要很长时间，特别是如果表中有很多书的话。

但是，如果在标题列上创建索引，数据库可以快速找到与搜索条件匹配的行。索引是一个单独的数据结构，它按排序顺序存储标题列的值。数据库可以利用索引快速找到标题中包含“编程”一词的行。

为包含 5000 万行的表建立索引

pg-million对于此示例，我们将在 PostgreSQL 中创建一个数据库，其中包含customers包含以下列的表：first_name, last_name, mobile_no, country。

插入5000万行随机数据

CREATE TABLE customers(first_name VARCHAR(50), last_name VARCHAR(50), mobile_no INTEGER, country VARCHAR(50))

INSERT INTO customers (first_name, last_name, mobile_no, country)
SELECT substr(md5(random()::text), 1, 10),
       substr(md5(random()::text), 1, 10),
       (random() * 70 + 10)::integer,
       (CASE WHEN random() < 0.5 THEN 'India' ELSE 'United Kingdom' END)
FROM generate_series(1, 50000000);

country在列 上创建索引

我们在列上创建索引country以获得组织良好的列表，使我们可以快速找到来自特定国家/地区的所有客户，而无需搜索整个列表。

CREATE INDEX idx_partial_country ON customers (country) WHERE country IN ('India', 'United Kingdom')

创建索引的时间：2m 2s

对于此示例，我们使用部分索引。部分索引是根据过滤行中特定值的条件创建的。这允许数据库仅对相关行进行索引和优化，从而减少索引大小并提高这些特定值的查询性能。

注意：不同数据库创建索引的语法和索引类型有所不同。您应该根据您的数据库和用例使用适当的语法和索引类型。

测量索引前后的查询执行时间

考虑以下查询

SELECT * FROM customers WHERE country='United Kingdom';

没有索引的查询执行时间：41836.270 ms

带索引的查询执行时间：24254.644 ms

Improvement in query execution time ~42.03%

（为了更好地理解，您可以在这里找到所有代码

指数表现如何？

深入了解指数的有效性非常重要。一些有用的指标包括：

• 索引使用情况统计信息：监控索引的使用情况，以了解哪些索引对查询性能有积极贡献。（例如：跟踪索引的大小，因为较大的索引可能会影响磁盘空间和 I/O 性能）
• 查询性能指标：监视涉及索引列的查询的查询执行时间和响应时间。（例如：查询执行时间突然增加可能表明存在索引相关问题。）
• 索引维护指标：定期评估索引的健康状况及其对数据库操作的影响。（例如：跟踪索引膨胀，当索引由于过度插入、更新或删除而变得低效时就会发生这种情况。）

何时使用索引？

• 频繁搜索查询：当您频繁搜索大型数据集中的特定数据时，请使用索引。它有助于快速找到所需的信息。
• 性能改进：索引可以通过避免扫描整个数据集来提高数据检索操作的速度，特别是对于复杂查询。
• 大数据量：在处理大量数据时使用索引，因为即使数据集增长，它也有助于保持高效的查询性能。

什么时候不使用索引？

• 频繁的写入操作：如果数据库频繁执行插入、更新或删除操作，请避免建立过多的索引，因为索引会减慢这些写入操作的速度并消耗额外的存储空间。索引不应该用在经常操作的列上。
• 小型数据集：对于相对较小的数据集，索引可能无法提供显着的性能提升，并且可能会带来不必要的开销。在这种情况下，收益可能不会超过成本。

结论

如果您正在寻找提高数据库性能的方法，那么数据库索引是一个不错的起点。通过在查询中经常使用的列上创建索引，可以显着提高数据库的性能并使查询速度更快。然而，在做出决定之前权衡索引的优点和缺点很重要。