MySQL高级 - 索引原理、EXPLAIN性能分析

RG
88 阅读6分钟

「这是我参与2022首次更文挑战的第3天，活动详情查看：2022首次更文挑战」

索引原理

MySQL中索引的常用数据结构有两种，一种是Hash，另一种是BTree。

HASH结构

  • Hash底层实现是由Hash表来实现的，是根据键值<key,value>存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

    • 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。

  • Hash索引的缺点

    • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
    • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
    • 哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。

  • Hsah索引的优点

    • 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引
    • 访问哈希索引的数据非常快，除非有很多哈希冲突。

B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

  • B+Tree结构

    • 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
    • 叶子节点包含了所有的索引值和data数据
    • 叶子节点用指针连接，提高区间的访问性能

  • B树索引的应用

    • 全键值查询 where x=123
    • 键值范围查询 where 45 < x < 123

EXPLAIN性能分析

EXPLAIN简介

  • 概述
    • 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：
  • EXPLAIN的作用
    • 表的读取顺序。（对应id）
    • 数据读取操作的操作类型。（对应select_type）
    • 哪些索引可以使用。（对应possible_keys）
    • 哪些索引被实际使用。（对应key）
    • 表直接的引用。（对应ref）
    • 每张表有多少行被优化器查询。（对应rows）
  • EXPLAIN的入门
    • explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。
    explain select * from course;

EXPLAIN字段介绍

ID介绍

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

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

select_type和table介绍

查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

  • simple：简单的select查询，查询中不包含子查询或者UNION
  • primary：查询中若包含任何复杂的子部分，最外层查询被标记
  • subquery：在select或where列表中包含了子查询
  • derived：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
  • union：如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
  • union result：UNION的结果

type介绍

type显示的是连接类型，是较为重要的一个指标。下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

-- 简化
system > const > eq_ref > ref > range > index > ALL
  • system：表仅有一行(等于系统表)。这是const连接类型的一个特例,很少出现。
  • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键放在where条件中，MySQL就能将该查询转换为一个常量
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见与主键或唯一索引扫描
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，这是比较常见连接类型。
  • range：只检索给定范围的行，使用一个索引来选择行。
  • index：出现index是SQL使用了索引，但是没有通过索引进行过滤，一般是使用了索引进行排序分组
  • ALL：对于每个来自于先前的表的行组合，进行完整的表扫描。

一般来说，需要保证查询至少达到range级别，最好能到ref

possible_keys 与 key介绍

  • possible_keys
    • 显示可能应用到这张表上的索引， 一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
  • key
    • 实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
    • 覆盖索引：一个索引包含(或覆盖)所有需要查询的字段的值,通过查询索引就可以获取到字段值

key_len介绍

表示索引中使用的字节数，可以通过该列计算查询中使用索引的长度。

key_len字段能够帮你检查是否充分利用了索引ken_len越长，说明索引使用的越充分

ref 介绍

  • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows 介绍

  • 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；越少越好

总结：当我们需要优化一个SQL语句的时候，我们需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描； 使用explain关键字可以模拟优化器执行sql语句，从而知道mysql是如何处理sql语句的，方便我们开发人员有针对性的对SQL进行优化。

  • 表的读取顺序。（对应id）
  • 数据读取操作的操作类型。（对应select_type）
  • 哪些索引可以使用。（对应possible_keys）
  • 哪些索引被实际使用。（对应key）
  • 每张表有多少行被优化器查询。（对应rows）
  • 评估sql的质量与效率 (对应type)

extra 介绍

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

  • Using fifilesort

执行结果Extra为Using filesort，这说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。典型的，在一个没有建立索引的列上进行了order by，就会触发fifilesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。filtered它指返回结果的行占需要读到的行(rows列的值)的百分比

  • Using temporary

执行结果Extra为Using temporary，这说明需要建立临时表(temporary table)来暂存中间结果。常见与group by和order by，这类SQL语句性能较低，往往也需要进行优化。

  • Using where

此语句的执行结果Extra为Using where，表示使用了where条件过滤数据

需要注意的是：

  1. 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
  2. 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。
  • Using index

执行结果为Extra为Using index，说明sql所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

  • Using join buffffer

执行结果Extra为Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算，这里每个表都有五条记录，内外表查询的type都为ALL。

avatar
文章
阅读
粉丝