代码编织梦想


MySQL45讲

基础篇

04-05 | 深入浅出索引

InnoDB 的索引模型

在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。
InnoDB 使用了 B+ 树索引模型,数据存储在 B+ 树中(B+ 树详解见链接漫画:什么是B+树?)。每一个索引在 InnoDB 里面对应一棵 B+ 树。
索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。

  • 在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。
  • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。

主键索引和非主键索引的区别在于:非主键索引查询时,首先搜索非主键索引树,获得主键 ID 的值,然后通过主键索引树搜索最终的结果。这个过程称为回表
基于非主键索引的查询需要多扫描一棵索引树。因此,在应用中应该尽量使用主键查询。

索引维护

B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。示例图如下:

InnoDB 的索引组织结构

  • 如果插入新的行 ID 值为 700,则只需要在 R5 的记录后面插入一个新记录。
  • 如果新插入的 ID 值为 400,则需要逻辑上挪动后面的数据,空出位置。
  • 如果新插入的 ID 值为 400且 R5 所在的数据页已满,根据 B+ 树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。页分裂操作不仅影响性能,还影响数据页的利用率。
  • 当相邻两个页由于删除了数据,利用率降低之后,会将数据页做合并。

案例:分析哪些场景下应该使用自增主键,哪些场景下不应该?

  • 从性能和存储空间方面考量,自增主键往往是更合理的选择。

自增主键的插入数据模式,每次插入一条新记录,都是追加操作,不会挪动其他记录,也不会触发叶子节点的分裂;业务逻辑的字段做主键,往往不容易保证有序插入,写数据成本相对较高,而且叶子节点占用空间也相对较大,如用身份证号做主键。主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

  • 适合用业务字段直接做主键的场景
  1. 只有一个索引
  2. 该索引必须是唯一索引

典型的 KV 场景。并且,由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。结合 “尽量使用主键查询”原则,直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。

覆盖索引

如果非主键索引已经“覆盖了”查询需求,则称为覆盖索引。比如:select ID from T where k between 3 and 5,索引 k 中已经包含了主键 ID,不需要回表。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

联合索引示意图

联合索引示意图

可以看到,索引项是按照索引定义里面出现的字段顺序排序的。

“最左前缀”原则,即:查询时,按照联合索引字段从左到右的顺序,则可以使用该索引。 比如:联合索引是(a,b,c),那么 a,ab,abc 都可以走这个索引的,但是 ac(实际上会走索引 a) 不走,bc 不走。

“最左前缀”原则,可以是最左N个字段,也可以是最左M个字符。 如果要查的是所有名字第一个字是“张”的人,SQL 语句的条件是 “where name like ‘张 %’”。这时,也能够用上这个索引,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。

如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。 比如:当已经有了 (a,b) 这个联合索引后,一般就不需要单独在 a 上建立索引了。

疑问:在建立联合索引的时候,如何安排索引内的字段顺序?

  1. 复用能力
    即高频优先。
  2. 占用空间
    如果既有联合查询,又有基于 a、b 各自的查询?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的。如果 a 字段是比 b 字段大的 ,建议创建一个(a,b) 的联合索引和一个 (b) 的单字段索引。
索引下推
select * from tuser where name like '张%' and age=10 and ismale=1;

根据“最左前缀”原则,这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录 ID3。
在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。

MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

无索引下推执行流程

无索引下推执行流程

这个过程 InnoDB 并不会去看 age 的值,只是按顺序把 “name 第一个字是 ’张’ ” 的记录一条条取出来回表。因此,需要回表 4 次。

索引下推执行流程

索引下推执行流程

与上图的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/TQ20160412/article/details/120130670

mysql之存储引擎-爱代码爱编程

InnoDB 介绍:InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL5.5之后,InnoDB是默认的MySQL存储引擎。 特点: DML操作遵循ACID模型,支持事务;行级锁,提高并发访问性能

在数据库中进行表内容的修改(mysql)-爱代码爱编程

根据表中内容,用命令语句创建数据库,表格,以及插入,修改,删除表格中的内容。 创建数据库:zrzy mysql> create database zrzy; 引用zrzy数据库: mysql> use zrzy; 创建student_info表: mysql> create table student_info(    A

mysql的外键-爱代码爱编程

MySQL 中的外键是一种关系型数据库特性,用于在两个表之间建立关联。外键定义了一个表中的列(或多列)与另一个表中的数据的关系,通常是通过主键和外键之间的关联。在 Django 的模型中,外键通过 models.Foreig

mysql中的锁及其作用-爱代码爱编程

在MySQL中,锁是用于控制对数据库对象的并发访问的一种机制。锁可以防止多个事务同时对同一数据进行修改或删除,以确保数据的完整性和一致性。 目录 MySQL中的锁有以下几种类型: 在MySQL中,使用锁需要注意以下几点: MySQL中的锁有以下几种类型: 共享锁(Shared Lock):也称为读锁(Read Lock)。多个事务可以

mysql 8.0关键字和保留字-爱代码爱编程

官网地址: https://dev.mysql.com/doc/refman/8.0/en/keywords.html 可以粘贴出去自己排版整理 {accessible} {account} {action} {a

关于mysql的lower_case_table_names引发的思考_mysql 如何查看lower_case_table_names 参数值-爱代码爱编程

lower_case_table_names设置大小写敏感的三个值0、1、2的区别? lower_case_table_names参数详解 1.参数说明: lower_case_table_names= 0 表名 存储

mysql 临时数据空间不足导致sql被killed 的问题与扩展-爱代码爱编程

开头还是介绍一下群,如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题,有需求都可以加群群内,可以解决你的问题。加群请联系 liuaustin3 ,(共1730人左右 1 + 2 + 3 + 4 +5) 4群(230+),另欢迎 OpenGauss

【mysql】-爱代码爱编程

一、背景介绍 MySQL中提供了各种各样的日志,每一个日志在不同的阶段有不同的作用,对数据的一致性和正确性得到保障,为数据恢复也提供至关重要的作用,那今天我们一起来讨论讨论MySQL中的各个日志 二、正文 binl

mysql基本命令-爱代码爱编程

MySQL 是一个流行的关系型数据库管理系统,以下是一些常用的 MySQL 基本命令: 连接到 MySQL 服务器: mysql -u [username] -p [password] 其中 [username] 是

阿里云mysql从 2003-爱代码爱编程

目的         由于需要在阿里云的实例中装MySQL数据库,安装前期(本地访问)还是挺顺利的,但是到了远程连接的时候,却出现了一系列的Bug,以为是没有        实名认证+没有备案        的原因导致的,但是后来想了想,不该呀,我是通过公网IP进行访问的,按理说跟那些没有关系才对,于是就在各个博主那里取到了经,现在回来记录下

jdbc的介绍与下载(mysql为例)-爱代码爱编程

1. JDBC简介 JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成

【mysql】内置函数-爱代码爱编程

目录 前言 一、日期函数 案例:创建一个留言表 二、字符串函数 三、数学函数​编辑 四、其它函数 前言 本篇博客介绍MYSQL中内置的函数,例如:时间函数、字符串函数、数学函数…… 希望各位看完能有所收获。 一、日期函数 获取年月日: mysql> select current_date(); +

【头歌系统数据库实验】实验2 mysql软件操作及建库建表建数据_头歌实验环境 mysql-爱代码爱编程

目录 第1关:创建数据库 第2关:创建供应商表S,并插入数据 第3关:创建零件表P,并插入数据  第4关:创建工程项目表J,并插入数据  第5关:创建供应情况表SPJ,并插入数据 如果觉得对你有帮助的话,不妨点赞、收藏、评论一下吧,爱你么么哒😘❤️❤️❤️ 第1关:创建数据库 任务描述 创建一个名为mydata的数据库。

mysql 索引类型-爱代码爱编程

什么是索引? 索引是一种用于提高数据库查询性能的数据结构。它是在表中一个或多个列上创建的,可以加快对这些列的数据检索速度。 索引的作用是通过创建一个额外的数据结构,使得数据库可以更快地定位和访问数据。当执行查询语句时,数据库可以使用索引来快速定位满足查询条件的数据行,而不需要逐行扫描整个表。这样可以大大减少查询的时间和资源消耗。 以下是一些MyS