SQL---索引

今天我们一起学习并梳理一下数据库中的索引的知识，带着以下几个问题，去深入的了解。

what：索引是什么？索引有哪几种类型？
why: 为什么要用到索引？作用是什么？优点和缺点？
who: 谁去使用？开发人员或测试人员
where: 在代码中或者sql查询中
when: 开发阶段、测试阶段、性能优化阶段
how: 怎么用的，创建索引，删除索引，索引失效

一、索引是什么？

索引是一种用于快速查询和检索数据的数据结构。 索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

二、为什么要使用索引？

为了提高查询的效率。
索引一经建立，就由数据库管理系统自动使用和维护，不需要用户干预。建立索引是为了加快数据的查询效率。

2.1索引的优点

①可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的。
②另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2.2索引的缺点

①创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
②占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

三、索引类型

3.1主键索引

①数据表的主键列使用的就是主键索引

②一张数据表有只能有一个主键，并且主键不能为 null，不能重复

③在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键

3.2二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引 均属于二级索引

唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

3.3聚集索引

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放只能有一种排列方式，所以一个表只能有一个聚集索引。比如字典中，用拼音查汉字，就是聚集索引。因为正文中字都是按照拼音排序的。而用偏旁部首查汉字，就是非聚集索引，因为正文中的字并不是按照偏旁部首排序的，我们通过检字表得到正文中的字在索引中的映射，然后通过映射找到所需要的字。

聚集索引并不是一种单独的索引类型，而是一种数据存储方式。将数据存储于索引放到了一块，索引结构的叶子节点保存了行数据。另外，主键索引属于聚集索引。

优点

聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点

①依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
②更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

3.4非聚集索引

非聚集索引：与聚集索引相反，索引顺序与物理存储顺序不一致。通俗点说：非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。

优点

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的。

缺点

非聚集索引也依赖于有序的数据。可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

四、索引用法

4.1创建索引

CREATE UNION/CLUSTERED/NONCLUSTERED INDEX 索引名 ON 表名 (列名)

4.2删除索引

DROP INDEX 表名.索引名

4.3怎样查看一条SQL使用了索引？

 没有创建索引，查询时如下：

 给loc字段添加索引，创建索引之后：

4.4索引失效的几种情况

失效的第1种情况：
select * from emp where ename like '%T';

ename上即使添加了索引，也不会走索引，为什么？
原因是因为模糊匹配当中以“%”开头了，mysql找不到。
解决方案：尽量避免模糊查询的时候以“%”开始。这是一种优化的手段/策略。

失效的第2种情况：
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果or其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。
解决方案：
不建议使用or，所以这就是为什么不建议使用or的原因。
或使用union联合查询。
举例：
dept表，loc字段有索引，deptname字段没有索引
select * from dept where loc = '福建' or deptname = '信息部';
--- 查询位于福建的或者信息部的部门信息
以上sql语句，由于deptname没有索引，会导致loc的索引失效，可以使用union联合查询：
select * from dept where loc = '福建' union select * from dept where deptname = '信息部';这样loc字段的索引还是生效的。

失效的第3种情况：
使用复合索引的时候，没有用到左侧的字段作为查找条件，索引失效
什么是复合索引？
两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
create index emp_job_sal_index on emp(job,sal);

索引正常：explain select * from emp where job = 'MANAGER';
索引失效：explain select * from emp where sal = 800;

失效的第4种情况：
在where当中索引列参加了运算，索引失效。
create index emp_sal_index on emp(sal);

索引正常：explain select * from emp where sal = 800;
索引失效：explain select * from emp where sal+1 = 800;

失效的第5种情况：
在where当中索引列使用了函数

ename字段有索引
explain select * from emp where lower(ename) = 'smith';