Оптимизация структуры таблицы MySQL и влияние размера строки. Mysql оптимизация таблиц

optimization - Способы оптимизации таблицы MySQL

Несколько возможных стратегий.

Если набор данных настолько велик, может быть полезно сохранить определенную информацию избыточно: сохранение кеш-таблиц, если доступ к некоторым записям происходит гораздо чаще, чем другие, денормализовать информацию (либо ограничить количество объединений, либо создавать таблицы с меньшим столбцы, поэтому у вас есть бережливый стол, чтобы хранить в памяти все время), или сводя резюме для быстрого поиска итогов.

Сводные таблицы (таблицы) могут быть синхронизированы либо периодически генерируя их, либо используя триггеры, либо даже комбинируя их с помощью таблицы кеша в течение последнего дня, на котором вы можете рассчитать фактические итоговые значения, и резюме для исторических данных... даст вам полную точность, не требуя для чтения полного индекса. Проверьте, что обеспечивает наилучшую производительность в вашей ситуации.

Разделение таблицы по периодам, безусловно, является опцией. Это похоже на разделение, но Блог Mayflower советует сделать это самостоятельно, поскольку реализация MySQL, похоже, имеет определенные ограничения.

Кроме того: если данные в этих исторических таблицах никогда не изменяются, и вы хотите уменьшить пространство, вы можете использовать myisampack. Индексы поддерживаются (вам нужно перестроить), и сообщается о повышении производительности, но я подозреваю, что вы будете получать скорость при чтении отдельных строк, но сталкиваетесь с уменьшением производительности при больших чтениях (так как много строк нужно распаковывать).

И последнее: вы могли бы подумать о том, что вам нужно из исторических данных. Нужна ли вам такая же информация, что у вас есть для более поздних записей, или есть вещи, которые просто не важны? Я мог бы представить, если у вас есть журнал доступа, например, что он хранит все виды информации, такие как ip, референтный url, запрошенный url, пользовательский агент... Возможно, через 5 лет пользовательский агент вообще не интересуется, это прекрасно, чтобы объединить все запросы от одного ip для одной страницы + css + javascript + изображения в одну запись (возможно, для разных файлов есть таблица с разными "множителями" ), а реферальные URL-адреса нужны только для количества событий и могут быть отделенным от точного времени или ip.

qaru.site

Оптимизация таблицы MySQL Структура и влияние размера строки

Одна из моих таблиц базы данных стала довольно большой, до такой степени, что, как я думаю, она влияет на производительность на моем сайте (это определенно делает резервные копии намного медленнее).

Он имеет ~ 13 000 000 строк и имеет размер 4.2 гигабайта, из которых 1,2 гигабайта - это данные.

структура выглядит следующим образом:

CREATE TABLE IF NOT EXISTS `t1` ( `id` int(10) unsigned NOT NULL, `int2` int(10) unsigned NOT NULL, `int3` int(10) unsigned NOT NULL, `int4` int(10) unsigned NOT NULL, `char1` varchar(255) NOT NULL, `int5` int(10) NOT NULL, `char2` varchar(1024) DEFAULT NULL, `char3` varchar(1024) NOT NULL, PRIMARY KEY (`id`,`int2`,`int3`,`int4`), KEY `key1` (`id`,`int2`,`char1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Общие операции в этой таблице вставки и выбирает, строки никогда не обновляются и редко удаляются. int2 - это номер текущей версии, который обычно означает только строки с самым высоким значением int2 для этого идентификатора.

Я думал несколько способов оптимизации этого и мне было интересно, что бы быть, который один преследовать:

char1 (который находится в индексе) на самом деле содержит только около 40000 различных строк. Я мог бы переместить строки во вторую таблицу (idchar -> char), а затем просто сохранить идентификатор в моей основной таблице за счет дополнительного этапа поиска id во время вставок и выбора.
char2 и char3 часто пусты. Я мог бы переместить их в отдельную таблицу, после чего я бы сделал LEFT JOIN в выборе.
Даже если char2 и char3 содержат данные, они обычно короче 1024 символов. Я мог бы сократить их до ~ 200.

Какой из них, по вашему мнению, является наиболее перспективным? Уменьшает ли размер строки (либо, делая char1 в целое число, либо удаляя/изменяя размер столбцов) в таблицах MySQL InnoDB, действительно оказывает большое влияние на производительность?

Thanks

stackoverrun.com

Оптимизация структуры таблиц MySQL с миллионами записей Безопасный SQL

Хорошо, я новичок в MySQL и SQL в целом. Я использую sql в своих базах доступа и веб-страницах aspx, но я не думаю, что я очень эффективен с этим. Ниже приведена моя текущая таблица Mysql и мой конечный результат. В настоящее время я чувствую, что делаю слишком много, чтобы получить результаты, которые я хочу.

Итак, мой вопрос – это лучший способ оптимизировать эту таблицу? Таблица заполнена от сотен устройств Android, отправляющих приложения, которые они установили на своих устройствах. Я собираю эту информацию и подсчитываю, сколько раз они показывали, какие из них самые популярные.

Сейчас я накопил более 3 миллионов строк, и я экспортирую эту таблицу и импортирую для доступа. Затем я запускаю два запроса в доступе для получения моего счета и экспорта в Excel для создания отчета ниже.

Уникальный идентификатор – это имя активности и класса в сочетании с / Вот как я определяю свои подсчеты

Я знаю, что этот вопрос можно рассматривать как дискуссию, но на самом деле ответить на вопрос, я просто хотел бы знать структуру, которую я должен использовать в своей базовой таблице.

Я знаю, что у меня даже нет настройки индекса, но не знаю, какое поле может это сделать?

Если этот вопрос будет рассмотрен, я сразу его рассмотрю.

На основе рекомендаций я добавил индекс, показанный ниже, и выполнил следующий запрос, чтобы получить нужные мне данные. Я правильно добавил индекс? Как насчет первичного ключа? я просто добавляю автоматически сгенерированный?

SELECT COUNT( name ) , CONCAT( userapps.activity, '/', userapps.class ) FROM userapps GROUP BY name

Solutions Collecting From Web of "Оптимизация структуры таблиц MySQL с миллионами записей"

sql.fliplinux.com

mysql - Оптимизация MySQL для большой таблицы myisam

Давайте рассмотрим это, начиная с вашего внутреннего запроса:

SELECT acctuniqueid, MIN( radacctid ) radacctid FROM radacct WHERE username='batman215' and acctstarttime between '2016-02-03 12:10:47' and '2016-04-25 16:46:01' GROUP BY acctuniqueid

Вы ищете совпадение равенства на username и соответствие диапазона на acctstarttime. Затем вы используете acctuniqueid для группировки и вытягивания экстремального значения (MIN()) из radacctid.

Поэтому, чтобы ускорить этот подзапрос, вам понадобится следующий составной индекс.

(username, acctstarttime, acctuniqueid, radacctid)

Как это работает? Подумайте об индексе (это индексы BTREE) как отсортированный список значений в нем.

Механизм запросов выполняет произвольный доступ к списку - быстро, O (log (n)) - для поиска первой записи, соответствующей username и нижнему концу вашего диапазона BETWEEN.
Затем он последовательно сканирует список, запись по записи, пока он не достигнет верхнего уровня диапазона BETWEEN. Это называется сканированием диапазона индекса.
При сканировании он ищет каждое новое значение acctuniqueid, по порядку, а затем берет самое низкое значение - первое по порядку - radacctid, а затем переходит к следующему значению accuniqueid, Это называется сканированием индексов, и это чудесно дешево.

Итак, добавьте этот составной индекс. Это, вероятно, сильно повлияет на производительность вашего запроса.

Внешний запрос выглядит следующим образом.

SELECT sum(acctinputoctets) as upload, sum(acctoutputoctets) as download FROM radacct a INNER JOIN ( /*an aggregate * yielding acctuniqueid and raddactid * naturally ordered on those two columns */ ) b ON a.acctuniqueid = b.acctuniqueid AND a.radacctid = b.radacctid

Для этого вам понадобится составной индекс покрытия

(acctuniqueid, radacctid, acctinputoctets, acctoutputoctets)

Эта часть запроса также удовлетворена маской индекса.

Первые два столбца в индексе позволяют искать каждую нужную строку на основе внутреннего результата запроса.
Механизм запросов может затем сканировать индекс, суммируя значения двух других столбцов.

(Это называется индексом покрытия, потому что он содержит некоторые столбцы, которые присутствуют только потому, что мы хотим их значения, а не потому, что хотим их индексировать. Некоторые другие модели и модели СУБД позволяют включать дополнительные столбцы в индексы, не делая их доступный для поиска. Это немного дешевле, особенно в операциях INSERT. MySQL этого не делает.)

Итак, ваш первый элемент действия: добавьте эти два составных индекса и повторите запрос.

По вашему вопросу, похоже, что вы разместили много индексов с одним столбцом на своем столе в надежде, что они ускорят работу. Это пресловутый антипаттерн в дизайне базы данных. С уважением, вы должны избавиться от любых индексов, которые вам не нужны. Они не помогают в запросах, и они замедляют INSERTS. Это ваш второй элемент действия.

В-третьих, прочитайте это http://use-the-index-luke.com/ Это очень полезно.

Pro tip: вы видели, как я отформатировал ваш запрос? Разработка личного соглашения о форматировании, в котором четко отображаются таблицы, столбцы, условия ON и другие аспекты запроса, чрезвычайно важна, когда вы должны это понимать.

qaru.site

sql - Оптимизация структуры таблицы MySQL и влияние размера строки

Одна из моих таблиц базы данных стала довольно большой, до такой степени, что я думаю, что это влияет на производительность на моем сайте (это определенно делает резервные копии намного медленнее).

Он имеет ~ 13 000 000 строк и имеет размер 4,2 ГБ, из которых 1,2 ГиБ - данные.

Структура выглядит так:

CREATE TABLE IF NOT EXISTS 't1' ( 'id' int(10) unsigned NOT NULL, 'int2' int(10) unsigned NOT NULL, 'int3' int(10) unsigned NOT NULL, 'int4' int(10) unsigned NOT NULL, 'char1' varchar(255) NOT NULL, 'int5' int(10) NOT NULL, 'char2' varchar(1024) DEFAULT NULL, 'char3' varchar(1024) NOT NULL, PRIMARY KEY ('id','int2','int3','int4'), KEY 'key1' ('id','int2','char1') ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Общие операции в этой таблице вставляются и выбираются, строки никогда не обновляются и редко удаляются. int2 - это номер текущей версии, что означает, что обычно выбираются только строки с самым высоким значением int2 для этого идентификатора.

Я подумывал о нескольких способах его оптимизации, и мне было интересно, какой из них будет преследовать:

char1 (который находится в индексе) фактически содержит только около 40 000 различных строк. Я мог бы переместить строки во вторую таблицу (idchar → char), а затем просто сохранить идентификатор в моей основной таблице за счет дополнительного этапа поиска id во время вставок и выбора.
char2 и char3 часто пусты. Я мог бы переместить их в отдельную таблицу, после чего я бы сделал LEFT JOIN в выборе.
Даже если char2 и char3 содержат данные, они обычно короче 1024 символов. Я мог бы сократить их до ~ 200.

благодаря

qaru.site

optimization - Оптимизация индекса таблицы MySQL

Я работаю с приложением, которое имеет базу данных MySQL на Amazon RDS. Таблица в вопросах настраивается как таковая:

CREATE TABLE 'log' ( 'id' bigint(20) unsigned NOT NULL AUTO_INCREMENT, 'timestamp' datetime NOT NULL, 'username' varchar(45) NOT NULL, .. snip some varchar and int fields .. PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Эта система была в бета-версии на некоторое время, и уже набор данных довольно велик, и запросы начинают довольно медленными.

SELECT COUNT(*) FROM log --> 16307224 (takes 105 seconds to complete)

Эта таблица в значительной степени используется только для создания одного отчета с таким запросом, как это

SELECT timestamp, username, [a few more] FROM log WHERE timestamp BETWEEN '2012-03-30 08:00:00' AND '2012-03-30 16:00:00' AND username='XX'

Что обычно даст что-то между 1000 и 6000 строк за 100-180 секунд для завершения, что означает, что веб-приложение часто будет тайм-аут и оставить пустой отчет (я также посмотрю на тайм-аут, но этот вопрос для корня причина).

Я не очень хорошо разбираюсь в базах данных, но думаю, что это МЕЖДУ, что меня убило. Я думаю, что я должен каким-то образом использовать временную метку в качестве индекса. Временная метка, связанная с именем пользователя, должна по-прежнему обеспечивать уникальность (я не использую поле id для чего-либо).

Если кто-нибудь там с предложениями по оптимизации, я все уши.

ОБНОВИТЬ:

Таблица теперь изменена на следующие

CREATE TABLE 'log' ( 'id' bigint(20) unsigned NOT NULL AUTO_INCREMENT, 'timestamp' datetime NOT NULL, 'username' varchar(45) NOT NULL, .. snip .. 'task_id' int(10) unsigned DEFAULT NULL, PRIMARY KEY ('id'), KEY 'index_un_ts' ('timestamp','username') ) ENGINE=InnoDB DEFAULT CHARSET=latin1

EXPLAIN SELECT возвращает следующее

id => 1 select_type => SIMPLE table => log type => range possible_keys => index_un_ts key => index_un_ts key_len => 55 ref => rows => 52258 Extra => Using where; Using index

qaru.site