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

什么是数据库索引？

数据库索引是一种能够加快数据库数据搜索和检索速度的技术。它就像为一本大书创建了快速查找指南，有助于加快搜索速度，让查找内容更加便捷。

索引可以加快 SELECT 查询和 WHERE 子句的执行速度，但会降低 INSERT 和 UPDATE 查询的速度。

图：数据库索引数据结构

为什么要建立索引？

假设你有一个书籍数据库，你想找到所有书名中包含“编程”一词的书籍。如果没有索引，数据库就必须扫描表中的每一行才能找到符合搜索条件的书籍。这可能会非常耗时，尤其是在表中书籍数量庞大的情况下。

但是，如果在标题列上创建索引，数据库就可以快速找到符合搜索条件的行。索引是一个独立的数据结构，它按排序顺序存储标题列的值。数据库可以使用索引快速找到标题中包含“programming”一词的行。

对包含 5000 万行的表进行索引

在这个例子中，我们将pg-million在 PostgreSQL 中创建一个数据库，其中包含一个表，该表customers有以下列：first_name，，，。last_namemobile_nocountry

插入 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 性能。）

• 查询性能指标：监控涉及索引列的查询的执行时间和响应时间。（例如：查询执行时间突然增加可能表明索引相关问题。）

• 索引维护指标：定期评估索引的健康状况及其对数据库操作的影响。（例如：跟踪索引膨胀情况，当索引由于过多的插入、更新或删除操作而变得效率低下时，就会发生索引膨胀。）

何时使用索引？

• 频繁搜索查询：当您经常在大型数据集中搜索特定数据时，请使用索引。它可以帮助您快速找到所需信息。

• 性能提升：索引可以提高数据检索操作的速度，特别是对于复杂查询，因为它可以避免扫描整个数据集。

• 大数据量：当处理大量数据时，会使用索引，因为即使数据集不断增长，索引也有助于保持高效的查询性能。

何时不应使用索引？

• 频繁写入操作：如果数据库频繁执行插入、更新或删除操作，请避免过度创建索引，因为索引会降低写入速度并占用额外的存储空间。不应在频繁操作的列上使用索引。

• 小数据集：对于相对较小的数据集，索引可能无法显著提升性能，反而会引入不必要的开销。在这种情况下，收益可能不足以弥补成本。

结论

如果您正在寻找提升数据库性能的方法，那么数据库索引是一个不错的起点。通过在查询中频繁使用的列上创建索引，您可以显著提升数据库性能，加快查询速度。但是，在做出决定之前，权衡索引的利弊至关重要。

在GitHub和Twitter上找到我