Общие этапы анализа производительности
1. Включите и зафиксируйте медленные запросы
2. объяснить + медленный анализ SQL
3. Показать запросы профиля, детали выполнения и жизненный цикл SQL на сервере MySQL.
4. Настройка параметров сервера базы данных SQL.
Маленький стол управляет большим столом
for(int i = 0; i < 5; i++){
for(int j = 0; j < 1000; j++){
...
}
}
for(int i = 0; i < 1000; i++){
for(int j = 0; j < 5; j++){
...
}
}
На примере приведенного выше кода, если он первый для, то например, мы установили только 5 ссылок, а второй для установили 1000. Нет сомнения, что лучше установить меньше ссылок, выбирайте!
Принцип оптимизации: маленькие таблицы управляют большими таблицами, то есть маленькие наборы данных управляют большими наборами данных.
принцип:
select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id
Когда набор данных таблицы B должен быть меньше, чем набор данных таблицы A, использование in имеет приоритет над существующими.
select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select id from A
for select * from B where B.id = A.id
Когда набор данных таблицы A меньше, чем набор данных таблицы B, лучше использовать exists, чем in (обе таблицы A и B должны быть проиндексированы).
существует использование: выберите … из таблицы, где существует (подзапрос)
Эту грамматику можно понимать как: поместить данные основного запроса в подзапрос для условной проверки и определить, сохраняются ли данные результата основного запроса в соответствии с результатом проверки (ИСТИНА или ЛОЖЬ).
1. exists (подзапрос) возвращает только true или false, поэтому select * в подзапросе также может быть select 1 или др. Официальное заявление состоит в том, что список SELECT будет игнорироваться во время фактического выполнения, поэтому нет никакой разницы
2. Фактический процесс выполнения существующего подзапроса, возможно, подвергся беспокойству, а не поточечному сравнению в нашем понимании.Если вас беспокоит проблема эффективности, вы можете провести реальный тест, чтобы определить, есть ли проблема эффективности. .
3. Часто существующий подзапрос можно заменить условными выражениями, другими подзапросами или JOIN, что оптимально требует конкретного анализа конкретного ответа на вопрос.
упорядочить по оптимизации
Предложение ORDER BY, попробуйте использовать индекс для сортировки, избегайте использования FileSort для сортировки
Создать таблицу + вставить данные + новый пример индекса:
use day_07;
create table tblA(
#id int primary key not null auto_increment,
age int,
birth TIMESTAMP not null
);
insert into tblA(age, birth) VALUES (22, NOW());
insert into tblA(age, birth) VALUES (23, NOW());
insert into tblA(age, birth) VALUES (24, NOW());
create index idx_A_ageBirth ON tblA(age, birth);
select * from tblA;
MySQL поддерживает два способа сортировки: FileSort и Index, причем Index является эффективным.
Это относится к тому, что MySQL сканирует сам индекс для завершения сортировки. Метод FileSort менее эффективен.
Если порядок удовлетворяет двум условиям, он будет использовать метод индекса для сортировки:
1. Оператор order by использует крайний левый передний столбец индекса, что на самом деле является правилом наилучшего левого префикса.
2. Используйте предложение where и предложение order by, чтобы объединить условные столбцы, чтобы удовлетворить самый левый передний столбец индекса, что также соответствует правилу наилучшего левого префикса.
Итак, насколько это возможно, чтобы завершить операцию сортировки в столбце индекса в соответствии с лучшим левым префиксом для построения индекса.
FileSort два вида
Если не в столбце индекса, файловая сортировка имеет два алгоритма: двусторонняя сортировка и односторонняя сортировка.
Двусторонняя сортировка: до MySQL 4.1 использовалась двусторонняя сортировка, что буквально означает двойное сканирование диска и, наконец, получение данных. Прочитайте указатель строки и столбец orderby, отсортируйте их, затем просмотрите отсортированный список и повторно прочитайте соответствующую передачу данных из списка в соответствии со значением в списке. Дисковый ввод-вывод занимает очень много времени: выборка поля сортировки с диска, сортировка в буфере, а затем выборка других полей с диска. Итак, это два сканирования диска или двусторонняя сортировка.
Чтобы получить пакет данных, необходимо дважды просканировать диск, а IO занимает очень много времени, поэтому после MySQL 4.1 появился второй улучшенный алгоритм — односторонняя сортировка.
Односторонняя сортировка: чтение всех столбцов, требуемых запросом, с диска, сортировка их в буфере в соответствии с порядком столбцов, а затем сканирование отсортированного списка для вывода, что более эффективно, позволяет избежать повторного чтения данных и Превращает случайный ввод-вывод в последовательный ввод-вывод, но использует больше места, поскольку сохраняет каждую строку в памяти. Но в этом случае я боюсь столкнуться с ситуацией, когда данные не могут быть получены за один раз, в этом случае необходимо получить несколько данных, то есть несколько IO, что не так хорошо, как предыдущие два- Алгоритм пути!
Два вида ям
В sort_buffer метод B занимает намного больше места, чем метод A, потому что метод B удаляет все поля, поэтому возможно, что общий размер извлеченных данных превышает емкость sort_buffer, так что только данные размером sort_buffer можно извлекать каждый раз, сортировать (создавать файл tmp, объединять несколькими способами), а затем брать размер sort_buffer после сортировки, а затем сортировать ... тем самым несколько операций ввода-вывода. Изначально я хотел сохранить одну операцию ввода-вывода, но это привело в большом количестве операций O, но выигрыш перевешивал потери.
упорядочить по стратегии оптимизации
Стратегия оптимизации: увеличить настройку параметра SORT_BUFFER_SIZE, увеличить настройку параметра MAX_LENGEND_FOR_SORT_DATA
1. Выберите * - это большой нет - нет при заказе, и только поля, требуемые запросами, очень важны. Эффекты здесь:
- Когда сумма размеров полей Query меньше max_length_for_sort data и поле сортировки не имеет тип TEXT|BLOB, будет использоваться улучшенный алгоритм - односторонняя сортировка, в противном случае будет использоваться старый алгоритм - многосторонняя сортировка
- Данные обоих алгоритмов могут превышать емкость sort_buffer, после чего будет создан tmp файл для сортировки слиянием, в результате чего будет несколько IO, но риск использования алгоритма односторонней сортировки будет больше, поэтому необходимо увеличить sort_buffer_size
2. Попробуйте увеличить sort_buffer_size
Независимо от того, какой алгоритм используется, увеличение этого параметра повысит эффективность.Конечно, его следует улучшать в соответствии с возможностями системы, потому что этот параметр есть для каждого процесса.
3. Попробуйте увеличить max_length_for_sort_data
Увеличение этого параметра увеличивает вероятность использования улучшенного алгоритма. Но если он установлен слишком большим, вероятность того, что общая емкость данных превысит sort_buffer_size, увеличивается, и очевидными симптомами являются высокая активность дискового ввода-вывода и низкая загрузка процессора.
упорядочить по сводке оптимизации
В Mysql есть два метода сортировки: сортировка по файлам или сортировка по отсортированному индексу со сканированием. Mysql может использовать один и тот же индекс для сортировки и запроса.
КЛЮЧ a_b_c (а, б, в)
В заказе можно использовать крайний левый префикс индекса
order by a
order by a, b
order by a, b, c
order by a DESC, b DESC, c DESC
Если where определено как константа с использованием крайнего левого префикса индекса, то order by может использовать индекс
where a = const order by b, c
where a = const and b = const order by c
where a = const and b > const order by c
Не могу использовать индекс для сортировки
order by a ASC, b DESC, c DESC # 排序方式不一致
where g = const order by b, c # 丢失a索引
where a = const order by c # 丢失b索引
where a = const order by d # d不是索引的一部分
where a in (...) order by b, c # 对于排序来说，多个相等条件也是范围查询
группировать по оптимизации
Оптимизация группировки точно такая же, как и упорядочивания Суть группировки заключается в том, чтобы сначала отсортировать, а затем сгруппировать по лучшему левому префиксу для построения индекса. Если нельзя использовать индексный столбец, увеличьте значение параметра max_length_for_sort_data или увеличьте значение параметра sort_buffer_size. где выше иметь, а условия, которые можно записать в где, не ограничиваются наличием.