MYSQL篇--sql优化高频面试题

sql优化

1 如何定位及优化SQL语句的性能问题?创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

其实对于性能比较低的sql语句定位,最重要的也是最有效的方法其实还是看sql的执行计划,而对于mysql来说 它其实也是提供了explain这样的命令可以便于查询sql的执行计划,并且通过执行计划 我们能够看到sql的执行情况,包括是否使用索引,使用了什么样的索引,以及使用索引的一些相关信息
对于执行计划来说 它里面有几个非常关键的字段,
比如说有key字段,这个字段就表示是否用了索引,如果没用索引,key字段就为null;
同时还有type字段 它表示使用索引的类型,索引的效果从差到好一般是全表索引,–index全索引树扫描,–》range范围查询–》ref(使用非唯一索引进行查找数据)–》eq-ref(使用主键索引或者唯一索引关联等)
possible key 可能使用到的索引
key length 索引的长度
extra信息,比如说有 using index,using where

2 大表数据的查询如何进行优化?

1 首先对于大表数据,第一个思路还是说优化sql+去使用索引
2. 使用缓存–如果说已经优化了sql,还可以通过使用缓存,将一些不会发生变化的比如配置信息,历史数据信息放到缓存redis中去
3. 其次还可以做主从复制,读写分离,将大量的查询操作通过读库完成
4. 做垂直拆分,也就是按照模块之间的耦合度将系统和数据拆分成更细粒度
5. 做水平拆分, 这一步就需要选择一个合适的sharing key,同时为了有更好的查询效率,表结构也要有改动,应用也要改动,注意sql中尽量带sharding key,将数据定位到限定的表上去查,而不是扫描全部的表

3 关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?

其实在业务系统的开发中 我除了使用主键进行查询以外,别的其实都是会在测试库上查看对应的耗时和执行效率
而我们系统的慢查询统计都是运维在做的,他们会通过邮件或者短信电话等方式推送和反馈给我们

针对于慢查询的sql分析,我们一般的操作其实是从三方面入手,就是明确慢查询的原因到底是什么?是没有走索引?还是load了过多不需要的数据,还是表的数据量过大导致的

而这三个方向 也有对应的处理方式
1 首先我们拿到sql会看下当前load的数据中有没有多余字段,如果说是因为load了多余的行导致的查询过慢 我们就优化sql,进行重写
2 其次看下有没有走索引,就是通过分析sql的执行计划,获取索引的使用情况,如果说没有走索引,就修改语句,尽量去命中索引
3 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表。

4 如何去优化WHERE子句 ?

对于此类考题,先说明如何定位低效SQL语句,然后根据SQL语句可能低效的原因做排查,先从索引着手,如果索引没有问题,考虑以上几个方面,数据访问的问题,长难查询句的问题还是一些特定类型优化的问题,逐一回答。

SQL语句优化的一些方法如下:
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
1 select id from t where num is null ‐‐ 可以在num上设置默认值0,确保表中num列,没有null值,然后这样查询:select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
1 select id from t where num=10 or num=20 ‐‐ 可以这样查询:select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用,否则会导致全表扫描,如:
1 select id from t where num in(1,2,3) ‐‐ 对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描:select id from t where name like ‘%
李%’若要提高效率,可以考虑全文检索。

7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法
作为索引选择的输入项。如下面语句将进行全表扫描:
1 select id from t where num=@num ‐‐可以改为强制查询使用索引:select id from t with (index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
1 select id from t where num/2=100 ‐‐ 应改为:select id from t where
num=100*2

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
1 select id from t where substring(name,1,3)=’abc’ ‐‐ name以abc开头的id应改
为: select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

5 MySQL数据库cpu飙升到500%的话他怎么处理?

当 cpu 飙升到 500%时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。
如果是 mysqld 造成的, show processlist,看看里面跑的 session 情况,是不是有消耗资源的 sql 在运行。找出消耗高的 sql,看看执行计划是否准确,
index-索引 是否缺失,或者实在是数据量太大造成。
一般来说,肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降),等进行相应的调整(比如说加索引、改 sql、改内存参数)之后,再重新跑这些 SQL。
也有可能是每个 sql 消耗资源并不多,但是突然之间,有大量的 session 连进来导致 cpu 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等

6 大表怎么优化?某个表有近千万数据,CRUD比较慢,如何优化?分库分表了是怎么做的?分表分库了有什么问题?有用到中间件么?他们的原理知道么?

当MySQL单表记录数过大时,数据库的CRUD性能会明显下降,一些常见的优化措施如下:

  1. 限定数据的范围: 务必禁止不带任何限制数据范围条件的查询语句。比
    如:我们当用户在查询订单历史的时候,我们可以控制在一个月的范围
    内。;
  2. 读/写分离: 经典的数据库拆分方案,主库负责写,从库负责读;
  3. 缓存: 使用MySQL的缓存,另外对重量级、更新少的数据可以考虑使用应用级别的缓存;

还有就是通过分库分表的方式进行优化,主要有垂直分表和水平分表

  1. 垂直分区:
    根据数据库里面数据表的相关性进行拆分。 例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。
    简单来说垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。
    如下图所示,这样来说大家应该就更容易理解了。
    img

垂直拆分的优点: 可以使得行数据变小,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。

垂直拆分的缺点: 主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。此外,垂直分区会让事务变得更加复杂;

垂直分表
把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中
在这里插入图片描述

适用场景
1、如果一个表中某些列常用,另外一些列不常用
2、可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数

缺点
有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差
对于应用层来说,逻辑算法增加开发成本
管理冗余列,查询所有数据需要join操作

  1. 水平分区:
    保持数据表结构不变,通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中,达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。举个例子:我们可以将用户信息表拆分成多个用户信息表,这样就可以避免单一表数据量过大对性能造成影响。
在这里插入图片描述

数据库水平拆分
水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题,但由于表的数据还是在同一台机器上,其实对于提升MySQL并发能力没有什么意义,所以 水平拆分最好分库 。
水平拆分能够 支持非常大的数据量存储,应用端改造也少,但 分片事务难以解决 ,跨界点Join性能较差,逻辑复杂。

《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片,因为拆分会带来逻辑、部署、运维的各种复杂度 ,一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择客户端分片架构,这样可以减少一次和中间件的网络I/O。

水平分表:
表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询次数
在这里插入图片描述

适用场景
1、表中的数据本身就有独立性,例如表中分表记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用。
2、需要把数据存放在多个介质上。

水平切分的缺点
1、给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION操作
2、在许多数据库应用中,这种复杂度会超过它带来的优点,查询时会增加读一个索引层的磁盘次数

下面补充一下数据库分片的两种常见方案:
客户端代理:分片逻辑在应用端,封装在jar包中,通
过修改或者封装JDBC层来实现。 当当网的 ShardingJDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理:在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现。

分库分表后面临的问题
事务支持
分库分表后,就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务,将付出高昂的性能代价; 如果由应用程
序去协助控制,形成程序逻辑上的事务,又会造成编程方面的负担。

跨库join
只要是进行切分,跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题
这些是一类问题,因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。
解决方案:与解决跨节点join问题的类似,分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行,因此很多时候它的速度要比单一大表快很多。但如果结果集很大,对应用程序内存的消耗是一个问题

数据迁移,容量规划,扩容等问题
来自淘宝综合业务平台团队,它利用对2的倍数取余具有向前兼容的特性(如对4取余得1的数对2取余也是1)来分配数据,避免了行级别的数据迁移,但是依然需要进行表级别的迁移,同时对扩容规模和分表数量都有限制。总得来说,这些方案都不是十分的理想,多多少少都存在一些缺点,这也从一个侧面反映出了
Sharding扩容的难度。

ID问题
一旦数据库被切分到多个物理结点上,我们将不能再依赖数据库自身
的主键生成机制。一方面,某个分区数据库自生成的ID无法保证在全局上是唯一的;
另一方面,应用程序在插入数据之前需要先获得ID,以便进行SQL路由.

一些常见的主键生成策略
UUID 使用UUID作主键是最简单的方案,但是缺点也是非常明显的。由于
UUID非常的长,除占用大量存储空间外,最主要的问题是在索引上,在建立索引和基于索引进行查询时都存在性能问题。 Twitter的分布式自增ID算法
Snowflake 在分布式系统中,需要生成全局UID的场合还是比较多的,twitter的snowflake解决了这种需求,实现也还是很简单的,除去配置信息,核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页
般来讲,分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候,我们通过分片规则可以比较容易定位到指定的分片,而当排序字段非分片字段的时候,情况就会变得比较复杂了。为了最终结果的准确性,我们需要在不同的分片节点中将数据进行排序并返回,并将不同分片返回的结果集进行汇总和再次排序,最后再返回给用户。
在这里插入图片描述

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

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

相关文章

dhcp 时间同步 详细介绍

装服务程序步骤 1.如果有默认配置 请先备份 再进行修改 2.修改完配置文件 请重启服务或重新加载配置文件 否则不生效 注意&#xff1a;有的软件 安装包的名字和 系统里服务程序的名字不一样 htttp httpd openssh-server ssh 高阶级改防火墙 一&#xff0c; dhcp自动分配IP地…

适合PC端的7款最佳时间规划、项目管理软件

分享PC端7类主流的时间管理规划软件&#xff1a;PingCode、Worktile、Todoist、Pomodoro Timer 、Toggl等。 一、时间管理软件的类型 时间管理软件可以根据其功能和应用场景被划分为几种不同的类型。每种类型的软件都旨在帮助用户以不同的方式更有效地管理和分配他们的时间。以…

构建基于RHEL8系列(CentOS8,AlmaLinux8,RockyLinux8等)的支持63个常见模块的PHP8.1.20的RPM包

本文适用&#xff1a;rhel8系列&#xff0c;或同类系统(CentOS8,AlmaLinux8,RockyLinux8等) 文档形成时期&#xff1a;2023年 因系统版本不同&#xff0c;构建部署应略有差异&#xff0c;但本文未做细分&#xff0c;对稍有经验者应不存在明显障碍。 因软件世界之复杂和个人能力…

【Git】查看凭据管理器的账号信息,并删除账号,解决首次认证登录失败后无法重新登录的问题

欢迎来到《小5讲堂》 大家好&#xff0c;我是全栈小5。 这是是《代码管理工具》序列文章&#xff0c;每篇文章将以博主理解的角度展开讲解&#xff0c; 特别是针对知识点的概念进行叙说&#xff0c;大部分文章将会对这些概念进行实际例子验证&#xff0c;以此达到加深对知识点的…

springIoc依赖注入循环依赖三级缓存

springIoc的理解&#xff0c;原理和实现 控制反转&#xff1a; 理论思想&#xff0c;原来的对象是由使用者来进行控制&#xff0c;有了spring之后&#xff0c;可以把整个对象交给spring来帮我们进行管理 依赖注入DI&#xff1a; 依赖注入&#xff0c;把对应的属性的值注入到…

SQL-修改表操作

&#x1f389;欢迎您来到我的MySQL基础复习专栏 ☆* o(≧▽≦)o *☆哈喽~我是小小恶斯法克&#x1f379; ✨博客主页&#xff1a;小小恶斯法克的博客 &#x1f388;该系列文章专栏&#xff1a;重拾MySQL &#x1f379;文章作者技术和水平很有限&#xff0c;如果文中出现错误&am…

运动模型非线性扩展卡尔曼跟踪融合滤波算法(Matlab仿真)

卡尔曼滤波的原理和理论在CSDN已有很多文章&#xff0c;这里不再赘述&#xff0c;仅分享个人的理解和Matlab仿真代码。 1 单目标跟踪 匀速转弯&#xff08;CTRV&#xff09;运动模型下&#xff0c;摄像头输出目标状态camera_state [x, y, theta, v]&#xff0c;雷达输出目标状…

VS中动态库的创建和调用

VS中动态库的创建和调用 库 ​ 库是写好的现有的&#xff0c;成熟的&#xff0c;可以复用的代码。库的存在形式本质上来说库是一种可执行代码的二进制。 ​ 库有两种&#xff1a;静态库&#xff08;.a、.lib&#xff09;和动态库&#xff08;.so、.dll&#xff09;。所谓静态…

Find My游戏手柄|苹果Find My技术与手柄结合,智能防丢,全球定位

游戏手柄是一种常见电子游戏机的部件&#xff0c;通过操纵其按钮等&#xff0c;实现对游戏虚拟角色的控制。随着游戏设备硬件的升级换代&#xff0c;现代游戏手柄又增加了&#xff1a;类比摇杆&#xff08;方向及视角&#xff09;&#xff0c;扳机键以及HOME菜单键等。现在的游…

uniapp 文字超出多少字,显示收起全文按钮效果demo(整理)

收起展开 <template><view class"font30 color000 mL30 mR30"><text :class"showFullText ? : clamp-text">{{ text }}</text><view v-if"showToggleBtn && text.length > 42" click"toggleShowFu…

Elasticsearch安装Windows版

目录 1.&#xff1a;下载安装包&#xff0c;选择指定的版本&#xff0c;这里选择了7.8.0&#xff0c;官网下载地址&#xff1a; ​编辑 2&#xff1a;下载好之后解压&#xff0c;解压之后是这样的&#xff1a; 3&#xff1a;配置环境变量&#xff0c;跟JDK一样&#xff0c;…

如何高效编写测试用例

本话题暂不探讨是否有必要编写详细的测试用例&#xff0c;在确定要交付详细的测试用例这个前提下&#xff0c;分享如何更高效地完成测试用例的编写。 对齐测试用例需求 首先、明确要完成的测试用例文档目标要求&#xff0c;模板、范围、粒度等。 用例文档使用者&#xff1a;…

JDBC初体验(二)——增、删、改、查

本课目标 理解SQL注入的概念 掌握 PreparedStatement 接口的使用 熟练使用JDBC完成数据库的增、删、改、查操作 SQL注入 注入原理&#xff1a;利用现有应用程序&#xff0c;将&#xff08;恶意的&#xff09;SQL命令注入到后台数据库引擎执行能力&#xff0c;它可以通过在…

element input组件自动失去焦点问题解决

最近在 Vue3 ElementPlus 中&#xff0c;使用 el-input 组件时&#xff0c;如果设置了 v-model&#xff0c;那么在每次改变内容后后&#xff0c;input 会自动失去焦点&#xff0c;这样会导致用户无法输入多个字符。 一、问题原因 如上图所示&#xff0c;配置项的 Name 和 Cod…

VSCode使用技巧

选择python 解释器 使用快捷键CtrlShiftP Python: Select Interpreter快捷键 返回上一次光标的位置 重新设置一下 navigate

jmeter分布式服务搭建

目录 一、环境准备 二、 安装包下载 三 、安装jdk 四 、控制机安装 4.1 解压压缩包 4.2 修改 bin/jmeter.properties 4.3 修改 bin/system.properties 五、执行机安装 5.1 解压安装包 5.2 修改 bin/jmeter.properties 5.3 修改 bin/system.properties 5.4 启动执行机 …

记录el-select+el-tree复选框,支持模糊查询,懒加载,树父子节点不关联,不全选

需求&#xff1a;一个机构下拉菜单&#xff0c;一个人员下拉菜单&#xff0c;默认带入当前登录用户的机构和人员。机构下拉菜单为两个接口&#xff0c;模糊查询为一个接口不包含懒加载&#xff0c;默认非模糊查询情况下为一个接口&#xff0c;点击节点懒加载。机构下拉菜单数据…

NACHI机器人虚拟示教器报I2101异常

前言 机器人示教器报&#xff1a;I2101 异常停止按钮或外部停止信号被输入 无法再示教模式下进行程序的运行&#xff01; 解决方法 结果 最后测试可以正常的运行程序

细说DMD芯片信号-DLP3

1&#xff0c; Block diagram 2. 信号介绍 2.1, LS interface&#xff1a; LD_Data_P/N(i), LD_CLK_P/N(i), LS_RDATA_A_BIST(O) 2.2, 视频信号: HSSI(High speed serial interface) High speed Differential Data pair lan A0~7 P/N, High speed Differential Clock A High…

如何使用人工智能优化 DevOps?

DevOps 和人工智能密不可分&#xff0c;影响着各种业务。DevOps 可以加快产品开发速度并简化现有部署的维护&#xff0c;而 AI 则可以改变整个系统的功能。DevOps团队可以依靠人工智能和机器学习来进行数据集成、测试、评估和发布系统。更重要的是&#xff0c;人工智能和机器学…