Slow query means that the cloud database query is slow and cannot return results in time. Such queries will be recorded in the slow query log section of the uniCloud web console. But the recording is not real-time, there is a certain delay.

Developers should check their slow queries frequently, fix problems, and ensure the health and stability of the business system.

After the database statement execution exceeds a certain time (Tencent Cloud and Alibaba Cloud are both 5 seconds) and still cannot return the result, the database request will report a timeout error.

Here's how to do query optimization to avoid such problems.

set the appropriate index

See: Database Index.

Cache JQL query results into redis

See: jql-cache-redis

Pay attention to the writing of query statements when there is a large amount of data

If your data volume is very large and the query still times out after setting a suitable index, you should consider the following optimizations.

Try to avoid using skip, at least it should not be a large value, because the skip operation Mongo server will still scan the skipped data, and the time-consuming operation with skip is linearly related to the number of skips. Instead of using skip directly, you might consider using the sort and range query capabilities.

For very large data, it can be queried in segments, that is, a query can be divided into multiple query operations according to certain conditions.

count

If there is a lot of data that meets the conditions, where+count will be very slow, and it is likely to time out. We recommend against using the count method in this way on large collections.

Use the skip method to pass in a larger value

You should avoid using the where+skip+limit query method to traverse the entire collection, because this method will slow down the response time as the number of skips grows, and may also cause the request to time out.

The code below gives an example. In order to avoid the example being too complicated, it is assumed that the create_dates of no two records are equal. If the create_date cannot uniquely identify the data, other fields can be added, such as the author of the article, etc.

Sort by create_date (creation time) in descending order. Each time you query, specify that the query condition is less than the create_date of the last record in the last query result, so that the paging effect can be achieved without using skip, and it can also ensure that users turn pages up and down When there is no duplication of data on the front and back pages due to the appearance of new data.

const db = uniCloud.database()
const dbCmd = db.command
module.exports = async function(event,context) {
  const {
    lastCreateDate = Date.now(),
    pageSize
  } = event
  if(pageSize > 100){
    throw new Error('单页数据不可超过100条')
  }
  const res = await db.collection('book').where({
    create_date: dbCmd.lt(lastCreateDate)
  })
  .limit(pageSize)
  .get()
}

After using the above writing method, you should use the previous page, next page, previous n pages (where n is a relatively small number), and next n pages to replace random page turning. You can refer to the pagination function of search results of Baidu or Google. When the number of result pages is very large, the number of pages in total is not displayed, and only the first 10 pages are supported to support random page turning; in the process of page turning, it is no longer supported. Random page turning only supports turning down a small number of pages, so that you can use where+skip (a small amount)+limit+orderBy method to quickly query the results based on the results already queried.

Sampling method sample

The sample operation will respond slowly when a large amount of data is triggered at high frequency. Before using it, please make sure that your business scenario is suitable for using the sample method.

clientDB linked table query timeout

Please refer to this document to adjust and optimize, Use getTemp for joint table query

Slow query log

仅阿里云支持

慢查询的定义：单条查询语句耗时超100毫秒。符合条件的查询会被统计进来。

在慢查询日志到一定量级时，我们会发送邮件通知到服务空间所属账号邮箱，请及时关注并前往uniCloud web控制台查看慢查询日志统计和详细信息，参考本文档进行优化。

慢查询日志支持查看统计及详细数据，可查询具体某个集合的慢查询日志记录。

A typical slow query log contains the following information

指标名称	说明
集合名称	数据库请求操作的集合的名称
查询语句	详细的查询指令模板，例：{"filter":{"invite_code":"?"}}，表示以 invite_code: "xxx" 作为查询条件进行的查询
执行开始时间	该慢查询执行开始的时间
执行时长	该慢查询执行的时长（毫秒）
文档扫描行数	该慢查询命中数据库内多少条数据，如果索引设置的合适此数值会尽可能的接近索引扫描行数
索引扫描行数	该慢查询命中索引扫描数据库内的数据
返回行数	该慢查询结果最终匹配数据库内多少条数据，即查询返回的条数

Partition table and redundant table

If the number of records in your data table exceeds hundreds of thousands, you can use the table partitioning strategy to optimize.

For example, in the order table of many e-commerce systems, the orders for the last three months are one table, and the overdue orders will be moved to other tables. The banking system will also archive transaction data from a few years ago to prevent the amount of data from being too large to be checked.

Redundant tables are another strategy. Instead of sub-tables, the high-frequency data in the large table is separately extracted into a table. In fact, jql-cache-redis is a redundant idea to store more data in redis.