共计 1839 个字符，预计需要花费 5 分钟才能阅读完成。
一. 概述
所有 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 索引。
：
