在最短的时间内从数据库中获取数据

I am using mysql and node.js. I have a button 'load previous chat' to load chat of two users. When i am clicking on that button it is giving result @ 12 lines/second (tested manually).How can i make it fast? Here is my code

我正在使用mysql和node.js.我有一个按钮'加载以前的聊天'来加载两个用户的聊天。当我点击那个按钮时它会给出结果@ 12行/秒(手动测试)。我怎样才能让它快速?这是我的代码

On server side-

在服务器端 -

socket.on('chatHistory', function(data){
    var count=0;
    connection.query("SELECT * FROM chatRecord WHERE uniqueRoomId = '"+data.roomId+"' ", function(err, result, fields) {
        if (err) throw err;


        for (var j in result) 
        {
            if (rows3.hasOwnProperty(j)) count++;
        }
        var k=0;
        if(result[0])
        {
            socket.emit('chatHistoryMaintain', {result:result,sum:count,nickname:data.nickname});
        }
    });
    });

Client side Code -

客户端代码 -

socket.on('chatHistoryMaintain', function(data){
    for(i=0;i<data.sum;i++)
    {
        if(data.result[i].sender==data.nickname) 
        {
          $('#chat-messages ul').append('<li class="marker"><div class="fl sender">'+nickname+' : </div><div class="fl text1">'+data.result[i].msg+'</div></li>');
        }
        else 
        {
          $('#chat-messages ul').append('<li class="marker"><div class="fl receiver">'+nickname+' : </div><div class="fl text">'+data.result[i].msg+'</div></li>');
        }
     }
});

How can i optimize it more as my application is giving results @ 12 lines/second whereas skype is as fast as 1560 lines/second ? What i am doing wrong?

我如何优化它,因为我的应用程序提供@ 12行/秒的结果,而Skype的速度高达1560行/秒?我做错了什么?

Here is details of my database engine

这是我的数据库引擎的详细信息

>     +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
>     | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
>     +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
>     | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
>     | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
>     | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
>     | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO      
> |
>     | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
>     | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
>     | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
>     | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
>     | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
>     +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

1 个解决方案

#1

In order to make it faster you first must find out why is it slow in the first place

为了使速度更快,首先必须先了解为什么它会变慢

Use EXPLAIN to find out if your query is doing row scan or using indexes and which ones.

使用EXPLAIN查明您的查询是在进行行扫描还是使用索引和哪些查询。

if you feel harassed by EXPLAIN output, try doing SELECT COUNT(*)... with same WHERE clause instead. If it is slow too, the index is probably the problem.

如果您感觉被EXPLAIN输出所骚扰,请尝试使用相同的WHERE子句执行SELECT COUNT(*)...如果它也很慢,那么索引可能就是问题所在。

Check if doing the query from cli client is also that slow

检查从cli客户端执行查询是否也很慢

If the problem is in query doing row scan, adjust your indexes. If mysql has no problem finding the data but rather returning it, don't wait for all 30k records to be pumped through socket to return them all in one batch. Consider using asynchronous fetching of rows instead (it's node.js after all!), as described in official doc. Putting a lot of records in node.js process memory at once is not a good idea, you can try monitoring memory usage of the process.

如果问题出在查询中进行行扫描,请调整索引。如果mysql在查找数据时没有问题,而是返回它,请不要等待所有30k记录通过套接字泵送到一个批次中全部返回。考虑使用行的异步提取(毕竟是node.js!),如官方文档中所述。在node.js进程内存中放入大量记录不是一个好主意,您可以尝试监视进程的内存使用情况。

#1