Существует большая база данных, 1 000 000 000 строк, называемых потоками (эти потоки фактически существуют, я не делаю все сложнее, потому что мне это нравится). В потоках есть только несколько вещей, чтобы сделать вещи быстрее: (int id, string hash, int answercount, int dateline (timestamp), int forumid, название строки)
Запрос:
select * from thread where forumid = 100 and replycount > 1 order by dateline desc limit 10000, 100
Поскольку это 1G записей, это довольно медленный запрос. Поэтому я подумал, давайте разделим этот 1G записей на столько таблиц, сколько у меня есть на многих форумах (категориях)! Это почти идеально. Имея много таблиц, у меня меньше записей для поиска, и это действительно быстрее. Теперь запрос будет выглядеть следующим образом:
select * from thread_{forum_id} where replycount > 1 order by dateline desc limit 10000, 100
Это действительно быстрее с 99% форумов (категория), так как большинство из них имеют только несколько тем (100k-1M). Однако из-за того, что некоторые из них содержат около 10 млн записей, некоторые запросы по-прежнему замедляются (0,1 / .2 секунды, для моего приложения !, я уже использую индексы! ).
Я не знаю, как улучшить это с помощью MySQL. Есть ли способ?
Для этого проекта я буду использовать 10 серверов (12 ГБ, жесткий диск 4х7200 об / мин на программном рейде 10, четырехъядерный процессор)
Идея состояла в том, чтобы просто разделить базы данных между серверами, но с проблемой, описанной выше, все еще недостаточно.
Если я установлю cassandra на этих 10 серверах (предположив, что я нахожу время, чтобы он работал, как и предполагалось), следует ли предположить, что нужно повысить производительность?
Что мне делать? Поддерживать работу с MySQL с распределенной базой данных на нескольких машинах или строить кластер cassandra?
Меня попросили указать, что такое индексы, вот они:
mysql> show index in thread; PRIMARY id forumid dateline replycount
Выберите объяснение:
mysql> explain SELECT * FROM thread WHERE forumid = 655 AND visible = 1 AND open <> 10 ORDER BY dateline ASC LIMIT 268000, 250; +----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+ | 1 | SIMPLE | thread | ref | forumid | forumid | 4 | const,const | 221575 | Using where; Using filesort | +----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
Вы должны прочитать следующее и немного узнать о преимуществах хорошо разработанной таблицы innodb и о том, как лучше всего использовать кластерные индексы – доступны только с innodb!
http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html
http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/
затем сконструируйте свою систему в соответствии со следующим упрощенным примером:
Важными особенностями являются то, что в таблицах используется механизм innodb, а первичный ключ для таблицы потоков больше не является единственным ключом auto_incrementing, а составным кластерным ключом на основе комбинации forum_id и thread_id. например
threads - primary key (forum_id, thread_id) forum_id thread_id ======== ========= 1 1 1 2 1 3 1 ... 1 2058300 2 1 2 2 2 3 2 ... 2 2352141 ...
Каждая строка форума включает счетчик next_thread_id (unsigned int), который поддерживается триггером и увеличивается каждый раз, когда поток добавляется к данному форуму. Это также означает, что мы можем хранить 4 миллиарда потоков на форум, а не 4 миллиарда потоков, если использовать один первичный ключ auto_increment для thread_id.
forum_id title next_thread_id ======== ===== ============== 1 forum 1 2058300 2 forum 2 2352141 3 forum 3 2482805 4 forum 4 3740957 ... 64 forum 64 3243097 65 forum 65 15000000 -- ooh a big one 66 forum 66 5038900 67 forum 67 4449764 ... 247 forum 247 0 -- still loading data for half the forums ! 248 forum 248 0 249 forum 249 0 250 forum 250 0
Недостатком использования составного ключа является то, что вы уже не можете просто выбрать поток по одному ключевому значению следующим образом:
select * from threads where thread_id = y;
ты должен сделать:
select * from threads where forum_id = x and thread_id = y;
Однако ваш код приложения должен знать о том, какой форум просматривается пользователем, поэтому реализовать его не так сложно – храните просматриваемый в данный момент forum_id в переменной сеанса или в скрытом поле формы и т. Д.
Вот упрощенная схема:
drop table if exists forums; create table forums ( forum_id smallint unsigned not null auto_increment primary key, title varchar(255) unique not null, next_thread_id int unsigned not null default 0 -- count of threads in each forum )engine=innodb; drop table if exists threads; create table threads ( forum_id smallint unsigned not null, thread_id int unsigned not null default 0, reply_count int unsigned not null default 0, hash char(32) not null, created_date datetime not null, primary key (forum_id, thread_id, reply_count) -- composite clustered index )engine=innodb; delimiter # create trigger threads_before_ins_trig before insert on threads for each row begin declare v_id int unsigned default 0; select next_thread_id + 1 into v_id from forums where forum_id = new.forum_id; set new.thread_id = v_id; update forums set next_thread_id = v_id where forum_id = new.forum_id; end# delimiter ;
Возможно, вы заметили, что я включил answer_count как часть первичного ключа, который немного странен, поскольку (forum_id, thread_id) композит сам по себе уникален. Это просто оптимизация индекса, которая экономит некоторые операции ввода-вывода, когда выполняются запросы, использующие функцию reply_count. Пожалуйста, обратитесь к 2 ссылкам выше для получения дополнительной информации об этом.
Я по-прежнему загружаю данные в свои таблицы примеров, и до сих пор у меня загружено ок. 500 миллионов строк (вдвое больше, чем ваша система). Когда процесс загрузки будет завершен, я должен ожидать,
250 forums * 5 million threads = 1250 000 000 (1.2 billion rows)
Я специально сделал некоторые из форумов, содержащих более 5 миллионов потоков, например, форум 65 имеет 15 миллионов потоков:
forum_id title next_thread_id ======== ===== ============== 65 forum 65 15000000 -- ooh a big one
select sum(next_thread_id) from forums; sum(next_thread_id) =================== 539,155,433 (500 million threads so far and still growing...)
под innodb суммирование next_thread_ids, чтобы дать общее количество потоков намного быстрее, чем обычно:
select count(*) from threads;
Сколько потоков имеет форум 65:
select next_thread_id from forums where forum_id = 65 next_thread_id ============== 15,000,000 (15 million)
снова это быстрее, чем обычно:
select count(*) from threads where forum_id = 65
Хорошо, теперь мы знаем, что у нас около 500 миллионов потоков, а на форуме 65 есть 15 миллионов потоков – посмотрим, как работает схема 🙂
select forum_id, thread_id from threads where forum_id = 65 and reply_count > 64 order by thread_id desc limit 32; runtime = 0.022 secs select forum_id, thread_id from threads where forum_id = 65 and reply_count > 1 order by thread_id desc limit 10000, 100; runtime = 0.027 secs
Выглядит довольно эффектно для меня – так что это одна таблица с 500 миллионами строк (и растет) с запросом, который охватывает 15 миллионов строк за 0,02 секунды (при загрузке!)
К ним относятся:
разбиение по диапазону
Sharding
бросая деньги и оборудование на него
и т.д…
надеюсь, что вы найдете этот ответ полезным 🙂
EDIT : ваших индексов с одним столбцом недостаточно. Вам нужно, по крайней мере, охватить три задействованные столбцы.
Более продвинутое решение: замените replycount > 1 с hasreplies = 1 , создав новое поле hasreplies , равное 1, когда replycount > 1 . Как только это будет сделано, создайте индекс в трех столбцах в следующем порядке: INDEX(forumid, hasreplies, dateline) . Убедитесь, что это индекс BTREE для поддержки заказа.
Вы выбираете на основе:
forumid hasreplies dateline Как только вы это сделаете, выполнение запроса будет включать:
forumid = X Это логарифмическая операция (длительность: log (количество форумов)). hasreplies = 1 (при одновременном совпадении forumid = X ). Это операция с постоянным временем, потому что hasreplies – только 0 или 1. Мое предыдущее предложение индексирования на replycount было неверным, поскольку это был запрос диапазона и, таким образом, не позволял использовать dateline для сортировки результатов (так что вы бы очень быстро выбрали потоки с ответами, но полученный список из миллиона строк пришлось бы сортировать полностью, прежде чем искать 100 элементов, которые вам нужны).
ВАЖНО : хотя это повышает производительность во всех случаях, ваше огромное значение OFFSET (10000!) Уменьшит производительность, потому что MySQL, похоже, не может пропустить вперед, несмотря на то, что читает прямо через BTREE. Таким образом, чем больше ваш OFFSET, тем медленнее будет запрос.
Я боюсь, что проблема OFFSET не решена автоматически, распространяя вычисления на несколько вычислений (как вы пропустите параллельное смещение?) Или переместитесь в NoSQL. Все решения (в том числе NoSQL) будут сводиться к моделированию OFFSET на основе dateline (в основном говоря dateline > Y LIMIT 100 вместо LIMIT Z, 100 где Y – дата элемента со смещением Z ). Это работает и устраняет любые проблемы с производительностью, связанные со смещением, но не позволяет перейти непосредственно на страницу 100 из 200.
Существует часть вопросов, связанных с NoSQL или MySQL. На самом деле это одна из основных вещей, скрытых здесь. Язык SQL легко записывается для человека и бит, который трудно читать для компьютера. В базах больших томов я бы рекомендовал избегать бэкэнд SQL, поскольку для этого требуется дополнительный синтаксический анализ шага. Я провел обширный бенчмаркинг, и есть случаи, когда SQL-парсер является самой медленной точкой. Вы ничего не можете с этим поделать. Хорошо, вы можете использовать предварительно обработанные операторы и получать к ним доступ.
BTW, он не широко известен, но MySQL вырос из базы данных NoSQL. Компания, в которой работали разработчики MySQL Дэвид и Монти, была компанией, занимающейся складированием данных, и им часто приходилось писать собственные решения для необычных задач. Это привело к большому набору внутренних библиотек C, используемых для ручного написания функций базы данных, когда Oracle и другие выполняли плохо. SQL был добавлен в этот почти 20-летний зоопарк в 1996 году для удовольствия. Что произошло после того, как вы это знаете.
На самом деле вы можете избежать накладных расходов SQL с MySQL. Но обычно синтаксический анализ SQL – это не самая медленная часть, но просто полезно знать. Чтобы проверить накладные расходы парсера, вы можете просто сделать тест для «SELECT 1», например;).
Вы не должны пытаться подгонять архитектуру базы данных к оборудованию, которое планируете покупать, а вместо этого планируете покупать оборудование, соответствующее вашей архитектуре базы данных.
Когда у вас будет достаточно ОЗУ для сохранения рабочего набора индексов в памяти, все ваши запросы, которые могут использовать индексы, будут быстрыми. Убедитесь, что ваш буфер ключей установлен достаточно большим, чтобы удерживать индексы.
Поэтому, если 12 Гб недостаточно, не используйте 10 серверов с 12 ГБ ОЗУ, используйте меньше 32 ГБ или 64 ГБ ОЗУ.
Индексы необходимы, но не забудьте выбрать правильный тип индекса: BTREE более подходит при использовании запросов с «<» или «>» в предложениях WHERE, тогда как HASH более подходит, когда у вас много разных значений в одном столбце и вы используете «=» или «<=>» в своем предложении WHERE.
Дальнейшее чтение http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html