Стратегии разбиения на страницы сложных (медленных) наборов данных
Другой потенциальный способ сделать это для SQL Server, который имеет меньше уверенности в системных таблицах (которые подвержены изменениям, версия к версии) должно использовать представления INFORMATION_SCHEMA:
select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
5 ответов
Мне пришлось разработать несколько стратегий разбивки на страницы с использованием PHP и MySQL для сайта, который просматривает более миллиона страниц в день. Я придерживался стратегии поэтапно:
Многоколоночные индексы Я должен был сделать это сначала, прежде чем пытаться создать материализованное представление.
Генерация материализованного представления . Я создал задание cron, которое произвело обычную денормализацию таблиц документов, которые я использовал. Я бы ВЫБРАЛ ... В OUTFILE ... а затем создайте новую таблицу и поверните ее в следующем порядке:
SELECT ... INTO OUTFILE '/tmp/ondeck.txt' FROM mytable ...;
CREATE TABLE ondeck_mytable LIKE mytable;
LOAD DATA INFILE '/tmp/ondeck.txt' INTO TABLE ondeck_mytable...;
DROP TABLE IF EXISTS dugout_mytable;
RENAME TABLE atbat_mytable TO dugout_mytable, ondeck_mytable TO atbat_mytable;
Это позволило сократить время блокировки конкурирующей записи mytable до минимума, а запросы на разбиение на страницы могли бы отбросить на atbat ] материализованный вид. Я упростил сказанное выше, опустив фактические манипуляции, которые не важны.
Memcache Затем я создал оболочку для моего подключения к базе данных, чтобы кэшировать эти разбитые на страницы результаты в memcache. Это была огромная победа в производительности. Однако этого все еще было недостаточно.
Пакетная генерация Я написал демон PHP и извлек в него логику разбивки на страницы. Он будет обнаруживать изменения mytable и периодически регенерировать от самой старой измененной записи до самой последней записи всех страниц в файловой системе веб-сервера. С помощью бит mod_rewrite я мог проверить, существует ли страница на диске, и обслужить ее. Это также позволило мне эффективно использовать преимущества обратного проксирования , позволив Apache обнаруживать заголовки If-Modified-Since и отвечать кодами ответа 304 . (Очевидно,
RE count (*) : При использовании таблиц MyISAM COUNT не создавал проблемы, когда мне удавалось уменьшить количество конфликтов чтения и записи на Таблица. Если бы я делал InnoDB, я бы создал триггер, который обновлял бы соседнюю таблицу с количеством строк. Этот триггер будет просто +1 или -1 в зависимости от операторов INSERT или DELETE.
RE-средства выбора страниц (колесики управления) Когда я перешел к агрессивному кэшированию запросов, запросы колесика большого пальца также кэшировались, а когда дело доходило до пакетной обработки при создании страниц я использовал временные таблицы, поэтому вычисление колесика не было проблемой. Многие вычисления с помощью колесика упростились, потому что это стало предсказуемым шаблоном файловой системы, которому на самом деле нужен только самый большой номер страницы. Наименьший номер страницы всегда был 1.
Мое предложение - запрашивать у MySQL на 1 строку больше, чем вам нужно в каждом запросе, и решать на основе количества строк в наборе результатов, показывать ли следующую страницу - ссылка
MySQL имеет специальный механизм для вычисления приблизительного количества результирующего набора без предложения LIMIT : FOUND_ROWS () .
Я ни в коем случае не эксперт по MySQL, но, возможно, откажусь от COUNT (*) и продолжу с COUNT (id)?
MySQL неплохо подходит для оптимизации запросов LIMIT .
Это означает, что он выбирает соответствующий буфер соединения, файловую сортировку буфер и т. д. ровно столько, чтобы удовлетворить условие LIMIT .
Также обратите внимание, что с 45k строк вам, вероятно, не понадобится точное количество. Приблизительное количество можно вычислить с помощью отдельных запросов к индексированным полям. Скажем, этот запрос:
SELECT COUNT(*)
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
может быть аппроксимирован следующим:
SELECT COUNT(*) *
(
SELECT COUNT(*)
FROM mytable
) / 1000
FROM (
SELECT 1
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
LIMIT 1000
)
, что намного эффективнее в MyISAM .
Если вы приведете пример вашего сложного запроса, я, вероятно, могу сказать кое-что более конкретное о том, как улучшить его разбиение на страницы.
Также обратите внимание, что с 45k строками вам, вероятно, не понадобится точное количество. Приблизительное количество можно вычислить с помощью отдельных запросов к индексированным полям. Скажем, этот запрос:
SELECT COUNT(*)
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
может быть аппроксимирован следующим:
SELECT COUNT(*) *
(
SELECT COUNT(*)
FROM mytable
) / 1000
FROM (
SELECT 1
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
LIMIT 1000
)
, что намного эффективнее в MyISAM .
Если вы приведете пример вашего сложного запроса, я, вероятно, могу сказать кое-что более конкретное о том, как улучшить его разбиение на страницы.
Также обратите внимание, что с 45k строк вам, вероятно, не понадобится точное количество. Приблизительное количество можно вычислить с помощью отдельных запросов к индексированным полям. Скажем, этот запрос:
SELECT COUNT(*)
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
может быть аппроксимирован следующим:
SELECT COUNT(*) *
(
SELECT COUNT(*)
FROM mytable
) / 1000
FROM (
SELECT 1
FROM mytable
WHERE col1 = :myvalue
AND col2 = :othervalue
LIMIT 1000
)
, что намного эффективнее в MyISAM .
Если вы приведете пример вашего сложного запроса, я, вероятно, могу сказать кое-что более конкретное о том, как улучшить его разбиение на страницы.