MySQL优化之索引优化-关联查询优化

171 阅读3分钟

开启掘金成长之旅!这是我参与「掘金日新计划 · 12 月更文挑战」的第11天,点击查看活动详情

前言

上篇我们学习了MySQL中的数据库优化之索引优化。有兴趣的小伙伴可以阅读(MySQL优化之索引优化(二))。
下面我们继续学习MySQL中的数据库优化之索引优化-关联查询优化。

索引优化

关联查询优化

采用左外连接

EXPLAIN SELECT SQL_NO_CACHE *
FROM type LEFT JOIN book
ON type.card = book.card;

此查询语句的type是ALL。较耗时。下面添加索引进行优化:

ALTER TABLE book ADD INDEX Y ( card);

EXPLAIN SELECT SQL_NO_CACHE *
FROM type LEFT JOIN book
ON type.card = book.card;

添加索引后,可以看到此查询语句的type变成了ref,rows也变成了1优化比较明显。这是由左连接的特性决定的。左连接LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。

采用内连接

EXPLAIN SELECT SQL_NO_CACHE *
FROM type INNER JOIN book
ON type.card = book.card;

此查询语句的type是ALL。较耗时。下面添加索引进行优化:

ALTER TABLE book ADD INDEX Y ( card);

EXPLAIN SELECT SQL_NO_CACHE *
FROM type INNER JOIN book
ON type.card = book.card;

添加索引后,可以看到此查询语句的type变成了ref,row也变成了1。

JOIN语句原理

如果直接使用JOIN语句,MySQL优化器可能会选择t1或t2作为驱动表,这样会影响我们分析SQL语句的执行过程。所以为了便于分析执行过程中的性能问题,我们使用straight_join让MySQL使用固定连接方式执行查询,这样优化器只会按照我们指定的方式去join。通常join前的表是驱动表,join后的表是被驱动表。

EXPLAIN SELECT *
FROM t1 STRAIGHT_JOIN t2
ON t1.a = t2.a;

上面语句的执行流程是:

  1. 从表t1中读入一行数据R;
  2. 从数据行R中,取出a字段到表t2里去查找;
  3. 取出表t2中满足条件的行,跟R组成一行,作为结果集的一部分;
  4. 重复执行步骤1到3,直到表t1的末尾循环结束。

这个过程是先遍历表t1,然后根据表t1中取出的每行数据中的a值,去表t2中查找满足条件的记录。在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引。

总结

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

小表概念
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成后,计算参与JOIN的各个字段的总数据量,数据量小的那个表,就是小表,应该作为驱动表。

今天先学习到这里,明天继续。