Оптимизация COUNT строки базы данных MySQL
У меня есть база данных MySQL (5.6.26) с большим количеством данных, и у меня проблема с выбором COUNT при объединении таблиц.
Этот запрос занимает около 23 секунд для выполнения:
SELECT COUNT(0) FROM user
LEFT JOIN blog_user ON blog_user.id_user = user.id
WHERE email IS NOT NULL
AND blog_user.id_blog = 1
Таблица пользователь MyISAM и содержит данные пользователя, такие как идентификатор, адрес электронной почты, имя и т. д.
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`email` varchar(100) DEFAULT '',
`hash` varchar(100) DEFAULT NULL,
`last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`) USING BTREE,
UNIQUE KEY `email` (`email`) USING BTREE,
UNIQUE KEY `hash` (`hash`) USING BTREE,
FULLTEXT KEY `email_full_text` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=5728203 DEFAULT CHARSET=utf8
Таблица blog_user является InnoDB и содержит только id, id_user и id_blog (пользователь может иметь доступ к нескольким блогам). id — это ПЕРВИЧНЫЙ КЛЮЧ, и есть индексы для id_blog, id_user и id_blog-id_user.
CREATE TABLE `blog_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_blog` int(11) NOT NULL DEFAULT '0',
`id_user` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id_blog_user` (`id_blog`,`id_user`) USING BTREE,
KEY `id_user` (`id_user`) USING BTREE,
KEY `id_blog` (`id_blog`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5250695 DEFAULT CHARSET=utf8
Я удалил все остальные таблицы и больше не подключался к серверу MySQL (среда тестирования).
Что я нашел до сих пор:
- Когда я удаляю некоторые столбцы из пользовательской таблицы, продолжительность запроса уменьшается (например, 2 секунды на удаленный столбец)
- Когда я удаляю все столбцы из пользовательской таблицы (кроме идентификатора и адреса электронной почты), продолжительность запроса составляет 0,6 секунды.
- Когда я изменяю таблицу blog_user также на MyISAM, продолжительность запроса составляет 46 секунд.
- Когда я изменяю пользовательскую таблицу на InnoDB, продолжительность запроса составляет 0,1 секунды.
Вопрос в том, почему MyISAM так медленно выполняет команду?
Решение
Сначала несколько комментариев к вашему запросу (после его исправления):
SELECT COUNT(*)
FROM user u LEFT JOIN
blog_user bu
ON bu.id_user = u.id
WHERE u.email IS NOT NULL AND bu.id_blog = 1;
Псевдонимы таблиц облегчают как написание, так и чтение запроса. Что еще более важно, у вас есть LEFT JOIN но твой WHERE пункт превращает его в INNER JOIN, Итак, напишите это так:
SELECT COUNT(*)
FROM user u INNER JOIN
blog_user bu
ON bu.id_user = u.id
WHERE u.email IS NOT NULL AND bu.id_blog = 1;
Разница важна, потому что она влияет на выбор, который может сделать оптимизатор.
Далее, индексы помогут этому запросу. Я предполагаю, что blog_user(id_blog, id_user) а также user(id, email) являются лучшими показателями.
Причина, по которой количество столбцов влияет на исходный запрос, заключается в том, что он выполняет много операций ввода-вывода. Чем меньше столбцов, тем меньше страниц требуется для хранения записей — и тем быстрее выполняется запрос. Надлежащие индексы должны работать лучше и последовательнее.
Другие решения
Чтобы ответить на реальный вопрос (почему myisam медленнее, чем InnoDB), я не могу дать авторитетный ответ.
Но это, безусловно, связано с одним из наиболее важных различий между двумя механизмами хранения: InnoDB поддерживает внешние ключи, а myisam — нет. Внешние ключи важны для объединения таблиц.
Я не знаю, улучшит ли определение ограничения по внешнему ключу скорость, но наверняка это гарантирует согласованность данных.
Еще одно замечание: вы замечаете, что время удаляет столбцы. Это указывает на то, что запрос требует полного сканирования таблицы. Этого можно избежать, создав индекс по столбцу электронной почты. Надеемся, что user.id и blog.id_user уже имеют индекс, в противном случае это ошибка. Столбцы, которые участвуют во внешнем ключе, явном или нет, всегда должны иметь индекс.
Это долгое время после события, которое будет очень полезно для OP, и все вышеизложенные предложения по ускорению запроса вполне уместны, но я удивляюсь, почему никто не заметил вывод EXPLAIN. В частности, почему был выбран индекс по электронной почте и как это связано с определением столбца электронной почты в пользовательской таблице.
Оптимизатор выбрал индекс для столбца электронной почты, предположительно потому, что он включен в предложение where. key_len для этого индекса сравнительно длинный, и это достаточно большая таблица с учетом значения auto_increment, поэтому требования к памяти для этого индекса были бы значительно выше, чем если бы он выбрал столбец id (4 байта против 303 байтов). Столбец электронной почты имеет значение NULLABLE, но по умолчанию содержит пустую строку, поэтому, если приложение явно не установит NULL, вы все равно не найдете NULL в этом столбце. Также вы не найдете более одной записи со значением по умолчанию с учетом ограничения UNIQUE. Столбец DEFAULT и ограничение UNIQUE, по-видимому, полностью расходятся друг с другом.
Учитывая вышесказанное и тот факт, что нам нужно только количество в запросе, я бы тогда подумал, служит ли электронная часть предложения where какой-либо цели, кроме замедления запроса, поскольку каждое значение сравнивается с NULL. Без этого оптимизатор, вероятно, выберет первичный ключ и сделает работу намного лучше. Еще лучше был бы запрос, который полностью игнорировал пользовательскую таблицу и брал счет на основе индекса покрытия для blog_user, который выделил Гордон Линофф.
Здесь стоит упомянуть и другие проблемы с индексацией:
На пользовательском столе
UNIQUE KEY `id` (`id`) USING BTREE,
является избыточным, поскольку id является ПЕРВИЧНЫМ КЛЮЧОМ и, следовательно, УНИКАЛЬНЫ по определению.
Чтобы ответить на ваш последний вопрос,
Вопрос в том, почему MyISAM так медленно выполняет команду?
MyISAM зависит от скорости вашего жесткого диска,
INNODB, как только данные читаются, со скоростью ОЗУ. 1-й раз, когда выполняется запрос, может быть загрузка данных, второй и более поздние будут избегать жесткого диска, пока не устареет из ОЗУ.