Comments 19
Если честно, то эта статья вообще не похожа на написанную разработчиком.
Куда больше похоже что она написана либо контент-менеджером, либо интерном.
Потому что разработчики в статьях пишут не оторванный от реальности список советов из интернета, а делятся примерами из собственного опыта.
У вас в компании СберЗдоровье реально были такие гении, которые использовали char в таблицах, и вы, на основании приведенных исследований, переделывали эти поля на varchar?
У вас реально есть запросы с миллионным оффсетом?
У вас реально используются memory таблицы?
Единственную отсылку к реальному опыту я вижу только в одном пункте.
Вы точно разработчик, а не студент, нанятый компанией СберМедицина продвигать ее блог на Хабре? Давайте вы тогда напишете что-нибудь интересное для других разработчиков, а не отписку для рекламы блога, в стиле "10 способов ускорить ваше похапе"?
Примеры №1 и №2 следовало объединить под единым лозунгом "Выбирайте правильные типы данных". Или уж тогда добавляйте советы типа "Используйте DATE вместо DATETIME, если Вам не нужна компонента времени", "храните JSON не в TEXT, а в JSON" и т.п. Ну и очень хочется спросить - а что, кто-то всё ещё использует строковые поля фиксированного размера для данных динамической длины?
Пример №3 совершенно не упоминает о действительно важных свойствах, недоступных Memory, а именно о внешних ключах и полнотексте. И да, транзакции и индексы Memory тоже не поддерживает, Archive в этом не одинок. Кроме того, насколько велика разница, если InnoDB таблица и индексы полностью прогреты (кэшированы)? Если разница по-прежнему троекратна (а она и будет приблизительно таковой - впрочем, её легко уменьшить до где-то двукратной), то причина этой разницы связана с чем угодно, но только не с тем, что Memory-таблица лежит в ОЗУ.
Пример №4 очень странен. Почему Вы решили сравнивать именно запись? Записывается-то значение один раз, а вот читать его будут неоднократно, может, искать и даже сортировать по нему. Вы сравните связывание и поиск по описываемым типам данных - сразу станет видно, что никакой "экономии на спичках" нет и в помине, и разница вполне себе ощущаема.
Пример №5 - не понимаю, зачем ДВА запроса, когда всё прекрасно объединяется в один. Пометка "что WHERE IN изменит порядок записей, поэтому его придется запоминать из первого запроса" - просто изумляет.. проблема с порядком не оттого, что "WHERE IN изменит порядок записей", а потому что Вы в процессе оптимизации тупо забыли добавить ORDER BY во второй запрос. Также следовало до конца пояснить, почему "mysql не нужно обращаться к диску для первого запроса" - для неспециалиста информация, откуда этот запрос выуживает значение id, совершенно неочевидна.
Пример №6 "Использование NULL для обозначения отсутствия значения может немного сохранить место" ... Ась? А Вы знаете, что NULLable поле требует больше места, чем NOT NULL? Какая же тут экономия - эффект будет с точностью до наоборот. Впрочем, разница в один байт как правило вообще не сказывается на потребности в дисковом пространстве, а для именно использованной (из примера №5) таблицы - не сказывается вообще. Как и откуда Вы умудрились получить 20 мегабайт экономии - ну очень большая загадка. Вероятнее всего, Вы просто неточно воспроизвели порядок заполнения таблицы.
Уж извините, но прочитав, как Вы представились в начале статьи, я не ожидал такого уровня верхоглядства.
Спасибо за столь детальный анализ! Со многим согласен.
Примеры 1-4: Вижу только как вы сами написали бы их и что упомянули бы, кажется, это окей - спасибо за дополнение ?
Пример 5: Расскажите, пожалуйста, как возможно это сделать ОДНИМ запросом?
Пример 6: Тут, похоже, вы меня не так поняли - место в таблице с NULLable полями экономится только в сравнении с указанием DEFAULT значения (например 0 или '') вместо NULL
как возможно это сделать ОДНИМ запросом?
SELECT *
FROM `customer`
WHERE `id` IN (
SELECT `id`
FROM `customer`
ORDER BY `first_name` LIMIT 1000000, 10
)
ORDER BY `first_name`;
место в таблице с NULLable полями экономится только в сравнении с указанием DEFAULT значения (например 0 или '') вместо NULL
Теперь вообще не понял. DEFAULT-значение поля - это вообще свойство поля, присутствующее исключительно в определении структуры таблицы и в принципе не влияющее на файл данных. Можно взять таблицу с DEFAULT NULL и без проблем скопировать её TABLESPACE в таблицу той же структуры, но с DEFAULT 0 или вообще без DEFAULT...
Насчёт того, что "NULLable поля редко имеет смысл индексировать из-за низкой селективности индекса" - это всего лишь частный случай... Давно и прочно эмпирически установлено, что при селективности более 5-7% использование индекса менее эффективно, чем fullscan. Что же до похожести планов - так он похож просто потому, что индекс используется не как сортированный набор, а как покрывающий. То есть на самом деле мы имеем всё тот же fullscan, но не по данным, а по индексу. Ну и, понятно, имеем два сравнения вместо одного (точнее, статистически полтора).
Кстати, попробуйте тот же запрос, но в версии
SELECT id
FROM customer
WHERE store_id IS NOT NULL
AND store_id=FLOOR(RAND()*50000) LIMIT 1;
Интересно, как сервер построит план выполнения на тех же структуре и данных, и какое получится время выполнения...
`id` IN (SELECT… LIMIT 1000000, 10)
А вы пробовали? Мне mariadb написала, что не умеет так. Впрочем, через join всё получилось.
P.S. Конечно, простота планировщика запросов mysql, не сумевшего построить приличный план выполнения для select * … limit 100000, 10, вызывает некоторое недоумение.
Мне mariadb написала, что не умеет так.
Я в шоке...MySQL и MariaDB - оно, конечно, родственники, но сейчас родственники настолько далёкие, что переносить сведения по одной СУБД на другую можно не всегда, а когда можно, то делать это надо ну с крайней осторожностью.
К тому же, если речь не идёт об ну очень древней версии СУБД, вместо подзапроса можно использовать и CTE:
WITH cte AS (
SELECT `id`
FROM `customer`
ORDER BY `first_name` LIMIT 1000000, 10
)
SELECT customer.*
FROM `customer`
JOIN cte USING (id)
ORDER BY `first_name`;
а на какой версии mysql вы проверяете?
в текущей документации не вижу, чтобы к MySQL does not support LIMIT in subqueries for certain subquery operators были приписки, что начиная с какой-то версии это ограничение не действует.
что же до вашего запроса с cte, там join вместо in, к нему это ограничение неприменимо.
вдогонку: вы будет смеяться, но запрос в виде
select * from users u where
id in (select id from (select id from users order by email LIMIT 1000000, 10) a);mariadb приняла.
в виде
with
cte as (select id from users order by email LIMIT 1000000, 10)
select * from users u where id in (select id from cte);тоже приняла, но после предыдущего запроса это уже не удивляет.
MySQL 8.0, LIMIT в CTE: https://dbfiddle.uk/wvnZaUIm
Этот код после соотв. корректировки работает и в MariaDB 10.3: https://dbfiddle.uk/7LuqxcqN
А вот напрямую LIMIT в подзапросе WHERE IN - да, не поддерживается ни тут, ни там. И вряд ли будет поддерживаться - из-за особенностей обработки LIMIT. Кстати, изрядные тормоза при большом offset связаны совершенно с той же причиной.
там join вместо in, к нему это ограничение неприменимо
id уникально и не содержит NULL (первичный ключ как-никак), так что не понимаю, почему Вы считаете, что неприменимо.
кто-то всё ещё использует строковые поля фиксированного размера для данных динамической длины?
Почему бы и нет? Если у таблицы FIXED ROW FORMAT они и так будут фиксированной длины, что может несколько ускорить выборки в некоторых случаях. Есть достаточно много кейсов, когда хранятся не числовые данные ограниченной длины, да взять какой-нить СНИЛС к примеру
+
а еще поле фиксированной длинны не покинет кластерный индекс, а вот VARCHAR будет заменено ссылкой на диск, в случае если размер строки начнет превышать выделенный лимит
я пытался это воспроизвести - но не получилось продемонстрировать разницу
подробнее тут: https://dev.mysql.com/blog-archive/externally-stored-fields-in-innodb/
Ну я в основном про Aria Engine в MariaDB или окончательно забытый MyISAM. В FIXED конечно не сунуть динамические поля типа VARCHAR или TEXT, но часто это и не надо. В плюсах, лично у нас, меньшая фрагментация (вернее даже отсутствие таковой, мы не удаляем записи, только помечаем).
Текст самих сообщений в отдельной таблице в данном случае, тут только всякая фиксированная мета-инфа типа sender/receiver/timestamp/flags etc
Это какие-то высосанные из пальца примеры.
Ускорение: за чет чего?
FIXED ROW FORMAT: за счет чего? СНИЛС как бы не один в таблице хранится, там еще какой-нибудь емейл будет.
Выше пример таблицы в 34 миллиона записей, там вообще нет CHAR.
СНИЛС приведен как пример строковых данных фиксированной длины. По поводу Fixed мы уже обсуждали с Вами вроде, каждая запись имеет фикс размер в таблице, у нее четкое смещение внутри. Причем во-первых одно, во-вторых легко вычисляемое. Отсюда и скорость, ибо не надо из разных мест файла в случае фрагментации искать где там куски этих полей VARCHAR/TEXT по файлу раскиданы. Причем у каждого такого поля может быть лютая фрагментация
"Искать" в любом случае ничего не надо. Все смещения есть в первичном индексе. Не менее "четкие". Если у вас выборка идет по индексу, то разницы вы не заметите. Если без индекса, то никакой фикс вас не спасет.
Вы заметили, что ваши примеры носят чисто теоретический характер? Не "у нас вот фиксированный формат дал прирост", а "ну вот снилс к примеру".
Плюс вы как будто не слышите собеседника. Я вам пишу, что снилс отдельно в воздухе не висит, он будет в таблице с данными переменной длины и никакой погоды не сделает. А вы мне просто повторяете по второму разу, "это пример". Это выдуманный из пальца пример. О чем и был мой исходный комментарий.
Блин, Вы так любите теории какие-то придумывать, выше глазками взгляните. 34 ляма, Aria, fixed format (ну да, там нет char полей). И у нас это дает то, что все это работает в виртуалке на 4 гигах оперативы. И индексы там конечно есть. Но! У вас либо в индексе одно смещение, по которому вы можете достать запись целиком всегда (да его даже хранить не надо - оно вычисляется, утрировано, строка*размер ), либо у вас в индексе или где-там еще 20 смещений (тут строка, тут кусок этого поля, тут еще кусок, тут начало еще одного, а вооон там еще его кусок). Что по-вашему быстрее - сразу достать всю строку или побегать по смещениям, подергать кусочки, склеить их и выдать?
Я вам пишу, что снилс отдельно в воздухе не висит, он будет в таблице с данными переменной длины и никакой погоды не сделает
Да что вы к СНИЛС привязались? Наличие в таблицах полей переменной длины это вопрос нормализации ваших конкретных данных. Если уж взять какую-то гипотетическую задачу хранения персональных данных гражданина, то поля переменной длины, не связанные с государственными идентификаторами можно и нужно хранить отдельно. А те самые идентификаторы (sic!) имеют фиксированные длины (СНИЛС, ИНН, номер паспорта, прав - все фиксированые)
Я вспомнил да. Вы тот мальчик, который нашел на улице функцию хэширования, и с тех пор всё хэширует — пароли, логины, емейлы, телефоны и дни рождения.
Если вы меня словом "мальчик" оскорбить пытались - ну я явно не девочка, мне 48 и борода :) Ничего плохого не видим с коллегами в хешировании или шифровании ни паролей, ни е-мейлов, ни телефонов. Есть плюсы, есть минусы. Все зависит от задачи.
Судя по Вашим статьям на Хабре - Вы сильно любите тестировать разные гипотезы - можете протестировать в том числе и разницу между FIXED/DYNAMIC/PAGE. Только давайте возьмем несколько "движков" InnoDB, Aria и что Вам еще захочется.
Оптимизация при работе с MySQL — экономия на спичках или с миру по нитке?