предыдущий постРезюме дизайна индекса MySQLПредставлено несколько основных факторов, влияющих на структуру индекса, в том числе коэффициент фильтрации, ширина и размер фрагментов индекса, а также сопоставление и фильтрация столбцов. представлено во второй половине статьиПроектирование и оптимизация индекса базы данныхВ книге идеальный процесс проектирования и рутина индекса Samsung, до сих пор, хотя мы освоили метод проектирования индекса с одной таблицей, но у нас нет возможности анализировать и оценивать потребление времени индекса.
В этой статье мы представим два метода анализа производительности индекса, упомянутых в книге: Метод основных вопросов (BQ) и Метод верхней границы быстрой оценки (QUBE).Эти два метода могут помочь нам быстро проанализировать и оценить производительность индекса. Своевременное обнаружение проблемы.
метод основного вопроса
Когда нам нужно рассмотреть анализ существующего запроса SELECT, даже если не хватает времени, запрос должен быть оценен с использованием метода основного вопроса.Содержание оценки очень простое: содержит ли существующий индекс или добавляемый индекс WHERE Все столбцы, используемые в , то есть для текущего запроса, есть ли индекс, который является индексом половинной ширины.
В предыдущей статье мы представили широкий и узкий индексы. Узкий индекс (имя пользователя) на самом деле называется индексом половинной ширины, который содержит все столбцы имени пользователя в WHERE. Текущий индекс имеет только одну звезду для этого запроса. Хотя он избегает случайный ввод-вывод, вызванный недопустимыми запросами к бэк-таблице, если производительность текущего индекса по-прежнему не соответствует потребностям, вы можете добавить возраст, чтобы превратить индекс в широкий индекс (имя пользователя, возраст), чтобы избежать доступа к бэк-таблице. ; для простого запроса на рисунке выше индекс (имя пользователя, возраст) на самом деле является индексом Samsung, но для простого запроса, содержащего ORDER BY Или для более сложных запросов (имя пользователя, возраст) может быть просто индекс с двумя звездами:
В настоящее время, если индекс по-прежнему не соответствует потребностям в производительности, вы можете рассмотреть возможность использования предыдущей статьи.Резюме дизайна индекса MySQLПредставленный в нем метод построения индекса был переработан.
Хотя метод основной задачи может быстро решить некоторые проблемы, вызванные индексами, он не гарантирует достаточной производительности.Когда таблица имеет индекс (город, имя пользователя, возраст), предикат
WHERE username="draveness" AND age="21", использование метода основного вопроса не дает правильных результатов.
Метод быстрой оценки верхнего предела
Метод базовой задачи очень прост, он может помочь нам оценить производительность запроса в кратчайшие сроки, но он не может точно отразить проблему производительности, связанную с индексом, а метод верхней границы быстрой оценки является более точным и сложным методом; Его цель - выявить проблему медленных путей доступа во время разработки программы.Выводом этого метода оценки является локальное время отклика:
Локальное время отклика — это время, требуемое для запроса на сервере базы данных, исключая любые задержки в сети и время связи в многоуровневой среде, только время, необходимое для выполнения задачи запроса.
Время отклика
Локальное время отклика равно сумме времени обслуживания и времени ожидания в очереди. Запрос на запрос должен ожидать в базе данных ответов ЦП и диска. Это также может быть связано с тем, что другие транзакции читают и записывают одни и те же данные, вызывая текущий запрос на ожидание захвата блокировки, однако основная часть времени ответа — это время обслуживания диска:
В процессе расчета QUBE другое время ожидания в очереди, кроме времени ожидания диска, будет игнорироваться, что может упростить весь процесс оценки, а время обслуживания диска в основном включает несколько частей синхронного чтения и записи и асинхронного чтения:
После исключения многих из вышеперечисленных частей мы получаем очень простой процесс оценки, Вход всего времени оценки — это только три входа случайного чтения и последовательного чтения и сбора данных, и они также являются основными факторами, влияющими на запрос:
В процессе сбора данных нет необходимости учитывать влияние разных индексов на один и тот же запрос, потому что один и тот же запрос будет получать один и тот же набор результатов с использованием разных индексов, а также полученные данные будут одинаковыми.
доступ
Когда MySQL читает строку индекса или строку таблицы, происходит доступ. При использовании полного сканирования таблицы или сканирования фрагмента индекса первая прочитанная строка является произвольным доступом. Произвольный доступ требует поиска и поворота диска, поэтому стоимость огромна, и все строки, которые считываются последовательно, считываются через последовательный доступ, что составляет лишь одну тысячную стоимости произвольного доступа.
Большое количество последовательных чтений строк индекса и таблицы может в принципе вызвать некоторые дополнительные спорадические случайные обращения, но это не очень важно для оценки общего запроса; при расчете локального времени отклика Они оцениваются как последовательные обращения.
Пример
Здесь мы просто приводим пример, показывающий, как рассчитать локальное время ответа, необходимое для запроса при использовании индекса, при условии, что у нас естьusersТаблица с 10 миллионами данных:
вusersВ дополнение к индексу первичного ключа таблица также имеет следующие вторичные индексы (имя пользователя, город), (имя пользователя, возраст) и (имя пользователя), когда мы используем запрос, как показано ниже:
Два условия запроса имеют коэффициенты фильтрации 0,05% и 12% соответственно.Запрос может напрямую использовать существующий вспомогательный индекс (имя пользователя, город).Затем мы начинаем оценивать SQL этого шага на основе общего количества строк в таблица и фактор фильтра.время выполнения:
Запрос сначала попадет в индекс (имя пользователя, город), просканирует подходящие фрагменты индекса, индекс будет иметь доступ в общей сложности к 10 000 000 * 0,05% * 12% = 600 фрагментов данных, включая 1 произвольный доступ и 599 последовательных доступов. Доступ , поскольку столбцы в индексе не могут удовлетворить потребности запроса, для каждой строки индекса будет сгенерирован произвольный доступ к таблице, чтобы получить информацию об оставшемся возрасте столбца:
В этом процессе всего генерируется 600 случайных доступов, а также будет 600 операций FETCH в процессе получения набора результатов.В общем, этот SQL-запрос был выполнен всего.601 случайный доступ, 599 последовательных обращений и 600 FETCH.По формуле в предыдущем разделе мы можем получить, что время запроса составляет около 6075,99 мс, что составляет около 6 с, что неприемлемо для большинства приложений.
В течение всего процесса запроса 600 случайных обращений к запросу обратной таблицы стали основным вкладом этого сверхмедленного запроса.Чтобы решить эту проблему, нам нужно только добавить индекс (имя пользователя, город, возраст) или добавить (имя пользователя , город, возраст) к существующему (Добавить новый столбец возраста после имени пользователя, города), чтобы избежать 600 случайных обращений:
Индекс (имя пользователя, город, возраст) на самом деле является индексом Samsung для этого запроса.Вы можете прочитать предыдущую статью о дизайне индекса.Резюме дизайна индекса MySQLЕсли у читателя достаточно времени, настоятельно рекомендуетсяПроектирование и оптимизация индекса базы данныхЭта книга.
Суммировать
Эта статья — самая короткая статья, которую я написал в этом году, и я хотел представить ее подробно.Проектирование и оптимизация индекса базы данныхЧто касается оценочного метода анализа производительности индекса в книге, я тщательно продумывал содержание этой части, и примеры также очень богаты.Полностью представить все содержание только через одну статью сложно, поэтому только часть Пункты знаний кратко представлены, поэтому данная статья называется краткой.
Если у вас есть какие-либо вопросы по содержанию статьи, вы можете оставить сообщение в комментариях.Системе комментариев нужна лестница для использования Disqus.
Follow: Draveness · GitHub