MySQL性能优化的案例分析

这篇文章主要介绍MySQL性能优化的案例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！上一篇 《实践（1）–MySQL性能优化》我们讲了数据库表设计的一些原则，Explain工具的介绍、SQL语句优化索引的最佳实践，本篇继续来聊聊 MySQL 如何选择合适的索引。MySQL 最终是否选择走索引或者一张表涉及多个索引，最终是如何选择索引，可以使用 trace 工具来一查究竟，开启 trace工具会影响 MySQL 性能，所以只能临时分析 SQL 使用，用完之后立即关闭。讲 trace 工具之前我们先来看一个案例：MySQL 如何选择合适的索引如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下:对于上面这两种 name>'a' 和 name>'zzz' 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。执行这两句sql提出来trace值，详见注释结论：全表扫描的成本低于索引扫描，所以MySQL最终选择全开发云主机域名表扫描。结论：查看trace字段可知索引扫描的成本低于全表扫描，所以MySQL最终选择索引扫描。分析：利用最左前缀法则：中间字段不能断，因此查询用到了 name索引 ，从 key_len = 74 也能看出，age 索引列用在排序过程过程中，因为 Extra 字段里没有 using filesort 。分析：从 explain 的执行结果来看：key_len = 74，查询使用了 name 索引，由于用了 position 进行排序，跳过了 age，出现了 Using filesort。分析：查询只用到索引name，age 和 position 用于排序，无Using filesort。分析：和案例3中explain的执行结果一样，但是出现了Using filesort ，因为索引的创建顺序为 name,age,position , 但是排序的时候 age 和 position 颠倒位置了。分析：与案例4对比，在Extra中并未出现** Using filesort **，因为 age 为常量，在排序中被优化，所以索引未颠倒，不会出现 Using filesort 。分析：虽然排序的字段列与索引顺序一样，且 order by 默认升序，这里 position desc 变成列降序，导致与索引的排序方式不同，从而产生 Using filesort 。MySQL8 以上版本有降序索引可以支持该种查询方式。分析：对于排序来说，多个相等条件也是范围查询。可以用覆盖索引优化MySQL 通过比较系统变量 max_length_for_sort_data (默认1024字节) 的大小和需要查询的字段总大小来判断使用那种排序模式。查看下这条sql对应trace结果如下(只展示排序部分):修改系统变量 max_length_for_sort_data (默认1024字节) ，employees 表所有字段长度总和肯定大于10字节trace排序部分结果:单路排序的详细过程：双路排序的详细过程：对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort_buffer 中，而双路排序只会把主键和需要排序的字段放到 sort_buffer 中进行排序，然后再通过主键回到原表查询需要的字段。如果MySQL排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。如果MySQL排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序（单路排序），把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。所以，MySQL 通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。注意：如果全部使用sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer（默认1M），MySQL很多参数设置都做过优化的，不要轻易调整。在这我们先往 employess 插入一些测试数据很多时候我们业务系统实现分页功能可能会用如下SQL实现表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。首先来看一个根据自增且连续主键排序的分页查询的例子：该 SQL 表示查询从第 9001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 9001开始的五行数据，如下:查询结果是一致的，我们再对比一下执行计划：显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。
但是，这条改写的 SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验所示(先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL):两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。另外如果原SQL是order by 非主键的字段，按照上面说饿的方法改写会导致两条SQL的结果不一致。所以这种改写得满足以下两个条件：再看一个根据非主键字段排序的分页查询，SQL 如下:发现并没有使用 name 字段的索引(key 字段对应的值为 null)，具体原因上前面讲过 : 扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。 知道不走索引的原因，那么怎么优化呢? 其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划:原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。往t1表插入1万行记录，往t2表插入100行记录一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。从执行计划中可以看到这些信息:上面SQL的大致流程如下：整个过程会读取 t2 表的所有数据（扫描100行），然后遍历这每行数据中字段 a 的值，根据 t2 表中的 a 的值索引扫描 t1 表中对应的行（扫描 100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行）。因此整个过程扫描了 200 行。如果被驱动表的关联字段没有索引，使用NLJ算法性能会比较低（下面有详细解释），MySQL 会选择 Block Nested-Loop Join 算法。把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。上面sql的大致流程如下:整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNJ 的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。straight_join解释straight_join功能同join类似，但能让左边的表来驱动右边的表，能改变优化器对于联表查询的执行顺序。比如 : select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql选择 t2 表作为驱动表。原则：小表驱动大表，即小的数据集驱动大的数据集。in：当B表的数据集小于A表的数据集时，in优于existsexists：当A表的数据集小于B表的数据集时，exists优于in将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留临时关闭mysql查询缓存，为了查看sql多次执行的真实时间。四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数据行。为什么mysql最终选择辅助索引而不是主键聚集索引?因为二级索引相对主键索引存储数据更少，检索性能应该更高对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算。对于innodb存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算。如果只需要知道表总行数的估计值可以用如下sql查询，性能很高插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。以上是MySQL性能优化的案例分析的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注开发云行业资讯频道！

相关推荐: mysql有哪些字段类型

下面讲讲关于mysql有哪些字段类型，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完mysql有哪些字段类型这篇文章你一定会有所受益。 数值类型MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、…