qq_36100464
qq_36100464
采纳率100%
2019-02-27 15:05

mysql5.7用EXPLAIN分析sql时关于索引的疑问

已采纳

分析sql及表结构如下
MySQL > EXPLAIN SELECT COUNT(UA.id) AS tp_count FROM ck_user_audit UA RIGHT JOIN ck_user AS U ON U.id = UA.uid LIMIT 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
*| 1 | SIMPLE | U | NULL | index | NULL | pid | 5 | NULL | 23003 | 100.00 | Using index |
| 1 | SIMPLE | UA | NULL | ALL | NULL | NULL | NULL | NULL | 3304 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
MySQL > desc ck_user;
+-----------------+---------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| phone | char(50) | YES | MUL | NULL | |
| pid | int(10) | YES | MUL | NULL | |

| email | char(100) | YES | MUL | NULL | |

执行sql用的是id这个主键,为什么分析出来却显示用的是pid这个索引?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • qq_36100464 qq_36100464 2年前

    图片说明可以用这个来解释

    点赞 评论 复制链接分享

相关推荐