Перейти к содержимому

Фото
- - - - -

InnoDB и запрос LIKE '%%'

mysql

  • Вы не можете создать новую тему
  • Please log in to reply
20 ответов в этой теме

#1 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 18:15

Доброе время суток!

 

Кто хорошо разбирается в InnoDB будьте добры помочь.

 

Недавно перевёл проект с MyISAM на InnoDB.

 

Сначала разницы в скорости работы почти не заметил, а потом обратил внимание, что зверски стали тормозить запросы вроде LIKE '%%'

 

Версия сервера 5.1.58, лежит на общем хостинге, так что обновить не светит (слышал, что с более поздной версии поддерживается FULLTEXT индекс).

 

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

 

Есть ли какие-то способы ускорить этот запрос? В гугле ничего не нашёл :(

 

Заранее признателен!


  • 0

#2 skill-A

skill-A

    Huge Cojones

  • Постоялец
  • 6 705 сообщений

Отправлено 26 Февраль 2014 - 18:25

https://dev.mysql.co...odb-tuning.html

 

http://stackoverflow...sam-performance

 

http://dev.mysql.com...ext-search.html


Сообщение изменено: skill-A (26 Февраль 2014 - 18:27 )

  • 0

улыбнись


#3 EastHastings

EastHastings

    Титулярный советникъ

  • Постоялец
  • 2 852 сообщений

Отправлено 26 Февраль 2014 - 18:29

убери % из запросов. % и индексы между собой не дружат


  • 0

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


#4 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 18:42

Если я правильно помню, то LIKE, REGEXP и прочие вообще не дружат с индексами. Как и любой другой FULLTEXT SCAN. Запусти команду EXPLAIN, и сравни (если это возможно), в чём разница на InnoDB и MyISAM


Сообщение изменено: Akhenaton (26 Февраль 2014 - 18:45 )

  • 0

#5 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 18:45

убери % из запросов. % и индексы между собой не дружат

 

Легко сказать. Тогда как мне осуществлять поиск-то?

 

 

Спасибо за ссылки, но ничего конкретно по моему случаю там не нашёл.


  • 0

#6 skill-A

skill-A

    Huge Cojones

  • Постоялец
  • 6 705 сообщений

Отправлено 26 Февраль 2014 - 18:46

simonsays, покрути настройки и пойми что на что влияет?


  • 0

улыбнись


#7 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 18:50

И вообще, что такое "зверски тормозить" и насколько большая у тебя таблица (БД) ?


  • 0

#8 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 18:57

И вообще, что такое "зверски тормозить" и насколько большая у тебя таблица (БД) ?

 

Запрос с LIKE занимает секунды 4, на MyISAM выполнялся мгновенно. В таблице 100-200к записей


  • 0

#9 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 19:02

Тогда смотри команду EXPLAIN (просто допиши это слово перед самим запросом). Смотри, на что мускуль ориентируется при выборке. Что-то мне подсказывает, что один из них (иннодб или myisam) тупо не дружит с индексами.

 

А столько данных за раз ты выпаскиваешь из таблицы и насколько сложен твой запрос ? Ну там, всякие адские JOIN, куча nested queries ?


Сообщение изменено: Akhenaton (26 Февраль 2014 - 19:03 )

  • 0

#10 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 19:07

Тогда смотри команду EXPLAIN (просто допиши это слово перед самим запросом). Смотри, на что мускуль ориентируется при выборке. Что-то мне подсказывает, что один из них (иннодб или myisam) тупо не дружит с индексами.

 

А столько данных за раз ты выпаскиваешь из таблицы и насколько сложен твой запрос ? Ну там, всякие адские JOIN, куча nested queries ?

Я EXPLAIN уже делал. И то, что игнорируются индексы при '%%', уже понял :) Запрос только на одну таблицу. Но в запросе 6 LIKE '%%' (по трём полям, по 2 раза).

Я и пытаюсь выяснить, есть ли какое-то обходное решение.


  • 0

#11 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 19:10

А ты смотрел имменно разницу (если такая возможность была/есть) в выполнении запроса при помощи EXPLAIN ?


  • 0

#12 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 19:11

В смысле, сравнивал ли с MyISAM? Нет. Чтобы сравнить сейчас, нужно обратно ставить MyISAM, не очень хочу это делать.


  • 0

#13 skill-A

skill-A

    Huge Cojones

  • Постоялец
  • 6 705 сообщений

Отправлено 26 Февраль 2014 - 19:13

сделаq копию таблицы и сравни

 

что за железо?


Сообщение изменено: skill-A (26 Февраль 2014 - 19:17 )

  • 0

улыбнись


#14 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 19:18

В смысле, сравнивал ли с MyISAM? Нет. Чтобы сравнить сейчас, нужно обратно ставить MyISAM, не очень хочу это делать.

А вот это пробовал делать ?

Disk space for InnoDB is likely to be 2-3 times as much as for MyISAM.

MyISAM and InnoDB use RAM radically differently. If you change all your tables, you should make significant adjustments:
⚈ key_buffer_size -- small but non-zero; say, 10M;
⚈ innodb_buffer_pool_size -- 70% of available RAM

 

Стащил отсюда: http://mysql.rjweb.o...p/myisam2innodb

 

Может ему не хватает RAM-а для своих дел и он начинает типо свопить ... дисковое I/O оно, суко, не быстрое.


  • 0

#15 Demetrio

Demetrio

    uid=0(root)

  • Админ
  • 18 649 сообщений
  • Откуда:Tallinn

Отправлено 26 Февраль 2014 - 19:26

У него же общий хостинг.
  • 0

#16 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 19:30

Кстати ... общий хостинг. Зачем оно надо, если у вас такие серьёзные (ну относительно, конечно) базы данных. Тем более, что скоро уже 6-ая версия мускула выйдет, а вы до сих пор на этой старой сидите. 

VPS на том же зоне вообще копейки стоит - ставь чё хочешь. Хоть PostgreSQL, который, скорее всего, лучше всего приспособлен для вашей задачи, потому что его создавали работать эффективно по полнотекстовому поиску. Он и индексировать умеет и всё подряд делать.

 

ИМХО


Сообщение изменено: Akhenaton (26 Февраль 2014 - 19:32 )

  • 0

#17 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 21:37

Грубо говоря, вариантов обойти это на уровне базы данных кроме как вернуться обратно на MyISAM нет?


  • 0

#18 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 21:41

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


  • 0

#19 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 22:09

На домашнем нет смысла, на нём всё и так быстро и чудесно работает.

 

Транзакции - очень много операций, где вставляется запись не в одну таблицу, а сразу и в зависимую какую-нибудь и связующую. По той же причине MyIsam не подходил - где-нибудь забудешь про то, что зависимые записи есть, забудешь запрос на удаление прописать, и в таблице куча "мёртвых" записей остаётся. Мелочь, конечно, но неприятно.

 

Пожалуй, с частным сервером единственный выход.


  • 0

#20 Akhenaton

Akhenaton
  • Постоялец
  • 8 025 сообщений

Отправлено 26 Февраль 2014 - 22:15

Я ничего не знаю про вашу систему, но, по-моему, проще использовать ORM для того языка, на котором работете ... тогда вообще по барабану, какая там база данных, ибо все зависимости настраиваются (в том числе и на удаление). Но это так - лирика и оффтоп.

 

А почему на домашнем компьютере всё быстро летает ? Откуда эта разница берётся ?


  • 0

#21 simonsays

simonsays
  • Пользователь
  • 835 сообщений

Отправлено 26 Февраль 2014 - 22:26

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

 

Пока вернусь на MyISAM, потом буду думать, насчёт VPS.


  • 0