MySQL索引的設計和使用
一.概述
所有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索引。
Linux公社的RSS地址 ： ofollow,noindex" target="_blank">https://www.linuxidc.com/rssFeed.aspx
本文永久更新連結地址： https://www.linuxidc.com/Linux/2018-09/154330.htm