一、普通索引
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
1、创建表的时候直接指定
>Create table student(
Id int not null,
name varchar(10) not null，
Loc varchar(30),
index index_name(name)
);
查看索引是否创建成功
>Show create table student \G;
删除索引的语法
>Drop index index_name on student;
2、在已经存在的表上创建普通索引
>create index index_name on student(name);
注：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。------CREATE INDEX indexName ON mytable(username(length));
3、通过修改已有的表的结果增加普通索引
>alter table student add index index_name(name);
4、explain 语句
增加数据(可以多增加几行)
mysql> insert into student values(01,'tom','guangzhou');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(02,'allen','shenzhen');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(03,'peter','dongguang');
mysql> select * from student;
+----+-------+-----------+
| id | name | loc |
+----+-------+-----------+
| 1 | tom | guangzhou |
| 2 | allen | shenzhen |
+----+-------+-----------+
继续在name上创建索引
>create index index_name on student(name);
mysql> select * from student;
下面增加一个解释
> explain select * from student where name='tom';
二、唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
1、创建表的时候直接指定
>Create table student(
Id int not null,
name varchar(10) not null,
Unique index index_unique_name(name)
);
2、在已经存在的表上创建普通索引
>create unique index index_unique_name on student(name);
3、通过修改已有的表的结果增加唯一索引
>alter table student add unique index index_name(name);
查看索引是否创建成功
>Show create table student \G;
删除索引的语法
>Drop index index_unique_name on student;(删除唯一索引)
>Drop index index_name on student;(删除唯一索引)
注：也可以同时删除连个索引
>alter table student drop index index_name, drop index index_name
主键索引：
> alter table student add primary key(id);
>show create table student \G 此时可以看到只要主键索引了
注意：主键就是索引，主键是唯一索引，而唯一索引不一定是主键。一个表上可以有多个唯一索引，但只要一个主键。
三、Mysql的列索引和多列索引（联合索引）
1、创建一个多列索引
l> create table student(
-> id int not null,
-> name varchar(30) not null,
-> loc varchar(30),
-> index index_name_loc(name,loc)
-> );
2、在已经存在的表上创建普通索引
>create index index_name_loc on student(name,loc);
3、通过修改已有的表的结果增加普通索引
>alter table student add index index_name_loc(name,loc);
查看多列索引是否创建成功
Show create table student \G;
作用：explain
> explain select * from student where name='name'
> and loc='shenzhen';
> explain select * from student ignore index(index_name_loc) where name='na
me'; 对结果做对比
删除索引的语法
>Drop index index_name_loc on student;
创建多个索引：
> create table student(
> id int not null primary key,
> name varchar(30) not null,
> loc varchar(30),
> index index_name1(name),
> index index_name2(loc)
> );
查看多列索引是否创建成功
Show create table student \G;
删除索引的语法
>Drop index index_name1 on student; #删除第一个索引
四、 全文索引（FULLTEXT）（补充作用）
注：mysql5.6版本以下只有myisam存储引擎支持全文索引，mysql5.6以上版本myisam和innodb都支持全文索引，两者性能有兴趣了可以比较一下。
1、创建表的时候直接指定
>Create table student(
Id int not null,
name varchar(10) not null,
fulltext index index_name(name) );
查看索引是否创建成功
>Show create table student \G;
删除索引的语法
>Drop index index_name on student;
2、在已经存在的表上创建普通索引
>create fulltext index index_name on student(name);
更改表的存储引擎> alter table student engine=myisam;
3、通过修改已有的表的结果增加普通索引
>alter table student add fulltext index index_name(name);
查看索引：
>show index from student;
>show keys from student;
查看索引
mysql> show index from tblname; mysql> show keys from tblname;
Table 表的名称。
·Non_unique如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name索引的名称。
· Seq_in_index索引中的列序列号，从1开始。
· Column_name列名称。
· Collation列以什么方式存储在索引中。在MySQL中，有值‘A'（升序）或NULL（无分类）。
· Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment
四、MySQL中Index与Key的区别
看似有差不多的作用，加了Key的表与建立了Index的表，都可以进行快速的数据查询。
他们之间的区别在于处于不同的层面上。
Key即键值，是关系模型理论中的一部份，比如有主键（Primary Key)，外键（Foreign Key）等，用于数据完整性检查与唯一性约束等。
Index则处于实现层面，比如可以对表个的任意列建立索引，那么当建立索引的列处于SQL语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。至于Unique Index，则只是属于Index中的一种而已，建立了Unique Index表示此列数据不可重复，猜想MySQL对Unique Index类型的索引可以做进一步特殊优化吧。
于是乎，在设计表的时候，Key只是要处于模型层面的，而当需要进行查询优化，则对相关列建立索引即可。
另外，在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique Index，无需重复再在上面建立索引了。
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。
索引用于：
快速找出匹配一个WHERE子句的行；
当执行联结时，从其他表检索行；
对特定的索引列找出MAX()或MIN()值；
如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
下面是建表的语句:
CREATE TABLE `phpcolor_ad` (
`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`type` mediumint(1) NOT NULL,
`code` text,
PRIMARY KEY (`id`),
KEY `type` (`type`)
);
最后一句的KEY `type` (`type`)是什么意思？
如果只是key的话，就是普通索引。
mysql的key和index多少有点令人迷惑，单独的key和其它关键词结合的key(primary key)实际表示的意义是不同，这实际上考察对数据库体系结构的了解的。
1、key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等
primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引；
(1)PRIMARY KEY 约束：唯一标识数据库表中的每条记录； (2)主键必须包含唯一的值；
(3)主键列不能包含 NULL 值；
(4)每个表都应该有一个主键，并且每个表只能有一个主键。（PRIMARY KEY 拥有自动定义的 UNIQUE 约束）
unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引；
UNIQUE 约束：唯一标识数据库表中的每条记录。
PS:
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；
可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在oracle上建立外键，不会自动建立index），因此创建key也有如下几种方式：
（1）在字段级以key方式建立， 如 create table t (id int not null primary key);
（2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
（3）在表级以key方式建立，如create table t(id int, primary key (id));
其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。
2、index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等
因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。如，create table t(id int,index inx_tx_id (id));
3、总结，最后的释疑
（1)我们说索引分类，分为主键索引（必须指定为“PRIMARY KEY”，没有PRIMARY Index）、唯一索引（unique index，一般写成unique key）、普通索引(index，只有这一种才是纯粹的index)等，也是基于是不是把index看作了key。
比如 create table t(id int, unique indexinx_tx_id (id));--index当作了key使用
（2)最重要的也就是，不管如何描述，需要理解index是纯粹的index（普通的key，或者普通索引index），还是被当作key（如：unique index、unique key和primary key），若当作key时则会有两种意义或起两种作用。
Comments