MySQL 索引数据结构

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: mysql 中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的 sql 必须等我对这条数据操作完了,才能对这条数据进行操作。

为什么是 B + 树

如果是用 AVL 平衡二叉树,树高度太高,索引查询需要访问磁盘;
每次访问以节点为单位进行磁盘 I/O ,需要尽量减少数据读取的 I/O 操作;
所以树高度一定不能太高,存储千万级别的数据,实践中 B+ 树的高度也就 4 或者 5。

B + 树经常用来比较的是 B 树;
B + 树的特点是所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;
对于范围查找,比如 15~50,B 树需要中序遍历二叉树,但是 B + 树直接在叶子节点顺序访问就可以了。

最左匹配原则

最左前缀匹配原则:在 MySQL 建立联合索引时会遵守最左前缀匹配原则;
即最左优先,在检索数据时从联合索引的最左边开始匹配。

索引的底层是一颗 B + 树,那么联合索引的底层也就是一颗 B + 树;
只不过联合索引的 B + 树节点中存储的是逗号分隔的多个值。

举例:创建一个 index_magor_class (magor,class) 的联合索引;
它是先根据 magor 排序,再根据 class 排序,如果索引后面还有字段,继续以此类推。

我们查询的 where 条件如果只传入了班级,是走不到联合索引的;
但是如果只传了学院编号,是可能会走到联合索引的。
MYSQL 的执行计划和查询的实际执行过程并不完全吻合;
比如你数据库数据量很少,可能直接全量遍历速度更快,就不走索引了。

在建表的时候如何设计索引

1、利用覆盖索引来进行查询操作,来避免回表操作。

比如我们表主键索引是学号,另外我们还根据手机号也建了索引,如果我们 where 条件是手机号
如果我们 select 获取的字段是学号,直接在手机号的索引表就能获取到数据,不需要回表;
如果我们 select 的时候还有其他字段,我们查询的时候流程是这样的,先根据手机号查到学号;
再根据学号去主键索引表查询数据,这个过程叫回表。

2、业务上具有唯一特性的字段,即使是组合字段,也建议建成唯一索引。
即使在应用层做了非常完善的校验和控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。

3、超过三个表禁止 join。
需要 join 的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引。

4、在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引。
索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上
可以使用 count (distinct left (列名,索引长度))/count (*) 的区分度来确定。

5、页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

6、索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

7、建组合索引的时候,区分度最高的在最左边。

8、防止因字段类型不同造成的隐式转换,导致索引失效。

MyBatis一二级缓存

Mybatis 的一级缓存是指 SQLSession,
一级缓存的作用域是 SQlSession, Mabits 默认开启一级缓存。
在同一个 SqlSession 中,执行相同的 SQL 查询时;
第一次会去查询数据库,并写在缓存中,第二次会直接从缓存中取。
当执行 SQL 时候两次查询中间发生了增删改的操作,则 SQLSession 的缓存会被清空。
每次查询会先去缓存中找,如果找不到,再去数据库查询,然后把结果写到缓存中。
Mybatis 的内部缓存使用一个 HashMap,key 为 hashcode+statementId+sql 语句。
Value 为查询出来的结果集映射成的 java 对象。
SqlSession 执行 insert、update、delete 等操作 commit 后会清空该 SQLSession 缓存。

二级缓存是 mapper 级别的,Mybatis 默认是没有开启二级缓存的。
第一次调用 mapper 下的 SQL 去查询用户的信息,查询到的信息会存放在该 mapper 对应的二级缓存区域。
第二次调用 namespace 下的 mapper 映射文件中,相同的 sql 去查询用户信息,会去对应的二级缓存内取结果。

MySQL 主从同步

Master 数据库只要发生变化,立马记录到 Binary log 日志文件中
Slave 数据库启动一个 I/O thread 连接 Master 数据库,请求 Master 变化的二进制日志
Slave I/O 获取到的二进制日志,保存到自己的 Relay log 日志文件中。
Slave 有一个 SQL thread 定时检查 Realy log 是否变化,变化那么就更新数据

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
MySQL索引与事务
MySQL索引与事务
|
4天前
|
监控 关系型数据库 MySQL
MySQL创建索引的注意事项
在索引的世界中,权衡是关键。权衡读写性能,权衡索引的数量和类型,权衡查询的频率和数据分布。通过谨慎的设计、定期的维护和持续的监控,我们能够确保索引在数据库中的角色得到最大的发挥,为应用提供更加高效和可靠的数据访问服务。在数据库优化的旅途中,索引是我们的得力助手,正确使用它将使数据库系统更具竞争力和可维护性。
10 0
|
6天前
|
存储 NoSQL 关系型数据库
索引的三种常见底层数据结构以及优缺点
索引的三种常见底层数据结构以及优缺点
|
14天前
|
SQL 存储 关系型数据库
MySQL索引(二)索引优化方案有哪些
MySQL索引(二)索引优化方案有哪些
30 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL - 聚簇索引和非聚簇索引
MySQL - 聚簇索引和非聚簇索引
|
1月前
|
存储 SQL 关系型数据库
MySQL - 深入解析MySQL索引数据结构
MySQL - 深入解析MySQL索引数据结构
|
20小时前
|
存储 关系型数据库 MySQL
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
7 0
|
1天前
|
存储 搜索推荐 关系型数据库
深度探讨数据库索引的数据结构及优化策略
深度探讨数据库索引的数据结构及优化策略
|
4天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
43 0
|
5天前
|
SQL 关系型数据库 MySQL
京东三面:什么情况会导致 MySQL 索引失效?
为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。
19 0

热门文章

最新文章