MySQL索引

一：MyIsam索引模型

MyIsam 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。但是他的叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
比如我们有一张User表，表里面有id ，和name两个字段。

当我们执行一条SQL语句 （4次IO读取）

select  *  from   id=10 ; 

第一步 ，先读取磁盘块1，发现10小于30 走左边 （第一次IO读取）

第二步，读取磁盘块2，发现10小于20，走左边（第二次IO读取）

第三步， 读取磁盘块3，找到索引10，拿到他的data数据00x0023（磁盘地址）（第三次IO读取）

第四步，根据磁盘地址获取整行数据，进行返回（第四次IO读取）

二：InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。因为InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）

当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下

1 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

2如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

3如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

比如我们有一张User表，表里面有id ，和name两个字段。

我们执行以下SQL语句， id是我们这张表的主键索引（3次IO读取）

select  *  from   id=10 ; 

第一步 ，先读取磁盘块1，发现10小于30 走左边 （第一次IO读取）

第二步，读取磁盘块2，发现10小于20，走左边（第二次IO读取）

第三步， 读取磁盘块3，找到索引10，获取它的data数据（整行数据）（第三次IO读取）

第四步，将获取的整行数据进行返回。

因为主键索引的data存储的数据是整行数据，所以只存在3次IO读取

非主键索引

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。而InnoDB的非主键索引只会存储主键值而非磁盘地址。

比如我们有一张User表，表里面有id ，name，age三个字段。

此过程为回表，因为非主键索引，data存储的该表的主键，则需要根据主键索引进行回表。回到主键索引的B+树中，获取对应索引块data的整行数据。

我们执行以下SQL语句，（6次IO读取）

select  *  from   age=15 ; 

第一步 ，先读取磁盘块1，发现15小于30 走左边 （第一次IO读取）

第二步，读取磁盘块2，发现15小于20，走左边（第二次IO读取）

第三步， 读取磁盘块3，找到索引15，获取它的data数据（主键id=10）（第三次IO读取）

第四步，回到主键索引树中，先读取磁盘块1，发现10小于30 走左边 （第四次IO读取）

第五步，读取磁盘块2，发现10小于20，走左边（第五次IO读取）

第五步，读取磁盘块3，找到索引10，获取它的data数据（整行数据）（第六次IO读取）

由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

三：覆盖索引

什么是覆盖索引呢，比如我们上面的age索引树，它的data存储的是我们的主键索引id，比如我们的SQL语句是（select id from user where age=15），那么我们是不是就可以不用进行回表了呢？ （因为在这个查询里面，索引 age 已经“覆盖了”我们的查询需求，我们称为覆盖索引。）

组合索引
比如我们有一张User表 ，他里面有id , name , age ,sex, phone 等5个字段。而在我们一个查询中，我们回频繁查询他的 性别，年龄，手机号。我们该怎么去设置索引呢。
这时就需要用到我们的联合索引，我们需要设置他的索引为（sex,age,phone）这三个字段的联合索引。

如上图所示，当我们执行以下SQL语句（2次io读取）

select id, sex , age , phone where sex =0  and age =15  and  phone=157

第一次查询 , 读取磁盘块1 ， sex =0 ,age =15 ,phone =157 走左边

第二次查询，读取磁盘块2， 找到 sex =0 ,age =15 ,phone =157
的索引，获取它的data数据，因为当前索引已经覆盖我们查询数据，id，sex，age，phone等数据，所以无需进行回表，直接返回。

四：最左前缀原则

联合索引先根据第一个字段排序，如果第一个字段有相同的，就按照第二个字段排序，注意，这里仅仅有相同的第一个字段情况下，才会根据第二个字段排序。

也相当于我们在上述建立索引（sex,age,phone）联合索引时，
也就相当于我们建立了（sex）单独索引，（sex,age）联合索引，（sex，age，phone）联合索引

SELECT * FROM user where age="1"; #未使用索引
SELECT * FROM user where phone="3"; #未使用索引
SELECT * FROM user where sex="0" and age="5"; #使用索引
SELECT * FROM user where sex="1" and age="3" and phone="4"; #使用索引
SELECT * FROM user where age="1" and phone="4";  #未使用索引
SELECT * FROM user where sex="2" and phone="4";  #使用索引 （只用了sex索引）
SELECT * FROM user where age="1" and sex="2"; #使用索引（MySQL的优化器帮我们做了处理）

索引失效

1.上述不满足最左前缀（mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。）

2.不等于(!= 或者<>)导致索引失效

3.is null可以使用索引，is not null无法使用索引

4.like以通配符%开头索引失效

5.OR 前后只要存在非索引的列，都会导致索引失效

五：怎么选择索引

1.主键索引是自增比较好，还是不自增比较好。

使用自增主键的好处，因为在我们B+树中，叶子节点是有序的双向链表。因为它是有序的，当我们不用自增主键时。我们为了保证有序，插入和删除会比较麻烦。

自增主键和业务主键的区别：
在性能上：自增主键的插入，往往是追加操作，不需要挪动数据，不易造成页分裂问题。业务主键的插入，无法保证有序插入，需要大量挪动数据，造成页分裂，写成本太高，影响性能。
在空间上：主键长度越小，普通索引的叶子节点就越小，占用空间越小

2.减少回表
比如我们有一张User表 ，他里面有id , name , age ,sex, phone 等5个字段。而在我们一个查询中，我们回频繁查询他的 性别，年龄，手机号。我们该怎么去设置索引呢。
这时就需要用到我们的联合索引，我们需要设置他的索引为（sex,age,phone），以此来减少它的回表。