在排查所有查询语句效率的过程中,发现了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 | +----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+ | ref | idx_ent_id | idx_ent_id | 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缓冲处理的过程
没有索引时:
有索引时
