Skip to main content

未索引列的查询

Optimize 提供的建议可帮助你识别和解决由数据库索引缺失引起的性能问题。

¥Optimize provides recommendations to help you identify and resolve performance issues caused by missing database indexes.

以下针对 User 模型的查询使用 where 属性 来过滤没有索引的列：

¥The following queries targeting the User model use a where property to filter on columns that do not have indexes:

await prisma.user.findFirst({
   where: {
      name: "Marc"
   }
})

await prisma.user.findFirst({
   where: {
      name: "Jon"
   }
})

await prisma.user.count({
   where: {
      name: "Nikolas"
   }
})

问题是什么？​

¥What is the problem?

索引使数据库能够更快地检索数据，类似于书中的索引如何帮助你无需逐页阅读即可找到信息。

¥An index allows the database to retrieve data more quickly, similar to how an index in a book helps you locate information without reading every page.

使用带有 where 属性的 Prisma 时，如果没有为相关列定义索引，则数据库可能需要扫描表中的每一行（“全表扫描”）来查找匹配项。出于以下几个原因，这可能是不可取的：

¥When using Prisma with a where property, if no indexes are defined for the relevant columns, the database may need to scan every row in the table (a “full table scan”) to find matches. This can be undesirable for several reasons:

用户体验​

¥User experience

对于大型数据集，如果数据库必须扫描整个表才能找到匹配的行，用户将面临更长的等待时间。

¥For large datasets, if the database must scan the entire table to find matching rows, users will experience longer waiting times.

资源利用率​

¥Resource utilization

  • 高 CPU 使用率：扫描大型表会显著增加 CPU 使用率，从而降低整体系统性能。

    ¥High CPU usage: Scanning large tables can significantly increase CPU usage, degrading overall system performance.

  • 内存消耗：全表扫描期间，需要更多内存来处理和存储数据。

    ¥Memory consumption: More memory is required to process and store data during a full table scan.

  • 磁盘 I/O：全表扫描会增加磁盘输入/输出操作，可能会减慢其他数据库活动的速度。

    ¥Disk I/O: Full table scans increase disk input/output operations, potentially slowing down other database activities.

警告

虽然这些问题可能不会在开发环境中出现，因为数据集较小，但在生产环境中，它们可能会成为严重的问题，因为生产环境中的数据集通常要大得多。

¥While these issues might not appear in development due to smaller datasets, they can become significant problems in production, where datasets are typically much larger.

更多关于数据库索引的信息​

¥More on database indexes

索引的工作原理​

¥How indexes work

索引会创建一个数据结构，用于存储索引列的值以及指向表中相应行的指针。当你使用索引列查询数据库时，数据库可以使用此索引快速定位相关行，而无需扫描整个表。

¥Indexes create a data structure that stores the indexed column's values along with pointers to the corresponding rows in the table. When you query the database using an indexed column, the database can use this index to quickly locate the relevant rows instead of scanning the entire table.

权衡索引​

¥The trade-offs of indexing

  • 空间与时间：索引需要额外的存储空间来保存索引数据，但它可以显著加快数据检索速度。

    ¥Space vs. time: Indexing requires additional storage space to save index data, but it significantly speeds up data retrieval.

  • 更新开销：每次在表中添加、更新或删除数据时，都需要额外开销来保持索引更新，这可能会降低写入操作的速度。

    ¥Update overhead: Every time data is added to, updated in, or removed from your table, there is an overhead to keep the indexes up to date, which can slow down write operations.

何时使用索引​

¥When to use indexes

  • 大型数据集：索引对于包含大量行的表尤其有用。

    ¥Large datasets: Indexes are particularly beneficial for tables with a large number of rows.

  • 带有过滤或排序的频繁查询：在 过滤或排序 常用的列上使用索引。

    ¥Frequent queries with filtering or sorting: Use indexes on columns that are frequently used for filtering or sorting.

  • 查找相关数据：在外键列上使用索引以加快相关记录的检索速度，例如使用 include 时。

    ¥Looking up related data: Use indexes on foreign key columns to speed up the retrieval of related records, such as when using include.

何时不使用索引​

¥When not to use indexes

  • 小表：对于行数很少的表，维护索引的开销可能不值得性能提升。

    ¥Small tables: For tables with very few rows, the overhead of maintaining indexes might not be worth the performance gain.

  • 写入密集型表：索引可能会降低写入操作（create、update、delete）的速度，因为索引也需要更新。避免在频繁写入操作的模型上过度索引。

    ¥Write-heavy tables: Indexes can slow down write operations (create, update, delete) because the index needs to be updated as well. Avoid excessive indexing on models with frequent write operations.

  • 不常访问的表：如果某个表很少被访问，则索引的好处可能无法弥补其开销。

    ¥Infrequently accessed tables: If a table is rarely accessed, the benefits of indexing may not justify the overhead.

  • 包含大数据量的列：索引包含大量数据的列可能会导致更高的存储需求，并且可能不会带来显著的性能提升。

    ¥Columns with large data: Indexing columns with large data can lead to higher storage requirements and might not provide significant performance improvements.

  • 很少被过滤的列：如果某个表经常被访问，但很少按特定列进行筛选，则在该列上创建索引可能并无益处。

    ¥Rarely filtered columns: If a table is often accessed but rarely filtered by a specific column, creating an index on that column may not be beneficial.

警告

即使你索引了某个列，数据库也不一定总是使用它。许多数据库管理系统，例如 PostgreSQL 和 MySQL，都拥有查询优化器，它会评估多个执行计划，并选择其认为最高效的执行计划。在某些情况下，这可能涉及忽略现有索引，转而采用其他执行计划，该执行计划会确定对特定查询的性能更佳。

¥Even if you index a column, the database may not always use it. Many database management systems, such as PostgreSQL and MySQL, have a query optimizer that evaluates multiple execution plans and selects the one it estimates to be most efficient. In some cases, this may involve ignoring an existing index in favor of a different execution plan that it determines will perform better for that specific query.