MySQL数据库 InnoDB引擎索引原理与设计索引调优简述

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:   MySQL的InnoDB引擎比较常用,了解它的索引原理,才能在设计索引的时候得心应手,轻松应对数据库表的优化。  也叫聚簇索引。  聚集索引 !=主键索引;  任何表都必然会有聚集索引,而主键索引并非必然存在。

  MySQL的InnoDB引擎比较常用,了解它的索引原理,才能在设计索引的时候得心应手,轻松应对数据库表的优化。

  也叫聚簇索引。

  聚集索引 !=主键索引;

  任何表都必然会有聚集索引,而主键索引并非必然存在。

  存在主键时,聚集索引选择主键索引来建立 没有主键时,聚集索引选择在后面的第一个唯一索引的列建立 没有唯一索引时,创建隐含列row_id来建立聚集索引,row_id是6位的整型,不能被引用

  实践中,通常都会指定主键,所以它们通常是同一回事。

  所有索引都是 B+Tree 方式存储,高度维持在3~4层, 只有叶子节点存储数据(这里已然是存了每行的所有列的数据);

  聚集索引的叶子节点数据, 逻辑上按照索引列正序排序,物理上不连续 ;

  每个节点的大小等于内存中的一页的大小(页是计算机管理存储器的逻辑块,许多OS中,页的大小通常为4K),使得每次获取一个节点时只需要一次IO;

  一个节点内的数据不一定填满整个节点,如果需要插入数据时,可快速插入而不必分拆节点。

  叶子节点的数据即是表数据的实际存储位置 ,当不使用任何索引查询时,直接按原正序遍历数据。

  也叫二级索引。

  所有索引都是 B+Tree 方式存储,高度维持在3~4层,只有二手叶子节点存储数据,但 叶子节点只存了聚集索引的列(如果聚集索引是主键,那么就是存了主键id的值,否则就是隐含列row_id;后面为省事直接叫id,实际意义以此描述为准);

  可以将辅助索引理解为一个小表,数据列含有索引列及id。

  索引覆盖:

  查询的 列范围是本次查询使用的索引包含的列及id 时,即为索引覆盖,表示本次查询的列的数据可以直接从索引中获取,不需要回表查询;

  查询语句前加上 explain 分析时,extra 列的值是 using index 时就表示本次查询属于索引覆盖。

  回表:

  相反,查询的列范围超出本次查询使用的索引包含的列及id时, 其他列的数据无法在索引中确定,必须要回表获取 ——这种行为就是回表;

  查询语句前加上 explain 分析时,extra 列的值是 using index condition,表示本次查询使用了索引的结果作为条件再回表获取数据。

  回表时, 通过辅助索引存储的id,去聚集索引直接定位获取对应的数据 ,再取出对应的列。

  小结:

  如果能够 避免回表,有助于提升查询的速度 。

  对于使用 Redundant 或者 Compact 格式的 InnoDB 表,索引键前缀长度限制为767字节。如果 TEXT 或 VARCHAR 列的前缀索引超过191个字符,则可能会达到此限制(假定为 utf8mb4 字符集,每个字符最多4个字节)。

  设计表时, 尽量给出合理的长度 ,避免字段太长导致索引长度过长,进而影响索引的性能。

  因为索引虽然加快查询速度,但是过多的索引也会成为数据库的负担,毕竟索引也需要磁盘存储起来的。

  与之相对的是单列索引,即是只在一个列上加索引的情况;

  单列索引无法满足需求时,可通过多个字段创建复合索引,也叫组合索引,也有些人习惯性的叫联合索引等,叫法不重要,反正就那意思。

  复合索引遵循 最左匹配原则 ,就是说查询时,右边或中间的字段可以少,左边的不可以少,否则索引失效。

  设计复合索引时需要注意:

  选择性好的字段在前(即使用频繁的字段),反之在后 用于范围查询的字段在后,即使用 between、in、>、<、>=等的情况

  因为复合索引遵循最左匹配原则,否则索引失效,但在 MySQL8.0 之后对某些特殊情况做了优化,即:如果左边的列缺失,但是 左边列的唯一值少而右面列的唯一值多 时,数据库直接跳过左边的列进行索引扫描,而不是直接遍历表。

  跳跃索引是优化的结果,并不是必然存在(不符合条件就不存在),也不是存在过以后就一直存在(刚开始符合条件但后面数据的变化导致不符合条件,以后也不会存在),所以最好还是不要依赖数据库的优化,同时要认清MySQL数据库的版本。

  本文讲述了聚集索引与辅助索引的概念及其数据结构,基于 B+Tree 数据结构进一步说明了索引覆盖与回表是如何发生的,以及各种索引设计时需要注意的地方。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
219 100
MySQL底层概述—2.InnoDB磁盘结构
|
25天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
|
27天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
1月前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
1月前
|
存储 缓存 关系型数据库
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
1月前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
167 42
|
24天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
124 25
|
11天前
|
监控 关系型数据库 MySQL
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。
|
19天前
|
SQL 关系型数据库 MySQL
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。

热门文章

最新文章