Oracle mechanics

28.07.2009

MySQL: использование временных таблиц на диске для таблиц с TEXT/BLOB столбцами

Filed under: MySQL,optimizer — Игорь Усольцев @ 16:12
Tags: , ,

В документации MySQL указаны случаи, когда temporary tables не могут быть перенесены в память (Memory (HEAP) Storage Engine) и MySQL использует для временных таблиц MyISAM, первый из которых:

  • Присутствие TEXT или BLOB столбца в таблице

Не совсем понятно, где должен присутствовать LOB столбец- в списке SELECT, условиях WHERE, конструкциях ORDER BY, GROUP BY?

Интересно, в каких случаях MySQL будет создавать для временных данных таблицы на диске, что фиксируется статистикой Created_tmp_disk_tables для запросов к таблицам с TEXT или BLOB столбцами.

Попробуем, влияет ли наличие операции сортировки ORDER BY при запросе к таблице со столбцом типа TEXT на использование дисковых временных таблиц:

mysql> explain select active, count(ID)
    -> from my_table group by active
    -> order by null;
+-----------------+
| Extra           |
+-----------------+
| Using temporary |
+-----------------+
mysql> explain select active, count(ID)
    -> from my_table group by active
    -> order by id;
+---------------------------------+
| Extra                           |
+---------------------------------+
| Using temporary; Using filesort |
+---------------------------------+

Судя по плану — влияет — добавляется Using filesort, проверяем практически:

mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
mysql> select active, count(ID) from my_table group by active order by null;
2 rows in set (0.11 sec)
mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
mysql> select active, count(ID) from my_table group by active order by id;
2 rows in set (0.12 sec)
mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+

ORDER BY по не-LOB столбцу не создаёт временных таблиц на диске несмотря на обещанный планом filesort!

Также не создаётся временных таблиц на диске при использовании LOB столбца в списке SELECT:

mysql> select length(TEXT_COLUMN), count(id) from my_table
    -> group by length(TEXT_COLUMN)
    -> order by null;
mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+

и в условии WHERE:

mysql> select active, count(TEXT_COLUMN) from my_table
    -> where TEXT_COLUMN like '%1%'
    -> group by active order by null;
mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+

Пробуем ORDER BY по LOB столбцу:

mysql> explain select active, count(ID) from my_table group by active
    -> order by TEXT_COLUMN;
+---------------------------------+
| Extra                           |
+---------------------------------+
| Using temporary; Using filesort |
+---------------------------------+

План выполнения мало отличается от предыдущих, но …

mysql> select active, count(ID) from my_table group by active
    -> order by TEXT_COLUMN;
2 rows in set (0.52 sec)
mysql> show status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+

ORDER BY по LOB столбцу создаёт временную таблицу на диске, несмотря на то,что по логике запроса эта сортировка АБСОЛЮТНО БЕЗСМЫСЛЕННА!

Понятно, что это просто bug оптимизатора MySQL (возможно, вызванный достаточно большой степенью свободы при создании запросов), а цитата из документации в начале этой заметки позволяет этот bug назвать фичей :)

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: