mysql 開發基礎系列15 索引的設計和使用

一.概述

　　所有mysql 列類型都可以被索引，是提高select查詢性能的最佳方法。 根據存儲引擎可以定義每個表的最大索引數和最大索引長度，每種引擎對每個表至少支持16個索引，總索引長度至少為256位元組。
　　myisam和innodb引擎的表預設是btree索引，支持首碼索引，首碼索引長度跟存儲引擎相關，對於myisam引擎 ，長度可達1000位元組長，對於innodb 長度可達767位元組，在使用多位元組字元集的列指定首碼長度時要考慮。
　　支持全文索引(fulltext)，只有myisam引擎支持，只限於char,varchar,text列。預設memory引擎使用hash索引，也支持tbree索引。

　　 1.  例如，要為city 表創建了10 個位元組的首碼索引，語法是：

-- 為city表的cityname欄位創建10個位元組的首碼索引
CREATE INDEX ixcityname ON city(cityname(10));
--  索引查看
EXPLAIN SELECT * FROM city WHERE cityname='';

　　2.  刪除索引

DROP INDEX  ixcityname ON  city;

二 .設計索引的原則：

1. 索引使用在where後的列，而不是select 選擇的列。
2. 索引列的基數越大，索引效果越好。
3. 使用短索引， 如果對字元串進行索引，應該指定一個首碼長度。如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。這樣能夠節省索引空間。
4.不過度使用索引。因為占用磁碟空間，降低寫操作性能。
5. innodb表的普通索引都會保存主鍵的鍵值，所以主鍵的鍵值儘可能選擇較短的類型。
6. 利用最左首碼，在創建一個n列索引時，實際是創建了mysql 可利用的n個索引，多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。

-- 創建city表的多列複合索引
CREATE INDEX ix1 ON city(cityname(10),citycode);
--  走索引的語句
EXPLAIN SELECT * FROM city WHERE  cityname='' AND citycode='';
EXPLAIN SELECT * FROM city WHERE  cityname=''
 -- 不走索引
EXPLAIN SELECT * FROM city WHERE  citycode=''

三. btree索引與hash索引　

memory 引擎可以選擇使用btree或hash索引， 兩種不同類型的索引各有其不同的適用範圍， hash索引使用需要註意：
1. 只用於使用=或 <=>操作符的等式比較。
2. 優化器不能使用hash索引來加速order by 操作。
3. mysql 不能確定在兩個值之間大約有多少行。如果將一個myisam表改為hash索引的memory表，會影響一些查詢的執行效率。
4. 只能使用整個關鍵字來搜索一行。

下麵來演示下：

-- 在city表上添加city_memory表
 CREATE TABLE city_memory SELECT * FROM city
-- 添加外鍵
ALTER TABLE city_memory ADD KEY idx_fk_country_id(country_id) ;
-- 添加主鍵
 ALTER TABLE city_memory ADD PRIMARY KEY(city_id);

-- city的btree 走索引
EXPLAIN SELECT * FROM city WHERE country_id > 1 AND country_id < 10

--  city_memory的 hash 不走索引
EXPLAIN SELECT * FROM city_memory WHERE country_id > 1 AND country_id < 10
EXPLAIN SELECT * FROM city_memory WHERE country_id > 1
EXPLAIN SELECT * FROM city_memory WHERE country_id < 10

-- city_memory的 hash 走索引
EXPLAIN SELECT * FROM city_memory WHERE country_id= 10
EXPLAIN SELECT * FROM city_memory WHERE country_id IN (10,11);

　　總結：大多數mysql 索引(如 primary key, unique index , index, fulltext index)在btree中存儲，只有空間列類型索引使用rtree,並且memory表支持hash索引