一条更新语句，导致数据库卡死，但从OS层面看没有数据库负载
 update wlflow.`crm_fm_result_info` set result_status = '已取消',end_date = '2016-07-31 23:59:59',bak2 = '20160805数据对比联通侧无订购'
WHERE `prod_rowid` = '144' AND `result_status` = '成功'
 AND `create_date` > '2016-05-08 15:30' AND `create_date` < '2016-08-01'
 AND `phone` NOT IN (
 SELECT phone FROM report.`ltspfb_20160805`)  

 
 进程列表中卡住的sql已经执行了9万多(96309)秒了
 mysql> show full processlist;
+-----------+--------------+---------------------+--------+---------+-------+-------+-----------------------+-----------+---------------+
| Id        | User         | Host                | db     | Command | Time  | State | Info                  | Rows_sent | Rows_examined |
+-----------+--------------+---------------------+--------+---------+-------+-------+-----------------------+-----------+---------------+
| 104716330 | ssx_dbmanage | 10.25.251.101:47720 | wlflow | Sleep   | 21082 |       | NULL                  |         1 |             0 |
| 104723084 | ssx_dbmanage | 10.25.251.101:48246 | wlflow | Sleep   |  9832 |       | NULL                  |         1 |             0 |
| 104724995 | ssx_dbmanage | 10.25.251.101:55815 | wlflow | Sleep   |   972 |       | NULL                  |         1 |             0 |
| 104725661 | report_sc    | 10.25.20.16:64246  | report | Query   |     96309 |       |  update wlflow.`crm_fm_result_info` set result_status = '已取消',end_date = '2016-07-31 23:59:59',bak2 = '20160805数据对比联通侧无订购'
WHERE `prod_rowid` = '144' AND `result_status` = '成功'
 AND `create_date` > '2016-05-08 15:30' AND `create_date` < '2016-08-01'
 AND `phone` NOT IN (
 SELECT phone FROM report.`ltspfb_20160805`)                   |         1 |             0 |




1千2百万条数据
mysql> show table status like 'crm_fm_result_info';
+--------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name               | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| crm_fm_result_info | InnoDB |      10 | Compact    | 11676615 |            219 |  2564784128 |               0 |   3132653568 |   7340032 |       12082756 | 2016-06-17 17:16:56 | NULL        | NULL       | utf8_general_ci | NULL     |                |         |
+--------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set

子表：1百5十4万条数据
mysql> show table status like 'ltspfb_20160805';
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| ltspfb_20160805 | InnoDB |      10 | Compact    | 1543803 |             40 |    62488576 |               0 |            0 |   4194304 | NULL           | 2016-08-05 15:20:02 | NULL        | NULL       | utf8_general_ci | NULL     |                |         |
+-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set

mysql> SHOW INDEX FROM report.ltspfb_20160805;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ltspfb_20160805 |          1 | phone    |            1 | phone       | A         |     1543803 | NULL     | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

mysql> show index from wlflow.crm_fm_result_info;
+--------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| crm_fm_result_info |          0 | PRIMARY       |            1 | rowid         | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | phone         |            1 | phone         | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | result_status |            1 | result_status | A         |        3222 | NULL     | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | end_date      |            1 | end_date      | A         |     2633994 | NULL     | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | bak5          |            1 | bak5          | A         |     1505139 |      255 | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | start_date    |            1 | start_date    | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_2       |            1 | phone         | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_2       |            2 | prod_rowid    | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_2       |            3 | result_status | A         |    10535978 | NULL     | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | prod_rowid_2  |            1 | prod_rowid    | A         |       26208 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | prod_rowid_2  |            2 | cust_id       | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | prod_rowid_2  |            3 | end_date      | A         |    10535978 | NULL     | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | prod_rowid    |            1 | prod_rowid    | A         |       16566 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | prod_rowid    |            2 | create_date   | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | prod_rowid    |            3 | result_status | A         |    10535978 | NULL     | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_3       |            1 | phone         | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_3       |            2 | prod_rowid    | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_3       |            3 | result_status | A         |    10535978 | NULL     | NULL   | YES  | BTREE      |         |               |
| crm_fm_result_info |          1 | phone_3       |            4 | create_date   | A         |    10535978 | NULL     | NULL   |      | BTREE      |         |               |
+--------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 rows in set

改成select看解析计划
mysql> explain select count(1)
from  wlflow.`crm_fm_result_info` a
WHERE `prod_rowid` = '144' AND `result_status` = '成功'
 AND `create_date` > '2016-05-08 15:30' AND `create_date` < '2016-08-01'
 AND phone not in  (
 SELECT phone FROM report.`ltspfb_20160805` b )  ;
+----+-------------+-------+-------+---------------------------------------+---------------+---------+-------+---------+------------------------------------+
| id | select_type | table | type  | possible_keys                         | key           | key_len | ref   | rows    | Extra                              |
+----+-------------+-------+-------+---------------------------------------+---------------+---------+-------+---------+------------------------------------+
|  1 | PRIMARY     | a     | ref   | result_status,prod_rowid_2,prod_rowid | result_status | 303     | const | 5835987 | Using index condition; Using where |
|  2 | SUBQUERY    | b     | index | phone                                 | phone         | 303     | NULL  | 1543803 | Using index                        |
+----+-------------+-------+-------+---------------------------------------+---------------+---------+-------+---------+------------------------------------+
2 rows in set

改成关联查询：（要求B表中phone唯一）
mysql> explain select count(1)
from  wlflow.`crm_fm_result_info` a,report.`ltspfb_20160805` b
WHERE a.`phone` = b.`phone`
and `prod_rowid` = '144' AND `result_status` = '成功'
 AND `create_date` > '2016-05-08 15:30' AND `create_date` < '2016-08-01';
+----+-------------+-------+-------+-----------------------------------------------------+-------+---------+----------------+---------+--------------------------+
| id | select_type | table | type  | possible_keys                                       | key   | key_len | ref            | rows    | Extra                    |
+----+-------------+-------+-------+-----------------------------------------------------+-------+---------+----------------+---------+--------------------------+
|  1 | SIMPLE      | b     | index | phone                                               | phone | 303     | NULL           | 1543803 | Using where; Using index |
|  1 | SIMPLE      | a     | ref   | phone,result_status,phone_2,prod_rowid_2,prod_rowid | phone | 302     | report.b.phone |       1 | Using where              |
+----+-------------+-------+-------+-----------------------------------------------------+-------+---------+----------------+---------+--------------------------+
2 rows in set


mysql 建索引 是锁表的，可以用一下shell命令

改造后台的SQL如下：
update  wlflow.`crm_fm_result_info` a,report.`ltspfb_20160805` b
set a.result_status = '已取消',
    a.end_date = '2016-07-31 23:59:59',
    a.bak2 = '20160805数据对比联通侧无订购'
WHERE a.`phone` = b.`phone`
and `prod_rowid` = '144' AND `result_status` = '成功'
 AND `create_date` > '2016-05-08 15:30'
 AND `create_date` < '2016-08-01';