Тестирование производительности запросов в MySQL

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

Некоторые вещи, которые могут быть для этого полезны:

rows столбец в результате EXPLAIN SELECT ... Потом,

mysql> set profiling=1;
mysql> select sql_no_cache * from mytable;
 ...
mysql> show profile;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000063 |
| Opening tables     | 0.000009 |
| System lock        | 0.000002 |
| Table lock         | 0.000005 |
| init               | 0.000012 |
| optimizing         | 0.000002 |
| statistics         | 0.000007 |
| preparing          | 0.000005 |
| executing          | 0.000001 |
| Sending data       | 0.001309 |
| end                | 0.000003 |
| query end          | 0.000001 |
| freeing items      | 0.000016 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000001 |
+--------------------+----------+
15 rows in set (0.00 sec)

Потом,

mysql> FLUSH STATUS;
mysql> select sql_no_cache * from mytable;
...
mysql> SHOW SESSION STATUS LIKE 'Select%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Select_full_join       | 0     |
| Select_full_range_join | 0     |
| Select_range           | 0     |
| Select_range_check     | 0     |
| Select_scan            | 1     |
+------------------------+-------+
5 rows in set (0.00 sec)

И еще одно интересное значение - last_query_cost, которое показывает, насколько дорого оптимизатор оценил запрос (значение - это количество случайных чтений страницы):

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 2635.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)

Документация MySQL - ваш друг.

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

Как предлагается в связанной статье, используйте FLUSH TABLES между тестовыми запусками для максимального сброса (особенно кеша запросов).

Разве ваше тестирование не должно принимать во внимание, что InnoDB сам по себе будет иметь разные состояния во время реальной производительности, так что вас интересует совокупная производительность по нескольким испытаниям? Насколько «реальным» будет ваше тестирование производительности, если вы хотите перезагружать InnoDB для каждой пробной версии? Запрос, который вы отклоняете, потому что он плохо работает сразу после перезапуска, может оказаться лучшим запросом после того, как InnoDB немного нагреется.

На вашем месте я бы сосредоточился на том, что оптимизатор запросов делает отдельно от производительности InnoDB. О том, как настроить InnoDB, много написано, но для начала полезно иметь хорошие запросы.

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

Вы пробовали вообще отключить кеширование запросов? Даже с SQL_NO_CACHE, простое включение кеша запросов приводит к потере около 3%.

Думали ли вы об использовании Maatkit? Одна из его возможностей, с которой я немного знаком, - это захват сетевых данных MySQL с помощью tcpdump и обработка дампа с помощью mk-query-digest. Этот инструмент позволяет отображать некоторые подробные сведения о каждом запросе. Но есть целый ряд других инструментов, которые должны упростить анализ запросов.

Вы можете попробовать рабочую среду mysql, я думал, что у нее есть монитор операторов sql, чтобы вы могли видеть, насколько это быстро и почему.

Полнотекстовые запросы в InnoDB выполняются медленно (LIKE "% query%"), вы ничего не можете сделать для их оптимизации. Решения варьируются от передачи той конкретной таблицы, которую вы запрашиваете, в MyISAM, чтобы вы могли создавать полнотекстовые индексы (которые innoDB не поддерживает), до денормализации строки в индексы с возможностью поиска (не рекомендуется), Doctrine ORM предоставляет простой пример того, как это архивировать: http://www.doctrine-project.org/documentation/manual/1_1/nl/behaviors:core-behaviors:searchable "Правильным" решением вашей проблемы было бы индексирование информации, в которой вы используете полнотекстовый поиск, с помощью таких решений, как Sphinx Search или Apache Solr.

Как было сказано ранее, вы должны учитывать состояние кеша при сравнении результатов, так как заполненный кеш дает чрезвычайно производительные запросы. Вы должны учитывать процент попаданий в кеш для конкретного запроса, даже если это дорогостоящий запрос, если у него коэффициент попадания в кеш 99%, средняя производительность будет очень высокой.

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

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