Как я могу быстро выбрать набор идентификаторов из большой таблицы?

У меня есть большая таблица с идентификатором в качестве основного. Около 3 миллионов строк, и мне нужно извлечь небольшой набор строк на основе заданного списка идентификаторов.

В настоящее время я делаю это на where... in, но это очень медленно, от 5 до 10 секунд.

Мой код:

select id,fa,fb,fc 
from db1.t1 
where id in(15,213,156,321566,13,165,416,132163,6514361,... );

Я пытался запрашивать один идентификатор за раз, но это все еще медленно. подобно

select id,fa,fb,fc from db1.t1 where id =25;

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

select id,fa,fb,fc from db1.t1 inner join  db1.temp  on t1.id=temp.id

Есть ли способ сделать это быстрее?

вот стол.

CREATE TABLE  `db1`.`t1` (
  `id` int(9) NOT NULL,
  `url` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastModified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Хорошо, вот объяснение выбора.

id=1,
select_type='SIMPLE', 
table='t1', 
type='range', 
possible_keys='PRIMARY', 
key='PRIMARY',
key_len= '4',
ref= '', 
rows=9, 
extra='Using where'

mysql performance where-in
person user1748365    schedule 03.10.2013    source источник
comment
Хм, PK всегда должен быть проиндексирован, но, судя по вашему описанию, это не так. Какие индексы у вас есть?   -  person Brad Peabody    schedule 03.10.2013
comment
Он имеет идентификатор в качестве первичного ключа. Я думаю, это означает, что он проиндексирован. поправьте меня, если я ошибаюсь.   -  person user1748365    schedule 03.10.2013
comment
ПОКАЗАТЬ ИНДЕКСЫ ИЗ db1.t1;   -  person Brad Peabody    schedule 03.10.2013
comment
это InnoDB или MyISAM?   -  person Christian Mark    schedule 03.10.2013
comment
Также запустите EXPLAIN <your query> и опубликуйте результаты в своем вопросе в текстовой форме.   -  person peterm    schedule 03.10.2013
comment
Не складывается, что в вашем утверждении CREATE, которое вы упомянули в PRIMARY KEY (urlId), нет столбца urlId.   -  person peterm    schedule 03.10.2013
comment
Попробуйте использовать InnoDB и сравните производительность.   -  person Brad Peabody    schedule 03.10.2013
comment
Стол довольно большой. Просто преобразование может занять несколько часов. Сейчас около 60гб. Я надеюсь, что есть другой выбор. иначе я попробую проверить сегодня вечером.   -  person user1748365    schedule 03.10.2013
comment
Что говорит SELECT @@key_buffer_size;? Ваша таблица активно вставляется/обновляется, пока вы выбираете из нее?   -  person peterm    schedule 03.10.2013
comment
key_buffer составляет 512M, а доля буфера ключей составляет 18%.   -  person user1748365    schedule 03.10.2013

Ответы (3)


arrow_upward
0
arrow_downward

Вот несколько советов, как можно ускорить работу таблицы:

  • Старайтесь избегать сложных запросов SELECT к таблицам MyISAM, которые часто обновляются, чтобы избежать проблем с блокировкой таблиц, возникающих из-за конфликта между читателями и писателями.
  • Чтобы отсортировать индекс и данные по индексу, используйте myisamchk --sort-index --sort-records=1 (при условии, что вы хотите отсортировать по индексу 1). Это хороший способ ускорить запросы, если у вас есть уникальный индекс, из которого вы хотите прочитать все строки по порядку в соответствии с индексом. В первый раз, когда вы сортируете большую таблицу таким образом, это может занять много времени.
  • Для таблиц MyISAM, которые часто изменяются, старайтесь избегать всех столбцов переменной длины (VARCHAR, BLOB и TEXT). Таблица использует динамический формат строки, если она включает хотя бы один столбец переменной длины.
  • Строки автоматически сжимаются в индексах MyISAM с префиксом и конечным пробелом. См. "Синтаксис CREATE INDEX".
  • Вы можете повысить производительность, кэшируя запросы или ответы в своем приложении, а затем одновременно выполняя множество вставок или обновлений. Блокировка таблицы во время этой операции гарантирует, что кэш индекса очищается только один раз после всех обновлений. Вы также можете воспользоваться кэшем запросов MySQL для достижения аналогичных результатов; см. "Кэш запросов MySQL".

Вы можете прочитать далее в этой статье об оптимизации ваших запросов.

person Christian Mark    schedule 03.10.2013

arrow_upward
0
arrow_downward

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

Я видел в плане объяснения SQL, что когда мы используем идентификатор where в (...), он преобразует его в Where (ID = 1) или (ID = 2) или (Id = 3) ..... так что список больше много или, поэтому для очень больших таблиц избегайте IN ()

Попробуйте «Объяснить» этот SQL, и он может сказать вам, где находится фактическое узкое место. Проверьте эту ссылку http://dev.mysql.com/doc/refman/5.5/en/explain.html надеюсь сработает

person Builder    schedule 27.11.2013

arrow_upward
-1
arrow_downward

Похоже, исходный оператор sql с использованием «in» должен быть в порядке, поскольку столбцы Id индексируются.

Я думаю, вам в основном нужен более быстрый компьютер — вы делаете этот запрос на виртуальном хостинге?

person Super Nerd    schedule 03.10.2013
comment
Ну, это i7 с 16 ГБ оперативной памяти на SSD. На самом деле я получил это на коробке разработки, а также тестировал на vps. VPS, кажется, немного лучше. - person user1748365; 03.10.2013