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
目录
相关文章
|
12天前
|
自然语言处理 搜索推荐 关系型数据库
MySQL实现文档全文搜索,分词匹配多段落重排展示,知识库搜索原理分享
本文介绍了在文档管理系统中实现高效全文搜索的方案。为解决原有ES搜索引擎私有化部署复杂、运维成本高的问题,我们转而使用MySQL实现搜索功能。通过对用户输入预处理、数据库模糊匹配、结果分段与关键字标红等步骤,实现了精准且高效的搜索效果。目前方案适用于中小企业,未来将根据需求优化并可能重新引入专业搜索引擎以提升性能。
|
25天前
|
关系型数据库 MySQL 数据库
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
84 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
|
20天前
|
缓存 NoSQL Redis
Redis原理—2.单机数据库的实现
本文概述了Redis数据库的核心结构和操作机制。
Redis原理—2.单机数据库的实现
|
1月前
|
存储 关系型数据库 MySQL
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
|
1月前
|
SQL 监控 关系型数据库
MySQL原理简介—12.MySQL主从同步
本文介绍了四种为MySQL搭建主从复制架构的方法:异步复制、半同步复制、GTID复制和并行复制。异步复制通过配置主库和从库实现简单的主从架构,但存在数据丢失风险;半同步复制确保日志复制到从库后再提交事务,提高了数据安全性;GTID复制简化了配置过程,增强了复制的可靠性和管理性;并行复制通过多线程技术降低主从同步延迟,保证数据一致性。此外,还讨论了如何使用工具监控主从延迟及应对策略,如强制读主库以确保即时读取最新数据。
MySQL原理简介—12.MySQL主从同步
|
1月前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
1月前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
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 的详细步骤,希望对您有所帮助。
173 42
|
3天前
|
SQL 关系型数据库 MySQL
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。