【Java面试】四、MySQL篇(上)

news/2024/7/20 17:21:39/文章来源:https://blog.csdn.net/llg___/article/details/139139412

在这里插入图片描述

文章目录

  • 1、定位慢查询
  • 2、慢查询的原因分析
  • 3、索引
    • 3.1 数据结构选用:二叉树 & 红黑树
    • 3.2 数据结构选用:B+树
  • 4、聚簇索引、非聚簇索引、回表查询
    • 4.1 聚簇索引、非聚簇索引
    • 4.2 回表查询
  • 5、覆盖索引、超大分页优化
    • 5.1 覆盖索引
    • 5.2 超大分页处理
  • 6、索引的创建
  • 7、索引的失效
  • 8、SQL优化的经验
  • 9、面试

1、定位慢查询

  • Arthas在线查看方法耗时
  • 运维工具Prometheus
  • 链路追踪工具Skywalking、Zipkin、OpenTemplate

在这里插入图片描述

  • MySQL自带的慢日志:记录执行超过n秒的SQL
//修改配置文件,文件位置
/etc/my.cnf//开启慢查询开关,生产环境不建议开启,会损失部分性能
slow_query_log=1//设置超过2秒的SQL
long_query_time=2

慢SQL被记录到/var/lib/mysql/localhost-slow.log

在这里插入图片描述

2、慢查询的原因分析

慢SQL通常是因为:

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询

前三种,可尝试使用SQL执行计划分析原因:

# SELECT语句前添加EXPLAIN或DESC,查看SQL语句执行情况的信息
EXPLAIN select * from t_table;
DESC select * from t_table;

在这里插入图片描述
此时SELECT返回的不是表数据,是一些执行信息:

  • possible: key 当前sql可能会使用到的索引
  • key: 当前sql实际命中的索引
  • key_len: 索引占用的大小,key和key_len搭配,检查是否存在索引失效
  • Extra:额外的优化建议

在这里插入图片描述

  • type:这条sql的连接的类型,性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
system:查询MySQL系统内置库的表const:根据主键查询eq_re:主键索引查询或唯一索引查询ref:索引查询range:范围查询index:索引树扫描,遍历整个索引all:不走索引,全盘扫描

3、索引

一种用于高效查数据的数据结构,以某种方式指向表里的数据。如下表,不加索引,查age=45的数据,就是逐行对比 + 遍历整个表直至最后一行,效率低下

在这里插入图片描述

如果去维护一个类似二叉树的结构,再查age=45的数据,则直接从根节点开始⇒ 45 > 36,去右侧 ⇒ 45 < 48 ⇒去左侧 ⇒ 查找完毕,如此,查找效率提升,这即索引的思想

3.1 数据结构选用:二叉树 & 红黑树

MySQL索引底层的数据结构是B+树。不选二叉树是因为:

在这里插入图片描述
如果数据递增或递减,此时二叉树变链表,即最坏情况的二叉树效率很低。既然二叉树有平衡性问题,那再考虑自平衡的二叉树 ⇒ 红黑树

在这里插入图片描述

红黑树时间复杂度为O(log n),但其也是一个二叉树,每个节点最多只能两个分支,因此,大数据量下,红黑树会很高。 ⇒ B树,每个节点可以多个分支,是一种多叉路衡查找树。以一颗5阶B树为例(最大度数mas-degree为5,每个节点最多存储4个key)

在这里插入图片描述

图中的灰色部分,存储指针,指向子节点。如20左侧的指针,指向的就是20以内的数据,20和30之间的指针,则指向20~30之间的数据,以此类推。且绿色部分存储的是对应的那条数据。

3.2 数据结构选用:B+树

相比二叉树,B树是一种矮胖树,B+树则是B树的一种优化,非叶子节点只存储指针,不存储数据。只有在叶子节点才去存储对应的数据,前面的非叶子节点起一个导航的作用,非叶子节点上就匹配到的数据,在叶子节点上也能找到这个数。

在这里插入图片描述

MySQL默认的存储引擎InnoDB默认使用B+树实现索引。相比B树,B+树:

  • 磁盘读写代价更低(只有叶子节点存数据)
  • 查询效率更稳定(最后都要落到叶子节点)
  • 适合于区间查询(叶子节点之间的双向指针,比如查6~34这个区间的数,先从根节点对比,走左边,到16,再走左边,到6,再跟双向指针拿到6到34的数据,不需要再从根节点开始重新找一次

4、聚簇索引、非聚簇索引、回表查询

4.1 聚簇索引、非聚簇索引

聚簇索引(又叫聚集索引),即B+树的叶子节点保存的是整行数据。非聚簇索引(又叫二级索引),即B+树单独叶子节点存储的是那行数据对应的主键

在这里插入图片描述
聚簇索引选取规则:(节点里存哪个)

  • 如果存在主键,主键索引就是聚集索引
  • 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
  • 如果表没有主键,或没有合适的唯一索引,则InnoDB 会自动生成一个rowid 作为隐藏的聚集索引

如下:建立聚簇索引时,这张表有主键ID,因此,节点中存储的是ID值,最后,叶子节点中存的那个row是整条数据值。

在这里插入图片描述

再比如给表的name字段建立非聚簇索引,节点存储name的值,最后的叶子节点,存储的是这条数据的主键值

4.2 回表查询

select * form user where name = 'Arm';

给name字段加了非聚簇索引,因此,执行如上SQL,先根据name的非聚簇索引的B+树 ⇒ A小于L,走左边,到G和J,再走左边,找到Arm ⇒ 因为是select *,而非聚簇索引的叶子节点存的是主键 ⇒ 拿着主键,回到聚簇索引,从其根节点开始查 ⇒ 聚簇索引的叶子节点存了整行数据,返回select * 的结果

在这里插入图片描述

总之,回表查询就是:先根据非聚簇索引找到主键值,再根据主键值到聚簇索引拿到整行数据

5、覆盖索引、超大分页优化

5.1 覆盖索引

即查询使用了索引,并且你需要返回的字段,在索引中能够全部找到。

在这里插入图片描述

select * form tb_user where id = 1;

是覆盖索引,虽然select * ,但其where是根据id过滤的,即用的是主键索引、聚簇索引,索引的叶子节点存了整行数据,需要返回的字段,在索引中能够全部找到

在这里插入图片描述

select id, name from tb_user where name = 'Arm';

是覆盖索引,where根据name过滤,走name的非聚簇索引,最后叶子节点存了id,而最后需要返回的就是id和name

在这里插入图片描述

select id, name, gender from tb_user where name = 'Arm';

不是覆盖索引,索引中拿不到gender值,需要回表查询

在这里插入图片描述

很明显,能一次查询出来的,符合覆盖索引的,效率最高,走回表查询的SQL,效率低

5.2 超大分页处理

使用limit分页查,需要对数据进行排序,数据量很大时,效率很低

在这里插入图片描述

比如,limit 900 0000,10,此时,需要排序前9000010行数据,再返回9000000到9000010行这10行:

在这里插入图片描述

解决方案是:覆盖索引 + 子查询

在这里插入图片描述
即先根据主键去分页order by id ,不select *,而是select id,再和原来的表关联查

6、索引的创建

需要创建索引的场景:

  • 数据量大(单表超过10万行),且查询频繁
  • 给常作为where、order by、group by操作的字段创建索引
  • 如果字段是字符串类型,且长度很长,给其建立索引压力大,可截取前几个字,建立前缀索引

在这里插入图片描述

  • 多用联合索引,而不是单列索引。因为如果给A + B两个字段建立了联合索引,刚好又select A, B from table where A = 1;就是覆盖索引,避免了回表,查询效率更高。下图即给name、status、address三个字段建了联合索引

在这里插入图片描述

  • 索引并不是越多越好,因为增删改也要同步去维护索引,索引多了,会影响增删改的效率

7、索引的失效

给表tb_seller的name,status,address字段创建联合索引:

在这里插入图片描述
索引失效的场景:

1)违反最左前缀法则

最左前缀法则,即select后面的字段,必须从索引的最左前列开始,并且不跳过索引中的列。以下为索引不失效的写法:

在这里插入图片描述

以下写法索引失效:
在这里插入图片描述
以下写法,中途跳过了联合索引的某一列,只有最左侧字段索引生效,从key_len的大小可以看出,其只命中了一个字段:

在这里插入图片描述

2)对status范围查询,则status右边的列address没有用到索引,但name,status还是走了索引了

在这里插入图片描述

3)在索引所在的列上进行运算,索引会失效

在这里插入图片描述

4)字符串不加单引号,索引失效

因为不对字符串类型加单引号,MySQL优化器会自动进行类型转换,造成索引失效
在这里插入图片描述

5)以%开头的Like模糊查询,索引失效

注意:如果仅仅是末尾进行模糊查询,索引不会失效

在这里插入图片描述

8、SQL优化的经验

1)表设计优化:

  • 设置合适的数值类型:tinyint、int、bigint
  • 字符串类型,char和varchar,char定长、效率高,varchar长度灵活可变,根据字符串实际长度来,但效率稍低

在这里插入图片描述

2)SQL语句优化

  • 避免select *
  • 避免索引失效的写法
  • 使用union all代替union,union会把两个查询的结果再做个去重

在这里插入图片描述

  • 避免where中对字段进行计算操作
  • join表时,能用inner join,不left join或者right join,业务必须要用时,可将小表(行数少的表)放外面。原因参考for循环嵌套,如下写法,MySQL进行三次连接,每次连接进行1000次操作,反之就是进行1000次连接,每次连接进行3次操作(inner join 就会自动优化,把小表放外面。left join或right join就不会把小表放外面)

在这里插入图片描述
3)读写分离,主从复制

  • 用于避免写操作影响查询效率
  • 主库写,从库读

在这里插入图片描述
4)索引的创建和失效
5)分库分表(见下篇)

9、面试

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_1052799.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

存储+调优:存储-memcached

存储调优&#xff1a;存储-memcached 什么是memcached? 高性能的分布式内存缓存服务器。通过缓存数据库的查询结果&#xff0c;减少数据库访问次数&#xff0c;以提高动态Web应用的速度、提高可扩展性。 在memcached中存什么&#xff1f; 尽快被保存 访问频率高 1.数据保…

【Unity】Unity项目转抖音小游戏(三)资源分包,抖音云CDN

业务需求&#xff0c;开始接触一下抖音小游戏相关的内容&#xff0c;开发过程中记录一下流程。 使用资源分包可以优化游戏启动速度&#xff0c;是抖音小游戏推荐的一种方式&#xff0c;抖音云也提供存放资源的CDN服务 抖音云官方文档&#xff1a;https://developer.open-douyi…

R可视化:另类的箱线图

介绍 方格状态的箱线图 加载R包 knitr::opts_chunk$set(echo TRUE, message FALSE, warning FALSE) library(patternplot) library(png) library(ggplot2) library(gridExtra)rm(list ls()) options(stringsAsFactors F)导入数据 data <- read.csv(system.file(&qu…

雷达基数据绘制成雷达图

x波段雷达基数据绘制成雷达图 1.雷达基数据格式Z_RADR_I_ZR001_20240521020002_O_DOR_YLD2-D_CAP_FMT.bin.bz2 2.基数据读取 python f StandardData(i) # 新版本标准数据radarTime f.scantime # 获取雷达时次date_str radarTime.strftime(%Y-%m-%d %H:%M:%S)date_str d…

C#【进阶】排序进阶

排序进阶 文章目录 插入排序希尔排序归并排序快速排序堆排序 插入排序 #region 知识点一 插入排序的基本原理 // 8 7 1 5 4 2 6 3 9 // 两个区域 // 排序区 // 未排序区 // 用一个索引值做分水岭// 未排序区元素 // 与排序区元素比较 // 插入到合适位置 // 直到未排序区清空 #e…

Docker(三) 容器管理

1 容器管理概述 Docker 的容器管理可以通过 Docker CLI 命令行工具来完成。Docker 提供了丰富的命令&#xff0c;用于管理容器的创建、启动、停止、删除、暂停、恢复等操作。 以下是一些常用的 Docker 容器命令&#xff1a; 1、docker run&#xff1a;用于创建并启动一个容器。…

Qt教程3-Ubuntu(x86_64)上配置arm64(aarch64)交叉编译环境及QT编译arm64架构工程

汇创慧玩 写在前面1. 查看系统架构相关指令2. ARM64交叉编译器环境搭建3. Qt编译arm64环境搭建4. 配置 Qt的本地aarch64交叉编译器5. 工程建立及编译验证 写在前面 苦辣酸甜时光八载&#xff0c;春夏秋冬志此一生 Qt简介&#xff1a; Qt&#xff08;官方发音 [kju:t]&#xff…

Spring Boot集成六大常用中间件,附集成源码,亲测有效

目录 万字论文&#xff0c;从0到1&#xff0c;只需1小时获取途径1、Spring Boot如何集成Spring Data JPA&#xff1f;2、Spring Boot如何集成Spring Security&#xff1f;3、Spring Boot如何集成Redis&#xff1f;4、Spring Boot如何集成RabbitMQ&#xff1f;5、Spring Boot如何…

【C++】C++11(一)

C11是一次里程碑式的更新&#xff0c;我们一起来看一看~ 目录 列表初始化&#xff1a;{ }初始化&#xff1a;std::initializer_list&#xff1a; 声明&#xff1a;auto&#xff1a;decltype&#xff1a; STL的一些变化&#xff1a; 列表初始化&#xff1a; { }初始化&#xf…

云计算期末复习(2)

MapReduce 包含Google MapReduce基本构架、Hadoop MapReduce基本构架 作业&#xff08;问答题&#xff09; &#xff08;1&#xff09;预习论文The Google File System&#xff0c;总结和分析GFS主要特点。 GFS的主要特点包括&#xff1a; 1. 高可靠性和容错性&#xff1a;G…

政府鼓励社会力量建设气膜体育场馆—轻空间

2023年12月1日&#xff0c;国家体育安全总局发布的《关于政协第十四届全国委员会第一次会议第00374号&#xff08;文体宣传类020号&#xff09;提案答复的函》中指出&#xff0c;2016年和2020年国务院发布的文件中均涉及推动气膜场馆建设及完善装配式建筑相关政策。下一步&…

炸裂!AI五分钟模仿爆款IP故事,涨粉速度太绝了!

‍ ‍大家好&#xff0c;我是向阳。 今天我要分享一个利用AI技术模仿爆款账号的小技巧&#xff0c;帮助大家迅速增加粉丝。这个方法简单实用&#xff0c;尤其适用于副业和本地生活领域。接下来&#xff0c;我将为大家详细讲解操作步骤。让我们开始吧。 副业赚钱&#xff1a;模…

2024年03月 Python(四级)真题解析#中国电子学会#全国青少年软件编程等级考试

Python等级考试(1~6级)全部真题・点这里 一、单选题(共25题,共50分) 第1题 运行如下代码,若输入整数3,则最终输出的结果为?( ) def f(x):if x==1:s=1else:s

基于 IP 的 DDOS 攻击实验

介绍 基于IP的分布式拒绝服务&#xff08;Distributed Denial of Service, DDoS&#xff09;攻击是一种利用大量受控设备&#xff08;通常是僵尸网络&#xff09;向目标系统发送大量请求或数据包&#xff0c;以耗尽目标系统的资源&#xff0c;导致其无法正常提供服务的攻击方式…

[集群聊天服务器]----(十)Nginx的tcp负载均衡配置--附带截图

接着上文&#xff0c;我们剖析了服务端和客户端的代码&#xff0c;但是单台服务器的并发量是有限的&#xff0c;面对并发量的要求&#xff0c;我们就需要引入Nginx来实现并发量的要求&#xff0c;将用户请求分发到不同的服务器上分担压力&#xff0c;这就是负载均衡。 选择负…

【国信华源:以专业服务,协助水利厅抵御强暴雨】

5月18日-19日&#xff0c;广西出现入汛以来最强暴雨天气过程&#xff0c;钦州、防城港、北海、南宁等地出现特大暴雨&#xff0c;多地打破降雨量极值。国信华源技术团队积极行动驻守一线&#xff0c;为打好山洪灾害防御的提前战、主动战提供了技术支撑。 5月17日18时&#xff0…

在某云服务器上搭建公网kali linux2.0

前提&#xff1a; 可用的 CVM 实例 挂载一个系统盘之外的盘&#xff0c;安装完成后可卸载&#xff01; 创建实例&#xff0c;安装centos7系统&#xff01; 然后执行fdisk -l看磁盘的情况 在这里我将把镜像写入vdb这块数据盘 非 root 的情况下记得sudo执行以下命令 注意&…

重新安装 Windows 10/11 后如何恢复丢失的数据?

“嗨&#xff0c;我的 Windows 10/11 崩溃了&#xff0c;所以我不得不重新安装它。我使用 USB 可启动驱动器重新安装了操作系统。但是&#xff0c;重新安装后&#xff0c;C 盘上的所有先前文件都丢失了。有什么方法可以恢复丢失的文件吗&#xff1f;” - 孙雯 在大多数情况下&…

Android --- 交换两个布局

准备布局 exchange_out_layout exchange_in_layout <?xml version"1.0" encoding"utf-8"?> <LinearLayout xmlns:android"http://schemas.android.com/apk/res/android"xmlns:tools"http://schemas.android.com/tools"andr…

【讲解下Web前端三大主流的框架】

&#x1f3a5;博主&#xff1a;程序员不想YY啊 &#x1f4ab;CSDN优质创作者&#xff0c;CSDN实力新星&#xff0c;CSDN博客专家 &#x1f917;点赞&#x1f388;收藏⭐再看&#x1f4ab;养成习惯 ✨希望本文对您有所裨益&#xff0c;如有不足之处&#xff0c;欢迎在评论区提出…