5.5.50-MariaDB的索引为什么在 in子句中无法生效,MySQL可以?MariaDB不是完全兼容MySQL吗?
比如如下我有两个表:
表一:t1,有1000行数据。
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| uin | int(10) unsigned | YES | MUL | NULL | |
+----------+------------------+------+-----+---------+-------+
表一:t2有1W行数据。
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| uin | int(10) unsigned | YES | MUL | NULL | |
| name | varchar(32) | YES | | NULL | |
+----------+------------------+------+-----+---------+-------+
在MariaDB中,如下select语句的explain性能分析如下:
MariaDB>explain select count(*) from t2 where uin in(select uin from t1);
+------+-------+------+-------+------+------+---------+------+-------+-----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+------+-------+-------+-----+------+-------+------+-----+----------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1000 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 76864191 | Using where; Using join buffer (flat, BNL join) |
| 2 | MATERIALIZED | t1 | index | uin | uin | 5 | NULL | 1000 | Using index |
+------+-------+-----+-------+----+------+---------+------+---------+-------+
mysql如下select语句的explain性能分析如下:
mysql>explain select count(*) from t2 where uin in(select uin from t1);
+----+--------+-------+---------+-------+------+------+------+-------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extr |
+----+-------+--------+----------+-------+------+-------+------+-----+-----+
| 1 | PRIMARY | t2| ALL | NULL | NULL | NULL | NULL | 10000 | Using where |
| 2 | DEPENDENT SUBQUERY | t1| index_subquery | uin| uin | 5 | func | 1 | Using index; Using where |
+----+---------+------+-------+------+------+------+------+------+------+
可以看出MySQL中index在where in语句中是生效的,利于查询效率,但是在MariaDB中并非如此,只能使用inner join来替换 where in,为什么?