mysql 開發進階篇系列 3 SQL 優化

一. 本章介紹mysql中的索引的分類，存儲，使用方法的介紹

　　1.  索引的存儲分類
　　MyISAM存儲引擎的表的數據和索引是自動分開存儲的，各自是獨立的一個文件, innodb 存儲引擎的表的數據和索引是存儲在同一個表空間裡面，可以有多個文件組成。 MyISAM和Innodb存儲引擎都支持btree索引，memory/heap存儲引擎支持hash和btree索引。

　　2. mysql如何使用索引
查詢要使用索引最主要的條件是查詢條件中需要使用索引關鍵字，如果是多列索引，那麼只有索引條件使用了多列關鍵字最左邊的首碼時，才可以使用索引，否則不能.

　　(1) 創建多列索引

 -- 多列索引又叫複合索引  創建city表的多列
CREATE INDEX ix1 ON city(cityname(10),citycode);    
-- 使用cityname進行查詢，發現即使不使用citycode也能使用到索引，這就是索引首碼的特性
EXPLAIN SELECT * FROM city WHERE cityname='12'

-- 使用citycode進行查詢，就沒有使用索引
EXPLAIN SELECT * FROM city WHERE citycode='12'

　　(2) 使用like
　　　　需要使用模糊查詢時， 建了索引的欄位 %必須放在關鍵詞後面

 -- 如下所示
EXPLAIN SELECT * FROM city WHERE cityname LIKE '12%'

　　(3) 使用條件is null
　　　　如果列名是索引，那麼使用is null 會使用到索引

  -- 如下所示
EXPLAIN SELECT * FROM city WHERE cityname IS NULL

　　3. 存在索引但不會使用到索引

(1) 使用索引比全表掃描更慢，則不使用索引。 比如key分佈在1到100之間，使用索引 key>1 and key<90 。
(2) 使用memory/heap表 使用where條件不使用 = 進行查詢時，那麼不會用到索引。
(3) 用or分割開的條件， 如果or 前麵條件中列有索引，or後面列沒有索引，那麼索引不會被用到如下

 --  country_id 列是索引  citycode不是索引
EXPLAIN SELECT * FROM city WHERE country_id=2 OR citycode='000'

--  country_id 列是外鍵索引  city_id主鍵索引
EXPLAIN SELECT * FROM city WHERE country_id=2 OR city_id=2 (發現key也是空後面在分析)

　　　(4) 列類型是字元串，記得值加引號

--  沒有加引號
EXPLAIN SELECT * FROM city WHERE cityname=22