什么是数据库索引?
数据库索引是一种可以更快地从数据库中搜索和检索数据的技术。这就像创建一个在一本大书中查找信息的快速指南。它有助于加快搜索速度并使查找内容变得更容易。
索引可加快 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 性能)
- 查询性能指标:监视涉及索引列的查询的查询执行时间和响应时间。(例如:查询执行时间突然增加可能表明存在索引相关问题。)
- 索引维护指标:定期评估索引的健康状况及其对数据库操作的影响。(例如:跟踪索引膨胀,当索引由于过度插入、更新或删除而变得低效时就会发生这种情况。)
何时使用索引?
- 频繁搜索查询:当您频繁搜索大型数据集中的特定数据时,请使用索引。它有助于快速找到所需的信息。
- 性能改进:索引可以通过避免扫描整个数据集来提高数据检索操作的速度,特别是对于复杂查询。
- 大数据量:在处理大量数据时使用索引,因为即使数据集增长,它也有助于保持高效的查询性能。
什么时候不使用索引?
- 频繁的写入操作:如果数据库频繁执行插入、更新或删除操作,请避免建立过多的索引,因为索引会减慢这些写入操作的速度并消耗额外的存储空间。索引不应该用在经常操作的列上。
- 小型数据集:对于相对较小的数据集,索引可能无法提供显着的性能提升,并且可能会带来不必要的开销。在这种情况下,收益可能不会超过成本。
结论
如果您正在寻找提高数据库性能的方法,那么数据库索引是一个不错的起点。通过在查询中经常使用的列上创建索引,可以显着提高数据库的性能并使查询速度更快。然而,在做出决定之前权衡索引的优点和缺点很重要。
标签:name,country,数据库,查询,索引,创建,性能 From: https://blog.51cto.com/u_15739596/7202889