предисловие
Сегодня мы поговорим о том, как оптимизировать производительность MySQL, в основном за счет оптимизации индекса. следующая статья разговорMySQL慢查询日志, мы определяем, какой оператор SQL имеет проблему, на основе журнала медленных запросов, а затем оптимизируем его, так что следите за обновлениями.MySQL慢查询日志篇
построить таблицу
// 建表
CREATE TABLE IF NOT EXISTS staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名',
age INT NOT NULL DEFAULT 0 COMMENT'年龄',
pos VARCHAR(20) NOT NULL DEFAULT "" COMMENT'职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职事件'
) CHARSET utf8 COMMENT'员工记录表';
// 插入数据
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('z3', 22, 'manager', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('July', 23, 'dev', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('2000', 23, 'dev', now());
// 建立复合索引（即一个索引包含多个字段）
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
Оптимизация 1: использовать все индексы
вводить
Установленный составной индекс содержит несколько полей, и лучше всего использовать их все при запросе, и строго соблюдать порядок индекса, чтобы эффективность запроса была наибольшей. (Самая идеальная ситуация, детально разбирается конкретная ситуация)
Случай SQL
Оптимизация 2: Правило самого левого префикса
вводить
Если установлен составной индекс, порядок индекса должен быть в том же порядке, в котором он был установлен, то есть слева направо, например: a->b->c (связанный со структурой данных B+ дерево)
Пример недопустимого индекса
- a->c: a допустимо, c неверно
- b->c: и b, и c недействительны
- с: с недопустимо
Случай SQL
Оптимизация 3: не делайте с индексами следующее
Следующее использование сделает индекс недействительным
- Вычисления, такие как: +, -, *, /, !=, , имеют значение NULL, не равно NULL или
- Функции, такие как: sum(), round() и т. д.
- Ручное/автоматическое преобразование типа, например: id = "1", которое изначально является числом, записывается в виде строки
Случай SQL
Оптимизация 4: не размещайте индекс справа от запроса диапазона.
Пример
Например, составной индекс: a->b->c, когда где a="" и b>10 и 3="", в это время можно использовать только a и b, а c не может использовать индекс, потому что после диапазона индекс является недействительным (связанным со структурой дерева B+)
Случай SQL
Оптимизация пятая: уменьшить использование select *
Используйте покрывающий индекс
То есть: поле запроса выбора совпадает с полем индекса, используемым в where.
Случай SQL
Оптимизация шестая: как нечеткий поиск
Ситуация отказа
- как "% Zhangsan%"
- как "% Чжан Сан"
решение
- Используйте составной индекс, то есть подобное поле является полем запроса выбора, например: выберите имя из таблицы, где имя типа «%zhangsan%»
- Используйте как «Чжансан%»
Случай SQL
Седьмая оптимизация: порядок по оптимизации
Когда в операторе запроса используется порядок по, если индекс не используется для сортировки, сортировка файлов будет сортироваться внутри файла. В этом случае, когда объем данных велик или параллелизм высок, возникнут проблемы с производительностью. и нуждаются в оптимизации.
Примеры ситуаций, когда происходит сортировка файлов
- порядок по полю не является индексным полем
- Порядок по полю является полем индекса, но покрывающий индекс не используется при выборе, например:
select * from staffs order by age asc; - Существует как сортировка по возрастанию ASC, так и сортировка по убыванию DESC, например:
select a, b from staffs order by a desc, b asc; - порядок по При сортировке нескольких полей порядок по не выполняется в соответствии с порядком индекса, то есть не в соответствии с правилом крайнего левого префикса, например:
select a, b from staffs order by b asc, a asc;
Решения на уровне индекса
- Сортировать по индексу первичного ключа
- В соответствии с правилом крайнего левого префикса и использованием покрывающего индекса для сортировки, когда сортируются несколько полей, сохраняйте согласованное направление сортировки.
- Принудительное использование индекса в операторе SQL, принудительный индекс (имя индекса)
- Не сортировка в базе данных, сортировка на уровне кода
упорядочить по алгоритму сортировки
- двусторонняя сортировка
До Mysql4.1 использовалась двухсторонняя сортировка, что буквально означает сканирование диска дважды, наконец, получение данных, чтение указателя строки и столбца ORDER BY, их сортировка, а затем сканирование отсортированного списка в соответствии со списком в таблице. список Значение повторно считывается из списка на выходе данных. То есть прочитать поле сортировки с диска, отсортировать в буфере, а затем прочитать другие поля с диска.
Дисковый ввод-вывод файла занимает очень много времени, поэтому после Mysql4.1 появился второй алгоритм — односторонняя сортировка.
- односторонняя сортировка
Прочитайте все столбцы, требуемые запросом с диска, отсортируйте их в буфере в соответствии с столбцом orderby, а затем просмотрите отсортированный список для вывода, Он более эффективен, позволяет избежать повторного чтения данных и превращает случайный ввод-вывод в последовательный ввод-вывод, но занимает больше места. Потому что он хранит каждую строку в памяти.
Когда мы неизбежно используем сортировку, что нам делать, когда уровень индекса не может быть оптимизирован? По возможности позволяйте MySQL использовать для сортировки второй однонаправленный алгоритм. Это может уменьшить большое количество случайных операций ввода-вывода и значительно повысить эффективность работы по сортировке. Давайте рассмотрим моменты, на которые необходимо обратить внимание при оптимизации односторонней сортировки.
Точка оптимизации односторонней сортировки
-
Увеличьте max_length_for_sort_data
В MySQL решение об использовании алгоритма «двусторонней сортировки» или алгоритма «односторонней сортировки» принимается параметром maxlength_forsort_data для принятия решения. Когда максимальная длина всех возвращаемых полей меньше значения этого параметра, MySQL выберет алгоритм «односторонней сортировки», в противном случае он выберет алгоритм «многосторонней сортировки». Поэтому, если у MySQL достаточно памяти для хранения неотсортированных полей, которые необходимо вернуть, вы можете увеличить значение этого параметра, чтобы разрешить MySQL использовать алгоритм «односторонней сортировки».
-
Удалите ненужные поля возврата и избегайте выбора *
Когда памяти не очень много, вы не можете заставить MySQL использовать алгоритм «односторонней сортировки», просто принудительно увеличив вышеуказанные параметры, иначе MySQL, возможно, придется разделить данные на множество сегментов, а затем отсортировать их, что может не стоить потери.. В это время необходимо удалить ненужные возвращаемые поля и привести длину возвращаемого результата в соответствие с ограничением параметра max_length_for_sort_data.
-
Увеличьте значение параметра sort_buffer_size.
Если это значение слишком мало, и вы возвращаете слишком много элементов за один раз, они, вероятно, будут сортироваться много раз, а затем, наконец, результаты каждой сортировки будут объединены, что будет медленнее и больше. заставить MySQL выбрать алгоритм «односторонней сортировки», но заставить MySQL минимизировать сегментацию данных, которые необходимо отсортировать в процессе сортировки, потому что сегментация заставит MySQL использовать временную таблицу для сортировки обмена.
Но sort_buffer_size не настолько велик, насколько это возможно:
- Sort_Buffer_Size — это параметр уровня соединения.Когда каждому соединению необходимо использовать буфер в первый раз, установленная память выделяется за один раз.
- Размер Sort_Buffer_Size не настолько велик, насколько это возможно, потому что это параметр уровня подключения, слишком большие настройки и высокий параллелизм могут привести к истощению ресурсов системной памяти.
- Говорят, что когда Sort_Buffer_Size превышает 2M, mmap() будет использоваться вместо malloc() для выделения памяти, что приведет к снижению эффективности.
Оптимизация восьмая: группировать по
Принцип заключается в том, чтобы сначала отсортировать, а затем сгруппировать, и его метод оптимизации может ссылаться на порядок. где выше иметь, а условия, которые можно записать в где, не ограничиваются наличием.
айтишник
一个在大厂做高级Java开发的程序猿
Обратите внимание на публичный аккаунт WeChat: IT-брат
Ответ: полный набор учебных пособий по Java, которые вы можете получить: основы Java, веб-приложение Java, все учебные пособия по JavaEE, включая весеннюю загрузку и т. д.
Re: Шаблон резюме, вы можете получить 100 красивых резюме
Re: Маршрут обучения Java, вы можете получить самую последнюю и самую полную дорожную карту обучения
Re: Java eBook, вы можете получить 13 обязательных к прочтению книг для лучших программистов