Блог

Есть два способа, которые я могу придумать, чтобы получить аналогичные результаты из нескольких таблиц. Один есть UNION , а другой есть JOIN . На все аналогичные вопросы по SO были даны ответы с помощью a UNION . Вот кодер, который я только что нашел:

 SELECT max(up.id) AS up, max(sc.id) AS sc, max(cl.id) AS cl
    FROM updates up, chat_staff sc, change_log cl
  

объясните:

  ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------------------------ 
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
 ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------------------------ 
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
 ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------------------------------ 
  

Мой вопрос — это лучше, чем следующее?

 SELECT "up.id" AS K, max(id) AS V FROM updates 
 UNION
SELECT "sc.id" AS K, max(id) AS V FROM chat_staff 
 UNION
SELECT "cl.id" AS K, max(id) AS V FROM change_log
  

объясните:

  ---- -------------- -------------- ------ --------------- ------ --------- ------ ------------------------------------- 
| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
 ---- -------------- -------------- ------ --------------- ------ --------- ------ ------ ------------------------------ 
|  1 | PRIMARY      | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
|  2 | UNION        | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
|  3 | UNION        | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
| NULL | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                              |
 ---- -------------- -------------- ------ --------------- ------ --------- ------ ------ ------------------------------ 
  

Оба этих метода просто прекрасны. На самом деле, у меня есть другой метод:

 SELECT
    IFNULL(maxidup,0) max_id_up,
    IFNULL(maxscup,0) max_sc_up,
    IFNULL(maxclup,0) max_cl_up
FROM
    (SELECT max(id) maxidup FROM updates)    up,
    (SELECT max(id) maxidsc FROM chat_staff) sc,
    (SELECT max(id) maxidcl FROM change_log) cl
;
  

Этот метод представляет три значения рядом, как в вашем первом примере. Он также показывает 0 в случае, если одна из таблиц пуста.

 mysql> DROP DATABASE IF EXISTS junk;
Query OK, 3 rows affected (0.11 sec)

mysql> CREATE DATABASE junk;
Query OK, 1 row affected (0.00 sec)

mysql> use junk
Database changed
mysql> CREATE TABLE updates (id int not null auto_increment primary key,x int);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE chat_staff LIKE updates;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE change_log LIKE updates;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO updates (x) VALUES (37),(84),(12);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO change_log (x) VALUES (37),(84),(12),(14),(35);
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    -> IFNULL(maxidup,0) max_id_up,
    -> IFNULL(maxidsc,0) max_sc_up,
    -> IFNULL(maxidcl,0) max_cl_up
    -> FROM
    -> (SELECT max(id) maxidup FROM updates)    up,
    -> (SELECT max(id) maxidsc FROM chat_staff) sc,
    -> (SELECT max(id) maxidcl FROM change_log) cl
    -> ;
 ----------- ----------- ----------- 
| max_id_up | max_sc_up | max_cl_up |
 ----------- ----------- ----------- 
|         3 |         0 |         5 |
 ----------- ----------- ----------- 
1 row in set (0.00 sec)

mysql> explain SELECT  IFNULL(maxidup,0) max_id_up,  IFNULL(maxidsc,0) max_sc_up, IFNULL(maxidcl,0) max_cl_up  FROM  (SELECT max(id) maxidup FROM updates)    up, (SELECT max(id) maxidsc FROM chat_staff) sc,  (SELECT max(id) maxidcl FROM change_log) cl;
 ---- ------------- ------------ -------- --------------- ------ --------- ------ ------ ------------------------------ 
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                        |
 ---- ------------- ------------ -------- --------------- ------ --------- ------ ------ ------------------------------ 
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                              |
|  1 | PRIMARY     | <derived3> | system | NULL          | NULL | NULL    | NULL |    1 |                              |
|  1 | PRIMARY     | <derived4> | system | NULL          | NULL | NULL    | NULL |    1 |                              |
|  4 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
|  3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No matching min/max row      |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
 ---- ------------- ------------ -------- --------------- ------ --------- ------ ------ ------------------------------ 
6 rows in set (0.02 sec)
  

В моем плане ОБЪЯСНЕНИЯ он Select tables optimized away похож на ваш. Почему ?

Поскольку id индексируется во всех таблицах, индекс используется для извлечения max (id), а не таблицы. Таким образом, Select tables optimized away это правильный ответ.

Шесть из одного, полдюжины из другого. То, как вы представляете данные оттуда, зависит исключительно от ваших личных предпочтений.

ОБНОВЛЕНИЕ 2011-10-20 15:32 EDT

Вы прокомментировали: знаете ли вы, как блокировка таблицы может поставить под угрозу это? Допустим, одна из рассматриваемых таблиц заблокирована. Будет ли этот запрос блокировать два других и держать их заблокированными до тех пор, пока не будет освобожден первый?

Это будет зависеть от механизма хранения. Если все рассматриваемые таблицы являются MyISAM, определенная возможность, поскольку MyISAM выполняет полную блокировку таблицы при ВСТАВКЕ, ОБНОВЛЕНИИ, УДАЛЕНИИ. Если три таблицы являются InnoDB, у вас есть преимущество MVCC для обеспечения изоляции транзакций. Это позволило бы каждому просматривать данные в определенный момент времени. Помимо DDL и БЛОКИРОВКИ ТАБЛИЦ explcit для InnoDB, ваш запрос не должен быть заблокирован.

На самом деле, хотя они похожи, есть небольшая разница. Первый дает вам таблицу с одной строкой и тремя столбцами (со значениями, идущими «поперек»), а второй дает вам таблицу с тремя строками и двумя столбцами (со значениями, идущими «вниз»).

При условии, что вы довольны обработкой или просмотром этих данных в любой форме, это, вероятно, будет зависеть от производительности.

По моему опыту (и это не имеет ничего общего конкретно с MySQL), последний запрос, вероятно, будет лучше. Это потому, что СУБД, с которыми я работаю, могут запускать подобные запросы параллельно для повышения эффективности, объединяя их при завершении всех. Тот факт, что они находятся в разных таблицах, означает, что конфликт блокировок между ними будет равен нулю.

Возможно, механизм анализа запросов СУБД мог бы выполнить аналогичную оптимизацию для первого запроса, но это потребовало бы гораздо большего интеллекта, чем я видел у большинства из них.

Один быстрый момент, если вы используете union all вместо just union , вы указываете базе данных не удалять повторяющиеся строки. В этом случае вы не получите никаких дубликатов из-за K того, что столбец отличается для всех трех подзапросов.

Но, как и во всех оптимизациях, измеряйте, а не угадывайте!Конечно, не принимайте за Евангелие разглагольствования случайных пользователей Интернета (да, даже меня).

Соберите различные таблицы-кандидаты со свойствами, которые вы, вероятно, будете иметь в рабочей среде, и сравните производительность каждой из них.