MySQL索引

索引

索引主要用来提高查询效率，索引和实际的数据都是存储在磁盘的，只不过在进行数据的读取的时候会优先把索引加载的内存中。索引的本质是一种数据结构

索引特点

优点：

• 大大加快查询速度

• 使用分组和排序时，可以显著减少查询时分组和排序的时间

• 创建唯一索引，能够保证数据库中每一行数据的唯一性

• 在实现数据的参考完整性方面，可以加快和表之间的连接

缺点

• 创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加

• 索引需要占据磁盘空间

• 对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

按数据结构分类：

Hash索引：不适用，不能进行范围查找，因为散列表中的值是无序的，无法进行大小比较，不能进行排序

B+Three索引:InnoDB默认使用是索引结构

索引按功能分类：

单列索引： 一个索引只包含单个列但一个表中可以有多个单列索引

单列索引又分为：普通索引，唯一索引，主键索引

普通索引：

MySQL中基本索引类型，没有限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快。

创建普通索引：

创建表时直接指定

 index 索引名(列名)

直接创建

create index 索引名 on 表名（列名）

修改表结构

 alter table 表名 add index 索引名（列名） 

删除索引：

 drop index 索引名 on 表名
 -- 或
 alter table 表名 drop index 索引名

唯一索引：

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。其他与普通索引类似

创建表时指定

 unique 索引名（列名）

直接创建：

 create unique index 索引名 on 表名（列名）

修改表结构：

 alter table 表名 add unique 索引名（列名）

删除索引：

 drop index 索引名 on 表名
 -- 或
 alter table 表名 drop index 索引名

主键索引（unique）：

创建表时MySQL会自动在主键列上建一个索引，就是主键索引。主键是具有唯一性并且不允许为null，所以是一种特殊的唯一索引。又叫聚簇索引

聚簇索引

数据和索引存储在一起的叫聚簇索引，没有存储在一起的叫非聚簇索引。

InnoDB中既有聚簇索引也有非聚簇索引，MyISAM中只有非聚簇索引

组合索引：

组合索引又叫复合索引，指的是在建立索引的时候使用多个字段，同样可以建立为普通索引和唯一索引。

复合索引的使用复合最左原则

创建索引：

 create (unique) index 索引名 on 表名（列1名（ ），列2名（）···）

删除索引：

 drop index 索引名 on 表名
 -- 或
 alter table 表名 drop index 索引名

最左原则（最左前缀原则）：查询条件如果直接匹配到索引定义时的第一个列名则使用索引，如果匹配条件只有一个，且不是索引定义时的第一个列，那么就不会用到索引，如果匹配条件跟定义索引时的列一样，无论顺序都会使用索引。

 select * from stdent where name='张三' or phone_num = '13415455' 
 --没有用到索引

全文索引(fulltext)：

全文索引的版本，存储引擎，数据类型的支持情况：

• MySQL 5.6以前的版本，只有MyISAM存储引擎支持全文索引

• MySQL 5.6以后的版本，MyISAM和InnoDB存储引擎均支持全文索引

• 只有字段的数据类型为char，varchar，text及其系列才可以建立全文索引

• 在数据量较大的情况下，将数据放入到一个没有全文索引的表中，然后再用create index创建fulltext索引，要比现在表中建立fulltext索引然后在写入的数据的速度要快

MySQL中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗一点就是，想对一个词进行全文索引就必须要保证词语的长度在最大搜索长度和最小搜索长度之间。

最小搜索长度和最大搜索长度查看默认值命令

 show variables like '%ft%'

使用全文索引

和经常使用的模糊匹配不同，全文索引有特殊的语法格式，使用match和against关键字。格式

 match(col1,col2,...（列）) against('查询关键字')

空间索引（了解）：

• MySQL在5.7之后的版本支持空间索引，而且支持OpenGIS几何数据模型

• 空间索引是对空间数据类型的字段建立索引，MySQL中空间数据几何有4种，分别是GWOMETRY，POINT，LINESTRING，POLYGON

• MySQL使用SPATIAL关键字进行扩展，使的能够用于创建正规索引类型的语法创建空间索引。

• 创建空间索引的列，必须声明其为NOT NULL。

类型	含义	说明
Geometry	空间数据	任何一种空间类型
Point	点	坐标值
LineString	线	有一系列点连接而成
Polygon	多边形	由多条线组成

使用

 spatial key 索引名(列)