【七天深入MySQL实战营】答疑汇总Day4 MySQL查询优化实战-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

【七天深入MySQL实战营】答疑汇总Day4 MySQL查询优化实战

简介: 【开营第四课】【 MySQL查询优化实战】 讲师:苏坡,袋鼠云高级数据库工程师。 课程内容:核心概念及原理;优化流程思路;常见场景下的优化。 答疑汇总:特别感谢班委@李敏 同学

【开营第四课】【 MySQL查询优化实战】
讲师:苏坡,袋鼠云高级数据库工程师。
课程内容:核心概念及原理;优化流程思路;常见场景下的优化。
答疑汇总:特别感谢班委@李敏 同学

1. 除了 explain,会有好的办法调优 sql 吗?

观测 mysql 行为的方法主要有两种:

(1) explain sql 语句

(2)show processlist 详情见 ppt 22 页(查看当前活跃会话状态)

调优 sql 的方法还有两种:

(1)show profiles,一条 sql 语句执行过程当中,每一步的开销

(2) Sql 优化器的 trace,可以看到优化器在选择执行计划时的判断标准,分析 sql 的时候,需要临时开一下,帮我们去模拟生成执行计划的过程(参考 https://segmentfault.com/a/1190000018136007

2.之前一直用 sqlserver,如果一个表 tbl,有 A、B 两个字段,且每个字段都建有一个索引,当执行 select * from tbl where A=? And B=? 时,mysql 会同时使用这两个索引查,然后将两个结果合并?

A:索引合并,是 innodb 优化器的一个比较典型的特征,通常来讲,发现一个sql语句出现索引合并的话,表示sql语句性能并不好,索引合并举例:and 交集,or 并集,一般来说一个表里面,建立了太多单列索引,就可能出现这种情况(索引合并)。看到(sql 语句)走的 a 和 b 两个索引,并且取的是交集,说明单个索引的过滤性并不是很好,建议改造成复合索引,建议大家一定要定期去检查表当中的索引,不要有太多单个的索引。

3.对于 8.0 的 explain analyze 有什么看法?8.0 的 hash join 之类的很多新特性,越来越接近 oracle,(老师)对于 mysql 的发展有什么看法?

A:8.0 的这个工具特别好用,推荐大家去使用,相比 5.6 和 5.7 的 analyze,8.0 的工具去分析问题的话,更为精确一些;不敢妄下结论,mysql毕竟从一个数据仓库的产品发展到现在,被收购 oracle 之后,oracle 的特性,在各个方面确实是比较完善一些,但 mysql 也并不是完全在接近 oracle,是在汲取 oracle 的一些特性吧。

4.Centos 下 Mysql 内存使用 1 个月内缓慢增长,直至数据库 OOM,kill 连接也不会释放,Mysql 内存参数已做限制,请问如何配置有效自动释放内存,个人查到 centos 内存调度算法 malloc 可以优化,但未实践(老师)在日常的工作上,也有碰到过内存相关的问题,做一些内存分析的工作之前,首先还是要看 mysql 是什么版本的,5.7 以前,分析内存可做的事情有限,比如分析数据库的会话和sql,推测某些操作会带来内存迅速升高,单从 mysql 的行为来去看(,不太好判断),5.7 之后,可以通过开启性能视图来分析,5.7 的性能视图还是比较丰富的,特别是内存这一块,建议感兴趣的同学可以去研究研究。

5.应用有两个数据源,一个是 mysql 8;一个是 mysql 5.7,用 8 版本的 jar 会对 5.7 的数据源有什么影响吗?这个应用在服务器的 tomcat 上部署旧报链接不上 mysql 8 的数据源,本地测试就可以,具体可以往那方面排查?

A:这个要不看一下 jdbc 版本的问题,关于连不上数据库的话,可能会报不同的原因,这个要有不同的报错出来(主要观察是报错内容,然后具体错误具体分析)。

6.select * from jdp_tb_trade where seller_nick in (‘’,’’,’’,’’...)。当 in 的集合中数量超过 9 后,会切换索引,导致查询变慢。这是什么原因,得怎么优化?

A:n 数量太多切换索引,这个是比较正常的的,要看切换前后具体使用什么索引。in 集合元素的数量,导致优化器选择了不同的索引,可以看看 where 条件的字段是不是够好的。关于这种问题,如何分析,首先看一下这个表有没有索引,在分析一下在(?)字段上数据分布的规律。(优化方式老师没回答,在 sql 语句中使用 use index ,可以强制指定索引)

7.数据库达到什么条件时才应该考虑分库分表

A:问题很实在,总会听到各种各种的看法,比如表达到多少条数据,或者怎么样之后。从我的角度来看,我们先优化,从下往上,sql及索引、库表结构、系统配置配置(详见 PPT第 6 页)、硬件,这是吹着调优的的过程,把单一 mysql 实例变得更好的过程,如果垂直调优达到瓶颈,当单个 mysql 已经做得足够好的情况下,我们要考虑分库分表。还有一种情况,在某些业务场景中,我们的业务压力,在将来特别短暂的一段时间可能会有扩增,对于业务发展情况,为了应对用户量、访问量的膨胀,提前考虑分库分表的策略。

8.新业务需要添加字段,如果该业务下线。如果多次的话,会有很多的无用字段,这种情况要怎么处理比较好?

A:业务上下线,需要增加/删除字段,这种做法并不不建议,在生产环境,频繁的给 mysql 表,增加/删除字段。某些企业情况特殊,确实需要频繁变动,像这种情况,可以考虑使用一下 mongodb 数据库,还有一种方式(在表里面)增加一种扩展的字段,没有就是空,有就塞(默认为空,在必要的时候才填充),这种也不太建议。

9.当业务开发时发现需要频繁获取分库后的某个库里的数据,应该通过 rpc 调用其他连接该库的 java 服务来获取,还是应该考虑重新分库?

第9个问题没有看太明白

10.请问循环事务调用的场景下,怎么在正常请求下保证强一致性?

有些问题信息不是很完善,以后私下沟通交流

11.请问如何通过网络层面分析是某些 sql 占用了大量的网络带宽?

A:网络带宽,可以通过有一些诊断工具/平台去分析,当有一个比较好的监控体系之后,看到在某个时间段,有网络带宽的上涨,首先判断是 出/入==> 查询/插入,通常来讲,像网络层面的增加,是查询,看到这种现象,然后分析对应的时间段,哪些 sql 语句扫描/返回的行数比较高,一般情况来讲的话,关于网络带宽升高的情况,某些查询返回大量数据,再去分析某个时间段,sql 的统计情况,就可以了。

12.对非时间字段建 hash 分区,有什么最佳推荐?

A:对于 hash 的话,并不是十分推荐,hash 这种算法只是单纯将数据打散,还是要看具体的查询场景,如果字段是枚举型的,比如分类,像电商场景下,(字段)是一个比较大的组织的 id / 地区,如果查询里面,都会携带这个 id,那我们就按照这个字段去分区,也是可以允许的

13.表分区个数有限制吗?有上万张表,按天分区能行吗?

A:表分区限制肯定有,具体数值一时说不上来,需要查看文档

14.mysql 存储过程功能和 oracle 存储过程相比有什么缺点?之前的一个培训老师说不建议使用 mysql 存储过程时什么原因?

A:为什么在 mysql 里面不建议使用,首先要清楚认识存储过程是一个什么东西:在数据库里面运行的,一段业务计算逻辑,属于业务代码,运行(业务代码)需要消耗额外的计算资源。msyql 和 oracle 的区别在于:oracle 拥有强大的计算能力,所以在 mysql 里面不建议使用。oracle 相对来说,在功能方面,比 mysql 要支持得更为全面一点

15.mysql sending data 占用时间较长,该怎么排查?

A:一般来说,sending data 占用时间长,通常来说,是扫描了过量的数据。对于这种情况,直接去分析这个 sql 语句的执行计划,是否走了索引,或者有其他的情况,导致它扫描了大量数据,或者说(sql 语句)里面存在子查询的情况

16.mysql count(*)  数据上千万时候非常慢,又要经常做统计,可以怎么做

A:一般这种场景,主要就是在分页的情况下,我理解,大部分的情况下就是做分页,每次要去做 count,又做 limit 。在数据量表大的情况下,limit 比较好优化,count(*) 比较容易,达到性能的上限。像这种情况,第一个要保证 count(*) 走的是索引,通过索引去扫描 count,做好了这点,count(*) 依然很慢的话,考虑在业务上做一些事,比如适当的妥协,不是感觉用户每一次去查看 count(*) 的时候,都必须要是准确的,用一定的缓存机制,选用服务器缓存也好,redis 缓存也好,采用过几分钟更新一次的方式,这样(的结果)就是再去获取 count 的数据的时候,不用等待太长的时间,当然有一个前提就是,对 count(*) 的查询,要求没有那么苛刻。

17.mysql 使用 group by ... order by ... limit [n] 有时候会选择错误的索引,对于这种情况该如何优化?除了使用 force index?

A:对于 Group by Order by 会选择错误索引的情况,首先要明确,在 mysql 里面,选择错误索引的原因,sql 语句里面有 group by,也有 order by,通常 mysql 会更加在意 where 条件,首先分析 sql 语句的构成,然后在根据表当中数据的分布规律,到底怎么样走索引更适合,最后再根据 sql 语句怎么,去判断建立怎样的索引是更加合适的。这里有一点建议,关于 sql 语句,这里可以利用 optimizer_trace 去分析,去看它为什么选择了,和咱们预期不一样的索引

18.数据量比较大的时候分页查询应该如何实现?

A:首先对于分页查询的这种优化的方式,在 mysql 数据库里面可以做的优化策略,就是在 PPT 里面讲到的覆盖索引的方式,就是先通过覆盖索引,去查找到主键id,再通过主键 id 的方式,去查找到这个表当中的数据。可能会存在这种情况,就是当数据量特别大的时候,通过覆盖索引去查询,还是会比较慢,这种情况的话,可以考虑在架构方面做一些扩展,在 mysql 里面没有太好的方式。第一,可以考虑用其他其他数据库产品代替,第二,可以在架构方面做扩展,然后看在业务上,能不能做一些妥协,比如禁止跳页,只让他点下一页,这样的话,每次点下一页,都会有一个 id,查询的 sql 语句就会类似 id > ? limit ?,那么不管我们的表,数据量有多大,查询的开销都不会很大。总的来讲,在数据量特别大的场景下,我们做很多事情都是很困难的,不可能通过标准的方式去看

19.查询时以时间顺序排序,在时间字段上建索引有帮助吗?还有其他方案吗?

A:给时间字段建索引排序的话,肯定是有帮助的,(给时间字段建索引)不管说是 between...and,range,还是 order by 也好,肯定是会有帮助的。关于其他方案,对于时间的查询,可能会有各种各样的需求,首先看时间存储的什么东西,比如年月日、时分秒,但是去做查询,是按照天或者其他什么做排序,可能需要表的结构上做改变,而不是通过索引/函数,去处理时间这个字段的值

20.一张10亿级别的大表,如何提升 count 的效率

A:对于10亿级别的大表,不建议在单一的 mysql 实例里面出一张10亿级别的大表。这个可以比较准确的说,如果在单一的 mysql 实例里面,有一张10亿级别的大表,不管我们怎样去查询,count(*) 效率都是很低的,除非有特别严谨的 where 条件,对于全表的 count(*) 查询,肯定还是很慢的。对于这种情况,可以用一些数仓的产品,或者用分库分表的策略,通过 MPP 的架构形式,来提升 count(*) 的效率

21.对索引顺序有建议吗 ?

A:建议排在前面的字段,一般是过滤性比较好的。对索引的顺序,也要看实际的业务场景,比如说排序的顺序,索引的顺序也要和排序的顺序一致,没有排序的场景,通常排在前面的字段,会选择过过滤型比较好的字段,因为对于复合索引来说,最前面的几个字段,也可以起到单列索引的效果






版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接