По заявкам трудящихся решил написать еще одну статью, посвященную оптимизации запросов в MySQL.
В прошлой статье habrahabr.ru/blogs/mysql/38907 рассматривались вопросы оптимизации LIMIT, GROUP BY, COUNT.
В данной статье я немного вернусь к вышеописанному и опишу пару примеров, с которыми столкнулся на проекте недавно, после этого приведу еще пару небольших примеров относительно того что такое хорошо и что такое плохо в MySQL.
Возвращаясь к теме больших LIMIT в запросах. Например есть у нас такой запрос, который вытягивает из базы картинки и имена пользователей, которые эти картинки выложили, притом вытягиваем мы эту информация для картинок с ID > 2500 и интересуют нас результаты с 5000 по 5100. (В данном примере стоит обратить внимание на саму конструкцию запроса).
Итак, что должен сделать наш запрос в этом случае:
1. Из множества страниц `tx_localrep_images` as tli
2. Из них выбираем те, которые удовлетворяют условию tli.uid > 2500
3. Дальше, т.к. мы имеем LEFT JOIN, то кол-во результатов (записей) в результирующей выборке не зависит от склейки, поэтому MySQL вполне мог бы сделать LIMIT на той выборке которую мы имеем к этому моменту (полученную после шага 2) и только после этого начинать склейку с таблицей `fe_users`. НО MySQL этого НЕ делает!
А делает следующее: вначале делает склейку, а уже потом отсекает первые «ненужные» 5000 записей!!!
Это совсем не то поведение, которое мы ожидали, но не беда! Существует вполне простое решение данной проблемы с помощью DERIVED TABLE. Вот такой запрос выполняется гораздо быстрее (разность в скорости выполнения прямо пропорциональна кол-ву записей в LIMIT)
После этого, я решил немного поэкспериментировать как работает MySQL оптимизатор с запросами вида SELECT(*)
Соответственно есть такой стандартный запрос.
Иесть такой, менее стандартный. Прошу уважаемое сообщество не писать о том, что LEFT JOIN в данном запросе не имеет смысла, т.к. не влияет на число записей в результирующей выборке. Это и так понятно всем… всем кроме MySQL оптимизатора :-), который исправно выполнит склейку, а уж потом будет вычислять число записей в ней.
Поэтому данный запрос на сравнительно небольшом наборе данных (50 000 строк) выполняется в 20 раз медленней.
COUNT(*) vs COUNT(column_name)
Замечал не раз, что многие считают, что COUNT(*) это алиас COUNT(column_name). Это совершенно не так.
Во-первых, эти запросы могут возвращать разные результаты. Такое может проявляться когда столбец column_name может содержать NULL значения. Т.е. конструкция COUNT(column_name) вернет кол-во записей с column_name IS NOT NULL.
Во-вторых, эти запросы выполняются с разной скоростью. Например, такой запрос
может выполнять гораздо дольше
т.к. запрос, использующий COUNT(*), при наличии индекса по полю tli.uid будет использывать покрывающий индекс и, соответственно, выполнится очень быстро. Первый же запрос будет искать COUNT методом ROW SCAN, о чем и говорит «Extra: USING WHERE» в EXPLAIN этого запроса.
На самом деле можно сделать так, чтобы и первый запрос использовал покрывающий индекс, для этого следует добавить следующий индекс к данной таблице
Но ИМХО, чем вводить дополнительные индексы, для одного единственного запроса, который их использует, и которые занимают лишнее место на диске и что самое ощутимое в кеше, а также тормозят наши изменения данных (UPDATE, INSERT, DELETE), то лучше уж использовать COUNT(*).
Поля, объявленные как DEFAULT NULL
Также если я оговорился и привел в пример сказав, что столбец может быть объявлен как DEFAULT NULL.
То сразу скажу, что такие столбцы лучше не объявлять вообще, если вы изначально не планируете хранить NULL — значения. Существует очень много задач, где нет необходимости в хранении NULL значений, однако очень многие создают таблицы содержащие NULL значения. Храниение НУЛЛ значений усложняет работу внутреннего механизма MySQL, а именно работа с индексами по этим полям и ведение статистики индексов, а также сравнение значений по таким полям.
When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM. Even when you do need to store a «no value» fact in a table, you might not need to use NULL. Consider using zero, a special value, or an empty string instead. The performance improvement from changing NULL columns to NOT NULL is usually small, so don't make finding and changing them on an existing schema a priority unless you know they are causing problems. However, if you're planning to index columns, avoid making them nullable if possible. © — High Performance MySQL, Second Edition
Длинна поля INT(length)
Немного отвлекусь, и скажу пару слов о хранении данных, вообще в этой статье говорить об этом не собирался. но сейчас состоялся разговор с одним горе оптимизатором… Соответственно добавлю пару строк и об этом, может быть и среди сообщества найдутся такие, кто повторяет его ошибки…
Как известно лучше подбирать типы данных для столбцов минимально достаточные для хранения всего диапазона значений в нем. Т.е. иногда люди используют INT там где спокойно можно использовать TINY INT и т.д.
В MySQL при создании таблиц можно задавать длину полей, что-то вроде `column` int(10) UNSIGNED NOT NULL
Так вот, я обнаружил создание таблиц, в которых значения длинны полей везде были разные от 1 до 20. Как оказалось некоторые думают, что MySQL будет выделять такое кол-во памяти для хранения поля, чтобы вместить его максимальное значение. Может быть плохо объяснил. Покажу на примере. Возьмем такой объявление:
`column` int(10) UNSIGNED NOT NULL
так вот, некоторые думают, что MySQL сделает следующее
1. создаст число из 10-ти 9-к (максимальное число), т.е. 999999999
2. найдет степень 2-ки минимально достаточную (т.е. минимальную из больших) для хранения этого числа, т.е в нашем случае
2^30 = 1073741824
3. данное значение округляется до байт в большую сторону, т.е. до 32 = 4 байта
Вобщем весь алгоритм описанный выше ничего общего к реальной жизни НЕ имеет!
На самом деле для MySQL под поле INT(1) выделится столько же памяти для хранения сколько и под INT(20).
Т.к. данное значение используется для вывода значений, т.е. размер зарезервированного места. Например когда мы выполняем запросы через command-line interface и.т.д.
Хранение IP-адресов в БД.
Кстати, я уже отошел от темы статьи, поэтому приведу последний trick, который на самом деле многие используют, кроме новичков, но именно для них я его и напишу.
Очень часто вижу, что люди хранят в базе IP-адрес используя тип данных VARCHAR(15), это очень неэкономно, притом достаточно медленно работает в поисках по range
Для хранения IP адресов в MySQL существует 2-фии.
Первая — INET_ATON
Позволяет преобразовывать строку, состоящую из 4 чисел, разделенных точками в значение типа INT UNSIGNED.
Делается это по такому алгоритму.
SELECT INET_ATON('X.Y.Z.J');
X*256^3+ Y*256^2+ Z*256^1+ J*256^0
Функция INET_NTOA выполняет обратное преобразование (из числа, к привычному виду IP-адреса разделенному точками).
Соответственно все что нам нужно — это создать в таблице поле UNSIGNED INT, в котором будет хранится преобразованное ф-ией INET_ATON число.
Соответственно если мы хотим, скажем, выполнить поиск и достать все IP адреса из какой-то подсети, скажем 255.255.0.0
То можно выполнить такой запрос
Что при наличии индекса по полю ip бужет достаточно быстро.
И последнее всегда думайте какой оптимальный тип данный можно выбрать для хранения нужного вам поля!
Например, для хранения маски подсети некоторые выберут VARCHAR(15), некоторые — INT и применят ф-ии INET_ATON, INET_NTOA. Но правильным вариантов в данном случае является выбор поля TINY INT для хранения количества единиц.
На этом пожалуй закончу. Устал уже писать.
ЗЫ. Оставьте мнение в комментах интересно ли вам или нет. Если нет — так и пишите: «Пощади нас, золотая антилопа! Хватит!»
ЗЗЫ. Спасибо хабра-сообществу за советы по компоновке статей и подаче материала. В особенности %hlomzik% и %maxshopen%
В прошлой статье habrahabr.ru/blogs/mysql/38907 рассматривались вопросы оптимизации LIMIT, GROUP BY, COUNT.
В данной статье я немного вернусь к вышеописанному и опишу пару примеров, с которыми столкнулся на проекте недавно, после этого приведу еще пару небольших примеров относительно того что такое хорошо и что такое плохо в MySQL.
Возвращаясь к теме больших LIMIT в запросах. Например есть у нас такой запрос, который вытягивает из базы картинки и имена пользователей, которые эти картинки выложили, притом вытягиваем мы эту информация для картинок с ID > 2500 и интересуют нас результаты с 5000 по 5100. (В данном примере стоит обратить внимание на саму конструкцию запроса).
Итак, что должен сделать наш запрос в этом случае:
1. Из множества страниц `tx_localrep_images` as tli
2. Из них выбираем те, которые удовлетворяют условию tli.uid > 2500
3. Дальше, т.к. мы имеем LEFT JOIN, то кол-во результатов (записей) в результирующей выборке не зависит от склейки, поэтому MySQL вполне мог бы сделать LIMIT на той выборке которую мы имеем к этому моменту (полученную после шага 2) и только после этого начинать склейку с таблицей `fe_users`. НО MySQL этого НЕ делает!
А делает следующее: вначале делает склейку, а уже потом отсекает первые «ненужные» 5000 записей!!!
SELECT
SQL_NO_CACHE tli.`uid`, tli.`caption`, fe.`username`
FROM
`tx_localrep_images` as tli
LEFT JOIN
`fe_users` as fe ON `tli`. cruser_id = fe.uid
WHERE
tli.uid > 2500
LIMIT
5000, 100;
* This source code was highlighted with Source Code Highlighter.
Это совсем не то поведение, которое мы ожидали, но не беда! Существует вполне простое решение данной проблемы с помощью DERIVED TABLE. Вот такой запрос выполняется гораздо быстрее (разность в скорости выполнения прямо пропорциональна кол-ву записей в LIMIT)
SELECT
SQL_NO_CACHE tli.`uid`, tli.`caption`, fe.`username`
FROM
(
SELECT
tli.`uid`, tli.`caption`, tli.`cruser_id`
FROM
`tx_localrep_images` as tli
WHERE
tli.uid > 2500
LIMIT
5000, 100
) as tli
LEFT JOIN
`fe_users` as fe ON `tli`. cruser_id = fe.uid;
* This source code was highlighted with Source Code Highlighter.
После этого, я решил немного поэкспериментировать как работает MySQL оптимизатор с запросами вида SELECT(*)
Соответственно есть такой стандартный запрос.
SELECT
SQL_NO_CACHE count(*)
FROM
`tx_localrep_images` as tli
WHERE
tli.uid > 500;
* This source code was highlighted with Source Code Highlighter.
Иесть такой, менее стандартный. Прошу уважаемое сообщество не писать о том, что LEFT JOIN в данном запросе не имеет смысла, т.к. не влияет на число записей в результирующей выборке. Это и так понятно всем… всем кроме MySQL оптимизатора :-), который исправно выполнит склейку, а уж потом будет вычислять число записей в ней.
SELECT
SQL_NO_CACHE count(*)
FROM
`tx_localrep_images` as tli
LEFT JOIN
`fe_users` as fe ON `tli`. cruser_id = fe.uid
WHERE
tli.uid > 500;
* This source code was highlighted with Source Code Highlighter.
Поэтому данный запрос на сравнительно небольшом наборе данных (50 000 строк) выполняется в 20 раз медленней.
COUNT(*) vs COUNT(column_name)
Замечал не раз, что многие считают, что COUNT(*) это алиас COUNT(column_name). Это совершенно не так.
Во-первых, эти запросы могут возвращать разные результаты. Такое может проявляться когда столбец column_name может содержать NULL значения. Т.е. конструкция COUNT(column_name) вернет кол-во записей с column_name IS NOT NULL.
Во-вторых, эти запросы выполняются с разной скоростью. Например, такой запрос
SELECT
SQL_NO_CACHE count(tli.`type`)
FROM
`tx_localrep_images` as tli
WHERE
tli.uid > 500;
* This source code was highlighted with Source Code Highlighter.
может выполнять гораздо дольше
SELECT
SQL_NO_CACHE count(*)
FROM
`tx_localrep_images` as tli
WHERE
tli.uid > 500;
* This source code was highlighted with Source Code Highlighter.
т.к. запрос, использующий COUNT(*), при наличии индекса по полю tli.uid будет использывать покрывающий индекс и, соответственно, выполнится очень быстро. Первый же запрос будет искать COUNT методом ROW SCAN, о чем и говорит «Extra: USING WHERE» в EXPLAIN этого запроса.
На самом деле можно сделать так, чтобы и первый запрос использовал покрывающий индекс, для этого следует добавить следующий индекс к данной таблице
alter table `tx_localrep_images` add key cover_key(uid, type);
* This source code was highlighted with Source Code Highlighter.
Но ИМХО, чем вводить дополнительные индексы, для одного единственного запроса, который их использует, и которые занимают лишнее место на диске и что самое ощутимое в кеше, а также тормозят наши изменения данных (UPDATE, INSERT, DELETE), то лучше уж использовать COUNT(*).
Поля, объявленные как DEFAULT NULL
Также если я оговорился и привел в пример сказав, что столбец может быть объявлен как DEFAULT NULL.
То сразу скажу, что такие столбцы лучше не объявлять вообще, если вы изначально не планируете хранить NULL — значения. Существует очень много задач, где нет необходимости в хранении NULL значений, однако очень многие создают таблицы содержащие NULL значения. Храниение НУЛЛ значений усложняет работу внутреннего механизма MySQL, а именно работа с индексами по этим полям и ведение статистики индексов, а также сравнение значений по таким полям.
When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM. Even when you do need to store a «no value» fact in a table, you might not need to use NULL. Consider using zero, a special value, or an empty string instead. The performance improvement from changing NULL columns to NOT NULL is usually small, so don't make finding and changing them on an existing schema a priority unless you know they are causing problems. However, if you're planning to index columns, avoid making them nullable if possible. © — High Performance MySQL, Second Edition
Длинна поля INT(length)
Немного отвлекусь, и скажу пару слов о хранении данных, вообще в этой статье говорить об этом не собирался. но сейчас состоялся разговор с одним горе оптимизатором… Соответственно добавлю пару строк и об этом, может быть и среди сообщества найдутся такие, кто повторяет его ошибки…
Как известно лучше подбирать типы данных для столбцов минимально достаточные для хранения всего диапазона значений в нем. Т.е. иногда люди используют INT там где спокойно можно использовать TINY INT и т.д.
В MySQL при создании таблиц можно задавать длину полей, что-то вроде `column` int(10) UNSIGNED NOT NULL
Так вот, я обнаружил создание таблиц, в которых значения длинны полей везде были разные от 1 до 20. Как оказалось некоторые думают, что MySQL будет выделять такое кол-во памяти для хранения поля, чтобы вместить его максимальное значение. Может быть плохо объяснил. Покажу на примере. Возьмем такой объявление:
`column` int(10) UNSIGNED NOT NULL
так вот, некоторые думают, что MySQL сделает следующее
1. создаст число из 10-ти 9-к (максимальное число), т.е. 999999999
2. найдет степень 2-ки минимально достаточную (т.е. минимальную из больших) для хранения этого числа, т.е в нашем случае
2^30 = 1073741824
3. данное значение округляется до байт в большую сторону, т.е. до 32 = 4 байта
Вобщем весь алгоритм описанный выше ничего общего к реальной жизни НЕ имеет!
На самом деле для MySQL под поле INT(1) выделится столько же памяти для хранения сколько и под INT(20).
Т.к. данное значение используется для вывода значений, т.е. размер зарезервированного места. Например когда мы выполняем запросы через command-line interface и.т.д.
Хранение IP-адресов в БД.
Кстати, я уже отошел от темы статьи, поэтому приведу последний trick, который на самом деле многие используют, кроме новичков, но именно для них я его и напишу.
Очень часто вижу, что люди хранят в базе IP-адрес используя тип данных VARCHAR(15), это очень неэкономно, притом достаточно медленно работает в поисках по range
Для хранения IP адресов в MySQL существует 2-фии.
Первая — INET_ATON
Позволяет преобразовывать строку, состоящую из 4 чисел, разделенных точками в значение типа INT UNSIGNED.
Делается это по такому алгоритму.
SELECT INET_ATON('X.Y.Z.J');
X*256^3+ Y*256^2+ Z*256^1+ J*256^0
Функция INET_NTOA выполняет обратное преобразование (из числа, к привычному виду IP-адреса разделенному точками).
Соответственно все что нам нужно — это создать в таблице поле UNSIGNED INT, в котором будет хранится преобразованное ф-ией INET_ATON число.
Соответственно если мы хотим, скажем, выполнить поиск и достать все IP адреса из какой-то подсети, скажем 255.255.0.0
То можно выполнить такой запрос
SELECT
ip
FROM
`ips`
WHERE
`ips`.ip > INET_ATON('255.255.0.0')
* This source code was highlighted with Source Code Highlighter.
Что при наличии индекса по полю ip бужет достаточно быстро.
И последнее всегда думайте какой оптимальный тип данный можно выбрать для хранения нужного вам поля!
Например, для хранения маски подсети некоторые выберут VARCHAR(15), некоторые — INT и применят ф-ии INET_ATON, INET_NTOA. Но правильным вариантов в данном случае является выбор поля TINY INT для хранения количества единиц.
На этом пожалуй закончу. Устал уже писать.
ЗЫ. Оставьте мнение в комментах интересно ли вам или нет. Если нет — так и пишите: «Пощади нас, золотая антилопа! Хватит!»
ЗЗЫ. Спасибо хабра-сообществу за советы по компоновке статей и подаче материала. В особенности %hlomzik% и %maxshopen%
