[MySQL] mysql优化实例-为join表关联字段增加索引

最新推荐文章于 2022-08-11 16:59:58 发布

原创最新推荐文章于 2022-08-11 16:59:58 发布 · 379 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #sql #索引 #java

在排查查询语句效率时，发现join关联表时被驱动表未走索引而全表扫描。如特定sql语句中表b因字段rule_id无索引全表扫描，添加索引后，MySQL关联字段有索引走Index Nested - Loop Join，无索引走Block Nested - Loop Join。

在排查所有查询语句效率的过程中 , 发现了join关联表的时候 , 被驱动表没有走索引而是进行的全表扫描

实际的sql语句如下:

explain select a.* from audit_rules a left join audit_rules_detail b on a.id=b.rule_id where a.ent_id=23684

输出:

+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | ref  | idx_ent_id    | idx_ent_id | 4       | const |   12 |       |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL       | NULL    | NULL  |   35 |       |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+

看到表b是全表扫描 , 这是因为b的字段rule_id没有索引

增加上索引以后

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref          | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
|  1 | SIMPLE      | a     | ref  | idx_ent_id    | idx_ent_id  | 4       | const        |   12 |             |
|  1 | SIMPLE      | b     | ref  | idx_rule_id   | idx_rule_id | 4       | sinanet.a.id |    1 | Using index |

MySQL是只支持一种JOIN算法Nested-Loop Join(嵌套循环链接)当关联字段有索引时,走的是Index Nested-Loop Join(索引嵌套链接)
没有索引时会走,Block Nested-Loop Join比Simple Nested-Loop Join多了一个中间join buffer缓冲处理的过程

没有索引时:

有索引时