精通MySQL之索引篇

索引是什么？

在日常开发中常常会遇到查询比较慢的情况，我们的第一反应就是给它加索引，那索引是什么呢？官方介绍索引是帮助MySQL高效获取数据的数据结构，数据库索引好比是一本书的目录，能加快数据库的数据查询速度。

那索引的好处有哪些呢？

1. 它可以提高数据检索的效率，降低数据库的成本。
2. 通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗。

任何事情都会有正反面，索引也不例外，那索引的坏处有哪些呢？

1. 索引会占据磁盘空间。
2. 索引虽然会提高查询效率，但会降低更新表的效率。
3. MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

那是不是有坏处就不用索引呢？

当然不是，索引必须拿来。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘文件上的文件中。

索引的分类

1. 单列索引：
   • 普通索引：add unique
   • 唯一索引：索引列中的值必须是唯一的，但允许有空值，add unique index
   • 主键索引：是一种特殊的唯一索引，不允许有空值
2. 组合索引：
   • 在表中的多个字段组合上创建的索引
   • 组合索引的使用，需要遵循最左前缀原则
   • 一般情况下，建议使用组合索引代替单列索引（主键索引除外）
3. 全文索引：只有在MyIsam、InnoDB上才能使用，而且只能在char、varchar、text类型字段上使用全文索引。
4. 空间索引：一般用不到

索引的使用

创建索引

CREATE INDEX index_name ON table(column(length)) ;

删除索引

DROP INDEX index_name ON table;

查看索引

SHOW INDEX FROM table_name G;

索引原理（重点）

索引的存储结构

说索引原理之前，先说说索引存储结构。索引是在存储引擎中实现的，也就是不同的存储引擎，会使用不同索引。其中MyIsam和InnoDB只支持B+数索引，老刘先不讲B树和B+树的概念，大家自行搜索。

接下来就是索引的重点，搞清楚了非聚集索引和聚集索引，索引原理就差不多了！

非聚集索引(MyIsam)

它说的是B+树叶子节点只会存储数据行(数据文件)的指针，即数据和索引不在一起。它包含主键索引和辅助索引，都会存储指针的值。

主键索引

MyIsam中B+树叶子节点存储的数据是数据的指针值，通过索引树找到对应的索引，然后通过索引中存储的记录指针，找到数据文件中对应的记录。

辅助索引(次要索引)

在MyIsam中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key是可以重复的。

聚集索引(InnoDB)

• 主键索引(聚集索引)的叶子节点会存储数据行，也就是说数据和索引在一起。
• 辅助索引只会存储主键值。
• 如果没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

主键索引

在InnoDB中要求表必须有主键(MyIsam可以没有)，如果没有显示指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键类型为长整形。

上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录，这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。

辅助索引

InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，即回表查询。

select * from user where name='Alice'
复制代码

根据这段SQL语句，会进行回表查询，检索两次，才会获得记录。回表性能比较低，尽量做到不回表。

索引使用场景

介绍完索引的相关概念后，老刘必须给讲讲哪些场景下可以使用场景，大家记几个就行。

哪些情况下需要使用索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 多表关联查询中，关联字段应该创建索引
4. 查询中排序的字段应该创建索引
5. 频繁查询字段
6. 查询中统计或者分组字段应该创建索引

哪些情况下不需要创建索引

1. 表记录太少，没必要创建索引
2. 经常进行增删改的表
3. 频繁更新的字段
4. where条件里使用频率不高的字段

为什么推荐多使用组合索引？

为了节省mysql索引存储空间以及提升搜索性能，能使用组合索引就不使用单列索引。

使用组合索引需要遵循最左前缀原则，什么是最左前缀原则？

1. 前缀索引：where like a%

   通配符%在右边不在左边，什么是前缀索引呢？当索引是很长的字符序列时，这个索引会很慢，占用内存。如果以name为索引，当name对应的字符串很长时，就可以用前缀索引where like a%。

2. 从左到右都有索引，不能断，直到遇到范围查询<，>，between。

索引失效

我们进行数据查询很慢时，可能就会存在索引失效的情况。遇到这种情况不要怕，我们可以使用explain命令对select语句的执行计划进行分析。explain出来的信息有10列，分别是

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
复制代码

下面老刘就使用一个案例进行这些参数进行说明，大家可以跟着老刘一起练习。这10个参数老刘只讲重要的，其他的大家自行学习。

--用户表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
--部门表
create table tdep(
id int primary key,
name varchar(100)
);
--地址表
create table taddr(
id int primary key,
addr varchar(100)
);
--创建普通索引
mysql> alter table tuser add index idx_dep(dep);
--创建唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
--创建组合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
--创建全文索引
mysql> alter table taddr add fulltext ft_addr(addr);
复制代码

id

每个SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序，有四种情况：

• id相同：执行顺序由上到下
• id不同：如果是子查询，id号会自增，id越大，优先级越高。
• id相同的不同的同时存在
• id列为null的就表示这是一个结果集，不需要使用它来进行查询。

select_type(重要)

表示查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。

simple，表示不需要union操作或者不包含子查询的简单select查询。

primary，一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary，并且只有有一个 。

先执行括号里面的sql语句，再执行外面的sql语句，内层的查询就是subquery。

subquery，除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。

dependent subquery，表示这个subquery的查询要受到外部表查询的影响。

union，它连接的两个select查询，第一个查询是PRIMARY，除了第一个表外，第二个以后的表select_type都是union。

dependent union，它与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。

union result，它包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。

derived，from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

可以理解为就是from字句后面出现子查询，取个别名，叫派生表。

table

显示查询的表名，如果查询使用了别名，那么这里显示的是别名。

type(重要)

它会显示很多参数类型，性能依次从好到坏显示为这样：

system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL
复制代码

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引，优化器会选用最优索引一个，最少要索引使用到range级别。 老刘只讲这个重要的，有些内容也没搞清楚。

system

可遇不可求，表中只有一行数据或是空表。

const(重要)

使用唯一索引或主键，返回记录一定是1行记录的等值where条件。

eq_ref(重要)

一般是连接字段主键或者唯一性索引。

此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 '='，查询效率较高。

ref(重要)

针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。

range(重要)

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index(重要)

关键字：条件是出现在索引树中的节点的，可能没有完全匹配索引。

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all(重要)

这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

possible_keys、key、key_len、ref、rows就不讲了，直接讲最后一个extra。

extra

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，这里写常见的几种。

no tables used

表示不带from字句的查询，使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

using filesort(重要)

排序时无法使用到索引时，就会出现这个，常见于order by和group by语句中。

using index(重要)

查询时不需要回表查询，直接通过索引就可以获取查询的数据。

using where(重要)

通常type类型为all，记录并不是所有的都满足查询条件，通常有where条件，并且一般没索引或者索引失效。

讲完分析索引的参数后，现在老刘讲一些索引失效的情况，大家一定要用心记住，老刘也记了好几遍！

索引失效分析

1. 一般SQL语句查询采用全值匹配，资料上叫全值匹配我最爱。

   

2. 最左前缀法则，对于组合索引而言，查询从索引的最左前列开始，并且不能跳过索引中的列不然就会失效。

   现在举一个带头的索引断（带头索引生效，其他索引失效）的例子：

   

3. 不要在索引上做计算，例如计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描。

   

4. 范围条件右边的列失效，就是不能继续使用索引中范围条件（bettween、<、>、in等）右边的列。

5. 尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少select *。

6. 索引字段上不要使用不等，索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描。

7. 主键索引字段上不可以判断null，索引字段上使用 is null 判断时，可使用索引。

8. 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描。like要以通配符结束相当于范围查找，索引不会失效。

9. 索引字段是字符串时，要加单引号，否则会导致索引失效而转向全表扫描。

10. 索引字段不要使用or，否则会导致索引失效而转向全表扫描。

　　转载链接：https://juejin.cn/post/6923773896248262664
　　来源：掘金