MySql索引

Mysql的索引

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种数据结构。索引可以提高查询速度。
索引的两种存储形式：B型树（BTREE）索引和哈希（HASH）索引。
InnoDB和MyIsam储存引擎支持BTREE索引，MEMORY储存引擎支持HASH索引和BTREE索引，默认为前者。

索引的分类

1、主键索引：创建在主键上的索引
2、普通索引（辅助索引）：创建在非主键上的索引
3、唯一性索引：创建在不重复字符字段上的索引，主键索引是特殊的唯一性索引
4、单列索引：创建在一个字段上的索引，只根据该字段进行索引
5、多列索引：创建在多个字段上的索引
6、空间索引：创建在空间字段上的索引（例如：具有x，y，z三个坐标的属性），MYSQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING、POLYGON等。目前只有MyIsam储存引擎支持空间检索，而且检索的字段不能为空值。
7、全文索引：创建在字符数据上的索引，只支持CHAR、VARCHAR、TEXT类型的字段。搜索执行方式不区分大小写，但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

索引的设计原则

1、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
2、为经常需要排序、分组和联合操作的字段建立索引
经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等字段的操作，排序操作会浪费很多时间，可以为其建立索引，有效的避免排序操作
3、为常作为查询条件的字段建立索引
某个字段经常作为查询条件，那么该字段的查询效率会影响执行效率，可以为该字段建立索引，提高表的查询效率
4、限制索引的数目
每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表的时候，对索引的重构和更新很麻烦
5、尽量使用数据量小的索引
如果索引的值很长，那么查询的速度就会受到影响
6、尽量使用前缀来索引
如果索引的值很长，就可以使用前缀来建立索引
7、删除不再使用或者很少使用的索引
表中的数据更新，或者删除，原有的数据可能不在需要，这时候就需要删除索引

创建索引

1、创建表的时候创建索引

创建表的时候直接创建索引，这种方式是最简单、方便的。
①创建普通索引：


②创建唯一索引：

创建唯一性索引，并且使用升序的方式建立

结果可以看到，id字段上已经建立了一个名为id_2的唯一性索引。
③创建全文索引

④创建单列索引

创建单列索引，为name建立索引，可以看出来，本来name的字段长度为10，而index_1的字段长度只有5。对于字符型的数据，可以不用查询出全部信息，只需要前缀就行。
⑤创建多列索引

⑥创建空间索引

需要注意：
PATIAL index must be NOT NUL
在所有的索引创建的时候，要注意索引名的不重复。

2、在已经存在的表上创建索引

①创建普通索引

②创建唯一索引

③创建全文索引

④创建单列索引

⑤创建多列索引

⑥创建空间索引

需要注意：space表的引擎必须为MyIsam，space字段必须为空间数据类型，而且是非空的。

3、用ALTER TABLE语句创建索引

①创建普通索引

②创建唯一索引

③创建全文索引

④创建单列索引

⑤创建多列索引

⑥创建全文索引

删除索引

DROP INDEX 索引名 ON 表名