MySQL медленная выборка по PRIMARY KEY, что делать?

123
Solmyr
На сайте с 10.09.2007
Offline
501
#11
ivan-lev:
Если char 32 почему 96? реально нужны ключи в mb-кодировке?..

Не нужны, вчера уже исправил, быстрее не стало.

ivan-lev:
key_buffer_size что показывает?

Глобальный 16777216

Но я уже сделал отдельный CACHE INDEX на эту таблицу:

SET GLOBAL the_cache.key_buffer_size = 2000000 * 32

CACHE INDEX the_table IN the_cache

И тоже не помогло.

M
На сайте с 04.12.2013
Offline
214
#12

Для слагов лучше использовать varchar, а еще лучше varbinary.

А еще лучше для больших объемов использовать числовые id, если у вас не социалка какая-нибудь (с поддержкой коротких адресов).

Хостинг FOZZY ( https://u75.ru/fozzy ) / Выслушаю предложения на домены ( https://u75.ru/domains-for-sale ) / Домены и скрипт для коротких ссылок ( https://u75.ru/domains-for-shortcuts )
edogs software
На сайте с 15.12.2005
Offline
771
#13
Solmyr:
Вот честно не понимаю, почему хвалят Postgre? Во многих случаях, когда у меня были проблемы с быстродействием, я тестировал Maria, Postgre и Mongo, и всегда быстрее всего оказывались или Maria или Mongo но никогда Postgre.

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

Solmyr:
Но я уже сделал отдельный CACHE INDEX на эту таблицу:

SET GLOBAL the_cache.key_buffer_size = 2000000 * 32
CACHE INDEX the_table IN the_cache

И тоже не помогло.

Так то кэш-индекс. Да, индекс у Вас возьмется из памяти, но потом-то надо добыть данные с диска. А это - еще раз 0.004 секунды в лучшем случае на позиционирование, т.е. из кэша индекса Вы берете данные о том, где лежит нужная строка на диске, потом он 0.004 секунды туда позицинируется в лучшем случае, считывает. А если несколько данных в разных местах (выборка в начале таблицы и в конце допустим), то учитывая размер таблицы в 3.5гб - будет несколько позиционирований.

Если надо ускорять - у Вас вариант а) сменить на ссд б) засунуть всё в память.

Разработка крупных и средних проектов. Можно с криптой. Разумные цены. Хорошее качество. Адекватный подход. Продаем lenovo legion в спб, дешевле магазинов, новые, запечатанные. Есть разные. skype: edogssoft
danforth
На сайте с 18.12.2015
Offline
153
#14
Solmyr:
Вот честно не понимаю, почему хвалят Postgre? Во многих случаях, когда у меня были проблемы с быстродействием, я тестировал Maria, Postgre и Mongo, и всегда быстрее всего оказывались или Maria или Mongo но никогда Postgre.

PostgreSQL почти всегда быстрее. Просто его нужно уметь готовить.

miketomlin:
Для слагов лучше использовать varchar, а еще лучше varbinary.

Я не думаю что там просто так char(32), скорее всего md5 хранят.

Solmyr, если данные в таблицы выбираются только по ключу (т.е. нет range запросов), можете вынести все в Redis. В InnoDB для PK иногда используются adaptive hash index. Чем обусловлен выбор таблицы MyISAM?

А вообще, сделайте пожалуйста

EXPLAIN FORMAT=JSON SELECT ...

и ещё вот это через CLI


SET profiling = 1;
SELECT ...-- это ваш запрос, в конце ;
SET profiling = 0;
SHOW PROFILE FOR QUERY 1;

Вангую что больше всего времени уходит на sending data, но тем не менее.

Junior Web Developer
dma84
На сайте с 21.04.2009
Offline
168
#15

У поля какая кодировка стоит? Может попробовать сделать CHARACTER SET 'ascii' COLLATE 'ascii_bin'?

Solmyr
На сайте с 10.09.2007
Offline
501
#16
danforth:
Я не думаю что там просто так char(32), скорее всего md5 хранят.

Там де-факто только циферки, но trailing zero имеют значения, то есть 00234 и 000234 это два разных айдишника. Потому тип данных сделан ascii_bin. Длина действительно может быть разной, от 5 до 32 цифер, но я не понимаю чем varchar в этом случае лучше чем char(32)

---------- Добавлено 28.03.2020 в 14:16 ----------

danforth:
если данные в таблицы выбираются только по ключу (т.е. нет range запросов), можете вынести все в Redis

Не хочется плодить зоопарк сервисов. И так уже есть Maria, Postgre и Mongo, только редиса не хватает...

---------- Добавлено 28.03.2020 в 14:20 ----------

danforth:
Вангую что больше всего времени уходит на sending data, но тем не менее.

MariaDB [odnodata]> SHOW PROFILE FOR QUERY 1;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000021 |

| Waiting for query cache lock | 0.000007 |

| init | 0.000005 |

| checking query cache for query | 0.000032 |

| checking permissions | 0.000007 |

| Opening tables | 0.000016 |

| After opening tables | 0.000006 |

| System lock | 0.000006 |

| Table lock | 0.000007 |

| Waiting for query cache lock | 0.000029 |

| init | 0.000038 |

| optimizing | 0.000020 |

| statistics | 0.019021 |

| preparing | 0.000011 |

| Unlocking tables | 0.000018 |

| executing | 0.000006 |

| Sending data | 0.000033 |

| end | 0.000006 |

| query end | 0.000019 |

| closing tables | 0.000025 |

| Unlocking tables | 0.000010 |

| freeing items | 0.000009 |

| updating status | 0.000006 |

| Waiting for query cache lock | 0.000004 |

| updating status | 0.000021 |

| Waiting for query cache lock | 0.000005 |

| updating status | 0.000004 |

| storing result in query cache | 0.000029 |

| cleaning up | 0.000005 |

+--------------------------------+----------+

На одном запросе по-моему ничего видно не будет, это статистику на 1000 надо собирать.

---------- Добавлено 28.03.2020 в 14:25 ----------

danforth:
А вообще, сделайте пожалуйста

{

"query_block": {

"select_id": 1,

"table": {

"table_name": "the_table",

"access_type": "const",

"possible_keys": ["PRIMARY"],

"key": "PRIMARY",

"key_length": "32",

"used_key_parts": ["id"],

"ref": ["const"],

"rows": 1,

"filtered": 100

}

}

S
На сайте с 30.09.2016
Offline
469
#17
Solmyr:
Там де-факто только циферки, но trailing zero имеют значения, то есть 00234 и 000234 это два разных айдишника.

Это не trailing zero, это ведущие нули

Solmyr:
Длина действительно может быть разной, от 5 до 32 цифер, но я не понимаю чем varchar в этом случае лучше чем char(32)
А нафига char(32), если длина разная?
Отпилю лишнее, прикручу нужное, выправлю кривое. Вытравлю вредителей.
Solmyr
На сайте с 10.09.2007
Offline
501
#18

В общем я уже решил. Полную структуру буду хранить в Монго, а для решения изначальной проблемы сделаю отдельную таблицу из id, value и буду ее хранить в MariaDB в таблице типа Memory.

danforth
На сайте с 18.12.2015
Offline
153
#19
Solmyr:
но я не понимаю чем varchar в этом случае лучше чем char(32)

длина будет n байт + 1, а не 32 байта.

Solmyr:
statistics | 0.019021 |
Solmyr:
На одном запросе по-моему ничего видно не будет, это статистику на 1000 надо собирать.

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

Попробуйте ANALYZE TABLE; сделать, если это не прод, можно так-же попробовать OPTIMIZE TABLE сделать. У вас mysql больше думает над тем, как выполнить запрос, чем над выборкой самих данных. Насколько критично наличие констрейнта на уникальность? Если в вашем стеке уже есть PostgreSQL, и версия 10 или выше, можете попробовать сделать таблицу с полем TEXT, но вместо PRIMARY KEY сделать так:

CREATE INDEX some_index_name ON your_table USING HASH(your_field);

Но тогда не будет уникального констрейнта (возможно появление дублей, если будете просто делать INSERT).

Хеш индексы чуть быстрее будут (должно быть O(1) вместо O(logn), но не уверен что в pg честная константа).

---------- Добавлено 28.03.2020 в 12:33 ----------

Solmyr:
а для решения изначальной проблемы сделаю отдельную таблицу из id, value и буду ее хранить в MariaDB в таблице типа Memory.

Memory это ещё более жалкое поделие, чем MyISAM :)

IL
На сайте с 20.04.2007
Offline
418
#20
Solmyr:
хранить в MariaDB в таблице типа Memory.

Вполне рабочее решение.. если памяти достаточно и остальные данные не нужны .

+ не забывать обновлять при добавлении и заполнять, если она по какой-то причине "опустела".. :D

... :) Облачные серверы от RegRu - промокод 3F85-3D10-806D-7224 ( http://levik.info/regru )
123

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий