时间戳

首页 > 解决方案 > 数据表js和查询次数php mysql

php - 数据表js和查询次数php mysql

问题描述

提前计划“sql_calc_found_rows”被折旧并使用新的数据表 1.10.18 我有一个问题。对于服务器端处理，您需要反馈总数和过滤总数。因此，从我收集到的信息来看，我必须为每个请求运行 3 个单独的查询，这似乎有点过分，但我想不出如何在不这样做的情况下获取信息。

// Grab the data with page offset
// for "data"
$query1 = 'SELECT .. FROM .. WHERE .. LIMIT ..'

// Grab filtered total which is the total using the "WHERE" without the "LIMIT"
// for "recordsFiltered"
$query2 = 'SELECT COUNT FROM .. WHERE .. '

// Grab the total records without the WHERE
// for "recordsTotal"
$query3 = 'SELECT COUNT FROM ..'

对于复杂的查询和半大型数据集（100k-2mill）记录，每次有人在搜索中键入一个字母（他们键入一个单词时的每个字母）或点击列排序和更改页面时间和数量时，都会触发这一事实的查询/执行似乎很疯狂。

我是否遗漏了任何东西，或者这只是使用数据表必须在每个请求中触发 3 个数据库查询所需要的？谢谢。

标签： phpmysqlsqldatatables

解决方案

我已经广泛使用了 Datatables（例如浏览计费数据库中的 1000 万条记录），你说得对，需要 3 次查询来获得你想要的结果，而无需任何优化。如果查询超过 1000 万条结果是绝对必要的，您将需要查看 DB 分片，因为在 100 万条记录之后，诸如 DB I/O 之类的东西开始发挥重要作用。

但是，使用一些技巧，您可以在多达 100 万条记录的大型数据集上实现可接受的用户体验，这些记录几乎可以立即响应。因此，策略是操纵数据库并草拟查询，这样您就无需每次都考虑整个记录集。如果有动画反馈（Datatables 有），用户总是愿意等待几秒钟，并且他们得到的结果总是他们所期望的，特别是如果它在每次搜索中始终返回数百万中的几条记录时. 少即是多，这是这里的目标。

以下是我尝试过的一些效果很好的方法：

  1. 如果您有这么多数据，那么每次执行 COUNT 都会返回一个准确的数字 - 每个请求都需要准确吗？最终用户是否需要准确查看报告的 2,000,001 条记录，或者只是“+200 万”可以接受？如果是这样，您可以缓存总数并减少更新频率吗？您只需要该数字即可返回确切的总数，除非您已经分页到最后，否则 Datatables 不会实际使用它。您可以将数据表配置为不报告确切的总数 - 所以只需给他们一个估计值。但是，您将始终需要过滤后的数字进行分页 - 但现在您每次只需要 2 个查询而不是 3 个。

  2. 如此庞大的数据集往往是只读的，并且只是随着时间的推移而附加，例如销售记录。您可以为其添加自定义数据库索引（例如，使用日期按年份过滤），并确保您的 WHERE 子句首先使用此检查，除了 Datatables 搜索请求实际上是什么？请注意，Datatables 允许您为每个 AJAX 请求客户端添加自定义参数，因此您可以使用外部选择器（例如，带有年份的组合框，默认为当前年份）来帮助在索引有用的查询表达式中更早地过滤结果。

  3. 仅对某些列单独使用专用输入搜索字段并专门应用它们，而不是使用一个通用输入字段，该输入字段在每一列上使用搜索条件：where field_name LIKE 'search input*'。Datatables 支持每列过滤器，但我也看到了一些服务器端实现，所有列都有一个搜索输入，这也可以处理大型数据集。在任何情况下，并非所有列都需要始终进行搜索。

  4. 您一次不会返回超过几百个对最终用户有意义的结果 - 使用 LIMIT 子句，一次最多返回 1000 个结果，如果此限制为，则向用户抱怨输入更好的搜索条件超过。数据表可以请求很多，但您的服务器不必接受它。

  5. 索引可以提高性能，但代价是它们使用了更多空间。为用户常用的查询设置特定索引。

  6. 考虑“去抖动”搜索输入，通过添加仅在用户停止输入一段时间（例如 1 秒）后才提交搜索请求的 javascript 超时。

  7. 我已经将 BLOB 和静态文本数据从数据库移到磁盘文件或像 Mongo 这样的 NOSQL 替代方案，并确保两种实现的主键匹配。您会发现您将使用的任何数据库肯定会更快地使用，包括执行备份，我相信您会这样做。

您的里程可能会有所不同，但正如您所知道的，您可以做很多事情来提高性能 - 只是不要每次都尝试绝对查询所有内容。没有人关心对庞大数据集的确切计数，如果他们这样做了，那么他们真正想要的是一份报告，这是一个不涉及数据表的不同用例。

推荐阅读