恋喵大鲤鱼 2018-07-07 12:50 采纳率: 100%
浏览 898
已结题

5.5.50-MariaDB的索引为什么在 where in子句中无法生效,MySQL可以?

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,为什么?

  • 写回答

1条回答 默认 最新

  • devmiao 2018-07-07 15:40
    关注
    评论

报告相同问题?

悬赏问题

  • ¥15 我这模型写的不对吗?为什么lingo解出来的下面影子价格这一溜少一个变量
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波