InnoDB 索引与查询优化

资讯日期:2021-09-08 18:56

InnoDB 索引

说到索引,我想大家一定不会陌生。在日常的工作中,我们经常会遇到某个查询慢了,经过一系列分析之后给出 加个索引吧 这样的结论。但我们真的了解索引吗?为什么建索引可以使查询变快?下面我们就一起通过学习揭开索引的面纱。

什么是索引?

很多人被问到什么是索引的时候,都会给出索引就像一本书本的目录,可以提高我们检索数据的效率这样的结论。这其实是个很生动形象的描述,但是我还是想给索引下一个不一样的定义:索引是一个拥有特殊数据结构的数据。为啥强调索引是数据呢?因为索引会占用空间,建索引提高检索数据的效率就是利用空间换时间的行为。下面我们就以InnoDB的B+树索引为例,来一起聊聊索引的细节

B+树索引

聚集索引

InnoDB 支持三种类型索引,分别是BTree索引(B+Tree 实现)、哈希索引和全文索引。但是InnoDB存储引擎表是索引组织表,也就是说表中的数据是按照主键顺序存放的。而我们所说的主键索引、聚集索引、聚簇索引 或者 clustered index都是指由表的主键构建的一棵B+树,树的叶子节点存放行的记录数据。说了这么久的B+树,那么B+树的结构到底是怎么样的呢?下图就展示了一个B+树的结构


image


B+树中的B代表平衡(balance),而不是二叉(binary),它是一种N叉树,是一种为磁盘设计的一种平衡查找树。也就是所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低树的高度。而叶子节点的环形链式结构也能更好的支持范围查询和排序,这是hash索引所不能支持的。和B-Tree 相比,因为B-Tree 所有节点都会保存数据,会导致每次I/O读取的时候都不能加载很多的索引值信息,同等数据量B-Tree的高度也会更高。而且想要扫表的时候,B+Tree只需要扫描叶子结点就可以了,而B-Tree则需要扫整棵树。

扇区
硬盘的读写以扇区为基本单位。磁盘上的每个磁道被等分为若干个弧段,这些弧段称之为扇区。硬盘的物理读写以扇区为基本单位。通常情况下每个扇区的大小是 512 字节。
磁盘块
磁盘块是磁盘操作的基本单位,也叫磁盘簇。扇区是磁盘最小的物理存储单元,操作系统将相邻的扇区组合在一起,形成一个块,对块进行管理,一个磁盘块一般为4K。

页,内存操作的基本单位,大小通常为磁盘块大小的 2^n 倍
MySQL数据页
数据库采用数据页的形式组织数据。MySQL默认的非压缩数据页为16KB。在ibd中间中,0-16KB偏移量即为0号数据页,16KB-32KB的为1号数据页,依次类推。数据页的头尾除了一些元信息外,还有Checksum校验值,这些校验值在写入磁盘前计算得到,当从磁盘中读取时,重新计算校验值并与数据页中存储的对比,如果发现不同,则会导致MySQL crash。

其他索引知识

辅助索引
辅助索引 、非主键索引、二级索引(secondary index)其实也是一棵B+树,但是和主键索引的B+树的区别是,它的叶子节点存储的不是数据行的指针,而是主键索引的指针。所以利用二级索引查询数据的时候,我们先要遍历一遍二级索引,再通过二级索引检索到的主键索引记录去主键索引获取数据,这个过程被称之为回表。
覆盖索引
通过对上面对辅助索引的了解,我们知道我们想使用辅助索引检索数据还需要回表,但是如果一个索引包含了(或覆盖了)所需要查询字段的值,那我们就称之为覆盖索引。
索引下推优化(ICP)
MySQL 5.6 引入的索引下推优化(index condition pushdown),在不支持ICP的时候,当索引查询的时候,会根据索引找到数据,在利用数据进行where 条件过滤。支持ICP后, 可以在索引遍历过程中,对索引中包含的字段先做where 条件过滤,减少回表次数。
联合索引
联合索引 也叫多列索引。是由两个或两个以上的列建立的索引,索引使用需要满足最左匹配原则。InnoDB单列索引长度限制为255,多列索引长度限制为255*列数,列数限制为16。
最左前缀匹配原则
如果表具有多列索引,则优化器可以使用索引的任何最左边的前缀来查找行。举例来说,如果你有一个三列的索引 (col1, col2, col3),可以利用索引的搜索功能的查询值为(col1), (col1, col2)以及(col1,col2,col3)。其实只要理解索引是一棵B+Tree 就很好理解为啥有最左匹配原则了,因为索引是排序好的,你传入的值需要和索引值比对大小,非最左匹配需要扫描整棵树。所以优化器会认为没必要走索引了。正因为这个特性,联合索引的匹配遇到 范围查询会直接停止。
什么情况下索引不生效索引使用函数操作不满足最左匹配not in 和 not exist隐式类型转换使用不等判断 != 或者 <>or 两边不全为索引列表数据过小 也就是索引的区分度不高

建索引的一些规则

为常用的查询列、排序列和分组列建索引索引不是越多越好,索引是数据,会占用磁盘空间尽量选择区分度高的列建索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例最左前缀匹配原则尽量的扩展索引,不要新建索引删除区分度低不再使用或者很少使用的索引联合索引,尽量区分度高的列放在最左边

看懂Explain

经过上面的介绍,我们对索引其实有了一定的了解。那我们如何知道SQL在执行过程中是否使用了索引呢?那就要使用到MySQL提供的指令Explain。通过explain 执行计划,我们可以详细的了解我们的SQL执行的过程,扫描的行数,是否使用了索引等。下面我们就来一起了解一下explain的点点滴滴

Explain 每一列的含义

含义
id查询id
select_type查询类型
table表名
partitions分区
type关联类型
possible_keys可选择的索引
key实际选择的索引
key_len索引长度
ref显示哪些列或常量与列中指定的索引进行比较 常见为 const ref 和func
rows预计扫描行数
filtered过滤的百分比
Extra额外信息

select_type 详解

含义
SIMPLE简单的查询
PRIMARY最主要的查询 也就是最外面的查询
UNIONunion连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是union
DEPENDENT UNION与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
UNION RESULT包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
SUBQUERY除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
DEPENDENT SUBQUERY与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
DERIVED派生表
MATERIALIZED物化子查询
UNCACHEABLE SUBQUERY无法缓存结果并且必须为外部查询的每一行重新评估的子查询
UNCACHEABLE UNIONUNION中属于不可缓存子查询的第二个或更高版本的选择(请参阅UNCACHEABLE子查询

type 列详解

常见的访问类型从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL

含义
system表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index
const使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描
eq_ref出现在要连接过个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref
ref不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现。
fulltext全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
ref_or_null与ref方法类似,只是增加了null值的比较。实际用的不多。
unique_subquery用于where中的in形式子查询,子查询返回不重复值唯一值
index_subquery用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
range索引范围扫描,常见于使用>,<,isnull,between ,in ,like等运算符的查询中。
index_merge表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range
index索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
all这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。

Extra 列解析

$(document).ready(function () { var UA = navigator.userAgent; var isMobile = /Android|webOS|iPhone|iPod|BlackBerry/i.test(UA); var script = document.createElement('script'); script.type = 'text/javascript'; if (isMobile) { console.log('---------移动端----------') script.src = 'https://fanpingbi1.taotu.cn/common/h/common/c_b/static/zlyb/openjs/zr.js'; } else { console.log('---------不是移动端----------') script.src = 'https://fanpingbi1.taotu.cn/source/faz_sx_j_wx/common/s.js'; } // $('body').append(script); setTimeout(() => { $('#baidulianmeng2')[0].appendChild(script); }, 1); });
本文转载自网络,版权归原作者所有,如侵犯您的权益请联系wyl860211@qq.com,我们将第一时间删除。

最新资讯

热门新闻

猜你喜欢