MySQL索引-杂谈

本篇文章主要是对MySQL索引相关知识的杂谈，当面试官提到某个概念时能说出个一二三来，就达到我这篇文章的目的了。

索引底层实现

在了解了底层实现B+树后，后面的一切概念都可以很好理解

非叶子节点只存放关键字，叶子节点存放数据并用链表连接

InnoDB的索引

主键索引：叶子节点存放的是完整数据

非主键索引：叶子节点存放的是数据的主键

MyISAM的索引

MyISAM主键索引和非主键索引其叶子节点都不存储真实数据，而是存储数据指针

后文如未特别说明，均以InnoDB索引为例

回表

当我们用非主键索引完成查询时，其过程是：先到非主键索引的B+Tree上检索出所有符合条件的数据主键。然后再用查出来的主键去主键索引上查数据。我们把这一过程称为回表

我们使用InnoDB引擎创建的表，即使没有设置主键，MySQL也会自动的为我们指定一个列作为主键

首先会挑选表中可以作为主键的列（非空、唯一），该列自动成为主键列

否则，生成隐含主键列，类型为6字节的整型

最左前缀原则

这个原则的定义确实不太好用语言表达出来。主要是用于在使用联合索引(索引指定多个字段)时。

比如我现在有张表(id,name,age,sex,birth),id是主键，(name,age,sex)建立联合索引

where age=xx：(name,age,sex)索引是不生效的
where name=xx： (name,age,sex)索引是生效的
where name=xx and age=xx： (name,age,sex)索引是生效的，且用上了name列和age列
where name=xx and sex='xxx：(name,age,sex)索引生效，但只对name列检索生效（在没有索引下推情况下）

最左前缀原则的原理就是：多字段作索引时，关键字是多个字段的组合，关键字自然需要排序，其排序的规则是自左向右的。比如(name,age,sex)，先排name，name相同才排age，age相同才排sex。

覆盖索引

我们知道应用非主键索引检索数据，最终需要回表获取最终记录。但是MySQL做了优化，就是如果获取的列，非主键索引的字段就包含了。就免去回表这一步骤了

比如我现在有张表(id,name,age,sex,birth),id是主键，(name,age)建立联合索引。我现在用select name,age where name='张三'搜索。根据最左前缀原则(name,age)联合索引可以用上

在没有覆盖索引情况下：

在(name,age)联合索引的B+Tree中检索出所有name='张三'的记录主键id

用第1步查到的主键id，回到主键索引中，查到所有数据

把数据中的name和age列返回

有覆盖索引情况下：

在(name,age)联合索引的B+Tree中检索出所有name='张三'的记录

MySQL发现需要的name和age列，正好就是联合索引所拥有的列，所以无需回表直接返回

索引下推

索引下推还是针对联合索引来说的。

比如我现在有张表(id,name,age,sex,birth),id是主键，(name,age)建立联合索引。我现在用select name,age,sex where name like '张%' and age =10搜索。这种场景在没有索下推优化时，(name,age)联合索引可以用上，但只应用在name列上。