Интервьюер: Как осуществляется заказ? Как оптимизировать?

Java база данных
Интервьюер: Как осуществляется заказ? Как оптимизировать?

01 Предисловие

Я только что перешел на новую работу.Подготовка заняла две недели, и я получил 5 предложений в течение 3 дней.Наконец, я выбрал предложение от единорога в интернет-индустрии в Гуанчжоу.Я только вчера присоединился к работе. За последние несколько дней я просто разобрался с интересными вопросами, которые были заданы в интервью, а также воспользовался случаем, чтобы поделиться с вами.

Интервьюер этой компании оказался немного интересным, с одной стороны, младшим братом-ровесником, мы проболтали два часа (у меня пересохло во рту). Вторая сторона - архитектор с Али.Видеоинтервью.После того как я представился, он спросил меня в начале:

Вы знакомы с MySQL?

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

Ознакомьтесь с индексами.

он:

Как упорядочить по реализации сортировки?

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

1.1 Начнем с каштана

Теперь есть список мест, структура такая:

CREATE TABLE `order` (
id INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键',
user_code VARCHAR ( 16 ) NOT NULL COMMENT '用户编号',
goods_name VARCHAR ( 64 ) NOT NULL COMMENT '商品名称',
order_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
city VARCHAR ( 16 ) DEFAULT NULL COMMENT '下单城市',
order_num INT ( 10 ) NOT NULL COMMENT '订单号数量',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8 COMMENT = '商品订单表';

Создать точечные данные:

// 第一步：创建函数
delimiter //

DROP PROCEDURE
IF
	EXISTS proc_buildata;
CREATE PROCEDURE proc_buildata ( IN loop_times INT ) BEGIN
DECLARE var INT DEFAULT 0;
WHILE
	var < loop_times DO
	
	SET var = var + 1;
INSERT INTO `order` ( `id`, `user_code`, `goods_name`, `order_date`, `city` , `order_num`)
VALUES
	( var, var + 1, '有线耳机', '2021-06-20 16:46:00', '杭州', 1 );

END WHILE;

END // delimiter;

// 第二步：调用上面生成的函数，即可插入数据，建议大家造点随机的数据。比如改改城市和订单数量
CALL proc_buildata(4000);

Мои сгенерированные данные выглядят так:

Существующие требования:Узнать количество заказов и номера пользователей мелких партнеров в Гуанчжоу за период 618, а в порядке возрастания количества заказов всего 1000.

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

select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000;

Как выполняется это заявление? Есть ли какие-то параметры, которые могут повлиять на его поведение?

02 Сортировать все поля

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

ALTER TABLE `order` ADD INDEX city_index ( `city` );

Используйте объяснение, чтобы увидеть выполнение

Обратите внимание, что результат последнего дополнительного поля:Using filesort, что указывает на необходимость сортировки.На самом деле MySQL будет выделять часть памяти для каждого потока для сортировки, называемую sort_buffer..

Чтобы более интуитивно понимать процесс сортированного выполнения, я примерно нарисовал индекс города:

Видно, что данные от ID-3 до ID-X теперь удовлетворяют условию sql. Весь процесс sql выглядит так:

  • 1. Инициализируйте sort_buffer и введите три поля city, order_num и user_code;
  • 2. В индексном городе найдите первый идентификатор первичного ключа, удовлетворяющий условию city='Guangzhou', который на рисунке равен ID_3;
  • 3. Возьмите всю строку из индекса id первичного ключа, возьмите значения трех полей city, order_num и user_code и сохраните их в sort_buffer;
  • 4, удалить первичный ключ записи из индекса id City;
  • 5. Повторяйте шаги 3 и 4 до тех пор, пока значение города не будет соответствовать условиям запроса, а соответствующий идентификатор первичного ключа будет ID_X на рисунке;
  • 6. Быстро отсортировать данные в sort_buffer по полю order_num;
  • 7. По результату сортировки взять первые 1000 строк и вернуть их клиенту.

Этот процесс называется сортировкой по полному полю, нарисуйте картинку, выглядит это так:

в,Сортировать по order_numЭтот шаг, который может быть выполнен в памяти, также может потребовать использования внешней сортировки, в зависимости от памяти и параметров, необходимых для сортировки.sort_buffer_size.

То есть размер памяти (sort_buffer), выделяемой MySQL для сортировки. Если объем сортируемых данных меньше sort_buffer_size, сортировка выполняется в памяти. Однако, если объем сортируемых данных слишком велик и память не поддерживает его, для помощи в сортировке требуется временный файл на диске.

Конечно, в MySQL 5.7 и выше вы можете использовать следующее введениеМетод обнаружения (используется позже), чтобы узнать, использует ли оператор сортировки временные файлы. PS:Оператор здесь может быть скопирован непосредственно в выполнение Navicat, чтобы выполняться вместе (скопируйте его и выполните вниз)

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

После выполнения из поля TRACE таблицы OPTIMIZER_TRACE можно получить следующие результаты:

Среди них рассмотренные_строки представляют количество строк для сортировки, 6883; sort_buffer_size — это размер буфера сортировки; sort_buffer_size — это размер моего буфера сортировки MySQL, который составляет 256 КБ.

Кроме того, значение sort_mode равно packed_additional_fields, что указывает на то, что процесс сортировки оптимизирует данные, то есть на то, сколько памяти занимают данные. Например: если нет длины определения данных 16, она рассчитывается в соответствии с этой длиной.Если данные занимают только 2, только память будет выделена в соответствии с длиной 2.

number_of_tmp_files представляет использование нескольких внешних файлов для облегчения сортировки. Я использовал два здесь.Когда память не может вместить это, я использую внешнюю сортировку.Внешняя сортировка обычно используется.Сортировка слияниемалгоритм. Это можно понять так просто,MySQL делит данные для сортировки на 2 части, и каждая часть сохраняется в этих временных файлах после сортировки по отдельности. Затем объедините эти 2 упорядоченных файла в один упорядоченный большой файл..

В последнем операторе запроса значение select @b-@a равно 6884, что означает, что во всем процессе сканируется только 6883 строки. Почему отображается 6884?

Поскольку при запросе таблицы OPTIMIZER_TRACE необходима временная таблица, когда механизм InnDB извлекает данные из временной таблицы, значение Inndb_rows_read увеличится на 1.

Таким образом, установка для internal_tmp_disk_storage_engine значения MyISAM решает проблему.

03 сортировка по ряду

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

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

Вот вопрос:

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

Должна быть Причина, по которой MySQL использует полную сортировку полей, контролируется параметром max_length_for_sort_data, и его значение по умолчанию равно 1024.

show variables like 'max_length_for_sort_data';

Поскольку длина city, order_num, user_code в примере из этой статьи = 16+4+16 =36 

SET max_length_for_sort_data = 16;

Когда длина отдельной строки превышает это значение, MySQL считает, что одна строка слишком велика и требует изменения алгоритма. Получается, что длина city, user_code и order_num равна 36, что явно не может вместить все поля запроса. Тогда нам нужно изменить алгоритм:sort_buffer хранит только поля order_num и id.

Процесс должен быть таким:

  • 1. Инициализируйте sort_buffer и обязательно поместите два поля, order_num и id;
  • 2. В индексном городе найдите первый идентификатор первичного ключа, удовлетворяющий условию city='Guangzhou', который на рисунке равен ID_3;
  • 3. Вернитесь к таблице, возьмите два поля order_num и id и сохраните их в sort_buffer;
  • 4. Возьмите идентификатор первичного ключа следующей записи из индексного города;
  • 5. Повторяйте шаги 3 и 4 до тех пор, пока они не будут удовлетворены, пока город = условия «Гуанчжоу», т.е. в FIG ID_X;
  • 6. Отсортировать данные в sort_buffer по полю order_num;
  • 7. Просмотрите результаты сортировки, возьмите первые 1000 строк и снова вернитесь к таблице, чтобы удалить три поля city, order_num и user_code и вернуть их клиенту.

Иллюстрация: Как видно из рисунка, в этом методе фактически на одну операцию возврата таблицы больше, но заполняемость sort_buffer_size стала меньше.

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

  • sort_mode становится ,Указывает, что только два поля, заказ_num и ID, участвуют в сортировке.
  • number_of_tmp_files стал равен 0, потому что количество строк, участвующих в сортировке, по-прежнему составляет 6883 строки, но каждая строка стала меньше, поэтому общий объем данных для сортировки стал меньше, sort_buffer_size может соответствовать памяти, используемой для сортировки, поэтому временные файлы не нужны.

Значение visible_rows по-прежнему равно 6883, что указывает на то, что данные, используемые для сортировки, составляют 6883 строки. Но значение оператора select @b-@a становится равным 7884. Потому что в это время, помимо процесса сортировки, после завершения сортировки, таблицу нужно вернуть один раз. Поскольку оператор ограничен 1000, он будет читать еще 1000 строк.

3.1 Подведите итоги

В сортировке rowid,Процесс сортировки может быть отсортирован еще раз, но нужно вернуть данные в таблицу.

Если памяти достаточно, MySQL отдаст приоритет сортировке всех полей и поместит все необходимые поля в sort_buffer, чтобы результаты запроса после сортировки возвращались сразу из памяти, без возврата в таблицу.

Это также отражает дизайнерскую идею MySQL:Если памяти достаточно, используйте больше памяти и минимизируйте доступ к диску..

Для таблиц InnoDB сортировка rowid потребует больше операций чтения с диска, поэтому она не будет предпочтительной..

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

Есть ли способ сделать сами данные упорядоченными? Напомним, что индексы, о которых мы узнали, упорядочены.

04 Оптимизация индекса

В настоящее время, если я создам составной индекс города и order_num, будут ли результирующие данные естественным образом упорядочены? Например:

alter table `order` add index city_order_num_index(city, order_num);

На данный момент индекс таблицы заказов выглядит так:

Оператор выполнения sql в начале статьи. Процесс выполнения до тех пор, как это:

  • 1. Найдите первый идентификатор первичного ключа, удовлетворяющий условию city='Guangzhou', из индекса (city, order_num);
  • 2, обратно в таблицу, принимая город, order_num, значение user_code из трех полей, напрямую возвращено как часть набора результатов;
  • 3. Берем идентификатор первичного ключа следующей записи из индекса (город, номер_заказа);
  • 4. Повторяйте шаги 2 и 3 до тех пор, пока не будет найдена 1000-я запись или цикл не завершится, когда условие city='Guangzhou' не будет выполнено.

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

Как видно из рисунка,В поле Дополнительно нет пункта Использование файловой сортировки, то есть сортировка не требуется. И поскольку объединенный индекс (city, order_num) сам по себе упорядочен, пока вы найдете первые 1000 записей, удовлетворяющих условиям, вы можете выйти и снова вернуться к таблице. То есть требуется всего 2000 сканирований.

Вопрос в том, есть ли лучшее решение?

05 Максимальная оптимизация

Приведенный выше метод по-прежнему возвращает таблицу один раз, главным образом потому, что user_code не включен в индекс. Оглядываясь назад на оптимизацию sql, которую мы изучили ранее, как избежать возврата к таблице?

Поле запроса, добавьте его в составной индекс, соответствующий этой таблице, заключается в добавлении user_code к составному индексу:

alter table `order` add index city_order_num_user_code_index(city, order_num, user_code);

На этот раз этот долгий процесс, прямой доступ к данным в корзине:

объяснить, чтобы увидеть выполнение:

Как видно из рисунка,В поле Дополнительно указано Использование индекса, то есть используется индексное покрытие.. Даже не нужна таблица возврата, просто просканируйте 1000 раз.

идеально~

5.1 Настройка параметров

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

СноваНапример, увеличьте значение max_length_for_sort_data.. Если значение слишком мало, это увеличит количество возвратов к таблице и снизит производительность запросов.

06 порядок по общим вопросам интервью

1. Если оператор запроса имеет несколько атрибутов in, существует ли процесс сортировки для выполнения SQL?

Предполагая, что теперь есть объединенный индекс (город, номер_заказа, код_пользователя), выполните следующую инструкцию SQL:

select city, order_num, user_code from `order` where city in ('广州') order by order_num limit 1000

при единичном условии сортировка, несомненно, не требуется. Объясни это:

Однако при наличии нескольких условий будет выполняться процесс сортировки, например выполнение следующего оператора

select city, order_num, user_code from `order` where city in ('广州','深圳') order by order_num limit 1000

поясните Ниже, если в конце вы увидите «Используя сортировку файлов», это означает, что идет процесс сортировки. Почему это?

Поскольку order_num изначально является составным индексом, он упорядочивается, когда выполняется только одно условие «city=Guangzhou». Он также заказывается, когда "город = Шэньчжэнь" удовлетворен. Но сложение их вместе не гарантирует, что order_num все еще в порядке.

2, предел страницы слишком велик, что приводит к большому количеству сортировки. Какие?

select * from `user` order by age limit 100000,10
  • Можно записать последний идентификатор предыдущей страницы.При запросе следующей страницы условие запроса сопровождается идентификатором, например: где идентификатор > последний идентификатор ограничения 10 предыдущей страницы.
  • Вы также можете ограничить количество страниц, если это позволяет бизнес.

3. Порядок хранения индекса не соответствует порядку по, как его оптимизировать?

Предполагая, что есть совместный индекс (возраст, имя), нам нужно изменить его следующим образом:Запросите имена и возраст 10 лучших учеников и отсортируйте их по возрасту, если возраст одинаковый, отсортируйте по имени в порядке убывания.. Соответствующий оператор SQL должен быть:

select name, age from student order by age, name desc limit 10;

объяснять,Значение дополнительного параметра — использование файловой сортировки., пройдите процесс сортировки:

Это связано с тем, что в индексном дереве (возраст, имя) возраст отсортирован от меньшего к большему,Если возраст совпадает, то сортировать по имени от меньшего к большему. По порядку сортируется по возрасту от меньшего к большему, а если возраста одинаковые, то сортируется по имени от большого к меньшему. То есть порядок хранения индекса несовместим с порядком по.

Как мы оптимизируем? Если mysql версии 8.0, поддержкаDescending Indexes, вы можете изменить индекс следующим образом:

CREATE TABLE `student` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `student_id` varchar(20) NOT NULL COMMENT '学号',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name` desc) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='学生表';

4. Нет условия where, нужно ли индексировать порядок по полям?

При ежедневной разработке вы можете столкнуться с порядком без условия "Где". В настоящее время нужно ли индексировать поля, стоящие за порядком? Если есть такой SQL, нужно ли индексировать create_time:

select * from student order by create_time;

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

Если вы запрашиваете SQL, измените его:

select * from student order by create_time limit m;

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

07 Резюме

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

В то же время, комбинируя трюк с индексным покрытием, мы также можем уменьшить количество возвратов к таблице.Если дизайн, когда бизнес сортировки индекса направлен на поле, по возможности добавленное в индекс, а оставшееся поле бизнес-запроса (например, текст города, user_code) было добавлено в комбинацию индекса, индекс для достижения лучшего охвата.

Конечно, у индексов есть и недостатки. Он занимает место и требует затрат на обслуживание. Поэтому при проектировании вам все равно нужно учитывать ваш реальный бизнес.

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

7.1 Ссылка

  • blog.csdn.net/weixin_28917279/article/details/113424610
  • time.geekbang.org/column/article/73479
  • zhuanlan.zhihu.com/p/380671457
Категории