Mysql索引与存储引擎详解

索引与存储引擎

想一下我们查字典的时候是不是要用偏旁部首笔画之类的去查我们想要的东西？查字典的案例就相当于使用索引。

现在我们可以将数据库分为两类，一种是OLAP，一种是OLTP。对于OLAP来说，数据主要是一些历史数据，用来做数据分析与决策的，不要求实时性，hive就是其中之一。hive建索引的三要素就是(关键值，文件名，偏移量)。

对于OLTP来说，主要是处理事务的，要求实时性较高，我们就需要更快的查询方式，也就是一个更好的数据结构。mysql使用的是B+树。

mysql还有一种数据结构叫哈希，哈希用于memory这个存储引擎。存储引擎就是数据文件在物理磁盘的组织形式。myisam和innodb(默认)使用的B+树，而memory使用的是hash索引。

那么innodb能使用hash吗？可以！但是是由mysql自适应调整，用户无法干预。
如下图是使用innodb存储引擎组织的数据文件。.frm文件存储的是表的结构，而.ibd文件存储的是真实地数据和索引。

我们在来看看myisam存储引擎的组织形式。
.frm依然是表的结构，.MYD是真实数据，.MYI是索引。

磁盘预读

我们都知道内存的读写速度与磁盘的读写速度根本不是一个量级，于是我们利用局部性原理，即使我仅仅需要从磁盘读取一个字符a，我也会将一个页的数据加载到内存。

Mysql数据结构的选择(为什么要选择B+)

先来看看hash的缺点。

那么当存储引擎为memory时，以上缺点均可忽略，因为是在内存，不管是等值查询还是范围查询，都不在乎，因为内存很快！！！
再来看看二叉树与红黑树。innodb引擎每次从磁盘取16k的页，每个树的节点就放一个值，这样会放很多节点，导致树的深度加深。

提升IO效率的两个方法
1.减少IO的次数

2.减少IO的大小

B树

我们可以看到B树在每一个磁盘块里都存放了某表里每一行的记录data，节点里面的数值是对表里的某一列（如id）建的索引列。假设么一个磁盘块(一页)有16KB，一行记录有1kb，那么每一个磁盘块可以存16行记录，然后三层的b树能存16x16x16=4096行记录(这里仅仅粗略的计算一下)

B+树

我们可以看到B+树每一个关键字出现了两次。然后只有叶子节点挂了每行的记录。假设一块还是16KB，然后关键字占10个字节，16KB/10个字节=1600个关键字，那么前两层有1600x1600的关键字，然后最后一层存data时，每一行记录为1kb，每一块存16行记录，然后1600x1600x16=40960000行记录(粗略估算)，所以三层的B+树能存的记录数是B树的10000倍。

如下图，我们来看看B+树的叶节点，我们发现与B树的叶节点不一样，B+树的叶节点可以从左向右（从小到大）进行遍历，因为最小关键字节点上有一个头指针，就相当于一个链表。当然，B+树也可以从根节点开始遍历。

建立索引

建立索引时不必全字段建立，可以只对前几个字符建立，如dname，我只对前20个字符建立。

Innodb与Myisam的底层

我们可以看到innodb存储数据时，是将表中的数据也一并存在B+树中。Innodb是通过B+树对主键建立索引，如果表里没有主键，则会自动选取唯一键作为索引，如果连唯一键都没有，那么会生成一个6字节的row_id来作为主键(用户不可见)。Innodb是聚簇索引，因为索引和数据都存在B+树中

再来看看myisam，myisam是非聚簇索引，因为跟主键一起存的是记录的地址。

那如果我对表里的非主键列手动添加索引呢？那么数据的组织形式就是下图在这样。叶子节点存放的是主键对应的值。

[注]单机版mysql推荐使用主键自增，集群版不推荐。

几个与索引相关的概念

回表

比如我执行select * from dept where name='gang';由于我对name建立了索引，所以先是在name这颗B+树上搜索，当搜索到gang时，找到对应主键，然后再去主键的那颗B+树，通过该主键找*的所有内容。这样看来遍历了两次B+树，也许你会觉得速度反而更慢了，但是当数据量多的时候，速度会明显提升。

索引覆盖

当我执行select id from dept where name='gang'时，我直接就找到id了，这种情况就叫索引覆盖。

组合索引与前缀匹配

我们在工作中，常常会遇到到几个字段经常使用，那么我们就将他们构建成组合索引。
我们就用员工表的name和age建。

如下图所示建立组合索引。

那么何谓前缀匹配？
我随便写四句SQL,大家来判断一下哪些情况会走索引？

select * from t_yuangong where name=? and age=?;

select * from t_yuangong where name=?;

select * from t_yuangong where age=?;

select * from t_yuangong where age=? and name=?;

结论，124会走索引。因为我建立的组合索引顺序(name,age),必须先走name，才能走age。第4个sql是通过mysql里的优化器，自动帮我们调整的，Mysql默认用CBO基于代价的优化器。
我们来实际执行一下就知道了。
第一句sql：

第2句sql：

第3句sql：（可以看到key为空）

第4句sql：

索引下推

还是来看这句sql：select * from t_yuangong where name=? and age=?;
首先我们要知道(name,age)组合索引是在同一个B+树上。
组织形式是一个二元组，如下图：比如我找(2,3),那么我肯定要先看name列，再看age列。

没有索引下推时，先从存储引擎中拉取数据(根据name筛选的数据)，然后mysql服务器根据拉去的数据再通过age去筛选。

有索引下推时，会直接根据name，age来获取数据，不需要再用mysql服务器进行数据筛选。

索引下推是将数据筛选从内存中转移到了磁盘上！！大大减少了IO量

MRR（Multi range read）

根据辅助索引的叶子结点上找到的主键值的集合存储到read_rnd_buffer中，然后在该buffer中对主键值进行排序，最后再利用已经排序好的主键值的集合，去访问表中的数据，这样就由原来的随机I/O变成了顺序I/O，降低了查询过程中的I/O消耗。

SELECT * FROM t WHERE key_part1>=1000 and key_part1<2000 AND key_part2=1000;

MRR是在内存中排序，不需要从根节点挨个遍历了。
表t有(key_part1,key_part2)的联合索引因此索引根据key_part1,key_part2的位置关系进行排序。

若没有MRR，此时查询类型为Range。SQL优化器会先将key_part1>1000 and key_part1<2000的数据先取出来，即使key_part2不等于1000。待取出的行数据后在根据key_part2的条件进行过滤，这会导致无用的数据被取出，如果有大量的数据且其key_part2不等于1000，则启用MRR优化会使性能有巨大的提升

启用MRR优化，优化器会先将查询条件进行拆分，然后在进行数据查询。上述语句，优化器会将查询条件拆分为(1000,1000),(1001,1000),(1002,1000),…,(1999,1000)，然后在根据这些拆分出的条件进行数据查询

FIC(fast index creation)

MySQL5.5之后，对innodb表创建或删除辅助索引的效率提升了很多，即增加了新的功能fast index creation。因为MySQL5.5之后，创建和删除辅助索引不在需要拷贝整个表的数据。

在5.5之前，在一个已经存在数据的表上增加或者删除索引是很耗时的。create index或drop index按照以下的方式进行工作：
-创建一个新的、空的临时表，表结构为使用alter table定义的新结构
-逐一拷贝数据到新表，插入数据行同时更新索引
-删除原表
-将新表的名字改为原表的名字

FIC原理如下：

索引类型

普通索引：又称二级索引或辅助索引，就是在非主键，非唯一的字段上加索引。

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个。(当有主键时，innodb就用主键来组织数据，当没有主键时，就用一个唯一的字段)

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

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

索引匹配方式

全值匹配

我们看看下面这张表，(name,age,pos)是组合索引。我们按下面这句sql对其进行全值匹配。

看一下执行计划，该索引占了140字节。

我们来看看表的结构，我们用的是utf-8编码，所以就是(24+20)x3+4+2+2=140，第一个4是int所占字节，后面两个2是varchar类型的标志位。

匹配最左前缀(只匹配前几列)

再看看下面这个查询，删掉了pos字段，就占78字节，24x3+4+2=78

匹配列前缀(匹配某些列的一部分)

看下面模糊查询

再看下面这种模糊查询，索引失效了。

匹配范围值

如下图范围查询，这种效率是高的。

但是，看下图这种查询，在范围查询之后的条件是不走索引的。

索引覆盖

哈希索引

组合索引之关键字or

为了说明方便，这一节我就直接干图形化界面了。
我建立了一张表t_abc,里面有三个int类型字段，a,b,c.然后我为他们建立组合索引。

以下这种情况15=4+4+4+1+1+1,1表示是否为空。

好了，我们来看看or的情况，下面这种情况很特殊，因为我是将表里所有的字段一起建立组合索引，所以走的是全索引。

现在我再建立一个表t_abc2，如下图：

然后我只给前三个字段建立组合索引。

执行下面的sql发现不走索引。

但是指定了查询字段他又会走索引，所以我们要尽量不用全表扫描。

in的情况与or一样。

索引覆盖

看看下面inventory表的索引。

然后看看下面这句sql的执行计划，查询字段刚好是组合索引，所以就是索引覆盖(using index)

再来看看actor表的索引结构。

然后下面在actor表中执行下面的命令，因为查到name后就直接可以返回id了，也是索引覆盖。

下面这种就不行了。

索引优化小细节

当使用索引列进行查询时，尽量不要使用表达式，把计算放到业务层，而不是数据库层。

尽量使用主键查询，因为主键查询不会触发回表。
尽量使用前缀索引，也就是给前几个字节建立索引。(之前讲过)

范围列可以用到索引，但是范围列后面的列无法使用索引，一个查询语句里最多只有一个范围列(之前讲过)。

使用索引扫描来排序，关于这一点，请看下面实战：
先来看看这张表的索引结构。

然后执行下面的这句sql，这句sql的字段恰好符合组合索引，所以走了using index condition。

然后下面这句sql我删掉了一个东东，然后就破坏了最左匹配，所以就不再是using index condition了，而是using filesort。

同理，我再把时间字段去了，这还是走的文件排序。

下面两张图展示了就算按照最左匹配原则，如果没有where，仅仅有order by的话，也不会走索引。

然后如下图，某些字段升序，某些字段降序也不会走索引，升序降序一定要统一。

然后下面三种情况也走索引，使用or时，如果是单列索引就会走索引。

如下图，phone是varchar类型，如果查询时，写成phone=整数类型，那么不会报错，但是会发生强制类型转换，从而导致索引失效。

索引监控

Mysql全方位解析