Skip to main content

由 LIKE 操作引起的全表扫描

Optimize 提供的建议可帮助你识别和解决由 LIKE 操作的全表扫描引起的性能问题。

¥Optimize provides recommendations to help you identify and resolve performance issues caused by full table scans from LIKE operations.

以下针对 User 模型的查询提供 contains 和 endsWith 作为选项，它们转换为 LIKE 和 ILIKE SQL 运算符。

¥The following query targeting the User model provides contains and endsWith as options, which translate to LIKE and ILIKE SQL operators.

await prisma.user.findMany({ 
  where: { 
    email: { contains: "gmail.com" }, 
    name: { endsWith: "Burk" } 
  } 
})

问题是什么？​

¥What is the problem?

SQL 中的 LIKE 和 ILIKE 运算符可能导致全表扫描，这可能会影响性能，尤其是在处理较大数据集时：

¥LIKE and ILIKE operators in SQL can lead to full table scans, potentially impacting performance, especially with larger datasets:

UX​

  • 加载时间较慢：全表扫描会显著增加检索数据所需的时间，导致用户等待时间更长。

    ¥Slower load times: Full table scans can significantly increase the time it takes to retrieve data, leading to longer wait times for users.

资源利用率​

¥Resource utilization

  • 资源使用增加：全表扫描会增加 CPU、内存使用量和磁盘 I/O，从而给数据库的系统资源带来压力。

    ¥Increased resource usage: Full table scans increase CPU, memory usage, and disk I/O, straining system resources for your database.

  • 成本增加：在无服务器数据库定价方案中，更密集的资源使用率会导致更高的成本。

    ¥Increased costs: In serverless database pricing plans, more intensive resource usage can translate into higher costs.