MYSQL索引

一、索引类型

1、主键索引

a) 唯一标识数据记录中的一条，不重复；常与auto_increment、not null联用；

2、唯一索引

a) 保证数据列中数据值得唯一性；

3、常规索引

a) 常用于优化数据查询效率，用在搜索，排序，分组等设计的列上（即WHERE、 GROUP BY、ORDER BY等），在数据库创建时候，用KEY或INDEX创建。

4、全文索引（FULLTEXT）

a) 不使用模式匹配的操作下去进行搜索单词或者短语，只能用于MYISAM，只可在CHAR、VARCHAR、TEXT类型上创建，使用时需要用MATCH（）、AGAINST()函数，MATCH列举将要搜索的一个或者多个列名，AGAINST搜索字符串（）；

5、单列索引、复合索引

a) 复合索引遵循最左前缀原则，只有查询条件使用了创建索引的第一个字段，索引才被使用；

6、空间索引

a) 使用SPATIAL关键字，数据类型有GEOMETRY、POINT、LINESTRING、POLYGON；

b) 只能在MYISAM表上创建；

创建语法：

CREATE TABLE table_name[col_name data

type][unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

修改语法：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]

[index_name](col_name[length],...)[ASC|DESC]

删除语法：

ALTER TABLE table_name DROP INDEX index_name；

DROP INDEX index_name ON table_name

  注意：AUTO_INCREMENT约束字段的唯一索引不能被删除！！

二、索引结构

1.B-TREE

时间复杂度：O(log(n))；

应用：

1).比较操作符=、>、>=、<、<=、BETWEEN;

          2).用于LIKE，但是以非通配符开头的常量；eg：LIKE‘par%se’;

         3).任何一个没有覆盖所有WHERE中AND级别条件的索引是不会被使用的。也就是说，要使用一个索引，这个索引中的第一列需要在每个AND组中出现。

 4).MYSQL预估使用索引会预读大部分数据时候，不使用索引。加入用LIMIT来限定返回条数，MYSQL使用索引；

2.HASH

时间复杂度：如果是一对一关系，操作符只是=时，O(1)；目前只有MEMORY和NDB支持HASH；

应用：

1）.对等比较（=、<=>(等于，可区别NULL)），不用于范围查询条件，对用语“键值对”的系统存储结构。

2）.不能用HASH索引来为ORDER BY操作符速；

3）.MYSQL不能判断两个值之间有多少条数据；

4）.只有完整的键才能被用于搜索一行数据；

索引的缺点：

1).索引要占用磁盘空间，如果创建每一种可能的索引组合，索引文件的大小可能超过数据文件，甚至超过操作系统允许的最大文件限制；

2).写入操作，如DELETE、UPDATE、INSERT，改动数据文件的时候，同时要写入索引，降低效率；