Оптимизация базы данных Sqlite

У меня есть база данных sqlite3 с тремя таблицами:

CREATE TABLE document (
  id Int PRIMARY KEY NOT NULL,
  root_id Int,
  name Varchar(100),
  active Tinyint
);
CREATE INDEX IDX_documentId ON document (id);
CREATE INDEX IDX_documentName ON document (name);

CREATE TABLE dictionary (
  id Int PRIMARY KEY NOT NULL,
  word Varchar(100) NOT NULL
);
CREATE INDEX IDX_dictionaryId ON dictionary (id);
CREATE UNIQUE INDEX IDX_dictionaryWord ON dictionary (word ASC);

CREATE TABLE document_index (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  document_id Int NOT NULL,
  word_id Int NOT NULL,
  FOREIGN KEY(document_id) REFERENCES document(id),
  FOREIGN KEY(word_id) REFERENCES dictionary(id)
);
CREATE INDEX IDX_documentIndexId ON document_index (id);
CREATE INDEX IDX_documentIndexDocId ON document_index (document_id);
CREATE INDEX IDX_documentIndexWordId ON document_index (word_id);

И у меня есть sql-скрипт для выбора всех документов, содержащих слово из словаря:

SELECT document.id, document.name
FROM document
     INNER JOIN document_index on document_index.document_id=document.id
     INNER JOIN dictionary on dictionary.id=document_index.word_id
WHERE dictionary.word LIKE @pQuery
   AND document.active = 1
   AND document.root_id in (@pRoot1, @pRoot2, @pRoot3, @pRoot4, @pRoot5, @pRoot6, @pRoot7)

Когда словарь содержит ~= 400 000 записей, документ ~= 1000 записей и document_index ~= 500 000 записей, запрос выполняется около 30 секунд на моем iPad 2.

Как оптимизировать запрос или изменить структуру базы данных (например, добавить индексы), чтобы сократить время запроса?


sql sqlite sql-optimization
person alexmac    schedule 04.01.2013    source источник
comment
Ну и индекс на document.root_id выскакивает с ходу, подозреваю, что Like - это ваша реальная проблема. Что передается в качестве параметра (в основном)   -  person Tony Hopkinson    schedule 04.01.2013

Ответы (4)


arrow_upward
2
arrow_downward

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

FTS позволяет использовать быструю версию предложения MATCH вместо изначально медленной LIKE.

К сожалению, FTS не включен по умолчанию на iOS, но, по-видимому, вы все равно можете сделать это, если создадите собственное приложение и включите свою собственную копию SQLite с включенным FTS.

person mvp    schedule 04.01.2013

arrow_upward
2
arrow_downward

Узким местом, скорее всего, является часть WHERE dictionary.word LIKE @pQuery.

  1. у вас нет индекса по Dictionary.word, поэтому SQLite нужно сканировать всю таблицу
  2. Вы используете оператор LIKE, который в большинстве случаев не может использовать индексы.

Действительно ли необходимо для вашего варианта использования использовать запрос LIKE вместо простой проверки на равенство строк?

person Philipp    schedule 04.01.2013
comment
Мне действительно нужно использовать LIKE, потому что мне нужно сравнивать по подстроке. - person alexmac; 04.01.2013
comment
@Alexander: Если вы выполняете сопоставление префикса с LIKE, это все равно определенно стоит индекса. sqlite.org/optoverview.html#like_opt - person Donal Fellows; 04.01.2013
comment
Когда вы опишите, как именно выглядят ваши запросы, мы могли бы что-то подсказать. Но это требует отдельного вопроса. - person Philipp; 04.01.2013

arrow_upward
0
arrow_downward

Попробуйте использовать dictionary.word = @pQuery вместо dictionary.word LIKE @pQuery

Запустите "анализ"

person Nadeer Madampat    schedule 04.01.2013
comment
К сожалению, я не могу использовать «равно», потому что мне нужно сравнивать по подстроке. - person alexmac; 04.01.2013

arrow_upward
0
arrow_downward

Я нашел решение. Это решение увеличило скорость выполнения запроса в 60 раз! раз. Я нашел его здесь а более подробно - здесь. Все просто, я заменил выражение LIKE на сравнение >= и ‹:

Старый:

dictionary.word LIKE 'prezident%'

новый:

dictionary.word >= 'prezident' AND dictionary.word < 'prezidentz' /* Added z to the second string*/

У этого решения есть одно ограничение, я могу искать по части строки, но только в конце строки, т.е. 'expr%'.

Спасибо всем за помощь!

person alexmac    schedule 06.01.2013