Мне часто задают один и тот же вопрос: как можно находить запросы, которые необходимо оптимизировать. Ведь, скажем, взглянув на отчет pt-query-digest мы легко найдем медленные запросы или запросы, которые вызывают большую нагрузку на систему, но как мы поймем, существует или нет возможность сделать выполнение этого запросы быстрее? Полный ответ на этот вопрос определенно потребует комплексного анализа, так как существует много путей оптимизации запросов. Однако, существует одна очень полезная метрика, которую вы можете применить — соотношение между количество возвращенных запросом рядов и проанализированными рядами.
Допустим, у нас есть такой пример:
# Time: 120911 17:09:44
# User@Host: root[root] @ localhost []
# Thread_id: 64914 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0
# Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F03
use sbtest;
SET timestamp=1347397784;
select * from sbtest where pad='abc';
Запрос в данном случае вернул 0 рядов (так как у нас нет совпадений), но для этого ему пришлось пройтись по 10 миллионам рядов. Какой сценарий был бы предпочтительнее? Если бы запрос прошелся по тому же числу рядов, которые в итоге он вернет. В таком случае, если я расставлю индексы в таблице, то я получу следующую запись в slow query log, куда падают все медленные запросы:
# Time: 120911 17:18:05
# User@Host: root[root] @ localhost []
# Thread_id: 65005 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 0.000323 Lock_time: 0.000095 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 0
# Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F14
SET timestamp=1347398285;
select * from sbtest where pad='abc';
Значение Rows_examined=0, совпадающее с Rows_sent означает, что запрос достаточно хорошо оптимизирован. Заметьте, если вы подумали, что в этом случае обращения к БД не происходит совсем — вы ошибаетесь. Проход по индексам выполняется, но, так как считаются только строки, которые были найдены и возвращены на верх для обработки MySQL-частью, то значение Rows_examined остается равным нулю.
Казалось бы, все очень просто, но это слишком поспешный вывод. Подобная математика сработает только с запросами без агрегирующих функций/group by, плюс только для запросов, которые проходят по ровно одной таблице. А как быть с запросами, которые затрагивают более одной таблицы?
# Time: 120911 17:25:22
# User@Host: root[root] @ localhost []
# Thread_id: 65098 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 0.000234 Lock_time: 0.000063 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F1D
SET timestamp=1347398722;
select * from sbtest a,sbtest b where a.id=5 and b.id=a.k;
mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | a | const | PRIMARY,k | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)
В этом случае мы на самом деле делаем объединение двух таблиц, но из-за того, что тип доступа к таблицам задан как «константа», MySQL не считает из за доступ к двум таблицам. В случае с «реальным» доступом, вывод будет таким:
# Time: 120911 17:28:12
# User@Host: root[root] @ localhost []
# Thread_id: 65099 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 0.000273 Lock_time: 0.000052 Rows_sent: 1 Rows_examined: 2 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F23
SET timestamp=1347398892;
select * from sbtest a,sbtest b where a.k=2 and b.id=a.id;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | a | ref | PRIMARY,k | k | 4 | const | 1 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sbtest.a.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
2 rows in set (0.00 sec)
В этом случае мы имеем 2 проанализированные строки для каждого множества строк, чего и следовало ожидать, потому что у нас есть 2 (логических) таблицы используемых в этом запросе. Это правило также не будет работать в том случае, если у вас есть в запросе group:
# Time: 120911 17:31:48
# User@Host: root[root] @ localhost []
# Thread_id: 65144 Schema: sbtest Last_errno: 0 Killed: 0
# Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2
# Bytes_sent: 75 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 12F24
SET timestamp=1347399108;
select count(*) from sbtest group by k;
Этот запрос вернет только 2 строки, хотя пройдет по 10 миллионам, и мы не можем в действительности оптимизировать этот запрос по-простому, так как проход по всем строкам действительно необходим для группировки результатов.
В таком случае вы можете задуматься насчет удаления из запроса group by и агрегирующих функций. Тогда запрос превратится в “select * from sbtest” , который вернет все 10 миллионов строк и, следовательно, здесь не будет простых способов оптимизации.
Этот метод создан не для того, чтобы дать вам ясный ответ «да или нет», но он может порядком помочь, какой оптимизации вы можете достигнуть в итоге. Допустим, у меня есть запрос, который использует индекс по 1000 строк и возвращает 10… У меня все еще может быть возможность уменьшить число строк, по которым он проходит, в 100 раз, — при помощи, например, добавления комбинированных индексов.
Итак, кратко — как можно быстро узнать, стоит ли оптимизировать запрос или нет?
— посмотрите, сколько строк возвращает запрос после удаления group by, distinct и агрегирующих функций (A)
— возьмите число пройденных строк, деленное на число таблиц в объединении (B)
— если B меньше или равно A, ваш запрос «идеален»
— если B/A равен 10 или больше. то этот запрос один из самых достойных кандидатов на оптимизацию.
Это простой метод и его можно смело использовать вместе с pt-query-digest, так так последний сообщает не только средние значения, но и краевые.
Оригинал статьи: здесь.
Автор: Go4Yachiyo
