tuple - Qual è la differenza di prestazioni nella divisione relazionale MySQL(IN AND anziché IN OR)?



tuple dangling (1)

Poiché MySQL non dispone di un operatore di divisione relazionale incorporato, i programmatori devono implementarne uno proprio. Esistono due esempi principali di implementazioni che possono essere trovati in questa risposta qui.

Per i posteri li elencherò qui di seguito:

Utilizzando 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

L'avvertenza è che devi usare HAVING COUNT (DISTINCT perché i duplicati di termid essendo 2 per lo stesso documentid sarebbe un falso positivo e il COUNT deve eguagliare il numero di valori termid nella clausola IN.

Usando 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

Ma questo può essere un problema per la gestione dei criteri che cambia molto.

Di queste due tecniche di implementazione, quale offrirebbe le migliori prestazioni?


Ho apportato alcuni miglioramenti nella versione JOIN ; vedi sotto.

Io voto per l'approccio JOIN per la velocità. Ecco come l'ho determinato:

HAVING, versione 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) |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+--------------------------------------------------+

"Extra" sottolinea che ha deciso di affrontare GROUP BY e utilizzare INDEX(city, state) anche se INDEX(state, city) potrebbe avere senso.

HAVING, versione 2

Facendo passare a INDEX(state, city) produce:

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 |
+----+-------------+-----------+-------+-----------------------+------------+---------+------+------+------------------------------------------+

ADERIRE

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 |
+----+-------------+-------+------+-----------------------+------------+---------+--------------------+------+--------------------------+

È necessario solo INDEX(state, city) . I numeri dell'handler sono i più piccoli per questa formulazione, quindi deduco che è il più veloce.

Si noti come l'ottimizzatore abbia deciso da solo quale tabella iniziare, probabilmente a causa di

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

conclusioni

JOIN (senza la tabella t non necessaria) è probabilmente il più veloce. Inoltre questo indice composito è necessario: INDEX(state, city) .

Per tradurre nuovamente nel tuo caso d'uso:

city --> documentid
state --> termid

Avvertenza: YMMV perché la distribuzione dei valori per documentid e termid potrebbe essere molto diversa dal caso di test che ho usato.





relational-database