mysql索引（InnoDB存储引擎）

MYSQL索引简单描述，主要针对InnoDB存储引擎

• 索引分类
• 索引简介
• 索引总结

索引分类

在mysql中，索引基本分为以下类：

1. 主键索引
2. 唯一索引
3. 普通索引
4. 全文索引
5. 组合索引
6. 前缀索引

索引简介

当mysql在创建表时指定了索引，会根据指定创建索引，若在创建表时，没指定索引

主键索引（聚集索引）

主键索引其实是一种唯一索引，但是必须制定为primary key，每张表必须有且只能有一个主键索引。在表创建的时候，若表中设置了主键（为某一列设置主键或者某几列设置联合主键），mysql会自动创建主键索引，若表中没有主键，mysql会选择非空的整形唯一索引创建主键并创建主键索引，当表中没有主键，也没有非空的整形唯一索引，mysql会创建一个隐藏的列设置为主键并创建主键索引。

唯一索引

索引列的所有值都只能出现一次，必须唯一，值可以为空。

普通索引

基本的所有类型，值可以为空，没有唯一限制性。一般情况下，普通索引查询时，会有回表查询，也就是会查询2棵B+树，第一次查询普通索引的B+树，找到主键，第二次，通过主键查询主键的B+树查找到数据（看下面总结的第6和第7条）；

但是有时候可以优化成覆盖索引，覆盖索引就是只在一棵B+树上就能查询出需要的列，比如:
对一张有主键id的user表的name列创建索引，那么这张表会有两个索引，一个主键索引id，一个普通的name索引，在name索引的B+树中，包含有name列的信息，叶子节点是id的信息，那么执行如下SQL的时候，就不需要回表再查询主键索引，直接从name索引中就能取出数据:

select name,id from user

全文索引

全文索引的所有类型为FULLTEXT，MyISAM支持，InnoDB5.6以后支持，全文索引可以再varchar,char,text类型的列上创建。

组合索引

多列值组成一个索引，在InnoDB中，组合索引默认会在最后补全主键。创建组合索引需要注意索引列的顺序，因为它符合最左匹配原则，即按顺序从左到右匹配，当左边的未匹配上，右边不再继续匹配。
创建组合索引的时候需要考虑占用磁盘的大小。如下例子：

有一张user表，有id,name,age三个字段，如果经常查询的条件有下列三种：
1、name=xxx and age=xxx
2、name=xxx
3、age=xxx

这个时候按照最左匹配选择，除主键索引外，还需要创建两个索引，有两种方案创建这两个索引：
方案一：
创建一个组合索引：name+age，这个索引能满足1,2查询条件（按照最左匹配原则，name都能匹配，3查询条件不能使用索引，因为3为age，与这个组合索引最左边是name,所以匹配不上，不会继续往右匹配）
再创建一个普通索引: age，这个索引满足1、3（弥补了上面组合索引不能覆盖3的情况）

方案二：
创建一个组合索引：age+name，这个索引能满足1,3查询条件
再创建一个普通索引: name，这个索引满足1、2（弥补了上面组合索引不能覆盖2的情况）

此时两个方案都能满足，但是最好选择方案一，因为索引也是需要持久化到磁盘的，两个方案中组合所有占用磁盘的大小都一样，但是普通索引方案一占用磁盘更小，因为age最多3位整数，name可能很多中文或英文。

前缀索引

当我们索引的字段是很长的字符串时，可以用到前缀索引。
前缀索引创建方法：

ALTER table 表名 add index 索引名(列名(前缀长度))
或
alter table 表名 add key(字段名(前缀长度))

这里涉及到一个选择性问题,选择性越高，代表索引价值越高。
前缀索引无法使用order by 和 group by,也无法使用前缀索引做覆盖扫描。

索引总结

1. 当mysql在创建表时，若设置了主键,会自动为主键创建索引；
2. 当mysql在创建表时，若设置了外键,会自动为外键创建索引；
3. 当mysql在创建表时，若没有显示设置主键，mysql会执行如下步骤:

1 首先判断表中是否有非空的整形唯一索引,如果有,则该列为主键(这时候可以使用  select _rowid from table 查询到主键列).
2 如果没有符合条件的则会自动创建一个6字节的主键(该主键是查不到的).

4. 在联合主键中，若创建组合索引的时候，若用户定义的索引字段中包含了主键中的字段，那么这个字段就不会再被InnoDB自动加到索引中了，如果用户的索引字段中没有包含（或未完全包含）主键字段，InnoDB就会把剩下的主键字段加到索引末尾，但是自动加的主键列在mysql的server中是识别不到的，不一定会起作用，最好还是在创建索引的时候显示把主键加到末尾。
5. 前缀索引的优点在于使索引变小，缺点也很明显，选择性变低了，查询效率会变低，
   无法使用前缀索引进行ORDER BY 和 GROUP BY ，也无法使用前缀索引做覆盖扫描。
6. InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引。
7. InnoDB普通索引的叶子节点存储主键值。
8. 优化覆盖索引的几种场景：
   一、 无WHERE条件的查询优化：
   优化措施很简单，就是对查询列建立索引。
   二.、回表（二次检索）优化：
   建立联合索引，末尾加上主键列
   三、分页查询优化
   建一个索引，包含排序列以及返回列，末尾加上主键列
9. 覆盖索引的优点及限制
   覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：
   1、索引项通常比记录要小，所以MySQL访问更少的数据。
   2、索引都按值得大小有序存储，相对于随机访问记录，需要更少的I/O。
   3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。
   4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。
   限制：
   1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。
   2、Hash和full-text索引不存储值，因此MySQL只能使用B+Tree。
   3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。
   4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

本文地址：https://blog.csdn.net/weixin_43682629/article/details/108978778