sending data->索引问题

最近,有两次Tomcat 响应很慢,直到宕机。期间登录mysql server ,使用show processlist均能看到大量的sending data 状态。查看官方的关于sending data说明:
  • Sending data

    The thread is processing rows for a statement and also is sending data to the client.

意思是正在将查询结果往客户端发送。


开始一直以为是max_allow_packet的值太小,并且认为程序有问题导致tomcat响应很慢。mysql 将查询的结果发送给前端tomcat.而此时tomcat因程序问题响应很慢,所以mysql将查询到的结果一直处理Sending data状态。但是让我不明白的是我前端有两个同样的tomcat,为什么同时都有问题。

后来仔细分析了两次出现大量Sending data的sql语句。发现大部分都是针对一个表上的查询。而且where 条件的那个字段值只有几种,大部分都是相同的值。这样在上面建索引根本没有任何的效果。同时用profile验证了这个sql查询每个阶段所需要的时间。发现在Sending data 上花费的时间最长。expalin 出来的结果显示共扫描了161172行。

mysql> explain select P.ID_ FROM NEWSFEEDDIST P WHERE RELATIONSHIP_ = 1 and CREATED_ >= DATE_SUB(NOW(),INTERVAL 18 DAY) AND CREATED_ < DATE_SUB(NOW(),INTERVAL 17 DAY);        +----+-------------+-------+------+-------------------------+-------------------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys           | key                     | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+-------------------------+-------------------------+---------+-------+--------+-------------+
| 1 | SIMPLE      | P     | ref | SNS_NEWSFEEDDIST_REL_IN | SNS_NEWSFEEDDIST_REL_IN | 3       | const | 161172 | Using where |
+----+-------------+-------+------+-------------------------+-------------------------+---------+-------+--------+-------------+

mysql> set profiling =1;

mysql> SELECT COUNT(1), MAX(P.CREATED_) FROM NEWSFEEDDIST P WHERE RELATIONSHIP_ = 1 AND CREATED_ >= DATE_SUB(NOW(),INTERVAL 19 DAY) AND CREATED_ < DATE_SUB(NOW(),INTERVAL 18 DAY);

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.060039 |
| checking query cache for query | 0.000161 |
| Opening tables                 | 0.000034 |
| System lock                    | 0.000019 |
| Table lock                     | 0.000021 |
| init                           | 0.000095 |
| optimizing                     | 0.000035 |
| statistics                     | 0.000157 |
| preparing                      | 0.000040 |
| executing                      | 0.000010 |
| Sending data                   | 1.079902 |
| end                            | 0.000018 |
| query end                      | 0.000005 |
| freeing items                  | 0.000056 |
| logging slow query             | 0.000004 |
| logging slow query             | 0.000053 |
| cleaning up                    | 0.000007 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

既然 RELATIONSHIP_ 字段上的索引无效,那么针对这个sql我在CREATED_字段上创建了一个索引。再次运行
mysql>alter table NEWSFEEDDIST add index(CREATED_);
mysql>SELECT COUNT(1), MAX(P.CREATED_) FROM NEWSFEEDDIST P WHERE RELATIONSHIP_ = 1 AND CREATED_ >= DATE_SUB(NOW(),INTERVAL 19 DAY) AND CREATED_ < DATE_SUB(NOW(),INTERVAL 18 DAY);
mysql>show profile for query 3;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000048 |
| checking query cache for query | 0.000130 |
| Opening tables                 | 0.000028 |
| System lock                    | 0.000011 |
| Table lock                     | 0.000019 |
| init                           | 0.000076 |
| optimizing                     | 0.000031 |
| statistics                     | 0.000183 |
| preparing                      | 0.000034 |
| executing                      | 0.000011 |
| Sending data                   | 0.007954 |
| end                            | 0.000013 |
| query end                      | 0.000009 |
| freeing items                  | 0.000088 |
| logging slow query             | 0.000009 |
| cleaning up                    | 0.000011 |
+--------------------------------+----------+
16 rows in set (0.00 sec)
此时在sending data上小了很多。而且用了CREATED_索引后扫描的行数变成了2888条

mysql>explain select P.ID_ FROM test_NEWSFEEDDIST P WHERE RELATIONSHIP_ = 1 and CREATED_ >= DATE_SUB(NOW(),INTERVAL 18 DAY) AND CREATED_ < DATE_SUB(NOW(),INTERVAL 17 DAY);                                    
+----+-------------+-------+-------+----------------------------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys                    | key      | key_len | ref | rows | Extra       |
+----+-------------+-------+-------+----------------------------------+----------+---------+------+------+-------------+
| 1 | SIMPLE      | P     | range | SNS_NEWSFEEDDIST_REL_IN,CREATED_ | CREATED_ | 9       | NULL | 2888 | Using where |
+----+-------------+-------+-------+----------------------------------+----------+---------+------+------+-------------+


这个效果还是不错的。cool


这里有个关于mysql sending data状态的解释
http://renxijun.blog.sohu.com/82906360.html

 

http://hi.baidu.com/hexie007/item/126939785b86353c714423cd

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
void SerialApp_ProcessMSGCmd( afIncomingMSGPacket_t *pkt ){ uint8 stat; uint8 seqnb; uint8 delay; switch ( pkt->clusterId ) { // A message with a serial data block to be transmitted on the serial port. case SERIALAPP_CLUSTERID1: // Store the address for sending and retrying. osal_memcpy(&SerialApp_RxAddr, &(pkt->srcAddr), sizeof( afAddrType_t )); seqnb = pkt->cmd.Data[0]; // Keep message if not a repeat packet if ( (seqnb > SerialApp_RxSeq) || // Normal ((seqnb < 0x80 ) && ( SerialApp_RxSeq > 0x80)) ) // Wrap-around { // Transmit the data on the serial port. if ( HalUARTWrite( SERIAL_APP_PORT, pkt->cmd.Data+1, (pkt->cmd.DataLength-1) ) ) { // Save for next incoming message SerialApp_RxSeq = seqnb; stat = OTA_SUCCESS; } else { stat = OTA_SER_BUSY; } } else { stat = OTA_DUP_MSG; } // Select approproiate OTA flow-control delay. delay = (stat == OTA_SER_BUSY) ? SERIALAPP_NAK_DELAY : SERIALAPP_ACK_DELAY; // Build & send OTA response message. SerialApp_RspBuf[0] = stat; SerialApp_RspBuf[1] = seqnb; SerialApp_RspBuf[2] = LO_UINT16( delay ); SerialApp_RspBuf[3] = HI_UINT16( delay ); osal_set_event( SerialApp_TaskID, SERIALAPP_RESP_EVT ); osal_stop_timerEx(SerialApp_TaskID, SERIALAPP_RESP_EVT); break; // A response to a received serial data block. case SERIALAPP_CLUSTERID2: if ((pkt->cmd.Data[1] == SerialApp_TxSeq) && ((pkt->cmd.Data[0] == OTA_SUCCESS) || (pkt->cmd.Data[0] == OTA_DUP_MSG))) { SerialApp_TxLen = 0; osal_stop_timerEx(SerialApp_TaskID, SERIALAPP_SEND_EVT); } else { // Re-start timeout according to delay sent from other device. delay = BUILD_UINT16( pkt->cmd.Data[2], pkt->cmd.Data[3] ); osal_start_timerEx( SerialApp_TaskID, SERIALAPP_SEND_EVT, delay ); } break; case SERIALAPP_CONNECTREQ_CLUSTER: SerialApp_ConnectReqProcess((uint8*)pkt->cmd.Data); case SERIALAPP_CONNECTRSP_CLUSTER: SerialApp_DeviceConnectRsp((uint8*)pkt->cmd.Data); default: break; }}每行代码注释
最新发布
06-06
这段代码是一个处理ZigBee串口应用的主函数,主要功能是接收并处理来自ZigBee网络中其他节点发送的消息和命令。以下是每行代码的注释解释: ```C void SerialApp_ProcessMSGCmd( afIncomingMSGPacket_t *pkt ) { uint8 stat; uint8 seqnb; uint8 delay; switch ( pkt->clusterId ) { // A message with a serial data block to be transmitted on the serial port. case SERIALAPP_CLUSTERID1: // Store the address for sending and retrying. osal_memcpy(&SerialApp_RxAddr, &(pkt->srcAddr), sizeof( afAddrType_t )); seqnb = pkt->cmd.Data[0]; // Keep message if not a repeat packet if ( (seqnb > SerialApp_RxSeq) || // Normal ((seqnb < 0x80 ) && ( SerialApp_RxSeq > 0x80)) ) // Wrap-around { // Transmit the data on the serial port. if ( HalUARTWrite( SERIAL_APP_PORT, pkt->cmd.Data+1, (pkt->cmd.DataLength-1) ) ) { // Save for next incoming message SerialApp_RxSeq = seqnb; stat = OTA_SUCCESS; } else { stat = OTA_SER_BUSY; } } else { stat = OTA_DUP_MSG; } // Select approproiate OTA flow-control delay. delay = (stat == OTA_SER_BUSY) ? SERIALAPP_NAK_DELAY : SERIALAPP_ACK_DELAY; // Build & send OTA response message. SerialApp_RspBuf[0] = stat; SerialApp_RspBuf[1] = seqnb; SerialApp_RspBuf[2] = LO_UINT16( delay ); SerialApp_RspBuf[3] = HI_UINT16( delay ); osal_set_event( SerialApp_TaskID, SERIALAPP_RESP_EVT ); osal_stop_timerEx(SerialApp_TaskID, SERIALAPP_RESP_EVT); break; // A response to a received serial data block. case SERIALAPP_CLUSTERID2: if ((pkt->cmd.Data[1] == SerialApp_TxSeq) && ((pkt->cmd.Data[0] == OTA_SUCCESS) || (pkt->cmd.Data[0] == OTA_DUP_MSG))) { SerialApp_TxLen = 0; osal_stop_timerEx(SerialApp_TaskID, SERIALAPP_SEND_EVT); } else { // Re-start timeout according to delay sent from other device. delay = BUILD_UINT16( pkt->cmd.Data[2], pkt->cmd.Data[3] ); osal_start_timerEx( SerialApp_TaskID, SERIALAPP_SEND_EVT, delay ); } break; case SERIALAPP_CONNECTREQ_CLUSTER: SerialApp_ConnectReqProcess((uint8*)pkt->cmd.Data); case SERIALAPP_CONNECTRSP_CLUSTER: SerialApp_DeviceConnectRsp((uint8*)pkt->cmd.Data); break; default: break; } } ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值