MySQL sending data status

Select * From searchzh where modified_date> '2017-09-02 14:45:22 ';

Performance of a MySQL query statement: sending data takes 10 minutes ....

+ -------------------------------- + ----------- +
| Status | duration |
+ -------------------------------- + ----------- +
| (Initialization) | 0.000006 |
| Checking query cache for query | 0.000025 |
| Check permissions | 0.000008 |
| Opening tables | 0.000148 |
| System lock | 0.000008 |
| Table lock | 0.000008 |
| Init | 1, 0.000007 |
| Check permissions | 0.000045 |
| Optimizing | 0.000016 |
| Statistics | 0.00027 |
| Preparing | 0.000034 |
| Executing | 0.000005 |
| Sendingdata | 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)

 

Cause:

Searchzh view-related tables have frequent update, insert, and delete operations. In MySQL's table locking mechanism, select statements have lower priority than update statements. Therefore, this select statement remains in the lock queue, and subsequent update operations will jump in front of the SELECT statement. Cause | sending data | 614.25387 |

There are multiple solutions, the simplest:

Select high_priority * From searchzh where modified_date> '2017-09-02 14:45:22 ';

Specifies that the SELECT statement has a high priority.

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

Mysql15: 21: 30> show status like 'table % ';
+ ----------------------- + ----------- +
| Variable_name | value |
+ ----------------------- + ----------- +
| Table_locks_immediate | 120977304 |
| Maid | 577 |
+ ----------------------- + ----------- +

Table lock contention is serious.

Inodb may be better used in scenarios with frequent updates and queries.