数据库的索引

1.建索引

对列字段1、列字段2和列字段3建一个联合索引：

create index 索引名 on 表名(字段1，字段2，字段3);

2.实际创建3个索引

联合索引实际建立了(字段1)、(字段1,字段2)、（字段1,字段2,字段3)三个索引。

3.排序规则

select * from 表名 where 字段1=1 and 字段2=2; //语句1

select * from 表名 where 字段2=2 and 字段1=1; //查询优化器会优化成语句1

检索时使用索引(字段1,字段2)进行数据匹配。首先会对最左边的字段1的数据排序，然后再对字段2排序。相当于执行了“order by 字段1，字段2”。

4.匹配特点

索引中所有列通过"=" 或 “IN” 进行精确匹配时，索引都可以被用到

遇到范围查询(>、<、between、like)就会停止匹配

select * from 表名 where 字段1>1 and 字段2=2; //a字段走索引（有序），b全表扫描（无序）

select * from 表名 where 字段1>1 and 字段2>2 and 字段3=3; //a、b字段走索引（有序），c无序全表扫描（无序）

3）按物理存储分类

1.聚簇索引

（1）记录和索引物理顺序一致（即索引的排列顺序和表数据的排列顺序一致），索引和对应行挨着存放；

（2）聚集索引就是主键索引，建立在表的主键上。如果主键是联合主键，则为组合索引；

（3）一张表只包含一个聚集索引；

（4）查询快；

（5）更新慢。因为要保持表中记录和索引的物理顺序一致，在插入新记录的时候就会对索引重新做一次排序

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

（1）记录和索引物理不连续，而是逻辑上连续，索引和对应行不存放在一起；

（2）索引建在主键以外的列上；

（3）一张表可以有多个非聚集索引；

（4）查询慢，因为回表（见第六点，索引的存储结构）。

（5）更新快。因为不需要维护表记录和索引的物理顺序一致，逻辑一致就行了

五、索引失效

1）Like

select * from 表名 where name like '张%' //走索引

select * from表名 where name like '%三丰' //不走索引

select * from 表名 where name like '%三%' //不走索引

2）or

select * from 表名 where name= '张三' or age= 18 //走索引（or左右所有条件字段都有索引，才走索引）

select * from 表名 where name= '张三' or course='历史' //不走索引

补充说明：用or可能不走索引，因此尽量用union联合查询代替or

3）索引列计算，不走索引

select * from 表名 where age = 10+8 //走索引

select * from 表名 where age + 8 = 18 //不走索引

4）索引列用函数，不走索引

select * from 表名 where concat('name','丰') ='张三丰'; //走索引

select * from 表名 where name = concat('张三,'丰'); //不走索引

5）索引列用！=，不走索引

select * from student where age != 18 //不走索引

六、索引的存储结构

索引的存储结构部分参考了文章：【MySQL】索引优化中的最左前缀原则和索引下推 - 灰信网（软件开发博客聚合）

1）概述

索引每个叶子节点分为索引区和数据区。

聚集索引：索引区存储主键值，数据区存储除了主键值外当前行数据

非聚集索引：索引区存储当前列值，数据区存储对应主键值

2）聚集索引存储结构的例子

3）聚集索引和非聚集索引存储结构的综合例子

表包含主键列ID（上有聚集索引）、普通列K（上有非聚集索引）。

insert into T values(100,1,'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

下图为该表的索引结构：

ID上的索引为聚集索引，存储主键值、除主键外当前行数据；

K上的索引为非聚集索引，存储当前列的值、对应主键值。

4）索引回表

1.定义

非聚集索引只存储当前列的值、列所在行对应的主键值，不存储所在列对应行其它列的数据。当查询条件是索引对应列，使用到了该索引，但是返回列表包含除了索引对应列的其他列。那么，执行查询，第一次只能通过主键值找到聚集索引，第二次再通过聚集索引才能找到需要的其他列数据；

2.例子

例1：

假如表有一个索引在name字段上:

select name,age from user where name='张三'; //要回表，因为返回列表包含其他列age

例2（还是根据上图）：

在 k 索引树上找到 k=3 的记录，取得 ID = 300；

再到 ID 索引树查到 ID=300 对应的 R3；（回表1次）

在 k 索引树取下一个值 k=5，取得 ID=500；

再回到 ID 索引树查到 ID=500 对应的 R4；（回表第2次）

在 k 索引树取下一个值 k=6，不满足条件，循环结束。

3.解决办法

使用覆盖索引（覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引包含覆盖）。

刚才的例子，只用把name和age建立联合索引，就实现了覆盖索引。

5）索引覆盖

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了（辅助索引树的结点数据区存的是其对应的主键值），因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为索引覆盖。