什么是索引

1、什么是MySQL索引？

• 官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。
• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
• 一种能帮助mysql提高了查询效率的数据结构：索引数据结构。

1.1、索引原理

索引的存储原理大致可以概括为一句话：以空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕

1.2、索引的分类

主键索引：primary key

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

• 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

复合索引：

• 一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

• Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
• 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方年遵循OpenGIS几何数据模型规则。

前缀索引：

• 在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

创建索引的基本操作

创建主键索引：

#建表时，主键默认为索引
create table user(
    id varchar(11) primary key,
    name varchar(20),
    age int
)

#查看user表中的索引
show index from user;

创建单列索引：

#创建单列索引，只能包含一个字段
create index name_index on user(name);

创建唯一索引：

#创建唯一索引，只能有一个列
create unique index age_index on user(age);

创建符合索引：

#复合索引
create index name_age_index on user(name,age);

满足复合索引的查询的两大原则：
假如创建的复合索引为三个字段，按顺序分别是(name,age,sex)
在查询时能利用复合索引的查询条件如下：

• 1、最左前缀原则(如下四种都满足条件)

  select * from user where name = ? 
  select * from user where name = ? and age = ?
  select * from user where name = ? and sex = ?
  select * from user where name = ? and age = ? and sex = ?
  

2.MySQL 引擎在执行查询时，为了更好地利用索引，在查询过程中会动态调整查询字段的顺序！(也就是说，当条件中的字段全部达到复合索引中的字段时，可以动态调整字段顺序，使其满足最前左缀)

#可以使用复合索引：索引中包含的字段数都有，只是顺序不正确，在执行的时候可以动态调整为最前左缀
select * from user where sex = ? and age = ? and name = ?
select * from user where age = ? and sex = ? and name = ?

#不可以使用复合索引：因为缺少字段，并且顺序不正确
select * from user where sex = ? and age = ? 
select * from user where age = ? and name = ?
select * from user where age = ?
select * from user where sex = ? 

删除索引的语法

我们可以使用 DROP INDEX 语句来删除索引。

DROP INDEX 的语法：

DROP INDEX index_name ON table_name;