Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Форумы портала PHP.SU :: Версия для печати :: Помогите оптимизировать таблицу
Форумы портала PHP.SU » PHP » SQL и Архитектура БД » Помогите оптимизировать таблицу

Страниц (1): [1]
 

1. nkl - 02 Сентября, 2014 - 14:25:53 - перейти к сообщению
Имеется таблица: id, url, html. В url хранятся урлы вида "http://site.com/news/bla-bla-bla/bla-bla/bla". А в столбце html соответвенно html код запрашиваемого урла. Сейчас в таблице чуть более 10 000 записей, и уже сейчас выборка типа SELECT * FROM crawler WHERE url = 'http://site.com/bla-bla/bla-bla/bla'; занимает пол секунды, а т.к. этот запрос происходит при получении любого url, в целом это влияет на загрузку любой страницы (не важно со старой версии сайта она или с новой). В итоге, имею почти секундную задержку до получения контента.

Пытался победить созданием такого индекса:
Спойлер (Отобразить)

но к каким либо заметным результатам это не привело. Что я делаю не так и как мне ускорить выборку по текстовому полю такой длинны какую обычно имеют современные модные многоуровневые ЧПУ УРЛы?
Заранее спасибо за идеи! Улыбка
2. RickMan - 02 Сентября, 2014 - 14:35:33 - перейти к сообщению
А url, я так понимаю, уникальный?
3. nkl - 02 Сентября, 2014 - 14:43:49 - перейти к сообщению
Да, url уникальный. Сейчас попробовал сделать его первичным ключом таблицы, один фиг, оно наоборот еще медленнее стало.
(Добавление)
Перевел таблицу в MYISAM, сделал полнотекстовый индекс для этого поля длиной 255 символов итог: никакого прироста в скорости выборки. Огорчение
4. RickMan - 02 Сентября, 2014 - 15:12:14 - перейти к сообщению
nkl пишет:
Да, url уникальный. Сейчас попробовал сделать его первичным ключом таблицы, один фиг, оно наоборот еще медленнее стало.
(Добавление)
Перевел таблицу в MYISAM, сделал полнотекстовый индекс для этого поля длиной 255 символов итог: никакого прироста в скорости выборки. Огорчение


А зачем делать первичным? Пусть первичным будет id. Сделай его для начала уникальным.

http://www[dot]mysql[dot]ru/docs/man/Ful[dot][dot][dot]text_Search[dot]html
5. Мелкий - 02 Сентября, 2014 - 15:18:07 - перейти к сообщению
Повесьте хэш-индекс.
Правда, mysql их не умеет, так что добавляете ещё поле и заполняете его значением хеш-функции от урла. Возможно простой int и crc32 дадут достаточную селективность. Искать надо, соответственно, по WHERE url = 'http://site.com/bla-bla/bla-bla/bla' and hash_index='значение CRC32 от урла'
6. tuareg - 02 Сентября, 2014 - 18:51:28 - перейти к сообщению
Мелкий пишет:
Искать надо, соответственно, по WHERE url = 'http://site.com/bla-bla/bla-bla/bla' and hash_index='значение CRC32 от урла'

Да только наоборот WHERE hash_index='значение CRC32 от урла' AND url = 'http://site.com/bla-bla/bla-bla/bla'
7. Мелкий - 02 Сентября, 2014 - 19:11:47 - перейти к сообщению
tuareg, не имеет значения. Если только для соблюдения принятого в проекте стандарта кодирования.
8. nkl - 03 Сентября, 2014 - 10:11:49 - перейти к сообщению
В общем, сделал еще 2 поля одно с md5, а другое с crc32 хэшами урлов, прироста в скорости никакой. Стал на хостера грешить, потому как ранее с ним не работал, дампанул базу на сервер digitalocean'a где все наши проекты крутятся, один хер, скорость выборки не изменилась, прямо печалька какая-то, скорость выборки:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT * FROM crawler WHERE md5_hash = "3536a09def9fe1026971f140c3a477b4"

Запрос открыт за 1,269c [0,532c выполнение, 0,737c выборка]

CODE (SQL):
скопировать код в буфер обмена
  1. SELECT * FROM crawler WHERE crc32_hash = 3570964716

crc32 = Запрос открыт за 1,254c [0,385c выполнение, 0,869c выборка]

Кстати говоря, раньше быстрее было, пока еще 2 столбца не добавил, и спрашивал по текстовому полю:
Запрос открыт за 0,652[0,285 выполнение, 0,367 выполнение]

Мне бы добиться общего времени работы запроса хотя бы до 0,3 сек, было бы просто шоколадно, тогда страница отлетала бы в nginx за полсекунды, но увы и ах Огорчение

Может на sql.ru что подскажут...
9. tuareg - 03 Сентября, 2014 - 10:37:36 - перейти к сообщению
Добавьте еще LIMIT 1;
И выложите EXLAIN запроса
P.S я надеюсь по полям индекс есть?
10. Мелкий - 03 Сентября, 2014 - 10:46:43 - перейти к сообщению
tuareg пишет:
P.S я надеюсь по полям индекс есть?

Присоединяюсь к вопросу.
11. nkl - 03 Сентября, 2014 - 10:51:24 - перейти к сообщению
Вот explain запроса:
id - 1
select_type - SIMPLE
table - crawler
type - const
possible_keys - UK_crawler_crc32_has
key - UK_crawler_crc32_has
key_len - 8
ref - const
rows - 1
Extra - null
P.S.
Если выбирать по id записи, скорость такая же, в среднем 0.8 с.
Вот как щас таблица выглядит:
CODE (SQL):
скопировать код в буфер обмена
  1. CREATE TABLE ***.crawler (
  2.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.   url varchar(255) NOT NULL,
  4.   visit_date datetime NOT NULL,
  5.   html longtext NOT NULL,
  6.   crc32_hash bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  7.   PRIMARY KEY (id, url, crc32_hash),
  8.   INDEX IDX_crawler (crc32_hash, id, url, visit_date),
  9.   UNIQUE INDEX UK_crawler_crc32_hash (crc32_hash)
  10. )
  11. ENGINE = INNODB
  12. AUTO_INCREMENT = 10007
  13. AVG_ROW_LENGTH = 10026
  14. CHARACTER SET utf8
  15. COLLATE utf8_general_ci
  16. ROW_FORMAT = DYNAMIC;

(Добавление)
Кстати, LIMIT 1 чуть ускорил запрос, в среднем на 0,1с)) Ускоряем дальше)
12. tuareg - 03 Сентября, 2014 - 11:05:33 - перейти к сообщению
Не ускорите, если тупо запрос по id => 0.8c . Быстрей работать не будет Недовольство, огорчение
Т.е быстрей чем по PK никак.
P.S но это очень странно, почему так долго выполняется этот запрос
13. Мелкий - 03 Сентября, 2014 - 11:20:18 - перейти к сообщению
nkl пишет:
UNIQUE INDEX UK_crawler_crc32_hash (crc32_hash)

Хэш-индекс не должен быть уникальным, хэш может быть идентичен для разных исходных данных.

nkl пишет:
PRIMARY KEY (id, url, crc32_hash),

Это что такое?
14. tuareg - 03 Сентября, 2014 - 11:38:56 - перейти к сообщению
Дополню Мелкий вполне хватит и int(11) беззнакового Улыбка
Т.е по идее Вам вполне хватит
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. CREATE TABLE ***.crawler (
  3.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   url varchar(255) NOT NULL,
  5.   visit_date datetime NOT NULL,
  6.   html longtext NOT NULL,
  7.   crc32_hash int(10) UNSIGNED NOT NULL DEFAULT 0,
  8.   PRIMARY KEY (id),
  9.   INDEX IDX_crawler (crc32_hash, id, url, visit_date),# Вот это тоже зачем???
  10.   INDEX UK_crawler_crc32_hash (crc32_hash)
  11. )
  12. ENGINE = INNODB
  13.  
15. nkl - 03 Сентября, 2014 - 14:16:42 - перейти к сообщению
Пардоньте друзья! Все в порядке! Это оказывается мой клиент СУБД так тормозит, хотя я пользовался функцией профилировки запросов в нем. В частности клиент называется dbForge Studio fo MySQL, удобный и няшный, но тупит оказывается.

Сейчас проверил те же самые запросы через консоль и вуаля, запрос по УРЛу занимает 0,02 c., запрос по id 0,00 c., запрос по crc32 хэшу 0.00 c.

Сами полюбуйтесь:
mysql> SELECT * FROM ***.crawler WHERE id = 2145 LIMIT 1;
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
| id | url | visit_date | html | crc32_hash |
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
| 2145 | http://site[dot]com/news/chempionat_[dot][dot][dot]/2014-03-22-8846 | 0000-00-00 00:00:00 | | 3191665859 |
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
1 row in set (0,00 sec)

mysql> SELECT * FROM ***.crawler WHERE url = "http://site.com/news/chempionat_rossii_2013_14_22_j_tur_razogrev_22_03_2014/2014-03-22-8846" LIMIT 1;
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
| id | url | visit_date | html | crc32_hash |
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
| 2145 | http://site[dot]com/news/chempionat_[dot][dot][dot]/2014-03-22-8846 | 0000-00-00 00:00:00 | | 3191665859 |
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
1 row in set (0,02 sec)

mysql> SELECT * FROM ***.crawler WHERE crc32_hash = 3191665859 LIMIT 1;
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
| id | url | visit_date | html | crc32_hash |
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
| 2145 | http://site[dot]com/news/chempionat_[dot][dot][dot]/2014-03-22-8846 | 0000-00-00 00:00:00 | | 3191665859 |
+------+---------------------------------------------------------------------------------------------------+---------------------+------+------------+
1 row in set (0,00 sec)

отдельное спасибо пользователю tuareg, именно он посчитал странным столь долгую выборку по primary_key [b]id[/b].

Мораль сей басни такова, меряйте скорость выполнения запросов mysql непосредственно из консоли mysql, а не сторонним говнософтом расположенным на расстоянии в 4-5к км от сервера!.. Нахмурился

 

Powered by ExBB FM 1.0 RC1