MySQL 性能优化之查询优化
本场 Chat 首先会带领大家畅聊 MySQL 性能优化之查询优化，了解 MySQL 之查询优化有
|
如今互联网行业用的最多就是 MySQL，不管项目大小都会用到它，然而 MySQL 其实玩的就是优化和架构设计。 本场 Chat 首先会带领大家畅聊 MySQL 性能优化之查询优化，了解 MySQL 之查询优化有那些，并且后期运用在项目中。本场 Chat 您将学到如下内容： 了解查询优化器模块；了解查询优化的基本思路；了解查询的基本原则；了解索引利弊及索引分类；了解索引相关优化。 本篇文章将阐述 MySQL 查询优化相关的东西，了解查询优化器、查询优化的基本思路、查询的基本原则、索引相关的优化。 一、查询优化器模块 查询优化器的任务是发现执行 SQL 查询的最佳方案。大多数查询优化器mysql 模糊搜索 优化，要么基于规则、要么基于成本。大多数查询优化器，包含 MySQL 的查询优化器，总或多或少地在所有可能的查询评估方案中搜索最佳方案。 MySQL 中 MySQL Query Optimizer 是优化器的核心，当 MySQL 数据拿到一个 Query 语句之后会交给 Query Optimizer 去解析，并产生一个最优的执行计划（这个是 Optimizer 认为是最优的，但不一定是真正最优的，就跟 Oracle 数据库会估算错 rows 一样），然后数据库按照这个执行计划去执行查询语句。在 SQL 语句整个执行过程中，Optimizer 是最耗时的，但是也有第三方工具为了提高性能绕开 MySQL 的 Query Optimizer 模块，比如：handlersocket。 对于多表关联查询，MySQL 优化器所查询的可能方案数随查询中引用的表的数目成指数增长。对于小数量的表，这不是一个问题。然而，当提交的查询需要的结果集很大时，查询优化所花的时间会很容易地成为服务器性能的瓶颈。 查询优化的一个更加灵活的方案时容许用户控制优化器详细地搜索最佳查询评估方案。一般思想是调查的方案越少，它编译一个查询所花费的时间越少。另外，由于优化器跳过一些方案，它可能错过一个最佳方案。优化器关于方案数量评估的行为可以通过两个系统变量来控制： optimizer_prune_level 变量告诉优化器根据对每个表访问的行数的估计跳过一些方案。我们的试验显示该类 “有根据的猜测” 很少错过最佳方案，并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为 on(optimizer_prune_level=1)。然而，如果你认为优化器错过了一个更好的查询方案，则该选项可以关闭 (optimizer_prune_level=0)，风险是查询编辑花费的时间更长。请注意即使使用该启发，优化器仍然可以探测呈指数数目的方案。 timizer_search_depth 变量告诉优化器对于每个未完成的 “未来的” 方案，应查看多深，以评估是否应对它进一步扩大。optimizer_search_depth 值较小会使查询编辑次数大大减小。例如，如果 optimizer_search_depth 接近于查询中表的数量，对 12、13 或更多表的查询很可能需要几小时甚至几天的时间来编译。同时，如果用 optimizer_search_depth 等于 3 或 4 编辑，对于同一个查询，编译器编译时间可以少于 1 分钟。如果不能确定合理的 optimizer_search_depth 值，该变量可以设置为 0，告诉优化器自动确定该值。 二、查询优化的基本思路 不管做项目设计还是产品设计都需要先有思路，才能规避一些问题。当然 MySQL 查询优化也需要研发或者 DBA 拥有一些思路，唯有思路指导书写，才会更加合理。 1. 优化更需要优化的 Query 语句 应该优化并发高的 Query 语句，不至于高并发下，由于 SQL 导致应用程序卡死，比如 php-fpm 的大量等待，而且一个高并发的 Query 语句，如果走错执行计划，本来只需要扫描几百行，结果扫描了几百万行，可能会有灾难性的后果，更加会导致业务卡顿，尤其是核心业务下出现的高并发 Query 语句。 2. 查看执行计划调整 Query 语句 根据 explain extended SQL 分析查询语句，就能查看执行计划，这个时候需要关注执行计划中的一些要素： select type type 扫描方式 3. 学会查看性能损耗（cpu 消耗、io 消耗） 当发现有慢 Query 语句时，需要定位到底是哪里慢，CPU 还是 IO 等： 三、查询的基本原则1. 永远用小结果集驱动大结果集（很多研发不会注重这些，都是习惯性的 left join） 做 join 查询时，驱动表，一定是条件限定后记录较少的表。 MySQL 的 join 只有一种算法 nested loop 也就是程序中的 for 循环，通过嵌套循环实现，驱动结果集越大，所需要循环的次数越多，访问被驱动表的次数也越多。降低 IO 同时降低 CPU。 2. 只查询需要的列（研发人员早起可能为了项目大多是 select _，后期优化必须关注） 只查询需要的列，可以让 IO 降低，列和排序算法也有关系。 3. 仅仅使用最有效的过滤条件 前提是用 a 条件 查询出结果 用 b 条件查询出结果，a、b 都用查询出结果，这三次结果都一样。 到底是用 a 条件还是 b 条件，还是两个条件都限定，只能看执行计划。 4. 尽量避免复杂的 join 和子查询5. 尽量在索引列上完成排序和查询四：索引利弊及索引分类 万事万物都有利弊，一个东西的出现，比如会在不同场景下有好好坏，就看如何权衡。 好处： 坏处： 五、索引分类六、索引相关优化1. 如何判断是否需要创建索引2. MySQL 中索引的限制（下面的原理在 Oracle 当中基本都成立）3. 使用索引的一些建议 综上所述，大致简单明了的阐述了 MySQL 查询优化一些相关的东西，至少对于中小型企业，可以作为研发人员的数据库规范，避免后期迁移或扩容时的一些问题。一切相关问题可以后续读者圈交流，谢谢大家耐心看完。 （编辑：上海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
