Практика оптимизации медленных запросов Mysql

Практика оптимизации медленных запросов Mysql

Цель:

Повысьте эффективность работы mysql, увеличьте параллелизм и улучшите скорость отклика

план:

Благодаря журналу медленных запросов Excel, предоставляемому Alibaba Cloud, операторы SQL, которые занимают много времени и стоят дорого, оптимизируются для повышения скорости доступа и эффективности работы сервера.

упражняться:

анализировать 阿里云给的数据库单日报表有以下字段

• Create Time DBName
• MySQL Total Execution Counts
• MySQL Total Execution Times
• Max Execution Time
• Max Lock Time
• Parse Total Row Counts
• Parse Max Row Count
• Return Total Row Counts
• Return Max Row Count
• SQL Text
• hard ware- latencies

соответственно

• время создания
• Имя базы данных
• mysql общее количество выполнений
• Время выполнения в mysql
• Максимальное время выполнения
• Максимальное время блокировки
• Разобрать статистику общего количества строк
• Разобрать максимальное количество строк
• Возвращает общее количество строк
• Возвращает максимальное количество строк
• оператор SQL
• аппаратная задержка

Согласно журналу медленных облачных запросов Ali, при условии, что это использование стратегии оптимизации выглядит следующим образом:

Для высокочастотного запроса с более чем 100 запросами в день максимальное время запроса/общее время запроса является самым большим, а выигрыш от оптимизации достигается за счет последовательной оптимизации.

Первое утверждение:

Количество выполнений: 1114 Максимальное затраченное время: 7 Максимальное количество строк для анализа: 348325 Максимальное количество возвращаемых строк 4 #Время выполнения: 1114 Максимальное время выполнения: 7 Разбор максимального количества строк: 348325 Возврат максимального количества строк 4

select id from appname_m_members where yiku_id = :1

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

анализировать

explain select id from appname_m_members where yiku_id = 1;

Видно, что к yiku_id действительно не добавлен индекс.

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

#增加索引

ALTER TABLE `appname_m_members` 
ADD INDEX `yiku_id` (`yiku_id`) ;

После попытки увеличить индекс снова проанализируйте выполнение оператора

результат:

Строки диапазона соответствия уменьшены с 32 до 1.

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

Второе утверждение:

Количество выполнений: 482 Максимальное затраченное время: 15 Максимальное количество строк для анализа: 764383 Максимальное количество возвращенных строк: 482

#执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482
select fullname as username , linkphone as userphone , 
`userimage` , `nickname` , `hospitalname` , `partmentname` , 
`doctortitle` , `iscertification` , `fullname` 
from `users` 
where `useruuid` = '597_f66e1cb79341cedf6f24aaf01fde8611' limit 1;

анализировать:

Добавьте к нему индекс:

#增加索引
ALTER TABLE `users` 
ADD INDEX `useruuid` (`useruuid`);

Диапазон прямых сканирования (строки) от 72W до 1, значительно увеличить

результат:

Строки диапазона соответствия уменьшены с 72 до 1

Третье предложение:

Количество выполнений: 820 Максимальное затраченное время: 10 Максимальное количество строк для анализа: 167214 Максимальное количество возвращаемых строк 1

#执行次数: 820 最大耗时: 10 解析最大行数: 167214  返回最大行数 1
select count ( postingid ) as postnum from mediposting 
where isaudit != :1
and isgoodcase = :2
and postsection = :3

анализировать:

Измените порядок операторов sql и измените их в соответствии с крайним левым принципом следующим образом.

select count(postingid) as postnum from mediposting 
where postsection = 1
and isgoodcase = 1
and isaudit != 1

результат:

В основном используется то, что PostSection — это общее количество индексов, и эта часть не нуждается в оптимизации.

##### 第四条语句:
执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482
##执行次数: 410 最大耗时: 10 解析最大行数:348325   返回最大行数 1
........

Результат: оператор слишком длинный (2017 символов), логика вложена и пока не оптимизирована

Пятое утверждение:

Количество выполнений: 659. Максимальное затраченное время: 6. Максимальное количество строк для анализа: 215 115. Максимальное количество возвращаемых строк: 659.

## 执行次数: 659 最大耗时: 6 解析最大行数:215115   返回最大行数 659
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
  `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
   ( initalscore+effectevaladd ) as `effectval` 
   from `medigooddoc` 
   where ( ( initalscore+effectevaladd ) > 80 ) 
   order by rand ( ) limit 1 ;

анализировать:

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

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
 `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` 
 where (initalscore+effectevaladd)  > 80
 and docid > ( 
     RAND() * (
         (SELECT MAX(docid) FROM `medigooddoc`) 
         -
         (SELECT MIN(docid) FROM `medigooddoc`)
    )
    +
    (SELECT MIN(docid) FROM `medigooddoc`) 
 )
 order by `docid` limit 1;

Заявление перед оптимизацией:

Видно, что диапазон сканирования очень большой (строк) 120 770 строк.

можно увидеть

• Минимальный диапазон запросов (строк) 1
• Получение max/min возвращает данные непосредственно из оптимизатора запросов mysql (дополнительно). В документации mysql есть следующее объяснение:

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

Эффективность выполнения теста:

• Реализовать 10 раз A: 2 941 мс B: 168 мс
• Выполнить 50 раз A: 14 441 мс B: 828 мс
• Выполнить 100 раз a: 29 126 мс b: 1 645 мс

Видно, что время выполнения на сто раз сократилось с 30 до менее 2 секунд, что значительно повышает скорость ответа запроса mysql. Но есть еще проблема.Идентификаторов всего 100 000. Результаты исходного запроса относительно сбалансированы, их больше 10 000 и несколько тысяч, но после использования этого оператора всегда есть id меньше 10 000, а результаты превзошли все наши ожидания.

Скорректированное смещение вероятности

план 1:

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

# php
$round = select max(docid) as max,min(docid) as min from medigooddoc;
$rand = rand($round['min'],$round['max']);

# sql
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
 `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` 
 where (initalscore+effectevaladd)  > 80
 and docid > $rand
 order by `docid` limit 1;

Такая проблема: будет еще одно взаимодействие sql, база данных

Сценарий 2:

использовать внутреннюю оптимизацию соединения

#可用一
select `docid` ,`docname`,
 `doctitle` , `docimgurl` ,
 `docdep` , `dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` as t1 
 join (
     select rand() * (select max(docid) from `medigooddoc`) 
     as rand
 ) as t2 
 where (t1.initalscore+t1.effectevaladd)  > 80
 and `t1`.`docid` >= t2.rand
 order by `docid` limit 1;

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

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

select `docid` ,`docname`,
 `doctitle` , `docimgurl` ,
 `docdep` , `dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` as t1 
 join (
     select rand() * 
     (
         (select max(docid) from `medigooddoc`)
         -
         (select min(docid) from `medigooddoc`)
     )
     +
     (select min(docid) from `medigooddoc`)  
     as rand
 ) as t2 
 where (t1.initalscore+t1.effectevaladd)  > 80
 and `t1`.`docid` >= t2.rand
 order by `docid` limit 1;

Подводя итог, поскольку схема 1 генерирует больше взаимодействий с базой данных, поскольку наша база данных является другим сервером, накладные расходы на сетевое соединение относительно велики, а дополнительные запросы также будут оказывать большее давление на базу данных в моменты высокой параллелизма.

Схема 2 использует метод внутреннего соединения, который может быть выполнен только с одним взаимодействием с базой данных, а максимальное и минимальное значения также напрямую возвращаются запросчиком mysql, что снижает различные накладные расходы на производительность базы данных, Таким образом, это лучшая схема .

результат:

Используйте информацию о структуре таблицы, сохраненную mysql, чтобы заменить неэффективный запрос порядка rand().

Глубокое понимание:

Шестое утверждение:

Количество выполнений: 729 Максимальное затраченное время: 4 секунды Максимальное количество строк для анализа: 130898 Максимальное количество возвращаемых строк 2

select `medigooddoc`.`docid` , `medigooddoc`.`yikuid` 
from `medigooddoc` 
where ( yikuid = 597725 or yikuid = -597725 );

анализировать:

Оптимизация:

полеyikuidпоказатель

ALTER TABLE `medigooddoc`
ADD INDEX `YiKuID` (`YiKuID`);

Повторите анализ объяснения

результат:

Количество рядов диапазона соответствия уменьшено с 8,3 до 1.

Статья 7 Заявление

Количество выполнений: 474 Максимальное затраченное время: 5 секунд Максимальное количество строк для анализа: 261797 Максимальное количество возвращаемых строк 1

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` 
 from `medigooddoc` order by rand ( ) limit 1;

анализировать

план

Получит случайную запись от порядка с помощью rand() limit 1 до метода внутреннего соединения

select `docid`, `docname`,
  `doctitle` , `docimgurl` 
 from `medigooddoc` as  t1 
 inner join 
 (
     select rand()  *
     (
         (select MAX(docid) from `medigooddoc`)
         -
         (select MIN(docid) from `medigooddoc`)
     )
     +
     (select MIN(docid) from `medigooddoc`)
     as rand
 ) as t2 
 on t1.docid >= t2.rand
 order by docid limit 1;

Повторите анализ объяснения

результат

Замените неэффективный порядок с помощью rand() информацией таблицы, хранящейся в mysql.

Статья 8

Количество выполнений: 136 Максимальное затраченное время: 7 секунд Максимальное количество строк для анализа: 301880 Максимальное количество возвращаемых строк 1

select `searchrecords`.`searchid` , `searchrecords`.`searchnum` 
from `searchrecords` 
where ( searchtype = 0 ) and ( userid = 14 ) 
and ( searchmsg = '碳酸钙D3' );

анализировать

план

Цель индекса — сузить диапазон запроса, различая первые три слова текстового содержимого и различая по идентификатору пользователя, можно получить более точный диапазон выполнения оператора.

ALTER TABLE searchrecords ADD INDEX searchmsg (searchmsg(5));
ALTER TABLE searchrecords ADD INDEX userid (userid);

После выделения диапазона индексацией первых 5 слов текста диапазон сокращается до 28 записей

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

Анализ влияния индексов на запись Таблица в основном используется для записи часто встречающихся слов, которые ищут пользователи, а статистические поля обновляются во время основной операции записи.Эти два недавно проиндексированных поля обновляются нечасто, поэтому накладные расходы на индексацию невелики.

результат

Дальность матча уменьшена с 29 до 1

Раздел IX Заявление

select `projects`.`id` , `projects`.`guid` , 
`projects`.`getittime` , `projects`.`keywords` ,
 `projects`.`barcode` as `num` , `projects`.`goodcasedep` ,
  `projects`.`bingshi` , `pictures`.* 
  from `projects` 
  inner join `pictures` on projects.guid = pictures.projectid 
  and pictures.filetype = :1 
  where ( islock != :2 ) and ( isgoodcase = :3 ) 
  and ( ( goodcasedep like :4 or goodcasedep like :5 
  or goodcasedep like :6 or goodcasedep like :7 
  or goodcasedep like :8 or goodcasedep like :9 
  or goodcasedep like :10 or goodcasedep like :11 
  or goodcasedep like :12 or goodcasedep like :13 
  or goodcasedep like :14 or goodcasedep like :15 
  or goodcasedep like :16 or goodcasedep like :17 
  or goodcasedep like :18 or goodcasedep like :19 
  or goodcasedep like :20 or goodcasedep like :21 
  or goodcasedep like :22 or goodcasedep like :23 
  or goodcasedep like :24 or goodcasedep like :25 
  or goodcasedep like :26 or goodcasedep like :27 
  or goodcasedep like :28 or goodcasedep like :29 
  or goodcasedep like :30 or goodcasedep like :31 
  or goodcasedep like :32 or goodcasedep like :33 
  or goodcasedep like :34 or goodcasedep like :35 
  or goodcasedep like :36 or goodcasedep like :37 
  or goodcasedep like :38 or goodcasedep like :39 
  or goodcasedep like :40 or goodcasedep like :41 ) ) 
  order by rand ( ) limit :42

результат:

На данный момент никаких изменений: Превышен лимит байтов

Пункт 10

Количество выполнений: 145 Максимальное затраченное время: 2 секунды Максимальное количество строк для анализа: 130898 Максимальное количество возвращаемых строк 1

select `medigooddoc`.`isfollow` , `medigooddoc`.`isconsult` ,
 `medigooddoc`.`isphone` , `medigooddoc`.`isprivate` 
 from `medigooddoc` where ( yikuid =  694 );

анализировать:

план

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

ALTER TABLE `medigooddoc` ADD INDEX YiKuID(`YiKuID`);

Повторите анализ объяснения

результат

Диапазон матча уменьшен с 12 до 1

Статья 11

Количество выполнений: 148 Максимальное затраченное время: 3 секунды Максимальное количество строк для анализа: 74616 Максимальное количество возвращаемых строк 30

select `magazinearticle`.`articleid` , 
`magazinearticle`.`articletitle` , 
`magazinearticle`.`article_publishtime` ,
 `magazinearticle`.`articlepicpath` ,
  `magazinearticle`.`articleurl` ,
   `magazinearticle`.`articlenum` ,
    `magazinearticle`.`perid` ,
     `magazinearticle`.`article_originallink` ,
      `magazinearticle`.`islink` from `magazinearticle` 
      where ( logicdel = 0 ) and ( perid != 60 ) 
      order by `article_publishtime` desc limit 1,30;

анализировать:

план:

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

ALTER TABLE `magazinearticle` ADD INDEX 
article_publishtime(`article_publishtime`);

Повторите анализ объяснения

результат:

Количество строк диапазона соответствия уменьшено с 2w до 59.

Глубокое понимание:

Различные типы объяснения

тип	значение
тип	значение
system	таблица имеет только одну строку
const	В таблице имеется не более одной строки, которая обычно используется для сравнения первичных ключей или уникальных индексов.
eq_ref	Каждый раз, когда строка объединяется с предыдущей таблицей, в таблице читается только одна строка. Это лучшая строка, кроме system и const. Для нее характерно использование =, и все части индекса участвуют в объединении и индекс является первичным ключом или не-индексами на нулевых уникальных ключах
ref	Если вы каждый раз сопоставляете только небольшое количество строк, лучше использовать = или , которые могут быть оставлены покрывающим индексом или не главным или не уникальным ключом.
fulltext	исследовать все
ref_or_null	Как ref, но включает NULL
index_merge	Указывает, что оптимизации слияния индексов (включая пересечения, объединения и объединения между пересечениями) были выполнены, но исключая межтабличные и полнотекстовые индексы. Это сложнее.Текущее понимание состоит в том, чтобы объединить сканирование индекса диапазона одной таблицы (если оценка стоимости лучше, чем обычный диапазон)
unique_subquery	Во вложенном запросе значение в (выбрать...) заменяет подзапрос формой «выберите уникальный_ключ_столбец». PS: Так что не обязательно неэффективно использовать подзапросы в предложении in!
index_subquery	То же, что и выше, но заменить подзапросы вида «выбрать не_уникальный_ключ_столбец»
range	диапазон постоянных значений
index	А. Когда запрос покрывается индексом, то есть когда все данные могут быть получены из дерева индекса (использование индекса в дополнительном) б) полное сканирование таблицы для поиска строк данных из индекса в порядке индекса (без использования индекса); в) Если одновременно появляются сообщения «Использование индекса» и «Использование места в дополнительном», это означает использование индекса для поиска значения ключа; г. Если он появляется один, индекс чтения используется для замены строки чтения, но не используется для поиска.
all	полное сканирование таблицы

Статья XII

Количество выполнений: 135 максимальное время: 3 секунды максимальное количество проанализированных строк: 78395 Возвращает максимальное количество строк 0

select distinct userid from weekhosnominate 
where userid = 351211 and datatype = 4

анализировать

план

ALTER TABLE `weekhosnominate` ADD INDEX UserID(`UserID`);

Повторите анализ объяснения

результат

Количество строк диапазона соответствия уменьшено с 1 до 288.

Статья 13

Количество выполнений: 110 Максимальное затраченное время: 2 секунды Максимальное количество строк для анализа: 87693 Максимальное количество возвращаемых строк 1

select `inspectioninfo`.`itemmsg` from `inspectioninfo` 
where ( itemid in ( 30 ,31 ) and itemtype = 0
and inspectionid = 109 ) limit 1 ;

анализировать

план:

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

ALTER TABLE `inspectioninfo` ADD INDEX 
InspectionID(`InspectionID`);

Повторите анализ объяснения

результат

Ряды диапазона соответствия уменьшены с 5 до 13.

Статья 14

Производительность: 103 Максимальное время: 2 секунды анализа Максимальное количество строк: 78395 Возвращает максимальное количество строк 0

select `weekhosnominate`.`id` from `weekhosnominate` 
where ( userid = 351211 );

анализировать:

план:

Индексируя идентификатор пользователя поля, чтобы различать и сужать область действия

ALTER TABLE `weekhosnominate` ADD INDEX UserID(UserID) ;

Выполните анализ объяснения еще раз, чтобы обнаружить, что Через идентификатор пользователя индекса диапазон составляет от почти 10 000 полных просмотров таблицы до десятков записей, на которые указывает индекс.

результат:

Количество совпадающих строк диапазона уменьшено с 9k до 288.

Глубокое понимание:

структура mysql

Принцип индекса MySQL

цель индекса
Цель индекса - повысить эффективность запроса. Его можно сравнить со словарем. Если мы хотим найти слово «mysql», мы должны найти букву m, затем найти букву y снизу вниз и затем найдите оставшийся sql. Если индекса нет, то вам может понадобиться просмотреть все слова, чтобы найти то, что вы хотите, что, если я хочу найти слова, начинающиеся с m? Или как насчет слов, начинающихся с ze? Вы чувствуете, что без индекса это вообще невозможно?

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

База данных та же, но она явно намного сложнее, потому что сталкивается не только с запросами на равенство, но и с запросами диапазона (>,

Диск IO и предварительное чтение
Диск доступа упоминался ранее, затем вы кратко представите дисковый ввод-вывод и предварительное чтение, данные чтения диска представляют собой механическое движение, и каждый раз при чтении данные можно разделить на время поиска, задержку вращения, время передачи. Время поиска относится ко времени, необходимому магнитному рычагу для указанной дорожки.Основной диск обычно меньше 5 мс, задержка вращения - это скорость диска, которую мы часто слышим, например, диск 7200 оборотов, что указывает на то, что он может повернуться 7200 в минуту. То есть 1 секунда может повернуться 120 раз, задержка вращения составляет 1/120/2 = 4,17 мс; время передачи относится к времени с диска считывает или записывает данные на диск, как правило, в нулевой точке миллисекунд относительно Первые два времени можно игнорировать. Затем доступ к времени диска, то есть время дискового ввода-вывода составляет около 5 + 4,17 = 9 мс, звучит неплохо, но знает, что машина 500 -MIPS может выполнять 500 миллионов инструкций в секунду из-за инструкций, полагающихся по характеру электричества, другими словами, время выполнения ввода-вывода может выполнять 400 000 инструкций, база данных составляет более 100 000 миллионов или 10 миллионов данных, и каждый раз 9 миллисекунд, это, очевидно, катастрофа. На следующем рисунке представлена ​​сравнительная карта задержек компьютерного оборудования для справки:

аппаратная задержка обработки

REFER:

Принцип индекса MySQL и медленная оптимизация запросов