MySQL高级篇——索引解决查询相关的优化问题

文章目录: 1.数据准备 2.内外连接优化 2.1 外连接优化 2.2 内连接优化 3.子查询优…

文章目录:

1.数据准备

2.内外连接优化

2.1 外连接优化

2.2 内连接优化

3.子查询优化

4.ORDER BY排序优化

5.GROUP BY分组优化

6.LIMIT分页查询优化

7.优先考虑覆盖索引

8.其它查询优化策略

8.1 EXISTS和IN的区分

8.2 COUNT(*)与COUNT(具体字段)效率

8.3 关于SELECT(*)

8.4 LIMIT 1 对优化的影响

8.5 多使用COMMIT


1.数据准备

准备两张表:type、book,每张表中各添加20条数据。


2.内外连接优化

  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN 的字段,数据类型保持绝对一致。
  • LEFT JOIN 时,选择小表作为驱动表, 大表作为被驱动表 。减少外层循环的次数。
  • INNER JOIN 时,MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
  • 不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用 JOIN 来代替子查询。
  • 衍生表建不了索引

2.1 外连接优化

2.2 内连接优化


3.子查询优化

MySQL 4.1 版本开始支持子查询,使用子查询可以进行 SELECT 语句的嵌套查询,即一个 SELECT 查询的结果作为另一个SELECT 语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的 SQL 操作

子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,子 查询的执行效率不高。 原因:

① 执行子查询时, MySQL 需要为内层查询语句的查询结果 建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再 撤销这些临时表 。这样会消耗过多的 CPU IO 资源,产生大量的慢查询。

② 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响。

③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

MySQL 中,可以使用连接( JOIN )查询来替代子查询。 连接查询 不需要建立临时表 ,其 速度比子查询要快 ,如果查询中使用索引的话,性能就会更好。

结论:尽量不要使用 NOT IN 或者 NOT EXISTS ,用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

数据准备:需要用到class表,建表相关参考:https://blog.csdn.net/weixin_43823808/article/details/124172443

上面的这条子查询sql就可以优化为 连接查询:👇👇👇

通过下面的优化(连接查询),type级别从index提升到了 ref。


4.ORDER BY排序优化

问题: WHERE 条件字段上加索引,但是为什么在 ORDER BY 字段上还要加索引呢?

优化建议:

1. SQL 中,可以在 WHERE 子句和 ORDER BY 子句中使用索引,目的是在 WHERE 子句中 避免全表扫描 ,在 ORDER BY 子句 避免使用 FileSort 排序 。当然,某些情况下全表扫描,或者 FileSort 排序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引。

3. 无法使用 Index 时,需要对 FileSort 方式进行调优。

key_len为73,是因为用到了整个联合索引。(age是INT类型4个字节 + 非空1个字节,classid与age同理,name是VARCHAR20:20*3 = 60,再加上非空1个字节、可变长2个字节,60+1+2=63,总共63+5+5=73)。

先展示一下此时student表中都存在哪些索引,下面所说的索引失效是针对下面这张运行结果图中的索引而言的。

关于filesort的相关优化问题:👇👇👇

结论:

1. 两个索引同时存在, mysql 自动选择最优的方案。(对于这个例子, mysql 选择 idx_age_stuno_name)。但是, 随着数据量的变化,选择的索引也会随之变化的

2. 当【范围条件】和【 group by 或者 order by 】的字段出现二选一时,优先观察条件字段的过 滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段 上。反之,亦然。


5.GROUP BY分组优化

  • group by 使用索引的原则几乎跟order by一致 ,group by 即使没有过滤条件用到索引,也可以直接 使用索引。
  • group by 先排序再分组,遵照索引建的最佳左前缀法则
  • 当无法使用索引列,增大 max_length_for_sort_data sort_buffer_size 参数的设置
  • where效率高于having,能写在where限定的条件就不要写在having中了
  • 减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。order bygroup bydistinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。
  • 包含了order bygroup bydistinct这些查询的语句,where条件过滤出来的结果集请保持在1000以内,否则SQL会很慢。

6.LIMIT分页查询优化


7.优先考虑覆盖索引

理解方式一 :索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。 一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二 :非聚簇复合索引的一种形式,它包括在查询里的 SELECT JOIN WHERE 子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)。

简单说就是, 索引列 + 主键 包含 SELECT FROM 之间查询的列

好处:

1. 避免 Innodb 表进行索引的二次查询(回表)

2. 可以把随机 IO 变成顺序 IO 加快查询效率

弊端:

索引字段的维护 总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA,或者称为业务数据架构师的工作。

先确保student表中只有一个主键索引,下面做测试:👇👇👇


8.其它查询优化策略

8.1 EXISTS和IN的区分

8.2 COUNT(*)COUNT(具体字段)效率

8.3 关于SELECT(*)

8.4 LIMIT 1 对优化的影响

8.5 多使用COMMIT

本文来自网络,不代表软粉网立场,转载请注明出处:https://www.rfff.net/p/3188.html

作者: HUI

发表评论

您的电子邮箱地址不会被公开。

返回顶部