version : 5.7, from 8.2.1.14 ORDER BY Optimization

В этом разделе описывается, когда MySQL может использовать индекс для удовлетворения предложения ORDER BY, сортировка файлов, когда индекс нельзя использовать, и информация о плане выполнения об ORDER BY в оптимизаторе.

Заказ по оператору может иметь различия в выполнении с ограничением или без него. Посмотреть детали8.2.1.17 LIMIT Query Optimization.

Реализовать порядок с помощью индекса

В некоторых случаях MySQL может использовать индекс, чтобы выполнить предложение ORDER BY и избежать дополнительной сортировки, связанной с выполнением операции сортировки файлов.

Хотя ORDER BY не соответствует индексу в точности, индекс все равно будет использоваться, пока в предложении WHERE все неиспользуемые части индекса (в случае индекса с несколькими полями — объединенный индекс) и все ORDER BY fields Все постоянны, нет проблем, пойдет в индекс вместо файловой сортировки.

Здесь у нас есть таблица tx_order,

CREATE TABLE `tx_order` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT ,
  `serial_number` varchar(255) NOT NULL ,
  `order_status` int unsigned DEFAULT 0 NOT NULL ,
  `market_id` varchar(10) DEFAULT NULL ,
  `market_name` varchar(255) DEFAULT NULL ,
  `shop_id` varchar(50) DEFAULT NULL ,
  `shop_name` varchar(100) DEFAULT NULL ,
  `mobile` varchar(64) DEFAULT NULL ,
  `create_date` datetime DEFAULT NULL ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2333702 DEFAULT CHARSET=utf8;

и добавьте индекс

alter table tx_order add index idx_market_date(market_id,create_date);

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

В следующем SQL оптимизатор использует индекс idx_market_date, чтобы избежать сканирования таблицы.

desc select market_id,create_date from tx_order.tx_order order by market_id,create_date;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

Однако все поля запроса в этом предложении sql находятся в индексе, если поле запроса не включено в индекс, например, «выберите market_id, create_date, market_name». В этом случае сканирование всего индекса и поиск в строках таблицы столбцов, не входящих в индекс, может быть более затратным, чем сканирование таблицы, и оптимизатор может не использовать индекс.

desc select market_id,create_date,market_name from tx_order.tx_order order by market_id,create_date;

1	SIMPLE	tx_order		ALL					1671956	100	Using filesort

В InnoDB мы знаем, что сам первичный ключ (кластеризованный индекс) является частью индекса, и этот индекс будет использоваться в следующем запросе.

desc select id,market_id,create_date from tx_order.tx_order order by market_id,create_date;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

В следующем случае, когда поле в индексе является константой в предложении where, а производительность индекса диапазона, сгенерированного предложением where, намного выше, чем производительность сканирования таблицы, такой запрос выберет индекс вместо скан таблицы.

desc select market_id,create_date from tx_order.tx_order where  market_id = '1009' order by create_date;

1	SIMPLE	tx_order		ref	idx_market_date	idx_market_date	33	const	170398	100	Using where; Using index

Следующие два SQL-запроса являются особыми, и вы также можете сравнить предыдущий порядок с помощью операторов ... asc. Глядя на результаты выполнения ниже, мы можем подумать, почему. При добавлении индекса временно невозможно указать порядок полей, изменить таблицу tx_order добавить индекс idx_market_date(market_id asc, create_date desc), хотя этот синтаксис поддерживается, но текущая версия MySQL не поддерживает никакой логики. в порядке возрастания. В объединенном индексе запросы сортируются по полям в индексе.Если сортировка несовместима, оптимизатор все равно частично просканирует таблицу.

desc select market_id,create_date from tx_order.tx_order order by market_id desc ,create_date desc ;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

desc select market_id,create_date from tx_order.tx_order order by market_id asc ,create_date desc ;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index; Using filesort

Индекс диапазона в предложении where следующего запроса лучше, чем сканирование таблицы, и оптимизатор выберет индекс для разрешения порядка.

desc select market_id,create_date from tx_order.tx_order where market_id > '1009' order by market_id asc;

1	SIMPLE	tx_order		range	idx_market_date	idx_market_date	33		835978	100	Using where; Using index

desc select market_id,create_date from tx_order.tx_order where market_id < '1009' order by market_id desc;

1	SIMPLE	tx_order		range	idx_market_date	idx_market_date	33		230966	100	Using where; Using index

В следующем запросе order by больше не является market_id, но market_id всех строк запроса является константой, поэтому он по-прежнему будет использоваться в порядке индексного анализа.

desc select market_id,create_date from tx_order.tx_order where market_id = '1009' and create_date>'2018-01-01' order by create_date desc;

1	SIMPLE	tx_order		range	idx_market_date	idx_market_date	39		94002	100	Using where; Using index

В некоторых случаях, несмотря на то, что MySQL использует индекс при обработке условия where, он не может использовать индекс для анализа порядка, см. следующий пример.

Индекс, используемый заказом, не является непрерывным, и MySQL все равно будет сканировать таблицу при разборе заказа.У меня здесь есть индекс idx_market_id (market_id, order_status, create_date), и я вижу результат выполнения sql ниже.

desc select market_id,create_date from tx_order.tx_order where  market_id='1009' order by market_id ,create_date ;

1	SIMPLE	tx_order		ref	idx_market_id,idx_market_type_create_date	idx_market_id	33	const	138084	100	Using where; Using index; Using filesort

Смешанная сортировка по возрастанию, убыванию

desc select market_id,create_date from tx_order.tx_order order by market_id asc ,create_date desc;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index; Using filesort

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

desc select mobile from tx_order.tx_order order by  abs(mobile);

1	SIMPLE	tx_order		index		idx_mobile	768		1671956	100	Using index; Using filesort

В многотабличном ассоциативном запросе столбцы в ORDER BY не все из 1-й непостоянной таблицы, используемой для поиска строк. (Это первая таблица в выходных данных EXPLAIN без константного типа соединения).

desc select a.market_id from tx_order.tx_order a ,tx_order_item b where a.id = b.order_id and a.market_id = '1009'  order by a.market_id,b.sku;

1	SIMPLE	b		ALL	idx_order_create				1	100	Using filesort
1	SIMPLE	a		eq_ref	PRIMARY,idx_market_date	PRIMARY	8	tx_order.b.order_id	1	10.19	Using where

Существуют разные выражения ORDER BY и GROUP BY.

desc select market_id,create_date from tx_order.tx_order   group by market_id,create_date order by create_date;

1	SIMPLE	tx_order		index	idx_market_date	idx_market_date	39		1671956	100	Using index; Using temporary; Using filesort

Для индексов, указывающих длину индекса сортировки. В этом случае индекс не может полностью определить порядок сортировки, и его необходимо отсортировать с помощью файловой сортировки. Например, при индексировании таблицы alter tx_order добавляется индекс idx_mobile(mobile(5)); однако mobile varchar(64).

desc select mobile from tx_order.tx_order order by mobile desc ;

1	SIMPLE	tx_order		ALL					1671956	100	Using filesort

В некоторых случаях тип используемого табличного индекса не поддерживает порядок строк. Это относится, например, к HASH-индексам таблиц HEAP.
На доступность отсортированных индексов может повлиять использование псевдонимов столбцов.

В приведенном ниже утверждении порядок изменен, и индекс не будет использоваться.

desc select abs(market_id) as aa from tx_order.tx_order order by aa;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index; Using filesort

Однако в следующем операторе, несмотря на то, что поле запроса имеет псевдоним, реальное поле сортировки по-прежнему является полем в индексе, поэтому при сортировке по-прежнему используется индекс.

desc select abs(market_id) as aa from tx_order.tx_order order by market_id;

1	SIMPLE	tx_order		index		idx_market_date	39		1671956	100	Using index

По умолчанию для оператора типа «группировать по col2, col2,...» MySQL также будет включать «упорядочить по col2, col2,...», что эквивалентно показанному вами ускорению «упорядочить по col2, col2, . .." sort, и в этом случае обработка оптимизатора не приводит к снижению производительности.

Для этого случая по умолчанию, если вы хотите обойти порядок по умолчанию, вы можете использовать порядок по нулю, чтобы избежать его, например:

desc select market_id,count(market_id) from tx_order.tx_order group by market_id order by null ;

Оптимизатор может по-прежнему использовать сортировку для реализации операций группировки. ORDER BY NULL подавляет порядок результата, отличный от предыдущего порядка операции группировки для определения результата.

Уведомление

GROUP BY по умолчанию сортирует неявно (то есть GROUP BY без столбца ASC или индикатора DESC для столбца). Однако не рекомендуется полагаться на неявное упорядочение GROUP BY (то есть упорядочение без индикатора ASC или DESC) или явное упорядочение GROUP BY (то есть с использованием явного индикатора ASC или DESC для GROUP BY в столбце). Чтобы сгенерировать заданный порядок сортировки, необходимо указать предложение ORDER BY.

Сортировка с файловой сортировкой

Когда сортировку по индексу нельзя использовать, MySQL использует сортировку по файлам для сканирования таблицы для сортировки набора результатов, и соответствующая сортировка по файлам создает дополнительную фазу сортировки во всем процессе запроса.

Для поддержки файловой сортировки реализация оптимизатора выделит определенный объем памяти в области sort_buffer_size, эта область памяти является эксклюзивной для каждого сеанса, и значение этой переменной можно изменить.

Если набор данных сортировки файлов слишком велик для сортировки в памяти, оптимизатор будет использовать диск в качестве временного файла для сортировки. Некоторые запросы особенно подходят для сортировки в памяти для завершения операции сортировки файлов, например, оптимизатор может эффективно использовать сортировку в памяти без необходимости реализации временного файла. Например

desc select * from tx_order.tx_order order by market_name desc limit 10;

1	SIMPLE	tx_order		ALL					1671956	100	Using filesort

Пример использования временного

desc select market_name from tx_order.tx_order order by RAND() desc limit 10;

1	SIMPLE	tx_order		ALL					1671956	100	Using temporary; Using filesort

Влияет на порядок путем оптимизации

Для медленных запросов файловой сортировки можно попробовать модифицировать скаляр max_length_for_sort_data для достижения эффекта, для управления точкой срабатывания алгоритма выбора файловой сортировки можно попробовать понизить значение max_length_for_sort_data. (Если значение max_length_for_sort_data увеличивается, а использование диска увеличивается, а использование процессора снижается, ознакомьтесь с подробностями.Оптимизация сортировки Mysql и использование индекса (перенос).

Чтобы улучшить скорость ORDER BY, проверьте, можете ли вы заставить MySQL использовать индекс вместо дополнительной стадии сортировки. Если это невозможно, попробуйте следующие стратегии:

Увеличьте значение переменной sort_buffer_size. В идеале значение должно быть достаточно большим, чтобы поместить весь результирующий набор в буфер сортировки (чтобы избежать записи на диск и проходов слияния), но, по крайней мере, оно должно быть достаточно большим, чтобы вместить 15 кортежей. (Можно объединить до 15 временных дисковых файлов, в каждом из которых должно быть место в памяти как минимум для одного кортежа.)

Учтите, что на размер значений столбцов, хранящихся в буфере сортировки, влияет значение системной переменной max_sort_length. Например, если кортеж хранит значения для столбца с длинной строкой и вы увеличиваете значение max_sort_length, размер кортежа буфера сортировки также увеличивается и может потребовать увеличения sort_buffer_size. Для значений столбцов, вычисляемых как строковые выражения (например, вызывающих строковые функции), алгоритм сортировки файлов не может указать максимальную длину значения выражения, поэтому должен назначать max_sort_length байтов для каждого кортежа.

Чтобы отслеживать количество проходов слияния (слияния временных файлов), проверьте переменную состояния Sort_merge_passes.
Увеличьте значение переменной read_rnd_buffer_size, чтобы читать больше строк за раз.
Измените системную переменную tmpdir, чтобы она указывала на выделенную файловую систему с большим количеством свободного места. Значение переменной может перечислять несколько путей, используемых в циклическом режиме; вы можете использовать эту функцию для распределения нагрузки по нескольким каталогам. : разделяет пути символом двоеточия ( ) в Unix и ; разделяет пути символом точки с запятой ( ) в Windows. Пути должны называть каталоги в файловых системах, расположенных на разных физических дисках, а не разные разделы на одном диске.

Просмотр разбора sql через план выполнения

Используйте EXPLAIN (см.8.8.1 Optimizing Queries with EXPLAIN), может проверить, может ли MySQL использовать индекс для разрешения предложения ORDER BY.

Если выходной дополнительный столбец EXPLAIN не содержит Использование файловой сортировки, используется индекс, а файловая сортировка не выполняется.
Если в выходном столбце Extra EXPLAIN содержится значение Using filesort, индекс не используется и выполняется файловая сортировка.

Кроме того, трассировка оптимизатора может быстро выводить информацию filesort_summary, когда выполняется сортировка файлов. Например:

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

Для трассировки MySQL см.Chapter 8 Tracing the Optimizer.

Суммировать

Чтобы написать эффективный и надежный запрос на сортировку, вам необходимо понять примерный процесс выполнения заказа, здесь вы можете обратиться кHow MySQL executes ORDER BY,Оптимизация сортировки Mysql и использование индекса (перенос)эти две статьи.

Когда мы пишем операторы sql и используем order by, мы сначала рассматриваем выполнение условий индекса. Если нет, то выполняется сортировка файлов в памяти. В худшем случае появляется временный файл. Конечно, это последнее, что нам нужно видеть.

Также вот мой личный опыт:

Совместный индекс — хорошая штука, его можно применить ко многим сценариям использования в проекте, подробную оптимизацию см.8.3 Optimization and Indexes.
Перезапись SQL, сложный одиночный SQL можно переписать на два или три, используя верхний индекс.
Настройте хорошую структуру таблицы и назначьте полям наиболее подходящие типы и длины.

В процессе открытия больше думайте о SQL и смотрите на план выполнения, чтобы эффективно избегать медленных запросов и повышать производительность службы.

Ссылаться на

How MySQL executes ORDER BY
Оптимизация сортировки Mysql и использование индекса (перенос)