字节二面:一条慢sql如何优化?

字节二面:一条慢sql如何优化?已关闭评论

作为二本上岸大厂的后端应届生,深知没人带一路摸索的艰辛,想把自己的心路历程与经验心得收获分享给大家。后期大厂面试系列持续更新中…..

01
前文

众所周知,想要进大厂,MYSQL是必问的技术之一。如果你在中小公司工作,也许对数据库的操作仅仅简单CRUD就够用啦。但是,对于大厂,面对百万级数据量,如何保证MYSQL性能依旧很好呢?


这其实涉及很多方面:索引、主从读写分离、集群、分库分表、sql、锁、参数调优、表结构等。而本文想带领大家探讨一下“一条sql执行很慢可能的原因,如何优化?”。


由于笔者水平有限,可能考虑的不是很全面。欢迎留言补充。


关注我的小伙伴应该都知道我的原则:如果想要详细了解或则想知道它具体内部咋实现的建议仔细去看书,这里我就简单分享我的理解,知道这些,面试基本够用啦。(悄悄说:我也是看书+从阿里面试官那里偷学的!)


02
sql怎么会变慢呢?

我从来不喜欢说废话、打广告,网上千篇一律的文章讲了半天也get不到点。只为对标大厂面试,接下来看看我怎样回答吧。干货慢慢,若有不足,欢迎留言改正。


我认为,一个 SQL 执行的很慢,我们要分两种情况讨论:


1、大多数情况下很正常,偶尔很慢,则有如下原因

(1)、数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候,遇到锁,如表锁、行锁。

(3)、sql写的烂


2、这条 SQL 语句一直执行的很慢,则有如下原因:

(1)、没有用上索引或则索引失效:例如该字段没有索引;或则由于对字段进行运算、函数操作导致无法用索引。

(2)、有索引可能会走全表扫描


怎样判断是否走全表扫描:

索引区分度(索引的值不同越多,区分度越高),称为基数,而数据量大时不可能全部扫描一遍得到基数,而是采样部分数据进行预测,那有可能预测错了,导致走全表扫描。


03
慢sql优化
01

数据库中设置SQL慢查询


方式一:修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log日志记录( slow_query_log)


      


方式二:通过MySQL数据库开启慢查询:


      


02

分析慢查询日志


# 可以通过如下命令定位低效率执行sql
show processlist;# sql 可以用 explain 分析执行计划。


对于执行计划的分析,也是面试官喜欢考察的一个点。面试官:你是怎样使用explain的?对于执行计划你是怎样分析的?


这里简单带过,之后会详细解答。


主要关注这几个字段即可:


type:表示MySQL在表中找到所需行的方式,或者叫访问类型

  • type=ALL,全表扫描,MySQL遍历全表来找到匹配行

  • type=index,索引全扫描

  • type=range,索引范围扫描

  • type=eq_ref,唯一索引

  • type=NULL,MySQL不用访问表或者索引,直接就能够得到结果(性能最好)

possible_keys: 表示查询可能使用的索引

key: 实际使用的索引

key_len: 使用索引字段的长度

rows: 扫描行的数量

Extra

  • using index:覆盖索引,不回表

  • using where:回表查询

  • using filesort:需要额外的排序,不能通过索引得到排序结果

03

慢sql如何让优化


对于MYSQL慢sql语句的优化,我们也可以分几个方面来进行分析(基本覆盖全面啦):


面试从这几方面考虑:索引+sql语句+数据库结构优化+优化器优化+架构优化。


索引


  • 尽量覆盖索引,5.6支持索引下推

  • 组合索引符合最左匹配原则

  • 避免索引失效

  • 再写多读少的场景下,可以选择普通索引而不要唯一索引。更新时,普通索引可以使用change buffer进行优化,减少磁盘IO,将更新操作记录到change bufer,等查询来了将数据读到内存再进行修改.

  • 索引建立原则(一般建在where和order by,基数要大,区分度要高,不要过度索引,外键建索引)


sql语句


1、分页查询优化

该方案适用于主键自增的表,可以把Limit查询转换成某个位置的查询。

select * from tb_sku where id>20000 limit 10;


2、优化insert语句

  • 多条插入语句写成一条

  • 在事务中插数据

  • 数据有序插入(主键索引)

数据库结构优化


1、将字段多的表分解成多个表有些字段使用频率高,有些低,数据量大时,会由于使用频率低的存在而变慢,可以考虑分开。


2、对于经常联合查询的表,可以考虑建立中间表


优化器优化

1、优化器使用MRR


原理:MRR 【Multi-Range Read】将ID或键值读到buffer排序,通过把「随机磁盘读」,转化为「顺序磁盘读」,减少磁盘IO,从而提高了索引查询的性能。


开启mrr:



对于 Myisam,在去磁盘获取完整数据之前,会先按照 rowid 排好序,再去顺序的读取磁盘。

对于 Innodb,则会按照聚簇索引键值排好序,再顺序的读取聚簇索引。


磁盘预读:请求一页的数据时,可以把后面几页的数据也一起返回,放到数据缓冲池中,这样如果下次刚好需要下一页的数据,就不再需要到磁盘读取(局部性原理)


索引本身就是为了减少磁盘 IO,加快查询,而 MRR,则是把索引减少磁盘 IO 的作用,进一步放大

https://zhuanlan.zhihu.com/p/148680235


2、其他(其他可以去看书了解)


架构优化

读/写分离(主库写,从库读)


总结

1、先设置慢查询(my.ini或数据库命令)

2、分析慢查询日志

3、定位低效率sql(show processlist)

4、explain分析执行计划(是否索引失效,用到索引没,用了哪些)

5、优化(索引+sql语句+数据库结构优化+优化器优化+架构优化)


认识到一个人的灵魂是无法把握的,这是智慧的最终成就。人本身就是最终的谜。by 王尔德


来源: 苏三说技术