在NodeJS中使用带有大量数据的Datatable时搜索错误

I have around 5,00,000 records in my database. I'm using DataTables to build an admin panel to manage the records. I have Node.js as backed with MongoDB.

我的数据库中有大约5,00,000条记录。我正在使用DataTables来构建管理面板来管理记录。我有Node.js作为MongoDB的支持。

I have used this library - https://www.npmjs.com/package/datatables-query

我使用过这个库 - https://www.npmjs.com/package/datatables-query

So far on page load I have successfully loaded results a shown in the below Image.

到目前为止，在页面加载时我已成功加载结果，如下图所示。

Whenever I type something in the search box, I get 500 error as shown in the screenshot.

每当我在搜索框中输入内容时，我都会收到500错误，如屏幕截图所示。

What could be the problem here?

这可能是什么问题？

Is DataTable a good option for showing grid with huge amount of data or is there any better option considering Node.js, Express and MongoDB combo?

DataTable是显示具有大量数据的网格的好选择还是考虑Node.js，Express和MongoDB组合有没有更好的选择？

Here is my server side code.

这是我的服务器端代码。

app.post('/getUsersData',function(req, res) {
  var Model = require('./models/user'),
    datatablesQuery = require('datatables-query'),
    params = req.body,
    query = datatablesQuery(Model);

  query.run(params).then(function (users) {
    var data = JSON.stringify(users);
    // var data = JSON.stringify(users);

    res.end(data);
  }, function (err) {
    res.status(500).json(err);
  });
});

I have a table in MongoDB named User with 3 columns

我在MongoDB中有一个名为User with 3 columns的表

1) Name

1）姓名

2) Email

2）电子邮件

3) Password

3）密码

$(document).ready(function() {
  var table = $('#datatable').DataTable({
    // dom: 'Bfrtip',
    processing: true,
    serverSide: true,
    order: [[1, 'asc']],
    "aoColumnDefs": [ { "sClass": "hide_me", "aTargets": [ 0 ], visible: false } ], // first column in visible columns array gets class "hide_me"
    ajax: {
        url: "/getUsersData",
        type: 'POST',
        dataSrc: "data"
    },
    columns: [
        { data : "_id"},
        { data : "name" },
        { data : "email" },
        { data : "password" },
   ],
   responsive: true
  });
});

2 个解决方案

#1

I would probably create an index on the data and search that index rather than searching the data itself. If you do however create a full text index you need to have a combination off all your cols in your collection and mongo allows only 1 full text index per collection.

我可能会创建一个数据索引并搜索该索引而不是搜索数据本身。但是，如果您创建了全文索引，则需要将所有cols组合在一起，而mongo只允许每个集合使用1个全文索引。

As for alternatives, you could look into AWS's Elastic Search (which works just fine with MongoDB) or Sphinx Index (based on PostreSQL)

至于替代方案，您可以查看AWS的弹性搜索（适用于MongoDB）或Sphinx索引（基于PostreSQL）

Edit: I know this answer doesn't actually answer the question in the slightest but I fear that the 500 error is not a memory issue in the application but rather on the DB (mongo is not like SQL, so don't design your applications like you would in SQL).

编辑：我知道这个答案实际上并没有真正回答这个问题但是我担心500错误不是应用程序中的内存问题而是数据库（mongo不像SQL，所以不要设计你的应用程序就像你在SQL中那样）。

Some reading material, if you plan on changing db structure

一些阅读材料，如果你打算改变数据库结构

http://rachbelaid.com/postgres-full-text-search-is-good-enough/
https://about.gitlab.com/2016/03/18/fast-search-using-postgresql-trigram-indexes/

http://rachbelaid.com/postgres-full-text-search-is-good-enough/ https://about.gitlab.com/2016/03/18/fast-search-using-postgresql-trigram-indexes/

#2

The key here is figuring out what exactly went wrong on the DB or server side layer. The error message that you are alerting out is a generic message from the datatables library which points you to the following documentation: https://datatables.net/manual/tech-notes/7

这里的关键是弄清楚DB或服务器端层究竟出了什么问题。您发出警告的错误消息是来自数据表库的通用消息，它指向以下文档：https：//datatables.net/manual/tech-notes/7

The documentation linked to above says that this error will occur anytime a non-200 response is returned from the server. In this case you are explicitly throwing a 500 error in the error handler/callback of the datatables query call, but that doesn't give you/us the information we need!

链接到上面的文档说，只要从服务器返回非200响应，就会发生此错误。在这种情况下，您明确地在数据表查询调用的错误处理程序/回调中抛出了500错误，但这并没有为您提供我们所需的信息！

The next step is to look at the response body in the network tab of your browser's console, since you are serializing the error as json and sending it along with your 500 response, or to console.log out the err in the error handler/callback in your node code. This will get you an understanding of what is actually going wrong, and with that information we can more accurately prescribe a solution. Post an update with whatever you log out please!

下一步是在浏览器控制台的网络选项卡中查看响应主体，因为您将错误序列化为json并将其与500响应一起发送，或者发送到console.log中错误处理程序/回调中的错误在您的节点代码中。这将使您了解实际出现的问题，通过这些信息，我们可以更准确地规定解决方案。请发布更新，无论您注销了什么！

#1