并发高时,出现锁等待:

----------------------------
*************************** 1. row ***************************
 waiting_trx_id: 14537594
 waiting_thread: 11373
INSERT into sch_work_time_temp(sch_work_time_id,SCH_WORK_TIME_STAT_ID,S_HOUR,S_MINUTE,WORK_DATE,SCH_DEVICE_CODE,SCH_STU_DEVICE_CODE,CRT_TIME,LOCK_FLAG)select sch_work_time_id,sch_work_time_stat_id,s_hour,s_minute,work_date,sch_device_code,SCH_STU_DEVICE_CODE,NOW() crt_time,null lock_flag from sch_work_time where sch_work_time_id>(select conf_value from dim_sys_conf where conf_id = 'PUSH_WORK_ID' ) ORDER BY sch_work_time_id   LIMIT 0,500
blocking_trx_id: 14537336
blocking_thread: 11365
 blocking_query: select  CONCAT_WS('_',SCH_GRADE_ID,SCH_KQ_TYPE_ID,SCH_KQJ_TYPE_ID) KEYSTR  ,S_MINUTE,S_MINUTE1,S_MINUTE2,SCH_TIME_ID,SCH_WP_ID from sch_rule WHERE SCH_WEEK_ID = (DAYOFWEEK(NOW())-1) AND SCH_GRADE_ID IN (SELECT DISTINCT B.SCH_GRADE_ID FROM sch_work_time_temp A LEFT JOIN sch_student B ON A.SCH_STU_DEVICE_CODE = B.SCH_STU_DEVICE_CODE AND B.VALID_FLAG=1 AND B.DEL_FLAG=0 WHERE A.LOCK_FLAG = '201709270957')
1 row in set (0.00 sec)

show procsslist:

| 11365 | lgl  | jxweb11:46200        | bdzh | Query       |     28 | Sending data                                                          | select  CONCAT_WS('_',SCH_GRADE_ID,SCH_KQ_TYPE_ID,SCH_KQJ_TYPE_ID) KEYSTR  ,S_MINUTE,S_MINUTE1,S_MIN |
| 11369 | lgl  | jxweb11:48568        | bdzh | Sleep       |   3728 |                                                                       | NULL                                                                                                 |
| 11370 | lgl  | jxweb11:48570        | bdzh | Sleep       |   3728 |                                                                       | NULL                                                                                                 |
| 11371 | lgl  | jxweb11:48572        | bdzh | Sleep       |   3728 |                                                                       | NULL                                                                                                 |
| 11372 | lgl  | jxweb11:48574        | bdzh | Sleep       |     23 |                                                                       | NULL                                                                                                 |
| 11373 | lgl  | jxweb11:48726        | bdzh | Query       |     26 | statistics                                                            | INSERT into sch_work_time_temp(sch_work_time_id,SCH_WORK_TIME_STAT_ID,S_HOUR,S_MINUTE,WORK_DATE,SCH_ |           

11365的查询操作阻塞了11373的插入操作。。。而查询并没锁定表。
两个thread的状态：
Sending data:
The thread is reading and processing rows for a SELECT statement, and sending data to the client.
Because operations occurring during this state tend to perform large amounts of disk access (reads), it is
often the longest-running state over the lifetime of a given query.
statistics：
The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long
time, the server is probably disk-bound performing other work.
上面两种状态直指disk io问题,有可能是io问题引起的阻塞？

so explain select:

+----+-------------+----------+------+---------------+------+---------+------+-------+-------------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra                                                             |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------------------------------------------------------------+
|  1 | SIMPLE      | A        | ALL  | NULL          | NULL | NULL    | NULL |   539 | Using where; Start temporary                                      |
|  1 | SIMPLE      | sch_rule | ALL  | NULL          | NULL | NULL    | NULL |   719 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | B        | ALL  | NULL          | NULL | NULL    | NULL | 15354 | Using where; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------------------------------------------------------------+

create some indexes and explain again:

+----+--------------+-------------+------+---------------------+-------------------+---------+---------------------------------+------+-------------+
| id | select_type  | table       | type | possible_keys       | key               | key_len | ref                             | rows | Extra       |
+----+--------------+-------------+------+---------------------+-------------------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE       | <subquery2> | ALL  | NULL                | NULL              | NULL    | NULL                            | NULL | Using where |
|  1 | SIMPLE       | sch_rule    | ref  | ind_sch_grade_id    | ind_sch_grade_id  | 9       | <subquery2>.SCH_GRADE_ID        |    5 | Using where |
|  2 | MATERIALIZED | A           | ALL  | ind_sch_work_time_1 | NULL              | NULL    | NULL                            |   21 | Using where |
|  2 | MATERIALIZED | B           | ref  | ind_sch_student_1   | ind_sch_student_1 | 183     | bdzh_test.A.SCH_STU_DEVICE_CODE |    1 | Using where |
+----+--------------+-------------+------+---------------------+-------------------+---------+---------------------------------+------+-------------+

and case over...