MySQL中的索引

一、索引的概念
1、索引的作用
索引的作用在于不需要加内存、不用改程序、不用调sql，只需要创建索引，就能提高海量数据的检索速度，但是要以插入、删除和更新的速度为代价。
2、索引的基本概念：
创建索引会形成一棵二叉树，查询时可进行二分查找。创建索引会占用磁盘空间，处理信息时，除了改变表中的数据，还要维护二叉树，对插入、删除和更新的速度有影响，但是影响不大，是典型的以空间换取时间。如下图所示：

常见的索引分为：主键索引（primary key）、唯一索引（unique）、普通索引（index）和全文索引（fulltext）。

3、创建索引的原则
（1）比较频繁作为查询条件的字段应该创建索引；
（2）唯一性太差的字段不适合单独创建索引，及时频繁作为查询条件；
（3）更新非常频繁的字段不适合创建索引；
（4）不会出现在where子句中的字段不该创建索引。

二、创建索引
1、创建主键索引
（1）第一种方式：在创建表的时候，直接在字段名后指定primary key。

（2）第二种方式：在创建表的最后，指定某列或某几列为主键索引。

（3）第三种方式：创建表以后再添加主键。

主键索引的特点：
1）一个表中，最多有一个主键索引；
2）主键索引的效率高；
3）创建索引的列不能为空，且不能重复；
4）主键索引的列基本上是int类型。

2、唯一索引的创建
1）第一种方式：在表定义时，在某列后面直接指定unique唯一属性。

2）第二种方式：创建表时，在表的后面指定某列或某几列为unique。

3）第三种方式：创建表以后再添加唯一属性。

唯一索引的特点：
1）一个表中可以有多个唯一索引，查询效率高；
2）如果在某一列建立唯一索引，这列上的数据可以为空，但是不可以重复；
3）如果一个唯一索引上的列指定为not null，等价于主键索引。

3、普通索引的创建
（1）第一种方式：在表定义最后，指定某列为普通索引。

（2）第二种方式：创建完表以后，添加某列为普通索引。

（3）第三种方式：使用create index 索引名 on 表名

普通索引的特点：如果某列需要创建索引，但是该列有重复的值，则应该使用普通索引，一个表中可以有多个普通索引。

4、全文索引的创建——fulltext
（1）全文索引的概念：
对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版（coreseek）。
（2）全文索引的创建
1）第一种方式：创建表时创建全文索引

2）第二种方式：使用“create fulltext index 全文索引名 on 表名(列名,1 列名2,…)”格式创建索引。
3）第三种方式：使用alter table 表名 add fulltext index 全文索引名(列名1, 列名2…)格式创建索引。
（3）基本操作
1）插入数据

2）使用普通方法查询

3）查看查询类型：可用explain关键字查看该语句用到的查询类型。

4）全文索引的使用
格式为：select * from 表名 where match(列名1, 列名2) against(‘xxx xxx’);如下所示：

通过explain来分析这个sql语句

但是，为何查询字符’do’却查不到？

因为：MySQL中的全文索引，有两个变量，最小搜索长度和最大搜索长度。对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。可通过命令“show variables like ‘%ft%’”查看，如下所示。

从中可知：ft_max_word_len为84，ft_min_word_len为4，表示MyISAM存储引擎允许搜索的字符范围是4~84。如何改变存储引擎的范围？全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件” /etc/my.cnf”来完成，然后重启 MySQL 服务器，并修复全文索引（命令：repair table test quick），不然参数不会生效。

三、查询索引
（1）第一种方法：show keys from 表名

（2）第二种方法：show index from 表名

（3）第三种方法：desc 表名

四、删除索引
（1）第一种方法-删除主键索引：alter table
表名 drop primary;

（2）第二种方法-删除其它索引：其它索引的删除，不能删除主键索引：alter table 表名 drop index 索引名;

（3）第三种方法：drop index 索引名 on 表名