【开营第四课】【 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：建议排在前面的字段，一般是过滤性比较好的。对索引的顺序，也要看实际的业务场景，比如说排序的顺序，索引的顺序也要和排序的顺序一致，没有排序的场景，通常排在前面的字段，会选择过过滤型比较好的字段，因为对于复合索引来说，最前面的几个字段，也可以起到单列索引的效果

来源：https://developer.aliyun.com/article/781439