索引

索引是在存储引擎中实现的。

优点

较少与磁盘的 IO
唯一索引可以保证数据的唯一性
加速数据的检索（索引会进行排序）

缺点

创建索引和维护索引需要耗费时间（对数据进行增删改）
索引需要占用磁盘空间（记录移位、页面分裂、页面合并）

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

索引的数据结构

InnoDB中索引的推演

页中的每行数据示意图

页的示意图

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。（数据页的编号不是连续的）
给所有的页建立一个目录项。
非叶子节点存放目录项，叶子节点存放具体的索引记录。

索引的概念

Hash 索引

结构类比 HashMap , 数组 + 链表。

缺点

Hash 索引在进行范围查询的时候，会进行全表查询。
在使用 Hash 索引时，数据的存储是没有顺序的。在 order by 的时候需要重新排序。
对于联合索引，Hash 值是将联合索引键合并之后一起计算的，无法对单独的一个键或几个键进行查询。
如果 Hash 索引列的重复值很多会影响查询效率，比例性别。

聚簇索引

一种数据存储方式（所有的用户记录都存储在了叶子节点）即 索引即数据，数据即索引。

二级索引（辅助索引、非聚簇索引）

在 MySQL中，一个数据表只能由一个聚簇索引，但是可以有多个二级索引。叶子节点存放的是列和主键的值。

回表

根据二级索引只能找到主键的值，并不知道完整的数据，只能根据主键的值，去聚簇索引中再差一遍，这个过程称为回表。

联合索引

同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。

联合索引是一个二级索引。

InnoDB 的 B+ 树索引的注意事项

根页面位置万年不动
内节点中目录项记录的唯一性
一个页面最少可以存储 2 条记录

索引优化

1、为什么不建议使用过长的字段作为主键？

因为所有的二级索引都引用主键索引，过长的主键索引会令二级索引变的过大。

2、为什么主键一般使用单调的字段呢？（这里的单调指的是排序）

因为 InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成再插入新纪录时，数据文件为了维护 B+Tree 的特性而频繁的分裂调整，十分低效。因此 自增字段作为主键是一个很好的选择

3、频繁作为 where 查询添加的字段适合作为索引

4、经常 group by 和 order by 的列

5、update、delete 的 where 条件列

6、distinct 的字段

7、多表 join 连接

连接的表尽量不要超过 3 张
对 where 条件字段创建索引
对连接的字段创建索引，类型必须一致

8、使用最频繁的列放到联合索引的左侧

9、在多个字段都要创建索引的情况下，联合索引优于单值索引

10、单表索引不超过 6 个

索引会占用额外的空间
影响 insert、update、delete 的效率
增加 MySQL 优化器的执行时间

11、有大量重复数据的列不要建立索引

索引下推

Index Condition Pushdown Optimization

CREATE TABLE `user1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(30) ,
  `age` int,
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`name`,`age`) 
);

insert into user1(name,age) values('1name1',1);
insert into user1(name,age) values('2name2',2);
insert into user1(name,age) values('3name3',3);
insert into user1(name,age) values('4name4',1);
insert into user1(name,age) values('5name5',5);

explain select * from user1 where name like '1%' and age =1\G;

先看下 MySQL 的架构

MySQL 服务层负责 SQL 语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推 的下推指的是将部分上层（server层）负责的事情，交给了下层（engine 层）去处理。

没有索引下推，MySQL 的查询过程

存储引擎读取数据
根据索引的主键值，定位并读物完整的行记录
存储引擎把记录交给 server 层去检测该记录是否满足 where 条件

使用索引下推，MySQL 的查询过程

存储引擎读取索引记录
判断 where 条件部分能否用索引中的列做检查，条件不满足，则处理下一行索引记录
条件满足，使用索引的主键去定位并读取完整的行记录（这就是回表）
存储引擎把记录交给 server 层，server 层检测该记录是否满足 where 条件的其余部分。

如何判断是否使用到了索引下推

使用 explain 查看 sql 的执行计划。如果 Extra 列的值是 Using index condition，就表示使用了索引下推。

索引下推的目的

索引下推的目的是减少回表次数，也就是要较少 IO 操作。

索引下推的使用条件

MySQL 版本 5.6 及以上
InnoDB 和 MyISAM 存储引擎。
explain 的 type 为 range、ref、eq_ref、ref_or_null

不能使用索引下推的情况

引用了子查询的条件不能下推
引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。
在虚拟生成列上创建的二级索引不支持

索引最左匹配原则

在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。当遇到范围查询(>、<、between、like)时就会停止匹配。

CREATE TABLE `left_match` (
  `a` int ,
  `b` int ,
  `c` int ,
  KEY `index_name_age` (`a`,`b`,`c`) 
);

insert into left_match(a,b,c) values(1,1,1);

explain select * from left_match where a = 1 and c = 1\G;

最左匹配的原理

最左匹配都是针对联合索引来说的。

索引的底层是一颗 B+ 树，

索引跳跃扫描

Skip Scan

支持不符合组合索引最左前缀原则条件下的 SQL，依然能够使用组合索引，减少不必要的扫描。

具体案例

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

索引跳跃扫描原理

在MySQL 8.0.13 及以后的版本中，SELECT f1, f2 FROM t1 WHERE f2 > 40;SQL执行过程如下：

获取 f1 字段第一个唯一值，也就是 f1 = 1。
构造 f1 = 1 and f2 > 40，进行范围查询。
获取 f1 字段第二个唯一值，也就是 f1 = 2。
构造 f1 = 2 and f2 > 40，进行范围查询。
一直扫描完f1字段所有的唯一值，最后将结果合并返回。
也就是说，最终执行的 SQL 语句是像下面这样的：

SELECT f1, f2 FROM t1 WHERE f1 = 1 and f2 > 40
union 
SELECT f1, f2 FROM t1 WHERE f1 = 2 and f2 > 40;

索引跳跃扫描限制条件

表T至少有一个联合索引，但是对于联合索引(A,B,C,D)来说，A和D可以是空的，但B和C必须是非空的。
查询必须只能依赖一张表，不能多表JOIN。
查询中不能使用GROUP BY或DISTINCT语句。
查询的字段必须是索引中的列。

索引覆盖

在使用这个索引查询时，使它的索引树的叶子节点上的数据可以覆盖你查询的所有字段。

例如 index_ncd(b、c、d) 是一个联合主键，当执行 sql select b,c,d from test where b = 1 时，根据 index_bcd 的叶子节点就可以获取到想要的字段，这是就可以不回表，只需要查询一次索引树。这个就是索引覆盖。

索引的数据结构​

索引的概念​

Hash 索引​

聚簇索引​

二级索引（辅助索引、非聚簇索引）​

回表​

联合索引​

InnoDB 的 B+ 树索引的注意事项​

索引优化​

1、为什么不建议使用过长的字段作为主键？​

2、为什么主键一般使用单调的字段呢？ （这里的单调指的是排序）​

3、频繁作为 where 查询添加的字段适合作为索引​

4、经常 group by 和 order by 的列​

5、update、delete 的 where 条件列​

6、distinct 的字段​

7、多表 join 连接​

8、使用最频繁的列放到联合索引的左侧​

9、在多个字段都要创建索引的情况下，联合索引优于单值索引​

10、单表索引不超过 6 个​

11、有大量重复数据的列不要建立索引​

索引下推​

如何判断是否使用到了索引下推​

索引下推的目的​

索引下推的使用条件​

索引最左匹配原则​

最左匹配的原理​

索引跳跃扫描​

具体案例​

索引跳跃扫描原理​

索引跳跃扫描限制条件​

索引覆盖​