索引概述

一 、 简述

• 1、mysql所有列类型都可以被索引，对相关列使用索引可以提高select操作性能。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎（eg:myisam,innoDB,bdb,memory ,act.）对每个表至少支持16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。
• 2、myisam和innodb存储引擎的表默认创建的是btree索引，memory存储引擎默认使用hash索引，但也支持btree索引；mysql目前还不支持函数索引，但支持前缀索引（即对索引字段的前n个字符创建索引）。前缀索引的长度和存储引擎相关。对myisam存储引擎的表，索引的前缀长度可以达到1000字节长，而对于innodb存储引擎的表，索引的前缀长度最长为767字节。前缀的长度限制单位为字节，但是 create table 语句中的前缀长度解释为字符数。
• 3、mysql 中还支持全文本（fulltext）索引，该索引可以用于全文搜索。但是当前（5.0）版本中只有myisam存储引擎支持fulltext索引，且仅限于char,varchar,text列。索引总是对整个列进行的，不支持局部（前缀）索引。
• 4、可以为空间列类型创建索引，仅myisam支持，且该索引字段必须非空。

二 、简单了解下索引

1）添加索引语法：

create [unique\fulltext\spatial] index index_name [using index_type] 
on tbl_name(index_col_name,....)
index_col_name:
col_name[(length)][asc|desc]

eg:创建索引： 为city表创建10个字节的前缀索引，create index cityname on city(city(10));

mysql> create index cityname on city(city(10));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

以city为条件查询，可以看到使用了新建的索引 cityname:explain select * from city where city='tom' \G

2）索引的删除语法为：drop index index_name on tbl_name;
eg:想要删除city表刚建立的索引cityname：drop index cityname on city

三、小结
如果要访问大部分行，则顺序读取要快的多，因为此时要避免磁盘搜索；否则，最好按照索引列指定检索相关数据比较快。
附注：
查看当前表city建表结构，执行show create table city;

CREATE TABLE city (
  city_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  city varchar(50) NOT NULL,
  country_id smallint(5) unsigned NOT NULL,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) 
  REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8