Skip to main content

moregeek program

#yyds干货盘点#深入浅出,一文吃透mysql索引-多极客编程

索引是什么


索引是为了提高数据查询效率的数据结构,类似于书的目录一样,可以根据目录而快速找到相关内容。


MySQL 8.0 版本中,InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引,其中,B+ 树索引使用最为广泛。


B+树索引


每一个索引在 InnoDB 里面对应一棵 B+ 树。
B+树索引的特点 :基于磁盘的平衡树,树非常矮,一般为 3~4 层,所以访问效率非常高,从千万或上亿数据里查询一条数据,只用 3、4 次 I/O。


假设我们有如下表,ID是主键,字段 k 上有索引:


#yyds干货盘点#深入浅出,一文吃透mysql索引


主键索引和非主键索引的示意图如下:


#yyds干货盘点#深入浅出,一文吃透mysql索引


其中R代表一整行的值。


主键索引和非主键索引的区别是:


  • 主键索引的叶子节点存放的是整行数据;
  • 非主键索引的叶子节点存放的是主键的值;
  • 非主键索引也被称为二级索引,而主键索引也被称为聚簇索引。

1、如果查询语句是


select * from table where ID = 100,以主键查询的方式,只需要搜索 ID 这棵 B+ 树。


2、如果查询语句是


select * from table where k = 1,以非主键的查询方式,则需要先搜索 k 索引树,得到 ID=100,再到 ID 索引树搜索一次,这个过程也被称为回表。


MySQL 中 B+ 树索引的管理



  1. 命令 EXPLAIN 查看是否使用索引。



  2. 查询表 mysql.innodb_index_stats 查看每个索引的大致情况。
字段 释义
database_name 数据库名
table_name 表名
index_name 索引名
last_update 统计信息最后一次更新时间
stat_name 统计信息名
stat_value 统计信息的值
sample_size 采样大小
stat_description 类型说明
  1. 查询表 sys.schema_unused_indexes 查看有哪些索引一直未被使用过,可以被废弃。
  • MySQL5.7 及以上的版本sys模式下
  • schema_redundant_indexes 和 schema_unused_indexes 两个视图

MySQL 存储数据和索引对象分析


索引组织表


数据的存储分为堆表索引组织表,目前大部分数据库都支持索引组织表的存储方式。


  1. 堆表

#yyds干货盘点#深入浅出,一文吃透mysql索引


如上图,堆表中的数据和索引是分开存储的,索引有序而数据是无序的,索引的叶子节点存的是数据在堆表中的地址。堆表中数据发生变更,其位置也会变,导致索引中的地址都需要更新,所以很影响性能。


  1. 索引组织表

数据根据主键排序存放在索引中,主键索引又叫聚集索引。在索引组织表中,数据即索引,索引即数据。InnoDB 存储引擎就是这样的数据组织方式。


二级索引


除了主键索引外,其他的索引都称之为二级索引,或非聚集索引,同样也是一颗 B+ 树索引,它和主键索引不同的是叶子节点存放的是索引键值、主键值


当通过使用二级索引来查询数据时,通过二级索引先找到主键值,再通过主键索引进行查询数据,这种二级索引通过主键索引进行再一次查询”的操作叫作回表


#yyds干货盘点#深入浅出,一文吃透mysql索引


与堆表相比,这种索引组织表这样的二级索引,若有数据发生变更时,其他索引无须进行维护,除非记录的主键发生了修改,所以性能优势会非常明显。


覆盖索引


上面提到了,二级索引的叶子节点存放的是索引键值、主键值,


例如我们有如下表:


create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

  1. 索引覆盖
    select id,name from user where name='ls';

#yyds干货盘点#深入浅出,一文吃透mysql索引


能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。


  1. 回表
    select id,name,sex from user where name='ls';

#yyds干货盘点#深入浅出,一文吃透mysql索引


能够命中name索引,索引叶子节点存储了主键id,但sex字段必须回表查询才能获取到,不符合索引覆盖,需要再次通过id值扫码聚集索引获取sex字段,效率会降低。


索引调优


函数索引


从 MySQL 5.7 版本开始,MySQL 开始支持创建函数索引 (即索引键是一个函数表达式)。 函数索引有两大用处:


  1. 优化业务 SQL 性能:

假如我们有一个注册日期字段 register_date,并对其创建了索引,现在有如下条件查询 where DATE_FORMAT(register_date,'%Y-%m') = '2021-10',那么能不能命中索引呢?


答案是不能,索引只对 register_date 的数据排序,并没有对 DATE_FORMAT(register_date) 排序,因此不能使用到此索引。


我们可以使用函数索引解决这个问题, 创建一个DATE_FORMAT(register_date) 的索引。


ALTER TABLE Testtable
ADD INDEX
idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

  1. 配合虚拟列(Generated Column)。

例如有如下表:


CREATE TABLE User (
userId BIGINT,
userInfo JSON,
mobile VARCHAR(255) AS (userInfo->>"$.mobile"),
PRIMARY KEY(userId),
UNIQUE KEY idx_mobile(mobile)
);

mobile 列就是一个虚拟列,由后面的函数表达式计算而成,本身这个列不占用任何的存储空间,而索引 idx_mobile 实质是一个函数索引。这样做的好处是在写 SQL 时可以直接使用这个虚拟列,而不用写冗长的函数:


-- 不用虚拟列
SELECT * FROM User
WHERE userInfo->>"$.mobile" = '15088888888'

-- 使用虚拟列
SELECT * FROM User
WHERE mobile = '15088888888'

最左前缀原则


B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。


例如我们有字段 a 和 b,都为高频字段,为了减少回表,我们可以建立联合索引 (a,b),这时不需要单独在 a 上建立索引了。


但是如果查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个索引, 如果 a 字段比 b 字段大可以创建 (a,b)、(b) 这两个索引,反之创建 (b,a)、(a) 这两个索引。


普通索引与唯一索引的选择


先说结论:业务代码已经保证不会写入重复数据”的情况下,建议尽量选择普通索引。


查询时:



  • 普通索引,查找到满足条件的第一个记录后,还需要查找下一个记录,直到碰到第一个不满足条件的记录。



  • 唯一索引,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。

上面的不同之处在性能差距上微乎其微。因为对于数据的读取不仅仅将需要读取的某一条数据从磁盘上读取出来,Innodb的数据是按照页为单位来进行读写的,每页的默认大小为16KB,所以对于普通索引来说,只是多做一次“查找和判断下一条记录”的操作,只需要一次指针寻找和一次计算,操作成本对于现在的 CPU 来说可以忽略不计。


更新时:



  • 普通索引,则是将更新记录在 change buffer,语句执行就结束了。



  • 唯一索引,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束。

唯一索引的更新不能使用 change buffer,普通索引可以使用到


什么是 change buffer?



  1. 当对数据页进行更新时,如果数据页在内存中则直接更新,如果不在 Innodb 会将更新操作记录在 change buffer 中,免去了去磁盘中读取数据页的过程,下次查询的时候,再将数据页读入内存,结合 change buffer 记录来返回数据,同时进行 merge 操作(将 change buffer 中的操作应用到原数据页)。
  2. change buffer 在内存中有拷贝,也会被写入到磁盘上,它是可以持久化的数据的。

对于唯一索引,更新时需要将数据页读取到内存中来判断是否违反了唯一性约束,数据页既然都已经读到内存中了,自然也就不需要 change buffer了;而普通索引,则是将更新记录在 change buffer。由于磁盘IO成本较高,不如使用 change buffer 对性能更加友好。


组合索引


组合索引(Compound Index)是指由多个列所组合而成的 B+树索引。


  1. 例如:

#yyds干货盘点#深入浅出,一文吃透mysql索引


对组合索引(a,b),因为其对列 a、b 做了排序,所以此索引可以优化的的 SQL 有:


WHERE a = ?
WHERE a = ? AND b = ?
WHERE b = ? AND a = ?
WHERE a = ? ORDER BY b DESC

索引(a,b)排序不能得出(b,a)排序,所以下面 SQL 不能被优化:


WHERE b = ?
WHERE b = ? ORDER BY a DESC

  1. 使用 组合索引 进行 索引覆盖

若查询的字段在二级索引的叶子节点中,则可直接返回结果,无需回表。这种通过组合索引避免回表的优化技术也称为索引覆盖(Covering Index)。


利用组合索引包含多个列的特性,可以实现索引覆盖技术,提升 SQL 的查询性能。

©著作权归作者所有:来自51CTO博客作者微客鸟窝的原创作品,如需转载,请注明出处,否则将追究法律责任
#yyds干货盘点#深入浅出,一文吃透mysql索引
https://blog.51cto.com/wekenw/4806866

#yyds干货盘点# 浅谈MySql事务与MVCC-多极客编程

事务是数据库最小工作单元,它的操作是不可以被分割的原子操作,是保证数据一致性的重要手段之一。本文将通过基本概念、特性、隔离级别以及实现原理,带领大家熟悉MySql中的事务。在MySql中,常见支持事务的存储引擎包括innoDB,NDB等,而另外一个常用的存储引擎MyISAM是不支持事务的,本文主要针对innoDB进行分析。 事务基础 首先来看一下事务的4大特性ACID: 原子性(Atomicity

MySQL之实例重启之前必做得几项工作-多极客编程

总结: 对陌生的数据库服务器重启之前必须要做的几项非常重要的参数确认和记录。第1.检查 MySQL服务的启动进程,确定采用的哪个my.cnf配置文件启动的服务。并且检查进程中显示的数据目录和my.cnf配置文件中指定的目录是否对应。特别是data目录  ibdata文件,binlog文件位置,binlog文件名称,buffer_pool值,log_slave_update等等 第2.重启前一定要登

#yyds干货盘点# MySQL性能优化(五):为什么查询速度这么慢-多极客编程

作者:xcbeyond博客:https://xcbeyond.cn/ 公众号:程序猿技术大咖 前面章节我们介绍了如何选择优化的数据类型、如何高效的使用索引,这些对于高性能的MySQL来说是必不可少的。但这些还完全不够,还需要合理的设计查询。如果查询写的很糟糕,即使表结构再合理、索引再合适,也是无法实现高性能的。 谈到MySQL性能优化,查询优化作为优化的源头,它也是最能体现一个系统是否更快。本章

#yyds干货盘点# MySQL性能优化(六):常见优化SQL的技巧-多极客编程

作者:xcbeyond博客:https://xcbeyond.cn/ 公众号:程序猿技术大咖 在面对不够优化、或者性能极差的SQL语句时,我们通常的想法是将重构这个SQL语句,让其查询的结果集和原来保持一样,并且希望SQL性能得以提升。而在重构SQL时,一般都有一定方法技巧可供参考,本文将介绍如何通过这些技巧方法来重构SQL。 一、分解SQL 有时候对于一个复杂SQL,我们首先想到的是是否需要将

HighgoDB查询慢SQL和阻塞SQL-多极客编程

目录环境文档用途详细信息环境系统平台:N/A版本:6.0,5.6.5,5.6.4,5.6.3,5.6.1,4.5.2,4.5,4.3.4.9,4.3.4.8,4.3.4.7,4.3.4.6,4.3.4.5,4.3.4.4,4.3.4.3,4.3.4.2,4.3.4,4.7.8,4.7.7,4.7.6,4.7.5,4.3.2文档用途本文介绍了如何对数据库日志进行分析,并获取执行慢的SQL语句。并提供

#yyds干货盘点#MySQL学习-索引的基础篇(下)-多极客编程

作者:汤圆 个人博客: javalover.cc 前言 前面介绍了索引的基础知识,包括索引的模型、分类、重建等等; 本篇我们再延伸一下索引的分类,比如覆盖索引、联合索引; 索引的分类从的方面来说,分为主键索引和非主键索引,像即将介绍的这些索引都属于非主键索引; 目录 覆盖索引 联合索引 最左前缀原则 索引下推 正文 1. 覆盖索引 定义:如果一条查询语句中,非主键索引覆盖了我们的查询需求,那么这

Postgres 国产化数据库道路之艰与ORACLE-多极客编程

随着去IOE逐年加深,数据库国产化已经是大势所趋。其中,国产化数据库中又以Postgresql数据库为原型二次开发的为甚,但是,国产化数据库要做到与其他种类的数据库如ORACLE数据库的完全适配,要做的工作真的很多,绝对不能仅仅是在原生态Postgresql基础之上加一个UI那么简单。近期在做国产数据库与oracle数据库的适配,过程中发现很多问题,这里仅仅对比oracle与Postgresql的

#yyds干货盘点# 浅谈MySql事务与MVCC-多极客编程

事务是数据库最小工作单元,它的操作是不可以被分割的原子操作,是保证数据一致性的重要手段之一。本文将通过基本概念、特性、隔离级别以及实现原理,带领大家熟悉MySql中的事务。在MySql中,常见支持事务的存储引擎包括innoDB,NDB等,而另外一个常用的存储引擎MyISAM是不支持事务的,本文主要针对innoDB进行分析。 事务基础 首先来看一下事务的4大特性ACID: 原子性(Atomicity

MySQL之实例重启之前必做得几项工作-多极客编程

总结: 对陌生的数据库服务器重启之前必须要做的几项非常重要的参数确认和记录。第1.检查 MySQL服务的启动进程,确定采用的哪个my.cnf配置文件启动的服务。并且检查进程中显示的数据目录和my.cnf配置文件中指定的目录是否对应。特别是data目录  ibdata文件,binlog文件位置,binlog文件名称,buffer_pool值,log_slave_update等等 第2.重启前一定要登

从一次 SQL 查询的全过程看 DolphinDB 的线程模型-多极客编程

分布式系统较为复杂,无论写入还是查询,都需要多个节点的配合才能完成操作。本教程以一个分布式 SQL 查询为例,介绍 DolphinDB 分布式数据库的数据流以及其中经历的各类线程池。通过了解 SQL 查询的全过程,也可以帮助我们更好地优化 DolpinDB 的配置和性能。1. DolphinDB 线程类型woker常规交互作业的工作线程,用于接收客户端请求,将任务分解为多个小任务,根据任务的粒度自

#私藏项目实操分享#云开发数据库有哪些常用操作-多极客编程

1、批量删除一个集合内的多条记录比如我们要删除集合为question的所有记录:db.collection('question') .where({ _id: _.exists(true) }) .remove()由于remove请求只支持通过匹配 where 语句来删除,我们可以在where里包含一个条件只要存在_id就删除,由于基本每个记录都有_id,所以就能都删除了。2、

#yyds干货盘点#sql server 常用函数基础实战系列(7)-多极客编程

sql server 常用函数基础实战系列之统计函数(聚合函数) - stdevp() - 求标准偏差StDev算出查询结果中指定字段所有值的标准差假设数据如下create table #tempTable_prize( id int IDENTITY (1,1) not null, prize_title varchar(50), --奖项标题 chall