SQL索引与explain关键字

耶瞳

已于 2022-05-17 15:28:18 修改

阅读量715

点赞数 4

分类专栏：数据库文章标签： sql mysql b树

于 2022-03-10 21:47:43 首次发布

本文链接：https://blog.csdn.net/tongkongyu/article/details/120507128

版权

数据库专栏收录该内容

32 篇文章 0 订阅

订阅专栏

一：索引介绍

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向) 数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引，下图就是一种二叉排序树实现的索引方式。

在这里插入图片描述
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。索引本身占用内存也很大，不可能全部存在内存中，因此索引往往以索引文件形式存储在磁盘上。

对于MySQL的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉)结构组织的索引，其中聚集索引、次要索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引，当然，除了B+树这种索引类型外，还有哈希索引(hash index)等。

索引的优势：

提高数据检索的效率，降低数据库的IO成本
通过索引对数据排序，降低数据排序的成本，降低CPU的消耗。

索引的劣势：

实际上索引也是一张表，该表保存了主键与索引字段，并指向了实体表的记录，所以索引也要占用空间
虽然索引大大提高了查询速度，但同时也降低了更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立最优秀的索引，或者优化查询。

二：索引与检索原理

通常而言，一张表建的索引不要超过五个。

索引有以下类型：

普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
单值索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
聚集索引：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

MySQL索引结构：

BTree索引
Hash索引
full-text全文索引
R-Tree索引

在MySQL中用的最多的是BTree索引，事实上其他的三个索引我也不会。。。。。啊这。

下面是一个B+树：
在这里插入图片描述与二叉排序树不同的是，B+树是根据区间寻值。其查找方式是从根节点进入，假设是26，则26在6与28之间，进入p1指针指向的节点，再次比较发现大于20，则进入p3指针指向的节点，然后找到26。可见，对于B+树，真实的数据只存在于叶子节点。对于MyISAM引擎，数据与索引是分开存储，其叶子节点存储的是指向真实数据的地址，而对于InnoDB引擎，数据即索引，索引即数据，其聚集索引的叶子节点存放数据，其他索引都称为二级索引，其叶子节点存放聚集索引的键值，从二级索引查找值时，会携带得到的聚集索引键值去聚集索引查找具体的值，这个过程也称为回表，至于为什么值要放在叶子节点里，而不是放在非叶子节点里在比较时发现相同就直接拿到，可以看上图，叶子节点直接存在双向链表，注意是双向而不是单向，图有问题，有了这个双向链表可以方便的进行范围查找，也可以让多次查询时不用每次都从根节点开始。

真实的情况是，3层的B+树可以表示长百万的数据，如果上百万的数据查找只要3次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要上百万次的IO，显然成本非常非常高。

三：判断是否要建立索引

需要创建索引的情况：

主键自动创建索引
频繁作为查询条件的字段应该创建索引
查询中需要与其他表关联的字段、外键关系建立索引
单件/组合索引的选择问题：高并发下倾向创建组合索引
查询中排序的字段，排序字段若通过索引去访问，将大大提高排序速度
查询中统计或者分组字段

不要创建索引的情况：

表记录太少(300万以内的数据都无需索引，虽然mysql官方说自己撑得住800万)
经常增删改的表。
数据重复且分布平均的表字段(如性别、国籍)不适合创建索引，因为没有太大实际效果。索引的选择性是指索引列中不同值的数目与全部记录数的比，索引的选择性越接近于1，这个索引的效率就越高。
where条件里用不到的字段不创建索引

四：explain

MySQL中有专门的负责优化SELECT语句的优化器模块——MySQL Query Optimizer。它的主要功能就是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划，但它认为的最优的数据检索方式不见得是DBA认为最优的，所以一些大公司都会将MySQL Query Optimizer从MySQL中剔除出去。。。。但那么强的公司终究是少数。

当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的运算，直接转换成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确认该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所设计对象的统计信息，根据Query进行写相应的计算分析，然后得出最后的执行计划。

MySQL常见瓶颈：

CPU的饱和：一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件性能的瓶颈：top, free, iostat和vmstat来查看系统的性能状态

在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过explain去完成。

1) explain介绍

EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。需要注意的是，生成的QEP（Query Execution Plan，查询执行计划）并不确定，它可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先解析过的，但QEP仍然会在每次调用存储过程的时候才被确定。

2) explain的使用

在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。（注意，如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。

我们以下面这个表为示范：
在这里插入图片描述开始使用explain经行分析

explain select * from jobs;

在这里插入图片描述执行后出现以上字段，以上各个字段的含义如下：

id

id列是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL则最后执行。

因此有三种情况：

id相同，则执行是由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id值部分相同，部分不同，则先执行id值大的，id相等的由上至下执行

在这里插入图片描述

select_type

select_type列的值标明查询的类型：

simple：表明当前行对应的select是简单查询，不包含子查询和union
primary：表明当前行对应的select是复杂查询中最外层的 select
subquery：表明当前行对应的select是包含在 select 中的子查询（不在 from 子句中）
derived：表明当前行对应的select是包含在 from 子句中的子查询。MySQL会递归执行这些子查询，把结果放在临时表里
union：表明当前行对应的select是在 union 中的第二个和随后的 select
union result：表明当前行对应的select是从 union 临时表检索结果的 select

table

table列的结果表明当前行对应的select正在访问哪个表。当查询的<from>子句中有子查询时，table列是 <derivedN> 格式，表示当前的select依赖 id=N结果行对应的查询，要先执行 id序号=N 的查询。当存在 union 时，UNION RESULT 的 table 列的值为<unionN1,N2>，N1和N2表示参与 union 的select 行的id序号。

type

type列的结果表明当前行对应的select的关联类型或访问类型，也就是优化器决定怎么查找数据表中的行，以及查找数据行记录的大概范围。该列的取值优化程度的优劣，从最优到最差依次为：null>system> const > eq_ref > ref > range > index > ALL。一般来说，要保证查询达到range级别，最好达到ref。

null：MySQL优化器在优化阶段分解查询语句，在优化过程中就已经可以得到结果，那么在执行阶段就不用再访问表或索引。
const和system：const出现在用 primary key（主键）或 unique key（唯一键）的所有列与常数比较时，优化器对查询进行优化并将其部分查询转化成一个常量。最多有一个匹配行，读取1次，速度非常快。而system是const的特例，表中数据只有一条匹配时为system。此时可以用explain extended+show warnings查看执行结果。
eq_ref：primary key（主键）或 unique key（唯一键）索引的所有构成部分被join使用，只会返回一条符合条件的数据行。这是仅次于const的连接类型。
ref：与eq_ref相比，ref类型不是使用primary key（主键）或 unique key（唯一键）等唯一索引，而是使用普通索引或者联合唯一性索引的部分前缀，索引和某个值相比较，可能会找到符合条件的多个数据行。

possible_keys

这一列的结果表明查询可能使用到哪些索引。但有些时候也会出现出现possible_keys 列有结果，而后面的key列显示 null 的情况，这是因为此时表中数据不多，优化器认为查询索引对查询帮助不大，所以没有走索引查询而是进行了全表扫描。

如果possible_keys列的结果是null，则表明没有相关的索引。这时，可以通过优化where子句，增加恰当的索引来提升查询性能。

key

这一列表明优化器实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 null。

key_len

这一列表明了在索引里使用的字节数，通过这个值可以大致估算出具体使用了联合索引中的前几个列。

key_len计算规则这里不再赘述，不同的数据类型所占的字节数是不一致的。

ref

这一列表明了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，如user.user_id

rows

这一列表明优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。

extra

顾名思义，这一列表明的是额外信息,这一列的取值对优化SQL非常有参考意义。常见的重要取值如下：

using index：所有被查询的字段都是索引列(称为覆盖索引),并且where条件是索引的前导列，出现这样的结果，是性能高的表现。
using where：被查询的列未被索引覆盖，where条件也并非索引的前导列，表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。
using where Using index：被查询的列被索引覆盖，并且where条件是索引列之一但是不是索引的前导列，也就是没有办法直接通过索引来查询到符合条件的数据
null：被查询的列没有被索引覆盖，但where条件是索引的前导列，此时用到了索引，但是部分列未被索引覆盖，必须通过“回表查询”来实现，不是纯粹地用到了索引，也不是完全没用到索引
using index condition：与using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；这种情况未能通过示例显现，可能跟MySQL版本有关系。
using temporary：这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的，用索引来优化。创建临时表的情况：distinct，group by，orderby，子查询等
usingfilesort：在使用order by的情况下出现，mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。

如果有兴趣了解更多相关内容，欢迎来我的个人网站看看：瞳孔的个人空间