mysql——索引

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云解析 DNS,旗舰版 1个月
简介: 在5.7+版本 innodb引擎下,可以总结:索引即数据,数据即索引。

在我们创建表的时候,会生成一个B+tree 数据结构,用以承载我们不断新增的数据。

聚簇索引示意图:

image-20220126110940966.png

目录页记录下一级所有页的主键最大值,而数据页会在最底层形成一个双向链表。这样就保证在对主键精确查找与范围查找时,会快速的查找到结果集。

执行过程:where id>=1 and id <=9

1、解析:MySQL解析器会将sql的查询条件解析成一个区间 id in [1,9]。

2、根据区间最小值 id=1条件,查询页码9的数据9,18。其中1<9,所以继续查找页码4的数据 3、6、9。1<3,所以定位到数据页 1。最大值9同理会定位到数据页3。

3、由于数据页时双向链表结构,所以很容易获取出[1,9]区间的数据

二级索引:

image-20220126171357827.png

二级索引中目录页与聚簇索引基本保持一直,由页码与索引列组成。数据页由页码、索引列加主键组成。

执行过程:where name=a;

1、解析:MYSQL解析器会将sql的查询条件解析为 name=[a];

2、根据name=a;条件,查询页码4 ,a<c 所以查询页码1的数据

3、在页码1查询到 索引为a的数据,得到主键。

4、回表:将获取到的主键值 在聚簇索引查询。注:如果查询的字段仅为索引列于主键,是不用回表的

联合索引:

将多个字段联合一起创建索引,结构与二级索引类似,唯一变化就是在 目录页与数据页中的索引字段由一个变成多个。

**选择什么样的字段创建索引:**

我觉得这是一个开放性的话题,不过话题永远会落到 “热数据”、“重复率”等等,却总是感觉不清晰。

最适合创建索引的字段就是主键,但是主键出生就有索引,不用我们去创建。但是可以依照主键的特性找到创建索引的条件。

1、访问率高,如果主键的访问率不高那只能说它不是主键

2、重复率低:主键是重复率低的极致表现,已经存在唯一性

3、容易排序:创建索引时,是需要索引字段进行排序。当然进入数据库的数据都可排序,但是像主键自增的数字还是最直接、快捷的。

4、占用存储空间小:如果对text文本进行索引,它的存储量也许就能达到或者超过1M,但是正常数据交互量才16K(页:一般默认情况下),这样会造成多个页来存储一个字段,而且还在目录页、数据页冗余式储存。不提浪费存储空间,IO消费就能让这个索引失去价值。

索引带来的坏影响:

1、消耗大量储存空间,多创建一个索引就等于多一份数据冗余。空间消耗难免的

2、写入的性能变差:在进行新增、修改、删除操作时,都会伴随着数据页、目录页的变化。聚簇改变之后还要同步其他的索引树,性能肯定也有影响

3、查询性能不一定有提升,有”回表“的可能性,相当于遍历了两颗索引树。还有其他情况:例如sql语句原因无法使索引产生作用。。。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
25天前
|
存储 NoSQL 关系型数据库
为什么MySQL不使用红黑树做索引
本文详细探讨了MySQL索引机制,解释了为何添加索引能提升查询效率。索引如同数据库的“目录”,在数据量庞大时提高查询速度。文中介绍了常见索引数据结构:哈希表、有序数组和搜索树(包括二叉树、平衡二叉树、红黑树、B-树和B+树)。重点分析了B+树在MyISAM和InnoDB引擎中的应用,并讨论了聚簇索引、非聚簇索引、联合索引及最左前缀原则。最后,还介绍了LSM-Tree在高频写入场景下的优势。通过对比多种数据结构,帮助理解不同场景下的索引选择。
60 6
|
21天前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
|
16天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
50 3
Mysql(4)—数据库索引
|
9天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
31 1
|
25天前
|
监控 关系型数据库 MySQL
MySQL数据表索引命名规范
MySQL数据表索引命名规范
28 1
|
25天前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
1月前
|
存储 关系型数据库 MySQL
MySQL中的索引及怎么使用
综上所述,MySQL索引的正确使用是数据库性能调优的关键一环。通过合理设计索引结构,结合业务需求和数据特性,可以有效提升数据库查询响应速度,降低系统资源消耗,从而确保应用的高效运行。
54 1
|
11天前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
24 0
|
2月前
|
关系型数据库 MySQL 数据库
MySQL删除全局唯一索引unique
这篇文章介绍了如何在MySQL数据库中删除全局唯一的索引(unique index),包括查看索引、删除索引的方法和确认删除后的状态。
79 9