写点什么

Java 岗大厂面试百日冲刺 - 日积月累,每日三题【Day4,kafkastreams 实战

作者:Java高工P7
  • 2021 年 11 月 10 日
  • 本文字数:5529 字

    阅读完需:约 18 分钟


??因此,不足的地方希望各位在评论区补充疑惑、见解以及面试中遇到的奇葩问法,希望这 100 天能够让我们有质的飞越,一起冲进大厂!!,让我们一起学(juan)起来!!!


车票




本栏目 Java 开发岗高频面试题主要出自以下各技术栈:Java基础知识集合容器并发编程JVMSpring全家桶MyBatis等ORMapping框架MySQL数据库Redis缓存RabbitMQ消息队列Linux操作技巧等。


??终于到了期待已久的 MySQL 系列,太舒服了,还是个阳光明媚的周五~~~COOL!



??写在前面,群里同学常提:数据库这方面,面试一般怎么问呢?



??我们虽不是大公司,但面试过很多朋友。我们一般从 sql 优化起头,基于回答内容,深入原理,然后往索引、事务上找,曾经实际优化的事儿,底子好的,一般两个点以后就不在问啦~



??至于为啥不再问,是因为问太多毫无意义!!,看完本文你就会有所体会。


面试题 1:你对数据库优化有哪些了解呀?


==================================================================================


正经回答:




在高并发环境下,数据库是最敏感的地方,nginx 负载均衡、Server 集群、MQ 消息队列、Redis 缓存集群、数据库主从集群所作的一切都是为了减轻数据库访问压力。但是!前提是要有健壮的数据库和底层代码,这样才能使前期准备不再是花架子。



性价比如上图,我们针对数据库的优化优先级大致如下:


  • 高:从 SQL 优化、索引优化入手,优化慢 SQL、利用好索引,是重中之重;

  • 中:SQL 优化之后,是对数据表结构设计、横纵分表分库,对数据量级的处理;

  • 低:通过修改数据库系统配置,最大化里用服务器内存等资源;

  • 低:通过以上方式还不行,那就是服务器资源瓶颈了,加机器。


优化成本:硬件 > 系统配置 > 数据库表结构 > SQL 及索引。


优化效果:硬件 < 系统配置 < 数据库表结构 < SQL 及索引。


深入追问:



追问 1:那你对 SQL 优化方面有哪些技巧呢?

简单说对于 SQL 优化,就三点:


  • 最大化利用索引;

  • 尽可能避免全表扫描;

  • 减少无效数据的查询;


首先要清楚 SELECT 语句 - 执行顺序:


FROM


<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。


ON


<筛选条件> # 对笛卡尔积的虚表进行筛选


JOIN <join, left join, right join…>


<join 表> # 指定 join,用于添加数据到 on 之后的虚表中,例如 left join 会将左表的剩余数据添加到虚表中


WHERE


<where 条件> # 对上述虚表进行筛选


GROUP BY


<分组条件> # 分组


<SUM()等聚合函数> # 用于 having 子句进行判断,在书写上这类聚合函数是写在 having 判断里面的


HAVING


<分组筛选> # 对分组后的结果进行聚合筛选


SELECT


<返回数据列表> # 返回的单列必须在 group by 子句中,聚合函数除外


DISTINCT


#数据除重


ORDER BY


<排序条件> # 排序


LIMIT


<行数限制>


SQL 优化策略:


声明:以下 SQL 优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准,以免画蛇添足。


一、避免不走索引的场景


  1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:


SELECT * FROM t WHERE username LIKE '%陈 %'


优化方式:尽量在字段后面使用模糊查询。如下:(原因涉及 B+Tree 索引最左前缀原则,可以参考《MySQL最左匹配原则,道儿上兄弟都得知道的原则》


SELECT * FROM t WHERE username LIKE '陈 %'


如果需求是要在前面使用模糊查询,


  • 使用 MySQL 内置函数 INSTR(str,substr) 来匹配,作用类似于 java 中的 indexOf(),查询字符串出现的角标位置,可参阅《MySQL模糊查询用法大全(正则、通配符、内置函数等)

  • 使用 FullText 全文索引,用 match against 检索

  • 数据量较大的情况,建议引用 ElasticSearch、solr,亿级数据量检索速度秒级

  • 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用 like ‘%xx%’。


  1. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描。如下:


SELECT * FROM t WHERE id = 1 OR id = 3


优化方式:可以用 union 代替 or。如下:


SELECT * FROM t WHERE id = 1


UNION


SELECT * FROM t WHERE id = 3


  1. 尽量避免进行 null 值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:


SELECT * FROM t WHERE score IS NULL


优化方式:可以给字段添加默认值 0,对 0 值进行判断。如下:


SELECT * FROM t WHERE score = 0


??这里说明了字段设为 not null 的重要性,详细请参考之前博文《领导含泪叮嘱我:MySQL 建表字段记得用 not null,不然就收拾包袱滚蛋》


  1. 尽量避免在 where 条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。


可以将表达式、函数操作移动到等号右侧。如下:


-- 全表扫描


SELECT * FROM T WHERE score/10 = 9


-- 走索引


SELECT * FROM T WHERE score = 10*9


  1. 当数据量大时,避免使用 where 1=1 的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:


SELECT username, age, sex FROM T WHERE 1=1


优化方式:用代码拼装 sql 时进行判断,没 where 条件就去掉 where,有 where 条件就加 and。


  1. 查询条件不要用 <> 或者 !=


使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。


  1. where 条件仅包含复合索引非前置列


如下:复合(联合)索引包含 key_part1,key_part2,key_part3 三列,但 SQL 语句没有包含索引前置列"key_part1",按照 MySQL 联合索引的最左匹配原则,不会走联合索引。。


select col1 from table where key_part2=1 and key_part3=2


了解其原理的同学可以参考《MySQL最左匹配原则,道儿上兄弟都得知道的原则》


  1. 隐式类型转换造成不使用索引


如下 SQL 语句由于索引对列类型为 varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。


select col1 from table where col_varchar=123;


了解其原理的同学可以参考《令人炸毛儿的MySQL隐式转换 - 无形之刃,最为致命》


  1. order by 条件要与 where 中条件一致,否则 order by 不会利用索引进行排序


-- 不走 age 索引


SELECT * FROM t order by age;


-- 走 age 索引


SELECT * FROM t where age > 0 order by age;


对于上面的语句,数据库的处理顺序是:


  • 第一步:根据 where 条件和统计信息生成执行计划,得到数据。

  • 第二步:将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看 order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则重新进行排序操作。

  • 第三步:返回排序后的数据。


当 order by 中的字段出现在 where 条件中时,才会利用索引而不再二次排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。


这个结论不仅对 order by 有效,对其他需要排序的操作也有效。比如 group by 、union 、distinct 等。



二、SELECT 语句的一些其他优化


  1. 避免出现select *


首先,select * 操作在任何类型数据库中都不是一个好的 SQL 编写习惯。


使用 select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的 I/O,内存和 CPU 消耗。


建议提出业务实际需要的列数,将指定列名以取代 select *。


具体详情见《为什么大家都说SELECT * 效率低》


  1. 避免出现不确定结果的函数


特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句,使用如 now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的 SQL 语句无法利用 query cache。


  1. 多表关联查询时,小表在前,大表在后


在 MySQL 中,执行 from 后的表关联查询是从左往右执行的(Oracle 相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只


《Android学习笔记总结+最新移动架构视频+大厂安卓面试真题+项目实战源码讲义》
浏览器打开:qq.cn.hn/FTe 免费领取
复制代码


扫描大表的前 100 行就符合返回条件并 return 了。


例如:表 1 有 50 条数据,表 2 有 30 亿条数据;如果全表扫描表 2,你品,那就先去吃个饭再说吧是吧。



  1. 使用表的别名


当在 SQL 语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。


  1. 用where字句替换HAVING字句


避免使用 HAVING 字句,因为 HAVING 只会在检索出所有记录之后才对结果集进行过滤,而 where 则是在聚合前刷选记录,如果能通过 where 字句限制记录的数目,那就能减少这方面的开销。HAVING 中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在 where 字句中。


  • where 和 having 的区别:where 后面不能使用组函数


  1. 调整Where字句中的连接顺序


MySQL 采用从左往右,自上而下的顺序解析 where 子句。根据这个原理,应将过滤数据多的条件往前放,最快速度缩小结果集。对了,听说 5.7 版的语法解析器已经实现了 where 后条件的自动调节工作。查询条件很多的场景,建议不要做这种尝试。

追问 2:嗯,那你说一下为什么不建议用 SELECT * 呢?

在阿里代码规范中的《阿里java开发手册(泰山版)》(提取码:hb6i)MySQL 部分描述声明:


4 - 1. 【强制】在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写出。



  • 增加查询分析器解析成本。


  • 增减字段容易与 resultMap 配置不一致。


  • 无用字段增加网络 消耗,尤其是 text 类型的字段。


1. 不需要的列会增加数据传输时间和网络开销


用“SELECT * ”数据库需要解析更多的对象、字段、权限、属性等相关内容,在 SQL 语句复杂,硬解析较多的情况下,会对数据库造成沉重的负担。


增大网络开销;* 有时会误带上如 log、IconMD5 之类的无用且大文本字段,数据传输 size 会几何增涨。如果 DB 和应用程序不在同一台机器,这种开销非常明显。


即使 mysql 服务器和客户端是在同一台机器上,使用的协议还是 tcp,通信也是需要额外的时间。


2. 对于无用的大字段,如 varchar、blob、text,会增加 io 操作


准确来说,长度超过 728 字节的时候,会先把超出的数据序列化到另外一个地方,因此读取这条记录会增加一次 io 操作。(MySQL InnoDB)


3. 失去 MySQL 优化器“覆盖索引”策略优化的可能性


SELECT * 杜绝了覆盖索引的可能性,而基于 MySQL 优化器的“覆盖索引”策略又是速度极快,效率极高,业界极为推荐的查询优化方式。





课间休息,《垂钓者 1》 坐标:北京 元大都遗址。??




面试题 2:你对分库分表是怎么看的呀?


=================================================================================


正经回答:




  • 分库:由单个数据库实例拆分成多个数据库实例,将数据分布到多个数据库实例中。

  • 分表:由单张表拆分成多张表,将数据划分到多张表内。


要知道,对于大型互联网项目,数据量级可能不是我们能想到的,每日新增数据量过千万是常有的事儿,想靠单台 MySQL 服务器是不现实的。你项羽在牛 B,也顶不住四个队友挂机啊!!项羽:???



随着业务数据量和网站 QPS 日益增高,对数据库压力也越来越大,单机版数据库很快会到达存储和并发瓶颈,就需要做数据库性能方面的优化,分库分表采取的是分而治之的策略,分库目的是减轻单台 MySQL 实例存储压力及可扩展性,而分表是解决单张表数据过大以后查询的瓶颈问题,坦白说,这些问题也是所有关系型数据库的“硬伤”。


常用策略包括:垂直分表水平分表垂直分库水平分库



1、垂直分表


垂直分表,或者叫竖着切表,是不是感受到该策略是以字段为依据的!主要按照字段的活跃性、字段长度,将表中字段拆分到不同的表(主表和扩展表)中。


特点:


  • 每个表的结构都不一样;

  • 每个表的数据也不一样,

  • 有一个关联字段,一般是主键或外键,用于关联兄弟表数据;

  • 所有兄弟表的并集是该表的全量数据;


场景


  1. 有几个字段属于热点字段,更新频率很高,要把这些字段单独切到一张表里,不然 innodb 行锁很恶心的,锁死你呀~~如用户表里的余额字段?不,我的余额就很稳定,一直是 0。。

  2. 有大字段,如text,存储压力很大,毕竟 innodb 数据和索引是同一个文件;同时,我又喜欢用 SELECT *,你懂得,这磁盘 IO 消耗的,跟玩儿似的,谁都扛不住的。

  3. 有明显的业务区分,或表结构设计时字段冗余;有些小伙伴看到第一点时,就发现陈哈哈是个菜鸡,用户表怎么会有余额字段?明显有问题啊!赶紧先到评论区喷陈哈哈一波~~然后笑嘻嘻的发现原来是个小尾巴,真不要脸是吧。。是的,因此不同业务我们要把具体字段拆开,这样才有利于业务后续扩展哦。


2、水平分表


水平分表,也叫“横着切”。。以行数据为依据进行切分,一般按照某列的自容进行切分。


如手机号表,我们可以通过前两位或前三位进行切分,如131、132、133 → phone_131、phone_132、phone_133,手机号有 11 位(100 亿),量大是很正常的事儿,这年头谁家老头老太太每个手机呢是吧。这样切就把一张大表切成了好几十张小表,数据量不就下来了。有同学就问了那我怎么知道我这手机号查哪个表呢?一看你就没认真看前两行标红的点,为啥标红嘞?比如我查13100001111,那我截取前三位,动态拼接到查询的表名上,就行了。


特点:


  • 每个表的结构都一样;

  • 每个表的数据都不一样,没有交集;

  • 所有表的并集是该表的全量数据;


场景:单表的数据量过大或增长速度很快,已经影响或即将会影响 SQL 查询效率,加重了 CPU 负担,提前到达瓶颈。记得水平分表越早越好,别问我为什么。。


你要有兴趣试一试,就关注我,让csdn研发同学给我的粉丝们分个表哈哈。。算了,别做梦了,忘了你是个菜狗了么~

用户头像

Java高工P7

关注

还未添加个人签名 2021.11.08 加入

还未添加个人简介

评论

发布
暂无评论
Java岗大厂面试百日冲刺 - 日积月累,每日三题【Day4,kafkastreams实战