一個mysql最佳化技巧的誤區

標籤：mysql   sql最佳化   mysql最佳化   索引   全表掃描   

        關於sql最佳化技巧，大家可能見過N個版本，尤其容易博得初中級程式員的眼球。倘若沒有一點分析實踐能力，直接將其拿來當作聖經記在心中並實踐於工作中，那你極有可能被掉坑。輕則代碼運行轉圈圈無響應，重則導致項目癱瘓造成經濟損失。

        廢話不多說，直接。

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/90/F6/wKioL1jzcDvxLbs2AACDHofFibU963.png" title="2017-04-16 21-15-22螢幕.png" alt="wKioL1jzcDvxLbs2AACDHofFibU963.png" />

        上面這條技巧粗略看一眼好像也沒有什麼問題。可事實是這樣的嗎？

        

        結論當然是否定的。且看執行個體分析：

        

CREATE TABLE `t_auxiliary_info` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `ac_id` tinyint(3) unsigned NOT NULL COMMENT ‘分類ID‘,  `name` varchar(250) NOT NULL DEFAULT ‘‘ COMMENT ‘名稱‘,  `number` smallint(6) unsigned NOT NULL DEFAULT ‘1‘ COMMENT ‘編號‘,  `attr` varchar(500) NOT NULL DEFAULT ‘‘ COMMENT ‘屬性‘,  `fdbid` int(10) unsigned NOT NULL COMMENT ‘使用者ID‘,  `status` tinyint(1) unsigned NOT NULL DEFAULT ‘1‘ COMMENT ‘狀態:1有效,0無效‘,  `stock_type` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘ COMMENT ‘存貨類型：1庫存商品，2原材料，3周轉材料‘,  PRIMARY KEY (`id`),#請注意這裡的索引  KEY `uniq_cid_acid` (`fdbid`,`ac_id`)) ENGINE=InnoDB AUTO_INCREMENT=645101 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

    上面是一張普通的業務表，仔細看錶中設定的索引：

  PRIMARY KEY (`id`),#主鍵索引  KEY `uniq_cid_acid` (`fdbid`,`ac_id`)#聯合索引

        

        再使用上述的in 或not in 來實踐以下，通過explain執行計畫工具看看實際效果。（在這裡為了公平起見，我不使用主鍵id，且in操作中的資料不是連續的。）

        

select * from t_auxiliary_info where fdbid in(‘1000‘,‘1500‘,‘1234‘,‘5155‘,‘6789‘,‘3423‘,‘5368‘,‘245645‘);

        

在上面的sql中，我們使用包含在聯合索引`uniq_cid_acid`中的欄位 `fdbid`作為搜尋條件

 

       見證奇蹟的時刻到了。

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/90/F9/wKiom1jzdaPAsg35AAB8Daxo-zs623.png" title="2017-04-16 21-45-07螢幕.png" alt="wKiom1jzdaPAsg35AAB8Daxo-zs623.png" />

    

        通過執行計畫， 我們可以清晰的看到這條sql的檢索類型為簡單簡單檢索，屬於範圍查詢，且已經使用到了索引  uniq_cid_acid，且沒有全表掃描（掃描行數為2804，而本表中資料條數為645101）。

       

        由此可以得出結論：不是所有sql中的in查詢會全表掃描。這裡推翻了in會導致全表掃描的結論。

        那麼在什麼情況下，使用in操作一樣可以使用到索引，不會全表掃描呢？

        1.  in的欄位必須是帶有索引的欄位。

        2.  in(...) 中的資料最好加上引號,即使欄位類型是數字。

        在這裡可能有人會有疑問，上面表中的欄位fdbid是int類型，難道數值型的欄位也需要用引號？我笑而不語。        

select * from t_auxiliary_info where fdbid not in(1000,1500,1234,5155,6789,3423,5368,245645);

  

        真相在這裡：

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M00/90/FA/wKioL1jzeajC3kv_AACBbcpzw3Y564.png" title="2017-04-16 22-02-45螢幕.png" alt="wKioL1jzeajC3kv_AACBbcpzw3Y564.png" />

        這就是全表掃描！！！

所以最後給出的結論：

1.   使用  in 條件的欄位必須是索引欄位。
   

2. 且，in(。。。) 括弧中的值都加上引號，無論欄位類型是什麼鬼。

歡迎討論指教。

本文出自 “我的PHP之路” 部落格，請務必保留此出處http://phpme.blog.51cto.com/663593/1916492

一個mysql最佳化技巧的誤區