由一條sql語句想到的子查詢優化

摘要：相信大家都使用過子查詢,因為使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，比較靈活，我也喜歡用，可最近因為一條包含子查詢的select count(*)語句導致點開管理系統的一個功能模塊列表時，耗時44幾秒，到了不可容忍的地步，定位發現是因為未加索引和用了子查詢導致，不 ...

摘要：相信大家都使用過子查詢,因為使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，比較靈活，我也喜歡用，可最近因為一條包含子查詢的select count(*)語句導致點開管理系統的一個功能模塊列表時，耗時44幾秒，到了不可容忍的地步，定位發現是因為未加索引和用了子查詢導致，不加索引導致查詢慢好理解，但子查詢也會引起查詢效率過低嗎？沒錯，所以本文就以這次案例來重新認識下MySQL子查詢。

特別說明：本文介紹的是在MySQL5.5.6版本下子查詢的案例，5.5.29版本的我也試過也會有子查詢效率低的問題。另外有關本文用到的sql及數據都在附錄部分，有需要的可自行下載測試！

一、問題定位過程

1.1 問題現象　　

點擊系統中某個列表功能模塊發現很慢，開啟log日誌發現使用到瞭如下的sql語句來統計符合要求的總記錄數，以進行分頁使用

        select count(*) from (select 
            schedule_id, schedule_code ,resource_code, schedule_type, schedule.oper_id, schedule.oper_time, 
            start_date, end_date, start_time, end_time, img_id, video_id, display_time, 
            schedule_color, terrace_code, stb_types, district_codes, user_group_codes, 
            igroup_code, schedule_status, schedule_description, step_id, owner_id, aud.description, so.oper_name
        from schedule_record as schedule 
            left join auditing_desc_record as aud 
            on schedule.schedule_code = aud.code 
            and aud.is_last_auditing = 1
            left join system_oper as so
            on owner_id = so.oper_id
        where 1=1  and schedule_status = 7  
        order by schedule.schedule_code desc) myCount ;

1.2 explain分析

手動執行該sql發現竟然用了21.18秒，懷疑是未使用索引或者表數據量過大，於是用explain語句分析

explain
select count(*) from (select 
            schedule_id, schedule_code ,resource_code, schedule_type, schedule.oper_id, schedule.oper_time, 
            start_date, end_date, start_time, end_time, img_id, video_id, display_time, 
            schedule_color, terrace_code, stb_types, district_codes, user_group_codes, 
            igroup_code, schedule_status, schedule_description, step_id, owner_id, aud.description, so.oper_name
        from schedule_record as schedule 
            left join auditing_desc_record as aud 
            on schedule.schedule_code = aud.code 
            and aud.is_last_auditing = 1
            left join system_oper as so
            on owner_id = so.oper_id
        where 1=1  and schedule_status = 7  
        order by schedule.schedule_code desc) myCount ;

1.3 改寫sql

當然，看到上圖，我相信很容易看出來是沒有加索引導致全表掃描（有3條type為ALL），查看索引發現確實如此，連接欄位schedule.schedule_code和aud.code都沒使用索引

show index from schedule_record;
show index from auditing_desc_record;

但是更成功引起我註意的是為什麼明明用了明明用了子查詢（內部查詢）只掃描了1827和11265條，最後外部查詢select count(*)卻掃描了1827*11265=20581155條記錄？懷疑是子查詢的導致，於是決定改寫sql，看看不用子查詢的效果

        select 
            count(schedule_code)
        from schedule_record as schedule 
            left join auditing_desc_record as aud 
            on schedule.schedule_code = aud.code 
            and aud.is_last_auditing = 1
            left join system_oper as so
            on owner_id = so.oper_id
        where 1=1  and schedule_status = 7  
        order by schedule.schedule_code desc;

那是因為沒有添加索引才會有子查詢效率低的問題嗎，接下來添加索引再試下

1.4 添加索引

ALTER TABLE auditing_desc_record ADD INDEX index_code (code);
ALTER TABLE schedule_record ADD INDEX index_schedule_code (schedule_code);

再查詢，發現發現不用子查詢效率依然要比用了子查詢效率高些

這樣對比不難發現，在這種情況下，用子查詢效率確實更低，因為這裡每次子查詢每次都需要建立臨時表，它會把結果集都存到臨時表，這樣外部查詢select count(*)又重新掃描一次臨時表，導致用時更長，掃描效率更低

但僅由此得出子查詢效率低似乎太過草莽了。為驗證我的想法，於是網上搜集了一些資料來確認下。

二、更多關於子查詢效率的問題

　　《高性能MySQL》，第4.4節“MySQL查詢優化器的限制”4.4.1小節“關聯子查詢”正好講到這個問題。

MySQL有時優化子查詢很差，特別是在WHERE從句中的IN()子查詢。像上面我碰到的情況，其實我的想法是MySQL會把

select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

變成下麵的樣子

select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);

但不幸的是，實際情況正好相反。MySQL試圖讓它和外面的表產生聯繫來“幫助”優化查詢，它認為下麵的exists形式更有效率

select * from abc_number_prop where exists (select * from abc_number_phone where phone = '82306839' and number_id = abc_number_prop.number_id);

由此看，在這兩種場合缺失不太適合使用子查詢，當然文中說到：但是總是認為子查詢效率很差也是不對的，有時候可能子查詢更好些。怎麼確定這個事情呢，應該經過評測來決定（執行查詢、用desc/explain等來看）。

在網上也能找到《高性能MySQL》的這節內容

參考資料4：MySQL 資料庫優化（12）Limitations of the MySQL Query Optimizer

三、附錄

3.1 表結構

-- ----------------------------
-- Table structure for auditing_desc_record
-- ----------------------------
DROP TABLE IF EXISTS `auditing_desc_record`;
CREATE TABLE `auditing_desc_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL COMMENT '記錄編號',
  `module_flag` int(5) NOT NULL COMMENT '模塊標識',
  `oper_id` int(11) NOT NULL COMMENT '操作人',
  `oper_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '操作時間',
  `status` int(5) NOT NULL COMMENT '記錄狀態',
  `description` varchar(250) NOT NULL COMMENT '審核說明',
  `is_last_auditing` int(2) NOT NULL COMMENT '是否最後一次審核',
  `auditing_count` int(5) NOT NULL COMMENT '記錄審核流程次數',
  `reaudit_description` varchar(250) DEFAULT NULL,
  `is_last_reauditing` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14518 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for schedule_record
-- ----------------------------
DROP TABLE IF EXISTS `schedule_record`;
CREATE TABLE `schedule_record` (
  `schedule_id` int(11) NOT NULL AUTO_INCREMENT,
  `schedule_code` varchar(30) NOT NULL,
  `schedule_type` int(5) NOT NULL,
  `resource_code` varchar(30) DEFAULT NULL,
  `oper_id` int(11) DEFAULT NULL,
  `oper_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `start_time` int(10) NOT NULL,
  `end_time` int(10) NOT NULL,
  `img_id` int(11) DEFAULT NULL,
  `video_id` int(11) DEFAULT NULL,
  `display_time` int(5) DEFAULT NULL,
  `schedule_color` varchar(8) NOT NULL,
  `terrace_code` varchar(30) DEFAULT NULL,
  `stb_types` text,
  `district_codes` text,
  `user_group_codes` text,
  `igroup_code` varchar(50) DEFAULT NULL,
  `schedule_status` int(5) NOT NULL,
  `schedule_description` varchar(200) DEFAULT NULL,
  `step_id` int(11) DEFAULT NULL,
  `owner_id` int(11) NOT NULL DEFAULT '55',
  PRIMARY KEY (`schedule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2534 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for system_oper
-- ----------------------------
DROP TABLE IF EXISTS `system_oper`;
CREATE TABLE `system_oper` (
  `oper_id` int(11) NOT NULL AUTO_INCREMENT,
  `oper_name` varchar(20) DEFAULT NULL,
  `oper_password` varchar(40) DEFAULT NULL,
  `oper_nikename` varchar(20) DEFAULT NULL,
  `oper_city` varchar(20) DEFAULT NULL,
  `oper_status` varchar(20) DEFAULT NULL,
  `last_login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `remark` varchar(500) DEFAULT NULL,
  `history_password` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`oper_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;

3.2 表數據

有需要的請下載這個壓縮包解壓導入即可

下載地址：https://files.cnblogs.com/files/zishengY/sub_query%3B.zip

學習本就是一個不斷模仿、練習、再到最後面自己原創的過程。

雖然可能從來不能寫出超越網上通類型同主題博文，但為什麼還是要寫？
於自己而言，博文主要是自己總結。假設自己有觀眾，畢竟講是最好的學（見下圖）。

於讀者而言，筆者能在這個過程get到知識點，那就是雙贏了。
當然由於筆者能力有限，或許文中存在描述不正確，歡迎指正、補充！
感謝您的閱讀。如果本文對您有用，那麼請點贊鼓勵。