mysql中主键索引和联合索引的原理与区别

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 Tair(兼容Redis),内存型 2GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。

索引是帮助MySQL高效获取数据的数据结构,本文主要介绍了mysql中主键索引和联合索引的原理与区别,具有一定的参考价值,感兴趣的可以了解一下

一、主键索引
主键索引:按照主键数据从小到大按照从左到右进行排序,叶节点只存储数据区;
接着将上面的页生成出来,页只存储索引和指针,指针指向数据域,当通过主键查找数据时,从B+树的头部开始寻址数据、读取数据。

上面为索引页
下面为数据页

查询select * from table where a=6,会从上到下走法,找索引

查询select * from table where a<6,会从上到下走法,找索引;
原因:先找a=6是走索引的,找到a=6数据后,将左边的数据全部返回即可。

为了方便把前面数据给你采用双向指针

查询select * from table where b=6,会从左到右走法,全表扫描,因为b未有命中索引。

二、什么是联合索引? 对应的B+树是如何生成的?
联合索引:将数据库表中多个字段组成一个索引。bcd联合索引;bcd三个字段进行排序

1、建立索引方式
create index idx_t1_bcd on t1(b,c,d);bcd三个字段进行排序

2、什么是最左前缀原则?
复合索引,也叫联合索引,用户可以在多个列上建立索引,这种索引叫做复合索引。
当我们创建一个组合索引的时候,如(k1,k2,k3),相当于创建了(k1)、
(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。

1

select * from table where k1=A and k2=B and k3=C

3、回表

select from t1 where b=1 and c=1 and d=1执行逻辑:
通过联合索引找到一条数据的3个字段,但是select
查找多个字段,所以需要存储主键字段,通过主键字段去主键索引里面找到完整的这条数据,并将这条数据返回。

通过主键再去主键索引里面找数据的过程叫做回表

4、为什么要遵守最左前缀原则才能利用到索引?
explain select * from t1 where c=1 and d=1 and b=1 ;
最左前缀原则和后面条件的顺序没有关系

不符合最左匹配原则:*11

符合最左匹配原则:1*1

b=1:走联合索引 (需要回表1次)

b>1:不走联合索引(因为需要回表很多次)

拿到主键会到主键索引里面拿数据(回表7次)

5、什么是覆盖索引?
查询的字段正好在联合索引字段里面,不需要回表

select b from t1 where b>1;(不需要回表了,直接从联合索引字段中将该字段取出来)

1

select a,b,c,d from t1 where b>1;

同样的也符合联合索引,也不需要回表,因为a字段(主键)在联合索引中。

1

select a,b,c,d,e from t1 where b>1;

不走联合索引,需要回表,因为e字段不在联合索引中,需要回表很多次

6、索引扫描底层原理
从bcd索引的叶子节点开始遍历
主键索引存储的是8条完整的数据
联合索引中存储的是8条不完整的数据,并且b字段在联合索引中,不会去回表到主键索引中。

从叶子节点开始扫描不需要符合最左匹配原则。

7、order by为什么会导致索引消失?
因为走全表扫描效率更高,不需要回表
走索引会回表多次,效率低

8、有哪些情况会导致索引失效?
1、索引列使用!=、not、is null、is not null查询的时候,由于索引数据的检索效率非常低,因此Mysql引擎会判断不走索引。

2、使用like通配符匹配后缀%xxx的时候,由于这种方式不符合索引的最左匹配原则,所以也不会走索引。

但是反过来,如果通配符匹配的是前缀xxx%,符合最左匹配,也会走索引。
3、对索引列上进行函数运算、导致mysql无法识别索引列,就不会走索引了。

4、使用or连接查询的时候,or语句前后没有同时使用索引,那么索引会失效。只有or左右查询字段都是索引列的时候,才会生效。

5、当索引列存在隐式转化的时候, 比如索引列是字符串类型,但是在sql查询中没有使用引号。

9、索引设计原则
查询更快、占用空间更小

1、选择合适的列作为索引

1.经常作为查询条件(where子句)、排序条件(order by子句) 分组条件(group by子句)的列建立索引。
2、区分度低的字段,例如性别,不要建立索引
3、更新频繁字段不适合创建索引
4、对于定义为text、image和bit的数据类型的列不要建立索引
5、定义有外键的数据列一定要建立索引。
6、数据量较小的表,索引效果较差,没有必要在此列建立索引
7、尽量使用扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
8、使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间,如果搜索词
超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。
2、避免使用过多的索引

1、每个索引都需要占用额外的磁盘空间
2、更新表(update、insert、delete操作时),所有的索引都需要被更新
3、维护索引需要成本

三、InnoDB 与MyISAM 的区别

聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。

非聚簇索引:叶子节点不存储数据、存储的是数据行地址,也就是说根据索引查找到数据行的位置再取磁盘查找数据,这个就有点类似一本树的目录,比如我们要找第三章第一节,那我们先在这个目录里面找,找到对应的页码后再去对应的页码看文章。

优势:
1、查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高
2、聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合,非聚簇索引不适合

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
13天前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
MySQL主从复制原理和使用
|
22小时前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志(Redo Log)和二进制日志(Binary Log)是两种重要的日志系统。重做日志主要用于保证事务的持久性和原子性,通过记录数据页的物理修改信息来恢复未提交的事务更改。二进制日志则记录了数据库的所有逻辑变化操作,用于数据的复制、恢复和审计。两者在写入时机、存储方式、配置参数和使用范围上有所不同,共同确保了数据库的稳定性和可靠性。
|
13天前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
6天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
34 0
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
31 0
|
8天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
18 0
|
25天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
102 6
|
22天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引
|
8天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
43 2

相关产品

  • 云数据库 RDS MySQL 版