MySql索引

在我们开发的过程中必然会和mysql（这里以5.7为例）数据库打交道。打交道的过程中其中一个最重要的部分就是sql优化，说到sql优化首先想到的就是加索引。很多人认为创建了索引就算大功告成了，真的是这样么？其实不然，创建索引很简单，但是真想把索引使用起来是非常苛刻的，是有多苛刻呢？可以这么说，我认为一个索引能被两条以上的sql语句用上就很不容易了。接下来就解析一下索引为什么那么难

要想解释索引为什么那么难，首先得先知道索引的存储结构。在大多数情况下大家都是默认使用的InnoDB数据库引擎（另外还有一种Myisam引擎，这么暂不讲解），所以我直接以InnoDB引擎讲解。

数据存储文件

MySQL的每个数据库都对应存放在一个与数据库同名的文件夹中，MySQL数据库文件包括MySQL所建数据库文件和MySQL所用存储引擎创建的数据库文件。

先看看存储文件有哪些，查看数据文件存储位置：show global` `variableslike"%datadir%"；

 

以test数据库为例，在test数据库中有一个表user

id: 主键

name: 字符串

age: 数字

 

user.frm: 存储数据表结果信息

user.ibd：存储索引和数据，换句话说就是，存储索引的同时存储数据。

这里终点讲 .ibd 文件，什么是存储索引的同时存储数据？

.ibd数据文件本身就是按B+Tree组织的一个索引结构文件，B+Tree将在其他文章讲解。

 

主键索引树

 

以上就是InnoDB引擎进行存储数据的方式，可以看出既有主键索引又有数据存储。这种结构就造成了InnoDB引擎的表必须要有主键，然后数据存放在主键索引树种。那有时候我们没有指定主键怎么办，InnoDB要求必须依赖主键索引树存储数据，所以当我们没有指定主键时会自动生成一个隱藏主键。

非主键索引

那么非主键索引是长什么样呢？这里以name创建一个索引，看看会是什么样。

 

非主键索引树以name作为key进行排序，那么存储的值是什么呢？就是该字段在主键索引中的主键ID。

用法讲解

那么非主键索引在使用的时候会怎么样呢？

select * from user where name = 'lili';

这是一条很常见的查询语句，那么它会走name索引吗？答案是不会走name 这颗索引树。

但是明明只以name为条件去查询了为什么不会走name索引树呢？关键在于前面的 * 也就是查询出的结果集，结果集中包含了age 这个字段，但是name 索引树中是没有这个值的，也就是说如果走了name索引树之后为了得到age字段还需要去查询主键索引树，这样就不划算了。（可能会想在name索引树种也维护全部的字段不就行了，那么就会出现数据一致性的问题，如果每一个索引树都各自维护全部数据的话，一是浪费内存，二是数据一致性很难保证）

select id, name from user where name = 'lili';

如果把sql 改成这样那么就会走name索引树，因为id和name 这两个字段在name索引树种都是有值的不需要再去查询主键索引树，这时候的查询效率就会比较高。

从这里可以看到使用索引的时候要看查询出的结果集是否全部在该索引树中。

 

 

联合非主键索引

创建一个name 和 age （name在前age在后）的联合索引树

 

对于这颗索引树应该怎么使用呢？

select * from user where name = 'lili';

对于这个sql就会走name,age这颗索引树，应该很容易看出来，这颗索引树中包含了全部字段，就不需要再去查询主键索引树了。

select * from user where name='lili' and age = 18;

同理这条语句也会走这颗索引树，不需要走主键索引树。因为name 和 age 正是索引值，也是这颗索引树的最佳使用。

select * from user where age = 18;

那么对于这条sql就不会走联合索引只会走主键索引，因为这颗索引树是以name在前age在后的方式创建的，所以在不查询name的时候只查询age是无法使用这个联合索引的。

 

假如user表中再添加一个字段sex,再使用以上sql查询时就又不会走联合索引树了，因为sex这个字段不在联合索引树中还需要去查询主键索引树，所以干脆就直接查询主键索引还会更快些。

 

总结

如果想正确使用非主键索引

第一：查询条件一定安装索引字段进行查询。

第二：查询结果集一定包含在索引内，不要查询索引外的字段，就想name，age联合索引，

select id,name,age from user where name = 'lili' and age = 18;

这就是一条符合查询联合索引的sql。

select id,name,age,sex from user where name = 'lili' and age = 18;

这是一条不符合查询联合索引的sql。

 

总之一句话查询条件和结果集都在索引内。