什么是索引？

索引是一种数据结构，能够帮助我们快速的检索数据库中的数据。

为什么要用索引?（索引的优点）

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

索引有什么缺点？

• 创建索引和维护索引需要耗费许多时间，这种时间随着数据量的增加而增加
• 占用物理存储空间 ，索引需要使用物理文件存储，也会耗费一定空间
• 当对表中的数据进行增删改操作时，索引也要动态的维护，降低了数据的修改速度

索引有哪些数据结构？

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引
InnoDB引擎，默认的是B+树。

Hash索引与B+树索引相比有什么优缺点？

优点：

• 因为Hash索引底层是哈希表，因此Hash索引在等值查询上是非常快的

缺点：

• 因为数据结构是哈希表的原因，数据在存储关系上是完全没有任何顺序关系的，因此Hash索引不支持顺序和范围查询（主要缺点）
• 哈希索引不支持联合索引的最左匹配规则
• 如果有大量重复键的情况下就容易出现哈希碰撞问题，哈希索引的效率会很低

B树和B+树区别

• B树的所有节点既存放 键(key) 也存放 数据(data);而B+树只有叶子节点存放 key 和 data，其他内节点只存放key。
• B树的叶子节点都是独立的;B+树的叶子节点之间有一条引用链指向与它相邻的叶子节点。
• B树的检索的过程相当于对范围内的每个节点的关键字做多分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

若用B-树作为索引结构图：

实际的B+树索引结构图：

聚集索引与非聚集索引

即索引结构和数据一起存放的索引。主键索引（一级索引）属于聚集索引。

• 在 Mysql 中，InnoDB引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB
  引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

即索引结构和数据分开存放的索引。

• 二级索引（非主键索引）属于非聚集索引。二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
• 在 Mysql 中，MYISAM引擎的表的.MYI文件包含了表的索引，叶子节点存储索引和索引对应数据的地址，指向.MYD文件的数据。

innodb：只能有一个聚簇索引，可以有很多个非聚簇索引。向innodb插入数据到时候必须要包含一个索引的key值，这个索引的key值可以是主键，如果没有主键，那么就是第一个唯一的列，如果没有唯一列，那么数据库就自动生成一个rowid。

一级索引与二级索引访问速度

一级索引（主键索引）是一种聚集索引。一级索引树的叶子节点存储的就是查询的整行数据。
二级索引（非主键索引，辅助索引）是一种非聚集索引。树的叶子节点存的是主键的值，正常情况下（索引覆盖则是非正常情况）查到主键值后还需要拿着主键值，然后到一级索引树上查到对应的数据（这个过程称之为回表）。因此一级索引的访问速度更快。

回表过程图

两个B+树索引分别如上图：

（1）id为PK（主键），聚集索引，叶子节点存储 行记录；

（2）name为KEY，普通索引，叶子节点存储PK值，即id；

普通索引的查询过程是怎么样的呢？
通常情况下，需要扫描两棵索引树。

例如：select * from t where name=‘ls’;

1. 首先存储引擎会先到name那棵索引树上找到 name=‘ls’ 的叶子节点，定位到主键值id=5
2. 然后再拿着主键5到主键索引树（聚集索引），找到该主键5对应的行数据（5，ls，m，A）

某些特殊情况下是不需要回表，例如覆盖索引这种情况

覆盖索引

理解一：如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
理解二：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
理解三：覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

举例说明：

以上图数据表为例
假设有上面一张表（数据库是MYSQL，存储引擎是Innodb），上面的ID字段是主键索引，age是普通索引。
对比下面两条SQL语句：

select id from usertest where age = 10;

select name from usertest where age = 10;

第一条SQL语句不会产生回表：普通索引存储的值是主键的值。也就是说age索引里面存储的结构是下面的情况

根据age查询id的时候，索引中的值完全可以覆盖查询结果集字段时，不会产生回表操作。

由此也可以看出第二条SQL语句会产生回表是因为查询的结果集无法通过索引中的值直接获取。需要根据age查询到的id值再回到主键索引里面再次查询，这个过程叫做回表。

EXPLAIN分析：
当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

创建索引时注意点

最左匹配原则：在MySQL建立联合索引时会遵守最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

【Mysql最左匹配原则】

• 使用复合索引的时候，没有使用左侧的列查找，索引失效（不满足最左匹配原则）
• 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
• like查询是以%开头
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 在where当中，索引列参加了运算或者使用了函数，索引失效

索引下推

索引下推是数据库检索数据过程中为减少回表次数而做的优化

索引下推(Index Condition Pushdown) ICP 是Mysql5.6之后新增的功能，主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理，而不是像之前是放到Server层去做过滤。

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询
一条sql语句执行会先经过MYSQL服务器 再到 存储引擎，存储引擎检索出数据之后再将数据返回给MYSQL服务器，MYSQL服务器再对数据进行筛选。

EXPLAN分析:
当使用explan进行分析时，如果使用了索引条件下推，Extra会显示Using index condition。

【索引设计（索引下推）】