MySQL之索引原理和执行计划-XAMPP中文组官网

在表查询操作时我们提到了一个索引的概念，本文就详细介绍索引实现原理以及简单的介绍一下B+树。MySQL的索引在面试中会被经常问到，擦亮bling bling的大眼睛一起来瞅瞅吧~

索引简介

索引就相当于是书的目录，能够帮助我们快速定位到所需要的数据，可以起到优化查询的功能，索引可以通过B+树进行构建。

B+树简介

树状图是一种数据结构，它是由n（n>=1）个有限结点组成一个具有层次关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树。B+树是通过二叉查找树，再由平衡二叉树，B树演化而来，可以控制树的高度控制搜索数据时的IO次数。IO次数取决于b+数的高度level。

索引实现原理

在数据库中B+树的高度一般都在2到4层，就是说查找某一个记录最多只需要2到4次IO操作，数据库中B+树索引分为聚簇索引和辅助索引，不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，不同的是叶子结点存放的数据不同。

聚簇索引

聚簇索引的构建前提

在建表时指定主键列，MySQL的innodb引擎会将主键列作为聚簇索引列，类似于书籍目录的大标题，如果建表的时候没有指定主键列，innodb引擎会自动选择唯一列作为聚簇索引列，如果都没有的话innodb引擎会生成隐藏的聚簇索引。

聚簇索引的作用

有了聚簇索引之后，插入的数据行在同一个区内都会按照主键值的顺序有序的在磁盘中存储数据，这为顺序IO提供了条件，当使用主键列作为查询条件的时候可以帮助我们快速的查找数据，理论上B+树为三层时，基于主键列只需要3次IO就可以锁定叶子节点中的数据页。

聚簇索引的构建过程

叶子节点：数据行所在的数据页(page,16kb)，由于聚簇索引在存储数据的时候是按照顺序的，叶子节点的数据页就直接生成了，并且叶子节点的数据是每条记录的完整数据。

支节点：主键值的范围抽取出来作为支节点，保存叶子节点的范围。根节点：根据支节点生成根节点，保存支节点的范围。

辅助索引

辅助索引的作用

辅助索引是使用普通列构建的索引，需要人为的创建，类似于书籍目录中大标题下的小标题，辅助索引的作用就是优化使用非聚簇索引列之外的列作为条件进行的查询。

但是辅助索引的缺点就是当使用辅助索引查询数据的时候由于辅助索引的叶子节点只有部分数据，有时还需要进行回表操作，就是还需要通过辅助索引查询的结果再通过聚簇索引得到完整的数据行，需要注意的是，创建辅助索引后，向表中插入数据的速度也会变慢，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

辅助索引的构建过程

叶子节点：将聚簇索引列和辅助索引列取出，按照辅助索引列进行排序，生成叶子节点的数据页，是部分数据，只包含辅助索引和聚簇索引的数据。

支节点：辅助索引列的范围抽取出来作为支节点，保存叶子节点的范围。根节点：根据支节点生成根节点，指向支节点的数据页，保存下层支节点的范围。

辅助索引的类型

辅助索引分为单列索引和联合索引，这里详细介绍一下联合索引。联合索引就是多个非主键列组合成的辅助索引，比如构建(a, b, c)这三个列的联合索引，相当于创建了a, ab, abc这三个索引。

使用联合索引需要遵循最左原则，在生成支节点时，只会将最左列作为支节点，因此查询条件必须包含最左列，因此建立联合索引的时候一定要选择重复值最少的列作为最左列，并且在查询时只要查询条件包含最左列都会走联合索引。可以分析一下查询时走联合索引的情况：

-- 联合索引全部覆盖：
select * from t1 where a= b= c= / a in b in c in /b= a= c=

-- 部分覆盖：
select * from t1 where a= b= / a= / a= c= 
select * from t1 where a= b<= >= like and c= (只走ab索引)

-- 不覆盖：
select * from t1 where b= c=

回表操作

MySQL中表的数据行最终是存储到很多的page上，innodb存储引擎会按照聚簇索引有序的组织存储表数据到各个区的连续的page上，这些连续的page就成为了聚簇索引的叶子节点，即聚簇索引叶子节点存储的就是原表数据。所以回表操作就是回聚簇索引。构建辅助索引的时候，是将主键值和辅助索引列值按照辅助索引列值进行排序构建辅助索引B树结构，当使用辅助索引作为查询条件的时候，会首先扫描辅助索引的B+树，如果辅助索引能够完全覆盖我们的查询结果就不需要进行回表操作，如果不能完全覆盖，只能通过得到的主键值回到聚簇索引（回表）扫描，最终得到想要的结果。

索引操作

上面说了这么多，如何查看和创建索引呢？请看下面的SQL：

-- 查询索引
desc 表名称;  -- PRI聚簇索引  MUL辅助索引  UNI唯一索引
show index from 表名; -- 更加详细的索引信息

-- 创建索引：
alter table 表名 add index idx_na(列名);  -- 单列索引,idx_na只是索引的名字，随便起
alter table 表名 add index idx_n_c_(列名1, 列名2);  -- 联合索引
alter table 表名 add index idx_d(列名(5));  -- 前五个字符构建的前缀索引

-- 删除索引alter table 表名 drop index idx_na;

建立索引规范

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引，因此索引设计在项目开发中是非常重要的，下面小庄列出一些建立索引的规范：①建表时一定要有主键列，一般是一个无关列；②为经常需要where，order by，group by，join on等操作的字段创建辅助索引；

③限制索引的数目，索引并不是越多越多好；

④删除不使用或者很少使用的索引，可以使用percona toolkit工具；

⑤数据很多的表中如果加索引建议在业务不繁忙时段进行操作；

⑥尽量不要在经常更新值的列上创建索引，容易引起索引失效。

不走索引的情况

虽然有时候创建了聚簇索引也创建了辅助索引，但是有时查询速度还是很慢，原因就是很有可能查询语句没有走索引，下面小庄也列出了一些不走索引的情况的查询，供大家参考：①没有查询条件，或者查询条件没有建立索引；②查询的结果集超过了表中数据总行数的15-30%，优化器觉得没必要走索引，这个和数据库的预读能力和参数有关；

③索引失效，统计的数据不真实，索引有自我维护的能力，对于表内容变化比较频繁的情况下，统计信息不准确，有可能会出现索引失效，一般是删除重建。

④查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)；

⑤隐式转换导致索引失效，这一点应当引起重视。也是开发中经常会犯的错误.查询的时候注意数据类型是否和表中定义的数据类型是否一致。

⑥< > ，not in 不走索引（辅助索引，单独的> < in有可能走索引也可能不走，尽量结合业务添加limit,or 和 in 使用不同的条件进行测试，选择哪种具体的方案）。

⑦like “%_” 百分号在最前面不走，%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品.

执行计划分析

在执行SQL语句时，是由解析器生成执行方案，优化器选择执行方案，那么一条SQL语句的执行方案开发人员能不能看到呢？答案是肯定滴，就是通过执行计划，并且通过分析执行计划，开发人员可以根据情况对SQL语句进行优化。执行计划就是优化器按照内置的代价计算算法得到的最终的执行计划，查看执行计划可以通过下述的SQL语句：

-- 二者任选其一
desc sql语句;
explain sql语句;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

开发人员就可以根据上述执行计划进行分析了，如何进行分析，就要看结果中的每个字段表示啥意思了，请听小庄娓娓道来~

table：执行计划涉及到的表，针对多表查询时，能够帮助确定查询较慢的表进行优化。

type：查询类型，分为全表扫描和索引扫描。

全表扫描ALL:不用到任何的索引（全表查询/ not in/ like '%&'等情形不会走任何索引）

索引扫描：index<range<ref<eq_ref<const(system)
 index:全索引扫描，需要扫描整颗索引树
 range:索引的范围查询，当查询>= <= like in or between and等条件时属于范围查询，对于id查询 not in !=是走range索引的
 ref:辅助索引的等值查询
 eq_ref:多表连接中，非驱动表的连接条件是主键或者唯一键
 const(system):聚簇索引等值查询

possible_keys：所有可能用的索引，所有和此次查询有关的索引

key：最后选择的索引

-- 在查询数据的时候如果有多个条件子句，需要使用联合索引，单列索引不会生效。
select * from city where countrycode='CHN' order by population;  -- population单列索引不会生效,index(countrycode, population)生效

key_len：联合索引覆盖长度，评估联合所用的应用长度，对于联合索引比如index(a, b, c)希望将来的查询语句对于联合索引的应用越充分越好，key_len可以帮助判断此次查询走了联合索引的几部分。

key_len计算规则：
在完全覆盖的情况下：key_len = a长度 + b长度 + c长度
长度受到数据类型、字符集的影响，指的是列的最大储值字节长度
没有约束not null时，需要单独一个字节存储列值是否是非空数字列的储值长度：

数字类型/是否非空（not null）	是	否
tinyint	1	1+1
int	4	4+1
bigint	8	8+1

字符列的储值长度：以utf8字符集为例，一个字符最大占3个字节，varchar需要1-2个字节存储字节长度

字符类型/是否非空(not null)	是	否
char(10)	3*10	3*10+1
varchar(10)	3*10+2	3*10+2+1

rows：此次查询需要扫描的行

extra：额外的信息

using filesort：表示此次查询使用到了文件排序，说明在查询中的排序操作没有使用到索引

转载请注明：XAMPP中文组官网 » MySQL之索引原理和执行计划

MySQL之索引原理和执行计划

与本文相关的文章

您必须登录才能发表评论！

与本文相关的文章

您必须 登录 才能发表评论！

您必须登录才能发表评论！