假如Account表有一个主键UUID, 那么下面这个SQL明显是应该很快返回的对吧
mysql> EXPLAIN select * from ACCOUNT WHERE UUID IN('43d92fe6-2379-4697-9ad9-74e14bb6e3e7');
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | ACCOUNT | const | PRIMARY | PRIMARY | 110 | const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)
加入我要去join另外一个表ACCOUNT_LINK, UUID在这张表上也是有索引的, 那么也应该很快吧?
mysql> EXPLAIN select * from ACCOUNT INNER JOIN ACCOUNT_LINK ON ACCOUNT.UUID=ACCOUNT_LINK.UUID WHERE AUTH_DOMAIN='snda' and ACCOUNT_LINK.LOGINNAME='danieltest001';
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+---------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+---------------------------+------+-------------+
| 1 | SIMPLE | ACCOUNT_LINK | ref | PRIMARY,idx_loginname,IDX_ACC_LINK_DOMAIN_LOGINNAME | idx_loginname | 272 | const | 1 | Using where |
| 1 | SIMPLE | ACCOUNT | eq_ref | PRIMARY | PRIMARY | 110 | account.ACCOUNT_LINK.UUID | 1 | |
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+---------------------------+------+-------------+
2 rows in set (0.05 sec)
如果我只是把Join改成IN, 也应该很快吧? 因为sub query 只返回一条记录, 区分度非常高, 外面的查询正好又可以用索引. 但是, MySQL会先查外面的表, 太逆天了吧?
mysql> EXPLAIN select * from ACCOUNT WHERE UUID IN(select UUID from ACCOUNT_LINK WHERE AUTH_DOMAIN='snda' and LOGINNAME='danieltest001');
+----+--------------------+--------------+-----------------+-----------------------------------------------------+---------+---------+------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------+-----------------+-----------------------------------------------------+---------+---------+------------+---------+-------------+
| 1 | PRIMARY | ACCOUNT | ALL | NULL | NULL | NULL | NULL | 8493047 | Using where |
| 2 | DEPENDENT SUBQUERY | ACCOUNT_LINK | unique_subquery | PRIMARY,idx_loginname,IDX_ACC_LINK_DOMAIN_LOGINNAME | PRIMARY | 292 | func,const | 1 | Using where |
+----+--------------------+--------------+-----------------+-----------------------------------------------------+---------+---------+------------+---------+-------------+
2 rows in set (0.00 sec)
博文
