mysql sending data 耗时_mysql sending data状态时间花费太大

select * from searchzh where modified_date > '2009-09-02 14:45:22';

一条mysql查询语句的性能：sending data 耗时10分钟。。。。

+--------------------------------+-----------+

| Status | Duration |

+--------------------------------+-----------+

| (initialization) | 0.000006 |

| checking query cache for query | 0.000025 |

| checking permissions | 0.000008 |

| Opening tables | 0.000148 |

| System lock | 0.000008 |

| Table lock | 0.000008 |

| init | 0.000007 |

| checking permissions | 0.000045 |

| optimizing | 0.000016 |

| statistics | 0.00027 |

| preparing | 0.000034 |

| executing | 0.000005 |

| Sending data | 614.25387 |

| end | 0.000046 |

| query end | 0.000012 |

| freeing items | 0.000017 |

| closing tables | 0.000008 |

| logging slow query | 0.000054 |

+--------------------------------+-----------+

18 rows in set (0.01 sec)

产生的原因：

searchzh视图相关的表，update，insert，delete操作频繁。在mysql的表锁定机制中，select语句的优先级比update低。所以此select语句一直在锁队列中等待，后来的update操作会插队到select语句前面。导致| Sending data | 614.25387 |

解决方法有多种，最简单的：

select HIGH_PRIORITY * from searchzh where modified_date > '2009-09-02 14:45:22';

指定该select语句具有高优先级。

=======================================

mysql15:21:30> SHOW STATUS LIKE 'Table%';

+-----------------------+-----------+

| Variable_name | Value |

+-----------------------+-----------+

| Table_locks_immediate | 120977304 |

| Table_locks_waited | 577 |

+-----------------------+-----------+

表锁争用现象比较严重。

在频繁更新和查询的情景下，使用innodb也许更好。

转载