MySQL关系分区（IN AND而不是IN OR）实现的性能差异是什么？ [英] What is the performance difference in MySQL relational division (IN AND instead of IN OR) implementations?

查看：157
本文介绍了MySQL关系分区（IN AND而不是IN OR）实现的性能差异是什么？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

因为MySQL没有内置的关系分割运算符，程序员必须实现自己的。 此回答在这里有两个实施示例。



对于后代，我将在下面列出：


使用GROUP BY / HAVING 





  SELECT t.documentid 
 FROM TABLE t 
 WHERE t.termid IN ，3）
 GROUP BY t.documentid 
 HAVING COUNT（DISINCT t.termid）= 3

注意，你必须使用HAVING COUNT（DISTINCT，因为
重复termid为2对于同一个documentid将是一个假
正数，并且COUNT必须等于



使用JOINs 





  SELECT t.documentid 
 FROM TABLE t 
 JOIN TABLE x ON x.termid = t.termid 
 AND x.termid = 1 
 JOIN TABLE y ON y.termid = t.termid 
 AND y.termid = 2 
 JOIN TABLE z ON z.termid = t.termid 
 AND z.termid = 3

但是这个对于处理很多变化的标准来说可能是一个痛苦。



解决方案 div>

我在 JOIN 版本中做了一些改进;见下文。



我投票赞成JOIN方法的速度。以下是我的决定方式：

 
  mysql> FLUSH状态; 
 mysql> SELECT city 
  - > FROM us_vch200 
  - > WHERE state IN（'IL'，'MO'，'PA'）
  - > GROUP BY城市
  - > HAVING计数（DISTINCT状态）> = 3; 
 + ------------- + 
 |城市| 
 + --------- + 
 | Springfield | 
 |华盛顿| 
 + ------------- + 
 mysql> SHOW SESSION STATUS LIKE'Handler％'; 
 + ---------------------------- + ------- + 
 | Variable_name |价值| 
 + ---------------------------- + ------- + 
 | Handler_external_lock | 2 | 
 | Handler_read_first | 1 | 
 | Handler_read_key | 2 | 
 | Handler_read_last | 1 | 
 | Handler_read_next | 4175 | - 全索引扫描
 
（etc）
 
 + ---- + ------------- + ------ ----- + ------- + ----------------------- + ------------ + --------- + ------ + ------ + ------------------------- ------------------------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + ------------- + ----------- + ------- + ------ ----------------- + ------------ + --------- + ------ +  - ---- + --------------------------------------------- ----- + 
 | 1 | SIMPLE | us_vch200 |范围| state_city，city_state | city_state | 769 | NULL | 4176 |使用where;使用分组（扫描）|的索引
 + ---- + ------------- + ----------- + ------- + ------ ----------------- + ------------ + --------- + ------ +  - ---- + --------------------------------------------- ----- +

'Extra'指出它决定处理 GROUP BY 并使用 INDEX（城市，州），即使 INDEX



它切换到 INDEX（州，城市）产生：

  mysql> FLUSH状态; 
 mysql> SELECT city 
  - > FROM us_vch200 IGNORE INDEX（city_state）
  - > WHERE state IN（'IL'，'MO'，'PA'）
  - > GROUP BY城市
  - > HAVING计数（DISTINCT状态）> = 3; 
 + ------------- + 
 |城市| 
 + ------------- + 
 | Springfield | 
 |华盛顿| 
 + ------------- + 
 mysql> SHOW SESSION STATUS LIKE'Handler％'; 
 + ---------------------------- + ------- + 
 | Variable_name |价值| 
 + ---------------------------- + ------- + 
 | Handler_commit | 1 | 
 | Handler_external_lock | 2 | 
 | Handler_read_key | 401 | 
 | Handler_read_next | 398 | 
 | Handler_read_rnd | 398 | 
（etc）
 
 + ---- + ------------- + ----------- + --- ---- + ----------------------- + ------------ + -------- -  + ------ + ------ + ---------------------------------- -------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + ------------- + ----------- + ------- + ------ ----------------- + ------------ + --------- + ------ +  - ---- + ------------------------------------------ + 
 | 1 | SIMPLE | us_vch200 |范围| state_city，city_state | state_city | 2 | NULL | 397 |使用where;使用索引;使用filesort | 
 + ---- + ------------- + ----------- + ------- + ------ ----------------- + ------------ + --------- + ------ +  - ---- + ------------------------------------------ +

JOIN 

  mysql> SELECT x.city 
  - > FROM us_vch200 x 
  - > JOIN us_vch200 y ON y.city = x.city AND y.state ='MO'
  - > JOIN us_vch200 z ON z.city = x.city AND z.state ='PA'
  - > WHERE x.state ='IL'; 
 + ------------- + 
 |城市| 
 + ------------- + 
 | Springfield | 
 |华盛顿| 
 + ------------- + 
集合中的2行（0.00秒）
 
 mysql> SHOW SESSION STATUS LIKE'Handler％'; 
 + ---------------------------- + ------- + 
 | Variable_name |价值| 
 + ---------------------------- + ------- + 
 | Handler_commit | 1 | 
 | Handler_external_lock | 6 | 
 | Handler_read_key | 86 | 
 | Handler_read_next | 87 | 
（etc）
 + ---- + ------------- + ------- + ------ + ---- ------------------- + ------------ + --------- + ------- ------------- + ------ + -------------------------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + ------------- + ------- + ------ + ----------- ------------ + ------------ + --------- + -------------- ------ + ------ + -------------------------- + 
 | 1 | SIMPLE | y | ref | state_city，city_state | state_city | 2 | const | 81 |使用where;使用索引| 
 | 1 | SIMPLE | z | ref | state_city，city_state | state_city | 769 | const，world.y.city | 1 |使用where;使用索引| 
 | 1 | SIMPLE | x | ref | state_city，city_state | state_city | 769 | const，world.y.city | 1 |使用where;使用索引| 
 + ---- + ------------- + ------- + ------ + ----------- ------------ + ------------ + --------- + -------------- ------ + ------ + -------------------------- +

只需要 INDEX（州，城市）。



请注意优化器如何构建自己的头脑，开始的表，可能是由于

  + ------- + ---------- + 
 |状态| COUNT（*）| 
 + ------- + ---------- + 
 | IL | 221 | 
 | MO | 81 | - 最小
 | PA | 96 | 
 + ------- + ---------- +

结论



JOIN t table）可能是最快的。此外，还需要此复合索引： INDEX（州，城市）。



要转换回您的用例： 

  city  - > documentid 
 state  - > termid

Caveat：YMMV，因为documentid和termid的值的分布可能与测试用例我用过。


Because MySQL does not have a built in relational division operator, programmers must implement their own. There are two leading examples of implementations which can be found in this answer here.

For posterity I'll list them below:

Using GROUP BY/HAVING

SELECT t.documentid
FROM TABLE t
WHERE t.termid IN (1,2,3)
GROUP BY t.documentid
HAVING COUNT(DISINCT t.termid) = 3

The caveat is that you have to use HAVING COUNT(DISTINCT because duplicates of termid being 2 for the same documentid would be a false positive. And the COUNT has to equal the number of termid values in the IN clause.

Using JOINs

SELECT t.documentid
FROM TABLE t
JOIN TABLE x ON x.termid = t.termid
              AND x.termid = 1
JOIN TABLE y ON y.termid = t.termid
              AND y.termid = 2
JOIN TABLE z ON z.termid = t.termid
              AND z.termid = 3

But this one can be a pain for handling criteria that changes a lot.

Of these two implementation techniques, which one would offer the best performance?

解决方案

I made some improvements in the JOIN version; see below.

I vote for the JOIN approach for speed. Here's how I determined it:

HAVING, version 1

mysql> FLUSH STATUS;
mysql> SELECT city
    ->     FROM us_vch200
    ->     WHERE state IN ('IL', 'MO', 'PA')
    ->     GROUP BY city
    ->     HAVING count(DISTINCT state) >= 3;
+-------------+
| city        |
+-------------+
| Springfield |
| Washington  |
+-------------+
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_external_lock      | 2     |
| Handler_read_first         | 1     |
| Handler_read_key           | 2     |
| Handler_read_last          | 1     |
| Handler_read_next          | 4175  | -- full index scan

(etc)

+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+
| id | select_type | table     | type  | possible_keys         | key        | key_len | ref  | rows | Extra                                            |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | us_vch200 | range | state_city,city_state | city_state | 769     | NULL | 4176 | Using where; Using index for group-by (scanning) |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+

The 'Extra' points out that it decided to tackle the GROUP BY and use INDEX(city, state) even though INDEX(state, city) might make sense.

HAVING, version 2

Making it switch to INDEX(state, city) yields:

mysql> FLUSH STATUS;
mysql> SELECT city
    ->     FROM us_vch200  IGNORE INDEX(city_state)
    ->     WHERE state IN ('IL', 'MO', 'PA')
    ->     GROUP BY city
    ->     HAVING count(DISTINCT state) >= 3;
+-------------+
| city        |
+-------------+
| Springfield |
| Washington  |
+-------------+
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_external_lock      | 2     |
| Handler_read_key           | 401   |
| Handler_read_next          | 398   |
| Handler_read_rnd           | 398   |
(etc)

+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+
| id | select_type | table     | type  | possible_keys         | key        | key_len | ref  | rows | Extra                                    |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | us_vch200 | range | state_city,city_state | state_city | 2       | NULL |  397 | Using where; Using index; Using filesort |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+

JOIN

mysql> SELECT x.city
    -> FROM us_vch200 x
    -> JOIN us_vch200 y ON y.city= x.city AND y.state = 'MO'
    -> JOIN us_vch200 z ON z.city= x.city AND z.state = 'PA'
    -> WHERE                                  x.state = 'IL';
+-------------+
| city        |
+-------------+
| Springfield |
| Washington  |
+-------------+
2 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_external_lock      | 6     |
| Handler_read_key           | 86    |
| Handler_read_next          | 87    |
(etc)    
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys         | key        | key_len | ref                | rows | Extra                    |
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+
|  1 | SIMPLE      | y     | ref  | state_city,city_state | state_city | 2       | const              |   81 | Using where; Using index |
|  1 | SIMPLE      | z     | ref  | state_city,city_state | state_city | 769     | const,world.y.city |    1 | Using where; Using index |
|  1 | SIMPLE      | x     | ref  | state_city,city_state | state_city | 769     | const,world.y.city |    1 | Using where; Using index |
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+

Only INDEX(state, city) is needed. The Handler numbers are the smallest for this formulation, so I deduce that it is the fastest.

Notice how the optimizer made up its own mind which table to start with, probably due to 

+-------+----------+
| state | COUNT(*) |
+-------+----------+
| IL    |      221 |
| MO    |       81 |  -- smallest
| PA    |       96 |
+-------+----------+

Conclusions

JOIN (without the unnecessary t table) is probably the fastest. Plus this composite index is needed: INDEX(state, city).

To translate back to your use case:

city --> documentid
state --> termid

Caveat: YMMV because the distribution of values for documentid and termid could be quite different than the test case I used.

这篇关于MySQL关系分区（IN AND而不是IN OR）实现的性能差异是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆