как MySQL реализует “группу”?
Я читал из MySQL Reference Manual и нахожу, что, когда он может взять использование индекса, он просто индексирует сканирование, другой он составит tmp таблицы и сделает вещи как filesort. И я также читал из другой статьи, которую "Группа" результатом отсортирует по группе столбцами по умолчанию, если "порядок пустым" добавленным пунктом, она не наденет filesort. Различие может быть найдено от, "объясните..." пункт. так моя проблема is:what является различием между "группой" пунктом, что с "порядком пустым указателем" и который не имеет? Я пытаюсь использовать профилирование для наблюдения то, что mysql делают на фоне и только видят результат как:
result for group clause without order by null:
|preparing | 0.000016 |
| Creating tmp table | 0.000048 |
| executing | 0.000009 |
| Copying to tmp table | 0.000109 |
**| Sorting result | 0.000023 |**
| Sending data | 0.000027 |
result for clause with "order by null":
preparing | 0.000016 |
| Creating tmp table | 0.000052 |
| executing | 0.000009 |
| Copying to tmp table | 0.000114 |
| Sending data | 0.000028 |
Таким образом, я предполагаю, какой MySQL делают, когда "порядок пустым указателем," добавил, он не использует filesort алгоритм, возможно, когда он составляет tmp таблицу, он использует индекс также, и затем используйте индекс, чтобы сделать группу операцией при завершении он просто считал результат строк таблицы и не сортирует результат.
Но мое самостоятельное суждение - то, что MySQL может использовать quicksort для сортировки объектов и затем группируется, таким образом, результат будет отсортирован также.
Любое ценившее мнение, спасибо.
3 ответа
Группировать по - группирует запись по некоторому столбцу. Например, у вас есть столбец «класс», и вы можете сгруппировать по этому столбцу, чтобы получить записи, сгруппированные на основе значений этого столбца.
Предложение GROUP BY позволяет использовать модификатор WITH ROLLUP, который вызывает добавление дополнительных строк к итоговым выводам. Эти строки представляют собой итоговые операции более высокого уровня (или супергрегатные). Таким образом, ROLLUP позволяет вам отвечать на вопросы на нескольких уровнях анализа с помощью одного запроса. Его можно использовать, например, для поддержки операций OLAP (онлайн-аналитическая обработка).
Предположим, что в таблице с именем sales есть столбцы года, страны, продукта и прибыли для записи рентабельности продаж:
CREATE TABLE sales ( year INT NOT NULL, {{1} } country VARCHAR (20) NOT NULL, product VARCHAR (32) NOT NULL, profit INT );
Содержание таблицы можно просуммировать за год с помощью простого ГРУППА ПО ГОДУ:
mysql> ВЫБРАТЬ год, СУММА (прибыль) ОТ продаж ГРУППА ПО году; + ------ + ------------- + | год | SUM (прибыль) | + ------ + ------------- + | 2000 | 4525 | | 2001 | 3010 | + ------ + ------------- +
Этот вывод показывает общую прибыль за каждый год, но если вы также хотите определить общей прибыли, суммированной за все годы, необходимо самостоятельно сложить отдельные значения или выполнить дополнительный запрос.
Или вы можете использовать ROLLUP, который обеспечивает оба уровня анализа с помощью одного запроса.Добавление модификатора WITH ROLLUP к предложению GROUP BY приводит к тому, что запрос создает другую строку, которая показывает общую сумму по всем значениям года:
mysql> SELECT year, SUM (прибыль) FROM sales GROUP BY year WITH ROLLUP; {{1 }} + ------ + ------------- + | год | SUM (прибыль) | + ------ + ------------- + | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | + ------ + ------------- +
Строка суперагрегата общего итога идентифицируется значением NULL в столбце года.
ROLLUP имеет более сложный эффект, когда имеется несколько столбцов GROUP BY. В этом случае каждый раз, когда происходит «разрыв» (изменение значения) в любом столбце группировки, кроме последнего, запрос создает дополнительную сводную строку суперагрегата.
Например, без ROLLUP сводная таблица продаж по годам, странам и продуктам может выглядеть следующим образом:
mysql> SELECT year, country, product, SUM (прибыль) - > ОТ продаж -> ГРУППА ПО году, стране, продукту; + ------ + --------- + --------- --- + ------------- + | год | страна | продукт | SUM (прибыль) | + ------ + --------- + ------------ + --------- ---- + | 2000 | Финляндия | Компьютер | 1500 | | 2000 | Финляндия | Телефон | 100 | | 2000 | Индия | Калькулятор | 150 | | 2000 | Индия | Компьютер | 1200 | | 2000 | США | Калькулятор | 75 | | 2000 | США | Компьютер | 1500 | | 2001 | Финляндия | Телефон | 10 | | 2001 | США | Калькулятор | 50 | | 2001 | США | Компьютер | 2700 | | 2001 | США | ТВ | 250 | + ------ + --------- + ------------ + ------------ - +
Вывод показывает сводные значения только на уровне анализа года / страны / продукта.При добавлении ROLLUP запрос создает несколько дополнительных строк:
mysql> SELECT год, страна, продукт, SUM (прибыль) -> FROM sales -> GROUP BY year, country, продукт С РОЛИКОМ; + ------ + --------- + ------------ + ---------- --- + | год | страна | продукт | SUM (прибыль) | + ------ + --------- + ------------ + --------- ---- + | 2000 | Финляндия | Компьютер | 1500 | | 2000 | Финляндия | Телефон | 100 | | 2000 | Финляндия | NULL | 1600 | | 2000 | Индия | Калькулятор | 150 | | 2000 | Индия | Компьютер | 1200 | | 2000 | Индия | NULL | 1350 | | 2000 | США | Калькулятор | 75 | | 2000 | США | Компьютер | 1500 | | 2000 | США | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Финляндия | Телефон | 10 | | 2001 | Финляндия | NULL | 10 | | 2001 | США | Калькулятор | 50 | | 2001 | США | Компьютер | 2700 | | 2001 | США | ТВ | 250 | | 2001 | США | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | + ------ + --------- + ------------ + ------------ - +
Для этого запроса добавление ROLLUP приводит к включению сводной информации на четырех уровнях анализа, а не только на одном. Вот как интерпретировать вывод ROLLUP:
*
Following each set of product rows for a given year and country, an extra summary row is produced showing the total for all products. These rows have the product column set to NULL.
*
Following each set of rows for a given year, an extra summary row is produced showing the total for all countries and products. These rows have the country and products columns set to NULL.
*
Finally, following all other rows, an extra summary row is produced showing the grand total for all years, countries, and products. This row has the year, country, and products columns set to NULL.
Другие соображения при использовании ROLLUP
В следующих элементах перечислены некоторые варианты поведения, характерные для реализации ROLLUP в MySQL:
Когда вы используете ROLLUP, вы также не можете использовать предложение ORDER BY отсортировать результаты. Другими словами, ROLLUP и ORDER BY исключают друг друга. Однако у вас все еще есть некоторый контроль над порядком сортировки. GROUP BY в MySQL сортирует результаты, и вы можете использовать явные ключевые слова ASC и DESC со столбцами, названными в списке GROUP BY, чтобы указать порядок сортировки для отдельных столбцов.(Итоговые строки более высокого уровня, добавленные ROLLUP, по-прежнему появляются после строк, из которых они вычисляются, независимо от порядка сортировки.)
LIMIT можно использовать для ограничения количества строк, возвращаемых клиенту. LIMIT применяется после ROLLUP, поэтому ограничение применяется к дополнительным строкам, добавленным ROLLUP. Например:
mysql> ВЫБРАТЬ год, страну, продукт, СУММУ (прибыль) -> ОТ продаж -> ГРУППА ПО году, стране, продукту С РОЛИКОМ - > ПРЕДЕЛ 5; + ------ + --------- + ------------ + ---------- --- + | год | страна | продукт | SUM (прибыль) | + ------ + --------- + ------------ + --------- ----+ | 2000 | Финляндия | Компьютер | 1500 | | 2000 | Финляндия | Телефон | 100 | | 2000 | Финляндия | NULL | 1600 | | 2000 | Индия | Калькулятор | 150 | | 2000 | Индия | Компьютер | 1200 | + ------ + --------- + ------------ + ------------ - +
Использование LIMIT с ROLLUP может привести к результатам, которые труднее интерпретировать, потому что у вас меньше контекста для понимания супергагрегированных строк.
Индикаторы NULL в каждой строке суперагрегата вырабатываются, когда строка отправляется клиенту. Сервер просматривает столбцы, указанные в предложении GROUP BY, следующее за крайним левым столбцом, значение которого изменилось. Для любого столбца в наборе результатов с именем, которое является лексическим соответствием любому из этих имен, его значение устанавливается в NULL. (Если вы укажете группировку столбцов по номеру столбца, сервер определяет, какие столбцы установить в NULL по номеру.)
Поскольку значения NULL в строках супергагрегата помещаются в набор результатов на таком позднем этапе обработки запроса , вы не можете проверить их как значения NULL в самом запросе. Например, вы не можете добавить в запрос HAVING product IS NULL, чтобы исключить из вывода все, кроме супергрегатных строк.
С другой стороны, значения NULL отображаются как NULL на стороне клиента и могут быть протестированы как таковые с использованием любого клиентского программного интерфейса MySQL.
mysql> select max(post_date),post_author from wp_posts
-> where id > 10 and id < 1000
-> group by post_author;
+———————+————-+
| max(post_date) | post_author |
+———————+————-+
| 2009-07-03 12:58:39 | 1 |
+———————+————-+
1 row in set (0.01 sec)
mysql> show profiles;
+———-+————+————————+
| Query_ID | Duration | Query |
+———-+————+————————+
| 1 | 0.00013200 | SELECT DATABASE() |
| 2 | 0.00030900 | show databases |
| 3 | 0.00030400 | show tables |
| 4 | 0.01180000 | select max(post_date),post_author from wp_posts where id > 10 and id < 1000 group by post_author |4 rows in set (0.00 sec)
mysql> show profile cpu,block io for query 4;
+———————-+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+———————-+———-+———-+————+————–+—————+
| starting | 0.000085 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000029 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000062 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.011386 | 0.004999 | 0.006999 | 0 | 0 |
| Sorting result | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000036 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
+———————-+———-+———-+————+————–+—————+
22 rows in set (0.00 sec)
mysql>
mysql>
mysql> select max(post_date),post_author from wp_posts
-> where id > 10 and id < 1000
-> group by post_author order by null;
+———————+————-+
| max(post_date) | post_author |
+———————+————-+
| 2009-07-03 12:58:39 | 1 |
+———————+————-+
1 row in set (0.01 sec)
mysql> show profiles;
+———-+————+—————–+
| Query_ID | Duration | Query
+———-+————+—————–+
|1 | 0.00013200 | SELECT DATABASE()
|2 | 0.00030900 | show databases
|3 | 0.00030400 | show tables
|4 | 0.01180000 | select max(post_date),post_author from wp_posts where id > 10 and id < 1000 group by post_author
|5 | 0.01177700 | select max(post_date),post_author from wp_posts where id > 10 and id < 1000 group by post_author order by null
5 rows in set (0.00 sec)
mysql> show profile cpu,block io for query 5;
+———————-+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+———————-+———-+———-+————+————–+—————+
| starting | 0.000097 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000065 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.011369 | 0.005999 | 0.004999 | 0 | 0 |
| Sending data | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
+———————-+———-+———-+————+————–+—————+
21 rows in set (0.00 sec)
Отсюда мы видим, что во второй части нет шага «Результат сортировки», поэтому это немного влияет на производительность.