在排查所有查詢語句效率的過程中 , 發(fā)現(xiàn)了join關(guān)聯(lián)表的時(shí)候 , 被驅(qū)動(dòng)表沒有走索引而是進(jìn)行的全表掃描
實(shí)際的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是全表掃描 , 這是因?yàn)閎的字段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（嵌套循環(huán)鏈接）
當(dāng)關(guān)聯(lián)字段有索引時(shí),走的是Index Nested-Loop Join(索引嵌套鏈接)
沒有索引時(shí)會(huì)走,Block Nested-Loop Join比Simple Nested-Loop Join多了一個(gè)中間join buffer緩沖處理的過程
沒有索引時(shí):
有索引時(shí)
聯(lián)系客服
