使用Prisma提高索引的查询性能(转载)

引言#

什么是数据库索引#

数据库索引是数据库服务器用来存储表数据子集的较小的辅助数据结构。索引通常用于提高给定表的读性能。

索引包含键 - 值对:

key: 用于创建索引的列
value: 以及指向特定表中的记录的指针

可以对一个表中的多个列建立索引。例如，如果有一个名为 User 的表，其中有 3 列: id、 firstName 和 lastName，则可以在 firstName 和 lastName 列上创建一个索引。

数据库索引的类型#

Index (默认值) : 一个正常的、非唯一的索引，它不对数据强制任何约束
Primay Key：主键，用于唯一标识表中的行
Unique indexes: 唯一索引，用于强制列中值的唯一性，防止重复值
**Full-text indexes：** 用于文本列并支持全文搜索

仅索引扫描直接查找索引中的记录#

数据库还可以选择从索引返回匹配的记录，甚至不需要 “查询” 原始表。这被称为仅索引扫描。

SELECT firstName from 'User' where firstName = 'Jimmy';

使用索引快速读取的代价#

普遍认为，索引对于提高读取性能非常有效，但是索引是有代价的。

写操作将产生额外的开销。这是因为每次写操作都需要更新索引。

索引的另一个成本是，它们需要从数据库服务器获得额外的资源进行维护。索引需要来自数据库服务器的额外存储、内存和 IO。

B-Tree 索引提高查询性能#

B 树的时间复杂度#

顺序扫描具有线性时间复杂度 (O (n))。这意味着检索一条记录所花费的时间与您拥有的记录数量呈线性关系。

另一方面，B 树具有对数时间复杂度 (O log (n))。这意味着，随着数据规模的增长，检索记录的成本增长速度明显较慢。

添加索引全量查询#

第一次查询无索引（216ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String
}

第二次查询添加 firstName（59ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName])
}

第三次查询添加联合索引（17ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName, lastName])
}

第四次查询添加索引并排序 (11ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName("desc"), lastName])
}

Hash 索引提高查询性能 (postgreSQL)#

使用 hash 索引#

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName], type: Hash)
}

npx prisma migrate dev --name add-firstName-index

原文链接#

https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL#the-price-you-pay-for-faster-reads