處理mysql使用in關鍵字子查詢1317錯誤 Error 1317 mysql query execution interrupted 消息內容：查詢執行被中斷（數據庫直接掛起） 1. 現象： （1）在PHP程序中使用子查詢語句，導致Mysql自動“掛起”，即數據庫“卡死”，程序不能正常運行 （2）在mysql命令行執行子查詢語句，Mysql需要等待較長時間，提示 “ Error 1317 mysql query execution interrupted” 2. 處理辦法有兩種 ： 006_kh表記錄數目共計為 24256 條 uzone_2701_kh 表中記錄數目共計為 52327條 原始SQL語句（子查詢）： [html] SELECT count(kh_id) FROM `006_kh` WHERE kh_id in (select khbh from uzone_2701_kh where uzbh ='180' and jgm='27010899') 使用 desc 命令分析，結果如下: [html] mysql> mysql> desc SELECT count(kh_id) FROM `006_kh` WHERE kh_id in (select khbh from uzone_2701_kh where uzbh ='180' and jgm='27010899') ; +----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | PRIMARY | 006_kh | index | NULL | PRIMARY | 4 | NULL | 89394 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | uzone_2701_kh | ALL | NULL | NULL | NULL | NULL | 24256 | Using where | +----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+ 2 rows in set (0.00 sec) （1） 第一種方式： sql腳本 [html] select count(kh_id) FROM `006_kh` where kh_id in(select khbh from (select khbh from uzone_2701_kh where uzbh ='180' and jgm='27010899') as khid_array) 使用 desc 命令分析，結果如下: [html] mysql> desc select count(kh_id) FROM `006_kh` where kh_id in(select khbh from (select khbh from uzone_2701_kh where uzbh ='180' and jgm='27010899') as khid_array) ; +----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | PRIMARY | 006_kh | index | NULL | PRIMARY | 4 | NULL | 96767 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 24099 | Using where | | 3 | DERIVED | uzone_2701_kh | ALL | NULL | NULL | NULL | NULL | 24256 | Using where | +----+--------------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+ 3 rows in set (0.02 sec) （2）第二種方式 ： sql腳本 ： [html] select count(a.kh_id) from 011_kh a inner join uzone_2701_kh b on a.kh_id = b.khbh where b.uzbh ='180' and b.jgm='27010899' 使用 desc 命令分析，結果如下: [html] mysql> mysql> desc select count(a.kh_id) from 011_kh a inner join uzone_2701_kh b on a.kh_id = b.khbh where b.uzbh ='180' and b.jgm='27010899' ; +----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 24256 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | dxzs_v2_new.b.khbh | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+ 2 rows in set (0.00 sec) 個人試驗結論：使用JOIN語句的查詢不一定總比使用子查詢的語句快，根據我自己的試驗結果和DESC分析結果 來說，還是JOIN語句比較快，效率比較高；因此，當使用in關鍵字進行子查詢，效率低下時，強烈推薦第二種！
